[Lesson11] SQLite

activity_main:
全以LinearLayou进行排版

<?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=".MainActivity">
    
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="产品："
            android:textSize="24sp"/>
        <EditText
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:id="@+id/edtPrice"
            android:inputType="number"/>
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="价格："
            android:textSize="24sp"/>
        <EditText
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:id="@+id/edtPrice"
            android:inputType="number"/>
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="数量："
            android:textSize="24sp"/>
        <EditText
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:id="@+id/edtAmount"
            android:inputType="number"/>
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="编号："
            android:textSize="24sp"/>
        <EditText
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:id="@+id/edtId"
            android:inputType="number"/>
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <Button
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:id="@+id/btnAdd"
            android:text = "新增"/>
        <Button
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:id="@+id/btnUpdate"
            android:text = "修改"/>
        <Button
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:id="@+id/btnDel"
            android:text = "删除"/>
        <Button
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:id="@+id/btnQuery"
            android:text = "查询"/>
    </LinearLayout>
</LinearLayout>

DBHelper:

public class DBHelper extends SQLiteOpenHelper {

    private final static String DATABASE_NAME = "store.db";
    private final static int VERSION = 1;

    public DBHelper(Context context){
        super(context,DATABASE_NAME,null,VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //第一次执行时，会建立资料表
        String sql = "create  table  product(_id integer primary key autoincrement,name varchar(30),price int,amount int) ";
        db.execSQL(sql);   //执行语法
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String sql = "drop table product";  //删除资料表
        db.execSQL(sql);
    }
}

MainActivity:

public class MainActivity extends AppCompatActivity {

    private DBHelper dbHelper = null;

    private EditText edtProduct,edtPrice,edtAmount,edtId;
    private Button btnAdd,btnUpdate,btnDel,btnQuery,btnMySQL;
    private TextView sqlData;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        dbHelper = new DBHelper(this);
        findViews();

    }

    private void findViews(){
        edtProduct = findViewById(R.id.edtProduct);
        edtPrice = findViewById(R.id.edtPrice);
        edtAmount = findViewById(R.id.edtAmount);
        edtId = findViewById(R.id.edtId);
        btnAdd = findViewById(R.id.btnAdd);
        btnUpdate = findViewById(R.id.btnUpdate);
        btnDel = findViewById(R.id.btnDel);
        btnQuery = findViewById(R.id.btnQuery);
        sqlData = findViewById(R.id.sqlData);

        btnAdd.setOnClickListener(addData);

        btnDel.setOnClickListener(delData);

        btnUpdate.setOnClickListener(updateData);

        btnQuery.setOnClickListener(v->{
            Cursor cursor = getAllData();
            StringBuffer sb = new StringBuffer("结果：\n");
            while(cursor.moveToNext()){
                int id = cursor.getInt(0);
                String name = cursor.getString(1);
                int price = cursor.getInt(2);
                int amount = cursor.getInt(3);

                sb.append(id).append("-");
                sb.append(name).append("-");
                sb.append(price).append("-");
                sb.append(amount).append("\n");
            }
            sqlData.setText(sb.toString());
        });
    }

    private View.OnClickListener delData = v -> {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        String id = edtId.getText().toString();
        db.delete("product","_id="+id,null);
        clearEditText();
    };

    private View.OnClickListener updateData = v -> {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name",edtProduct.getText().toString());
        values.put("price",Integer.parseInt(edtPrice.getText().toString()));
        values.put("amount",Integer.parseInt(edtAmount.getText().toString()));
        String id = edtId.getText().toString();

        db.update("product",values,"_id="+id,null);
        clearEditText();
    };

    private View.OnClickListener addData = v -> {
        //写入资料到SQLite Database
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name",edtProduct.getText().toString());
        values.put("price",Integer.parseInt(edtPrice.getText().toString()));
        values.put("amount",Integer.parseInt(edtAmount.getText().toString()));
        //新增
        db.insert("product",null,values);
        clearEditText();
    };

    //查询资料
    private Cursor getAllData(){
        SQLiteDatabase db = dbHelper.getReadableDatabase();

        //String[] columns = {"_id","name","price","amount"};
        //SQLite专用
        //Cursor cursor = db.query("product",columns,null,null,null,null,null);

        String sql = "select _id,name,price,amount  from product";
        Cursor cursor = db.rawQuery(sql,null);

        return cursor;
    }

    private void clearEditText(){
        edtProduct.setText("");
        edtPrice.setText("");
        edtAmount.setText("");
        edtId.setText("");
    }

    protected  void onDestroy(){
        super.onDestroy();
        dbHelper.close();
    }
}

谢谢大家愿意花时间阅读，小弟弟我在此鞠躬