SQLite增删改查示例

2020-05-03 分类于作者 xybz

用DatabaseHelper重写onCreate方法创建数据库db1.db ,用方法openOrCreateDatabase创建数据库db2.db,分别用SQLiteDatabase封装好的方法和sql语句实现增删改查

我写代码时遇到了很多问题，openOrCreateDatabase注意一定要写正确路径，写之前不要忘记初始化按钮；最后我发现此代码还有bug，此代码只是展示如何进行增删改擦，所以语句都写死了，只能运行一次，如果想再次运行，必须把前面建好的库删掉，重新按顺序运行；下面是代码部分

样式文件

<?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">

        <Button
            android:id="@+id/bt1"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:onClick="db1_create"
            android:text="db1_create" />

        <Button
            android:id="@+id/bt2"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:onClick="db2_create"
            android:text="db2_create" />
    </LinearLayout>

    <Button
        android:id="@+id/bt3"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="insert"
        android:text="insert" />

    <Button
        android:id="@+id/bt4"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="delete"
        android:text="delete" />

    <Button
        android:id="@+id/bt5"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="update"
        android:text="update" />

    <Button
        android:id="@+id/bt6"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="query"
        android:text="query" />
</LinearLayout>

Activity

package com.example.sqlite;

import androidx.appcompat.app.AppCompatActivity;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.Toast;

import DB.DatabaseHelper;

public class MainActivity extends AppCompatActivity {
    SQLiteDatabase db1,db2;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        Button bt1=(Button)this.findViewById(R.id.bt1);
        Button bt2=(Button)this.findViewById(R.id.bt2);
        Button bt3=(Button)this.findViewById(R.id.bt3);
        Button bt4=(Button)this.findViewById(R.id.bt4);
        Button bt5=(Button)this.findViewById(R.id.bt5);
        Button bt6=(Button)this.findViewById(R.id.bt6);
    }

    public void db1_create(View v){
        DatabaseHelper dbHelper = new DatabaseHelper(this, "db1.db", null, 1);
        db1=dbHelper.getWritableDatabase();
        Log.e("db1","create success");
    }
    public void db2_create(View v){
        db2 = SQLiteDatabase.openOrCreateDatabase ("/data/data/com.example.sqlite/databases/db2.db",null);
        String createtableSql = " create table user(id integer(10) , name varchar(10),primary key(id))";
        db2.execSQL(createtableSql);
        Toast.makeText(this, "数据库 db2 创建成功", Toast.LENGTH_SHORT).show();
        Log.e("db2","create success");
    }
    public void insert(View v){
        ContentValues values=new ContentValues();
        values.put("id", "0");
        values.put("name", "zs");
        db1.insert("user", null, values);
        values.put("id", "1");
        values.put("name", "ls");
        db1.insert("user", null, values);
        values.put("id", "2");
        values.put("name", "ww");
        db1.insert("user", null, values);
        Toast.makeText(this, "数据库 db1 插入数据成功", Toast.LENGTH_SHORT).show();

        String insertSql ="INSERT INTO user(id,name)VALUES(0,'zs'),(1,'ls'),(2,'ww')";
        db2.execSQL(insertSql);
        Toast.makeText(this, "数据库 db2 插入数据成功", Toast.LENGTH_SHORT).show();
    }
    public void delete(View v){
        String whereClause = "name=?";
        String[] whereArgs = {"zs"};
        db1.delete("user",whereClause,whereArgs);
        Toast.makeText(this, "数据库 db1 删除zs成功", Toast.LENGTH_SHORT).show();

        String deleteSql ="DELETE FROM user WHERE name='zs'";
        db2.execSQL(deleteSql);
        Toast.makeText(this, "数据库 db2 删除zs成功", Toast.LENGTH_SHORT).show();
    }
    public void update(View v){
        ContentValues values = new ContentValues();
        values.put("name", "zs");
        String whereClause = "name=?";
        String[] whereArgs = {"ls"};
        db1.update("user",values,whereClause,whereArgs);
        Toast.makeText(this, "db1 成功将ls改为zs", Toast.LENGTH_SHORT).show();

        String updateSql ="UPDATE user SET name='zs' WHERE name='ls';";
        db2.execSQL(updateSql);
        Toast.makeText(this, "db2 成功将ls改为zs", Toast.LENGTH_SHORT).show();

    }
    public void query(View v){
        String querySQl="select * from user";
        Cursor cursor=db1.rawQuery(querySQl, null);
        while (cursor.moveToNext()) {
            int id = cursor.getInt(0); //获取第一列的值,第一列的索引从0开始
            String name = cursor.getString(1);//获取第二列的值
            Log.e("data",id+":"+name);
        }
        Toast.makeText(this, "查询完成", Toast.LENGTH_SHORT).show();
    }
}

DatabaseHelper构造类

package DB;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

public class DatabaseHelper extends SQLiteOpenHelper {
    private Context mContext;

    public static final String createtableSql = "create table user(id integer(10) , name varchar(10),primary key(id))";

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(createtableSql);
        Toast.makeText(mContext, "数据库 db1 创建成功", Toast.LENGTH_SHORT).show();
    }

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

    }
}