Spring + SpringMVC + MyBatis deep learning and setup (5) -- dynamic SQL and springmvcmybatis

Spring + SpringMVC + MyBatis deep learning and setup (5) -- dynamic SQL and springmvcmybatis

Reprinted please indicate the source: http://www.cnblogs.com/Joanna-Yan/p/6908763.html

I have talked about Spring + SpringMVC + MyBatis deep learning and building (4) -- MyBatis input ing and output ing

Mybatis core: flexibly operate SQL statements, Judge by expressions, and flexibly splice and assemble SQL statements.

Mybatis provides various tag methods to dynamically splice SQL statements.

1. if & where1.2 requirements

Dynamic SQL is used to define the statement of user information comprehensive query list and user information query list total.

The query condition is determined. If the input parameter is not blank, the query condition is spliced.

1.3 mapper. xml

<Select id = "findUserList" parameterType = "joanna. yan. mybatis. entity. UserQueryVo" resultType = "joanna. yan. mybatis. entity. UserCustom"> SELECT * from user <! -- Where can automatically remove the first and --> <where> <if test = "userCustom! = Null "> <if test =" userCustom. sex! = Null and userCustom. sex! = ''"> And user. sex =#{ userCustom. sex} </if> <if test = "userCustom. username! = Null and userCustom. username! = ''"> And user. username LIKE '% $ {userCustom. username} %' </if> </where> </select>

<Select id = "findUserCount" parameterType = "joanna. yan. mybatis. entity. UserQueryVo" resultType = "int"> SELECT count (*) from user <! -- Where can automatically remove the first and --> <where> <if test = "userCustom! = Null "> <if test =" userCustom. sex! = Null and userCustom. sex! = ''"> And user. sex =#{ userCustom. sex} </if> <if test = "userCustom. username! = Null and userCustom. username! = ''"> And user. username LIKE '% $ {userCustom. username} %' </if> </where> </select>

1.4 test code

@ Test public void findUserListTest () throws Exception {SqlSession sqlSession = sqlSessionFactory. openSession (); UserMapper userMapper = sqlSession. getMapper (UserMapper. class); // create a packaging object and set the query condition UserQueryVo userQueryVo = new UserQueryVo (); UserCustom userCustom = new UserCustom (); // because dynamic SQL is used here, if a value is not set, the condition is not concatenated in SQL // userCustom. setSex ("1"); userCustom. setUsername ("Zhang Sanfeng"); userQueryVo. setUserCustom (userCustom); List <UserCustom> list = userMapper. findUserList (userQueryVo); System. out. println (list );}

Printed SQL: If no sex value is set, the conditions are not spliced in SQL.

2. SQL segment 2.1 Requirements

Extract the dynamic SQL judgment code block implemented above to form an SQL segment. The SQL segment can be referenced in other statement statements. This facilitates development by programmers.

2.2 define SQL segments

<! -- Define SQL fragment id: Unique Identification experience of SQL fragment: 1. the SQL segment is defined based on a single table, so that the SQL segment can be reused. do not include where --> <SQL id = "query_user_where"> <if test = "userCustom! = Null "> <if test =" userCustom. sex! = Null and userCustom. sex! = ''"> And user. sex =#{ userCustom. sex} </if> <if test = "userCustom. username! = Null and userCustom. username! = ''"> And user. username LIKE '% $ {userCustom. username} %' </if> </SQL>

2.3 reference SQL snippets

Define statement in mapper. xml to reference the SQL segment:

<Select id = "findUserList" parameterType = "joanna. yan. mybatis. entity. UserQueryVo" resultType = "joanna. yan. mybatis. entity. UserCustom"> SELECT * from user <! -- Where can automatically remove the first and --> <where> <! -- Reference the id of the SQL fragment. If the id specified by refid is not in this mapper file, add namespace in front --> <include refid = "query_user_where"> </include> <! -- Other SQL fragments can be referenced here --> </where> </select>

<Select id = "findUserCount" parameterType = "joanna. yan. mybatis. entity. UserQueryVo" resultType = "int"> SELECT count (*) from user <! -- Where can automatically remove the first and --> <where> <! -- Reference the id of the SQL fragment. If the id specified by refid is not in this mapper file, add namespace in front --> <include refid = "query_user_where"> </include> <! -- Other SQL fragments can be referenced here --> </where> </select>

3. foreach

Pass an array or List to SQL. mybatis uses foreach for parsing.

3.1 requirements

Add multiple IDs to the user query list and the total number of queries.

The SQL statement is as follows:

SELECT * from user where id = 1 OR id = 10 OR id = 16

SELECT * from user where id IN (1, 10, 16)

3.2 add List <Integer> ids to the input parameter type to input multiple ids.

3.3 modify mapper. xml

WHERE id = 1 OR id = 10 OR id = 16

In the preceding query condition, the query condition is defined as an SQL segment. Now we need to modify the SQL segment.

<! -- Define SQL fragment id: Unique Identification experience of SQL fragment: 1. the SQL segment is defined based on a single table, so that the SQL segment can be reused. do not include where --> <SQL id = "query_user_where"> <if test = "userCustom! = Null "> <if test =" userCustom. sex! = Null and userCustom. sex! = ''"> And user. sex =#{ userCustom. sex} </if> <if test = "userCustom. username! = Null and userCustom. username! = ''"> And user. username LIKE '% $ {userCustom. username} %' </if> <if test = "ids! = Null "> <! -- Use foreach to traverse the passed ids collection: Specify the set property item of the input object: The name of the object generated by each traversal is open: the string that starts to splice and close: separator: the string to be spliced in the two objects to be traversed --> <! -- Is to achieve the following SQL concatenation: AND (id = 1 OR id = 10 OR id = 16) --> <foreach collection = "ids" item = "user_id" open = "AND (" close = ")" separator = "or"> <! -- The string to be concatenated for each traversal --> id =#{ user_id} </foreach> </if> </SQL>

3.4 test code

@ Test public void findUserListTest () throws Exception {SqlSession sqlSession = sqlSessionFactory. openSession (); UserMapper userMapper = sqlSession. getMapper (UserMapper. class); // create a packaging object and set the query condition UserQueryVo userQueryVo = new UserQueryVo (); UserCustom userCustom = new UserCustom (); // because dynamic SQL is used here, if a value is not set, the condition is not concatenated in SQL // userCustom. setSex ("1"); userCustom. setUsername ("James"); // Input Multiple id lists <Integer> ids = new ArrayList <> (); ids. add (1); ids. add (10); ids. add (16); userQueryVo. setIds (ids); userQueryVo. setUserCustom (userCustom); List <UserCustom> list = userMapper. findUserList (userQueryVo); System. out. println (list );}

If this article is helpful to you, please let me know ~