Java SQLQuery类代码示例

import com.mysema.query.sql.SQLQuery; //导入依赖的package包/类
public void setBalance(int i, long amount, String description) {
    if (i < 0)
        return;

    QBalance b = QBalance.balance;
    QTransactions t = QTransactions.transactions;

    // We need to know the current amount to get the amount changed for the transaction log
    SQLQuery query = DatabaseManager.getInstance().getNewQuery();
    Long current = query.from(b).where(b.id.eq(i)).uniqueResult(b.amount);
    current = current == null ? 0 : current;

    try {
        SQLInsertClause balanceClause = DatabaseManager.getInstance().getInsertClause(b);
        SQLInsertClause transactionClause = DatabaseManager.getInstance().getInsertClause(t);

        transactionClause.columns(t.id, t.description, t.amount).values(i, description, amount - current).execute();
        balanceClause.columns(b.id, b.amount).values(i, amount).execute();
    } catch (QueryException e) {
        // Transaction insert won't fail, only balance will. when it does, update instead
        SQLUpdateClause clause = DatabaseManager.getInstance().getUpdateClause(b);

        clause.where(b.id.eq(i)).set(b.amount, amount).execute();
    }
}
 

import com.mysema.query.sql.SQLQuery; //导入依赖的package包/类
public List<Transaction> getTransactions(long limit) {
    QTransactions t = QTransactions.transactions;
    QPlayers p = QPlayers.players;

    SQLQuery query = DatabaseManager.getInstance().getNewQuery();

    List<Tuple> list = query
        .from(t)
        .join(p)
        .on(t.id.eq(p.id))
        .orderBy(t.datetime.asc())
        .limit(limit)
        .list(p.uuid, t.datetime, t.description, t.amount);

    return transformTuple(list);
}
 

import com.mysema.query.sql.SQLQuery; //导入依赖的package包/类
public List<Transaction> getTransaction(long lowEnd, long highEnd) {
    QTransactions t = QTransactions.transactions;
    QPlayers p = QPlayers.players;

    SQLQuery query = DatabaseManager.getInstance().getNewQuery();

    List<Tuple> list = query
        .from(t)
        .join(p)
        .on(p.id.eq(t.id))
        .orderBy(t.datetime.asc())
        .where(t.amount.between(lowEnd, highEnd))
        .orderBy(t.datetime.asc())
        .list(p.uuid, t.datetime, t.description, t.amount);

    return transformTuple(list);
}
 

import com.mysema.query.sql.SQLQuery; //导入依赖的package包/类
@Test
public void testMappingProjection()
        throws Exception {
	
	
	Connection conn = DbHelper.objectDb();
	
	SQLQuery sqlquery = new SQLQuery(conn, new HSQLDBTemplates());
	try {
		 List<DbObject> list =
				 sqlquery
						 .from(qTestDbObject)
						 .where(qTestDbObject.id.eq(1l))
						 .list(
						 		new QueryDslMappingProjection<DbObject>(
						 				DbObject.class,
										qTestDbObject.id, qTestDbObject.name, qTestDbObject.email,
										qTestDbObject.creationTime, qTestDbObject.typeName, qTestDbObject.typeOrdinal ));
		 
		 assertEquals(1, list.size());
		 DbHelper.assertDbObjectMapping(list.get(0));
	} finally {
		conn.close();
	}
}
 

import com.mysema.query.sql.SQLQuery; //导入依赖的package包/类
private long downloadMain(QueryConfigurer commonClause, QueryConfigurer orderByClause, Consumer consumer,
		Limiter limiter, Expression<?>... expressions) throws LimiterException, IOException {

	ResultSetExtractor<Long> extractor = new ExtractorResultSetExtractor(consumer, limiter);

	limiter.start();
	try {

		SQLQuery query = queryDslJdbcOperations.newSqlQuery();
		query = commonClause.configure(query);
		query = orderByClause.configure(query);

		return queryDslJdbcOperations.queryForObject(query, extractor, expressions);
	} catch (IllegalStateException ex) {
		throw (IOException) ex.getCause();
	} finally {
		limiter.stop();
	}
}
 

import com.mysema.query.sql.SQLQuery; //导入依赖的package包/类
@Test
public void testMyBatisToQuerydsl() {

	LocalDateTime ldt = LocalDateTime.now();
	LocalDate ld = LocalDate.now();
	LocalTime lt = LocalTime.now();

	ConversionTest record = new ConversionTest();
	record.setJodaDatetime(ldt);
	record.setJodaDate(ld);
	record.setJodaTime(lt);
	int count = mapper.insert(record);
	assertEquals(1, count);

	SQLQuery query = queryDslJdbcOperations.newSqlQuery();
	query.from(ct);
	Tuple tuple = queryDslJdbcOperations.queryForObject(query,
			new QTuple(ct.jodaDatetime, ct.jodaDate, ct.jodaTime));
	assertEquals(ldt, tuple.get(ct.jodaDatetime));
	assertEquals(ld, tuple.get(ct.jodaDate));
	assertEquals(lt, tuple.get(ct.jodaTime));
}
 

import com.mysema.query.sql.SQLQuery; //导入依赖的package包/类
@Test
public void testSaveAndLoad() {

	LocalDate orig = LocalDate.now();
	SQLInsertClause insert = queryFactory.insert(vd);
	insert.set(vd.dt, orig);
	long count = insert.execute();
	assertEquals(1L, count);

	SQLQuery query = queryFactory.from(vd);
	LocalDate result = query.uniqueResult(vd.dt);
	assertEquals(orig, result);

	assertEquals(
			Integer.valueOf(1),
			jdbcOperations.queryForObject("SELECT COUNT(*) FROM verify_datetime WHERE dt=?", Integer.class,
					orig.toString("yyyy-MM-dd")));
}
 

import com.mysema.query.sql.SQLQuery; //导入依赖的package包/类
@Test
public void testSaveAndLoad_plus1d() {

	LocalDate orig = LocalDate.now().plusDays(1);
	SQLInsertClause insert = queryFactory.insert(vd);
	insert.set(vd.dt, orig);
	long count = insert.execute();
	assertEquals(1L, count);

	SQLQuery query = queryFactory.from(vd);
	LocalDate result = query.uniqueResult(vd.dt);
	assertEquals(orig, result);

	assertEquals(
			Integer.valueOf(1),
			jdbcOperations.queryForObject("SELECT COUNT(*) FROM verify_datetime WHERE dt=?", Integer.class,
					orig.toString("yyyy-MM-dd")));
}
 

import com.mysema.query.sql.SQLQuery; //导入依赖的package包/类
@Override
public DirectedEventRow findEventById(String id) {
	int idInt = FastNumberUtils.parseIntWithCheck(id);
	WalkerTransactionPair100 results = null;
	QWalkerTransactionPair100 t = new QWalkerTransactionPair100("t");
	Connection conn;
	try {
		conn = getConnection();
		SQLQuery sq = from(conn, t).where(t.pairId.eq(idInt));
		results = sq.singleResult(t);
		conn.close();
	} catch (Exception e) {
		e.printStackTrace();
	}

	return funnel.from(results);
}
 

import com.mysema.query.sql.SQLQuery; //导入依赖的package包/类
@Override
public List<WalkerIdentifierType100> findByQuery(StringQuery q)
		throws Exception {
	List<WalkerIdentifierType100> results = new ArrayList<WalkerIdentifierType100>();

	QWalkerIdentifierType100 t = new QWalkerIdentifierType100("t");
	Connection conn;
	conn = getConnection();
	SQLQuery sq = buildQuery(q, t, conn);
	sq = setOffsetAndLimit(q, sq);
	if (sq != null) {
		sq = sq.orderBy(t.idtypeId.asc());
		results = sq.list(t);
	}
	conn.close();
	if (results != null) {
		logger.debug("Returning " + results.size() + " entries");
	}
	return results;
}
 

import com.mysema.query.sql.SQLQuery; //导入依赖的package包/类
/**
 * Uses the full bean, but optimized SQL
 * 
 * @param offset
 * @param limit
 * @return
 * @throws Exception
 */
private List<WalkerEntityref100> getAllFast(long offset, long limit)
		throws Exception {
	Connection conn = getConnection();
	QWalkerEntityref100 t = new QWalkerEntityref100("t");
	SQLQuery sq = from(conn, t);
	/*
	 * XXX: Note that we are treating offset and limit differently than
	 * usual. When using it in a between statement, we need to use
	 * limit+offset
	 */

	sq = sq.where(t.entityrefId.between(offset, limit + offset).and(
			t.idtypeId.notIn(idTypeDAO.getSkipTypes())));

	/*
	 * Note that we are not sorting the elements here, in order to speed
	 * things up.
	 */
	logger.debug("Query: " + sq.toString());
	List<WalkerEntityref100> results = sq.list(t);
	conn.close();

	return results;
}
 

import com.mysema.query.sql.SQLQuery; //导入依赖的package包/类
@Test
public void sec020101_カラムを絞って照会する() {

	QAuthor a = new QAuthor("a");
	SQLQuery query = queryDslJdbcOperations.newSqlQuery();
	query.from(a);
	List<Tuple> list = queryDslJdbcOperations.query(query, new QTuple(a.id,
			a.loginId, a.name));

	for (Tuple tuple : list) {
		Long valId = tuple.get(a.id);
		String valLoginId = tuple.get(a.loginId);
		String valName = tuple.get(a.name);
		LocalDateTime valUpdatedAt = tuple.get(a.updatedAt);
		LocalDateTime valCreatedAt = tuple.get(a.createdAt);
		Integer valLockVersion = tuple.get(a.lockVersion);
		Integer valDeletedFlg = tuple.get(a.deletedFlg);
		out.println(format(
				"{0}: loginId={1}, name={2}, updatedAt={3}, createdAt={4}, lockVersion={5}, deletedFlg={6}",
				valId, valLoginId, valName, valUpdatedAt, valCreatedAt,
				valLockVersion, valDeletedFlg));
	}
}
 

import com.mysema.query.sql.SQLQuery; //导入依赖的package包/类
@Test
public void testSaveAndLoad_plus1d() {

	LocalDateTime orig = LocalDateTime.now().plusDays(1);
	SQLInsertClause insert = queryFactory.insert(vd);
	insert.set(vd.dtm, orig);
	long count = insert.execute();
	assertEquals(1L, count);

	SQLQuery query = queryFactory.from(vd);
	LocalDateTime result = query.uniqueResult(vd.dtm);
	assertEquals(orig, result);

	assertEquals(
			Integer.valueOf(1),
			jdbcOperations.queryForObject("SELECT COUNT(*) FROM verify_datetime WHERE dtm=?", Integer.class,
					orig.toString("yyyy-MM-dd HH:mm:ss.SSS")));
}
 

import com.mysema.query.sql.SQLQuery; //导入依赖的package包/类
@Test
public void sec020501_CASE式を指定する_単純CASE式() {

	/* 抽出条件を組み立てる。 */
	QTodo a = new QTodo("a");
	SQLQuery query = queryDslJdbcOperations.newSqlQuery();
	query.from(a);

	/* CASE式を組立てる。 */
	Expression<String> doneDesc = a.doneFlg.when(0).then("未実施").when(1)
			.then("実施済").otherwise("不定");

	/* 取出すカラムとデータの取出し方を指定してクエリを発行する。 */
	List<Tuple> list = queryDslJdbcOperations.query(query, new QTuple(a.id,
			a.doneFlg, doneDesc));

	/* クエリの結果を表示する。 */
	for (Tuple tuple : list) {
		Long valId = tuple.get(a.id);
		Integer valDoneFlg = tuple.get(a.doneFlg);
		String valDoneDesc = tuple.get(doneDesc);
		out.println(format("{0}: doneFlg={1}, doneDesc(CASE)={2}", valId,
				valDoneFlg, valDoneDesc));
	}
}
 

import com.mysema.query.sql.SQLQuery; //导入依赖的package包/类
@Test
public void sec0501_GROUPBY() {

	QTodo a = new QTodo("a");
	SQLQuery query = queryDslJdbcOperations.newSqlQuery();
	query.from(a);
	query.groupBy(a.postedBy);
	List<Tuple> list = queryDslJdbcOperations.query(query, new QTuple(a.postedBy, a.id.count(), a.id.sum(),
			a.postedAt.min(), a.postedAt.max()));

	for (Tuple tuple : list) {
		String valPostedBy = tuple.get(a.postedBy);
		Long valCount = tuple.get(a.id.count());
		Long valSum = tuple.get(a.id.sum());
		LocalDateTime valMinPostedAt = tuple.get(a.postedAt.min());
		LocalDateTime valMaxPostedAt = tuple.get(a.postedAt.max());
		out.println(format("{0}: COUNT(id)={1}, SUM(id)={2}, MIN(postedAt)={3}, MAX(postedAt)={4}", valPostedBy,
				valCount, valSum, valMinPostedAt, valMaxPostedAt));
	}
}