View Javadoc
1   /*
2    *    Copyright 2009-2021 the original author or authors.
3    *
4    *    Licensed under the Apache License, Version 2.0 (the "License");
5    *    you may not use this file except in compliance with the License.
6    *    You may obtain a copy of the License at
7    *
8    *       http://www.apache.org/licenses/LICENSE-2.0
9    *
10   *    Unless required by applicable law or agreed to in writing, software
11   *    distributed under the License is distributed on an "AS IS" BASIS,
12   *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13   *    See the License for the specific language governing permissions and
14   *    limitations under the License.
15   */
16  package org.apache.ibatis.executor;
17  
18  import java.util.ArrayList;
19  import java.util.Date;
20  import java.util.HashMap;
21  import java.util.List;
22  import java.util.Map;
23  
24  import org.apache.ibatis.builder.StaticSqlSource;
25  import org.apache.ibatis.cache.Cache;
26  import org.apache.ibatis.cache.decorators.LoggingCache;
27  import org.apache.ibatis.cache.decorators.ScheduledCache;
28  import org.apache.ibatis.cache.decorators.SerializedCache;
29  import org.apache.ibatis.cache.decorators.SynchronizedCache;
30  import org.apache.ibatis.cache.impl.PerpetualCache;
31  import org.apache.ibatis.domain.blog.Author;
32  import org.apache.ibatis.domain.blog.Blog;
33  import org.apache.ibatis.domain.blog.Comment;
34  import org.apache.ibatis.domain.blog.Post;
35  import org.apache.ibatis.domain.blog.Section;
36  import org.apache.ibatis.domain.blog.Tag;
37  import org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator;
38  import org.apache.ibatis.executor.keygen.SelectKeyGenerator;
39  import org.apache.ibatis.mapping.Discriminator;
40  import org.apache.ibatis.mapping.MappedStatement;
41  import org.apache.ibatis.mapping.ParameterMap;
42  import org.apache.ibatis.mapping.ParameterMapping;
43  import org.apache.ibatis.mapping.ParameterMode;
44  import org.apache.ibatis.mapping.ResultFlag;
45  import org.apache.ibatis.mapping.ResultMap;
46  import org.apache.ibatis.mapping.ResultMapping;
47  import org.apache.ibatis.mapping.SqlCommandType;
48  import org.apache.ibatis.mapping.SqlSource;
49  import org.apache.ibatis.mapping.StatementType;
50  import org.apache.ibatis.scripting.xmltags.DynamicSqlSource;
51  import org.apache.ibatis.scripting.xmltags.TextSqlNode;
52  import org.apache.ibatis.session.Configuration;
53  import org.apache.ibatis.type.JdbcType;
54  import org.apache.ibatis.type.TypeHandlerRegistry;
55  
56  class ExecutorTestHelper {
57  
58    static final Cache authorCache;
59  
60    static {
61      authorCache =
62          new SynchronizedCache(
63              new SerializedCache(
64                  new LoggingCache(
65                      new ScheduledCache(
66                          new PerpetualCache("author_cache")))));
67  
68    }
69  
70    static MappedStatement prepareInsertAuthorMappedStatement(final Configuration config) {
71      final TypeHandlerRegistry registry = config.getTypeHandlerRegistry();
72      return new MappedStatement.Builder(config, "insertAuthor", new StaticSqlSource(config,"INSERT INTO author (id,username,password,email,bio,favourite_section) values(?,?,?,?,?,?)"), SqlCommandType.INSERT)
73          .parameterMap(
74              new ParameterMap.Builder(
75                  config, "defaultParameterMap", Author.class,
76                  new ArrayList<ParameterMapping>() {
77                    {
78                      add(new ParameterMapping.Builder(config, "id", registry.getTypeHandler(int.class)).build());
79                      add(new ParameterMapping.Builder(config, "username", registry.getTypeHandler(String.class)).build());
80                      add(new ParameterMapping.Builder(config, "password", registry.getTypeHandler(String.class)).build());
81                      add(new ParameterMapping.Builder(config, "email", registry.getTypeHandler(String.class)).build());
82                      add(new ParameterMapping.Builder(config, "bio", registry.getTypeHandler(String.class)).jdbcType(JdbcType.VARCHAR).build());
83                      add(new ParameterMapping.Builder(config, "favouriteSection", registry.getTypeHandler(Section.class)).jdbcType(JdbcType.VARCHAR).build());
84                    }
85                  }).build())
86          .cache(authorCache).build();
87    }
88  
89    static MappedStatement prepareInsertAuthorMappedStatementWithAutoKey(final Configuration config) {
90      final TypeHandlerRegistry registry = config.getTypeHandlerRegistry();
91      return new MappedStatement.Builder(config, "insertAuthor", new StaticSqlSource(config,"INSERT INTO author (username,password,email,bio,favourite_section) values(?,?,?,?,?)"), SqlCommandType.INSERT)
92          .parameterMap(
93              new ParameterMap.Builder(config, "defaultParameterMap", Author.class, new ArrayList<ParameterMapping>() {
94                {
95                  add(new ParameterMapping.Builder(config, "username", registry.getTypeHandler(String.class)).build());
96                  add(new ParameterMapping.Builder(config, "password", registry.getTypeHandler(String.class)).build());
97                  add(new ParameterMapping.Builder(config, "email", registry.getTypeHandler(String.class)).build());
98                  add(new ParameterMapping.Builder(config, "bio", registry.getTypeHandler(String.class)).jdbcType(JdbcType.VARCHAR).build());
99                  add(new ParameterMapping.Builder(config, "favouriteSection", registry.getTypeHandler(Section.class)).jdbcType(JdbcType.VARCHAR).build());
100               }
101             }).build())
102         .cache(authorCache)
103         .keyGenerator(Jdbc3KeyGenerator.INSTANCE)
104         .keyProperty("id")
105         .build();
106   }
107 
108   static MappedStatement prepareInsertAuthorProc(final Configuration config) {
109     final TypeHandlerRegistry registry = config.getTypeHandlerRegistry();
110     return new MappedStatement.Builder(config, "insertAuthorProc", new StaticSqlSource(config,"{call insertAuthor(?,?,?,?)}"), SqlCommandType.INSERT)
111         .parameterMap(new ParameterMap.Builder(config, "defaultParameterMap", Author.class,
112             new ArrayList<ParameterMapping>() {
113               {
114                 add(new ParameterMapping.Builder(config, "id", registry.getTypeHandler(int.class)).build());
115                 add(new ParameterMapping.Builder(config, "username", registry.getTypeHandler(String.class)).build());
116                 add(new ParameterMapping.Builder(config, "password", registry.getTypeHandler(String.class)).build());
117                 add(new ParameterMapping.Builder(config, "email", registry.getTypeHandler(String.class)).build());
118               }
119             }).build())
120         .cache(authorCache).build();
121   }
122 
123   static MappedStatement prepareUpdateAuthorMappedStatement(final Configuration config) {
124     final TypeHandlerRegistry registry = config.getTypeHandlerRegistry();
125     return new MappedStatement.Builder(config, "updateAuthor", new StaticSqlSource(config,"UPDATE author SET username = ?, password = ?, email = ?, bio = ? WHERE id = ?"), SqlCommandType.UPDATE)
126         .parameterMap(new ParameterMap.Builder(config, "defaultParameterMap", Author.class,
127             new ArrayList<ParameterMapping>() {
128               {
129                 add(new ParameterMapping.Builder(config, "username", registry.getTypeHandler(String.class)).build());
130                 add(new ParameterMapping.Builder(config, "password", registry.getTypeHandler(String.class)).build());
131                 add(new ParameterMapping.Builder(config, "email", registry.getTypeHandler(String.class)).build());
132                 add(new ParameterMapping.Builder(config, "bio", registry.getTypeHandler(String.class)).jdbcType(JdbcType.VARCHAR).build());
133                 add(new ParameterMapping.Builder(config, "id", registry.getTypeHandler(int.class)).build());
134               }
135             }).build())
136         .cache(authorCache).build();
137   }
138 
139   static MappedStatement prepareDeleteAuthorMappedStatement(final Configuration config) {
140     final TypeHandlerRegistry registry = config.getTypeHandlerRegistry();
141     return new MappedStatement.Builder(config, "deleteAuthor", new StaticSqlSource(config,"DELETE FROM author WHERE id = ?"), SqlCommandType.DELETE)
142         .parameterMap(new ParameterMap.Builder(config, "defaultParameterMap", Author.class,
143             new ArrayList<ParameterMapping>() {
144               {
145                 add(new ParameterMapping.Builder(config, "id", registry.getTypeHandler(int.class)).build());
146               }
147             }).build())
148         .cache(authorCache)
149         .build();
150   }
151 
152   static MappedStatement prepareSelectOneAuthorMappedStatement(final Configuration config) {
153     final TypeHandlerRegistry registry = config.getTypeHandlerRegistry();
154 
155     final ResultMap rm = new ResultMap.Builder(config, "defaultResultMap", Author.class, new
156         ArrayList<ResultMapping>() {
157           {
158             add(new ResultMapping.Builder(config, "id", "id", registry.getTypeHandler(int.class)).build());
159             add(new ResultMapping.Builder(config, "username", "username", registry.getTypeHandler(String.class)).build());
160             add(new ResultMapping.Builder(config, "password", "password", registry.getTypeHandler(String.class)).build());
161             add(new ResultMapping.Builder(config, "email", "email", registry.getTypeHandler(String.class)).build());
162             add(new ResultMapping.Builder(config, "bio", "bio", registry.getTypeHandler(String.class)).build());
163             add(new ResultMapping.Builder(config, "favouriteSection", "favourite_section", registry.getTypeHandler(Section.class)).build());
164           }
165         }).build();
166 
167     return new MappedStatement.Builder(config, "selectAuthor", new StaticSqlSource(config,"SELECT * FROM author WHERE id = ?"), SqlCommandType.SELECT)
168         .parameterMap(new ParameterMap.Builder(config, "defaultParameterMap", Author.class,
169             new ArrayList<ParameterMapping>() {
170               {
171                 add(new ParameterMapping.Builder(config, "id", registry.getTypeHandler(int.class)).build());
172               }
173             }).build())
174         .resultMaps(new ArrayList<ResultMap>() {
175           {
176             add(rm);
177           }
178         })
179         .cache(authorCache).build();
180   }
181 
182   static MappedStatement prepareSelectAllAuthorsAutoMappedStatement(final Configuration config) {
183     final TypeHandlerRegistry registry = config.getTypeHandlerRegistry();
184     return new MappedStatement.Builder(config, "selectAuthorAutoMap", new StaticSqlSource(config,"SELECT * FROM author ORDER BY id"), SqlCommandType.SELECT)
185         .resultMaps(new ArrayList<ResultMap>() {
186           {
187             add(new ResultMap.Builder(config, "defaultResultMap", Author.class, new ArrayList<ResultMapping>() {
188               {
189                 add(new ResultMapping.Builder(config, "favouriteSection", "favourite_section", registry.getTypeHandler(Section.class)).build());
190                 add(new ResultMapping.Builder(config, null, "not_exists", Object.class).build());
191               }
192             }).build());
193           }
194         }).fetchSize(1000).timeout(2000).build();
195   }
196 
197   static MappedStatement prepareSelectOneAuthorMappedStatementWithConstructorResults(final Configuration config) {
198     final TypeHandlerRegistry registry = config.getTypeHandlerRegistry();
199     return new MappedStatement.Builder(config, "selectAuthor", new StaticSqlSource(config,"SELECT * FROM author WHERE id = ?"), SqlCommandType.SELECT)
200         .parameterMap(new ParameterMap.Builder(config, "defaultParameterMap", Author.class,
201             new ArrayList<ParameterMapping>() {
202               {
203                 add(new ParameterMapping.Builder(config, "id", registry.getTypeHandler(int.class)).build());
204               }
205             }).build())
206         .resultMaps(new ArrayList<ResultMap>() {
207           {
208             add(new ResultMap.Builder(config, "defaultResultMap", Author.class, new ArrayList<ResultMapping>() {
209               {
210                 add(new ResultMapping.Builder(config, null, "id", registry.getTypeHandler(Integer.class)).javaType(int.class).flags(new ArrayList<ResultFlag>() {
211                   {
212                     add(ResultFlag.CONSTRUCTOR);
213                   }
214                 }).build());
215                 add(new ResultMapping.Builder(config, "username", "username", registry.getTypeHandler(String.class)).build());
216                 add(new ResultMapping.Builder(config, "password", "password", registry.getTypeHandler(String.class)).build());
217                 add(new ResultMapping.Builder(config, "email", "email", registry.getTypeHandler(String.class)).build());
218                 add(new ResultMapping.Builder(config, "bio", "bio", registry.getTypeHandler(String.class)).build());
219                 add(new ResultMapping.Builder(config, "favouriteSection", "favourite_section", registry.getTypeHandler(Section.class)).build());
220               }
221             }).build());
222           }
223         })
224         .cache(authorCache)
225         .build();
226   }
227 
228   static MappedStatement prepareSelectTwoSetsOfAuthorsProc(final Configuration config) {
229     final TypeHandlerRegistry registry = config.getTypeHandlerRegistry();
230     return new MappedStatement.Builder(config, "selectTwoSetsOfAuthors", new StaticSqlSource(config,"{call selectTwoSetsOfAuthors(?,?)}"), SqlCommandType.SELECT)
231         .statementType(StatementType.CALLABLE)
232         .parameterMap(new ParameterMap.Builder(
233             config, "defaultParameterMap", Author.class,
234             new ArrayList<ParameterMapping>() {
235               {
236                 add(new ParameterMapping.Builder(config, "id1", registry.getTypeHandler(int.class)).build());
237                 add(new ParameterMapping.Builder(config, "id2", registry.getTypeHandler(int.class)).build());
238               }
239             }).build())
240         .resultMaps(new ArrayList<ResultMap>() {
241           {
242             ResultMap map = new ResultMap.Builder(config, "defaultResultMap", Author.class, new ArrayList<ResultMapping>() {
243               {
244                 add(new ResultMapping.Builder(config, "id", "id", registry.getTypeHandler(int.class)).build());
245                 add(new ResultMapping.Builder(config, "username", "username", registry.getTypeHandler(String.class)).build());
246                 add(new ResultMapping.Builder(config, "password", "password", registry.getTypeHandler(String.class)).build());
247                 add(new ResultMapping.Builder(config, "email", "email", registry.getTypeHandler(String.class)).build());
248                 add(new ResultMapping.Builder(config, "bio", "bio", registry.getTypeHandler(String.class)).build());
249               }
250             }).build();
251             add(map);
252             add(map);
253           }
254         }).build();
255   }
256 
257   static MappedStatement prepareSelectAuthorViaOutParams(final Configuration config) {
258     final TypeHandlerRegistry registry = config.getTypeHandlerRegistry();
259     return new MappedStatement.Builder(config, "selectAuthorViaOutParams", new StaticSqlSource(config, "{call selectAuthorViaOutParams(?,?,?,?,?)}"), SqlCommandType.SELECT)
260         .statementType(StatementType.CALLABLE)
261         .parameterMap(new ParameterMap.Builder(config, "defaultParameterMap", Author.class,
262             new ArrayList<ParameterMapping>() {
263               {
264                 add(new ParameterMapping.Builder(config, "id", registry.getTypeHandler(int.class)).build());
265                 add(new ParameterMapping.Builder(config, "username", registry.getTypeHandler(String.class)).jdbcType(JdbcType.VARCHAR).mode(ParameterMode.OUT).build());
266                 add(new ParameterMapping.Builder(config, "password", registry.getTypeHandler(String.class)).jdbcType(JdbcType.VARCHAR).mode(ParameterMode.OUT).build());
267                 add(new ParameterMapping.Builder(config, "email", registry.getTypeHandler(String.class)).jdbcType(JdbcType.VARCHAR).mode(ParameterMode.OUT).build());
268                 add(new ParameterMapping.Builder(config, "bio", registry.getTypeHandler(String.class)).jdbcType(JdbcType.VARCHAR).mode(ParameterMode.OUT).build());
269               }
270             }).build())
271         .resultMaps(new ArrayList<>())
272         .cache(authorCache).build();
273   }
274 
275   static MappedStatement prepareSelectDiscriminatedPost(final Configuration config) {
276     final TypeHandlerRegistry registry = config.getTypeHandlerRegistry();
277     final ResultMap discriminatorResultMap = new ResultMap.Builder(config, "postResultMap", HashMap.class, new ArrayList<ResultMapping>() {
278       {
279         add(new ResultMapping.Builder(config, "subject", "subject", registry.getTypeHandler(String.class)).build());
280         add(new ResultMapping.Builder(config, "body", "body", registry.getTypeHandler(String.class)).build());
281       }
282     }).build();
283     config.addResultMap(discriminatorResultMap);
284     return new MappedStatement.Builder(config, "selectPosts", new StaticSqlSource(config,"SELECT * FROM post"), SqlCommandType.SELECT)
285         .resultMaps(new ArrayList<ResultMap>() {
286           {
287             add(new ResultMap.Builder(config, "defaultResultMap", HashMap.class, new ArrayList<ResultMapping>() {
288               {
289                 add(new ResultMapping.Builder(config, "id", "id", registry.getTypeHandler(int.class)).build());
290                 add(new ResultMapping.Builder(config, "blog_id", "blog_id", registry.getTypeHandler(int.class)).build());
291               }
292             })
293                 .discriminator(new Discriminator.Builder(
294                     config, new ResultMapping.Builder(config, "section", "section", registry.getTypeHandler(String.class)).build(),
295                     new HashMap<String, String>() {
296                       {
297                         put("NEWS", discriminatorResultMap.getId());
298                         put("VIDEOS", discriminatorResultMap.getId());
299                         put("PODCASTS", discriminatorResultMap.getId());
300                         //NEWS left out on purpose.
301                       }
302                     }).build()).build());
303 
304           }
305         }).build();
306   }
307 
308   static MappedStatement createInsertAuthorWithIDof99MappedStatement(final Configuration config) {
309     return new MappedStatement.Builder(config, "insertAuthor", new StaticSqlSource(config,"INSERT INTO author (id,username,password,email,bio) values(99,'someone','******','someone@apache.org',null)"), SqlCommandType.INSERT)
310         .statementType(StatementType.STATEMENT)
311         .parameterMap(new ParameterMap.Builder(config, "defaultParameterMap", Author.class,
312             new ArrayList<>()).build())
313         .cache(authorCache)
314         .build();
315   }
316 
317   static MappedStatement createSelectAuthorWithIDof99MappedStatement(final Configuration config) {
318     final TypeHandlerRegistry registry = config.getTypeHandlerRegistry();
319     return new MappedStatement.Builder(config, "selectAuthor", new StaticSqlSource(config,"SELECT * FROM author WHERE id = 99"), SqlCommandType.SELECT)
320         .statementType(StatementType.STATEMENT)
321         .parameterMap(new ParameterMap.Builder(config, "defaultParameterMap", Author.class, new ArrayList<>()).build())
322         .resultMaps(new ArrayList<ResultMap>() {
323           {
324             add(new ResultMap.Builder(config, "defaultResultMap", Author.class, new ArrayList<ResultMapping>() {
325               {
326                 add(new ResultMapping.Builder(config, "id", "id", registry.getTypeHandler(int.class)).build());
327                 add(new ResultMapping.Builder(config, "username", "username", registry.getTypeHandler(String.class)).build());
328                 add(new ResultMapping.Builder(config, "password", "password", registry.getTypeHandler(String.class)).build());
329                 add(new ResultMapping.Builder(config, "email", "email", registry.getTypeHandler(String.class)).build());
330                 add(new ResultMapping.Builder(config, "bio", "bio", registry.getTypeHandler(String.class)).build());
331               }
332             }).build());
333           }
334         }).build();
335   }
336 
337   static MappedStatement prepareComplexSelectBlogMappedStatement(final Configuration config) {
338     final TypeHandlerRegistry registry = config.getTypeHandlerRegistry();
339     final SqlSource sqlSource = new StaticSqlSource(config, "SELECT b.id, b.author_id, b.title, a.username, a.password, a.email, a.bio" +
340         " FROM blog b" +
341         " INNER JOIN author a ON b.author_id = a.id" +
342         " WHERE b.id = ?");
343     final ParameterMap parameterMap = new ParameterMap.Builder(config, "defaultParameterMap", int.class,
344         new ArrayList<ParameterMapping>() {
345           {
346             add(new ParameterMapping.Builder(config, "id", registry.getTypeHandler(int.class)).build());
347           }
348         }).build();
349     final ResultMap resultMap = new ResultMap.Builder(config, "defaultResultMap", Blog.class, new ArrayList<ResultMapping>() {
350       {
351         add(new ResultMapping.Builder(config, "id", "id", registry.getTypeHandler(int.class))
352             .flags(new ArrayList<ResultFlag>() {
353               {
354                 add(ResultFlag.ID);
355               }
356             }).build());
357         add(new ResultMapping.Builder(config, "title", "title", registry.getTypeHandler(String.class)).build());
358         add(new ResultMapping.Builder(config, "author.id", "author_id", registry.getTypeHandler(int.class)).build());
359         add(new ResultMapping.Builder(config, "author.username", "username", registry.getTypeHandler(String.class)).build());
360         add(new ResultMapping.Builder(config, "author.password", "password", registry.getTypeHandler(String.class)).build());
361         add(new ResultMapping.Builder(config, "author.email", "email", registry.getTypeHandler(String.class)).build());
362         add(new ResultMapping.Builder(config, "author.bio", "bio", registry.getTypeHandler(String.class)).build());
363         add(new ResultMapping.Builder(config, "posts", "id", registry.getTypeHandler(int.class)).javaType(List.class).nestedQueryId("selectPostsForBlog").build());
364       }
365     }).build();
366 
367     return new MappedStatement.Builder(config, "selectBlogById", sqlSource, SqlCommandType.SELECT)
368         .parameterMap(parameterMap)
369         .resultMaps(new ArrayList<ResultMap>() {
370           {
371             add(resultMap);
372           }
373         }).build();
374   }
375 
376   static MappedStatement prepareSelectBlogByIdAndAuthor(final Configuration config) {
377     final TypeHandlerRegistry registry = config.getTypeHandlerRegistry();
378     final SqlSource sqlSource = new StaticSqlSource(config,"SELECT b.id, b.author_id, b.title, a.username, a.password, a.email, a.bio" +
379         " FROM blog b" +
380         " INNER JOIN author a ON b.author_id = a.id" +
381         " WHERE b.id = ? and a.id = ?");
382     final ParameterMap parameterMap = new ParameterMap.Builder(config, "defaultParameterMap", Map.class,
383         new ArrayList<ParameterMapping>() {
384           {
385             add(new ParameterMapping.Builder(config, "blogId", registry.getTypeHandler(int.class)).build());
386             add(new ParameterMapping.Builder(config, "authorId", registry.getTypeHandler(int.class)).build());
387           }
388         }).build();
389     final ResultMap resultMap = new ResultMap.Builder(config, "defaultResultMap", Blog.class, new ArrayList<ResultMapping>() {
390       {
391         add(new ResultMapping.Builder(config, "id", "id", registry.getTypeHandler(int.class))
392             .flags(new ArrayList<ResultFlag>() {
393               {
394                 add(ResultFlag.ID);
395               }
396             }).build());
397         add(new ResultMapping.Builder(config, "title", "title", registry.getTypeHandler(String.class)).build());
398         add(new ResultMapping.Builder(config, "author.id", "author_id", registry.getTypeHandler(int.class)).build());
399         add(new ResultMapping.Builder(config, "author.username", "username", registry.getTypeHandler(String.class)).build());
400         add(new ResultMapping.Builder(config, "author.password", "password", registry.getTypeHandler(String.class)).build());
401         add(new ResultMapping.Builder(config, "author.email", "email", registry.getTypeHandler(String.class)).build());
402         add(new ResultMapping.Builder(config, "author.bio", "bio", registry.getTypeHandler(String.class)).build());
403         add(new ResultMapping.Builder(config, "posts", "id", registry.getTypeHandler(int.class)).javaType(List.class).nestedQueryId("selectPostsForBlog").build());
404       }
405     }).build();
406 
407     return new MappedStatement.Builder(config, "selectBlogByIdAndAuthor", sqlSource, SqlCommandType.SELECT)
408         .parameterMap(parameterMap)
409         .resultMaps(new ArrayList<ResultMap>() {
410           {
411             add(resultMap);
412           }
413         }).build();
414 
415   }
416 
417   static MappedStatement prepareSelectPostsForBlogMappedStatement(final Configuration config) {
418     final TypeHandlerRegistry registry = config.getTypeHandlerRegistry();
419     final SqlSource sqlSource = new StaticSqlSource(config,"SELECT p.id, p.created_on, p.blog_id, p.section, p.subject, p.body, pt.tag_id," +
420         " t.name as tag_name, c.id as comment_id, c.name as comment_name, c.comment" +
421         " FROM post p" +
422         " INNER JOIN post_tag pt ON pt.post_id = p.id" +
423         " INNER JOIN tag t ON pt.tag_id = t.id" +
424         " LEFT OUTER JOIN comment c ON c.post_id = p.id" +
425         " WHERE p.blog_id = ?");
426     final ParameterMap parameterMap = new ParameterMap.Builder(config, "defaultParameterMap", Author.class,
427         new ArrayList<ParameterMapping>() {
428           {
429             add(new ParameterMapping.Builder(config, "id", registry.getTypeHandler(int.class)).build());
430           }
431         }).build();
432     final ResultMap tagResultMap = new ResultMap.Builder(config, "tagResultMap", Tag.class, new ArrayList<ResultMapping>() {
433       {
434         add(new ResultMapping.Builder(config, "id", "tag_id", registry.getTypeHandler(int.class))
435             .flags(new ArrayList<ResultFlag>() {
436               {
437                 add(ResultFlag.ID);
438               }
439             }).build());
440         add(new ResultMapping.Builder(config, "name", "tag_name", registry.getTypeHandler(String.class)).build());
441       }
442     }).build();
443     final ResultMap commentResultMap = new ResultMap.Builder(config, "commentResultMap", Comment.class, new ArrayList<ResultMapping>() {
444       {
445         add(new ResultMapping.Builder(config, "id", "comment_id", registry.getTypeHandler(int.class))
446             .flags(new ArrayList<ResultFlag>() {
447               {
448                 add(ResultFlag.ID);
449               }
450             }).build());
451         add(new ResultMapping.Builder(config, "name", "comment_name", registry.getTypeHandler(String.class)).build());
452         add(new ResultMapping.Builder(config, "comment", "comment", registry.getTypeHandler(String.class)).build());
453       }
454     }).build();
455     config.addResultMap(tagResultMap);
456     config.addResultMap(commentResultMap);
457     final ResultMap postResultMap = new ResultMap.Builder(config, "defaultResultMap", Post.class, new ArrayList<ResultMapping>() {
458       {
459         add(new ResultMapping.Builder(config, "id", "id", registry.getTypeHandler(int.class))
460             .flags(new ArrayList<ResultFlag>() {
461               {
462                 add(ResultFlag.ID);
463               }
464             }).build());
465         add(new ResultMapping.Builder(config, "blog", "blog_id", registry.getTypeHandler(int.class)).javaType(Blog.class).nestedQueryId("selectBlogById").build());
466         add(new ResultMapping.Builder(config, "createdOn", "created_on", registry.getTypeHandler(Date.class)).build());
467         add(new ResultMapping.Builder(config, "section", "section", registry.getTypeHandler(Section.class)).build());
468         add(new ResultMapping.Builder(config, "subject", "subject", registry.getTypeHandler(String.class)).build());
469         add(new ResultMapping.Builder(config, "body", "body", registry.getTypeHandler(String.class)).build());
470         add(new ResultMapping.Builder(config, "tags").nestedResultMapId(tagResultMap.getId()).build());
471         add(new ResultMapping.Builder(config, "comments").nestedResultMapId(commentResultMap.getId()).build());
472       }
473     }).build();
474     return new MappedStatement.Builder(config, "selectPostsForBlog", sqlSource, SqlCommandType.SELECT)
475         .parameterMap(parameterMap)
476         .resultMaps(new ArrayList<ResultMap>() {
477           {
478             add(postResultMap);
479           }
480         }).build();
481   }
482 
483   static MappedStatement prepareSelectPostMappedStatement(final Configuration config) {
484     final TypeHandlerRegistry registry = config.getTypeHandlerRegistry();
485     final SqlSource sqlSource = new StaticSqlSource(config,"SELECT p.id, p.created_on, p.blog_id, p.section, p.subject, p.body, pt.tag_id," +
486         " t.name as tag_name, c.id as comment_id, c.name as comment_name, c.comment" +
487         " FROM post p" +
488         " LEFT OUTER JOIN post_tag pt ON pt.post_id = p.id" +
489         " LEFT OUTER JOIN tag t ON pt.tag_id = t.id" +
490         " LEFT OUTER JOIN comment c ON c.post_id = p.id" +
491         " WHERE p.id = ?");
492     final ParameterMap parameterMap = new ParameterMap.Builder(config, "defaultParameterMap", Author.class,
493         new ArrayList<ParameterMapping>() {
494           {
495             add(new ParameterMapping.Builder(config, "id", registry.getTypeHandler(int.class)).build());
496           }
497         }).build();
498     final ResultMap tagResultMap = new ResultMap.Builder(config, "tagResultMap", Tag.class, new ArrayList<ResultMapping>() {
499       {
500         add(new ResultMapping.Builder(config, "id", "tag_id", registry.getTypeHandler(int.class))
501             .flags(new ArrayList<ResultFlag>() {
502               {
503                 add(ResultFlag.ID);
504               }
505             }).build());
506         add(new ResultMapping.Builder(config, "name", "tag_name", registry.getTypeHandler(String.class)).build());
507       }
508     }).build();
509     final ResultMap commentResultMap = new ResultMap.Builder(config, "commentResultMap", Comment.class, new ArrayList<ResultMapping>() {
510       {
511         add(new ResultMapping.Builder(config, "id", "comment_id", registry.getTypeHandler(int.class))
512             .flags(new ArrayList<ResultFlag>() {
513               {
514                 add(ResultFlag.ID);
515               }
516             }).build());
517         add(new ResultMapping.Builder(config, "name", "comment_name", registry.getTypeHandler(String.class)).build());
518         add(new ResultMapping.Builder(config, "comment", "comment", registry.getTypeHandler(String.class)).build());
519       }
520     }).build();
521     config.addResultMap(tagResultMap);
522     config.addResultMap(commentResultMap);
523     final ResultMap postResultMap = new ResultMap.Builder(config, "", Post.class, new ArrayList<ResultMapping>() {
524       {
525         add(new ResultMapping.Builder(config, "id", "id", registry.getTypeHandler(int.class))
526             .flags(new ArrayList<ResultFlag>() {
527               {
528                 add(ResultFlag.ID);
529               }
530             }).build());
531         add(new ResultMapping.Builder(config, "blog", "blog_id", registry.getTypeHandler(int.class)).javaType(Blog.class).nestedQueryId("selectBlogById").build());
532         add(new ResultMapping.Builder(config, "createdOn", "created_on", registry.getTypeHandler(Date.class)).build());
533         add(new ResultMapping.Builder(config, "section", "section", registry.getTypeHandler(Section.class)).build());
534         add(new ResultMapping.Builder(config, "subject", "subject", registry.getTypeHandler(String.class)).build());
535         add(new ResultMapping.Builder(config, "body", "body", registry.getTypeHandler(String.class)).build());
536         add(new ResultMapping.Builder(config, "tags").nestedResultMapId(tagResultMap.getId()).build());
537         add(new ResultMapping.Builder(config, "comments").nestedResultMapId(commentResultMap.getId()).build());
538       }
539     }).build();
540 
541 
542     return new MappedStatement.Builder(config, "selectPostsForBlog", sqlSource, SqlCommandType.SELECT)
543         .parameterMap(parameterMap)
544         .resultMaps(new ArrayList<ResultMap>() {
545           {
546             add(postResultMap);
547           }
548         }).build();
549   }
550 
551 
552   static MappedStatement prepareSelectPostWithBlogByAuthorMappedStatement(final Configuration config) {
553     final TypeHandlerRegistry registry = config.getTypeHandlerRegistry();
554     final SqlSource sqlSource = new StaticSqlSource(config,"SELECT p.id, p.created_on, p.blog_id, p.author_id, p.section, p.subject, p.body, pt.tag_id," +
555         " t.name as tag_name, c.id as comment_id, c.name as comment_name, c.comment" +
556         " FROM post p" +
557         " LEFT OUTER JOIN post_tag pt ON pt.post_id = p.id" +
558         " LEFT OUTER JOIN tag t ON pt.tag_id = t.id" +
559         " LEFT OUTER JOIN comment c ON c.post_id = p.id" +
560         " WHERE p.id = ?");
561     final ParameterMap parameterMap = new ParameterMap.Builder(config, "defaultParameterMap", Author.class,
562         new ArrayList<ParameterMapping>() {
563           {
564             add(new ParameterMapping.Builder(config, "id", registry.getTypeHandler(int.class)).build());
565           }
566         }).build();
567     final ResultMap tagResultMap = new ResultMap.Builder(config, "tagResultMap", Tag.class, new ArrayList<ResultMapping>() {
568       {
569         add(new ResultMapping.Builder(config, "id", "tag_id", registry.getTypeHandler(int.class))
570             .flags(new ArrayList<ResultFlag>() {
571               {
572                 add(ResultFlag.ID);
573               }
574             }).build());
575         add(new ResultMapping.Builder(config, "name", "tag_name", registry.getTypeHandler(String.class)).build());
576       }
577     }).build();
578     final ResultMap commentResultMap = new ResultMap.Builder(config, "commentResultMap", Comment.class, new ArrayList<ResultMapping>() {
579       {
580         add(new ResultMapping.Builder(config, "id", "comment_id", registry.getTypeHandler(int.class))
581             .flags(new ArrayList<ResultFlag>() {
582               {
583                 add(ResultFlag.ID);
584               }
585             }).build());
586         add(new ResultMapping.Builder(config, "name", "comment_name", registry.getTypeHandler(String.class)).build());
587         add(new ResultMapping.Builder(config, "comment", "comment", registry.getTypeHandler(String.class)).build());
588       }
589     }).build();
590     config.addResultMap(tagResultMap);
591     config.addResultMap(commentResultMap);
592     final ResultMap postResultMap = new ResultMap.Builder(config, "postResultMap", Post.class, new ArrayList<ResultMapping>() {
593       {
594         add(new ResultMapping.Builder(config, "id", "id", registry.getTypeHandler(int.class))
595             .flags(new ArrayList<ResultFlag>() {
596               {
597                 add(ResultFlag.ID);
598               }
599             }).build());
600 
601         add(new ResultMapping.Builder(config, "blog").nestedQueryId("selectBlogByIdAndAuthor").composites(new ArrayList<ResultMapping>() {
602           {
603             add(new ResultMapping.Builder(config, "authorId", "author_id", registry.getTypeHandler(int.class)).build());
604             add(new ResultMapping.Builder(config, "blogId", "blog_id", registry.getTypeHandler(int.class)).build());
605           }
606         }).build());
607         add(new ResultMapping.Builder(config, "createdOn", "created_on", registry.getTypeHandler(Date.class)).build());
608         add(new ResultMapping.Builder(config, "section", "section", registry.getTypeHandler(Section.class)).build());
609         add(new ResultMapping.Builder(config, "subject", "subject", registry.getTypeHandler(String.class)).build());
610         add(new ResultMapping.Builder(config, "body", "body", registry.getTypeHandler(String.class)).build());
611         add(new ResultMapping.Builder(config, "tags").nestedResultMapId(tagResultMap.getId()).build());
612         add(new ResultMapping.Builder(config, "comments").nestedResultMapId(commentResultMap.getId()).build());
613       }
614     }).build();
615 
616 
617     return new MappedStatement.Builder(config, "selectPostsForBlog", sqlSource, SqlCommandType.SELECT)
618         .parameterMap(parameterMap)
619         .resultMaps(new ArrayList<ResultMap>() {
620           {
621             add(postResultMap);
622           }
623         }).build();
624   }
625 
626 
627   static MappedStatement prepareInsertAuthorMappedStatementWithBeforeAutoKey(final Configuration config) {
628     final TypeHandlerRegistry registry = config.getTypeHandlerRegistry();
629     final ResultMap rm = new ResultMap.Builder(config, "keyResultMap", Integer.class, new ArrayList<>())
630         .build();
631 
632     MappedStatement kms = new MappedStatement.Builder(config, "insertAuthor!selectKey", new StaticSqlSource(config,"SELECT 123456 as id FROM SYSIBM.SYSDUMMY1"), SqlCommandType.SELECT)
633         .keyProperty("id")
634         .resultMaps(new ArrayList<ResultMap>() {
635           {
636             add(rm);
637           }
638         })
639         .build();
640     config.addMappedStatement(kms);
641     return new MappedStatement.Builder(config, "insertAuthor", new DynamicSqlSource(config, new TextSqlNode("INSERT INTO author (id,username,password,email,bio,favourite_section) values(#{id},#{username},#{password},#{email},#{bio:VARCHAR},#{favouriteSection})")), SqlCommandType.INSERT)
642         .parameterMap(
643             new ParameterMap.Builder(config, "defaultParameterMap", Author.class, new ArrayList<ParameterMapping>() {
644               {
645                 add(new ParameterMapping.Builder(config, "id", registry.getTypeHandler(Integer.class)).build());
646                 add(new ParameterMapping.Builder(config, "username", registry.getTypeHandler(String.class)).build());
647                 add(new ParameterMapping.Builder(config, "password", registry.getTypeHandler(String.class)).build());
648                 add(new ParameterMapping.Builder(config, "email", registry.getTypeHandler(String.class)).build());
649                 add(new ParameterMapping.Builder(config, "bio", registry.getTypeHandler(String.class)).jdbcType(JdbcType.VARCHAR).build());
650                 add(new ParameterMapping.Builder(config, "favouriteSection", registry.getTypeHandler(Section.class)).jdbcType(JdbcType.VARCHAR).build());
651               }
652             }).build())
653         .cache(authorCache)
654         .keyGenerator(new SelectKeyGenerator(kms, true))
655         .keyProperty("id")
656         .build();
657   }
658 
659 
660 }