Hello, I'm a bookshelf.
In the first two sections, it mainly introduces that Mybatis framework completes the basic addition, deletion, modification and query according to Dao layer interface, mapping file (Mapper.xml file for writing SQL statement) and configuration file SqlMapConfig.xml.
This section will go deep into mapping files and introduce dynamic SQL. Dynamic SQL is introduced to better control the impact of incoming parameters on SQL statements in complex business scenarios.
The operating environment of the project still adopts [Mybatis] quick build 01 , I won't repeat it here.
2. Dynamic SQL if
The first magic weapon used in dynamic SQL is the if tag, which is usually used in where statements. If is mainly used for judgment.
Whether to use the parameter as the query condition is determined by judging whether the parameter value meets a certain condition. It is also often used to judge whether to update a field in the update statement, and can also be used to judge whether to insert the value of a field in the insert statement.
The following describes the above three scenarios one by one.
2.1 use if in query statement where
In order to better understand how to introduce if tags into where, we first introduce a common example. Find out the user's record according to the user's id, username and password.
Add findByCondition method to Dao layer interface UserMapper.
public User findByCondition(User user);
Mapping file usermapper Add in XML
<select id="findByCondition" resultType="com.zssj.domain.User">
select * from user where id = #{id} and username = #{username} and password = #{password}
</select>Test code in test class
@Test
public void test6() throws IOException {
//1. Read the core configuration file sqlmapconfig xml
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2. Create SqlSessionFactory factory
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(in);
//3. Use the factory to produce a SqlSession object
SqlSession session = factory.openSession();
//4. Use SqlSession to create the proxy object of Dao interface
UserMapper userMapper = session.getMapper(UserMapper.class);
User user = new User();
user.setId(6);
user.setUsername("caocao");
user.setPassword("hello123");
User byIdTest = userMapper.findByCondition(user);
System.out.println(byIdTest);
//5. Release resources
session.close();
in.close();
}In this example, the test method generates a User object with an id value of 6, a username of caocao, and a password of hello123.
So in usermapper In the query statement of XML, these three attribute values are used as query conditions after where to find a corresponding record in the database.
If the password in the User object in the test method is not assigned. Continue to execute the method query and find that the query result is empty.
In fact, when this query statement matches the data in the database, the password is automatically matched to null, and there must be no such record in the database.
At this time, if you want to find out the corresponding records in the database, you should use if to exclude empty fields as query criteria.
The findByCondition method in the above Dao layer interface UserMapper remains unchanged.
Mapping file usermapper Modify the select tag in XML to
<select id="findByCondition" resultType="com.zssj.domain.User">
select * from user where 1 = 1
<if test="id != 0"> and id = #{id} </if>
<if test="username != null"> and username = #{username} </if>
<if test="password != null"> and password = #{password} </if>
</select>The test method is modified to
@Test
public void test6() throws IOException {
//1. Read the core configuration file sqlmapconfig xml
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2. Create SqlSessionFactory factory
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(in);
//3. Use the factory to produce a SqlSession object
SqlSession session = factory.openSession();
//4. Use SqlSession to create the proxy object of Dao interface
UserMapper userMapper = session.getMapper(UserMapper.class);
User user = new User();
user.setId(6);
user.setUsername("caocao");
User byIdTest = userMapper.findByCondition(user);
System.out.println(byIdTest);
//5. Release resources
session.close();
in.close();
}When creating the User object in the test method, only the id and username attributes are assigned values. UserMapper.xml can find out the records in the database by adding if tag.
The if tag has a required attribute test. The attribute value of test is a judgment expression. The result of the expression can be true or false. Only when it is true, the query conditions in the if tag will be incorporated into where.
In the above example, since the password value is null and false according to the judgment of the if sub tag, and password = #{password} is excluded from the query criteria. Therefore, a record in the database can be queried according to the id value and username value.
In short, the if sub tag in where is a filter for the incoming parameters. The parameters meet the conditions I set. I put this condition after where as the query condition. If it does not meet the conditions, it will not be used.
Another point to note is why where is followed by where 1=1. This is because without this condition, when all if dynamic judgments fail to meet the condition, the last generated SQL will end with where, which does not comply with the SQL specification and will report an error.
2.2 if used in update column
Now we still use a requirement to explain the use of if. When the requirement updates a record in the database, only the changed fields are updated.
It should be noted that when updating, you cannot update the original field with value but no change to empty or null. This dynamic updating of columns can be achieved through the if tag.
The updateByIdSelective method is added to the Dao layer interface UserMapper
public int updateByIdSelective(User user);
Mapping file usermapper Add in XML
<update id="updateByIdSelective">
update user
set
<if test="username != null">username = #{username},</if>
<if test="password != null">password = #{password},</if>
id = #{id}
where id = #{id}
</update>The test method is modified to
@Test
public void test7() throws IOException {
//1. Read the core configuration file sqlmapconfig xml
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2. Create SqlSessionFactory factory
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(in);
//3. Use the factory to produce a SqlSession object
SqlSession session = factory.openSession();
//4. Use SqlSession to create the proxy object of Dao interface
UserMapper userMapper = session.getMapper(UserMapper.class);
User user = new User();
user.setId(1);
user.setUsername("liuchan");
int update = userMapper.updateByIdSelective(user);
session.commit();
System.out.println(update);
//5. Release resources
session.close();
in.close();
}In the test method, we created a User object, assigned 1 to id, liuchan to username, and no assignment to password. Therefore, this update statement should only update the username field with id value of 1.
Before update
After update
Through the comparison of database diagram, the username with id No. 1 changes from zhangsan to liuchan. The password has not changed.
Imagine that if you do not use if as a judgment in updating SQL, the password will be updated to null even though it is not assigned a new value.
Here we still need to remind you of two points. The first point is that there is a comma after the SQL statement in each if tag element. The second point is the condition of id={id} in front of the where keyword.
The following two situations can help you understand.
In the first case, if the if judgment causes all conditions to be null or empty. If there is id=#{id} this condition, the final SQL statement will become
update user set id = #{id} where id = #{id}.
Without this condition, the final SQL is as follows.
update user set where id = #{id}.
This SQL syntax error. There is no content after the set keyword. It is directly the where keyword.
In the second case, only one query condition is not null or empty, assuming username.
If there is id=#{id} this condition, the final SQL statement is as follows.
update user set username = #{username}, id = #{id} where id = #{id}
Without this condition, the final SQL is as follows
update user set username = #{username}, where id = #{id}
In this way, the where keyword is directly preceded by a comma, and the SQL statement is also wrong.
In short, the condition id=#{id} ensures that SQL syntax does not go wrong to the greatest extent. Of course, in a specific business scenario, you can select the fields that play this role according to your needs.
2.3 if used in insert dynamic insertion column
When inserting data into a database table, if the parameter value of a column is not empty, the incoming value is used. If the incoming value is empty, the default value in the database (usually empty) is used instead of the incoming value.
insert3 method is added to Dao layer interface UserMapper
int insert3(User user);
Mapping file usermapper Add in XML
<insert id="insert3" useGeneratedKeys="true" keyProperty="id">
insert into user (
id,
<if test="password != null">password</if>
username
)
values (
#{id},
<if test="password != null">#{password}</if>
#{username}
)
</insert>The test method is modified to
@Test
public void test8() throws IOException {
//1. Read the core configuration file sqlmapconfig xml
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2. Create SqlSessionFactory factory
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(in);
//3. Use the factory to produce a SqlSession object
SqlSession session = factory.openSession();
//4. Use SqlSession to create the proxy object of Dao interface
UserMapper userMapper = session.getMapper(UserMapper.class);
User user = new User();
user.setUsername("menghuo");
userMapper.insert3(user);
//Transaction submission is required for new data
session.commit();
//6. Release resources
session.close();
in.close();
}The User class object is generated in the test method. Since the id is the primary key and increases automatically, no value is set. In addition, only username is assigned a value. The value of password in the SQL statement is judged by if. If it is not empty, the passed in value will be used. If it is empty, the default value of the database will be used.
3. Dynamic SQL choose
The above describes how to use if to judge whether parameters are available in query statements, but this can only meet the basic judgment. If you want to implement if else... Such logic requires the choose when otherwise tag.
The element choose contains two Tags: when and otherwise. There is at least one when and 0 or 1 otherwise in a choose.
The following is an example to understand its usage. Assuming that in the user table, in addition to the id value being unique, username is also unique. In addition to uniquely retrieving a record through the id value, username is also possible.
The following query scenario is implemented. When the parameter id has a value, use the id value to query. When the id has no value, judge whether the user name has a value. If there is a value, use the user name to query. When it is judged that the user name has no value, return an empty record.
The Dao layer interface UserMapper adds the selectByIdOrUsername method
public User selectByIdOrUsername(User user);
Mapping file usermapper Add in XML
<select id="selectByIdOrUsername" resultType="com.zssj.domain.User">
select * from user
where 1=1
<choose>
<when test="id != 0" >and id = #{id}</when>
<when test="username != null"> and username = #{username}</when>
<otherwise>and 1=2</otherwise>
</choose>
</select>The test method is modified to
@Test
public void test9() throws IOException {
//1. Read the core configuration file sqlmapconfig xml
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2. Create SqlSessionFactory factory
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(in);
//3. Use the factory to produce a SqlSession object
SqlSession session = factory.openSession();
//4. Use SqlSession to create the proxy object of Dao interface
UserMapper userMapper = session.getMapper(UserMapper.class);
User user = new User();
user.setUsername("lisi");
User byIdTest = userMapper.selectByIdOrUsername(user);
System.out.println(byIdTest);
//5. Release resources
session.close();
in.close();
}We generated the User class object in the test method, but only assigned a value to username.
So this User object is passed into usermapper After the select tag of XML, the queried records are judged by username.
In addition, it should be noted that if there is no otherwise, when the when conditions in front of the choose tag are not met, the SQL statement will become select * from user where 1=1
Then all records in the database will be found, but our return result is a single User object, which will lead to an error.