JPA複雑クエリ-Querydsl
29567 ワード
依存の追加
mvn compileを実行すると、Queryエンティティが生成されます.
単一テーブルクエリ
マルチテーブル操作例(1対1)
クエリ結果をDTOで出力した例では、クエリが終了すると、クエリ結果をDTOオブジェクトに手動で変換します.この方法はあまり優雅ではありません.QueryDSLは、次の例を参照してください.
上記はいくつかのアイデアを提供しているだけで、もちろん@QueryProjectionを使って実現することもでき、非常に柔軟です.1対の複数の例:
リンク
Querydsl Reference Guide
QueryDSLの使用
複雑なクエリーのカプセル化
Spring boot-jpaはQueryDSLを統合して複雑な操作を簡略化する
Spring Boot JPA-Querydslを使用して複雑な操作を処理
転載先:https://www.cnblogs.com/tonyq/p/7881142.html
<dependency>
<groupId>com.querydslgroupId>
<artifactId>querydsl-jpaartifactId>
dependency>
<dependency>
<groupId>com.querydslgroupId>
<artifactId>querydsl-aptartifactId>
<scope>providedscope>
dependency>
<plugin>
<groupId>com.mysema.mavengroupId>
<artifactId>apt-maven-pluginartifactId>
<version>1.1.3version>
<executions>
<execution>
<goals>
<goal>processgoal>
goals>
<configuration>
<outputDirectory>target/generated-sources/javaoutputDirectory>
<processor>com.querydsl.apt.jpa.JPAAnnotationProcessorprocessor>
configuration>
execution>
executions>
plugin>
mvn compileを実行すると、Queryエンティティが生成されます.
単一テーブルクエリ
package com.chhliu.springboot.jpa.repository;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.transaction.Transactional;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Component;
import com.chhliu.springboot.jpa.entity.QUser;
import com.chhliu.springboot.jpa.entity.User;
import com.querydsl.core.types.Predicate;
import com.querydsl.jpa.impl.JPAQueryFactory;
/**
* :QueryDSL JPA
* @author chhliu
*/
@Component
@Transactional
public class UserRepositoryManagerDsl {
@Autowired
private UserRepositoryDls repository;
@Autowired
@PersistenceContext
private EntityManager entityManager;
private JPAQueryFactory queryFactory;
@PostConstruct
public void init() {
queryFactory = new JPAQueryFactory(entityManager);
}
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 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 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 findUserAndOrder(){
QUser quser = QUser.user;
return queryFactory.selectFrom(quser)
.orderBy(quser.id.desc())
.fetch();
}
/**
* Details:Group By
*/
public List 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 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);
}
}
マルチテーブル操作例(1対1)
package com.chhliu.springboot.jpa.repository;
import java.util.ArrayList;
import java.util.List;
import javax.annotation.PostConstruct;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import com.chhliu.springboot.jpa.dto.PersonIDCardDto;
import com.chhliu.springboot.jpa.entity.QIDCard;
import com.chhliu.springboot.jpa.entity.QPerson;
import com.querydsl.core.QueryResults;
import com.querydsl.core.Tuple;
import com.querydsl.core.types.Predicate;
import com.querydsl.jpa.impl.JPAQuery;
import com.querydsl.jpa.impl.JPAQueryFactory;
@Component
public class PersonAndIDCardManager {
@Autowired
@PersistenceContext
private EntityManager entityManager;
private JPAQueryFactory queryFactory;
@PostConstruct
public void init() {
queryFactory = new JPAQueryFactory(entityManager);
}
/**
* Details:
*/
public List findAllPersonAndIdCard(){
Predicate predicate = (QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());
JPAQuery 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 findByDTO(){
Predicate predicate = (QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());
JPAQuery jpaQuery = queryFactory.select(QIDCard.iDCard.idNo, QPerson.person.address, QPerson.person.name)
.from(QIDCard.iDCard, QPerson.person)
.where(predicate);
List tuples = jpaQuery.fetch();
List dtos = new ArrayList();
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 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();
}
}
クエリ結果をDTOで出力した例では、クエリが終了すると、クエリ結果をDTOオブジェクトに手動で変換します.この方法はあまり優雅ではありません.QueryDSLは、次の例を参照してください.
/**
* Details: : Bean
*/
public List 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 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 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();
}
上記はいくつかのアイデアを提供しているだけで、もちろん@QueryProjectionを使って実現することもでき、非常に柔軟です.1対の複数の例:
package com.chhliu.springboot.jpa.repository;
import java.util.List;
import javax.annotation.PostConstruct;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import com.chhliu.springboot.jpa.entity.QOrder;
import com.chhliu.springboot.jpa.entity.QOrderItem;
import com.querydsl.core.Tuple;
import com.querydsl.core.types.Predicate;
import com.querydsl.jpa.impl.JPAQuery;
import com.querydsl.jpa.impl.JPAQueryFactory;
@Component
public class OrderAndOrderItemManager {
@Autowired
@PersistenceContext
private EntityManager entityManager;
private JPAQueryFactory queryFactory;
@PostConstruct
public void init() {
queryFactory = new JPAQueryFactory(entityManager);
}
/**
* Details: ,
*/
public List findOrderAndOrderItemByOrderName(String orderName){
//
Predicate predicate = QOrder.order.orderName.eq(orderName);
JPAQuery jpaQuery = queryFactory.select(QOrder.order, QOrderItem.orderItem)
.from(QOrder.order, QOrderItem.orderItem)
.where(QOrderItem.orderItem.order.id.intValue().eq(QOrder.order.id.intValue()), predicate);
//
return jpaQuery.fetch();
}
/**
* Details:
*/
public List findAllByOrderName(String orderName){
//
Predicate predicate = QOrder.order.orderName.eq(orderName);
JPAQuery jpaQuery = queryFactory.select(QOrder.order, QOrderItem.orderItem)
.from(QOrder.order, QOrderItem.orderItem)
.rightJoin(QOrder.order)
.on(QOrderItem.orderItem.order.id.intValue().eq(QOrder.order.id.intValue()));
jpaQuery.where(predicate);
//
return jpaQuery.fetch();
}
}
リンク
Querydsl Reference Guide
QueryDSLの使用
複雑なクエリーのカプセル化
Spring boot-jpaはQueryDSLを統合して複雑な操作を簡略化する
Spring Boot JPA-Querydslを使用して複雑な操作を処理
転載先:https://www.cnblogs.com/tonyq/p/7881142.html