前言
使用过spring data jpa的同学，都很清楚，对于复杂的sql查询，处理起来还是比较复杂的，而本文中的querydsl就是用来简化jpa操作的。
querydsl定义了一种常用的静态类型语法，用于在持久域模型数据之上进行查询。jdo和jpa是querydsl的主要集成技术。本文旨在介绍如何使用querydsl与jpa组合使用。jpa的querydsl是jpql和criteria查询的替代方法。querydsl仅仅是一个通用的查询框架，专注于通过java api构建类型安全的sql查询。
要想使用querydsl，需要做两个前提操作：
1、pom文件中，加入依赖
|
1
2
3
4
5
6
7
8
9
10
|
<!--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文件中，加入编译插件
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
<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实体类来说明，生成的查询类型如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
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命令，就会在
|
1
|
<outputdirectory>target/generated-sources/java</outputdirectory> |
目录下生成对应的查询类型。然后将生成的类都拷贝到项目中，即可。
本文涉及到的entity如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
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 + "]"; } } |
上面的这个实体类，主要用于单表操作。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
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 + "]"; } } |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
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主要用于一对一关系的示例操作
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
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方法………… } |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
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的查询操作接口，如下：
|
1
2
3
4
5
6
7
|
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接口提供了如下方法：
|
1
2
3
4
5
6
7
8
9
10
|
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中的其他接口使用方法类似，详情请参考：http://www.zzvips.com/article/157455.html
测试如下：
|
1
2
3
4
5
6
7
8
|
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如下：
复制代码 代码如下:
select user0_.id as id1_5_, user0_.address as address2_5_, user0_.age as age3_5_, user0_.name as name4_5_ from t_user user0_ where user0_.name=?
单表操作示例代码如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
|
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@transactionalpublic 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); } } |
多表操作示例(一对一)如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
|
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; @componentpublic 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给我们提供了更好的方式，见下面的示例：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
/** * 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来实现，非常灵活。
一对多示例：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
|
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; @componentpublic 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大大的简化了我们的操作
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。
原文链接：https://blog.csdn.net/liuchuanhong1/article/details/70244261
