-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathindex.html
289 lines (183 loc) · 33.6 KB
/
index.html
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
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title></title>
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
<meta property="og:type" content="website">
<meta property="og:title" content="">
<meta property="og:url" content="https://snuk87.github.io/index.html">
<meta property="og:site_name" content="">
<meta property="og:locale" content="en">
<meta name="twitter:card" content="summary">
<link rel="alternate" href="/atom.xml" title="" type="application/atom+xml">
<link rel="icon" href="/favicon.png">
<link href="//fonts.googleapis.com/css?family=Source+Code+Pro" rel="stylesheet" type="text/css">
<link rel="stylesheet" href="/css/style.css">
</head>
<body>
<div id="container">
<div id="wrap">
<header id="header">
<div id="banner"></div>
<div id="header-outer" class="outer">
<div id="header-title" class="inner">
<h1 id="logo-wrap">
<a href="/" id="logo"></a>
</h1>
</div>
<div id="header-inner" class="inner">
<nav id="main-nav">
<a id="main-nav-toggle" class="nav-icon"></a>
<a class="main-nav-link" href="/">Home</a>
<a class="main-nav-link" href="/archives">Archives</a>
</nav>
<nav id="sub-nav">
<a id="nav-rss-link" class="nav-icon" href="/atom.xml" title="RSS Feed"></a>
<a id="nav-search-btn" class="nav-icon" title="Search"></a>
</nav>
<div id="search-form-wrap">
<form action="//google.com/search" method="get" accept-charset="UTF-8" class="search-form"><input type="search" name="q" class="search-form-input" placeholder="Search"><button type="submit" class="search-form-submit"></button><input type="hidden" name="sitesearch" value="https://snuk87.github.io"></form>
</div>
</div>
</div>
</header>
<div class="outer">
<section id="main">
<article id="post-skip-locked-queue" class="article article-type-post" itemscope itemprop="blogPost">
<div class="article-meta">
<a href="/2019/11/18/skip-locked-queue/" class="article-date">
<time datetime="2019-11-17T15:07:35.000Z" itemprop="datePublished">2019-11-18</time>
</a>
</div>
<div class="article-inner">
<header class="article-header">
<h1 itemprop="name">
<a class="article-title" href="/2019/11/18/skip-locked-queue/">Implementing a SKIP LOCKED queue using spring-data</a>
</h1>
</header>
<div class="article-entry" itemprop="articleBody">
<h2 id="Introduction"><a href="#Introduction" class="headerlink" title="Introduction"></a>Introduction</h2><p>Recently I was looking for a solution to implement a simple job queue backed my a SQL database. The database already existed and I wanted to avoid to spin up an extra instance of Redis or RabbitMQ, also because it wasn’t a distributed system. I found this <a href="https://vladmihalcea.com/database-job-queue-skip-locked/" target="_blank" rel="noopener">article</a> by Vlad Mihalcea, where he explains how to use a feature called SKIP LOCKED to achieve what I needed. The example he provides is using plane Hibernate and JPA and I wanted to make a short example about how to do the same using Spring Data. The full example is available on <a href="https://github.com/SnuK87/skip-lock-queue" target="_blank" rel="noopener">Github</a>. To execute the example project you need a running instance of postgres or you can use the <code>docker-compose.yml</code> file to run a postgres container on your machine.</p>
<h2 id="Implementation"><a href="#Implementation" class="headerlink" title="Implementation"></a>Implementation</h2><p>First we have to create our entity class. The fields / columns <code>processedAt</code> and <code>processedBy</code> are <code>null</code> by default and should be updated after the job execution finished successfully.</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">@Entity</span></span><br><span class="line"><span class="meta">@Data</span></span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">ImportantJob</span> </span>{</span><br><span class="line"></span><br><span class="line"> <span class="meta">@Id</span></span><br><span class="line"> <span class="meta">@GeneratedValue</span></span><br><span class="line"> <span class="keyword">private</span> Long id;</span><br><span class="line"> <span class="keyword">private</span> Instant createdAt;</span><br><span class="line"> <span class="keyword">private</span> Instant processedAt;</span><br><span class="line"> <span class="keyword">private</span> String processedBy;</span><br><span class="line">}</span><br></pre></td></tr></table></figure>
<p>Next we have to create our repository and annotate the methods with the following annotations:<br><code>@Lock(LockModeType.PESSIMISTIC_WRITE)</code>: locks the entity as soon as a transaction updates it.<br><code>@QueryHints({ @QueryHint(name = "javax.persistence.lock.timeout", value = "-2") })</code>: equivalent to <code>LockOptions.NO_WAIT</code>. This will skip the locked row immediately.</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="class"><span class="keyword">interface</span> <span class="title">ImportantJobRepository</span> <span class="keyword">extends</span> <span class="title">JpaRepository</span><<span class="title">ImportantJob</span>, <span class="title">Long</span>> </span>{</span><br><span class="line"></span><br><span class="line"> <span class="meta">@Lock</span>(LockModeType.PESSIMISTIC_WRITE)</span><br><span class="line"> <span class="meta">@QueryHints</span>({ <span class="meta">@QueryHint</span>(name = <span class="string">"javax.persistence.lock.timeout"</span>, value = <span class="string">"-2"</span>) })</span><br><span class="line"> <span class="function">Optional<ImportantJob> <span class="title">findTopByProcessedAtIsNull</span><span class="params">()</span></span>;</span><br><span class="line">}</span><br></pre></td></tr></table></figure>
<p>In this example the application just sleeps for n seconds to simulate the job execution. Afterwards the execution time and the executing thread will will be saved in the entity. The row lock is only enabled for the duration of a transaction, therefore we have to annotate the method with <code>@Transactional</code>.</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">@Transactional</span></span><br><span class="line"><span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">doJob</span><span class="params">()</span> </span>{</span><br><span class="line"> log.info(<span class="string">"doJob..."</span>);</span><br><span class="line"> repo.findTopByProcessedAtIsNull().ifPresent(job -> {</span><br><span class="line"> <span class="keyword">try</span> {</span><br><span class="line"> Thread.sleep(ThreadLocalRandom.current().nextInt(<span class="number">6</span>) * <span class="number">1000L</span>);</span><br><span class="line"> job.setProcessedAt(Instant.now());</span><br><span class="line"> job.setProcessedBy(Thread.currentThread().getName());</span><br><span class="line"> } <span class="keyword">catch</span> (InterruptedException e) {</span><br><span class="line"> log.error(e.getMessage());</span><br><span class="line"> Thread.currentThread().interrupt();</span><br><span class="line"> }</span><br><span class="line"> });</span><br><span class="line">}</span><br></pre></td></tr></table></figure>
<p>Finally we can execute our jobs using multiple Threads.</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">run</span><span class="params">(String... args)</span> <span class="keyword">throws</span> Exception </span>{</span><br><span class="line"> <span class="comment">// init jobs</span></span><br><span class="line"> List<ImportantJob> jobs = IntStream.range(<span class="number">0</span>, <span class="number">20</span>)</span><br><span class="line"> .mapToObj(i -> <span class="keyword">new</span> ImportantJob(Instant.now()))</span><br><span class="line"> .collect(Collectors.toList());</span><br><span class="line"> repo.saveAll(jobs);</span><br><span class="line"></span><br><span class="line"> <span class="comment">// execute jobs with 2 concurrent threads</span></span><br><span class="line"> ExecutorService pool = Executors.newFixedThreadPool(<span class="number">2</span>);</span><br><span class="line"> IntStream.range(<span class="number">0</span>, <span class="number">20</span>).forEach(i -> pool.submit(() -> service.doJob()));</span><br><span class="line"> pool.shutdown();</span><br><span class="line">}</span><br></pre></td></tr></table></figure>
</div>
<footer class="article-footer">
<a data-url="https://snuk87.github.io/2019/11/18/skip-locked-queue/" data-id="ck3350m1x0000ywvt4e6v67rs" class="article-share-link">Share</a>
<ul class="article-tag-list" itemprop="keywords"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/jpa/" rel="tag">jpa</a></li><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/spring-data/" rel="tag">spring-data</a></li></ul>
</footer>
</div>
</article>
<article id="post-spring-data-querydsl" class="article article-type-post" itemscope itemprop="blogPost">
<div class="article-meta">
<a href="/2019/11/10/spring-data-querydsl/" class="article-date">
<time datetime="2019-11-10T05:47:49.000Z" itemprop="datePublished">2019-11-10</time>
</a>
</div>
<div class="article-inner">
<header class="article-header">
<h1 itemprop="name">
<a class="article-title" href="/2019/11/10/spring-data-querydsl/">Spring Data JPA with Querydsl</a>
</h1>
</header>
<div class="article-entry" itemprop="articleBody">
<h2 id="Introduction"><a href="#Introduction" class="headerlink" title="Introduction"></a>Introduction</h2><p>Spring-Data makes it really easy to implement the persistence layer of your application but when your domain model becomes more complex then also your queries will and you can’t rely on the spring data query derivation mechanism anymore. Therefore you have to implement JPQL or native queries by using the <code>@Query</code> annotation. But there are some major drawbacks like:</p>
<ul>
<li><strong>Error-prone</strong>: Handwrititen queries are a source for typos and cumbersome to write/read because of the not optimal formatting of multiline strings in Java (Hopefully will become better with <a href="https://openjdk.java.net/jeps/355" target="_blank" rel="noopener">JEP-355</a>). </li>
<li><strong>No type safety</strong>: Type safety will be lost when writing queries as strings. This can lead to errors at runtime.</li>
<li><strong>Hard to maintain</strong>: When we change a column of an entity we also have to change the query string. The IDE or the compiler are not able to detect those changes, therefore it is easy to run into errors at runtime.</li>
</ul>
<p>To avoid those problems I will explain how to use a framework called <a href="http://www.querydsl.com/" target="_blank" rel="noopener">Querydsl</a> together with spring-data-jpa. The complete code of the demo application is available on <a href="https://github.com/SnuK87/querydsl-playground" target="_blank" rel="noopener">github</a>.</p>
<blockquote>
<p>Querydsl is a framework which enables the construction of statically typed SQL-like queries. Instead of writing queries as inline strings or externalizing them into XML files they can be constructed via a fluent API like Querydsl. </p>
</blockquote>
<h2 id="Setup"><a href="#Setup" class="headerlink" title="Setup"></a>Setup</h2><p>In order to use Querydsl in your project you just have to add the following dependencies and a maven plugin to you <code>pom.xml</code>.</p>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="tag"><<span class="name">dependency</span>></span></span><br><span class="line"> <span class="tag"><<span class="name">groupId</span>></span>com.querydsl<span class="tag"></<span class="name">groupId</span>></span></span><br><span class="line"> <span class="tag"><<span class="name">artifactId</span>></span>querydsl-apt<span class="tag"></<span class="name">artifactId</span>></span></span><br><span class="line"> <span class="tag"><<span class="name">scope</span>></span>provided<span class="tag"></<span class="name">scope</span>></span> </span><br><span class="line"><span class="tag"></<span class="name">dependency</span>></span></span><br><span class="line"></span><br><span class="line"><span class="tag"><<span class="name">dependency</span>></span></span><br><span class="line"> <span class="tag"><<span class="name">groupId</span>></span>com.querydsl<span class="tag"></<span class="name">groupId</span>></span></span><br><span class="line"> <span class="tag"><<span class="name">artifactId</span>></span>querydsl-jpa<span class="tag"></<span class="name">artifactId</span>></span></span><br><span class="line"><span class="tag"></<span class="name">dependency</span>></span></span><br></pre></td></tr></table></figure>
<p>The maven APT plugin with the <code>JPAAnnotationProcessor</code> scans your project for <code>@Entity</code> annotated classes and generates a corresponding query type that is prefixed with a <code>Q. Run</code>mvn clean compile` or build the project with eclipse to execute the plugin and generate the query types.</p>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="tag"><<span class="name">plugin</span>></span></span><br><span class="line"> <span class="tag"><<span class="name">groupId</span>></span>com.mysema.maven<span class="tag"></<span class="name">groupId</span>></span></span><br><span class="line"> <span class="tag"><<span class="name">artifactId</span>></span>apt-maven-plugin<span class="tag"></<span class="name">artifactId</span>></span></span><br><span class="line"> <span class="tag"><<span class="name">version</span>></span>1.1.3<span class="tag"></<span class="name">version</span>></span></span><br><span class="line"> <span class="tag"><<span class="name">executions</span>></span></span><br><span class="line"> <span class="tag"><<span class="name">execution</span>></span></span><br><span class="line"> <span class="tag"><<span class="name">goals</span>></span></span><br><span class="line"> <span class="tag"><<span class="name">goal</span>></span>process<span class="tag"></<span class="name">goal</span>></span></span><br><span class="line"> <span class="tag"></<span class="name">goals</span>></span></span><br><span class="line"> <span class="tag"><<span class="name">configuration</span>></span></span><br><span class="line"> <span class="tag"><<span class="name">outputDirectory</span>></span>target/generated-sources/java<span class="tag"></<span class="name">outputDirectory</span>></span></span><br><span class="line"> <span class="tag"><<span class="name">processor</span>></span>com.querydsl.apt.jpa.JPAAnnotationProcessor<span class="tag"></<span class="name">processor</span>></span></span><br><span class="line"> <span class="tag"></<span class="name">configuration</span>></span></span><br><span class="line"> <span class="tag"></<span class="name">execution</span>></span></span><br><span class="line"> <span class="tag"></<span class="name">executions</span>></span></span><br><span class="line"><span class="tag"></<span class="name">plugin</span>></span></span><br></pre></td></tr></table></figure>
<p>To write queries with Querydsl we need to autowire an instance of <code>JPAQueryFactory</code> into our repository implementation class. For that we can simply create a configuration class and create a <code>@Bean</code>.</p>
<h2 id="Configuration"><a href="#Configuration" class="headerlink" title="Configuration"></a>Configuration</h2><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">@Configuration</span></span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">QueryDslConfiguration</span> </span>{</span><br><span class="line"></span><br><span class="line"> <span class="meta">@PersistenceContext</span></span><br><span class="line"> <span class="keyword">private</span> EntityManager entityManager;</span><br><span class="line"></span><br><span class="line"> <span class="meta">@Bean</span></span><br><span class="line"> <span class="function"><span class="keyword">public</span> JPAQueryFactory <span class="title">jpaQueryFactory</span><span class="params">()</span> </span>{</span><br><span class="line"> <span class="keyword">return</span> <span class="keyword">new</span> JPAQueryFactory(entityManager);</span><br><span class="line"> }</span><br><span class="line">}</span><br></pre></td></tr></table></figure>
<h2 id="Domain-model"><a href="#Domain-model" class="headerlink" title="Domain model"></a>Domain model</h2><p>Our domain model consists of four entities:</p>
<ul>
<li>Organization: An organization contains many departments</li>
<li>Department: A department belongs to one organization and has many employees</li>
<li>Employee: An employee is working for one department and has many roles</li>
<li>Role: Each employee can have many roles</li>
</ul>
<p><img src="/images/er.PNG" alt="Domain model"></p>
<h2 id="Implementation"><a href="#Implementation" class="headerlink" title="Implementation"></a>Implementation</h2><p>To combine the magic of the spring <code>JpaRepository</code> and Querydsl we have to create a new interface for our Querydsl powered queries first.</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="class"><span class="keyword">interface</span> <span class="title">EmployeeRepositorySupport</span> </span>{</span><br><span class="line"></span><br><span class="line"> <span class="function">List<Employee> <span class="title">findByOrganizationId</span><span class="params">(Long organizationId)</span></span>;</span><br><span class="line"> </span><br><span class="line"> <span class="function">List<Employee> <span class="title">findByDepartmentIdAndRoleName</span><span class="params">(Long departmentId, String roleName)</span></span>;</span><br><span class="line"> </span><br><span class="line"> <span class="function">List<EmployeeDto> <span class="title">findByOrganizationIdAndRoleName</span><span class="params">(Long organizationId, String roleName)</span></span>;</span><br><span class="line"> </span><br><span class="line">}</span><br></pre></td></tr></table></figure>
<p>Then we can create a new interface that extends the <code>JpaRepository</code> and our created iterface.</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="class"><span class="keyword">interface</span> <span class="title">EmployeeRepository</span> <span class="keyword">extends</span> <span class="title">JpaRepository</span><<span class="title">Employee</span>, <span class="title">Long</span>>, <span class="title">EmployeeRepositorySupport</span> </span>{</span><br><span class="line"></span><br><span class="line">}</span><br></pre></td></tr></table></figure>
<p>This way we can call the auto generated queries by spring-data and our typesafe Querydsl queries from the same interface. Last but not least we to provide an implementation for our repository.</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">@Repository</span></span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">EmployeeRepositorySupportImpl</span> <span class="keyword">extends</span> <span class="title">QuerydslRepositorySupport</span> <span class="keyword">implements</span> <span class="title">EmployeeRepositorySupport</span> </span>{</span><br><span class="line"></span><br><span class="line"> <span class="keyword">private</span> <span class="keyword">final</span> JPAQueryFactory queryFactory;</span><br><span class="line"></span><br><span class="line"> <span class="function"><span class="keyword">public</span> <span class="title">EmployeeRepositorySupportImpl</span><span class="params">(JPAQueryFactory queryFactory)</span> </span>{</span><br><span class="line"> <span class="keyword">super</span>(Employee<span class="class">.<span class="keyword">class</span>)</span>;</span><br><span class="line"> <span class="keyword">this</span>.queryFactory = queryFactory;</span><br><span class="line"> }</span><br><span class="line"></span><br><span class="line"> <span class="meta">@Override</span></span><br><span class="line"> <span class="function"><span class="keyword">public</span> List<Employee> <span class="title">findByOrganizationId</span><span class="params">(Long organizationId)</span> </span>{</span><br><span class="line"> <span class="keyword">return</span> queryFactory.select(employee).from(employee, department)</span><br><span class="line"> .where(department.organization.id.eq(organizationId)</span><br><span class="line"> .and(employee.department.eq(department)))</span><br><span class="line"> .fetch();</span><br><span class="line"> }</span><br><span class="line"> </span><br><span class="line"> <span class="meta">@Override</span></span><br><span class="line"> <span class="function"><span class="keyword">public</span> List<Employee> <span class="title">findByDepartmentIdAndRoleName</span><span class="params">(Long departmentId, String roleName)</span> </span>{</span><br><span class="line"> <span class="keyword">return</span> queryFactory.select(employee).from(employee, department)</span><br><span class="line"> .innerJoin(employee.roles, role)</span><br><span class="line"> .where(department.id.eq(departmentId)</span><br><span class="line"> .and(role.name.eq(roleName))</span><br><span class="line"> .and(employee.roles.any().eq(role))</span><br><span class="line"> .and(employee.department.eq(department)))</span><br><span class="line"> .fetch();</span><br><span class="line"> }</span><br><span class="line"> </span><br><span class="line"> <span class="meta">@Override</span></span><br><span class="line"> <span class="function"><span class="keyword">public</span> List<EmployeeDto> <span class="title">findByOrganizationIdAndRoleName</span><span class="params">(Long organizationId, String roleName)</span> </span>{</span><br><span class="line"> <span class="keyword">return</span> <span class="keyword">new</span> ArrayList<>(queryFactory.select(employee).from(employee, department)</span><br><span class="line"> .innerJoin(employee.roles, role)</span><br><span class="line"> .where(department.organization.id.eq(organizationId)</span><br><span class="line"> .and(employee.department.eq(department))</span><br><span class="line"> .and(role.name.eq(roleName))</span><br><span class="line"> .and(employee.roles.any().eq(role)))</span><br><span class="line"> .transform(GroupBy.groupBy(employee.id)</span><br><span class="line"> .as(<span class="keyword">new</span> QEmployeeDto(employee.id, employee.email, employee.name, department.id)))</span><br><span class="line"> .values());</span><br><span class="line"> }</span><br><span class="line">}</span><br></pre></td></tr></table></figure>
<p>The implementing class extends the <code>QuerydslRepositorySupport</code> class and gets an instance of <code>JPAQueryFactory</code> injected. Now we can write type safe queries by using the generated query types (<code>QEmployee.employee</code>, …).</p>
<p>The method <code>findByOrganizationIdAndRoleName</code> shows an example of how to use projectons with querydsl. Instead of returning the entity type the method returns a DTO type. In order to use the DTO as query type we have to annotate the constructor with <code>@QueryProjection</code> like in the following example:</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">@Data</span></span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">EmployeeDto</span> </span>{</span><br><span class="line"> <span class="keyword">private</span> Long id;</span><br><span class="line"> <span class="keyword">private</span> String email;</span><br><span class="line"> <span class="keyword">private</span> String name;</span><br><span class="line"> <span class="keyword">private</span> Long departmentId;</span><br><span class="line"> </span><br><span class="line"> <span class="meta">@QueryProjection</span></span><br><span class="line"> <span class="function"><span class="keyword">public</span> <span class="title">EmployeeDto</span><span class="params">(Long id, String email, String name, Long departmentId)</span> </span>{</span><br><span class="line"> <span class="keyword">this</span>.id = id;</span><br><span class="line"> <span class="keyword">this</span>.email = email;</span><br><span class="line"> <span class="keyword">this</span>.name = name;</span><br><span class="line"> <span class="keyword">this</span>.departmentId = departmentId;</span><br><span class="line"> }</span><br><span class="line">}</span><br></pre></td></tr></table></figure>
</div>
<footer class="article-footer">
<a data-url="https://snuk87.github.io/2019/11/10/spring-data-querydsl/" data-id="ck3350m230001ywvt43idbklb" class="article-share-link">Share</a>
<ul class="article-tag-list" itemprop="keywords"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/jpa/" rel="tag">jpa</a></li><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/querydsl/" rel="tag">querydsl</a></li><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/spring-data/" rel="tag">spring-data</a></li></ul>
</footer>
</div>
</article>
</section>
<aside id="sidebar">
<div class="widget-wrap">
<h3 class="widget-title">Tags</h3>
<div class="widget">
<ul class="tag-list" itemprop="keywords"><li class="tag-list-item"><a class="tag-list-link" href="/tags/jpa/" rel="tag">jpa</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/querydsl/" rel="tag">querydsl</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/spring-data/" rel="tag">spring-data</a></li></ul>
</div>
</div>
<div class="widget-wrap">
<h3 class="widget-title">Tag Cloud</h3>
<div class="widget tagcloud">
<a href="/tags/jpa/" style="font-size: 20px;">jpa</a> <a href="/tags/querydsl/" style="font-size: 10px;">querydsl</a> <a href="/tags/spring-data/" style="font-size: 20px;">spring-data</a>
</div>
</div>
<div class="widget-wrap">
<h3 class="widget-title">Archives</h3>
<div class="widget">
<ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/11/">November 2019</a></li></ul>
</div>
</div>
<div class="widget-wrap">
<h3 class="widget-title">Recent Posts</h3>
<div class="widget">
<ul>
<li>
<a href="/2019/11/18/skip-locked-queue/">Implementing a SKIP LOCKED queue using spring-data</a>
</li>
<li>
<a href="/2019/11/10/spring-data-querydsl/">Spring Data JPA with Querydsl</a>
</li>
</ul>
</div>
</div>
</aside>
</div>
<footer id="footer">
<div class="outer">
<div id="footer-info" class="inner">
© 2019 Dennis Griese<br>
Powered by <a href="http://hexo.io/" target="_blank">Hexo</a>
</div>
</div>
</footer>
</div>
<nav id="mobile-nav">
<a href="/" class="mobile-nav-link">Home</a>
<a href="/archives" class="mobile-nav-link">Archives</a>
</nav>
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.0.3/jquery.min.js"></script>
<link rel="stylesheet" href="/fancybox/jquery.fancybox.css">
<script src="/fancybox/jquery.fancybox.pack.js"></script>
<script src="/js/script.js"></script>
</div>
</body>
</html>