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<T> entities, boolean setPrimaryKey) {DatabaseStatement stmt = statements.getInsertStatement();executeInsertInTx(stmt, entities, setPrimaryKey);}private void executeInsertInTx(DatabaseStatement stmt, Iterable<T> 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).* <p>* 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 deadlocksdb.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<MessageEntity> dataList = new ArrayList<MessageEntity>();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> V callInTx(Callable<V> 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<AccountInformation> getUserData(final int type) {ArrayList<AccountInformation> userList = null;try {userList = (ArrayList<AccountInformation>) getDaoSession().callInTx(new Callable<List<AccountInformation>>() {@Overridepublic List<AccountInformation> 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<UrlInformation> QueryUrlInfomationList(final int type, final int commandType, final String url, final long recordId, final int isUpload, final long createTime) {ArrayList<UrlInformation> dataList = null;try {dataList = (ArrayList<UrlInformation>) getDaoSession().callInTx(new Callable<List<UrlInformation>>() {@Overridepublic List<UrlInformation> call() throws Exception {UrlInformationDao dao = getDaoSession().getUrlInformationDao();QueryBuilder<UrlInformation> 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<T> 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<MessageEntity> 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<MessageEntity> dataList = new ArrayList<MessageEntity>();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;}