半期考试 之 SQLite 操作2

要求：

• 两个输入框，要求输入姓名和年龄；
• 下方四个按钮，分别表示增加，删除，更新，回滚；
• 添加或者删除后，下方的listview数据动态改变；

xml

<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout 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"
    tools:context=".MainActivity">

    <EditText
        android:id="@+id/editText_name"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:layout_marginStart="8dp"
        android:layout_marginLeft="8dp"
        android:layout_marginEnd="8dp"
        android:layout_marginRight="8dp"
        android:ems="10"
        android:hint="Input name?"
        android:inputType="textPersonName"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent" />

    <EditText
        android:id="@+id/editText_age"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:layout_marginStart="8dp"
        android:layout_marginLeft="8dp"
        android:layout_marginTop="11dp"
        android:layout_marginEnd="8dp"
        android:layout_marginRight="8dp"
        android:ems="10"
        android:hint="Input age?"
        android:inputType="number"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/editText_name" />

    <Button
        android:id="@+id/button"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="44dp"
        android:layout_marginEnd="10dp"
        android:layout_marginRight="10dp"
        android:onClick="add"
        android:text="Add"
        app:layout_constraintEnd_toStartOf="@+id/button2"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="@+id/editText_age"
        tools:ignore="OnClick" />

    <Button
        android:id="@+id/button2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginEnd="6dp"
        android:layout_marginRight="6dp"
        android:onClick="delete"
        android:text="Delete"
        app:layout_constraintBottom_toTopOf="@+id/listView"
        app:layout_constraintEnd_toStartOf="@+id/button3"
        app:layout_constraintStart_toEndOf="@+id/button"
        tools:ignore="OnClick" />

    <Button
        android:id="@+id/button3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginEnd="2dp"
        android:layout_marginRight="2dp"
        android:onClick="update"
        android:text="Update"
        app:layout_constraintBottom_toTopOf="@+id/listView"
        app:layout_constraintEnd_toStartOf="@+id/button4"
        app:layout_constraintStart_toEndOf="@+id/button2"
        tools:ignore="OnClick" />

    <Button
        android:id="@+id/button4"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginEnd="1dp"
        android:layout_marginRight="1dp"
        android:onClick="retrieve"
        android:text="Retrieve"
        app:layout_constraintBottom_toTopOf="@+id/listView"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toEndOf="@+id/button3"
        tools:ignore="OnClick" />

    <ListView
        android:id="@+id/listView"
        android:layout_width="0dp"
        android:layout_height="0dp"
        android:layout_marginStart="8dp"
        android:layout_marginLeft="8dp"
        android:layout_marginEnd="8dp"
        android:layout_marginRight="8dp"
        android:layout_marginBottom="8dp"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/button" />

</androidx.constraintlayout.widget.ConstraintLayout>

java

public class MainActivity extends AppCompatActivity {

    MySqlHelper mySQLHelper;
    SQLiteDatabase db;
    EditText editText_name, editText_age;
    ListView listView;
    SimpleCursorAdapter adapter;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        editText_name = findViewById(R.id.editText_name);
        editText_age = findViewById(R.id.editText_age);
        listView = findViewById(R.id.listView);
        mySQLHelper = new MySqlHelper(this, "db", null, 1);
        db = mySQLHelper.getWritableDatabase();
        Cursor cursor = db.query(MySqlHelper.PersonTable, null, null, null, null, null, null, null);

        adapter = new SimpleCursorAdapter(this, android.R.layout.simple_list_item_2, cursor, new String[]{"name", "age"}, new int[]{android.R.id.text1, android.R.id.text2}, CursorAdapter.FLAG_REGISTER_CONTENT_OBSERVER);
        listView.setAdapter(adapter);
    }

    public void add(View view) {
        String name = editText_name.getText().toString();
        String age = editText_age.getText().toString();
//        db.execSQL("insert into person(name,age) values('"+name+"','"+age+"')");
        ContentValues contentValues = new ContentValues();
        contentValues.put("name", name);
        contentValues.put("age", age);
        db.insert(MySqlHelper.PersonTable, null, contentValues);

        reload();
    }

    public void delete(View view) {
        String name = editText_name.getText().toString();
        db.delete(MySqlHelper.PersonTable, "name=?", new String[]{name});
        reload();
    }

    public void update(View view) {
        String name = editText_name.getText().toString();
        String age = editText_age.getText().toString();
        ContentValues contentValues = new ContentValues();
        contentValues.put("name", name);
        contentValues.put("age", age);
        db.update(MySqlHelper.PersonTable, contentValues, "name=?", new String[]{name});
        reload();
    }

    public void retrieve(View view) {
        String name = editText_name.getText().toString();
        new MyAsyncTask().execute("%" + name + "%");
//        Cursor cursor=db.query(MySQLHelper.PersonTable,null,"name like *",new String[]{name},null,null,null,null);
//        adapter.swapCursor(cursor);
    }

    private void reload() {
        Cursor cursor = db.query(MySqlHelper.PersonTable, null, null, null, null, null, null, null);
        adapter.swapCursor(cursor);
    }

    class MyAsyncTask extends AsyncTask<String, Nullable, Cursor> {
        @Override
        protected Cursor doInBackground(String... strings) {
            Cursor cursor = db.query(MySqlHelper.PersonTable, null, "name like ?", new String[]{"%" + strings[0] + "%"}, null, null, null, null);
            return cursor;
        }

        @Override
        protected void onPostExecute(Cursor cursor) {
            super.onPostExecute(cursor);
            adapter.swapCursor(cursor);
        }
    }
}

数据库操作类

public class MySqlHelper extends SQLiteOpenHelper {

    public static final String PersonTable = "person";

    public MySqlHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

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

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

    }
}