排序、分页
排序
.asc() // 升序 .desc() // 降序 .asc().nullsFirst() // 升序，空值放前面 .asc().nullsLast() // 降序，空值放前面
//排序 List<MemberDomain> orderList = queryFactory.selectFrom(qm) .orderBy(qm.name.asc()) .fetch();
分页
.limit(long limit) // 限制查询结果返回的数量。即一页多少条记录（pageSize） .offset(long offset) // 跳过多少行。offset = ( pageNum - 1 ) * pageSize // pageNum：第几页
QMemberDomain qm = QMemberDomain.memberDomain; //写法一 JPAQuery<MemberDomain> query = queryFactory .selectFrom(qm) .orderBy(qm.age.asc()); // 查询总条数。fetchCount时，orderBy不会被执行 long total = query.fetchCount(); // 获取过滤后的查询结果集 List<MemberDomain> list0= query.offset(2).limit(5).fetch(); //写法二。fetchResults()自动实现count查询和结果查询，并封装到QueryResults<T>中 QueryResults<MemberDomain> results = queryFactory .selectFrom(qm) .orderBy(qm.age.asc()) .offset(2) .limit(5) .fetchResults(); List<MemberDomain> list = results.getResults(); // 过滤后的查询结果集 logger.debug("total:"+results.getTotal()); // 符合过滤条件的的总条数 logger.debug("offset:"+results.getOffset()); // 跳过多少条符合过滤条件的查询结果 logger.debug("limit:"+results.getLimit()); // 限制查询结果返回的条数
写法一和二都会发出两条sql进行查询，一条查询count，一条查询具体数据。
写法二的getTotal()等价于写法一的fetchCount。
无论是哪种写法，在查询count的时候，orderBy、limit、offset这三个都不会被执行。可以大胆使用。
子查询
// 子查询作为where条件内容 @Test public void selectJPAExpressions() { List<MemberDomain> subList = queryFactory .selectFrom(qm) .where(qm.status.in( JPAExpressions.select(qm.status).from(qm))) .fetch(); } // 子查询作为select查询字段 @Test public void selectJPAExpressions() { QUserAddress ua = QUserAddress.userAddress; QUser u = QUser.user; List<UserAddressDTO> list = queryFactory .select( Projections.bean(UserAddressDTO.class , ua.addressee , Expressions.asNumber( JPAExpressions .select(u.id.count()) .from(u) .where(u.id.ne(ua.userId)) ) .longValue() // asNumber接收子查询结果后需要指定数值的数据类型 .as("lon") // , Expressions.asString( // asString接收子查询结果后不用指定数据类型 // JPAExpressions. // select(u.username) // .from(u) // .where(u.id.eq(ua.userId)) // ) // .as("password") ) ) .from(ua) .where(ua.id.eq(38)) .fetch(); }
联表动态查询
// JPA查询工厂 @Autowired private JPAQueryFactory queryFactory; /** * 关联查询示例，查询出城市和对应的旅店 */ @Test public void findCityAndHotel() { QTCity qtCity = QTCity.tCity; QTHotel qtHotel = QTHotel.tHotel; JPAQuery<Tuple> jpaQuery = queryFactory .select(qtCity, qtHotel) .from(qtCity) .leftJoin(qtHotel) .on(qtHotel.city.longValue().eq(qtCity.id.longValue())); // 分离式 添加查询条件 jpaQuery.where(QTCity.tCity.name.like("shanghai")); // 获取查询结果 List<Tuple> result = jpaQuery.fetch(); // 对多元组取出数据，这个和select时的数据相匹配 for (Tuple row : result) { System.out.println("qtCity:" + row.get(qtCity)); System.out.println("qtHotel:" + row.get(qtHotel)); System.out.println("--------------------"); } }
联表一对多查询封装
方式一：查询结果返回类型为List
List<UserAddressDTO> list = queryFactory .from(u) .join(ua) .on(ua.userId.eq(u.id)) .where(u.id.eq(31)) .transform(GroupBy.groupBy(u.id) .list( Projections.bean(UserAddressDTO.class, u.id, u.username, GroupBy.list( Projections.bean(UserAddress.class, ua.address, ua.city, ua.district )).as("userAddresses"))) );
方式二：查询结果返回类型为Map
map的key为分组字段，一般为主键ID
Map<Integer, UserAddressDTO> map = queryFactory .from(u) .join(ua) .on(ua.userId.eq(u.id)) .where(u.id.eq(31)) .transform(GroupBy.groupBy(u.id) .as( Projections.bean(UserAddressDTO.class, u.id, u.username, GroupBy.list(Projections.bean(UserAddress.class, ua.address, ua.city, ua.district )).as("userAddresses"))) );
实体类：
@Data @Builder @AllArgsConstructor @NoArgsConstructor public class UserAddressDTO { private Integer id; private String username; private String password; private String phone; private List<UserAddress> userAddresses; }
使用聚合函数
//聚合函数-avg() Double averageAge = queryFactory .select(qm.age.avg()) .from(qm) .fetchOne(); //聚合函数-concat() String concat = queryFactory .select(qm.name.concat(qm.address)) .from(qm) .fetchOne(); //聚合函数-date_format() String date = queryFactory .select( Expressions.stringTemplate("DATE_FORMAT({0},'%Y-%m-%d')", qm.registerDate)) .from(qm) .fetchOne();
当用到DATE_FORMAT这类QueryDSL似乎没有提供支持的Mysql函数时，可以手动拼一个String表达式。这样就可以无缝使用Mysql中的函数了。
使用 Template 实现自定义语法
QueryDSL并没有对数据库的所有函数提供支持，好在它提供了Template特性。
可以使用Template来实现各种QueryDSL未直接支持的语法。
Template的局限性：
由于Template中使用了{}来作为占位符（内部序号从0开始），而正则表达式中也可能使用了{}，因而会产生冲突。
QMemberDomain qm = QMemberDomain.memberDomain; //使用booleanTemplate充当where子句或where子句的一部分 List<MemberDomain> list = queryFactory .selectFrom(qm) .where(Expressions.booleanTemplate("{0} = \"tofu\"", qm.name)) .fetch(); //上面的写法，当booleanTemplate中需要用到多个占位时 List<MemberDomain> list1 = queryFactory .selectFrom(qm) .where( Expressions.booleanTemplate("{0} = \"tofu\" and {1} = \"Amoy\"", qm.name, qm.address)) .fetch(); //使用stringTemplate充当查询语句的某一部分 String date = queryFactory .select( Expressions.stringTemplate("DATE_FORMAT({0},'%Y-%m-%d')", qm.registerDate)) .from(qm) .fetchFirst(); //在where子句中使用stringTemplate String id = queryFactory .select(qm.id) .from(qm) .where( Expressions.stringTemplate("DATE_FORMAT({0},'%Y-%m-%d')", qm.registerDate).eq("2018-03-19")) .fetchFirst();
QueryDslPredicateExecutor 风格
通常使用Repository来继承QueryDslPredicateExecutor<T>接口。通过注入Repository来使用。
Repository 接口
Spring Data JPA中提供了QueryDslPredicateExecutor接口，用于支持QueryDSL的查询操作。
public interface tityRepository extends JpaRepository<City, Integer>, QuerydslPredicateExecutor<city> {}
QueryDslPredicateExecutor<T>接口提供了findOne()，findAll()，count()，exists()四个方法来支持查询。并可以使用更优雅的BooleanBuilder 来进行条件分支管理。
count()会返回满足查询条件的数据行的数量
exists()会根据所要查询的数据是否存在返回一个boolean值
findOne()、findAll()
findOne
从数据库中查出一条数据。没有重载方法。
Optional<T> findOne(Predicate var1);
和JPAQuery的fetchOne()一样，当根据查询条件从数据库中查询到多条匹配数据时，会抛NonUniqueResultException。使用的时候需要慎重。
findAll()
findAll是从数据库中查出匹配的所有数据。提供了以下几个重载方法。
Iterable<T> findAll(Predicate var1); Iterable<T> findAll(Predicate var1, Sort var2); Iterable<T> findAll(Predicate var1, OrderSpecifier<?>... var2); Iterable<T> findAll(OrderSpecifier<?>... var1); Page<T> findAll(Predicate var1, Pageable var2);
使用示例：
QMemberDomain qm = QMemberDomain.memberDomain; // QueryDSL 提供的排序实现 OrderSpecifier<Integer> order = new OrderSpecifier<>(Order.DESC, qm.age); Iterable<MemberDomain> iterable = memberRepo.findAll(qm.status.eq("0013"),order); QMemberDomain qm = QMemberDomain.memberDomain; // Spring Data 提供的排序实现 Sort sort = new Sort(new Sort.Order(Sort.Direction.ASC, "age")); Iterable<MemberDomain> iterable = memberRepo.findAll(qm.status.eq("0013"), sort);
单表动态分页查询
单表动态查询示例：
//动态条件 QTCity qtCity = QTCity.tCity; //SDL实体类 //该Predicate为querydsl下的类,支持嵌套组装复杂查询条件 Predicate predicate = qtCity.id.longValue().lt(3).and(qtCity.name.like("shanghai")); //分页排序 Sort sort = new Sort(new Sort.Order(Sort.Direction.ASC,"id")); PageRequest pageRequest = new PageRequest(0,10,sort); //查找结果 Page<TCity> tCityPage = tCityRepository.findAll(predicate, pageRequest);
Querydsl SQL 查询
Querydsl SQL 模块提供与 JDBC API 的集成。可以使用更多的 JDBC SQL方法。比如可以实现 from 的查询主体为子查询出来的临时表、union、union All 等Querydsl-JPA限制的相关操作。还可以根据 JDBC API 获取数据库的类型使用不同的数据库语法模板。
依赖及配置
依赖：
<dependency> <groupId>com.querydsl</groupId> <artifactId>querydsl-sql</artifactId> <version>${querydsl.version}</version> </dependency> <!-- joda-time为querydsl-sql中必需的新版本的时间日期处理库 --> <dependency> <groupId>joda-time</groupId> <artifactId>joda-time</artifactId> <version>2.10.5</version> </dependency>
yaml配置：
logging: level: com.querydsl.sql: debug # 打印日志
SQLQuery 的 Q 类
需要自己创建编写（可以基于apt 插件生成的 JPA 的Q类改造），并放到主目录（src）启动类下的包里。
使用 extends RelationalPathBase<Entity> 的Q类。推荐
需要将数据库表名传入构造方法的table参数里，path 可以传别名，所有的property参数为实体类的属性名（驼峰命名），addMetadata() 中ColumnMetadata.named("FeildNmae") 的 FeildNmae 为数据库字段名。
使用该Q类查询所有字段数据时（即select(Q类)）可以自动映射封装结果集。
使用extends EntityPathBase<Entity>的Q类。
需要将传入构造方法的variable参数改成数据库表名，并且将所有的property参数改成相对应的数据库字段名。
**注意：**使用 extends EntityPathBase<Entity> 的实体Q类，直接 select(Q类) 会报错，无法自动映射封装结果集，需要使用Projections.bean(Entity.class，Expression<?>... exprs) 手动封装结果集。
/** * extends RelationalPathBase<Entity> 的Q类示例 */ public class QEmployee extends RelationalPathBase<Employee> { private static final long serialVersionUID = 1394463749655231079L; public static final QEmployee employee = new QEmployee("EMPLOYEE"); public final NumberPath<Integer> id = createNumber("id", Integer.class); public final StringPath firstname = createString("firstname"); public final DatePath<java.util.Date> datefield = createDate("datefield", java.util.Date.class); public final PrimaryKey<Employee> idKey = createPrimaryKey(id); public QEmployee(String path) { super(Employee.class, PathMetadataFactory.forVariable(path), "PUBLIC", "EMPLOYEE"); addMetadata(); } public QEmployee(PathMetadata metadata) { super(Employee.class, metadata, "PUBLIC", "EMPLOYEE"); addMetadata(); } protected void addMetadata() { addMetadata(id, ColumnMetadata.named("ID").ofType(Types.INTEGER)); addMetadata(firstname, ColumnMetadata.named("FIRSTNAME").ofType(Types.VARCHAR)); addMetadata(datefield, ColumnMetadata.named("DATEFIELD").ofType(Types.DATE)); } }
/** * extends EntityPathBase<Entity> 的Q类示例 */ public class QUserAddressS extends EntityPathBase<UserAddress> { private static final long serialVersionUID = -1295712525L; public static final QUserAddressS userAddress = new QUserAddressS("tb_user_address"); public final NumberPath<Integer> id = createNumber("id", Integer.class); public final StringPath address = createString("address"); public final DateTimePath<java.util.Date> createTime = createDateTime("create_time", java.util.Date.class); public QUserAddressS(String variable) { super(UserAddress.class, forVariable(variable)); } public QUserAddressS(Path<? extends UserAddress> path) { super(path.getType(), path.getMetadata()); } public QUserAddressS(PathMetadata metadata) { super(UserAddress.class, metadata); } }
SQLQueryFactory 方式
装配及基本使用
装配
@Configuration @Slf4j public class QueryDslConfig { @Bean public SQLQueryFactory sqlQueryFactory(DataSource druidDataSource){ SQLTemplates t; try(Connection connection = druidDataSource.getConnection()){ t = new SQLTemplatesRegistry().getTemplates(connection.getMetaData()); }catch (Exception e){ log.error("", e); t = SQLTemplates.DEFAULT; } com.querydsl.sql.Configuration configuration = new com.querydsl.sql.Configuration(t); configuration.addListener(new SQLBaseListener(){ @Override public void end(SQLListenerContext context) { if (context != null && !DataSourceUtils.isConnectionTransactional(context.getConnection(), druidDataSource)){ // 若非事务连接 SQLCloseListener.DEFAULT.end(context); } } }); configuration.setExceptionTranslator(new SpringExceptionTranslator()); // 创建SQLQueryFactory，且数据库连接由spring管理 return new SQLQueryFactory(configuration, () -> DataSourceUtils.getConnection(druidDataSource)); } }
注入
@Autowired private SQLQueryFactory sqlQueryFactory; • 1 • 2
SQLQueryFactory 基本使用
/** * 子查询作为临时表传入from()中 */ @Test public void selectBySqlQueryFactory(){ // 使用 extends RelationalPathBase<Entity> 的QEntity，自动映射封装 QUserAddressSql uaSql = QUserAddressSql.userAddress; // 子查询 SQLQuery<Tuple> q = SQLExpressions .select( // 查询字段须是数据库表中的字段名（不是实体属性名），且类型一致 uaSql.addressee , uaSql.userId ) .from(uaSql); List<Tuple> fetch = sqlQueryFactory .select( // 查询字段须是临时表中的字段别名，且类型一致 Expressions.template(String.class, "q.addressee").as("addressee") , Expressions.numberTemplate(Integer.class, "q.user_id").as("userId") ) .from(q, Expressions.stringPath("q")) // 子查询作为临时表 .fetch(); System.out.println(fetch); } /** * 子查询结果集 union */ @Test public void selectBySqlQueryFactory(){ // 使用 extends EntityPathBase<Entity> 的改造版QEntity，结果集如需封装到实体类，必须手动指定实体类来接收 QUserAddressSql uaSql = QUserAddressSql.userAddress; QUserSql uSql = QUserSql.user; SQLQuery<Tuple> a = SQLExpressions .select(uaSql.userId.as("user_id") , uaSql.phone) .from(uaSql) .where(uaSql.userId.eq(30)); SQLQuery<Tuple> b = SQLExpressions .select(uSql.id.as("user_id") , uSql.phone) .from(uSql) .where(uSql.id.eq(29).or(uSql.id.eq(30))); Union<Tuple> union = sqlQueryFactory.query().union(a, b); long count = sqlQueryFactory .from(union, Expressions.stringPath("q")).fetchCount(); List<UserAddressDTO> list = sqlQueryFactory .from(union, Expressions.stringPath("q")) .orderBy(Expressions.numberPath(Integer.class, "user_id").desc() , Expressions.stringTemplate("phone").desc()) .offset(0) .limit(5) .transform( GroupBy.groupBy(Expressions.template(String.class, "q.user_id")).list( Projections.bean(UserAddressDTO.class , Expressions.template(Integer.class, "q.user_id").as("userId") , GroupBy.list(Projections.bean(UserAddress.class , Expressions.stringTemplate("q.phone").as("phone") )).as("userAddresses") ))); System.out.println(count); list.forEach(s -> System.out.println(JSON.toJSONString(s))); }
SQLExpression 表达式工具类
// 合并多张表记录。union为去重合并，unionAll为不去重合并 static <T> Union<T> union(SubQueryExpression<T>... sq) static <T> Union<T> union(List<SubQueryExpression<T>> sq) static <T> Union<T> unionAll(SubQueryExpression<T>... sq) static <T> Union<T> unionAll(List<SubQueryExpression<T>> sq) // 调用函数查询序列 static SimpleExpression<Long> nextval(String sequence) static <T extends Number> SimpleExpression<T> nextval(Class<T> type, String sequence) // 使用示例：SQL写法：select seq_process_no.nextval from dual; Long nextvalReturn = sqlQueryFactory.select(SQLExpressions.nextval("序列名")).fetchOne; // 将多列记录聚合为一列记录。delimiter为分隔符。Oracle数据库专属，其他数据库报错 static WithinGroup<Object> listagg(Expression<?> expr, String delimiter) // 使用示例： SQLExpression.listagg(qEntity.name, ",").withinGroup.OrderBy(qEntity.name.asc()).getValue.as("Name") // 将多列记录聚合为一列记录。separator为分隔符。MySQL、PostgreSQL都可用，PostgreSQL会根据模板翻译成String_agg函数 static StringExpression groupConcat(Expression<String> expr, String separator) static StringExpression groupConcat(Expression<String> expr) static <T> RelationalFunctionCall<T> relationalFunctionCall(Class<? extends T> type, String function, Object... args) static <D extends Comparable> DateExpression<D> date(DateTimeExpression<D> dateTime) static <D extends Comparable> DateExpression<D> date(Class<D> type, DateTimeExpression<?> dateTime) static <D extends Comparable> DateTimeExpression<D> dateadd(DatePart unit, DateTimeExpression<D> date, int amount) static <D extends Comparable> DateExpression<D> dateadd(DatePart unit, DateExpression<D> date, int amount) // 获取两个日期的时间间隔(end-start) static <D extends Comparable> NumberExpression<Integer> datediff(DatePart unit, DateTimeExpression<D> start, DateTimeExpression<D> end)
JPASQLQuery 方式
使用 JPASQLQuery 作为查询引擎时，使用的QEntity（extends EntityPathBase<Entity>），传入构造方法的 variable 参数可以不为数据库表名（因为 JPASQLQuery 可以找到映射的真实表名，仅把此参数作为表别名），但所有的 property 参数仍必需为相对应的数据库字段名。
故并不能直接使用 apt 插件生成 的 jpa 使用的 Q类，仍需要使用改造版的 Q类（extends EntityPathBase<Entity>）。
@Test public void selectBySqlQueryFactory(){ // 使用 extends EntityPathBase<Entity> 的改造版QEntity，结果集如需封装到实体类，必须手动指定实体类来接收 QUserAddress ua = QUserAddress.userAddress; // jpa+sql的查询工具，本例使用的oracle的sql模板 JPASQLQuery<?> jpasqlQuery = new JPASQLQuery<Void>(em, new OracleTemplates()); // 子查询 SQLQuery<Tuple> q = SQLExpressions .select( // 查询字段须是数据库表中的字段名（不是实体属性名），且类型一致。如直接不使用QEntity的属性，则需手动指定 Expressions.stringPath("addressee").as("addressee") , Expressions.numberPath(Integer.class, "user_id").as("user_id") ) .from(ua); List<Tuple> fetch = jpasqlQuery .select( // 查询字段须是临时表中的字段名或别名，且类型一致。结果集字段需添加别名手动映射封装 Expressions.template(String.class, "q.addressee").as("addressee") , Expressions.numberTemplate(Integer.class, "q.user_id").as("userId") ) .from(q, Expressions.stringPath("q")) // 子查询作为临时表 .fetch(); System.out.println(fetch); }
