在实际应用开发过程中,我们往往需要写复杂的SQL语句,需要拼接,而拼接SQL语句又稍微不注意,由于引号,逗号等缺失可能都会导致错误,Mybatis提供了动态SQL语句,也就是可以根据用户提供的参数,动态决定查询语句依赖的查询条件或SQL语句的内容。
定义一个工具类,减少代码的重复性
public class MybatisUtilone {
private static SqlSessionFactory factory = null;
static {
String config="mybatis-sql.xml"; // 需要和你的项目中的文件名一样
try {
InputStream in = Resources.getResourceAsStream(config);
//创建SqlSessionFactory对象,使用SqlSessionFactoryBuild
factory = new SqlSessionFactoryBuilder().build(in);
} catch (IOException e) {
e.printStackTrace();
}
}
//获取SqlSession的方法
public static SqlSession getSqlSession() {
SqlSession sqlSession = null;
if( factory != null){
sqlSession = factory.openSession();// 非自动提交事务
}
return sqlSession;
}
}
Dao层接口
If标签
List<User> selectUser(User user);
Where标签
List<User> selectUserwhere(User user);
Foreach标签
List selectfor(List idlist);
Dao映射配置
<!--if-->
<select id="selectUser" resultType="com.gx.pojo.User">
select * from user where
<if test="name !=null and name !=''">
name = #{name}
</if>
<if test="age>0">
and age > #{age}
</if>
</select>
<!--where-->
<select id="selectUserwhere" resultType="com.gx.pojo.User">
<include refid="userSql"></include>
<where>
<if test="name !=null and name !=''">
name = #{name}
</if>
<if test="age>0">
and age > #{age}
</if>
</where>
</select>
<!--foreach-->
<!--collection:表示接口中的方法参数类型,如果是数组使用array,如果是list集合使用list
item:自定义表示数组和集合的成员变量
open:循环开始的字符
close:循环结束时的字符
separator:集合成员之间分隔符
-->
<select id="selectfor" resultType="com.gx.pojo.User">
<include refid="userSql"></include> where id in
<foreach collection="list" item="myid" open="(" close=")" separator=",">
#{myid}
</foreach>
</select>
测试类
private static SqlSession sqlSession = MybatisUtilone.getSqlSession();
private static UserDaoone userDaoone = sqlSession.getMapper(UserDaoone.class);
@Test
public void RunslectUser(){
User user = new User();
user.setName("张三");
user.setAge(19);
List<User> userList = userDaoone.selectUser(user);
for (User su:userList) {
System.out.println("if=====" + su);
}
sqlSession.close();
}
@Test
public void RunselectUserwhere(){
User user = new User();
user.setAge(23);
List<User> userList = userDaoone.selectUserwhere(user);
for (User su:userList) {
System.out.println("if=====" + su);
}
sqlSession.close();
}
@Test
public void runselectforone(){
List<Integer> list = new ArrayList<>();
list.add(1);
list.add(2);
list.add(3);
List<User> userList = userDaoone.selectforone(list);
for (User su:userList) {
System.out.println("if=====" + su);
}
sqlSession.close();
}
Foreach测试类结果