安卓Sqlite数据库

最新推荐文章于 2026-01-01 11:18:41 发布

原创最新推荐文章于 2026-01-01 11:18:41 发布 · 501 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

Android

本文详细介绍了如何使用SQLite数据库进行创建、插入、删除、更新和查询操作，并提供了具体的代码示例，包括使用SQL语句和ContentValues方式。同时，文章还讲解了数据库事务处理的重要性及其实现方法。

参考视频学习:https://www.bilibili.com/video/BV1wt411n7pz
查看数据库的软件 SQLite Expert
从编译器右下角打开的模拟器中找到对应项目的数据库文件(sb,-shm,-wal三个文件都保存),然后添加到软件中去查看

软件运行结果
创建数据库

public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String TAG = "DatabaseHelper";
    Context mContext;

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_BOOK = "create table " + Constants.TABLE_NAME + "(" +
                "_id integer primary key autoincrement, " +
                "name text, " +
                "age integer," +
                "salary integer," +
                "phone integer," +
                "average integer," +
                "address text" +
                ")";
        db.execSQL(CREATE_BOOK);
        Toast.makeText(mContext, "finish", Toast.LENGTH_SHORT).show();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.e(TAG, "onUpgrade: 升级数据库...");
    }
}

一个常量类去管理

public class Constants {

    public static final String DATABASE_NAME = "test.db";

    public static final int DATABASE_VERSION = 1;

    public static final String TABLE_NAME = "employee";
    
}

Dao层去增删改查
Sql语言版

public class Dao {
   private DatabaseHelper mHelper;
   private static final String TAG = "Dao";

   public Dao(Context context) {
       //创建数据库
       mHelper = new DatabaseHelper(context);
   }

   public void insert() {
       SQLiteDatabase db = mHelper.getWritableDatabase();
       String sql = "insert into "+Constants.TABLE_NAME+"(_id,name,age,salary,phone,average,address) " +
               "values(?,?,?,?,?,?,?)";
       db.execSQL(sql,new Object[]{1,"Mike",60,111,110,55,"USA"});
       db.close();
   }

   public void delete() {
       SQLiteDatabase db = mHelper.getWritableDatabase();
       String sql = "delete from "+Constants.TABLE_NAME+" where age = 60";
       db.execSQL(sql);
       db.close();
   }

   public void update() {
       SQLiteDatabase db = mHelper.getWritableDatabase();
       String sql = "update "+Constants.TABLE_NAME+" set salary = 2 where age = 60";
       db.execSQL(sql);
       db.close();
   }

   public void query() {
       SQLiteDatabase db = mHelper.getWritableDatabase();
       String sql = "select * from "+Constants.TABLE_NAME;
       Cursor cursor = db.rawQuery(sql, null);
       while (cursor.moveToNext()) {
           //返回name是第几列
           int index = cursor.getColumnIndex("name");
           //返回name那一列的并转成字符串
           String name = cursor.getString(index);
           Log.e(TAG, "query: "+name );
       }
       cursor.close();
       db.close();
   }
}

使用API版本的

public class Dao {

    private DatabaseHelper mHelper;
    private static final String TAG = "Dao";

    public Dao(Context context) {
        //创建数据库
        mHelper = new DatabaseHelper(context);
    }

    public void insert() {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("_id",2);
        values.put("name","Tom");
        values.put("age",20);
        values.put("salary",100);
        values.put("phone",120);
        values.put("average",123);
        values.put("address","China");
        db.insert(Constants.TABLE_NAME,null,values);

        db.close();
    }

    public void delete() {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        db.delete(Constants.TABLE_NAME,"age=?",new String[]{"20"});
        db.close();
    }

    public void update() {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("age",50);
        db.update(Constants.TABLE_NAME,values,"name = ?",new String[]{"Tom"});
        db.close();
    }

    public void query() {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        Cursor cursor = db.query(Constants.TABLE_NAME, null, "age = 50", null, null, null, null);
        while (cursor.moveToNext()){
            Log.e(TAG, "name->"+cursor.getString(cursor.getColumnIndex("name")));
        }
        cursor.close();
        db.close();
    }
}

测试类测试(测试类在androidTest包下面那个)

@RunWith(AndroidJUnit4.class)
public class TestDatabase {
    Context context = InstrumentationRegistry.getInstrumentation().getTargetContext();

    @Test
    public void testCreate() {

    }
    @Test
    public void testInsert() {
        Dao dao = new Dao(context);
        dao.insert();
    }
    @Test
    public void testDelete() {
        Dao dao = new Dao(context);
        dao.delete();
    }
    @Test
    public void testUpdate() {
        Dao dao = new Dao(context);
        dao.update();
    }
    @Test
    public void testQuery() {
        Dao dao = new Dao(context);
        dao.query();
    }
}

数据库事务
两个特点
- 安全性
  比如公司有一百万,你的工资是一万,当工资给你发工资时,这时突然停电,公司账户少了一万,但你的账户没有增加钱,这就是安全性不够.
  安全性:当有异常时就会两个都不变,不发生异常时会正常变化

public class DataBaseHelper extends SQLiteOpenHelper {
    private static final String TABLE_NAME = "account";
    public DataBaseHelper(@Nullable Context context) {
        super(context, "test.db", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "create table "+TABLE_NAME+"(_id integer,name varchar,money integer)";
        db.execSQL(sql);
    }

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

    }
}

@RunWith(AndroidJUnit4.class)
public class TestDataBase {

    Context context = InstrumentationRegistry.getInstrumentation().getTargetContext();
    private DataBaseHelper mHelper = new DataBaseHelper(context);

    @Test
    public void testDataBase() {
        mHelper.getWritableDatabase();
    }

    @Test
    public void testInsert() {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql = "insert into account values (1,'company',1000000)";
        String sql1 = "insert into account values (2,'my_count',0)";
        db.execSQL(sql);
        db.execSQL(sql1);
    }

	//当有异常时就会两个都不变,不发生异常时会正常变化
    @Test
    public void testUpdate() {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        db.beginTransaction();
        try {
            String sql = "update account set money = 1000000 - 10000 where name = 'company'";
            //int i = 10 / 0;
            String sql1 = "update account set money = 10000 where name = 'my_count'";
            db.execSQL(sql);
            db.execSQL(sql1);
            db.setTransactionSuccessful();
        }catch (Exception e){
            throw new RuntimeException("发生异常!");
        }finally {
            db.endTransaction();
            db.close();
        }
    }

}

高效性
添加大量数据时,使用的时间长短
加上这两句,效率会提升几十倍,因为添加后数据一次性添加进去,而不是一条一条地添加
db.beginTransaction();
db.endTransaction();

@Test
    public void testInsert() {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        Long start = System.currentTimeMillis();
        db.beginTransaction();
        for(int i=0;i<3000;i++) {
            String sql = "insert into account values (1,'company',1000000)";
            db.execSQL(sql);
        }
        db.endTransaction();
        Log.e(TAG, "time -> " + (System.currentTimeMillis() - start) );
    }

升级数据库后的数据处理,以下是kotlin写法

override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        Log.e(TAG, "onUpgrade: $oldVersion $newVersion")
        //添加了一个新的表
        if (oldVersion <= 1) {
            db.execSQL(createCategory)
        }
        //添加了一个新的列
        if (oldVersion <= 2){
            db.execSQL("alter table Book add column category_id integer")
        }
    }