SQLiteOpenHelper：数据库的创建、更新的操作对象
SQLiteDatabase：执行数据的增删改查的操作对象
SQLiteStatement：SQL执行的操作对象
继承系统的SQLiteOpenHelper，在onCreate和onUpgrade中实现数据库的创建以及更新
public class MyDbHelper extends SQLiteOpenHelper {
private static final String TAG = "MyDbHelper";
private static final String DB_NAME = "my.db";
private static final int DB_VERSION = 1;
public MyDbHelper(@Nullable Context context) {
super(context, DB_NAME, null, DB_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
Log.e(TAG, "onCreate");
// 创建数据库
MyDbConfig.dbCreate(db);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.e(TAG, "onUpgrade >> oldVersion:" + oldVersion + "; newVersion:" + newVersion);
// 更新数据库
MyDbConfig.dbUpgrade(db, oldVersion, newVersion);
}
}
数据库的创建和更新单独的放在一个类中，方便管理维护
public class MyDbConfig {
/**
* 数据库创建时调用
*
* @param db
*/
public static void dbCreate(SQLiteDatabase db) {
// 用户表
db.execSQL("CREATE TABLE IF NOT EXISTS " + MyDaoSample.TABLE_NAME
+ "("
+ "_id INTEGER PRIMARY KEY AUTOINCREMENT,"
+ MyDaoSample.USER_NAME + " VARCHAR(20) NOT NULL,"
+ MyDaoSample.AGE + " INTEGET,"
+ MyDaoSample.SEX + " VARCHAR(1)"
+ ")"
);
}
/**
* 数据库升级时调用
*
* @param db
* @param oldVersion
* @param newVersion
*/
public static void dbUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// 用户表
// 更新方法1，可以直接删除原表，然后调用onCreate重新创建新表
db.execSQL("DROP TABLE " + MyDaoSample.TABLE_NAME);
dbCreate(db);
// 更新方法2，可以在原表基础上添加字段等操作
// 添加字段
//db.execSQL("ALTER TABLE " + MyDaoSample.TABLE_NAME + " ADD category TEXT");
}
}
对数据库的操作使用单例进行管理，用于获取一个可读可写的SQLiteDatabase
public class MyDbManage {
private static MyDbManage myDbManage;
private final MyDbHelper myDbHelper;
private MyDbManage(Context context) {
myDbHelper = new MyDbHelper(context);
}
public static MyDbManage getInstance(Context context) {
if (myDbManage == null) {
synchronized (MyDbManage.class) {
if (myDbManage == null) {
myDbManage = new MyDbManage(context);
}
}
}
return myDbManage;
}
/**
* 获取一个可写的数据库
*
* @return SQLiteDatabase
*/
public SQLiteDatabase getWritableDatabase() {
return myDbHelper.getWritableDatabase();
}
}
针对每个实体，建议单独创建操作数据的DAO，更方便维护
public class MyDaoSample {
private static final String TAG = MyDaoSample.class.getSimpleName();
public static final String TABLE_NAME = "user";// 表名
public static final String USER_NAME = "username";
public static final String AGE = "age";
public static final String SEX = "sex";
private static MyDaoSample myDaoSample;
private final SQLiteDatabase mDb;
private MyDaoSample() {
MyDbManage dbManage = MyDbManage.getInstance(CoreApplication.mApp);
mDb = dbManage.getWritableDatabase();
}
public static MyDaoSample getInstance() {
if (myDaoSample == null) {
synchronized (MyDaoSample.class) {
if (myDaoSample == null) {
myDaoSample = new MyDaoSample();
}
}
}
return myDaoSample;
}
public void insert() {
ContentValues values = new ContentValues();
values.put(USER_NAME, "susu");
values.put(AGE, 18);
mDb.beginTransaction();
mDb.insert(TABLE_NAME, null, values);
mDb.setTransactionSuccessful();
mDb.endTransaction();
}
public void delete() {
String conditions = USER_NAME + "=?";
String[] args = {"susu"};
int delete = mDb.delete(TABLE_NAME, conditions, args);
Log.d(TAG, "delete:" + delete);
}
public void deleteAll() {
mDb.beginTransaction();
int delete = mDb.delete(TABLE_NAME, null, null);
Log.d(TAG, "delete:" + delete);
mDb.execSQL("UPDATE SQLITE_SEQUENCE SET SEQ=0 WHERE NAME='" + TABLE_NAME + "'");// 自增长ID设置为0
mDb.setTransactionSuccessful();
mDb.endTransaction();
}
public void update() {
ContentValues values = new ContentValues();
values.put(AGE, 20);
String conditions = USER_NAME + "=?";
String[] args = {"susu"};
int affectNum = mDb.update(TABLE_NAME, values, conditions, args);
Log.d(TAG, "update affectNum:" + affectNum);
}
public void query() {
Cursor cursor = mDb.query(TABLE_NAME, null,
null, null, null, null, null);
while (cursor.moveToNext()) {
@SuppressLint("Range")
int _id = cursor.getInt(cursor.getColumnIndex("_id"));
@SuppressLint("Range")
String username = cursor.getString(cursor.getColumnIndex(USER_NAME));
@SuppressLint("Range")
String age = cursor.getString(cursor.getColumnIndex(AGE));
Log.d(TAG, "query _id: + " + _id + "; username:" + username + "; age:" + age);
}
cursor.close();
}
}
插入：SQLiteStatement.executeInsert
更新、删除：SQLiteStatement.executeUpdateDelete
查询：SQLiteCursorDriver.query
1、预编译SQL语句，重复的操作使用SQLiteStatement
2、使用事务，做数据更新操作时提高性能
3、及时关闭Cursor
4、耗时异步化
特点：对象关系映射（ORM）模型，不需要写SQL语句，通过对对象的操作间接操作数据。
不涉及反射，靠的是代码辅助生成。
dependencies {
classpath 'org.greenrobot:greendao-gradle-plugin:3.3.0'
}
plugins {
id 'org.greenrobot.greendao'
}
greendao {
schemaVersion 2
}
dependencies {
api 'org.greenrobot:greendao:3.3.0'
}
@Entity
public class User {
@Id(autoincrement = true)
private Long uId;
private String name;
private String password;
// 省略get/set方法
}
public class DBOpenHelper extends DaoMaster.DevOpenHelper {
public DBOpenHelper(Context context, String name) {
super(context, name);
}
@Override
public void onCreate(SQLiteDatabase db) {
// 数据库创建
StandardDatabase database = new StandardDatabase(db);
DaoMaster.createAllTables(database, false);
}
@Override
public void onUpgrade(Database db, int oldVersion, int newVersion) {
// 数据库升级
super.onUpgrade(db, oldVersion, newVersion);
}
}
public class DBManager {
private final DaoMaster.DevOpenHelper devOpenHelper;
private static DBManager instance;
private static final String DB_NAME = "my.db";
private DBManager(Context context) {
devOpenHelper = new DBOpenHelper(context, DB_NAME);
}
public static DBManager getInstance(Context context) {
if (instance == null) {
synchronized (DBManager.class) {
if (instance == null) {
instance = new DBManager(context);
}
}
}
return instance;
}
/**
* 获取可写数据库
*
* @return
*/
public SQLiteDatabase getWritableDatabase() {
return devOpenHelper.getWritableDatabase();
}
/**
* 获取可写的会话层
*
* @return
*/
public DaoSession getWriteDaoSession() {
DaoMaster daoMaster = new DaoMaster(getWritableDatabase());
DaoSession daoSession = daoMaster.newSession(IdentityScopeType.None);
return daoSession;
}
}
public class UserModel {
private final UserDao userDao;
private UserModel() {
DaoSession daoSession = DBManager.getInstance().getWriteDaoSession();
userDao = daoSession.getUserDao();
}
private final static UserModel userModel = new UserModel();
public static UserModel getInstance() {
return userModel;
}
public void deleteUser(User user) {
userDao.delete(user);
}
public void deleteAllUser() {
userDao.deleteAll();
}
public void insertUser(User user) {
userDao.insert(user);
}
public void updateUser(User user) {
userDao.update(user);
}
public List queryAllUser() {
QueryBuilder qb = userDao.queryBuilder();
List list = qb.list();
return list;
}
public User queryUser(String name) {
QueryBuilder qb = userDao.queryBuilder();
qb.where(UserDao.Properties.Name.eq(name));
return qb.build().unique();
}
}
GreenDao访问层:
Dao、Entity、DaoSession、DaoMaster、Properties、QueryBuilder
提供 XXEntity 数据模型对象、数据模型对象的 Properties 用来做每个字段的快速访问，以及操作数据模型的 XXEntityDao
GreenDao中间层:
AbstractDao、AbstractDaoSession、AbstractDaoMaster、IdentityScope、Join、AbstractQuery、WhereCondition
数据操作者 XXEntityDao 的具体操作 AbstractDao
XXEntityDao 的管理者 AbstractDaoSession
DaoSession 封装过程的性能优化
GreenDao底层:
Database、DatabaseOpenHelper、DatabaseStatement
StandardDatbase 实现 Database 接口，内部代理 SQLiteDatabase
StandardDatabaseStatement 实现 DatabaseStatement，内部代理 SQLiteStatement
DatabaseOpenHelper 内部 SQLiteDatabase 改为 StandardDatabase 进行代理
主要体现在 DaoConfig 中，增删改查 SQL 的 预编译的 Statement 的缓存
每次数据库操作都使用了事务提高性能
DaoMaster、DaoSession、XXDao，这三个都会自动创建，不需自己编写
DaoMaster
DaoMaster 保存数据库对象 SQLiteDatabase 并管理特定模式的 DAO 类，有静态方法来创建表和删除它们
它的内部类OpenHelper和DevOpenHelper是SQLiteOpenHelper的实现
DaoSession
管理特定模式的所有可用DAO对象，可以使用getter获取DAO对象，还提供了一些通用的持久性方法，如实体的插入，加载，更新，刷新和删除
XXDao
引入依赖
implementation 'net.zetetic:android-database-sqlcipher:3.5.6'
修改DBManager中getWriteDatabase获取方式
devOpenHelper.getEncryptedWritableDb("123456");// 密码123456
public class DBOpenHelper extends DaoMaster.DevOpenHelper {
public DBOpenHelper(Context context, String name) {
super(context, name);
}
@Override
public void onCreate(SQLiteDatabase db) {
StandardDatabase database = new StandardDatabase(db);
DaoMaster.createAllTables(database, false);
}
@Override
public void onUpgrade(Database db, int oldVersion, int newVersion) {
MyMigrationHelper.migrate(db, new MyMigrationHelper.ReCreateAllTableListener() {
@Override
public void onCreateAllTables(Database db, boolean ifNotExists) {
DaoMaster.createAllTables(db, ifNotExists);
}
@Override
public void onDropAllTables(Database db, boolean ifExists) {
DaoMaster.dropAllTables(db, ifExists);
}
}, UserDao.class);
}
}
public class MyMigrationHelper {
public static boolean DEBUG = true;
private static final String TAG = MyMigrationHelper.class.getSimpleName();
private static final String SQLITE_MASTER = "sqlite_master";
private static final String SQLITE_TEMP_MASTER = "sqlite_temp_master";
private static WeakReference weakListener;
public static void migrate(SQLiteDatabase db, Class>... daoClasses) {
printLog("【The Old Database Version】" + db.getVersion());
Database database = new StandardDatabase(db);
migrate(database, daoClasses);
}
public static void migrate(SQLiteDatabase db, ReCreateAllTableListener listener, Class>... daoClasses) {
weakListener = new WeakReference<>(listener);
migrate(db, daoClasses);
}
public static void migrate(Database database, ReCreateAllTableListener listener, Class>... daoClasses) {
weakListener = new WeakReference<>(listener);
migrate(database, daoClasses);
}
public static void migrate(Database database, Class>... daoClasses) {
printLog("【Generate temp table】start");
generateTempTables(database, daoClasses);
printLog("【Generate temp table】complete");
ReCreateAllTableListener listener = null;
if (weakListener != null) {
listener = weakListener.get();
}
if (listener != null) {
listener.onDropAllTables(database, true);
printLog("【Drop all table by listener】");
listener.onCreateAllTables(database, false);
printLog("【Create all table by listener】");
} else {
dropAllTables(database, true, daoClasses);
createAllTables(database, false, daoClasses);
}
printLog("【Restore data】start");
restoreData(database, daoClasses);
printLog("【Restore data】complete");
}
/**
* 生成临时表
*
* @param db
* @param daoClasses
*/
private static void generateTempTables(Database db, Class>... daoClasses) {
for (int i = 0; i < daoClasses.length; i++) {
String tempTableName = null;
DaoConfig daoConfig = new DaoConfig(db, daoClasses[i]);
String tableName = daoConfig.tablename;
if (!isTableExists(db, false, tableName)) {
printLog("【New Table】" + tableName);
continue;
}
try {
tempTableName = daoConfig.tablename.concat("_TEMP");
StringBuilder dropTableStringBuilder = new StringBuilder();
dropTableStringBuilder.append("DROP TABLE IF EXISTS ").append(tempTableName).append(";");
db.execSQL(dropTableStringBuilder.toString());
StringBuilder insertTableStringBuilder = new StringBuilder();
insertTableStringBuilder.append("CREATE TEMPORARY TABLE ").append(tempTableName);
insertTableStringBuilder.append(" AS SELECT * FROM ").append(tableName).append(";");
db.execSQL(insertTableStringBuilder.toString());
printLog("【Table】" + tableName + "\n ---Columns-->" + getColumnsStr(daoConfig));
printLog("【Generate temp table】" + tempTableName);
} catch (SQLException e) {
Log.e(TAG, "【Failed to generate temp table】" + tempTableName, e);
}
}
}
private static boolean isTableExists(Database db, boolean isTemp, String tableName) {
if (db == null || TextUtils.isEmpty(tableName)) {
return false;
}
String dbName = isTemp ? SQLITE_TEMP_MASTER : SQLITE_MASTER;
String sql = "SELECT COUNT(*) FROM " + dbName + " WHERE type = ? AND name = ?";
Cursor cursor = null;
int count = 0;
try {
cursor = db.rawQuery(sql, new String[]{"table", tableName});
if (cursor == null || !cursor.moveToFirst()) {
return false;
}
count = cursor.getInt(0);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (cursor != null)
cursor.close();
}
return count > 0;
}
private static String getColumnsStr(DaoConfig daoConfig) {
if (daoConfig == null) {
return "no columns";
}
StringBuilder builder = new StringBuilder();
for (int i = 0; i < daoConfig.allColumns.length; i++) {
builder.append(daoConfig.allColumns[i]);
builder.append(",");
}
if (builder.length() > 0) {
builder.deleteCharAt(builder.length() - 1);
}
return builder.toString();
}
private static void dropAllTables(Database db, boolean ifExists, @NonNull Class>... daoClasses) {
reflectMethod(db, "dropTable", ifExists, daoClasses);
printLog("【Drop all table by reflect】");
}
private static void createAllTables(Database db, boolean ifNotExists, @NonNull Class>... daoClasses) {
reflectMethod(db, "createTable", ifNotExists, daoClasses);
printLog("【Create all table by reflect】");
}
/**
* dao class already define the sql exec method, so just invoke it
*/
private static void reflectMethod(Database db, String methodName, boolean isExists, @NonNull Class>... daoClasses) {
if (daoClasses.length < 1) {
return;
}
try {
for (Class cls : daoClasses) {
Method method = cls.getDeclaredMethod(methodName, Database.class, boolean.class);
method.invoke(null, db, isExists);
}
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
/**
* 从临时表中恢复数据
*
* @param db
* @param daoClasses
*/
private static void restoreData(Database db, Class>... daoClasses) {
for (int i = 0; i < daoClasses.length; i++) {
DaoConfig daoConfig = new DaoConfig(db, daoClasses[i]);
String tableName = daoConfig.tablename;
String tempTableName = daoConfig.tablename.concat("_TEMP");
if (!isTableExists(db, true, tempTableName)) {
continue;
}
try {
// get all columns from tempTable, take careful to use the columns list
List newTableInfos = TableInfo.getTableInfo(db, tableName);
List tempTableInfos = TableInfo.getTableInfo(db, tempTableName);
ArrayList selectColumns = new ArrayList<>(newTableInfos.size());
ArrayList intoColumns = new ArrayList<>(newTableInfos.size());
for (TableInfo tableInfo : tempTableInfos) {
if (newTableInfos.contains(tableInfo)) {
String column = '' + tableInfo.name + '';
intoColumns.add(column);
selectColumns.add(column);
}
}
// NOT NULL columns list
for (TableInfo tableInfo : newTableInfos) {
if (tableInfo.notnull && !tempTableInfos.contains(tableInfo)) {
String column = '' + tableInfo.name + '';
intoColumns.add(column);
String value;
if (tableInfo.dfltValue != null) {
value = "'" + tableInfo.dfltValue + "' AS ";
} else {
value = "'' AS ";
}
selectColumns.add(value + column);
}
}
if (intoColumns.size() != 0) {
StringBuilder insertTableStringBuilder = new StringBuilder();
insertTableStringBuilder.append("REPLACE INTO ").append(tableName).append(" (");
insertTableStringBuilder.append(TextUtils.join(",", intoColumns));
insertTableStringBuilder.append(") SELECT ");
insertTableStringBuilder.append(TextUtils.join(",", selectColumns));
insertTableStringBuilder.append(" FROM ").append(tempTableName).append(";");
db.execSQL(insertTableStringBuilder.toString());
printLog("【Restore data】 to " + tableName);
}
StringBuilder dropTableStringBuilder = new StringBuilder();
dropTableStringBuilder.append("DROP TABLE ").append(tempTableName);
db.execSQL(dropTableStringBuilder.toString());
printLog("【Drop temp table】" + tempTableName);
} catch (SQLException e) {
Log.e(TAG, "【Failed to restore data from temp table 】" + tempTableName, e);
}
}
}
private static void printLog(String info) {
if (DEBUG) {
Log.d(TAG, info);
}
}
public interface ReCreateAllTableListener {
void onCreateAllTables(Database db, boolean ifNotExists);
void onDropAllTables(Database db, boolean ifExists);
}
private static class TableInfo {
int cid;
String name;
String type;
boolean notnull;
String dfltValue;
boolean pk;
@Override
public boolean equals(Object o) {
return this == o
|| o != null
&& getClass() == o.getClass()
&& name.equals(((TableInfo) o).name);
}
@Override
public String toString() {
return "TableInfo{" +
"cid=" + cid +
", name='" + name + '\'' +
", type='" + type + '\'' +
", notnull=" + notnull +
", dfltValue='" + dfltValue + '\'' +
", pk=" + pk +
'}';
}
private static List getTableInfo(Database db, String tableName) {
String sql = "PRAGMA table_info(" + tableName + ")";// 查询表结构
printLog(sql);
Cursor cursor = db.rawQuery(sql, null);
if (cursor == null)
return new ArrayList<>();
TableInfo tableInfo;
List tableInfos = new ArrayList<>();
while (cursor.moveToNext()) {
tableInfo = new TableInfo();
tableInfo.cid = cursor.getInt(0);
tableInfo.name = cursor.getString(1);
tableInfo.type = cursor.getString(2);
tableInfo.notnull = cursor.getInt(3) == 1;
tableInfo.dfltValue = cursor.getString(4);
tableInfo.pk = cursor.getInt(5) == 1;
tableInfos.add(tableInfo);
printLog("getTableInfo >>> " + tableName + "：" + tableInfo);
}
cursor.close();
return tableInfos;
}
}
}
Original: https://blog.csdn.net/linglingchenchen/article/details/123632277
Author: lingchenjie_
Title: Android原生数据库SQLite和GreenDao的使用
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/815549/
转载文章受原作者版权保护。转载请注明原作者出处！