JOOQ
初识JOOQ¶
JOOQ 是基于Java访问关系型数据库的工具包，轻量，简单，并且足够灵活，可以轻松的使用Java面向对象语法来实现各种复杂的sql。对于写Java的码农来说ORMS再也熟悉不过了，不管是Hibernate或者Mybatis，都能简单的使用实体映射来访问数据库。
get back in control of your sql
JOOQ的优点¶
- DSL(Domain Specific Language )风格，代码够简单和清晰。遇到不会写的sql可以充分利用IDEA代码提示功能轻松完成。
- 保留了传统ORM 的优点，简单操作性，安全性，类型安全等。不需要复杂的配置，并且可以利用Java 8 Stream API 做更加复杂的数据转换。
几个概念¶
jooq对表的抽象
jooq对字段的抽象
这里准确来说应该是Select族.Select有很多相似的类型,SelectConditionStep,SelectSelectStep等等,
是对查询一个封装。
jooq对查询条件的抽象,说白了就是where后面条件的一个集合
jooq对数据库数据的抽象,可以理解为数据库中的一条或者多条数据
jooq对查询结果集的封装,可以这样用:Record r = select xx,也可以这样玩:Result<Record>,目前基本上是用Result<Record>这种用法来存查询的结果集。
jooq的上下文,通过它,基本上可以做任何你想做的事,其它的元素,都可以通过这哥们得到,所以,搞懂了DSLContext,什么jooq都不是问题了。
准备¶
maven 里面引入
<dependency>
<groupId>com.zxy.common</groupId>
<artifactId>common-dao</artifactId>
</dependency>
使用¶
用spring注解进来
private CommonDao<Member> dao ;
@Autowired
public void setDao(CommonDao<Member> dao) {
this.dao = dao;
}
get¶
dao.get(String id);
dao.getOptional(String id);
update¶
T update(T entity)
void update(Collection<T> entities)
// JOOQGenericDao源码 如果修改的实体有null值，但是数据库又是不能为null的，那么这个字段不会修改。
public T update(T entity) {
this.record(entity, true, DSL.using(this.configuration)).update();
return entity;
}
private UpdatableRecord<?> record(T object, boolean forUpdate, DSLContext context) {
UpdatableRecord r = (UpdatableRecord)context.newRecord(this.table, object);
if(forUpdate) {
r.changed(this.primaryKey, false);
}
int size = r.size();
for(int i = 0; i < size; ++i) {
if(r.getValue(i) == null && !r.field(i).getDataType().nullable()) {
r.changed(i, false);
}
}
return r;
}
delete¶
void delete(ID id)
void delete(Collection<ID> ids)
void delete(Condition... conditions)
void delete(Stream<Condition> conditions)
void deleteWithOptional(Stream<Optional<Condition>> conditions)
根据ID返还唯一对象，get查询不到会报错，getOptional返还可为空的对象.
fetch¶
-
只查询一列
List<String> titles1 = create.select().from(BOOK).fetch().getValues(BOOK.TITLE); List<String> titles2 = create.select().from(BOOK).fetch(BOOK.TITLE); String[] titles3 = create.select().from(BOOK).fetchArray(BOOK.TITLE); -
查询一列并且类型转换
List<Long> ids1 = create.select().from(BOOK).fetch().getValues(BOOK.ID, Long.class); List<Long> ids2 = create.select().from(BOOK).fetch(BOOK.ID, Long.class); Long[] ids3 = create.select().from(BOOK).fetchArray(BOOK.ID, Long.class); -
查询book的id,并且查询所有等于这个id的结果
Map<Integer, BookRecord> map1 = create.selectFrom(BOOK).fetch().intoMap(BOOK.ID); Map<Integer, BookRecord> map2 = create.selectFrom(BOOK).fetchMap(BOOK.ID); Map<Integer, String> map3 = create.selectFrom(BOOK).fetch().intoMap(BOOK.ID, BOOK.TITLE); Map<Integer, String> map4 = create.selectFrom(BOOK).fetchMap(BOOK.ID, BOOK.TITLE); -
Group by AUTHOR_ID and list all books written by any author:
Map<Integer, Result<BookRecord>> group1 = create.selectFrom(BOOK).fetch().intoGroups(BOOK.AUTHOR_ID); Map<Integer, Result<BookRecord>> group2 = create.selectFrom(BOOK).fetchGroups(BOOK.AUTHOR_ID); Map<Integer, List<String>> group3 = create.selectFrom(BOOK).fetch().intoGroups(BOOK.AUTHOR_ID, BOOK.TITLE); Map<Integer, List<String>> group4 = create.selectFrom(BOOK).fetchGroups(BOOK.AUTHOR_ID, BOOK.TITLE); -
fetchInto
create.selectFrom(BOOK) .orderBy(BOOK.ID) .fetch() .into(new RecordHandler<BookRecord>() { @Override public void next(BookRecord book) { Util.doThingsWithBook(book); } }); // Or more concisely create.selectFrom(BOOK) .orderBy(BOOK.ID) .fetchInto(new RecordHandler<BookRecord>() {...}); // Or even more concisely with Java 8's lambda expressions: create.selectFrom(BOOK) .orderBy(BOOK.ID) .fetchInto(book -> { Util.doThingsWithBook(book); }; );
项目实际参考¶
数据库分组查询¶
List<String> list = grantCommonDao.execute(x ->
x.select(GRANT.OPERATOR_TYPES).from(GRANT)
.leftJoin(GRANT_DETAIL).on(GRANT.ID.eq(GRANT_DETAIL.GRANT_ID))
.leftJoin(ROLE).on(GRANT.ROLE_ID.eq(ROLE.ID))
.leftJoin(parentRole).on(GRANT.ROLE_ID.eq(parentRole.PARENT_ID))
.where(GRANT_DETAIL.MEMBER_ID.eq(memberId))
.and(GRANT_DETAIL.ORGANIZATION_ID.in(organizationIds))
.and(ROLE.ID.eq(roleId).or(parentRole.ID.eq(roleId)))
.groupBy(GRANT.OPERATOR_TYPES))
.fetch().getValues(GRANT.OPERATOR_TYPES);
java8分组查询¶
Result<Record> record = courseChapterCommonDao.execute(
x -> x.select(Fields.start().add(COURSE_CHAPTER).add(COURSE_CHAPTER_SECTION).end()).from(COURSE_CHAPTER)
.leftJoin(COURSE_CHAPTER_SECTION).on(COURSE_CHAPTER.ID.eq(COURSE_CHAPTER_SECTION.CHAPTER_ID))
.where(COURSE_CHAPTER.COURSE_ID.eq(courseId))
.orderBy(COURSE_CHAPTER.SEQUENCE.desc(), COURSE_CHAPTER_SECTION.SEQUENCE.asc())
.fetch());
Map<String, List<CourseChapterSection>> sectionMap = record.into(COURSE_CHAPTER_SECTION)
.into(CourseChapterSection.class).stream().filter(x -> x.getId() != null)
.collect(groupingBy(CourseChapterSection::getChapterId));
List<CourseChapter> courseChapters = new ArrayList<>(record.into(COURSE_CHAPTER).into(CourseChapter.class).stream()
.collect(toMap(CourseChapter::getId, p -> p, (p, q) -> q)).values());
courseChapters.forEach(x -> x.setCourseChapterSections(sectionMap.get(x.getId())));
courseChapters.sort((a, b) -> a.getSequence() - b.getSequence()); // 倒序
return courseChapters;
分页查询¶
com.zxy.product.course.jooq.tables.Organization organizationTable = ORGANIZATION.as("organization"); // 所属部门
com.zxy.product.course.jooq.tables.Member createUserTable = MEMBER.as("createUser"); // 创建人
SelectSelectStep<Record> selectListField = x
.select(Fields.start().add(COURSE_INFO).add(organizationTable).add(createUserTable).end()); // 查询list
SelectSelectStep<Record> selectCountField = x.select(Fields.start().add(COURSE_INFO.ID.count()).end()); // 查询总条数
List<Condition> param = Stream
.of(name.map(COURSE_INFO.NAME::contains), status.map(COURSE_INFO.STATUS::eq),
createTime.map(COURSE_INFO.CREATE_TIME::gt),
organizationId.map(COURSE_INFO.ORGANIZATION_ID::eq), type.map(COURSE_INFO.TYPE::eq))
.filter(Optional::isPresent).map(Optional::get).collect(Collectors.toList());
Function<SelectSelectStep<Record>, SelectConditionStep<Record>> stepFunc = a -> {
SelectConditionStep<Record> select = a.from(COURSE_INFO).leftJoin(organizationTable)
.on(organizationTable.ID.eq(COURSE_INFO.ORGANIZATION_ID)).leftJoin(createUserTable)
.on(createUserTable.ID.eq(COURSE_INFO.CREATE_MEMBER_ID)).where(param);
itemScromType.map(stype -> select.andExists(x.select(COURSE_CHAPTER_SECTION.ID)
.from(COURSE_CHAPTER_SECTION).where(COURSE_INFO.ID.eq(COURSE_CHAPTER_SECTION.COURSE_ID)
.and(COURSE_CHAPTER_SECTION.FILE_TYPE.eq(stype)))));
return select;
};
int count = stepFunc.apply(selectCountField).fetchOne().getValue(0, Integer.class);
SelectConditionStep<Record> listSetp = stepFunc.apply(selectListField);
listSetp.orderBy(COURSE_INFO.CREATE_TIME.desc());
Result<Record> record = listSetp.limit((pageNum - 1) * pageSize, pageSize).fetch();
List<CourseInfo> courseInfoList = record.into(COURSE_INFO).into(CourseInfo.class);
List<Organization> organizationList = record.into(organizationTable).into(Organization.class);
List<Member> createUserList = record.into(createUserTable).into(Member.class);
IntStream.range(0, courseInfoList.size()).forEach(i -> {
courseInfoList.get(i).setOrganization(organizationList.get(i));
courseInfoList.get(i).setCreateUser(createUserList.get(i));
});
return PagedResult.create(count, courseInfoList);