详解spring boot jpa整合QueryDSL来简化复杂操作
前言
使用过spring data jpa的同学,都很清楚,对于复杂的sql查询,处理起来还是比较复杂的,而本文中的querydsl就是用来简化jpa操作的。
querydsl定义了一种常用的静态类型语法,用于在持久域模型数据之上进行查询。jdo和jpa是querydsl的主要集成技术。本文旨在介绍如何使用querydsl与jpa组合使用。jpa的querydsl是jpql和criteria查询的替代方法。querydsl仅仅是一个通用的查询框架,专注于通过java api构建类型安全的sql查询。
要想使用querydsl,需要做两个前提操作:
1、pom文件中,加入依赖
<!--query dsl --> <dependency> <groupid>com.querydsl</groupid> <artifactid>querydsl-jpa</artifactid> </dependency> <dependency> <groupid>com.querydsl</groupid> <artifactid>querydsl-apt</artifactid> <scope>provided</scope> </dependency>
2、pom文件中,加入编译插件
<plugin> <groupid>com.mysema.maven</groupid> <artifactid>apt-maven-plugin</artifactid> <version>1.1.3</version> <executions> <execution> <goals> <goal>process</goal> </goals> <configuration> <outputdirectory>target/generated-sources/java</outputdirectory> <processor>com.querydsl.apt.jpa.jpaannotationprocessor</processor> </configuration> </execution> </executions> </plugin>
该插件会查找使用javax.persistence.entity注解的域类型,并为它们生成对应的查询类型。下面以user实体类来说明,生成的查询类型如下:
package com.chhliu.springboot.jpa.entity; 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; /** * quser is a querydsl query type for user */ @generated("com.querydsl.codegen.entityserializer") public class quser extends entitypathbase<user> { private static final long serialversionuid = 1153899872l; public static final quser user = new quser("user"); public final stringpath address = createstring("address"); public final numberpath<integer> age = createnumber("age", integer.class); public final numberpath<integer> id = createnumber("id", integer.class); public final stringpath name = createstring("name"); public quser(string variable) { super(user.class, forvariable(variable)); } public quser(path<? extends user> path) { super(path.gettype(), path.getmetadata()); } public quser(pathmetadata metadata) { super(user.class, metadata); } }
我们建立好实体类之后,然后运行mvn clean complie命令,就会在
<outputdirectory>target/generated-sources/java</outputdirectory>
目录下生成对应的查询类型。然后将生成的类都拷贝到项目中,即可。
本文涉及到的entity如下:
package com.chhliu.springboot.jpa.entity; import java.io.serializable; import javax.persistence.entity; import javax.persistence.generatedvalue; import javax.persistence.generationtype; import javax.persistence.id; import javax.persistence.table; @entity @table(name="t_user") public class user implements serializable{ /** * */ private static final long serialversionuid = 1l; @id() @generatedvalue(strategy = generationtype.auto) private int id; private string name; private string address; private int age; …………省略getter,setter方法………… /** * attention: * details:方便查看测试结果 * @author chhliu */ @override public string tostring() { return "user [id=" + id + ", name=" + name + ", address=" + address + ", age=" + age + "]"; } }
上面的这个实体类,主要用于单表操作。
package com.chhliu.springboot.jpa.entity; import javax.persistence.cascadetype; import javax.persistence.entity; import javax.persistence.generatedvalue; import javax.persistence.id; import javax.persistence.onetoone; import javax.persistence.table; /** * 描述:todo * @author chhliu */ @entity @table(name="person") public class person { @id @generatedvalue private integer id; private string name; private string address; @onetoone(mappedby="person", cascade={cascadetype.persist, cascadetype.remove, cascadetype.merge}) private idcard idcard; …………省略getter,setter方法………… @override public string tostring() { return "person [id=" + id + ", name=" + name + ", address=" + address + ", idcard=" + idcard + "]"; } }
package com.chhliu.springboot.jpa.entity; import javax.persistence.cascadetype; import javax.persistence.entity; import javax.persistence.fetchtype; import javax.persistence.generatedvalue; import javax.persistence.id; import javax.persistence.onetoone; import javax.persistence.table; /** * 描述: * @author chhliu */ @entity @table(name="idcard") public class idcard { @id @generatedvalue private integer id; private string idno; @onetoone(cascade={cascadetype.merge, cascadetype.remove, cascadetype.persist}, fetch=fetchtype.eager) private person person; …………省略getter,setter方法………… @override public string tostring() { return "idcard [id=" + id + ", idno=" + idno + ", person=" + person + "]"; } }
上面两个entity主要用于一对一关系的示例操作
package com.chhliu.springboot.jpa.entity; import java.util.list; import javax.persistence.cascadetype; import javax.persistence.column; import javax.persistence.entity; import javax.persistence.fetchtype; import javax.persistence.generatedvalue; import javax.persistence.id; import javax.persistence.onetomany; import javax.persistence.table; /** * 描述:order实体类 * @author chhliu */ @entity @table(name="order_c") public class order { @id @generatedvalue @column(name="id") private integer id; @column(length=20, name="order_name") private string ordername; @column(name="count") private integer count; @onetomany(mappedby = "order",cascade={cascadetype.persist,cascadetype.remove},fetch = fetchtype.eager) private list<orderitem> orderitems; …………省略getter,setter方法………… }
package com.chhliu.springboot.jpa.entity; import javax.persistence.cascadetype; import javax.persistence.column; import javax.persistence.entity; import javax.persistence.fetchtype; import javax.persistence.generatedvalue; import javax.persistence.id; import javax.persistence.joincolumn; import javax.persistence.manytoone; import javax.persistence.table; /** * 描述:orderitem实体类 * @author chhliu */ @entity @table(name="order_item") public class orderitem { @id @generatedvalue @column(name="id", nullable=false) private integer id; @column(name="item_name", length=20) private string itemname; @column(name="price") private integer price; @manytoone(cascade={cascadetype.persist,cascadetype.remove, cascadetype.merge}, fetch=fetchtype.eager) @joincolumn(name = "order_id") private order order; …………省略getter,setter方法………… }
上面两个entity用于展示一对多关系的示例操作。
首先,我们来看单表操作
1、使用spring data jpa
要想使用spring data jpa提供的querydsl功能,很简单,直接继承接口即可。spring data jpa中提供了querydslpredicateexecutor接口,用于支持querydsl的查询操作接口,如下:
package com.chhliu.springboot.jpa.repository; import org.springframework.data.jpa.repository.jparepository; import org.springframework.data.querydsl.querydslpredicateexecutor; import com.chhliu.springboot.jpa.entity.user; public interface userrepositorydls extends jparepository<user, integer>, querydslpredicateexecutor<user>{ // 继承接口 }
querydslpredicateexecutor接口提供了如下方法:
public interface querydslpredicateexecutor<t> { t findone(predicate predicate); iterable<t> findall(predicate predicate); iterable<t> findall(predicate predicate, sort sort); iterable<t> findall(predicate predicate, orderspecifier<?>... orders); iterable<t> findall(orderspecifier<?>... orders); page<t> findall(predicate predicate, pageable pageable); long count(predicate predicate); boolean exists(predicate predicate); }
以上方法的使用和spring data jpa中的其他接口使用方法类似,详情请参考:
测试如下:
public user finduserbyusername(final string username){ /** * 该例是使用spring data querydsl实现 */ quser quser = quser.user; predicate predicate = quser.name.eq(username);// 根据用户名,查询user表 return repository.findone(predicate); }
对应的sql如下:
单表操作示例代码如下:
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<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); } }
多表操作示例(一对一)如下:
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<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(); } }
上面将查询结果以dto的方式输出的示例中,在查询结束后,将查询结果手动的转换成了dto对象,这种方式其实不太优雅,querydsl给我们提供了更好的方式,见下面的示例:
/** * 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(); }
上面只是提供了几种思路,当然,还可以使用@queryprojection来实现,非常灵活。
一对多示例:
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<tuple> findorderandorderitembyordername(string ordername){ //添加查询条件 predicate predicate = qorder.order.ordername.eq(ordername); jpaquery<tuple> 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<tuple> findallbyordername(string ordername){ //添加查询条件 predicate predicate = qorder.order.ordername.eq(ordername); jpaquery<tuple> 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大大的简化了我们的操作
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持。