Java Code Examples for com.j256.ormlite.stmt.QueryBuilder#selectColumns()

/**
 * Build our query for Post objects that match a User.
 */
private PreparedQuery<Post> makePostsForUserQuery() throws SQLException {
	// build our inner query for UserPost objects
	QueryBuilder<UserPost, Integer> userPostQb = userPostDao.queryBuilder();
	// just select the post-id field
	userPostQb.selectColumns(UserPost.POST_ID_FIELD_NAME);
	SelectArg userSelectArg = new SelectArg();
	// you could also just pass in user1 here
	userPostQb.where().eq(UserPost.USER_ID_FIELD_NAME, userSelectArg);

	// build our outer query for Post objects
	QueryBuilder<Post, Integer> postQb = postDao.queryBuilder();
	// where the id matches in the post-id from the inner query
	postQb.where().in(Post.ID_FIELD_NAME, userPostQb);
	return postQb.prepare();
}
 

@Test
public void testRawResultsObjectMapper() throws Exception {
	Dao<Foo, Object> dao = createDao(Foo.class, true);
	Foo foo1 = new Foo();
	foo1.val = 12321;
	foo1.stringField = "fjpojefpwoewfjpewf";
	assertEquals(1, dao.create(foo1));
	Foo foo2 = new Foo();
	foo2.val = 754282321;
	foo2.stringField = "foewjfewpfjwe";
	assertEquals(1, dao.create(foo2));

	QueryBuilder<Foo, Object> qb = dao.queryBuilder();
	qb.selectColumns(Foo.ID_COLUMN_NAME, Foo.VAL_COLUMN_NAME, Foo.STRING_COLUMN_NAME);
	GenericRawResults<Foo> rawResults =
			dao.queryRaw(qb.prepareStatementString(), new DataType[] { DataType.INTEGER, DataType.INTEGER,
					DataType.STRING }, new FooObjectArrayMapper());
	List<Foo> results = rawResults.getResults();
	assertEquals(2, results.size());
	assertEquals(foo1.id, results.get(0).id);
	assertEquals(foo1.val, results.get(0).val);
	assertEquals(foo1.stringField, results.get(0).stringField);
	assertEquals(foo2.id, results.get(1).id);
	assertEquals(foo2.val, results.get(1).val);
	assertEquals(foo2.stringField, results.get(1).stringField);
}
 

@Test
public void testBasic() throws Exception {
	Dao<Foo, Object> dao = createDao(Foo.class, true);
	Foo foo1 = new Foo();
	foo1.val = 12321;
	assertEquals(1, dao.create(foo1));
	Foo foo2 = new Foo();
	foo2.val = 754282321;
	assertEquals(1, dao.create(foo2));

	QueryBuilder<Foo, Object> qb = dao.queryBuilder();
	qb.selectColumns(Foo.VAL_COLUMN_NAME);
	GenericRawResults<Integer> rawResults = dao.queryRaw(qb.prepareStatementString(), new IntMapper());
	List<Integer> results = rawResults.getResults();
	assertEquals(2, results.size());
	assertEquals(foo1.val, (int) results.get(0));
	assertEquals(foo2.val, (int) results.get(1));
}
 

@Test
public void testFooMapper() throws Exception {
	Dao<Foo, Object> dao = createDao(Foo.class, true);
	Foo foo1 = new Foo();
	foo1.val = 12321;
	assertEquals(1, dao.create(foo1));
	Foo foo2 = new Foo();
	foo2.val = 754282321;
	assertEquals(1, dao.create(foo2));

	QueryBuilder<Foo, Object> qb = dao.queryBuilder();
	qb.selectColumns(Foo.ID_COLUMN_NAME, Foo.VAL_COLUMN_NAME);
	GenericRawResults<Foo> rawResults = dao.queryRaw(qb.prepareStatementString(), new FooMapper());
	List<Foo> results = rawResults.getResults();
	assertEquals(2, results.size());
	assertEquals(foo1.id, results.get(0).id);
	assertEquals(foo1.val, results.get(0).val);
	assertEquals(foo2.id, results.get(1).id);
	assertEquals(foo2.val, results.get(1).val);
}
 

protected void getQueryIdsForOrg(MitroRequestContext context, int orgId, Set<Integer> userIds, Set<Integer> secretIds, Set<Integer> groupIds)
    throws MitroServletException, SQLException {
  // TODO: We need to create an API for orgs and clean this up.
  @SuppressWarnings("deprecation")
  AuthenticatedDB userDb = AuthenticatedDB.deprecatedNew(context.manager, context.requestor);
  if (!userDb.isOrganizationAdmin(orgId)) {
    throw new MitroServletException("Not org or no access");
  }

  RPC.GetOrganizationStateResponse out = GetOrganizationState.doOperation(context,  orgId);

  userIds.clear();

  // TODO: change GetOrganizationState query to return the user id column.
  if (!out.members.isEmpty()) {
    QueryBuilder<DBIdentity, Integer> query = context.manager.identityDao.queryBuilder();
    query.selectColumns(DBIdentity.ID_NAME);
    Where<DBIdentity, Integer> where = query.where();
    where.in(DBIdentity.NAME_FIELD_NAME, Manager.makeSelectArgsFromList(out.members));

    for (DBIdentity identity : query.query()) {
      userIds.add(identity.getId());
    }
  }

  secretIds.clear();
  secretIds.addAll(out.orgSecretsToPath.keySet());
  secretIds.addAll(out.orphanedSecretsToPath.keySet());

  groupIds.clear();
  groupIds.addAll(out.groups.keySet());
}
 

/**
 * Build our query for User objects that match a Post
 */
private PreparedQuery<User> makeUsersForPostQuery() throws SQLException {
	QueryBuilder<UserPost, Integer> userPostQb = userPostDao.queryBuilder();
	// this time selecting for the user-id field
	userPostQb.selectColumns(UserPost.USER_ID_FIELD_NAME);
	SelectArg postSelectArg = new SelectArg();
	userPostQb.where().eq(UserPost.POST_ID_FIELD_NAME, postSelectArg);

	// build our outer query
	QueryBuilder<User, Integer> userQb = userDao.queryBuilder();
	// where the user-id matches the inner query's user-id field
	userQb.where().in(Post.ID_FIELD_NAME, userPostQb);
	return userQb.prepare();
}
 

@Test
public void testInnerQueryCacheLookup() throws Exception {
	Dao<Foo, Object> fooDao = createDao(Foo.class, true);
	Dao<Outer, Object> outerDao = createDao(Outer.class, true);
	outerDao.setObjectCache(true);
	Dao<Inner, Object> innerDao = createDao(Inner.class, true);
	innerDao.setObjectCache(true);

	Foo foo = new Foo();
	assertEquals(1, fooDao.create(foo));

	Outer outer1 = new Outer();
	assertEquals(1, outerDao.create(outer1));
	Outer outer2 = new Outer();
	outer2.foreign = foo;
	assertEquals(1, outerDao.create(outer2));

	Inner inner = new Inner();
	inner.foreign = foo;
	assertEquals(1, innerDao.create(inner));

	QueryBuilder<Inner, Object> innerQb = innerDao.queryBuilder();
	innerQb.selectColumns(Inner.SOME_STRING_FIELD_NAME);
	QueryBuilder<Outer, Object> qb = outerDao.queryBuilder();
	List<Outer> results = qb.selectColumns(Outer.SOME_STRING_FIELD_NAME)
			.where()
			.in(Outer.SOME_STRING_FIELD_NAME, innerQb)
			.query();
	assertEquals(1, results.size());
}
 

@Test(expected = SQLException.class)
public void testTwoResultsInSubQuery() throws Exception {
	BaseDaoImpl<ForeignFoo, Integer> foreignDao =
			new BaseDaoImpl<ForeignFoo, Integer>(connectionSource, ForeignFoo.class) {
			};
	QueryBuilder<ForeignFoo, Integer> qbInner = foreignDao.queryBuilder();
	qbInner.selectColumns(ID_COLUMN_NAME);
	QueryBuilder<ForeignFoo, Integer> qbOuter = foreignDao.queryBuilder();
	qbInner.selectColumns(FOREIGN_COLUMN_NAME);

	Where<ForeignFoo, Integer> where = qbOuter.where();
	where.in(ID_COLUMN_NAME, qbInner);
	where.prepare();
}
 

@Test(expected = SQLException.class)
public void testResultColumnNoMatchWhere() throws Exception {
	BaseDaoImpl<ForeignFoo, Integer> foreignDao =
			new BaseDaoImpl<ForeignFoo, Integer>(connectionSource, ForeignFoo.class) {
			};
	QueryBuilder<ForeignFoo, Integer> qbInner = foreignDao.queryBuilder();
	qbInner.selectColumns(STRING_COLUMN_NAME);
	QueryBuilder<ForeignFoo, Integer> qbOuter = foreignDao.queryBuilder();
	Where<ForeignFoo, Integer> where = qbOuter.where();
	where.in(ID_COLUMN_NAME, qbInner);
	where.prepare();
}
 

@Test
public void testRawResultsMapper() throws Exception {
	Dao<Foo, Object> dao = createDao(Foo.class, true);
	Foo foo1 = new Foo();
	foo1.val = 12321;
	assertEquals(1, dao.create(foo1));
	Foo foo2 = new Foo();
	foo2.val = 754282321;
	assertEquals(1, dao.create(foo2));

	QueryBuilder<Foo, Object> qb = dao.queryBuilder();
	qb.selectColumns(Foo.ID_COLUMN_NAME, Foo.VAL_COLUMN_NAME);
	CloseableIterator<Foo> iterator = dao.iterator();
	try {
		DatabaseResults results = iterator.getRawResults();
		for (int count = 0; results.next(); count++) {
			Foo foo = dao.mapSelectStarRow(results);
			switch (count) {
				case 0 :
					assertEquals(foo1.id, foo.id);
					assertEquals(foo1.val, foo.val);
					break;
				case 1 :
					assertEquals(foo2.id, foo.id);
					assertEquals(foo2.val, foo.val);
					break;
				default :
					fail("Unknown entry in list");
			}
		}
	} finally {
		iterator.close();
	}
}
 

@Test
public void testInSubQuery() throws Exception {
	Dao<Foo, Integer> fooDao = createDao(Foo.class, true);
	Dao<Basic, String> basicDao = createDao(Basic.class, true);

	Basic basic1 = new Basic();
	String string1 = "ewpofjewgprgrg";
	basic1.id = string1;
	assertEquals(1, basicDao.create(basic1));
	Basic basic2 = new Basic();
	String string2 = "e2432423432wpofjewgprgrg";
	basic2.id = string2;
	assertEquals(1, basicDao.create(basic2));

	Foo foo1 = new Foo();
	foo1.stuff = basic1.id;
	Foo foo2 = new Foo();
	foo2.stuff = basic2.id;
	Foo foo3 = new Foo();
	String string3 = "neither of the others";
	foo3.stuff = string3;

	int num1 = 7;
	for (int i = 0; i < num1; i++) {
		assertEquals(1, fooDao.create(foo1));
	}
	int num2 = 17;
	for (int i = 0; i < num2; i++) {
		assertEquals(1, fooDao.create(foo2));
	}
	int num3 = 29;
	long maxId = 0;
	for (int i = 0; i < num3; i++) {
		assertEquals(1, fooDao.create(foo3));
		if (foo3.id > maxId) {
			maxId = foo3.id;
		}
	}

	QueryBuilder<Basic, String> bqb = basicDao.queryBuilder();
	bqb.selectColumns(Basic.ID_FIELD);

	// string1
	bqb.where().eq(Basic.ID_FIELD, string1);
	List<Foo> results = fooDao.query(fooDao.queryBuilder().where().in(Foo.STUFF_FIELD_NAME, bqb).prepare());
	assertEquals(num1, results.size());

	// string2
	bqb.where().eq(Basic.ID_FIELD, string2);
	results = fooDao.query(fooDao.queryBuilder().where().in(Foo.STUFF_FIELD_NAME, bqb).prepare());
	assertEquals(num2, results.size());

	// ! string2 with not().in(...)
	bqb.where().eq(Basic.ID_FIELD, string2);
	results = fooDao.query(fooDao.queryBuilder().where().not().in(Foo.STUFF_FIELD_NAME, bqb).prepare());
	assertEquals(num1 + num3, results.size());

	// string3 which there should be none
	bqb.where().eq(Basic.ID_FIELD, string3);
	results = fooDao.query(fooDao.queryBuilder().where().in(Foo.STUFF_FIELD_NAME, bqb).prepare());
	assertEquals(0, results.size());

	// string1 OR string2
	bqb.where().eq(Basic.ID_FIELD, string1).or().eq(Basic.ID_FIELD, string2);
	results = fooDao.query(fooDao.queryBuilder().where().in(Foo.STUFF_FIELD_NAME, bqb).prepare());
	assertEquals(num1 + num2, results.size());

	// all strings IN
	bqb.where().in(Basic.ID_FIELD, string1, string2, string3);
	results = fooDao.query(fooDao.queryBuilder().where().in(Foo.STUFF_FIELD_NAME, bqb).prepare());
	assertEquals(num1 + num2, results.size());

	// string1 AND string2 which there should be none
	bqb.where().eq(Basic.ID_FIELD, string1).and().eq(Basic.ID_FIELD, string2);
	results = fooDao.query(fooDao.queryBuilder().where().in(Foo.STUFF_FIELD_NAME, bqb).prepare());
	assertEquals(0, results.size());
}
 

@Test
public void testInSubQuerySelectArgs() throws Exception {
	Dao<Foo, Integer> fooDao = createDao(Foo.class, true);
	Dao<Basic, String> basicDao = createDao(Basic.class, true);

	Basic basic1 = new Basic();
	String string1 = "ewpofjewgprgrg";
	basic1.id = string1;
	assertEquals(1, basicDao.create(basic1));
	Basic basic2 = new Basic();
	String string2 = "e2432423432wpofjewgprgrg";
	basic2.id = string2;
	assertEquals(1, basicDao.create(basic2));

	Foo foo1 = new Foo();
	foo1.stuff = basic1.id;
	Foo foo2 = new Foo();
	foo2.stuff = basic2.id;

	int num1 = 7;
	for (int i = 0; i < num1; i++) {
		assertEquals(1, fooDao.create(foo1));
	}
	int num2 = 17;
	long maxId = 0;
	for (int i = 0; i < num2; i++) {
		assertEquals(1, fooDao.create(foo2));
		if (foo2.id > maxId) {
			maxId = foo2.id;
		}
	}
	// using seletArgs
	SelectArg arg1 = new SelectArg();
	SelectArg arg2 = new SelectArg();
	QueryBuilder<Basic, String> bqb = basicDao.queryBuilder();
	bqb.selectColumns(Basic.ID_FIELD);
	bqb.where().eq(Basic.ID_FIELD, arg1);
	PreparedQuery<Foo> preparedQuery =
			fooDao.queryBuilder().where().in(Foo.STUFF_FIELD_NAME, bqb).and().lt(Foo.ID_FIELD_NAME, arg2).prepare();
	arg1.setValue(string1);
	// this should get none
	arg2.setValue(0);
	List<Foo> results = fooDao.query(preparedQuery);
	assertEquals(0, results.size());
}
 

/**
 * 返回数据库中所有记录的指定列的值
 *
 * @param selectColumns 指定列名
 * @return
 */
public List<T> queryAllBySelectColumns(String[] selectColumns) throws SQLException {
    QueryBuilder queryBuilder = ormLiteDao.queryBuilder();
    queryBuilder.selectColumns(selectColumns);
    return queryBuilder.query();
}