// jOOQ臨時(shí)表
Table temp = DSL.table(DSL.select(BS_SALESMAN.SALESID, DSL.count().as("count"))
                .from(Tables.BS_SALESMAN)
                .where(BS_SALESMAN.SALESID.eq(1))
                .groupBy(BS_SALESMAN.MOBILE))
                .as("temp");
// 或者使用val
val user = BS_USER.as("user");

// 去重查詢
DSL.selectDistinct().from();

// 創(chuàng)建字段
Field<String> field = DSL.field("hello world");

// 創(chuàng)建一個(gè)常量值
Param<String> helloWorld = DSL.val("hello world");

// 獲取表記錄對象
Table<Record> table = DSL.table("bs_user");

// 為真的條件:1=1
Condition condition = DSL.trueCondition();

// 驗(yàn)證DSL.exists()方法
public void exits() throws IOException, SQLException {
        DSLContext dslContext = getdslContext();
        Condition condition = DSL.exists(DSL.select(DSL.field("c_username")));
        Table<Record> table = DSL.table("t_login");
        SelectQuery<Record> selectQuery = dslContext.selectQuery(table);
        selectQuery.addConditions(condition);
        Result<Record> fetch = selectQuery.fetch();
        for (Object aResult : fetch) {
            Record record = (Record) aResult;
            System.out.println(record);
            System.out.println(record.getValue("c_username"));
        }
}

// jOOQ強(qiáng)制索引
DSL.select().from(BS_USER.forceIndex("idx_userid")).where(BS_USER.USERID.eq(1));

// 日期格式化
/**
 * 時(shí)間字段轉(zhuǎn)字符串
 *
 * @param field  時(shí)間類字段
 * @param format 抓換格式
 * @return 字符型字段
 */
 public static Field<String> dateFormat(Field<?> field, String format) {
    return DSL.field("date_format({0}, {1})", SQLDataType.VARCHAR,
            field, DSL.inline(format));
 }

/**
 * 字符串轉(zhuǎn)時(shí)間戳字段
 *
 * @param time   時(shí)間
 * @param format 格式
 * @return 時(shí)間戳字段
 */
public static Field<Timestamp> stringFormat(String time, String format) {
    return DSL.field("date_format({0}, {1})", SQLDataType.TIMESTAMP,
            time, DSL.inline(format));
}

// dao查詢
DSL.select(dateFormat(BS_USER.CREATETM, "%d/%m/%Y").as("createtm"),DSL.COUNT())
   .from(BS_USER)
   .groupBy(dateFormat(BS_USER.CREATETM, "%d/%m/%Y"))
   .fetch();

// case when
DSL.when(US_USER.AGE.lessOrEqual(18), "未成年").otherwise("成年").as("年齡階段"),

// 聯(lián)合主鍵更新
dao.execute(e -> e.insertInto(BS_USER).columns(BS_USER.GROUP_ID
                , BS_USER.USERNAME
                , BS_USER.USERNICKNAME
                , BS_USER.AGE
                , BS_USER.SEX)
                .values(1, "張三", "wind", "23", 1)
        ).onDuplicateKeyUpdate()
       .set(BS_USER.LEVEL, 3)
       // 注意，這里還需調(diào)用execute方法才會(huì)真正執(zhí)行
       .execute()
);