Java Code Examples for com.j256.ormlite.stmt.UpdateBuilder#updateColumnValue()

@Test
public void testPrepareStatementUpdateValueExpression() throws Exception {
	Dao<Foo, Integer> fooDao = createDao(Foo.class, true);
	Foo foo = new Foo();
	foo.val = 123213;
	assertEquals(1, fooDao.create(foo));

	QueryBuilder<Foo, Integer> stmtb = fooDao.queryBuilder();
	stmtb.where().eq(Foo.ID_FIELD_NAME, foo.id);
	List<Foo> results = fooDao.query(stmtb.prepare());
	assertEquals(1, results.size());

	UpdateBuilder<Foo, Integer> updateb = fooDao.updateBuilder();
	String stuff = "deopdjq";
	updateb.updateColumnValue(Foo.STUFF_FIELD_NAME, stuff);
	StringBuilder sb = new StringBuilder();
	updateb.escapeColumnName(sb, Foo.VAL_FIELD_NAME);
	sb.append("+ 1");
	updateb.updateColumnExpression(Foo.VAL_FIELD_NAME, sb.toString());
	assertEquals(1, fooDao.update(updateb.prepare()));

	results = fooDao.queryForAll();
	assertEquals(1, results.size());
	assertEquals(stuff, results.get(0).stuff);
	assertEquals(foo.val + 1, results.get(0).val);
}
 

@Test
public void testUpdateTwoDates() throws Exception {
	Dao<TwoDates, Integer> dao = createDao(TwoDates.class, true);

	TwoDates foo = new TwoDates();
	long now = System.currentTimeMillis();
	Date date1 = new Date(now);
	Date date2 = new Date(now + 1000);
	foo.date1 = date1;
	foo.date2 = date2;

	assertEquals(1, dao.create(foo));

	UpdateBuilder<TwoDates, Integer> ub = dao.updateBuilder();
	ub.updateColumnValue(TwoDates.FIELD_NAME_DATE1, date2);
	ub.updateColumnValue(TwoDates.FIELD_NAME_DATE2, date1);
	dao.update(ub.prepare());
}
 

@Test
public void testRefresh() throws Exception {
	Dao<Foo, Integer> dao = createDao(Foo.class, true);
	Foo foo = new Foo();
	int equal1 = 11312331;
	foo.equal = equal1;
	assertEquals(1, dao.create(foo));
	int equal2 = 312312;

	assertNotNull(dao.queryForId(foo.id));

	UpdateBuilder<Foo, Integer> updateBuilder = dao.updateBuilder();
	updateBuilder.updateColumnValue(Foo.EQUAL_COLUMN_NAME, equal2);
	updateBuilder.where().eq(Foo.ID_COLUMN_NAME, foo.id);
	assertEquals(1, dao.update(updateBuilder.prepare()));

	assertEquals(equal1, foo.equal);
	assertEquals(1, dao.refresh(foo));
	assertEquals(equal2, foo.equal);
}
 

@Test
public void testSerializable() throws Exception {
	Dao<TestSerial, Integer> dao = createDao(TestSerial.class, true);
	TestSerial foo = new TestSerial();
	String stuff = "hjrjpe";
	foo.stuff = stuff;
	assertEquals(1, dao.create(foo));

	TestSerial result = dao.queryForId(foo.id);
	assertNotNull(result);
	assertEquals(stuff, result.stuff);

	UpdateBuilder<TestSerial, Integer> up = dao.updateBuilder();
	String newStuff = "165445654";
	up.updateColumnValue("stuff", newStuff);
	assertEquals(1, dao.update(up.prepare()));

	result = dao.queryForId(foo.id);
	assertNotNull(result);
	assertEquals(newStuff, result.stuff);
}
 

@Test
public void testBasic() throws Exception {
	Dao<Foo, Integer> dao = createDao(Foo.class, true);
	Foo foo = new Foo();
	int val = 66654654;
	foo.val = val;
	assertEquals(1, dao.create(foo));

	Foo result = dao.queryForId(foo.id);
	assertNotNull(result);
	assertEquals(val, result.val);

	UpdateBuilder<Foo, Integer> up = dao.updateBuilder();
	int newVal = 165445654;
	up.updateColumnValue(Foo.VAL_COLUMN_NAME, newVal);
	assertEquals(1, dao.update(up.prepare()));

	result = dao.queryForId(foo.id);
	assertNotNull(result);
	assertEquals(newVal, result.val);
}
 

/**
 * 如果更新记录字段值为null则忽略不更新
 *
 * @param t
 * @return
 */
private int updateIfValueNotNull(T t) throws SQLException, IllegalAccessException {
    UpdateBuilder updateBuilder = ormLiteDao.updateBuilder();
    Map<String, Object> map = getFieldsIfValueNotNull(t);
    if (map.isEmpty()) {
        throw new SQLException("all field value is null.");
    }
    if (map.get("id") == null) {
        throw new SQLException("id is null.");
    }
    updateBuilder.where().idEq(map.get("id"));
    for (Map.Entry<String, Object> entry : map.entrySet()) {
        if (entry.getKey().equals("id")) {
            continue;
        }
        updateBuilder.updateColumnValue(entry.getKey(), entry.getValue());
    }
    return updateBuilder.update();
}
 

@Override
public boolean updateTagText(ExtentTag tag, String text) throws MaeDBException {
    try {
        UpdateBuilder<ExtentTag, String> updateBuilder = eTagDao.updateBuilder();
        updateBuilder.where().eq(TAB_TAG_COL_TID, tag.getId());
        updateBuilder.updateColumnValue(TAB_ETAG_COL_TEXT,  text);
        if (updateBuilder.update() == 1) {
            setAnnotationChanged(true);
            eTagDao.refresh(tag);
            return true;
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return false;

}
 

/**
 * 把没有用户的数据更新为当前用户的信息
 * 
 * @param userName
 */
public int updateRecordToNewUser(String userName)
{
	UpdateBuilder<GameRanking, ?> ub = getDao().updateBuilder();
	try
	{
		ub.updateColumnValue("user_name", userName);
		ub.updateColumnValue("nickname", userName);
		ub.where().isNull("user_name").or().eq("user_name", "");

		return ub.update();
	}
	catch (SQLException e)
	{
		e.printStackTrace();
	}

	return -1;
}
 

/**
 * 更新某个用户的本地头像
 *
 * @param phone    该用户手机号
 * @param headPath 头像
 * @return 更新成功后返回表中对应的行数，失败代表不存在该phone的数据
 */
public int updateUserLocalHead(String phone, String headPath)
{
    int lineNum = -1;
    try
    {
        UpdateBuilder<UserBean, Integer> updateBuilder = getDao().updateBuilder();
        updateBuilder.updateColumnValue(UserDbConfig.LOCAL_HEAD, headPath);
        updateBuilder.where().eq(UserDbConfig.PHONE, phone);
        lineNum = getDao().update(updateBuilder.prepare());
    } catch (SQLException e)
    {
        KLog.e(TAG + " UserDao.updateUserLocalHead fail : " + e.toString());
    }
    return lineNum;
}
 

/**
 * 将某个手机号的用户标记为已注册
 *
 * @param phone 手机号
 * @return 更新成功后返回表中对应的行数，失败代表不存在该phone的数据
 */
public int updateUserAsRegisted(String phone)
{
    int lineNum = -1;
    try
    {
        UpdateBuilder<UserBean, Integer> updateBuilder = getDao().updateBuilder();
        updateBuilder.updateColumnValue(UserDbConfig.IS_REGIST, true);
        updateBuilder.where().eq(UserDbConfig.PHONE, phone);
        lineNum = getDao().update(updateBuilder.prepare());
    } catch (SQLException e)
    {
        KLog.e(TAG + " UserDao.updateUserAsRegisted fail : " + e.toString());
    }
    return lineNum;
}
 

@Test
public void testUpdateTwoNullsInSeperateStatements() throws Exception {
	Dao<Foo, Integer> dao = createDao(Foo.class, true);
	UpdateBuilder<Foo, Integer> ub = dao.updateBuilder();
	ub.updateColumnValue(Foo.STRING_COLUMN_NAME, null);
	dao.update(ub.prepare());
	ub = dao.updateBuilder();
	ub.updateColumnValue(Foo.EQUAL_COLUMN_NAME, null);
	dao.update(ub.prepare());
}
 

@Test
public void testUpdateBuilderSerializable() throws Exception {
	Dao<SerializedUpdate, Integer> dao = createDao(SerializedUpdate.class, true);
	SerializedUpdate foo = new SerializedUpdate();
	SerializedField serialized1 = new SerializedField("wow");
	foo.serialized = serialized1;
	assertEquals(1, dao.create(foo));

	SerializedUpdate result = dao.queryForId(foo.id);
	assertNotNull(result);
	assertNotNull(result.serialized);
	assertEquals(serialized1.foo, result.serialized.foo);

	// update with dao.update
	SerializedField serialized2 = new SerializedField("zip");
	foo.serialized = serialized2;
	assertEquals(1, dao.update(foo));

	result = dao.queryForId(foo.id);
	assertNotNull(result);
	assertNotNull(result.serialized);
	assertEquals(serialized2.foo, result.serialized.foo);

	// update with UpdateBuilder
	SerializedField serialized3 = new SerializedField("crack");
	UpdateBuilder<SerializedUpdate, Integer> ub = dao.updateBuilder();
	ub.updateColumnValue(SerializedUpdate.SERIALIZED_FIELD_NAME, serialized3);
	ub.where().idEq(foo.id);
	assertEquals(1, ub.update());

	result = dao.queryForId(foo.id);
	assertNotNull(result);
	assertNotNull(result.serialized);
	assertEquals(serialized3.foo, result.serialized.foo);
}
 

@Test
public void testPrepareStatementUpdateValueWhere() throws Exception {
	Dao<Foo, Integer> fooDao = createDao(Foo.class, true);
	Foo foo1 = new Foo();
	foo1.val = 78582351;
	assertEquals(1, fooDao.create(foo1));
	Foo foo2 = new Foo();
	String stuff = "eopqjdepodje";
	foo2.stuff = stuff;
	foo2.val = 123344131;
	assertEquals(1, fooDao.create(foo2));

	UpdateBuilder<Foo, Integer> updateb = fooDao.updateBuilder();
	String newStuff = "deopdjq";
	updateb.updateColumnValue(Foo.STUFF_FIELD_NAME, newStuff);
	StringBuilder sb = new StringBuilder();
	updateb.escapeColumnName(sb, Foo.VAL_FIELD_NAME);
	sb.append("+ 1");
	updateb.updateColumnExpression(Foo.VAL_FIELD_NAME, sb.toString());
	updateb.where().eq(Foo.ID_FIELD_NAME, foo2.id);
	assertEquals(1, fooDao.update(updateb.prepare()));

	List<Foo> results = fooDao.queryForAll();
	assertEquals(2, results.size());
	Foo foo = results.get(0);
	assertEquals(foo1.id, foo.id);
	assertEquals(foo1.val, foo.val);
	assertNull(foo.stuff);
	foo = results.get(1);
	assertEquals(foo2.id, foo.id);
	assertEquals(foo2.val + 1, foo.val);
	assertEquals(newStuff, foo.stuff);
}
 

private void resetDownloadStatusAndDlmId(long dlm_id) {
	try {
		UpdateBuilder<Video, String> q = getVideoDao().updateBuilder();
		q.where().eq("dlm_id", dlm_id);
		q.updateColumnValue("dlm_id", 0);
		q.updateColumnValue("download_status", Video.DL_STATUS_NOT_STARTED);
		q.update();
	} catch (SQLException e) {
		e.printStackTrace();
	}
}
 

/**
	 * Update a video's download_status in the database.
	 * 
	 * Proxy to videoDao.update, but will not modify any fields but download_status.
	 * Also updates the downloaded_video_count of all Topics in the video's parent 
	 * hierarchy.
	 * 
	 * @param video The video to update.
	 */
	public void updateDownloadStatus(Video video, int was, int is) {
		Log.d(LOG_TAG, "updateDownloadStatus");
		try {
			Dao<Video, String> videoDao = getVideoDao();
			UpdateBuilder<Video, String> u = videoDao.updateBuilder();
			u.updateColumnValue("download_status", is);
			u.where().eq("youtube_id", video.getYoutube_id());
			videoDao.update(u.prepare());
			
			// DEBUG
//			QueryBuilder<Video, String> q = videoDao.queryBuilder();
//			q.where().eq("readable_id", video.getReadable_id());
//			Video v = videoDao.queryForFirst(q.prepare());
//			Log.d(LOG_TAG, String.format("updated dl status of %s from %d to %d", video.getTitle(), was, v.getDownload_status()));
			
//			Dao<Topic, String> topicDao = getTopicDao();
//			UpdateBuilder<Topic, String> ub = topicDao.updateBuilder();
//			ub.updateColumnExpression("downloaded_video_count", "downloaded_video_count + 1");
//			Topic p = video.getParentTopic();
//			while (p != null) {
//				topicDao.refresh(p);
//				Log.d(LOG_TAG, String.format("updating parent topic %s. Value was %d...", p.getTitle(), p.getDownloaded_video_count()));
//				ub.where().idEq(p.getId());
//				PreparedUpdate<Topic> pu = ub.prepare();
//				topicDao.update(pu);
//				
//				// DEBUG
////				p = topicDao.queryForId(p.getId());
////				Log.d(LOG_TAG, String.format("updated to %d.", p.getDownloaded_video_count()));
//				
//				p = p.getParentTopic();
//			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
 

/**
 * 将所有未读通知设置为已读
 */
public void setAllNotifyRead()
{
    try
    {
        UpdateBuilder<InviteBean, Integer> updateBuilder = getDao().updateBuilder();
        updateBuilder.where().eq(InviteDbConfig.READ, false);
        updateBuilder.updateColumnValue(InviteDbConfig.READ, true);
        getDao().update(updateBuilder.prepare());
    } catch (SQLException e)
    {
        KLog.e(TAG + "InviteDao.setAllNotifyRead fail:" + e.toString());
    }
}
 

@Test
public void testUpdateTwoNulls() throws Exception {
	Dao<Foo, Integer> dao = createDao(Foo.class, true);
	UpdateBuilder<Foo, Integer> ub = dao.updateBuilder();
	ub.updateColumnValue(Foo.STRING_COLUMN_NAME, null);
	ub.updateColumnValue(Foo.EQUAL_COLUMN_NAME, null);
	dao.update(ub.prepare());
}
 

@Test(expected = SQLException.class)
public void testUpdatePreparedThrow() throws Exception {
	Dao<Foo, Integer> dao = createDao(Foo.class, true);
	Foo foo = new Foo();
	assertEquals(1, dao.create(foo));
	DatabaseConnection conn = connectionSource.getReadWriteConnection(FOO_TABLE_NAME);
	try {
		conn.close();
		UpdateBuilder<Foo, Integer> ub = dao.updateBuilder();
		ub.updateColumnValue(Foo.EQUAL_COLUMN_NAME, 1);
		dao.update(ub.prepare());
	} finally {
		connectionSource.releaseConnection(conn);
	}
}
 

/**
 * 更新某个用户的昵称
 *
 * @param userBean 更新数据
 * @return 更新成功后返回表中对应的行数，失败代表不存在该phone的数据
 */
public int updateUserName(UserBean userBean)
{
    int lineNum = -1;
    try
    {
        UpdateBuilder<UserBean, Integer> updateBuilder = getDao().updateBuilder();
        String nameApp = userBean.getNameApp();
        String nameSystem = userBean.getNameSystem();
        //新数据对象中如果app备注名不为空，则所有姓名相关字段都需要更新
        if (StringUtil.isNotEmpty(nameApp))
        {
            updateBuilder.updateColumnValue(UserDbConfig.NAME_APP, userBean.getNameApp());
            updateBuilder.updateColumnValue(UserDbConfig.NAME_SYSTEM, userBean.getNameSystem());
            updateBuilder.updateColumnValue(UserDbConfig.DISPLAY_NAME, userBean.getDisplayName());
            updateBuilder.updateColumnValue(UserDbConfig.FIRST_CHAR, userBean.getFirstChar());
            updateBuilder.updateColumnValue(UserDbConfig.SIMPLE_SPELL, userBean.getSimpleSpell());
            updateBuilder.updateColumnValue(UserDbConfig.FULL_SPELL, userBean.getFullSpell());
        }
        //新数据对象中系统备注名不为空，需要对比老数据
        else if (StringUtil.isNotEmpty(nameSystem))
        {
            UserBean localBean = queryUserByPhone(userBean.getPhone());
            if (localBean != null)
            {
                //老数据中app备注名不为空，则只需更新系统备注名
                if (StringUtil.isNotEmpty(localBean.getNameApp()))
                {
                    updateBuilder.updateColumnValue(UserDbConfig.NAME_SYSTEM, userBean.getNameSystem());
                }
                //除了app备注名其余都更新
                else
                {
                    updateBuilder.updateColumnValue(UserDbConfig.NAME_SYSTEM, userBean.getNameSystem());
                    updateBuilder.updateColumnValue(UserDbConfig.DISPLAY_NAME, userBean.getDisplayName());
                    updateBuilder.updateColumnValue(UserDbConfig.FIRST_CHAR, userBean.getFirstChar());
                    updateBuilder.updateColumnValue(UserDbConfig.SIMPLE_SPELL, userBean.getSimpleSpell());
                    updateBuilder.updateColumnValue(UserDbConfig.FULL_SPELL, userBean.getFullSpell());
                }
            }
        }

        updateBuilder.where().eq(UserDbConfig.PHONE, userBean.getPhone());
        lineNum = getDao().update(updateBuilder.prepare());
    } catch (SQLException e)
    {
        KLog.e(TAG + " UserDao.updateUserName fail : " + e.toString());
    }
    return lineNum;
}
 

/**
 * Remove metadata references (by updating the field to null) with foreign
 * keys to the metadata parent id
 * 
 * @param parentId
 *            parent id
 * @return updated count
 * @throws SQLException
 *             upon failure
 */
public int removeMetadataParent(long parentId) throws SQLException {

	UpdateBuilder<MetadataReference, Void> ub = updateBuilder();
	ub.updateColumnValue(MetadataReference.COLUMN_PARENT_ID, null);

	ub.where().eq(MetadataReference.COLUMN_PARENT_ID, parentId);

	PreparedUpdate<MetadataReference> update = ub.prepare();
	int updated = update(update);

	return updated;
}