JOOQ学习

2020.09.04 默认分类 0 评

jOOQ系列教程
https://jooq.diamondfsd.com/learn/section-1-how-to-start.html

范围查询：

生成正确数据的sql查询是

select created_on from queue_token where created_on between '2015-07-16' and '2015-07-17' and token_queue_id=1;
我写的等效jooq查询在下面,但没有给出所需的结果

create.select().from(com.database.tables.QueueToken.QUEUE_TOKEN)

               .where(com.database.tables.QueueToken.QUEUE_TOKEN.TOKEN_QUEUE_ID.equal(1))
                .and(com.database.tables.QueueToken.QUEUE_TOKEN.CREATED_ON.between(new Timestamp(fromDate.getTime())).and(new Timestamp(toDate.getTime())))
                .fetch();

国内Jooq的文档实在太少了，需要用到时间查询的时候居然找不到任何资料。自己整理了一下几种用法。

查询在某一天的记录
public List findList(LocalDate date){

return create
       .selectFrom(Record)
       .where(Record.createDate
               .between(date.atStartOfDay(), date.plusDays(1).atStartOfDay())
       )
       .fetch()?.into(Record.class)

}
date的格式是“2017-03-09“，这里createDate的时间格式是LocalDateTime，所以我将date转换成LocalDateTime。解析出来的sql语句是这样的'record'.'create_date' between '2017-03-09T00:00' and '2017-03-10T00:00'。

查询从某一天到某一天的记录
public List findList(LocalDate date){

return create
       .selectFrom(Record)
       .where(Record.createDate
               .cast(LocalDate.class)
               .between(date, date.plusDays(1))
       )
       .fetch()?.into(Record.class)

}
这里我将createDate转换成了LocalDate，再查询在“2017-03-09”和“2017-03-10”之间的记录。这里返回的结果是包含“03-09”到“03-10”两天的。

查询在某一天之前的记录（不包括作参数的那天）

将between(date, date.plusDays(1)) 改成 lessThan(date)

查询在某一天之后的记录（不包括作参数的那天）

将between(date, date.plusDays(1)) 改成 greaterThan(date)

查询在某一天之前的记录（包括作参数的那天）

将between(date, date.plusDays(1)) 改成 lessOrEqual(date)

查询在某一天之后的记录（包括作参数的那天）

将between(date, date.plusDays(1)) 改成 greaterOrEqual(date)

PS：直接在.where(Record.createDate.cast(LocalDate.class).eq(date)条件语句中判断equals，理论上是可行的。然而我这边生成的sql放在数据库中能够返回数据的，但是程序中返回空集。如有大神知道原因，麻烦告知，谢谢。

使用SQL文

public List selectMoreBCByAcDate(String acDate) {

String sql = " (SELECT\r\n" + 
        "    sm.acDate AS as_date,\r\n" + 
        "    sm.account_id,\r\n" + 
        "    sm.accountStatementId AS as_id,\r\n" + 
        "    sum(\r\n" + 
        "    IF\r\n" + 
        "    ( sm.type = '1', sm.income, 0 )) AS total_advert_amount,\r\n" + 
        "    sum(\r\n" + 
        "    IF\r\n" + 
        "    ( sm.type = '2', sm.income, 0 )) AS total_film_amount,\r\n" + 
        "    sum(\r\n" + 
        "    IF\r\n" + 
        "    ( sm.type = '3', sm.income, 0 )) AS total_sm_amount,\r\n" + 
        "    sum(\r\n" + 
        "    IF\r\n" + 
        "    ( sm.type = '0', sm.income, 0 )) AS total_offline_amount,\r\n" + 
        "    sum( sm.income ) AS total_amount,\r\n" + 
        "    sm.type,\r\n" + 
        // 矢链账单生成时，状态默认为：1-未结算
        "    '1' AS state \r\n" +

// " sm.state * 1 AS state rn" +

        " FROM\r\n" + 
        "    t_ac_statement_month sm \r\n" + 
        " WHERE\r\n" + 
        "    sm.acDate = '" + acDate + "' \r\n" + 
        " GROUP BY\r\n" + 
        "    sm.account_id,\r\n" + 
        "    sm.type,\r\n" + 
        "    sm.state) a" ;
        
sql = sql.replaceAll("\\r\\n", "");

// 注意SQL文，需要是一个子表才可以。【（SELECT * FROM ）别名】的形式传入

List<BCStatementMonth> list = create.selectFrom(sql).fetchInto(BCStatementMonth.class);

if (CollectionUtils.isEmpty(list)) {
    return null;
}

return list;

}

使用SUM方法，合计金额

/**

获取一条月对账单记录
@param accountId 智能账户ID
@param state 结算状态(1.对账中 2.已对账 3.结算中 4.已结算)
@param acDate 核算日期（YYYYMM）
@return 一条月对账单记录

*/
public SelectAccountACSM selectSingleByKeys(String accountId, String state, String acDate) {


SelectQuery<?> sq = create.selectQuery();

sq.addSelect(TAcStatementMonth.T_AC_STATEMENT_MONTH.INCOME.sum().as("income"), 
        TAcStatementMonth.T_AC_STATEMENT_MONTH.ACCOUNT_ID, 
        TAcStatementMonth.T_AC_STATEMENT_MONTH.ACCOUNT_NAME,
        TAcStatementMonth.T_AC_STATEMENT_MONTH.ACDATE,
        TAcStatementMonth.T_AC_STATEMENT_MONTH.STATE);

sq.addFrom(TAcStatementMonth.T_AC_STATEMENT_MONTH);
sq.addConditions(TAcStatementMonth.T_AC_STATEMENT_MONTH.ACCOUNT_ID.eq(accountId)
               .and(TAcStatementMonth.T_AC_STATEMENT_MONTH.STATE.eq(Byte.valueOf(state)))
               .and(TAcStatementMonth.T_AC_STATEMENT_MONTH.ACDATE.eq(acDate)));

List<SelectAccountACSM> list = sq.fetchInto(SelectAccountACSM.class);

if (CollectionUtils.isEmpty(list)) {
    return null;
} else {
    // 没有账户信息
    if (StringUtils.isBlank(list.get(0).getAccountId())) {
        return null;
    }
    else
    {
        return list.get(0);
    }
}

}

杰客网络

杰客网络个人博客