12. Dynamic SQL
12.1 introduction
-
What is dynamic SQL: dynamic SQL refers to generating different SQL statements according to different conditions.
-
Using the feature of dynamic SQL can completely get rid of this pain.
-
Dynamic SQL elements are similar to JSTL or XML based text processors.
-
In previous versions of MyBatis, there were many elements that needed time to understand.
-
MyBatis3 has greatly reduced the types of elements. Now you only need to learn half of the original elements.
-
MyBatis uses powerful OGNL based expressions to eliminate most other elements.
-
Dynamic SQL is splicing SQL statements. We just need to ensure the correctness of SQL and arrange and combine them according to the SQL format.
-
Recommendations:
- First write a complete SQL in Mysql, and then modify the corresponding to become our dynamic SQL implementation.
-
if choose(when,otherwise) trim(where,set) foreach
12.2 construction environment
-
SQL statement
-
CREATE TABLE `blog` ( `id` varchar(50) NOT NULL COMMENT 'Blog ID', `title` varchar(100) NOT NULL COMMENT 'Blog title', `author` varchar(30) NOT NULL COMMENT 'Blogger', `create_time` datetime NOT NULL COMMENT 'Creation time', `views` int(30) NOT NULL COMMENT 'Views' ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-
Create a basic project
-
Guide bag.
-
Write a configuration file.
-
Write entity classes.
-
public class Blog { private String id; private String title; private String author; private Date createTime;//The property name and field name are inconsistent private int views; }
-
-
Write the Mapper interface and Mapper.xml file corresponding to the entity class.
-
//insert data int addBlog(Blog blog);
-
<insert id="addBlog" parameterType="blog"> insert into blog (id, title, author, create_time, views) values (#,#,#,#,#) </insert>
-
-
IDUtils tool class
-
@SuppressWarnings("all") //Suppress warning public class IDUtils { public static String getId(){ return UUID.randomUUID().toString().replaceAll("-",""); } @Test public void test(){ System.out.println(IDUtils.getId()); System.out.println(IDUtils.getId()); System.out.println(IDUtils.getId()); } }
-
Suppress warning
- @SuppressWarnings("all")
-
-
test
-
@Test public void testAddBlog() { SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); Blog blog = new Blog(); blog.setId(IDUtils.getId()); blog.setTitle("Mybatis So simple"); blog.setAuthor("Jcooling"); blog.setCreateTime(new Date()); blog.setViews(9999); mapper.addBlog(blog); blog.setId(IDUtils.getId()); blog.setTitle("Java So simple"); mapper.addBlog(blog); blog.setId(IDUtils.getId()); blog.setTitle("Spring So simple"); mapper.addBlog(blog); blog.setId(IDUtils.getId()); blog.setTitle("Linux So simple"); mapper.addBlog(blog); sqlSession.commit(); sqlSession.close(); }
-
-
12.3,IF
-
Interface class
-
//Query blog List<Blog> queryBlogIf(Map map);
-
-
Corresponding xml
-
<select id="queryBlogIf" parameterType="map" resultType="blog"> select * from blog where 1=1 <if test="title!=null"> and title=# </if> <if test="author!=null"> and author=# </if> </select>
-
-
test
-
@Test public void testQueryBlogIf() { SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap map = new HashMap(); // map.put("title","Linux is so simple"); map.put("author","JCooling"); List<Blog> blogs = mapper.queryBlogIf(map); for (Blog blog : blogs) { System.out.println(blog); } sqlSession.close(); }
-
12.4,choose(when,otherwise)
-
Interface class
-
//query List<Blog> queryBlogChoose(Map map);
-
-
Corresponding xml
-
<select id="queryBlogChoose" parameterType="map" resultType="blog"> select * from blog <where> <choose> <when test="title!=null"> title=# </when> <when test="author!=null"> and author=# </when> <otherwise> and views=# </otherwise> </choose> </where> </select>
-
-
test
-
@Test public void testQueryBlogChoose() { SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap map = new HashMap(); map.put("title","Linux So simple"); // map.put("author","JCooling"); map.put("views","9999"); List<Blog> blogs = mapper.queryBlogChoose(map); for (Blog blog : blogs) { System.out.println(blog); } sqlSession.close(); }
-
12.5,trim(where,set)
-
Interface class
-
//Query blog List<Blog> queryBlogIf(Map map); //Update blog int updateBlogSet(Map map);
-
-
Corresponding xml
-
<select id="queryBlogIf" parameterType="map" resultType="blog"> select * from blog <where> <if test="title!=null"> title=# </if> <if test="author!=null"> and author=# </if> </where> </select> <update id="updateBlogSet" parameterType="map"> update blog <set> <if test="title!=null"> title=#, </if> <if test="author!=null"> author=# </if> </set> where id=# </update>
-
-
test
-
@Test public void testQueryBlogIf() { //Ibid } @Test public void testUpdateBlogSet() { SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap map = new HashMap(); map.put("title","Linux So simple 2"); //map.put("author","JCooling"); map.put("id","66c94df75eb34c8eab1a82c415b28dc2"); int i = mapper.updateBlogSet(map); if (i==1) { System.out.println("Update succeeded"); }else { System.out.println("Update failed"); } sqlSession.commit(); sqlSession.close(); }
-
-
The set element will dynamically prefix the set keyword and delete irrelevant commas.
-
trim
-
//prefixOverrides prefix //suffixOverrides suffix <trim prefix="where" prefixOverrides="and|or" suffixOverrides=""></trim>
-
-
The so-called dynamic SQL is still an SQL statement in essence, but we can execute a logical code at the SQL level.
12.6,foreach
-
Another common operation requirement of dynamic SQL is to traverse a set, usually when constructing IN conditional statements.
-
The foreach element is very powerful. It allows you to specify a collection and declare collection item s and index variables that can be used in the element body.
-
It also allows you to specify the beginning and end strings and place separators between the iteration results. Extra delimiters are not accidentally appended.
-
be careful
- You can pass any iteratable object (such as List, Set, etc.), Map object or array object to foreach as a Set parameter.
- When an iteratable object or array is used, index is the number of current iterations, and the value of item is the element obtained in this iteration.
- When using a Map object (or a collection of Map.Entry objects), index is the key and item is the value.
-
Interface class
-
//Check the blog of records 1, 2 and 3 List<Blog> queryBlogForeach(Map map);
-
-
Corresponding xml
-
<!--We are now passing on a universal message map，this map There can be a collection in!--> <select id="queryBlogForeach" parameterType="map" resultType="blog"> select * from blog_1 <where> <foreach collection="ids" item="id" open="and (" close=")" separator="or"> id=# </foreach> </where> </select>
-
-
test
-
@Test public void testQueryBlogForeach() { SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap map = new HashMap(); ArrayList<Integer> ids = new ArrayList<Integer>(); ids.add(1); ids.add(2); map.put("ids",ids); List<Blog> blogs = mapper.queryBlogForeach(map); for (Blog blog : blogs) { System.out.println(blog); } sqlSession.close(); }
-
12.7 SQL fragment
-
Sometimes, we may extract some functional parts for reuse.
-
Interface class
-
//Query blog List<Blog> queryBlogIf1(Map map);
-
-
Corresponding xml
-
//Extract common parts using SQL Tags <sql id="if-title-author"> <if test="title!=null"> title=# </if> <if test="author!=null"> and author=# </if> </sql> //Use the include tag reference where necessary <select id="queryBlogIf1" parameterType="map" resultType="blog"> select * from blog <where> <include refid="if-title-author"></include> </where> </select>
-
-
test
-
@Test public void testQueryBlogIf1() { //Ibid }
-
-
matters needing attention
- It is best to define SQL fragments based on a single table.
- Do not have a where tag.