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();
    }

更多方式详见