动态SQL
- Dynamic SQL, 动态SQL 不是固定写死的SQL,可以根据输入条件变化,可以参考 MyBatis 的官方文档。
<if> 相当于 if (…){…}
<choose><when><otherwise> 相当于 if…else if…else
- 本例按照学号区间,查询student表
1.指定起止,如 begin=20190003 , end=20190006
2.只有开始,如 begin=20190003
3.只有结束,如 end=20190006
4.即没有开始,也没有结束
这种根据条件而变化的SQL,可以使用动态SQL。
1.修改mybatis-mapper.xml
<?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="af.test">
<select id="getStudent" resultType="my.Student">
select * from student
<where>
<if test="begin != null">
id >= #{begin}
</if>
<if test="end != null">
AND id <= #{end}
</if>
</where>
</select>
</mapper>
使用 <where> 标签,里面嵌多个<if>,这么写的时候,MyBatis会自己处理,当第一个<if>为空时,后面紧跟着的 AND 会被 MyBatis忽略。也可以这样写
select * from student where 1=1
<if …>
AND id >= #{begin}
</if>
<if test="end != null">
AND id <= #{end}
</if>
2.在mybatis-config.xml添加此mapper
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://127.0.0.1/af_example?useUnicode=true&characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="a1b2c3"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mybatis-mapper.xml"/>
</mappers>
</configuration>
3.注释参数进行测试
package my;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class Test
{
public static void main(String[] args) throws Exception
{
// 创建 SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
try (SqlSession session = sqlSessionFactory.openSession()) {
HashMap<String,Object> map = new HashMap<>();
//map.put("begin", 20190003);
//map.put("end", 20190006);
List<Student> rows = session.selectList("af.test.getStudent", map);
for(Student s : rows )
{
System.out.println(s.id + "\t," + s.name);
}
}
System.out.println("Exit");
}
}
4.结果略去啦,构造的SQL可以在控制台看到。