jooq多表查询_JOOQ初学-简单的增删改查demo

1 packagecom.jooq.Test;2

3 importjava.sql.Connection;4

5 importorg.jooq.Condition;6 importorg.jooq.DSLContext;7 importorg.jooq.Record;8 importorg.jooq.Result;9 importorg.jooq.SelectQuery;10 importorg.jooq.Table;11 importorg.jooq.UpdateQuery;12 importorg.jooq.impl.DSL;13

14 importcom.jolbox.bonecp.BoneCP;15

16 public classJooqDao {17

18 private DSLContext dslContext = null;19

20 //获取DSLContext对象

21 privateDSLContext getdslContext() {22 BoneCP boneCP =BoneCpPool.getBoneCp();23 Connection conn =BoneCpPool.getConnection(boneCP);24 dslContext =DSL.using(conn);25 returndslContext;26 }27

28 //简单实体查询

29 public voidselect(String add) {30 DSLContext dslContext =getdslContext();31 Table table = DSL.table("sys_role");32 //获取查询对象

33 SelectQuery selectQuery =dslContext.selectQuery(table);34 //查询条件

35 Condition eq = DSL.field("r_name").eq(add);36 //添加查询条件

37 selectQuery.addConditions(eq);38 Result fetch =selectQuery.fetch();39 for(Object aResult : fetch) {40 Record record =(Record) aResult;41 System.out.println(record);42 System.out.println(record.getValue("r_name"));43 }44 }45

46 //实体更新

47 public voidupdate(String name) {48 DSLContext dslContext =getdslContext();49 Table table = DSL.table("sys_role");50 //获取更新对象

51 UpdateQuery updateQuery =dslContext.updateQuery(table);52 //更新r_name字段的值为财务结算管理1

53 updateQuery.addValue(DSL.field("r_name"), "财务结算管理");54 //根据查询条件

55 Condition condition = DSL.field("r_name").eq(name);56 updateQuery.addConditions(condition);57 int excute =updateQuery.execute();58 System.out.println(excute);59 select("财务结算管理");60 }61

62 //原生态的sql查询

63 public voidgetVal() {64 DSLContext dslContext =getdslContext();65 Table table = DSL.table("sys_role");66 Result fetch =dslContext.select().from(table)67 .where("domain_id=0").and("r_status='1'")68 .orderBy(DSL.field("b_createtime").asc()).fetch();69

70 for(Object aResult:fetch){71 Record record =(Record) aResult;72 System.out.println(record);73 }74

75 /*Map fetchAnyMap = orderBy.fetchAnyMap();76 Set keySet = fetchAnyMap.keySet();77 for(String s:keySet)78 {79 System.out.println("key--"+s+"--val:"+fetchAnyMap.get(s));80 }*/

81 }82

83 //验证DSL.exists方法

84 public voidexits(){85 DSLContext getdslContext =getdslContext();86 Condition condition = DSL.exists(DSL.select(DSL.field("r_name")));87 Table table = DSL.table("sys_role");88 SelectQuery selectQuery =getdslContext.selectQuery(table);89 selectQuery.addConditions(condition);90 Result fetch =selectQuery.fetch();91 for(Object aResult : fetch) {92 Record record =(Record) aResult;93 System.out.println(record);94 System.out.println(record.getValue("r_name"));95 }96 }97

98 public static voidmain(String[] args) {99

100 JooqDao dao = newJooqDao();101 dao.exits();102 }103 }