Android-SQLite

布局设计 activity_main.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout 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"
    android:paddingHorizontal="10dp"
    android:gravity="center">
    <EditText
        android:id="@+id/et_id"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="编号"
        android:textSize="30sp"/>
    <EditText
        android:id="@+id/et_name"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="名称"
        android:textSize="30sp"
        android:layout_marginTop="20dp"
        android:layout_below="@+id/et_id"/>
    <EditText
        android:id="@+id/et_number"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="数量"
        android:textSize="30sp"
        android:layout_marginTop="20dp"
        android:layout_below="@+id/et_name"/>
    <EditText
        android:id="@+id/et_price"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="单价"
        android:textSize="30sp"
        android:layout_marginTop="20dp"
        android:layout_below="@+id/et_number"/>

    <LinearLayout
        android:id="@+id/lyt_btn"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="20dp"
        android:layout_below="@+id/et_price"
        android:orientation="horizontal">
        <Button
            android:id="@+id/btn_insert"
            android:layout_width="0dp"
            android:layout_weight="1"
            android:layout_marginHorizontal="4dp"
            android:layout_height="wrap_content"
            android:text="插入"/>
        <Button
            android:id="@+id/btn_query"
            android:layout_width="0dp"
            android:layout_weight="1"
            android:layout_marginHorizontal="4dp"
            android:layout_height="wrap_content"
            android:text="查询"/>
        <Button
            android:id="@+id/btn_delete"
            android:layout_width="0dp"
            android:layout_weight="1"
            android:layout_marginHorizontal="4dp"
            android:layout_height="wrap_content"
            android:text="删除"/>
        <Button
            android:id="@+id/btn_update"
            android:layout_width="0dp"
            android:layout_weight="1"
            android:layout_marginHorizontal="4dp"
            android:layout_height="wrap_content"
            android:text="修改"/>
    </LinearLayout>
    <TextView
        android:id="@+id/tv"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="20dp"
        android:layout_below="@id/lyt_btn"/>


</RelativeLayout>

编写 DBOpenHelper.java

package com.example.sqlitedemo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

public class DBOpenHelper extends SQLiteOpenHelper {
    private static final String TABLE_NAME = "shop";
    public DBOpenHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "CREATE TABLE IF NOT EXISTS "+
                TABLE_NAME+
                "(" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "name TEXT," +
                "number INTEGER," +
                "price REAL)";
        db.execSQL(sql);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String sql = "DROP TABLE IF EXISTS "+TABLE_NAME;
        db.execSQL(sql);
        onCreate(db);
    }

    public void insert(String name, int number, float price) {
        SQLiteDatabase db = getWritableDatabase();
        db.beginTransaction();
        try{
            ContentValues values = new ContentValues();
            values.put("name", name);
            values.put("number", number);
            values.put("price", price);
            db.insertOrThrow(TABLE_NAME, null,values);
            db.setTransactionSuccessful();
        }finally {
            db.endTransaction();
        }
    }

    public void update(String id, String name) {
        SQLiteDatabase db = getWritableDatabase();
        db.beginTransaction();
        try{
            ContentValues values = new ContentValues();
            values.put("name", name);
            db.update(TABLE_NAME, values, "id = ?", new String[]{id});
            db.setTransactionSuccessful();
        }finally {
            db.endTransaction();
        }
    }

    public void delete(String id) {
        SQLiteDatabase db = getWritableDatabase();
        db.beginTransaction();
        try{
            db.delete(TABLE_NAME, "id = ?", new String[]{id});
        }finally {
            db.endTransaction();
        }
    }

    public Cursor query(String name){
        SQLiteDatabase db = getReadableDatabase();
        return db.query(TABLE_NAME, null, "name like ?", new String[]{"%"+name+"%"}, null,null,null);
    }
}

编写 MainActivity.java

package com.example.sqlitedemo;

import androidx.appcompat.app.AppCompatActivity;

import android.database.Cursor;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

public class MainActivity extends AppCompatActivity {
    private EditText mEtId, mEtName, mEtNumber, mEtPrice;
    private Button mBtnInsert, mBtnQuery, mBtnDelete, mBtnUpdate;
    private TextView mTv;
    private DBOpenHelper dbOpenHelper;
    private static final String DB_NAME = "List.db";
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mEtId = findViewById(R.id.et_id);
        mEtName = findViewById(R.id.et_name);
        mEtNumber = findViewById(R.id.et_number);
        mEtPrice = findViewById(R.id.et_price);

        mBtnInsert = findViewById(R.id.btn_insert);
        mBtnQuery = findViewById(R.id.btn_query);
        mBtnDelete = findViewById(R.id.btn_delete);
        mBtnUpdate = findViewById(R.id.btn_update);

        mTv = findViewById(R.id.tv);

        dbOpenHelper = new DBOpenHelper(this, DB_NAME, null,1);

        mBtnInsert.setOnClickListener((View view)->{
            dbOpenHelper.insert(
                    mEtName.getText().toString(),
                    Integer.parseInt(mEtNumber.getText().toString()),
                    Float.parseFloat(mEtPrice.getText().toString())
            );
            mEtName.setText("");
            mEtNumber.setText("");
            mEtPrice.setText("");
            Toast.makeText(this, "insert success", Toast.LENGTH_SHORT).show();
        });
        mBtnDelete.setOnClickListener((View view)->{
            dbOpenHelper.delete(mEtId.getText().toString());
            Toast.makeText(this, "delete success", Toast.LENGTH_SHORT).show();
        });
        mBtnUpdate.setOnClickListener((View view)->{
            dbOpenHelper.update(
                    mEtId.getText().toString(),
                    mEtName.getText().toString()
            );
            Toast.makeText(this, "update success", Toast.LENGTH_SHORT).show();
        });
        mBtnQuery.setOnClickListener((View view)->{
            Cursor cursor = dbOpenHelper.query(mEtName.getText().toString());
            while (cursor.moveToNext()){
                int id = cursor.getInt(cursor.getColumnIndexOrThrow("id"));
                String name = cursor.getString(cursor.getColumnIndexOrThrow("name"));
                int number = cursor.getInt(cursor.getColumnIndexOrThrow("number"));
                float price = cursor.getFloat(cursor.getColumnIndexOrThrow("price"));
                mTv.append(String.format("%d---%s---%d---%f\n",id,name,number, price));
            }
            Toast.makeText(this, "query success", Toast.LENGTH_SHORT).show();

        });
    }
}

运行结果

编号在插入时可以无需输入, 将自动自增

插入若干条数据后,点击查询即可实现按名称的模糊查询,若名称为空,则相当于查询所有数据