-
Notifications
You must be signed in to change notification settings - Fork 29
/
sql-query.html
115 lines (94 loc) · 2.93 KB
/
sql-query.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
<html>
<head>
<title>SQL query | Intro | Ebean</title>
<meta name="layout" content="_layout2/base-docs.html"/>
<meta name="bread1" content="Introduction" href="/docs/intro/"/>
<meta name="bread2" content="Queries" href="/docs/intro/queries"/>
<meta name="bread3" content="SQL query" href="/docs/intro/queries/sql-query"/>
<#assign n0_intro="active">
<#assign n1_queries="active">
<#assign sqlQuery="active">
</head>
<body>
<h2>SQL Query</h2>
<p>
Sometimes it is useful to not use entity beans or dto beans but instead execute sql queries
producing <code>SqlRow</code> objects or reading directly from JDBC ResultSet.
</p>
<p>
In typical recent applications around <code>2%</code> of queries were SqlQuery type queries.
</p>
<h3>SqlQuery and SqlRow</h3>
<p>
Note that with SqlQuery we can get Ebean to add <code>limit/offset</code> clause appropriate
to the database platform and we can bind parameters using all the mapped types
(Enums, java.time, array types etc).
</p>
<p>
For more information goto <a href="/docs/query/sqlquery">docs / query / sqlquery</a>
</p>
<#include "/_common/lang-buttons.html">
<div class="code-java">
<pre content="java">
String sql = "select id, name, when_active from customer where status = ?";
List<|SqlRow> rows = DB.sqlQuery(sql)
.setParameter(1, "NEW")
.setMaxRows(100)
.findList();
for (SqlRow sqlRow : rows) {
Long id = row.getLong("id")
String name = row.getString("name")
Timestamp whenActivated = row.getTimestamp("when_activated")
}
</pre>
</div>
<div class="code-kt">
<pre content="kotlin">
val sql = """
select id, name, when_activated
from customer
where name like ?
""".trimIndent()
val rows = DB.sqlQuery(sql)
.setParameter(1, "Rob%")
.setMaxRows(100)
.findList()
for (row in rows) {
val id = row.getLong("id")
val name = row.getString("name")
val whenActivated = row.getTimestamp("when_activated")
}
</pre>
</div>
<h3>SqlQuery and RowMapper</h3>
<p>
Alternatively we can use a RowMapper to read from the JDBC <em>ResultSet</em>.
</p>
<#include "/_common/lang-buttons.html">
<div class="code-java">
<pre content="java">
String sql = "select id, name, status from customer order by name desc";
DB.sqlQuery(sql)
.findEachRow((resultSet, rowNum) -> {
// read directly from ResultSet
long id = resultSet.getLong(1);
String name = resultSet.getString(2);
// do something interesting with the data
});
</pre>
</div>
<div class="code-kt">
<pre content="kotlin">
val sql = "select id, name, status from customer order by name desc"
DB.sqlQuery(sql)
.findEachRow { resultSet, rowNum ->
// read directly from ResultSet
val id = resultSet.getLong(1)
val name = resultSet.getString(2)
// do something interesting with the data
}
</pre>
</div>
<@next_edit "JDBC Query" "/docs/intro/queries/jdbc-query" "/docs/intro/queries/sql-query.html"/>
</body>
</html>