动态sql(一)
传递pojo综合查询用户信息
1、数据库数据
2、JavaBean
package Domain;
import java.io.Serializable;
public class UserDomain implements Serializable {
private int id;
private String name;
private String password;
private String sex;
private int age;
//无参构造函数
public UserDomain() {
}
//get|set
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
//toString
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", password=" + password + ", sex=" + sex + ", age=" + age + "]";
}
}
3、接口
package Mapper;
import java.util.List;
import Domain.QueryUser;
import Domain.UserDomain;
public interface UserMapper {
//动态sql(一):查询 ID or NAME or PASSWORD or SEX or age
List<UserDomain> findUserByAll(UserDomain user);
}
4、映射文件中的sql语句
这里有两种方法:
- 方法一中的where 1=1 是为了避免where 关键字后面的第一个词直接就是 “and”而导致语法错误。
- 方法二直接使用where标签
<?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">
<!-- namespace和mapper接口同名 -->
<mapper namespace="Mapper.UserMapper">
<!-- 动态sql(一)(查询 ID or NAME or PASSWORD or SEX or age) -->
<!-- 方法一 -->
<!-- <select id="findUserByAll" parameterType="UserDomain" resultType="UserDomain">
select * from test001
where 1=1
<if test="id!=0">
and id=#{id}
</if>
<if test="name!=null and name!=''">
and name like '%${name}%'
</if>
<if test="password!=null and password!=''">
and password=#{password}
</if>
<if test="sex!=null and sex!=''">
and sex=#{sex}
</if>
<if test="age!=0">
and age=#{age}
</if>
</select> -->
<!-- 方法二 -->
<select id="findUserByAll" parameterType="UserDomain" resultType="UserDomain">
select * from test001
<where>
<if test="id!=0">
and id=#{id}
</if>
<if test="name!=null and name!=''">
and name like '%${name}%'
</if>
<if test="password!=null and password!=''">
and password=#{password}
</if>
<if test="sex!=null and sex!=''">
and sex=#{sex}
</if>
<if test="age!=0">
and age=#{age}
</if>
</where>
</select>
</mapper>
5、测试
package test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
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.Before;
import org.junit.Test;
import Domain.QueryUser;
import Domain.UserDomain;
import Mapper.UserMapper;
public class Test01 {
private SqlSessionFactory ssf;
//获得会话工厂
@Before
public void creatSqlSessionFactory() {
InputStream in = null;
try {
in = Resources.getResourceAsStream("SqlMapConfig.xml");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
ssf = new SqlSessionFactoryBuilder().build(in);
}
@Test
public void findUserByAll() {
//打开一个会话
SqlSession s = ssf.openSession();
//获得代理对象
UserMapper mapper = s.getMapper(UserMapper.class);
//new对象
UserDomain user = new UserDomain();
user.setName("老");
user.setSex("男");
//调用代理对象
List<UserDomain> list = mapper.findUserByAll(user);
for (UserDomain x : list) {
System.out.println(x);
}
//关闭
s.close();
}
}
动态sql(二)
先看下面sql语句
SELECT * FROM test001 WHERE NAME LIKE '%老%' AND id IN (4,16,17)
如果需要你在映射文件里面写,你会发现写不下去了,那么接下来就是解决这个问题
1、数据库数据、JavaBean同上,只是多了一个QueryUser类(封装查询条件)
package Domain;
import java.util.List;
public class QueryUser extends UserDomain {
private List<Integer> ids;
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
}
2、接口里面写方法
package Mapper;
import java.util.List;
import Domain.QueryUser;
import Domain.UserDomain;
public interface UserMapper {
//动态sql(二):查询id号 and 模糊查询姓
List<QueryUser> findUserByIdAndName(QueryUser qu);
}
3、映射文件写sql语句
这里会使用到foreach,向sql传递数组或List,mybatis使就会使用foreach解析
(1)方法一
<?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">
<!-- namespace和mapper接口同名 -->
<mapper namespace="Mapper.UserMapper">
<!-- 动态sql(二):查询id号 and 模糊查询姓 -->
<select id="findUserByIdAndName" parameterType="QueryUser" resultType="QueryUser">
select * from test001
<where>
<if test="name!=null and name!=''">
and name like "%${name}%"
</if>
<if test="ids!=null">
<foreach collection="ids" item="id" open="and id in (" close=")" separator=",">
#{id}
</foreach>
</if>
</where>
</select>
</mapper>
(2)方法二
Sql中可将重复的sql提取出来,使用时用include引用即可,最终达到sql重用的目的
<?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">
<!-- namespace和mapper接口同名 -->
<mapper namespace="Mapper.UserMapper">
<!-- 动态sql(二):查询id号 and 模糊查询姓 -->
<!-- 截取下来的代码片段 -->
<sql id="pd">
<if test="name!=null and name!=''">
and name like "%${name}%"
</if>
<if test="ids!=null">
<foreach collection="ids" item="id" open="and id in (" close=")" separator=",">
#{id}
</foreach>
</if>
</sql>
<!-- sql语句 -->
<select id="findUserByIdAndName" parameterType="QueryUser" resultType="QueryUser">
select * from test001
<where>
<include refid="pd"></include>
</where>
</select>
</mapper>
注意:如果引用其它映射文件的sql片段,则在引用时需要加上namespace,如下:
<include refid="namespace.sql片段”/>
4、测试
package test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
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.Before;
import org.junit.Test;
import Domain.QueryUser;
import Domain.UserDomain;
import Mapper.UserMapper;
public class Test01 {
private SqlSessionFactory ssf;
//获得会话工厂
@Before
public void creatSqlSessionFactory() {
InputStream in = null;
try {
in = Resources.getResourceAsStream("SqlMapConfig.xml");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
ssf = new SqlSessionFactoryBuilder().build(in);
}
@Test
public void findUserByIdAndName() {
//打开一个会话
SqlSession s = ssf.openSession();
//获得代理对象
UserMapper mapper = s.getMapper(UserMapper.class);
//new对象
QueryUser user = new QueryUser();
user.setName("老");
ArrayList<Integer> arrayList = new ArrayList<Integer>();
Collections.addAll(arrayList, 16,17,18);
user.setIds(arrayList);
//调用代理对象
List<QueryUser> list = mapper.findUserByIdAndName(user);
for (QueryUser x : list) {
System.out.println("数据:"+x.getId()+"==="+x.getName());
}
//关闭
s.close();
}
}