QueryDSL学习笔记

QueryDSL简介

1. QueryDSL仅仅是一个通用的查询框架，专注于通过Java API构建类型安全的SQL查询。
2. Querydsl可以通过一组通用的查询API为用户构建出适合不同类型ORM框架或者是SQL的查询语句，也就是说QueryDSL是基于各种ORM框架以及SQL之上的一个通用的查询框架。
3. 借助QueryDSL可以在任何支持的ORM框架或者SQL平台上以一种通用的API方式来构建查询。目前QueryDSL支持的平台包括JPA,JDO,SQL,Java
   Collections,RDF,Lucene,Hibernate Search。
   使用QueryDSL需先通过Maven插件编译自动生成实体类型的结构查询实体。
   mvn clean complie

package com.ly.domain;import static com.querydsl.core.types.PathMetadataFactory.*;
import com.querydsl.core.types.dsl.*;
import com.querydsl.core.types.PathMetadata;
import javax.annotation.Generated;
import com.querydsl.core.types.Path;/*** QBank is a Querydsl query type for Bank*/
@Generated("com.querydsl.codegen.EntitySerializer")
public class QBank extends EntityPathBase<Bank> {private static final long serialVersionUID = -1720494478L;public static final QBank bank = new QBank("bank");public final StringPath bankType = createString("bankType");public final DateTimePath<java.util.Date> gmtCreate = createDateTime("gmtCreate", java.util.Date.class);public final DateTimePath<java.util.Date> gmtModified = createDateTime("gmtModified", java.util.Date.class);public final NumberPath<Long> id = createNumber("id", Long.class);public final NumberPath<Long> isDeleted = createNumber("isDeleted", Long.class);public final StringPath name = createString("name");public final NumberPath<Long> ordernum = createNumber("ordernum", Long.class);public QBank(String variable) {super(Bank.class, forVariable(variable));}public QBank(Path<? extends Bank> path) {super(path.getType(), path.getMetadata());}public QBank(PathMetadata metadata) {super(Bank.class, metadata);}}

使用范例
单表查询

       public User findUserByUserName(final String userName){/*** 该例是使用spring data QueryDSL实现*/QUser quser = QUser.user;Predicate predicate = quser.name.eq(userName);return repository.findOne(predicate);}/*** attention:* Details：查询user表中的所有记录*/public List<User> findAll(){QUser quser = QUser.user;return queryFactory.selectFrom(quser).fetch();}/*** Details：单条件查询*/public User findOneByUserName(final String userName){QUser quser = QUser.user;return queryFactory.selectFrom(quser).where(quser.name.eq(userName)).fetchOne();}/*** Details：单表多条件查询*/public User findOneByUserNameAndAddress(final String userName, final String address){QUser quser = QUser.user;return queryFactory.select(quser).from(quser) // 上面两句代码等价与selectFrom.where(quser.name.eq(userName).and(quser.address.eq(address)))// 这句代码等同于where(quser.name.eq(userName), quser.address.eq(address)).fetchOne();}/*** Details：使用join查询*/public List<User> findUsersByJoin(){QUser quser = QUser.user;QUser userName = new QUser("name");return queryFactory.selectFrom(quser).innerJoin(quser).on(quser.id.intValue().eq(userName.id.intValue())).fetch();}/*** Details：将查询结果排序*/public List<User> findUserAndOrder(){QUser quser = QUser.user;return queryFactory.selectFrom(quser).orderBy(quser.id.desc()).fetch();}/*** Details：Group By使用*/public List<String> findUserByGroup(){QUser quser = QUser.user;return queryFactory.select(quser.name).from(quser).groupBy(quser.name).fetch();}/*** Details：删除用户*/public long deleteUser(String userName){QUser quser = QUser.user;return queryFactory.delete(quser).where(quser.name.eq(userName)).execute();}/*** Details：更新记录*/public long updateUser(final User u, final String userName){QUser quser = QUser.user;return queryFactory.update(quser).where(quser.name.eq(userName)).set(quser.name, u.getName()).set(quser.age, u.getAge()).set(quser.address, u.getAddress()).execute();}/*** Details：使用原生Query*/public User findOneUserByOriginalSql(final String userName){QUser quser = QUser.user;Query query = queryFactory.selectFrom(quser).where(quser.name.eq(userName)).createQuery();return (User) query.getSingleResult();}/*** Details：分页查询单表*/public Page<User> findAllAndPager(final int offset, final int pageSize){Predicate predicate = QUser.user.id.lt(10);Sort sort = new Sort(new Sort.Order(Sort.Direction.DESC, "id"));PageRequest pr = new PageRequest(offset, pageSize, sort);return repository.findAll(predicate, pr);}

多表操作示例(一对一)

/*** Details：多表动态查询*/public List<Tuple> findAllPersonAndIdCard(){Predicate predicate = (QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());JPAQuery<Tuple> jpaQuery = queryFactory.select(QIDCard.iDCard.idNo, QPerson.person.address, QPerson.person.name).from(QIDCard.iDCard, QPerson.person).where(predicate);return jpaQuery.fetch();}/*** Details：将查询结果以DTO的方式输出*/public List<PersonIDCardDto> findByDTO(){Predicate predicate = (QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());JPAQuery<Tuple> jpaQuery = queryFactory.select(QIDCard.iDCard.idNo, QPerson.person.address, QPerson.person.name).from(QIDCard.iDCard, QPerson.person).where(predicate);List<Tuple> tuples = jpaQuery.fetch();List<PersonIDCardDto> dtos = new ArrayList<PersonIDCardDto>();if(null != tuples && !tuples.isEmpty()){for(Tuple tuple:tuples){String address = tuple.get(QPerson.person.address);String name = tuple.get(QPerson.person.name);String idCard = tuple.get(QIDCard.iDCard.idNo);PersonIDCardDto dto = new PersonIDCardDto();dto.setAddress(address);dto.setIdNo(idCard);dto.setName(name);dtos.add(dto);}}return dtos;}/*** Details：多表动态查询，并分页*/public QueryResults<Tuple> findByDtoAndPager(int offset, int pageSize){Predicate predicate = (QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());return queryFactory.select(QIDCard.iDCard.idNo, QPerson.person.address, QPerson.person.name).from(QIDCard.iDCard, QPerson.person).where(predicate).offset(offset).limit(pageSize).fetchResults();}

/*** Details：方式一：使用Bean投影*/public List<PersonIDCardDto> findByDTOUseBean(){Predicate predicate = (QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());return queryFactory.select(Projections.bean(PersonIDCardDto.class, QIDCard.iDCard.idNo, QPerson.person.address, QPerson.person.name)).from(QIDCard.iDCard, QPerson.person).where(predicate).fetch();}/*** Details：方式二：使用fields来代替setter*/public List<PersonIDCardDto> findByDTOUseFields(){Predicate predicate = (QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());return queryFactory.select(Projections.fields(PersonIDCardDto.class, QIDCard.iDCard.idNo, QPerson.person.address, QPerson.person.name)).from(QIDCard.iDCard, QPerson.person).where(predicate).fetch();}/*** Details：方式三：使用构造方法，注意构造方法中属性的顺序必须和构造器中的顺序一致*/public List<PersonIDCardDto> findByDTOUseConstructor(){Predicate predicate = (QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());return queryFactory.select(Projections.constructor(PersonIDCardDto.class, QPerson.person.name, QPerson.person.address, QIDCard.iDCard.idNo)).from(QIDCard.iDCard, QPerson.person).where(predicate).fetch();}

更多方式详见

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！