-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathzai-grailsshi-yong-sqlhuo-qu-shu-ju.html
302 lines (284 loc) · 19.3 KB
/
zai-grailsshi-yong-sqlhuo-qu-shu-ju.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
290
291
292
293
294
295
296
297
298
299
300
301
302
<!DOCTYPE html>
<html lang="zh-cn">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="canonical" href="./zai-grailsshi-yong-sqlhuo-qu-shu-ju.html" />
<title> 愚钝的故事 — 在Grails使用Sql获取数据
</title>
<link rel="stylesheet" href="./theme/css/style.css">
<!--[if lt IE 9]>
<script src="https://oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script>
<script src="https://oss.maxcdn.com/libs/respond.js/1.4.2/respond.min.js"></script>
<![endif]-->
<meta name="author" content="Jet Geng">
<meta name="tags" contents="Sql, Grails, ">
</head>
<body>
<header class="header">
<div class="container">
<div class="header-inner">
<h1 class="header-name">
<a class="nodec" href="/">愚钝的故事</a>
</h1>
<h3 class="header-text">Jet Geng的工作和生活</h3>
<ul class="header-menu list-inline">
<li><a class="nodec icon-github" href="https://github.com/jetgeng"></a></li>
</ul>
</div>
</div>
</header> <!-- /.header --> <div class="container">
<div class="post full-post">
<h1 class="post-title">
<a href="/zai-grailsshi-yong-sqlhuo-qu-shu-ju.html" title="Permalink to 在Grails使用Sql获取数据">在Grails使用Sql获取数据</a>
</h1>
<ul class="list-inline">
<li class="post-date">
<a class="text-muted" href="/zai-grailsshi-yong-sqlhuo-qu-shu-ju.html" title="2014-06-12T17:39:00+08:00">Thu 12 June 2014</a>
</li>
<li class="muted">·</li>
<li class="post-category">
<a href="./category/grails.html">Grails</a>
</li>
<li class="muted">·</li>
<li>
<a href="/tag/Sql.html">Sql</a>, <a href="/tag/Grails.html">Grails</a> </li>
<li class="muted">·</li>
<li>
<address class="post-author">
By <a href="./author/jet-geng.html">Jet Geng</a>
</address>
</li>
</ul>
<div class="post-content">
<div class="section" id="id1">
<h2>前因</h2>
<p>Grails默认情况使用Hibernate作为数据存取的框架。不过Hibernate的缺点是众所周知的。所以我们在一些复杂的场合需要通过 groovy.sql.Sql 直接使用sql来获取数据。这样就会存在如下的问题：</p>
<blockquote>
<ol class="arabic simple">
<li>如何使用Grails配置的数据库连接?</li>
<li>如何执行sql，进行数据库相关操作?</li>
<li>如何将查询的数据转换成Domain Class?</li>
</ol>
</blockquote>
<p>下面就从上面这3个问题来说明如何在grails环境中直接使用sql来对数据库进行操作。</p>
</div>
<div class="section" id="id2">
<h2>连接数据库</h2>
<p>连接数据库相对来说比较简单，通过如下代码就可以完成。</p>
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre> 1
2
3
4
5
6
7
8
9
10</pre></div></td><td class="code"><div class="highlight"><pre><span></span><span class="kd">class</span> <span class="nc">DoSomethingServices</span> <span class="o">{</span>
<span class="kt">def</span> <span class="n">dataSource</span>
<span class="kt">def</span> <span class="nf">queryDataWithSql</span><span class="o">(){</span>
<span class="n">Sql</span> <span class="n">sql</span> <span class="o">=</span> <span class="k">new</span> <span class="n">Sql</span><span class="o">(</span><span class="n">dataSource</span><span class="o">)</span>
<span class="n">sql</span><span class="o">.</span><span class="na">each</span><span class="o">(</span><span class="s2">"select * from sometable"</span><span class="o">){</span> <span class="n">it</span> <span class="o">-></span>
<span class="n">println</span> <span class="n">it</span>
<span class="o">}</span>
<span class="o">}</span>
<span class="o">}</span>
</pre></div>
</td></tr></table><p>代码详细说明：</p>
<blockquote>
<ul class="simple">
<li>第3行 注入dataSource， 这个dataSource 就是Grails中在DataSource.groovy中配置的 数据源。 默认的hibernate也是在使用这个数据源。</li>
<li>第5 行 使用通过注入的dataSource对象 创建sql对象。 关于Sql对象的使用可以参考 <a class="reference external" href="http://groovy.codehaus.org/api/groovy/sql/Sql.html">http://groovy.codehaus.org/api/groovy/sql/Sql.html</a></li>
<li>第6行 使用each方法执行一个sql语句。然后逐行回调。</li>
</ul>
</blockquote>
<p>连接数据库和查询数据就这么简单。</p>
<p>到这里肯定有人会问，如果需要往sql语句中加入参数怎么办。如何避免 Sql注入。
这个Sql在设计的过程中已经考虑到了。而且使用及其简单。只要使用如下代码即可。</p>
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre>1
2
3
4
5
6
7
8</pre></div></td><td class="code"><div class="highlight"><pre><span></span> <span class="kt">def</span> <span class="nf">queryDataWithSql</span><span class="o">(){</span>
<span class="n">Sql</span> <span class="n">sql</span> <span class="o">=</span> <span class="k">new</span> <span class="n">Sql</span><span class="o">(</span><span class="n">dataSource</span><span class="o">)</span>
<span class="kt">def</span> <span class="n">paramValue</span> <span class="o">=</span> <span class="o">..</span>
<span class="kt">def</span> <span class="n">paramValue2</span> <span class="o">=</span> <span class="o">..</span>
<span class="n">sql</span><span class="o">.</span><span class="na">each</span><span class="o">(</span><span class="s2">"select * from sometable where field = ${paramValue} and field2 = ${paramValue2}"</span><span class="o">){</span> <span class="n">it</span> <span class="o">-></span>
<span class="n">println</span> <span class="n">it</span>
<span class="o">}</span>
<span class="o">}</span>
</pre></div>
</td></tr></table><p>第5行直接使用GString传入参数。最终执行的时候其实是讲GString中得参数获取出来。通过PreparedStatement传入参数的方式。这样可以避免sql的注入的攻击。你不相信？那就看看Sql.java这个类中得eachRow方法吧。这个方法位于groovy-all-2.1.9-source.jar/groovy/sql/Sql.java 的第1236行。</p>
</div>
<div class="section" id="domain-class">
<h2>数据如何转换成Domain Class对象</h2>
<p>这个问题是一个大问题。不过不是没有办法。最笨的办法就是写成如下的样子：</p>
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre>1
2
3
4
5</pre></div></td><td class="code"><div class="highlight"><pre><span></span><span class="n">sql</span><span class="o">.</span><span class="na">each</span><span class="o">(</span><span class="s2">"select field1 , field2 from sometable where field = ${paramValue} and field2 = ${paramValue2}"</span><span class="o">){</span> <span class="n">it</span> <span class="o">-></span>
<span class="kt">def</span> <span class="n">someDomain</span> <span class="o">=</span> <span class="k">new</span> <span class="n">SomeDomin</span><span class="o">()</span>
<span class="n">someDomain</span><span class="o">.</span><span class="na">field1</span> <span class="o">=</span> <span class="n">it</span><span class="o">[</span><span class="s2">"field1"</span><span class="o">]</span>
<span class="n">someDomain</span><span class="o">.</span><span class="na">field2</span> <span class="o">=</span> <span class="n">it</span><span class="o">[</span><span class="s2">"field2"</span><span class="o">]</span>
<span class="o">}</span>
</pre></div>
</td></tr></table><p>这个方法最大的缺点是代码量多，并且会有大量重复的代码。给人感觉很恶心。
在Groovy中又如下的办法可以对对象的字段赋值：</p>
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre>1
2</pre></div></td><td class="code"><div class="highlight"><pre><span></span> <span class="kt">def</span> <span class="n">key</span> <span class="o">=</span> <span class="s2">"field1"</span>
<span class="n">someDomain</span><span class="o">.</span><span class="na">getProperties</span><span class="o">()[</span><span class="n">key</span><span class="o">]</span> <span class="o">=</span> <span class="s2">"someValue"</span>
</pre></div>
</td></tr></table><p>getProperties这个方法将该对象的所有值放到一个Map中返回。具体可参考http://groovy.codehaus.org/groovy-jdk/java/lang/Object.html#getProperties%28%29 对这个map进行赋值，就等于对这个对象进行赋值。
所以下面我只要有一个字段和变量名对应的map，什么就会搞定了。
于是有了如下的代码：</p>
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre> 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</pre></div></td><td class="code"><div class="highlight"><pre><span></span><span class="kd">class</span> <span class="nc">DomainClassInfoService</span> <span class="o">{</span>
<span class="kt">def</span> <span class="n">sessionFactory</span>
<span class="kt">def</span> <span class="n">grailsApplication</span>
<span class="kt">def</span> <span class="nf">getDomainClass</span><span class="o">(</span><span class="n">clazzName</span><span class="o">)</span> <span class="o">{</span>
<span class="k">return</span> <span class="n">grailsApplication</span><span class="o">.</span><span class="na">domainClasses</span><span class="o">.</span><span class="na">find</span> <span class="o">{</span>
<span class="n">it</span><span class="o">.</span><span class="na">name</span> <span class="o">==</span> <span class="n">clazzName</span>
<span class="o">}</span>
<span class="o">}</span>
<span class="kt">def</span> <span class="nf">getFieldColumnMap</span><span class="o">(</span><span class="n">clazz</span><span class="o">)</span> <span class="o">{</span>
<span class="kt">def</span> <span class="n">fieldColumnMap</span> <span class="o">=</span> <span class="o">[:]</span>
<span class="kt">def</span> <span class="n">hibernateMetaClass</span> <span class="o">=</span> <span class="n">sessionFactory</span><span class="o">.</span><span class="na">getClassMetadata</span><span class="o">(</span><span class="n">clazz</span><span class="o">)</span>
<span class="kt">def</span> <span class="n">grailsDomainClass</span> <span class="o">=</span> <span class="n">getDomainClass</span><span class="o">(</span><span class="n">clazz</span><span class="o">.</span><span class="na">getSimpleName</span><span class="o">())</span>
<span class="kt">def</span> <span class="n">domainProps</span> <span class="o">=</span> <span class="n">grailsDomainClass</span><span class="o">.</span><span class="na">getProperties</span><span class="o">()</span>
<span class="n">domainProps</span><span class="o">.</span><span class="na">each</span> <span class="o">{</span> <span class="n">prop</span> <span class="o">-></span>
<span class="c1">//get the property's name</span>
<span class="kt">def</span> <span class="n">propName</span> <span class="o">=</span> <span class="n">prop</span><span class="o">.</span><span class="na">getName</span><span class="o">()</span>
<span class="c1">//please refer to the hibernate javadoc</span>
<span class="c1">//http://www.hibernate.org/hib_docs/v3/api/org/hibernate/persister/entity/AbstractEntityPersister.html</span>
<span class="kt">def</span> <span class="n">columnProps</span> <span class="o">=</span> <span class="n">hibernateMetaClass</span><span class="o">.</span><span class="na">getPropertyColumnNames</span><span class="o">(</span><span class="n">propName</span><span class="o">)</span>
<span class="k">if</span> <span class="o">(</span><span class="n">columnProps</span> <span class="o">&&</span> <span class="n">columnProps</span><span class="o">.</span><span class="na">length</span> <span class="o">></span> <span class="mi">0</span><span class="o">)</span> <span class="o">{</span>
<span class="c1">//get the columnname, which is stored into the first array</span>
<span class="kt">def</span> <span class="n">columnName</span> <span class="o">=</span> <span class="n">columnProps</span><span class="o">[</span><span class="mi">0</span><span class="o">]</span>
<span class="n">fieldColumnMap</span><span class="o">[</span><span class="n">propName</span><span class="o">]</span> <span class="o">=</span> <span class="n">columnName</span>
<span class="o">}</span>
<span class="o">}</span>
<span class="k">return</span> <span class="n">fieldColumnMap</span>
<span class="o">}</span>
<span class="o">}</span>
</pre></div>
</td></tr></table><dl class="docutils">
<dt>以上代码说明如下：</dt>
<dd><ul class="first last simple">
<li>5 ~ 6 行注入将要使用的两个服务，一个是hibernate的sessionFactory， 另外一个是grailsApplication 上下文</li>
<li>7 ~ 9 这个方法是根据给定的段类名。比如有一个Domain Class的全名为 org.gunn.domain.Book 这里的clazzName 就是Book。
* 第 8 行是从grailsApplication中获取所有Domain Class的DefaultGrailsDomainClass这个类的对象。这里牵涉到一个Artefact的概念，请参考 <a class="reference external" href="https://grails.org/Developer+-+Artefact+API">https://grails.org/Developer+-+Artefact+API</a></li>
<li>12 ~ 28 行就是 根据Domain Class中的变量来获取数据库对应的的字段名。 有代码在这里就不多解释了。</li>
</ul>
</dd>
</dl>
<p>结合我们上面的那个properties的小技巧，我们就使用如下代码来完成使用Sql查询数据，转换成Domain Class的对象。</p>
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre> 1
2
3
4
5
6
7
8
9
10</pre></div></td><td class="code"><div class="highlight"><pre><span></span><span class="n">String</span> <span class="n">querySql</span> <span class="o">=</span> <span class="s1">''' select * from table where field1 = ? '''</span>
<span class="kt">def</span> <span class="n">tripSegmentFieldColumnMap</span> <span class="o">=</span> <span class="n">domainClassInfoService</span><span class="o">.</span><span class="na">getFieldColumnMap</span><span class="o">(</span><span class="n">SomeDomain</span><span class="o">)</span>
<span class="n">Sql</span> <span class="n">sql</span> <span class="o">=</span> <span class="k">new</span> <span class="n">Sql</span><span class="o">(</span><span class="n">dataSource</span><span class="o">)</span>
<span class="n">sql</span><span class="o">.</span><span class="na">eachRow</span><span class="o">(</span><span class="n">querySql</span><span class="o">,</span> <span class="n">field1Value</span><span class="o">){</span>
<span class="n">SomeDomain</span> <span class="n">someObject</span> <span class="o">=</span> <span class="k">new</span> <span class="n">SomeDomain</span><span class="o">()</span>
<span class="n">tripSegmentFieldColumnMap</span><span class="o">.</span><span class="na">each</span> <span class="o">{</span> <span class="n">key</span><span class="o">,</span> <span class="n">value</span> <span class="o">-></span>
<span class="n">someObject</span><span class="o">.</span><span class="na">getProperties</span><span class="o">()[</span><span class="n">key</span><span class="o">]</span> <span class="o">=</span> <span class="n">it</span><span class="o">[</span><span class="n">value</span><span class="o">]</span>
<span class="o">}</span>
<span class="o">}</span>
</pre></div>
</td></tr></table><p>这个方法对于非关系的，没有太大问题。如果有类似于一对多这样的关系的话，会引起hibernate中著名的n+1的问题。例如SomeDomain 中有一个变量是SomeParent, 并且SomeDomain belong to 这个SomeParent的话。那么像上面那样直接赋值就会引起去发起数据库查询请求查询SomeParent的。所以可以使用如下的方式进行避免：</p>
<div class="highlight"><pre><span></span><span class="n">sql</span><span class="o">.</span><span class="na">eachRow</span><span class="o">(</span><span class="n">querySql</span><span class="o">,</span> <span class="n">field1Value</span><span class="o">){</span>
<span class="n">SomeDomain</span> <span class="n">someObject</span> <span class="o">=</span> <span class="k">new</span> <span class="n">SomeDomain</span><span class="o">()</span>
<span class="n">SomeParent</span> <span class="n">someParent</span> <span class="o">=</span> <span class="k">new</span> <span class="n">SomeParent</span><span class="o">()</span>
<span class="n">someParent</span><span class="o">.</span><span class="na">id</span> <span class="o">=</span> <span class="n">it</span><span class="o">.</span><span class="na">parentId</span>
<span class="n">tripSegmentFieldColumnMap</span><span class="o">.</span><span class="na">each</span> <span class="o">{</span> <span class="n">key</span><span class="o">,</span> <span class="n">value</span> <span class="o">-></span>
<span class="k">if</span><span class="o">(</span><span class="n">key</span> <span class="o">!=</span> <span class="s2">"parentId"</span><span class="o">)</span>
<span class="n">someObject</span><span class="o">.</span><span class="na">getProperties</span><span class="o">()[</span><span class="n">key</span><span class="o">]</span> <span class="o">=</span> <span class="n">it</span><span class="o">[</span><span class="n">value</span><span class="o">]</span>
<span class="o">}</span>
<span class="n">someObject</span><span class="o">.</span><span class="na">parent</span> <span class="o">=</span> <span class="n">someParent</span>
</pre></div>
<p>这个办法很土，如果你又更好的。欢迎分享！谢谢！</p>
</div>
</div>
</div>
<hr class="separator">
<div class="col-md-8 col-md-offset-2">
<div id="disqus_thread">
<script>
var disqus_shortname = 'techfoolishstory';
(function() {
var dsq = document.createElement('script');
dsq.type = 'text/javascript';
dsq.async = true;
dsq.src = '//' + disqus_shortname + '.disqus.com/embed.js';
(document.getElementsByTagName('head')[0] ||
document.getElementsByTagName('body')[0]).appendChild(dsq);
})();
</script>
<noscript>
Please enable JavaScript to view the
<a href="https://disqus.com/?ref_noscript=techfoolishstory">
comments powered by Disqus.
</a>
</noscript>
<a href="https://disqus.com" class="dsq-brlink">
blog comments powered by <span class="logo-disqus">Disqus</span>
</a>
</div>
</div>
</div>
<footer class="footer">
<div class="container">
<p class="text-center">
Jet Geng, <a href="" target="_blank">版权归Jet Geng所有，转载请注明！</a> unless otherwise noted.
</p>
<div class="text-center">
Generated by <a href="http://getpelican.com" target="_blank">Pelican</a> with the <a href="http://github.com/nairobilug/pelican-alchemy">alchemy</a> theme.
</div>
</div>
</footer> <!-- /.footer -->
<script src="./theme/js/jquery.min.js"></script>
<script src="./theme/js/bootstrap.min.js"></script>
</body> <!-- 42 -->
</html>