GreenDao 3.3.0 增删改查的使用（三）
16lz
2021-01-26
GreenDao 3.3.0 基本使用与入门（一）
GreenDao 3.3.0 多表关联使用（二）
GreenDao 3.3.0 增删改查的使用（三）
GreenDao 的数据操作处理是继承自AbstractDao这个抽象类
最基本的函数：
- insert(T) delete(T) update(T) save(T) insertOrReplace(T) loadAll()
- 其中 save(T) 和insertOrReplace(T) 方法比较特殊既能进行插入操作也能执行修改操作
- insertInTx(T…) deleteInTx(T…) updateInTx(T…) saveInTx(T…) insertOrReplaceInTx(T…)
- refresh(T) 刷新数据
注意：在这函数后面有InTx(T…),是表示开启事物进行多个数据的操作
/** * Inserts the given entities in the database using a transaction. * * @param entities The entities to insert. */ public void insertInTx(T... entities) { insertInTx(Arrays.asList(entities), isEntityUpdateable()); } /** * Inserts the given entities in the database using a transaction. The given entities will become tracked if the PK * is set. * * @param entities The entities to insert. * @param setPrimaryKey if true, the PKs of the given will be set after the insert; pass false to improve * performance. */ public void insertInTx(Iterable entities, boolean setPrimaryKey) { DatabaseStatement stmt = statements.getInsertStatement(); executeInsertInTx(stmt, entities, setPrimaryKey); } private void executeInsertInTx(DatabaseStatement stmt, Iterable entities, boolean setPrimaryKey) { db.beginTransaction(); try { synchronized (stmt) { if (identityScope != null) { identityScope.lock(); } try { if (isStandardSQLite) { SQLiteStatement rawStmt = (SQLiteStatement) stmt.getRawStatement(); for (T entity : entities) { bindValues(rawStmt, entity); if (setPrimaryKey) { long rowId = rawStmt.executeInsert(); updateKeyAfterInsertAndAttach(entity, rowId, false); } else { rawStmt.execute(); } } } else { for (T entity : entities) { bindValues(stmt, entity); if (setPrimaryKey) { long rowId = stmt.executeInsert(); updateKeyAfterInsertAndAttach(entity, rowId, false); } else { stmt.execute(); } } } } finally { if (identityScope != null) { identityScope.unlock(); } } } db.setTransactionSuccessful(); } finally { db.endTransaction(); } } save 和 insertOrReplace 区别
save it will be inserted (key is null) or updated (key is not null)
有key的对象执行更新，无key的执行插入
当对象有key但并不在数据库时会执行失败.适用于保存本地列表
/** * "Saves" an entity to the database: depending on the existence of the key property, it will be inserted * (key is null) or updated (key is not null). * * This is similar to {@link #insertOrReplace(Object)}, but may be more efficient, because if a key is present, * it does not have to query if that key already exists. */ public void save(T entity) { if (hasKey(entity)) { update(entity); } else { insert(entity); } }
insertOrReplace
传入的对象在数据库中，有则更新无则插入,源码显示带有事物和线程
推荐同步数据库时使用该方法
/** * Insert an entity into the table associated with a concrete DAO. * * @return row ID of newly inserted entity */ public long insertOrReplace(T entity) { return executeInsert(entity, statements.getInsertOrReplaceStatement(), true); } private long executeInsert(T entity, DatabaseStatement stmt, boolean setKeyAndAttach) { long rowId; if (db.isDbLockedByCurrentThread()) { rowId = insertInsideTx(entity, stmt); } else { // Do TX to acquire a connection before locking the stmt to avoid deadlocks db.beginTransaction(); try { rowId = insertInsideTx(entity, stmt); db.setTransactionSuccessful(); } finally { db.endTransaction(); } } if (setKeyAndAttach) { updateKeyAfterInsertAndAttach(entity, rowId, true); } return rowId; }QueryBuilder 构建查询语
支持函数查询条件 distinct、 where 、whereOr 、or 、and、 OrderAsc/Desc、 join、 limit/offset(分页两个合用) 等具体查看QueryBuilder API
构建对象 CursorQuery buildCursor()、List list()、 T unique()、 T uniqueOrThrow()、 DeleteQuery buildDelete() 等
Property 配置where 条件判断
- eq()：==
- noteq()：!= ,"<>?"
- gt()： >
- lt()：<
- ge：>=
- le:<=
- like()：包含
- between：俩者之间
- in：在某个值内
- notIn：不在某个值内
- isNull: is null
- isNotNull: is not null
分页查询
- limit(int): 限制查询的数量;
- offset(int): 每次返回的数量; offset不能单独使用;
查询与LazyList类
Query ： Query类表示一个查询能够执行很多次;而当通过QueryBuilder的任何查询方法（eg:list()）来获取查询结果时，querybuilder都会 在其内部创建Query来执行查询语句的;如果执行多次查询应该使用Query对象; 如果只想获取一个结果时可以使用Query(or QueryBuilder)中的unique()方法;LazyList : 可以通过以下方法获取查询结果集合；list() 缓存查询结果;list()类型一般为ArrayListlistLazy() 懒查询,只有当调用list()中的实体对象时才会执行查询操作并且只缓存第一次被查询的结果，需要关闭listlazyUncached() 懒查询,只有当调用list()中的实体对象时才会执行查询操作并且不缓存;listIterator() 对查询结果进行遍历，不缓存，需要关闭;后面三个类是LazyList中的方法，LazyList为了执行不同的缓存策略其内部持有数据库的cursor对象；一般情况下这三个方法执行完毕后会自动关闭cursor；但是防止在还没有执行完查询结果时，对象被终结cursor还是无法被关闭的情况发生，需要手动关闭close();getDaoSession/getDatabase 开启事物处理
getDatabase 开启
Database database = getDaoSession().getMessageEntityDao().getDatabase(); Cursor cursor = null; ArrayList dataList = new ArrayList(); try { database.beginTransaction(); cursor = database.rawQuery(sql, null); while (cursor.moveToNext()) { MessageEntity data = createMessageEntity(cursor); dataList.add(data); } database.setTransactionSuccessful(); } catch (Exception e) { LogUtil.e(TAG, "QueryMessageEntity " + e); } finally { if (database != null) database.endTransaction(); if (null != cursor) { cursor.close(); } } getDaoSession 开启
/** * Calls the given Callable inside a database transaction and returns the result of the Callable. If you don't * except a result, consider runInTx. */ public V callInTx(Callable callable) throws Exception { db.beginTransaction(); try { V result = callable.call(); db.setTransactionSuccessful(); return result; } finally { db.endTransaction(); } } callInTx 事物处理 执行查询操作
/** * @param type -1：设备账户 ，0：app主账户，1：app一般账户 * @return */ public synchronized ArrayList getUserData(final int type) { ArrayList userList = null; try { userList = (ArrayList) getDaoSession().callInTx(new Callable() { @Override public List call() { return getDaoSession().getAccountInformationDao().queryBuilder() .where(AccountInformationDao.Properties.UserType.eq(type)) .build() .list(); } }); } catch (Exception e) { LogUtil.e(TAG, "getUserData " + e); } return userList; }
getDaoSession runInTx 开启线程处理
* Run the given Runnable inside a database transaction. If you except a result, consider callInTx. */ public void runInTx(Runnable runnable) { db.beginTransaction(); try { runnable.run(); db.setTransactionSuccessful(); } finally { db.endTransaction(); } }insertOrReplace 新增或修改数据
/** * 增加或修改用户信息 * * @param * @return */ public synchronized boolean setUserData(AccountInformation entity) { try { AccountInformationDao dao = getDaoSession().getAccountInformationDao(); AccountInformation user = dao.queryBuilder() .where(AccountInformationDao.Properties.UserId.eq(entity.getUserId())) .build() .unique(); if (user != null) { entity.setId(user.getId()); } dao.insertOrReplace(entity); dao.refresh(entity); return true; } catch (Exception e) { LogUtil.e(TAG, "setUserData " + e); return false; } }QueryBuilder 切换条件执行查询语句
public synchronized ArrayList QueryUrlInfomationList(final int type, final int commandType, final String url, final long recordId, final int isUpload, final long createTime) { ArrayList dataList = null; try { dataList = (ArrayList) getDaoSession().callInTx(new Callable>() { @Override public List call() throws Exception { UrlInformationDao dao = getDaoSession().getUrlInformationDao(); QueryBuilder queryBuilder = dao.queryBuilder(); if (type == 1) { queryBuilder.where(UrlInformationDao.Properties.CommentType.eq(commandType)); } else if (type == 5) { queryBuilder.where(UrlInformationDao.Properties.CommentType.eq(commandType), UrlInformationDao.Properties.CreateTime.eq(createTime) ); } else if (type == 6) { /* sql = "select * from " + Provider.UrlInformation.TABLE_NAME+ " where " + Provider.UrlInformation.commentType + " = '"+ commandType + "'" + " and "+ Provider.UrlInformation.url +"<>'' and '" +url +"' LIKE '%'||"+Provider.UrlInformation.url + "||'%'";*/ queryBuilder.where(UrlInformationDao.Properties.CommentType.eq(commandType), UrlInformationDao.Properties.Url.like("%" + url + "%")); } return queryBuilder.build().list(); } }); } catch (Exception e) { LogUtil.e(TAG, "QueryUrlInfomationList " + e); } return dataList; 执行SQL语句
rawQuery queryRawCreate execSQL
public long queryUserMaxId() { long id=0; try { UsersDao userDao = getDaoSession().getUsersDao(); String sql = "select max("+UsersDao.Properties.Id.columnName+") "+UsersDao.Properties.Id.columnName+" from " + UsersDao.TABLENAME; sql = "select max(_id) as maxId"+" from " + UsersDao.TABLENAME; // sql="select * from "+UserDao.TABLENAME; Cursor cursor = userDao.getDatabase().rawQuery(sql, null); if (cursor.moveToNext()){//moveToNext moveToLast //id = cursor.getLong(cursor.getColumnIndex(UserDao.Properties.Id.columnName)); id = cursor.getLong(cursor.getColumnIndex("maxId")); Log.d(TAG,"queryUserMaxId cursor users id="+id); } cursor.close(); return id; } catch (Exception e) { e.printStackTrace(); Log.d(TAG,"queryUserMaxId cursor Exception"+e); } return 0;}QueryBuilder buildDelete 带事物删除
适用于数据量比较大批量删除
/** * Builds a reusable query object for deletion (Query objects can be executed more efficiently than creating a * QueryBuilder for each execution. */ public DeleteQuery buildDelete() { if (!joins.isEmpty()) { throw new DaoException("JOINs are not supported for DELETE queries"); } String tablename = dao.getTablename(); String baseSql = SqlUtils.createSqlDelete(tablename, null); StringBuilder builder = new StringBuilder(baseSql); // tablePrefix gets replaced by table name below. Don't use tableName here because it causes trouble when // table name ends with tablePrefix. appendJoinsAndWheres(builder, tablePrefix); String sql = builder.toString(); // Remove table aliases, not supported for DELETE queries. // TODO(?): don't create table aliases in the first place. sql = sql.replace(tablePrefix + ".\"", '"' + tablename + "\".\""); checkLog(sql); return DeleteQuery.create(dao, sql, values.toArray()); } /** * 删除所有家长端用户 * * @param * @return */ public synchronized boolean deleteUserByType() { try { getDaoSession().getAccountInformationDao().queryBuilder() .where(AccountInformationDao.Properties.UserType.gt(-1)) .buildDelete().executeDeleteWithoutDetachingEntities(); return true; } catch (Exception e) { LogUtil.e(TAG, "deleteUserByType " + e); return false; } }Query SQL 查询
QueryBuilder>build()
Query对象一旦生成就能多次被使用，你也可以为下一次查询增加查询条件
Query query = userDao.queryBuilder().where( new StringCondition("_ID IN " + "(SELECT USER_ID FROM USER WHERE READ_ID = 0)")).build();//分组查询Query query = userDao.queryRawCreate( ", GROUP G WHERE G.NAME=? AND T.GROUP_ID=G._ID", "admin");Query query = userDao.queryBuilder().where( userDao.Properties.IsRead.gt(0), userDao.Properties.DistinctName.eq("yes")).build();List userFirst = query.list(); query.setParameter(0, 1);query.setParameter(1, "no");List userSecond = query.list();自定义SQL 拼接多个查询语句
/** * 聊天消息查询 * * @param type 1： 根据messaId查询，2：查询所有消息，3：群消息根据时间查询（大于），4：群消息根据时间查询（小于）,5：私聊消息根据时间查询（大于）， * 6：私聊消息根据时间查询（小于）,7：当前消息根据时间查询（大于），6：当前消息根据时间查询（小于） * @param messageId 消息Id 传私人Id为私聊消息，传群Id,为群聊消息 * @param time 时间 * @param order 只能传 DESC(由大到小)和ASC(由小到大) * @param size 查询数据的数量 * @return * @userId userId：自己用户id * @groupId 私聊方Id或群id */ public synchronized ArrayList QueryMessageList(int type, long messageId, long userId, long reuserid, String time, int size, String order) { String sql = null; Database database = getDaoSession().getMessageEntityDao().getDatabase(); if (type == 1) { sql = "select * from " + MessageEntityDao.TABLENAME + " where " + MessageEntityDao.Properties.MessageId.columnName + " = '" + messageId + "'"; } else if (type == 2) { sql = "select * from " + MessageEntityDao.TABLENAME; } else if (type == 3) { sql = "select * from " + MessageEntityDao.TABLENAME + " where " + MessageEntityDao.Properties.ReuserId.columnName + " = '" + reuserid + "'" + " and " + MessageEntityDao.Properties.CreateTime.columnName + " > '" + time + "'" + " order by " + MessageEntityDao.Properties.CreateTime.columnName + " " + order + " limit '" + size + "' offset '" + 0 + "'"; } else if (type == 4) { sql = "select * from " + MessageEntityDao.TABLENAME + " where " + MessageEntityDao.Properties.ReuserId.columnName + " = '" + reuserid + "'" + " and " + MessageEntityDao.Properties.CreateTime.columnName + " < '" + time + "'" + " order by " + MessageEntityDao.Properties.CreateTime.columnName + " " + order + " limit '" + size + "' offset '" + 0 + "'"; } else if (type == 5) { sql = "select * from '" + MessageEntityDao.TABLENAME + "'" + " where " + " ( " + " ( " + MessageEntityDao.Properties.UserId.columnName + " = '" + reuserid + "' and " + MessageEntityDao.Properties.ReuserId.columnName + " = '" + userId + "' ) " + " or " + " ( " + MessageEntityDao.Properties.UserId.columnName + " = '" + userId + "' and " + MessageEntityDao.Properties.ReuserId.columnName + " = '" + reuserid + "' ) " + " ) " + " and " + MessageEntityDao.Properties.CreateTime.columnName + " > '" + time + "'" + " order by " + MessageEntityDao.Properties.CreateTime.columnName + " " + order + " limit '" + size + "' offset '" + 0 + "'"; } else if (type == 6) { sql = "select * from '" + MessageEntityDao.TABLENAME + "'" + " where " + " ( " + " ( " + MessageEntityDao.Properties.UserId.columnName + " = '" + reuserid + "' and " + MessageEntityDao.Properties.ReuserId.columnName + " = '" + userId + "' ) " + " or " + " ( " + MessageEntityDao.Properties.UserId.columnName + " = '" + userId + "' and " + MessageEntityDao.Properties.ReuserId.columnName + " = '" + reuserid + "' ) " + " ) " + " and " + MessageEntityDao.Properties.CreateTime.columnName + " < '" + time + "'" + " order by " + MessageEntityDao.Properties.CreateTime.columnName + " " + order + " limit '" + size + "' offset '" + 0 + "'"; } else if (type == 7) { sql = "select * from '" + MessageEntityDao.TABLENAME + "'" + " where " + MessageEntityDao.Properties.CreateTime.columnName + " > '" + time + "'" + " order by " + MessageEntityDao.Properties.CreateTime.columnName + " " + order + " limit '" + size + "' offset '" + 0 + "'"; } else if (type == 8) { sql = "select * from messagelist t where EXISTS ( select 1 from messagelist GROUP BY distinctName HAVING MAX(_id) = t._id)" + " and " + MessageEntityDao.Properties.CreateTime.columnName + " < '" + time + "'" + " order by " + MessageEntityDao.Properties.CreateTime.columnName + " " + order + " limit '" + size + "' offset '" + 0 + "'"; //+Provider.MessageEntity.commenType + " = '" + comment + "'" //SELECT * FROM test_table t WHERE EXISTS ( SELECT 1 FROM test_table GROUP BY uname HAVING MAX(id) = t.id ) } LogUtil.d(TAG, "QueryMessageEntity ,sql:" + sql); Cursor cursor = null; ArrayList dataList = new ArrayList(); try { database.beginTransaction(); cursor = database.rawQuery(sql, null); while (cursor.moveToNext()) { MessageEntity data = createMessageEntity(cursor); dataList.add(data); } database.setTransactionSuccessful(); } catch (Exception e) { LogUtil.e(TAG, "QueryMessageEntity " + e); } finally { if (database != null) database.endTransaction(); if (null != cursor) { cursor.close(); } } return dataList; } /** * 创建消息实体 * * @param cursor * @return */ public MessageEntity createMessageEntity(Cursor cursor) { MessageEntity data = new MessageEntity(); data.setChatType(cursor.getInt(cursor.getColumnIndex(MessageEntityDao.Properties.ChatType.columnName))); data.setMessageId(cursor.getLong(cursor.getColumnIndex(MessageEntityDao.Properties.MessageId.columnName))); data.setMessageType(cursor.getInt(cursor.getColumnIndex(MessageEntityDao.Properties.MessageType.columnName))); data.setUserId(cursor.getLong(cursor.getColumnIndex(MessageEntityDao.Properties.UserId.columnName))); data.setUserName(cursor.getString(cursor.getColumnIndex(MessageEntityDao.Properties.UserName.columnName))); data.setUserPic(cursor.getString(cursor.getColumnIndex(MessageEntityDao.Properties.UserPic.columnName))); data.setReuserId(cursor.getLong(cursor.getColumnIndex(MessageEntityDao.Properties.ReuserId.columnName))); data.setReuserName(cursor.getString(cursor.getColumnIndex(MessageEntityDao.Properties.ReuserName.columnName))); data.setReuserPic(cursor.getString(cursor.getColumnIndex(MessageEntityDao.Properties.ReuserPic.columnName))); data.setMcontent(cursor.getString(cursor.getColumnIndex(MessageEntityDao.Properties.Mcontent.columnName))); data.setCreateTime(cursor.getString(cursor.getColumnIndex(MessageEntityDao.Properties.CreateTime.columnName))); data.setIsRead(cursor.getInt(cursor.getColumnIndex(MessageEntityDao.Properties.IsRead.columnName))); data.setIsSend(cursor.getInt(cursor.getColumnIndex(MessageEntityDao.Properties.IsSend.columnName))); data.setMessageSize(cursor.getInt(cursor.getColumnIndex(MessageEntityDao.Properties.MessageSize.columnName))); data.setSubCode(cursor.getInt(cursor.getColumnIndex(MessageEntityDao.Properties.SubCode.columnName))); data.setSendType(cursor.getInt(cursor.getColumnIndex(MessageEntityDao.Properties.SendType.columnName))); data.setReceType(cursor.getInt(cursor.getColumnIndex(MessageEntityDao.Properties.ReceType.columnName))); data.setSubGroup(cursor.getInt(cursor.getColumnIndex(MessageEntityDao.Properties.SubGroup.columnName))); data.setLocalityContent(cursor.getString(cursor.getColumnIndex(MessageEntityDao.Properties.LocalityContent.columnName))); data.setDistinctName(cursor.getString(cursor.getColumnIndex(MessageEntityDao.Properties.DistinctName.columnName))); return data; }更多相关文章
- 【ndk】直接使用ndk提供的arm-linux-androideabi-gcc编译android
- 学习ContentProvider---之一：查询数据库
- 提高代码质量－工具篇
- Android(安卓)6.0 通话记录生成保存和读取显示
- EventBus使用教程
- android selector 背景选择器的使用， button （未点击，点击，选中保持
- flutter 持久化存储-----数据库sqflite
- Android入门:深入学习理解 Handler HandlerThread AsyncQueryHan
- android编译源码+在源码中build工程方法(紧支持jdk1.5)