QueryDSL基本操作demo

1、装配实例化JPAQueryFactory

private final BaseDao dao;
private final JPAQueryFactory factory;
private final QUser qUser;
private final QFile qFile;

@Autowired
public UserServiceImpl(BaseDao dao) {
	this.factory = new JPAQueryFactory(dao.getEntityManager());
	this.dao = dao;
	this.qFile = QFile.file;
	this.qUser = QUser.user;
}

2、单表简单查询

User user = factory
		.from(qUser)
		.select(
				Projections.bean(
						User.class,
						qUser.username
				)
		)
		.where(qUser.id.eq("8f98b857fa954cfb986b88a2692e9d81"))
		.where(qUser.tel.eq("4646464"))
		.fetchOne();

3、单表分页查询

JPAQuery listQuery = factory
		//tel字段去重（6条数据）
		.selectDistinct(qUser.tel)
		.from(qUser)
		.select(
				//封装映射到UserViewModel中
				Projections.bean(
						UserViewModel.class,
						qUser.id,
						qUser.username
				)
		)
		//where可多个参数
		.where(predicate,qUser.createdTime.before(new Date()))
		.orderBy(qUser.id.desc())
		.offset(pageIndex)
		.limit(pageSize);
List<UserViewModel> list = listQuery.fetch();
//fetchCount不会执行orderBy、offset、limit参数也不会去重(7条数据)
int count = (int) listQuery.fetchCount();

4、多表简单查询

UserViewModel userViewModel = factory
		.select(
				Projections.bean(
						UserViewModel.class,
						qUser.id,
						qUser.creator,
						qUser.tel,
						qUser.username,
						qUser.address,
						qFile.name.as("name"),
						qUser.createdTime.stringValue()
				)
		)
		.from(qUser,qFile)
		.where(
				qFile.name.eq("test")
				.and(qUser.username.eq(qFile.creator))
		)
		.fetchOne();

5、子查询

List<FileViewModel> selectDome = factory
		.from(qFile)
		.select(
				Projections.bean(
						FileViewModel.class,
						qFile.id,
						qFile.name,
						qFile.size
				)
		)
		.where(
				qFile.size.lt(
						JPAExpressions
						.select(qFile.size.avg())
						.from(qFile)
				)
		)
		.fetch();

6、聚合函数

Long getCount = factory
		.from(qUser)
		.select(qUser.id.count())
		.fetchOne();
Double getAvg = factory
		.from(qFile)
		.select(qFile.size.avg())
		.fetchOne();
Long getMin = factory
		.select(qFile.size.min())
		.from(qFile)
		.fetchOne();
Long getSum = factory
		.select(qFile.size.sum())
		.from(qFile)
		.fetchOne();

7、更新操作

factory.update(qMoldsUsage)
		.set(qMoldsUsage.returneeId,model.getReturneeId())
		.set(qMoldsUsage.returnTime,model.getReturnTime())
		.where(qMoldsUsage.id.eq(model.getId()))
		.execute();

8、删除操作

factory.delete(qExitStorage)
		.where(qExitStorage.materialId.eq(model.getMaterialId()))
		.execute();