数据库联系增删查改

题目：

代码：

dialog_layout.xml文件：

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical">

    <EditText
        android:id="@+id/name_et"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="请输入学生姓名" />

    <EditText
        android:id="@+id/age_et"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="请输入学生年龄"
        android:inputType="phone" />

    <EditText
        android:id="@+id/score_et"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="请输入学生分数"
        android:inputType="phone" />
</LinearLayout>

activity_main.xml文件：

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context="com.mrzhao.sqlitedemo.MainActivity">

    <Button
        android:id="@+id/bt_create"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_margin="5dp"
        android:onClick="onClick"
        android:text="创建数据库" />

    <Button
        android:id="@+id/bt_insert"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_margin="5dp"
        android:onClick="onClick"
        android:text="插入数据" />

    <Button
        android:id="@+id/bt_query"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_margin="5dp"
        android:onClick="onClick"
        android:text="查询数据" />

    <Button
        android:id="@+id/bt_upData"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_margin="5dp"
        android:onClick="onClick"
        android:text="修改数据" />

    <Button
        android:id="@+id/bt_delete"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_margin="5dp"
        android:onClick="onClick"
        android:text="删除数据" />

    <ScrollView
        android:layout_width="match_parent"
        android:layout_height="match_parent">

        <TextView
            android:id="@+id/show_tv"
            android:layout_width="match_parent"
            android:layout_height="match_parent"
            android:layout_margin="10dp"
            android:textColor="@android:color/holo_blue_light"
            android:textSize="14sp" />
    </ScrollView>
</LinearLayout>

代码目录结构：

Config文件：

public class Config {
    public static final String DB_NAME = "student.db";
    public static final String TABLE_NAME = "student";
    public static final int DB_VERSION = 1;
}

SQLiteHelper文件：

public class SQLiteHelper extends SQLiteOpenHelper {
    public SQLiteHelper(Context context) {
        super(context, Config.DB_NAME, null, Config.DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table if not exists student(_id integer primary key autoincrement,name varchar(10),age  varchar(10),score int(10))");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

DBUtils文件:

public class DBUtils {
    private static DBUtils dbUtils;
    private SQLiteHelper helper;

    private DBUtils() {
    }

    public static synchronized DBUtils getDbUtils() {
        if (dbUtils == null) {
            dbUtils = new DBUtils();
        }
        return dbUtils;
    }

    /**
     * 创建数据库
     *
     * @param context
     */
    public void createDB(Context context) {
        helper = new SQLiteHelper(context);
    }

    /**
     * 添加一个学生
     *
     * @param student
     * @return
     */
    public boolean insertStudent(Student student) {
        SQLiteDatabase db = helper.getReadableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", student.getName());
        values.put("age", student.getAge());
        values.put("score", student.getScore());
        return db.insert(Config.TABLE_NAME, null, values) > 0;
    }

    /**
     * 查询全部学生
     *
     * @return
     */
    public List<Student> queryAllStudent() {
        List<Student> list = new ArrayList<>();
        SQLiteDatabase db = helper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from " + Config.TABLE_NAME, null);
        if (cursor != null && cursor.getCount() > 0) {
            while (cursor.moveToNext()) {
                String name = cursor.getString(cursor.getColumnIndex("name"));
                String age = cursor.getString(cursor.getColumnIndex("age"));
                int score = cursor.getInt(cursor.getColumnIndex("score"));
                Student student = new Student(name, age, score);
                list.add(student);
            }
        }
        return list;
    }

    /**
     * 根据姓名更新学生信息
     *
     * @param name
     * @param age
     * @param score
     * @return
     */
    public boolean upDataStudent(String name, String age, int score) {
        SQLiteDatabase db = helper.getReadableDatabase();
        ContentValues values = new ContentValues();
        values.put("age", age);
        values.put("score", score);
        return db.update(Config.TABLE_NAME, values, "name = ?", new String[]{name}) > 0;
    }

    /**
     *  根据名字删除一条数据
     * @param name
     * @return
     */
    public boolean deleteStudent(String name) {
        SQLiteDatabase db = helper.getReadableDatabase();
        return db.delete(Config.TABLE_NAME, "name = ?", new String[]{name}) > 0;
    }
}

Student文件：

public class Student {
    private String name;
    private String age;
    private int score;

    public Student(String name, String age, int score) {
        this.name = name;
        this.age = age;
        this.score = score;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAge() {
        return age;
    }

    public void setAge(String age) {
        this.age = age;
    }

    public int getScore() {
        return score;
    }

    public void setScore(int score) {
        this.score = score;
    }

    @Override
    public String toString() {
        return "Student{" +
                "name='" + name + '\'' +
                ", age='" + age + '\'' +
                ", score=" + score +
                '}'+"\n";
    }
}

MainActivity文件：

public class MainActivity extends AppCompatActivity {

    private TextView showTv;
    private DBUtils dbUtils;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        //用于展示查询数据的
        showTv = (TextView) findViewById(R.id.show_tv);
        //获取数据库的操作类
        dbUtils = DBUtils.getDbUtils();
    }

    public void onClick(View view) {
        switch (view.getId()) {
            case R.id.bt_create:
                //创建数据库
                dbUtils.createDB(this);
                break;
            case R.id.bt_insert:
                //插入数据
                showStudentDialog(1);
                break;
            case R.id.bt_query:
                //查询数据
                List<Student> students = dbUtils.queryAllStudent();
                if (students.size() > 0) {
                    showTv.setText(students.toString());
                }
                break;
            case R.id.bt_upData:
                //修改数据
                showStudentDialog(3);
                break;
            case R.id.bt_delete:
                //删除数据
                showStudentDialog(2);
                break;
        }
    }

    public void showStudentDialog(final int type) {
        String title = "";
        String btText = "";
        switch (type) {
            case 1:
                title = "添加学生";
                btText = "确认添加";
                break;
            case 2:
                title = "删除学生";
                btText = "确认删除";
                break;
            case 3:
                title = "更新学生";
                btText = "确认更新";
                break;
        }


        AlertDialog.Builder builder = new AlertDialog.Builder(this);
        builder.setTitle(title);
        builder.setIcon(R.mipmap.ic_launcher);
        View view = LayoutInflater.from(this).inflate(R.layout.dialog_layout, null);
        final EditText nameEt = (EditText) view.findViewById(R.id.name_et);
        final EditText ageEt = (EditText) view.findViewById(R.id.age_et);
        final EditText scoreEt = (EditText) view.findViewById(R.id.score_et);

        builder.setView(view);
        builder.setPositiveButton(btText, new DialogInterface.OnClickListener() {
            @Override
            public void onClick(DialogInterface dialog, int which) {
                String name = nameEt.getText().toString();
                String age = ageEt.getText().toString();
                int score = Integer.valueOf(scoreEt.getText().toString());
                if (TextUtils.isEmpty(name)) {
                    Toast.makeText(MainActivity.this, "请输入用户名", Toast.LENGTH_SHORT).show();
                    return;
                }

                boolean isSuccess = false;
                switch (type) {
                    case 1:
                        //添加
                        isSuccess = dbUtils.insertStudent(new Student(name, age, score));
                        break;
                    case 2:
                        //删除
                        isSuccess = dbUtils.deleteStudent(name);
                        break;
                    case 3:
                        //修改
                        isSuccess = dbUtils.upDataStudent(name, age, score);
                        break;
                }
                //更新成功 则 更新UI展示
                if (isSuccess) {
                    List<Student> students = dbUtils.queryAllStudent();
                    if (students.size() > 0) {
                        showTv.setText(students.toString());
                    }
                }


            }
        });
        builder.setNegativeButton("取消", new DialogInterface.OnClickListener() {
            @Override
            public void onClick(DialogInterface dialog, int which) {

            }
        });
        builder.show();
    }
}