Mybatis 多条件查询(模糊查询 使用in查询)
转载自:
http://blog.csdn.net/liangwenmail/article/details/50346219Mybatis支持多个条件的查询,使用if标签拼接。
下面是三个条件的查询得例子,使用的数据库是mysql,用到了模糊查询及in做条件查询。
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=“”>
- <select id=“selectTeacher” parameterType=“map” resultType=“map”>
- select t.tid,t.tname,t.taddr
- from test_teacher t
- where 1=1
- <if test=“tid != ” and tid != null”>
- and tid=#{tid}
- </if>
- <if test=“tname != ” and tname != null”>
- and tname like #{tname}
- </if>
- <if test=“addrs != ” and addrs != null”>
- and taddr in
- <foreach item=“item” index=“index” collection=“addrs” open=“(“
- separator=“,” close=“)”>
- #{item}
- </foreach>
- </if>
- </select>
- </mapper>
<?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="">
<select id="selectTeacher" parameterType="map" resultType="map">
select t.tid,t.tname,t.taddr
from test_teacher t
where 1=1
<if test="tid != '' and tid != null">
and tid=#{tid}
</if>
<if test="tname != '' and tname != null">
and tname like #{tname}
</if>
<if test="addrs != '' and addrs != null">
and taddr in
<foreach item="item" index="index" collection="addrs" open="("
separator="," close=")">
#{item}
</foreach>
</if>
</select>
</mapper>
dao层:
- package mybatis.dao;
- import java.util.List;
- public interface TestTeacherDao {
- public List selectTeacher(String tid,String tname,List addrs);
- }
package mybatis.dao;
import java.util.List;
public interface TestTeacherDao {
public List selectTeacher(String tid,String tname,List addrs);
}
- package mybatis.dao;
- import java.io.InputStream;
- import java.util.ArrayList;
- import java.util.HashMap;
- import java.util.List;
- import java.util.Map;
- import org.apache.ibatis.session.SqlSession;
- import org.apache.ibatis.session.SqlSessionFactory;
- import org.apache.ibatis.session.SqlSessionFactoryBuilder;
- public class TestTeacherDaoImpl implements TestTeacherDao {
- private int res=-1;
- private SqlSession sqlSession=null;
- public SqlSession getSqlSession(){
- try{
- String resource=”mybatis-config.xml”;
- InputStream is=org.apache.ibatis.io.Resources.getResourceAsStream(resource);
- SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
- sqlSession=sqlSessionFactory.openSession(true);
- }catch(Exception e){
- System.out.println(”出错”);
- e.printStackTrace();
- }
- return sqlSession;
- }
- @Override
- public List selectTeacher(String tid,String tname,List addrs) {
- Map map=new HashMap();
- map.put(”tid”, tid);
- map.put(”tname”, “%”+tname+“%”);
- map.put(”addrs”,addrs);
- sqlSession=getSqlSession();
- List list=sqlSession.selectList(”selectTeacher”, map);
- return list;
- }
- }
package mybatis.dao;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;public class TestTeacherDaoImpl implements TestTeacherDao {
private int res=-1;
private SqlSession sqlSession=null;
public SqlSession getSqlSession(){
try{ String resource="mybatis-config.xml"; InputStream is=org.apache.ibatis.io.Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is); sqlSession=sqlSessionFactory.openSession(true); }catch(Exception e){ System.out.println("出错"); e.printStackTrace(); } return sqlSession; } @Override public List selectTeacher(String tid,String tname,List addrs) { Map map=new HashMap(); map.put("tid", tid); map.put("tname", "%"+tname+"%"); map.put("addrs",addrs); sqlSession=getSqlSession(); List list=sqlSession.selectList("selectTeacher", map); return list; }
}
测试:
- package mybatis.test;
- import java.util.ArrayList;
- import java.util.List;
- import java.util.Map;
- import mybatis.dao.TestTeacherDao;
- import mybatis.dao.TestTeacherDaoImpl;
- public class Test {
- public static void main(String[] args) {
- TestTeacherDao td=new TestTeacherDaoImpl();
- List addrs=new ArrayList();
- addrs.add(”shandong”);
- addrs.add(”beijing”);
- List list=td.selectTeacher(”“, “”,addrs);
- for(int i=0;i<list.size();i++){
- Map map=(Map)(list.get(i));
- System.out.println(map.get(”tname”));
- }
- }
- }
package mybatis.test;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;import mybatis.dao.TestTeacherDao;
import mybatis.dao.TestTeacherDaoImpl;public class Test {
public static void main(String[] args) { TestTeacherDao td=new TestTeacherDaoImpl(); List addrs=new ArrayList(); addrs.add("shandong"); addrs.add("beijing"); List list=td.selectTeacher("", "",addrs); for(int i=0;i<list.size();i++){ Map map=(Map)(list.get(i)); System.out.println(map.get("tname")); } }
}