Android SQLite数据库中基础的增删改查操作以及API的详解-阿里云开发者社区

SQLite建表注意事项

SQLite相关的API如下

1.创建一个类MyUserlogDBHelper去继承SQLiteOpenHelper，在该类里面创建表。

public class MyUserlogDBHelper extends SQLiteOpenHelper {
    public MyUserlogDBHelper db;
    //对外提供参数，单例模式
    private static SQLiteOpenHelper mHelper;
    public static synchronized SQLiteOpenHelper getInstance(Context context){
        if (mHelper==null){
            mHelper=new MyUserlogDBHelper(context,"user_log",null,1);
        }
        return mHelper;
    }
    private MyUserlogDBHelper(@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 user_log (_id integer primary key autoincrement,name text)";
        //拿到db执行sql语句即可
        db.execSQL(sql);
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }
 ）

2.在Activity中写查询，增加，修改，删除数据的操作。

public class MainActivity extends AppCompatActivity {
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
    }
    public void create(View view) {
        SQLiteOpenHelper helper = MyUserlogDBHelper.getInstance(this);
        SQLiteDatabase readableDatabase = helper.getReadableDatabase();
    }
    //查询语句
    public void query(View view) {
        SQLiteOpenHelper instance = MyUserlogDBHelper.getInstance(this);
        SQLiteDatabase db = instance.getReadableDatabase();
        if (db.isOpen()){
            //查询语句
            String sql="select * from user_log";
            //获取游标
            Cursor cursor = db.rawQuery(sql, null);
            while(cursor.moveToNext()){
                //不规范的写法
                /*int _id=cursor.getColumnIndex("0");
                int name=cursor.getColumnIndex("1");
                */
                //规范的写法
                int _id=cursor.getInt(cursor.getColumnIndex("_id"));
              String name=cursor.getString(cursor.getColumnIndex("name"));
                Log.d("数据库", "_id: "+_id+"，name:"+name);
            }
            //关闭游标
            cursor.close();
            //关闭数据库
            db.close();
        }
    }
    //插入语句
    public void insert(View view) {
        SQLiteOpenHelper instance = MyUserlogDBHelper.getInstance(this);
        SQLiteDatabase db = instance.getWritableDatabase();
        //判断数据库是否开启
        if (db.isOpen()){
            String sql="insert into user_log(name) values('路宇')";
            db.execSQL(sql);
        }
        db.close();
    }
    //修改
    public void update(View view) {
        SQLiteOpenHelper instance = MyUserlogDBHelper.getInstance(this);
        SQLiteDatabase db = instance.getWritableDatabase();
        if (db.isOpen()){
            String sql="update user_log set name=? where _id=?";
            db.execSQL(sql,new Object[]{"成龙","5"});
        }
        //关闭数据库
        db.close();
    }
    //删除数据
    public void delete(View view) {
        SQLiteOpenHelper instance = MyUserlogDBHelper.getInstance(this);
        SQLiteDatabase db = instance.getWritableDatabase();
        //判断数据是否是打开状态
        if (db.isOpen()){
            String sql="delete from user_log where _id=?";
            db.execSQL(sql,new Object[]{6});
        }
        db.close();//关闭数据库操作
    }
}

3.布局文件。

<?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"
    tools:context=".MainActivity"
    android:orientation="vertical"
    >
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="创建表"
        android:onClick="create" />
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="查询"
        android:onClick="query" />
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="插入"
        android:onClick="insert" />
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="修改"
        android:onClick="update" />
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="删除"
        android:onClick="delete" />
</LinearLayout>

以上就是SQLite数据库中，最简单的增删改查操作！

下面看第二个实例如下

1、创建DBTestHelper类

public class DBTestHelper extends SQLiteOpenHelper {
    private static DBTestHelper dbTestHelper = null;
    public DBTestHelper(@Nullable Context context, int version) {
        super(context, "ly.db", null, version);
    }
    /**
     * 什么时候才会创建数据库文件？
     * 1.数据库文件不存在
     * 2.连接数据库
     * <p>
     * 什么时候调用该方法？
     * 当数据库文件创建时调用
     *
     * @param db
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.i("TAG", "DBTestHelper onCreate() ");
        String sql = "CREATE TABLE person(" +
                "       _id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "       name VARCHAR," +
                "       age INT" +
                ");";
        db.execSQL(sql);
        //插入一些初始化的值
        db.execSQL("INSERT INTO person(name,age) VALUES('Tom1',11)");
        db.execSQL("INSERT INTO person(name,age) VALUES('Tom2',13)");
        db.execSQL("INSERT INTO person(name,age) VALUES('Tom3',15)");
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.i("TAG", "DBTestHelper onUpgrade() ");
    }
}

2、布局效果如下

3、DataBasesActivity类具体的实现方法

public class DataBasesActivity extends AppCompatActivity {
    private Button btn_create;
    private Button btn_update;
    private Button btn_delete;
    private Button btn_insert;
    private Button btn_query;
    private Button btn_update_record;
    private Button btn_test_transaction;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_data_bases);
        btn_create = findViewById(R.id.btn_create);
        btn_update = findViewById(R.id.btn_update);
        btn_delete = findViewById(R.id.btn_delete);
        btn_insert = findViewById(R.id.btn_insert);
        btn_query = findViewById(R.id.btn_query);
        btn_update_record = findViewById(R.id.btn_update_record);
        btn_test_transaction = findViewById(R.id.btn_test_transaction);
        btn_create.setOnClickListener(onClickListener);
        btn_update.setOnClickListener(onClickListener);
        btn_delete.setOnClickListener(onClickListener);
        btn_insert.setOnClickListener(onClickListener);
        btn_query.setOnClickListener(onClickListener);
        btn_update_record.setOnClickListener(onClickListener);
        btn_test_transaction.setOnClickListener(onClickListener);
    }
    private final View.OnClickListener onClickListener = new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            switch (v.getId()) {
                case R.id.btn_create://创建库
                    DBTestHelper dbTestHelper = new DBTestHelper(DataBasesActivity.this, 1);
                    //当执行完该方法后，才会执行DBTestHelper类中的 onCreate()方法：进行数据库表的创建与初始化
                    SQLiteDatabase database = dbTestHelper.getReadableDatabase();
                    Toast.makeText(DataBasesActivity.this, "创建数据库！", Toast.LENGTH_SHORT).show();
                    break;
                case R.id.btn_update: //更新库
                    DBTestHelper dbTestHelper1 = new DBTestHelper(DataBasesActivity.this, 2);
                    //执行该方法后，会执行数据库用于版本更新的方法onUpgrade()
                    SQLiteDatabase database1 = dbTestHelper1.getReadableDatabase();
                    Toast.makeText(DataBasesActivity.this, "更新数据库！", Toast.LENGTH_SHORT).show();
                    break;
                case R.id.btn_delete: //删除记录
                    deleteRecord();
                    break;
                case R.id.btn_insert://添加记录
                    insertRecord();
                    break;
                case R.id.btn_query: //查询记录
                    queryRecord();
                    break;
                case R.id.btn_update_record: //更新记录
                    updateRecord();
                    break;
                case R.id.btn_test_transaction: //测试事务
                    testTransaction();
                    break;
                default:
                    break;
            }
        }
    };
    /**
     * 测试事务处理
     * UPDATE age=16 FROM person WHERE _id = 1;
     * UPDATE age=17 FROM person WHERE _id = 3;
     * <p>
     * 一个功能中对数据库进行的多个操作：要么都成功，要么都失败！
     * <p>
     * 事务处理的3步：
     * 1.开启事务(获取连接后)
     * 2.设置事务成功(再全部正常执行)
     * 3.结束事务(finally中)
     */
    private void testTransaction() {
        SQLiteDatabase database = null;
        try {
            DBTestHelper dbTestHelper3 = new DBTestHelper(DataBasesActivity.this, 2);
            //得到连接
            database = dbTestHelper3.getReadableDatabase();
            //1.开启事务(获取连接后)
            database.beginTransaction();
            //执行update  UPDATE person SET name='Jack',age=21 WHERE id=4;
            ContentValues contentValues = new ContentValues();
            contentValues.put("age", 16);
            int updateCount = database.update("person", contentValues, "_id=?", new String[]{"1"});
            Log.i("TAG", "updateCount: " + updateCount);
            //出了异常
            boolean flag = true;
            if (flag) {
                throw new RuntimeException("出异常了！");
            }
            contentValues = new ContentValues();
            contentValues.put("age", 17);
            int updateCount2 = database.update("person", contentValues, "_id=?", new String[]{"3"});
            Log.i("TAG", "updateCount2: " + updateCount2);
            // 2.设置事务成功(再全部正常执行)
            database.setTransactionSuccessful();
        } catch (RuntimeException e) {
            e.printStackTrace();
        } finally {
            if (database != null) {
                //3.结束事务(finally中)
                database.endTransaction();
                database.close();
            }
        }
    }
    //查询记录
    private void queryRecord() {
        DBTestHelper dbTestHelper3 = new DBTestHelper(DataBasesActivity.this, 2);
        //得到连接
        SQLiteDatabase database = dbTestHelper3.getReadableDatabase();
//        Cursor cursor = database.query("person", null, "_id=?", new String[]{"3"}, null, null, null); //查询id为3的
        //查询所有的记录
        Cursor cursor = database.query("person", null, null, null, null, null, null);
        int count = cursor.getCount();
        while (cursor.moveToNext()) {
            int rowid = cursor.getInt(cursor.getColumnIndex("rowid"));
            int id = cursor.getInt(0);
            String name = cursor.getString(1);
            int age = cursor.getInt(2);
            Log.i("TAG", "id-" + id + "name-" + name + "age-" + age + "rowid: " + rowid);
        }
        cursor.close();
        database.close();
        Toast.makeText(this, "count=" + count, Toast.LENGTH_SHORT).show();
    }
    //删除记录
    private void deleteRecord() {
        DBTestHelper dbTestHelper3 = new DBTestHelper(DataBasesActivity.this, 2);
        //得到连接
        SQLiteDatabase database = dbTestHelper3.getReadableDatabase();
        //执行delete DELETE FROM person WHERE _id=2
        int deleteCount = database.delete("person", "_id=?", new String[]{"2"});
        database.close();
        Toast.makeText(this, "deleteCount=" + deleteCount, Toast.LENGTH_SHORT).show();
    }
    //更新记录
    private void updateRecord() {
        DBTestHelper dbTestHelper3 = new DBTestHelper(DataBasesActivity.this, 2);
        //得到连接
        SQLiteDatabase database = dbTestHelper3.getReadableDatabase();
        //执行update  UPDATE person SET name='Jack',age=21 WHERE id=4;
        ContentValues contentValues = new ContentValues();
        contentValues.put("name", "Jack");
        contentValues.put("age", 13);
        int updateCount = database.update("person", contentValues, "_id=?", new String[]{"3"});
        database.close();
        Toast.makeText(this, "updateCount=" + updateCount, Toast.LENGTH_SHORT).show();
    }
    //添加记录
    private void insertRecord() {
        DBTestHelper dbTestHelper3 = new DBTestHelper(DataBasesActivity.this, 2);
        //得到连接
        SQLiteDatabase database = dbTestHelper3.getReadableDatabase();
        //执行insert INSERT INTO person(name,age) VALUES('Tom',12);
        ContentValues contentValues = new ContentValues();
        contentValues.put("name", "Tom");
        contentValues.put("age", 12);
        long id = database.insert("person", null, contentValues);
        //关闭
        database.close();
        //提示
        Toast.makeText(this, "id=" + id, Toast.LENGTH_SHORT).show();
    }
}

Android SQLite数据库中基础的增删改查操作以及API的详解

热门文章

最新文章

相关课程

相关电子书

相关实验场景