Android Studio Sqlite的增删改查

MySQLiteOpenHelper.java

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import androidx.annotation.Nullable;

/**
 * 工具类 单例模式(1 构造函数私有化,2 对外提供函数)
 */
public class MySQLiteOpenHelper extends SQLiteOpenHelper {

    //2 对外提供函数
    private static MySQLiteOpenHelper myInstansce;

    public synchronized static MySQLiteOpenHelper getInstance(Context context){
        if(myInstansce == null){
            myInstansce =  new MySQLiteOpenHelper(context,"qingDB.db",null,1); //以后想要做数据库升级,把1改成2
        }
        return myInstansce;
    }

    //1 构造函数私有化 (有数据库的名字 数据库的版本号 → 拿到这些信息才能有能力创建数据库)
    private MySQLiteOpenHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    //创建表,表数据初始化,数据库第一次创建的时候调用,第2次发现有了,就不会重复创建了,也意味着此函数只会创建一次
    //数据库初始化用的
    @Override
    public void onCreate(SQLiteDatabase db) {

        //创建表
        //主键一般两点要求:
        //    1 命名必须是:_id (下划线id)
        //    2 格式必须是Integer
        // 主键自动增长: autoincrement
        // 创建了两列:_id,name
        String sql = "create table people(_id integer primary key autoincrement,name text)";
        db.execSQL(sql);

    }

    //数据库升级用的
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

MainActivity.java

import androidx.appcompat.app.AppCompatActivity;
import android.database.Cursor;
import android.os.Bundle;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.view.View;
import android.widget.EditText;
import android.widget.Toast;

public class MainActivity extends AppCompatActivity {
    private EditText edi1,edi2,edi3,edi4;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        edi1 = findViewById(R.id.edi1);
        edi2 = findViewById(R.id.edi2);
        edi3 = findViewById(R.id.edi3);
        edi4 = findViewById(R.id.edi4);

        MySQLiteOpenHelper helper = MySQLiteOpenHelper.getInstance(this);
        helper.getReadableDatabase();
    }

    //查询
    public void query(View view) {
        String pwd = edi2.getText().toString();
        //因为是单例模式,所以获取的是同一个数据库
        MySQLiteOpenHelper helper = MySQLiteOpenHelper.getInstance(this);
        SQLiteDatabase db = helper.getReadableDatabase();
        //如果数据库打开成功,db.isOpen()返回true,就进入if
        if( db.isOpen()){
            //查询返回游标
            Cursor cursor = db.rawQuery("select * from people where _id=1",null);
            //检测下面有没有数据,迭代游标,游标往下面移动来遍历数据
            while(cursor.moveToNext()){
                //获取第几列的数据
                //偷懒的写法
                //int _id = cursor.getInt(0);
                //String name = cursor.getString(1);
                //标准写法
                int _id =  cursor.getInt(cursor.getColumnIndex("_id"));
                String name =  cursor.getString(cursor.getColumnIndex("name"));
                Log.e("cs",_id+" "+name);
                System.out.println(name);

                if(pwd.equals(name)){
                    Toast.makeText(MainActivity.this, "登录成功！", Toast.LENGTH_LONG).show();
                }
            }
            //游标记得一定要关闭,否则会耗费性能
            cursor.close();
            //数据库也关闭
            db.close();
        }
    }

    //插入数据
    public void insert(View view) {
        MySQLiteOpenHelper helper = MySQLiteOpenHelper.getInstance(this);
        SQLiteDatabase db = helper.getWritableDatabase();
        //规范,检查数据库打开了才能执行操作
        if(db.isOpen()){
            String sql = "insert into people(name) values('qingDa')";
            db.execSQL(sql);
            //规范,必须关闭数据库
            db.close();
        }
    }

    //修改数据
    public void update(View view) {
        String pwd = edi4.getText().toString();
        MySQLiteOpenHelper helper = MySQLiteOpenHelper.getInstance(this);
        SQLiteDatabase db = helper.getWritableDatabase();
        //规范,检查数据库打开了才能执行操作
        if(db.isOpen()){
            //修改第2条数据
            String sql = "update people set name = ? where _id = ?";
            db.execSQL(sql,new Object[]{pwd,1});
            //规范,必须关闭数据库
            db.close();
        }
    }

    //删除
    public void delete(View view) {
        MySQLiteOpenHelper helper = MySQLiteOpenHelper.getInstance(this);
        SQLiteDatabase db = helper.getWritableDatabase();
        //规范,检查数据库打开了才能执行操作
        if(db.isOpen()){
            //删除第5条数据
            String sql = "delete from people where _id=?";
            db.execSQL(sql,new Object[]{5});
            //规范,必须关闭数据库
            db.close();
        }
    }
}

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=".MainActivity">
    <EditText
        android:id="@+id/edi1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"/>
    <EditText
        android:id="@+id/edi2"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"/>
    <android.widget.Button
        android:id="@+id/chaxun"
        android:layout_width="110dp"
        android:layout_height="wrap_content"
        android:text="查询"
        android:onClick="query"
        android:layout_marginLeft="30dp"/>
    <android.widget.Button
        android:layout_width="110dp"
        android:layout_height="wrap_content"
        android:text="添加"
        android:onClick="insert"
        android:layout_marginLeft="30dp"/>
    <android.widget.Button
        android:layout_width="110dp"
        android:layout_height="wrap_content"
        android:text="删除"
        android:onClick="delete"
        android:layout_marginLeft="30dp"/>
    <EditText
        android:id="@+id/edi3"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"/>
    <EditText
        android:id="@+id/edi4"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"/>
    <android.widget.Button
        android:layout_width="110dp"
        android:layout_height="wrap_content"
        android:text="修改"
        android:onClick="update"
        android:layout_marginLeft="30dp"/>
</LinearLayout>