-
Notifications
You must be signed in to change notification settings - Fork 212
/
Copy pathcomplexQueries.html
165 lines (155 loc) · 8.34 KB
/
complexQueries.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
<!DOCTYPE html>
<!--
| Generated by Apache Maven Doxia Site Renderer 2.0.0-M18 from src/site/markdown/docs/complexQueries.md at 03 Jun 2024
| Rendered using Apache Maven Fluido Skin 2.0.0-M9
-->
<html xmlns="http://www.w3.org/1999/xhtml" lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<meta name="generator" content="Apache Maven Doxia Site Renderer 2.0.0-M18" />
<title>MyBatis Dynamic SQL</title>
<link rel="stylesheet" href="../css/apache-maven-fluido-2.0.0-M9.min.css" />
<link rel="stylesheet" href="../css/site.css" />
<link rel="stylesheet" href="../css/print.css" media="print" />
<script src="../js/apache-maven-fluido-2.0.0-M9.min.js"></script>
</head>
<body>
<div class="container-fluid container-fluid-top">
<header>
<div id="banner">
<div class="pull-left"><div id="bannerLeft"><h1><a href="../docs/introduction.html">MyBatis Dynamic SQL</a></h1></div></div>
<div class="pull-right"><div id="bannerRight"><h1><a href="http://www.mybatis.org/" class="externalLink"><img class="imageLink" src="https://mybatis.org/images/mybatis-logo.png" alt="MyBatis logo" /> MyBatis</a></h1></div></div>
<div class="clear"><hr/></div>
</div>
<div id="breadcrumbs">
<ul class="breadcrumb">
<li id="publishDate">Last Published: 03 Jun 2024<span class="divider">|</span>
</li>
<li id="projectVersion">Version: 1.5.2</li>
</ul>
</div>
</header>
<div class="row-fluid">
<header id="leftColumn" class="span2">
<nav class="well sidebar-nav">
<ul class="nav nav-list">
<li class="nav-header">User's Guide</li>
<li><a href="../docs/introduction.html">Introduction</a></li>
<li><a href="../docs/CHANGELOG.html">Change Log</a></li>
<li><a href="../docs/quickStart.html">Quick Start</a></li>
<li><a href="../docs/exceptions.html">Exceptions thrown by the Library</a></li>
<li><a href="../docs/configuration.html">Configuration of the Library</a></li>
<li><a href="../docs/databaseObjects.html">Modeling Database Objects</a></li>
<li><a href="../docs/whereClauses.html"><span class="icon-chevron-down"></span>WHERE Clause Support</a>
<ul class="nav nav-list">
<li><a href="../docs/conditions.html">WHERE Conditions</a></li>
</ul></li>
<li><a href="../docs/select.html"><span class="icon-chevron-down"></span>SELECT Statements</a>
<ul class="nav nav-list">
<li><a href="../docs/caseExpressions.html">Case Expressions</a></li>
<li class="active"><a>Complex Queries</a></li>
</ul></li>
<li><a href="../docs/delete.html">DELETE Statements</a></li>
<li><a href="../docs/insert.html">INSERT Statements</a></li>
<li><a href="../docs/update.html">UPDATE Statements</a></li>
<li><a href="../docs/subQueries.html">SubQuery Support</a></li>
<li><a href="../docs/functions.html">Database Functions</a></li>
<li><a href="../docs/mybatis3.html">MyBatis3 Support</a></li>
<li><a href="../docs/spring.html">Spring Support</a></li>
<li><a href="../docs/springBatch.html">Spring Batch Support</a></li>
<li><a href="../docs/kotlinOverview.html"><span class="icon-chevron-right"></span>Kotlin Support</a></li>
<li><a href="../docs/howItWorks.html">How it Works</a></li>
<li><a href="../docs/extending.html">Extending the Library</a></li>
<li><a href="../docs/codingStandards.html">Coding Standards</a></li>
<li><a href="../docs/motivation.html">Motivation</a></li>
<li class="nav-header">Project Documentation</li>
<li><a href="../project-info.html"><span class="icon-chevron-right"></span>Project Information</a></li>
<li><a href="../project-reports.html"><span class="icon-chevron-right"></span>Project Reports</a></li>
</ul>
</nav>
<div class="well sidebar-nav">
<div id="poweredBy">
<div class="clear"></div>
<div class="clear"></div>
<a href="https://maven.apache.org/" class="builtBy" target="_blank"><img class="builtBy" alt="Built by Maven" src="../images/logos/maven-feather.png" /></a>
</div>
</div>
</header>
<main id="bodyColumn" class="span10">
<section><a id="Complex_Queries"></a>
<h1>Complex Queries</h1>
<p>Enhancements in version 1.1.2 make it easier to code complex queries. The Select DSL is implemented as a set of related
objects. As the select statement is built, intermediate objects of various types are returned from the various methods
that implement the DSL. The select statement can be completed by calling the <code>build()</code> method many of the intermediate
objects. Prior to version 1.1.2, it was necessary to call <code>build()</code> on the <strong>last</strong> intermediate object. This
restriction has been removed, and it is now possible to call <code>build()</code> on <strong>any</strong> intermediate object. This, along with
several other enhancements, has simplified the coding of complex queries.</p>
<p>For example, suppose you want to code a complex search on a Person table. The search parameters are id, first name,
and last name. The rules are:</p>
<ol style="list-style-type: decimal;">
<li>If an id is entered, use the id and ignore the other search parameters</li>
<li>If an id is not entered, then do a fuzzy search based on the other parameters</li>
</ol>
<p>This can be implemented with code like the following…</p>
<pre><code class="language-java">public SelectStatementProvider search(Integer targetId, String fName, String lName) {
var builder = select(id, firstName, lastName) // (1)
.from(person)
.where(); // (2)
if (targetId != null) { // (3)
builder
.and(id, isEqualTo(targetId));
} else {
builder
.and(firstName, isLike(fName).filter(Objects::nonNull).map(s -> "%" + s + "%")) // (4) (5)
.and(lastName, isLikeWhenPresent(lName).map(this::addWildcards)); // (6)
}
builder
.orderBy(lastName, firstName)
.fetchFirst(50).rowsOnly(); // (7)
return builder.build().render(RenderingStrategies.MYBATIS3); // (8)
}
public String addWildcards(String s) {
return "%" + s + "%";
}
</code></pre>
<p>Notes:</p>
<ol style="list-style-type: decimal;">
<li>Note the use of the <code>var</code> keyword here. If you are using an older version of Java, the actual type is
<code>QueryExpressionDSL<SelectModel>.QueryExpressionWhereBuilder</code></li>
<li>Here we are calling <code>where()</code> with no parameters. This sets up the builder to accept conditions further along in the
code. If no conditions are added, then the where clause will not be rendered</li>
<li>This <code>if</code> statement implements the rules of the search. If an ID is entered , use it. Otherwise, do a fuzzy search
based on first name and last name.</li>
<li>The <code>filter</code> method on this line will mark the condition as unrenderable if the filter is not satisfied.</li>
<li>The <code>map</code> statement on this line allows you to change the parameter value before it is placed in the parameter Map.
In this case we are adding SQL wildcards to the start and end of the search String - but only if the search String
is not null. If the search String is null, the lambda will not be called and the condition will not render</li>
<li>This line shows the use of a method reference instead of a lambda on the <code>map</code>. Method references allow you to more
clearly express intent. Note also the use of the <code>isLikeWhenPresent</code> function which is a built-in function that
applies a non-null filter</li>
<li>It is a good idea to limit the number of rows returned from a search. The library now supports <code>fetch first</code> syntax
for limiting rows</li>
<li>Note that we are calling the <code>build</code> method from the intermediate object retrieved in step 1. It is no longer
necessary to call <code>build</code> on the last object returned from a select builder</li>
</ol></section>
</main>
</div>
</div>
<hr/>
<footer>
<div class="container-fluid">
<div class="row-fluid">
<p>© 2016–2024
<a href="https://www.mybatis.org/">MyBatis.org</a>
</p>
</div>
</div>
</footer>
<script>
if(anchors) {
anchors.add();
}
</script>
</body>
</html>