动态sql语句概述
Mybatis 的映射⽂件中，前⾯我们的 SQL 都是⽐较简单的，有些时候业务逻辑复杂时，我们的 SQL是动态变化的，此时在前⾯的学习中我们的 SQL 就不能满⾜要求了。
参考的官⽅⽂档，描述如下：
Dynamic SQL
One of the most powerful features of MyBatis has always been its Dynamic SQL capabilities. If you have any experience with JDBC or any similar framework, you understand how painful it is to conditionally concatenate strings of SQL together, making sure not to forget spaces or to omit a comma at the end of a list of columns. Dynamic SQL can be downright painful to deal with.
While working with Dynamic SQL will never be a party, MyBatis certainly improves the situation with a powerful Dynamic SQL language that can be used within any mapped SQL statement.
The Dynamic SQL elements should be familiar to anyone who has used JSTL or any similar XML based text processors. In previous versions of MyBatis, there were a lot of elements to know and understand. MyBatis 3 greatly improves upon this, and now there are less than half of those elements to work with. MyBatis employs powerful OGNL based expressions to eliminate most of the other elements:
· if
· choose (when, otherwise)
· trim (where, set)
· foreach
我们根据实体类的不同取值，使⽤不同的 SQL语句来进⾏查询。⽐如在 id如果不为空时可以根据id查询，如果username 不同空时还要加⼊⽤户名作为条件。这种情况在我们的多条件组合查询中经常会碰到。
接下来我们来看具体的代码实现
在IUserProxyDao接口中增加findByCondition方法
package study.lagou.com.dao;
import study.lagou.com.pojo.User;
import java.util.List;
public interface IUserProxyDao {
List<User> findAll();
List<User> findByCondition(User user);
}
在UserProxyMapper.xml配置文件中编写对应的查询方法，这里resultType和parameterType都基于别名来进行使用
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="study.lagou.com.dao.IUserProxyDao">
<!--查询方法-->
<select id="findAll" resultType="study.lagou.com.pojo.User">
SELECT * FROM user
</select>
<select id="findByCondition" resultType="user" parameterType="user">
SELECT * FROM user
<where>
<if test="id != null">
AND id = #{id}
</if>
<if test="username != null and username != ''">
AND username = #{username}
</if>
</where>
</select>
</mapper>
在MyBatisProxyTest测试类编写测试方法 testFindByCondition()，然后通过设置不同的查询参数进行查询测试
package study.lagou.com.test;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import study.lagou.com.dao.IUserProxyDao;
import study.lagou.com.pojo.User;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
* @Description: MyBatis传统开发方式测试
* @Author houjh
* @Email: happyxiaohou@gmail.com
* @Date: 2021-3-22 19:48
*/
public class MyBatisProxyTest {
@Test
public void testFindAll() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
IUserProxyDao mapper = sqlSession.getMapper(IUserProxyDao.class);
List<User> all = mapper.findAll();
for (User user : all) {
System.out.println(user);
}
}
@Test
public void testFindByCondition() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
IUserProxyDao mapper = sqlSession.getMapper(IUserProxyDao.class);
User param = new User();
param.setId(2);
param.setUsername("zhangsan");
List<User> all = mapper.findByCondition(param);
for (User user : all) {
System.out.println(user);
}
}
}
上一篇笔记地址:https://www.jianshu.com/p/f377dd1073ef
