-
Notifications
You must be signed in to change notification settings - Fork 7
Expand file tree
/
Copy pathnative-sql.html
More file actions
392 lines (365 loc) · 19.9 KB
/
native-sql.html
File metadata and controls
392 lines (365 loc) · 19.9 KB
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
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
<!doctype html>
<html lang="en">
<head>
<title>Native SQL / findNative | Query | Ebean</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="shortcut icon" href="/images/favicon.ico">
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto|Source+Sans+Pro|Ubuntu&display=swap">
<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.1.0/css/all.css" integrity="sha384-lKuwvrZot6UHsBSfcMvOkWwlCMgc0TaWr+30HWe3a4ltaBwTZhyTEggF5tJv8tbt" crossorigin="anonymous">
<link rel="stylesheet" href="/css/reset3.css">
<link rel="stylesheet" href="/css/site3.css">
<link rel="stylesheet" href="/css/pygments3.css">
</head>
<body>
<div id="main">
<div id="banner">
<header>
<nav id="top">
<h1 id="breadcrumb">
<a class="nav-logo" href="/"><img src="/images/logo-200.png" height="35"></a> <a href="/docs">Documentation</a><span class="sep"> / </span><a href="/docs/query/">Query</a><span class="sep"> / </span><span class="last">Native SQL</span>
</h1>
<ul>
<li><a onclick="toggleTheme();" title="switch dark light theme"><i class="fas fa-adjust"></i></a></li>
</ul>
</nav>
</header>
</div>
<div class="grid grid-docs">
<aside>
<nav class="side">
<ul>
<li class="nav0 ">
<a href="/docs/getting-started">Getting started</a>
</li>
<li class="nav0 ">
<a href="/docs/intro">Introduction</a>
</li>
<li class="nav0 active">
<a class="active" href="/docs">Documentation</a>
<ul>
<li class="nav1 ">
<a href="/docs/agents">AI Agents</a>
</li>
<li class="nav1 ">
<a href="/docs/best-practice">Best practice</a>
</li>
<li class="nav1 active">
<a class="active" href="/docs/query">Query</a>
<ul class="nav">
<li >
<a href="/docs/query/query-beans">Query beans</a>
</li>
<li >
<a href="/docs/query/where">Where</a>
</li>
<li >
<a href="/docs/query/expressions">Expressions</a>
</li>
<li >
<a href="/docs/query/orderBy">OrderBy</a>
</li>
<li >
<a href="/docs/query/select">Select</a>
</li>
<li >
<a href="/docs/query/fetch">Fetch</a>
</li>
<li >
<a href="/docs/query/fetchgroup">FetchGroup</a>
</li>
<li >
<a href="/docs/query/filterMany">FilterMany</a>
</li>
<li >
<a href="/docs/query/aggregation">Aggregation</a>
</li>
<li >
<a href="/docs/query/having">Having</a>
</li>
<li class="active">
<a class="active" href="/docs/query/native-sql">Native Sql</a>
</li>
<li >
<a href="/docs/query/rawSql">RawSql</a>
</li>
<li >
<a href="/docs/query/update">Update query</a>
</li>
<li >
<a href="/docs/query/delete">Delete query</a>
</li>
<li >
<a href="/docs/query/dtoquery">DtoQuery</a>
</li>
<li >
<a href="/docs/query/sqlquery">SqlQuery</a>
</li>
<li >
<a href="/docs/query/sqlupdate">SqlUpdate</a>
</li>
<li >
<a href="/docs/query/callablesql">CallableSql</a>
</li>
</ul>
</li>
<li class="nav1 ">
<a href="/docs/persist">Persist</a>
</li>
<li class="nav1 ">
<a href="/docs/transactions">Transactions</a>
</li>
<li class="nav1 ">
<a href="/docs/mapping">Mapping</a>
</li>
<li class="nav1 ">
<a href="/docs/ddl-generation">DDL & Migrations</a>
</li>
<li class="nav1 ">
<a href="/docs/logging">Logging</a>
</li>
<li class="nav1 ">
<a href="/docs/testing">Testing</a>
</li>
<li class="nav1 ">
<a href="/docs/read-replicas">Read Replicas</a>
</li>
<li class="nav1 ">
<a href="/docs/database">Database platforms</a>
</li>
<li class="nav1 ">
<a href="/docs/multi-database">Multiple databases</a>
</li>
<li class="nav1 ">
<a href="/docs/kotlin">Kotlin</a>
</li>
<li><a href="/docs/tuning">Tuning</a></li>
<li class="nav1 ">
<a href="/docs/features">Features</a>
</li>
</ul>
</li>
<li class="nav0 ">
<a href="/support">Getting help</a>
</li>
<li class="nav0 ">
<a target="_blank" href="/apidoc/13">API Javadoc</a>
</li>
<li class="nav0 ">
<a href="/videos">Videos</a>
</li>
<li class="nav0 ">
<a href="/docs/upgrading">Upgrading</a>
</li>
<li class="nav0 ">
<a href="/docs/deprecated">Deprecated</a>
</li>
<li class="nav0 ">
<a href="/releases">Releases</a>
</li>
</ul>
</nav>
</aside>
<article>
<form action="https://www.google.com/search" method="get" class="inline-form">
<input type="hidden" name="as_sitesearch" value="ebean.io">
<div id="page-search">
<div class="input-group">
<input class="frm" name="q" id="searchinput" type="text" placeholder="Search... (press 's' to focus)" data-placeholder-focus="Search... (use '↑', '↓' and '⏎' to select results)" data-placeholder-blur="Search... (press 's' to focus)" autocomplete="off">
<div class="input-group-btn">
<button class="frm" type="submit"><i class="fas fa-search"></i></button>
</div>
</div>
<div id="page-search-results" style="display: none;">
<ul id="search-results-container" class="search-results"><li class=" active"><a href="/docs" title="Docs"><span style="color:#777;">Docs</span> Documentation </a></li><li class=""><small style="color:#999;">And 101 more...</small></li></ul>
</div>
</div>
</form>
<h2>Native SQL - findNative</h2>
<p>
With <code>findNative</code> we supply the SQL. The SQL can contain positioned parameters with <code>?</code>
or named parameters like <code>:foo</code>
</p>
<h5>Example - positioned parameters</h5>
<div class="syntax java"><div class="highlight"><pre><span></span><span class="n">String</span> <span class="n">sql</span> <span class="o">=</span> <span class="s">"select id, name from customer where name like ?"</span><span class="o">;</span>
<span class="n">Customer</span> <span class="n">customer</span> <span class="o">=</span> <span class="n">DB</span><span class="o">.</span><span class="na">findNative</span><span class="o">(</span><span class="n">Customer</span><span class="o">.</span><span class="na">class</span><span class="o">,</span> <span class="n">sql</span><span class="o">)</span>
<span class="o">.</span><span class="na">setParameter</span><span class="o">(</span><span class="s">"Jo%"</span><span class="o">)</span>
<span class="o">.</span><span class="na">findOne</span><span class="o">();</span>
</pre></div>
</div>
<h5>Example - named parameters</h5>
<div class="syntax java"><div class="highlight"><pre><span></span><span class="n">String</span> <span class="n">sql</span> <span class="o">=</span> <span class="s">"select id, name from customer where name like :name order by name desc"</span><span class="o">;</span>
<span class="n">List</span><span class="o"><</span><span class="n">Customer</span><span class="o">></span> <span class="n">customers</span> <span class="o">=</span> <span class="n">DB</span><span class="o">.</span><span class="na">findNative</span><span class="o">(</span><span class="n">Customer</span><span class="o">.</span><span class="na">class</span><span class="o">,</span> <span class="n">sql</span><span class="o">)</span>
<span class="o">.</span><span class="na">setParameter</span><span class="o">(</span><span class="s">"name"</span><span class="o">,</span> <span class="s">"Jo%"</span><span class="o">)</span>
<span class="o">.</span><span class="na">findList</span><span class="o">();</span>
</pre></div>
</div>
<p>
With <em>findNative</em> the columns are automatically mapped to bean properties.
</p>
<p>
With the above examples we are selecting <b>some</b> columns and as a result getting
<b>partially populated entity beans</b> (which is good - we should only fetch things out of the DB
that we need).
</p>
<p>
We can use <code>select *</code> if we desire all the columns but this is not generally recommended.
</p>
<h5>Example - select *</h5>
<div class="syntax java"><div class="highlight"><pre><span></span><span class="n">String</span> <span class="n">sql</span> <span class="o">=</span> <span class="s">"select * from customer where name like :name order by name desc"</span><span class="o">;</span>
<span class="n">List</span><span class="o"><</span><span class="n">Customer</span><span class="o">></span> <span class="n">customers</span> <span class="o">=</span> <span class="n">DB</span><span class="o">.</span><span class="na">findNative</span><span class="o">(</span><span class="n">Customer</span><span class="o">.</span><span class="na">class</span><span class="o">,</span> <span class="n">sql</span><span class="o">)</span>
<span class="o">.</span><span class="na">setParameter</span><span class="o">(</span><span class="s">"name"</span><span class="o">,</span> <span class="s">"Jo%"</span><span class="o">)</span>
<span class="o">.</span><span class="na">findList</span><span class="o">();</span>
</pre></div>
</div>
<h2>Column mapping</h2>
<p>
The way column mapping works is that when a query is first run the JDBC meta data is read for the
columns in the resultSet and then these columns are automatically mapped to bean properties using
the naming convention.
</p>
<p>
This only needs to be done for the first execution of the query. How the query is mapped to
beans is cached so we only need to read the JDBC resultSet meta data once per query.
</p>
<p>
For columns that do not conform to the naming convention, we use sql column alias.
</p>
<h5>Example - column alias</h5>
<div class="syntax java"><div class="highlight"><pre><span></span><span class="c1">// using sql column alias</span>
<span class="n">String</span> <span class="n">sql</span> <span class="o">=</span> <span class="s">"select id, fname as first_name, lname as last_name "</span> <span class="o">+</span>
<span class="s">"from contact where lname like ?"</span><span class="o">;</span>
<span class="n">List</span><span class="o"><</span><span class="n">Contact</span><span class="o">></span> <span class="n">contacts</span> <span class="o">=</span> <span class="n">DB</span><span class="o">.</span><span class="na">findNative</span><span class="o">(</span><span class="n">Contact</span><span class="o">.</span><span class="na">class</span><span class="o">,</span> <span class="n">sql</span><span class="o">)</span>
<span class="o">.</span><span class="na">setParameter</span><span class="o">(</span><span class="mi">1</span><span class="o">,</span> <span class="s">"B%"</span><span class="o">)</span>
<span class="o">.</span><span class="na">findList</span><span class="o">();</span>
</pre></div>
</div>
<h3>Unmapped columns</h3>
<p>
Sometimes we want to specify columns in the resultSet for the <code>order by</code>
clause that can not be mapped to a bean property. In this case these properties are
ignored.
</p>
<h5>Example - an unmapped column for order by</h5>
<div class="syntax java"><div class="highlight"><pre><span></span><span class="n">String</span> <span class="n">sql</span> <span class="o">=</span> <span class="s">"select id, name, "</span> <span class="o">+</span>
<span class="s">"case when anniversary >= ? then 1 when anniversary < ? then 2 end as order_column_1 "</span> <span class="o">+</span>
<span class="s">"from o_customer "</span> <span class="o">+</span>
<span class="s">"order by order_column_1"</span><span class="o">;</span>
<span class="n">List</span><span class="o"><</span><span class="n">Customer</span><span class="o">></span> <span class="n">result</span> <span class="o">=</span> <span class="n">DB</span><span class="o">.</span><span class="na">findNative</span><span class="o">(</span><span class="n">Customer</span><span class="o">.</span><span class="na">class</span><span class="o">,</span> <span class="n">sql</span><span class="o">)</span>
<span class="o">.</span><span class="na">setParameter</span><span class="o">(</span><span class="n">LocalDate</span><span class="o">.</span><span class="na">now</span><span class="o">())</span>
<span class="o">.</span><span class="na">setParameter</span><span class="o">(</span><span class="n">LocalDate</span><span class="o">.</span><span class="na">now</span><span class="o">())</span>
<span class="o">.</span><span class="na">findList</span><span class="o">();</span>
</pre></div>
</div>
<h2>firstRow / maxRows</h2>
<p>
We can specify <code>firstRow</code> and <code>maxRows</code> and the SQL will be modified
appropriately using <em>limit/offset</em> or equivalent clause.
</p>
<h5>Example - using maxRows</h5>
<div class="syntax java"><div class="highlight"><pre><span></span><span class="n">String</span> <span class="n">sql</span> <span class="o">=</span> <span class="s">"select id, name from o_customer order by name, id"</span><span class="o">;</span>
<span class="n">List</span><span class="o"><</span><span class="n">Customer</span><span class="o">></span> <span class="n">result</span> <span class="o">=</span> <span class="n">DB</span><span class="o">.</span><span class="na">findNative</span><span class="o">(</span><span class="n">Customer</span><span class="o">.</span><span class="na">class</span><span class="o">,</span> <span class="n">sql</span><span class="o">)</span>
<span class="o">.</span><span class="na">setMaxRows</span><span class="o">(</span><span class="mi">50</span><span class="o">)</span>
<span class="o">.</span><span class="na">findList</span><span class="o">();</span>
</pre></div>
</div>
<h2>fetchQuery</h2>
<p>
When using findNative we can think of it as effectively supplying the SQL for "root" or "origin"
query and can use <code>fetchQuery</code> to additionally fetch other parts of the graph.
</p>
<p>
In the example below we are using findNative with supplied SQL to fetch Customers and additionally
using <code>fetchQuery</code> to fetch the related orders and contacts for those customers.
</p>
<h5>Example - using fetchQuery</h5>
<div class="syntax java"><div class="highlight"><pre><span></span><span class="n">List</span><span class="o"><</span><span class="n">Customer</span><span class="o">></span> <span class="n">result</span> <span class="o">=</span> <span class="n">DB</span><span class="o">.</span><span class="na">findNative</span><span class="o">(</span><span class="n">Customer</span><span class="o">.</span><span class="na">class</span><span class="o">,</span> <span class="n">sql</span><span class="o">)</span>
<span class="o">.</span><span class="na">fetchQuery</span><span class="o">(</span><span class="s">"orders"</span><span class="o">)</span>
<span class="o">.</span><span class="na">fetchQuery</span><span class="o">(</span><span class="s">"contacts"</span><span class="o">)</span>
<span class="o">.</span><span class="na">findList</span><span class="o">();</span>
</pre></div>
</div>
<h2>Multiple tables</h2>
<p>
Excluding Oracle (see oracle limitation below) our SQL can select from multiple tables and these
can automatically be mapped into related entity beans.
</p>
<p>
For example, we can fetch and populate 2 related beans like Contact and Customer. The contact
entity beans returned will include customer beans with the customer id and name populated.
</p>
<h5>Example - contacts and their associated customer</h5>
<div class="syntax java"><div class="highlight"><pre><span></span><span class="c1">// Contacts + Customer</span>
<span class="n">String</span> <span class="n">sql</span>
<span class="o">=</span> <span class="s">"select con.id, con.first_name, con.last_name, cust.id, cust.name "</span> <span class="o">+</span>
<span class="s">" from contact con "</span> <span class="o">+</span>
<span class="s">" join customer cust on cust.id = con.customer_id "</span> <span class="o">+</span>
<span class="s">" order by con.first_name desc"</span><span class="o">;</span>
<span class="n">List</span><span class="o"><</span><span class="n">Contact</span><span class="o">></span> <span class="n">contacts</span> <span class="o">=</span> <span class="n">DB</span><span class="o">.</span><span class="na">findNative</span><span class="o">(</span><span class="n">Contact</span><span class="o">.</span><span class="na">class</span><span class="o">,</span> <span class="n">sql</span><span class="o">)</span>
<span class="o">.</span><span class="na">findList</span><span class="o">();</span>
</pre></div>
</div>
<h5>Example - order lines and their associated product</h5>
<div class="syntax java"><div class="highlight"><pre><span></span><span class="n">String</span> <span class="n">sql</span> <span class="o">=</span> <span class="s">"select line.*, p.* "</span> <span class="o">+</span>
<span class="s">" from order_line line "</span> <span class="o">+</span>
<span class="s">" join product p on p.id = line.product_id "</span> <span class="o">+</span>
<span class="s">" where line.order_id = ?"</span><span class="o">;</span>
<span class="n">List</span><span class="o"><</span><span class="n">OrderLine</span><span class="o">></span> <span class="n">lines</span> <span class="o">=</span> <span class="n">DB</span><span class="o">.</span><span class="na">findNative</span><span class="o">(</span><span class="n">OrderLine</span><span class="o">.</span><span class="na">class</span><span class="o">,</span> <span class="n">sql</span><span class="o">)</span>
<span class="o">.</span><span class="na">setParameter</span><span class="o">(</span><span class="n">order</span><span class="o">.</span><span class="na">getId</span><span class="o">())</span>
<span class="o">.</span><span class="na">findList</span><span class="o">();</span>
</pre></div>
</div>
<h2>Limitations</h2>
<p>
The limitation with using findNative (excluding Oracle) is that it can only map multiple tables
when the path for a given table is <em>unique</em>.
</p>
<p>
For example, lets say we have Customer that maps both a billingAddress and shippingAddress
to the same address table. When ebean tries to map a column for the address table it does not know
which path (billingAddress or shippingAddress) the column maps to.
</p>
<p>
When we hit this limitation we need to use <a href="/docs/query/rawSql">RawSql</a> instead
where we can map the columns more explicitly to bean paths/properties.
</p>
<h3>Oracle limitation</h3>
<p>
There is a limitation with the Oracle JDBC driver in that the meta data currently does not include
the table that a column relates to. This means that with Oracle we can only map a single table
and not multiple tables (and would have to use <a href="/docs/query/rawSql">RawSql</a> instead).
</p>
<h2 id="rawSql">RawSql</h2>
<p>
Using findNative is preferred over <em>RawSql</em> as the way to execute <code>SQL</code> queries with
<code>entity beans</code> because it is nice and simple as the columns are mapped to properties
automatically for us.
</p>
<p>
However, findNative has some limitations when we want the select clause to include columns from
multiple tables (limitations described above). When we hit those limitations we then need to
use <a href="/docs/query/rawSql">RawSql</a> with more explicit mapping of columns to properties.
</p>
<nav class="next">
<p class="edit-page">
<a href="https://github.com/ebean-orm/website-source/blob/master/docs/query/native-sql.html"><i class="fab fa-github"></i> Edit Page</a>
</p>
<p class="next">
<a href="/docs/query/rawSql" class="btn btn-info">Next: RawSql</a>
</p>
</nav>
</article>
</div>
</div>
<script src="https://code.jquery.com/jquery-3.4.1.slim.min.js" integrity="sha384-J6qa4849blE2+poT4WnyKhv5vZF5SrPo0iEjwBvKU7imGFAV0wwj1yYfoRSJoZ+n" crossorigin="anonymous"></script>
<script src="/js/site3.js"></script>
<script src="/js/search3.js"></script>
<script async src="https://www.googletagmanager.com/gtag/js?id=UA-75181644-1"></script>
<script>
window.dataLayer = window.dataLayer || [];
function gtag(){dataLayer.push(arguments);}
gtag('js', new Date());
gtag('config', 'UA-75181644-1');
</script>
</body>
</html>