前言
使用過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.jfrwli.cn/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
