mybatis之动态SQL操作之查询

1)  查询条件不确定,需要根据情况产生SQL语法,这种情况叫动态SQL

/**
 * 持久层
 * @author AdminTC
 */
public class StudentDao {
    /**
     * 动态SQL--查询
     */
    public List<Student> dynaSQLwithSelect(String name,Double sal) throws Exception{
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        try{
            Map<String,Object> map = new LinkedHashMap<String, Object>();
            map.put("pname",name);
            map.put("psal",sal);
            return sqlSession.selectList("mynamespace.dynaSQLwithSelect",map);
        }catch(Exception e){
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }finally{
            sqlSession.commit();
            MyBatisUtil.closeSqlSession();
        }
    }
    public static void main(String[] args) throws Exception{
        StudentDao dao = new StudentDao();
        
        List<Student> studentList1 = dao.dynaSQLwithSelect("哈哈",null);
        for(Student student : studentList1){
            System.out.println(student.getId()+":"+student.getName()+":"+student.getSal());
        }
        System.out.println("--------------");
        List<Student> studentList2 = dao.dynaSQLwithSelect(null,7000D);
        for(Student student : studentList2){
            System.out.println(student.getId()+":"+student.getName()+":"+student.getSal());
        }
        System.out.println("--------------");
        List<Student> studentList3 = dao.dynaSQLwithSelect("哈哈",7000D);
        for(Student student : studentList3){
            System.out.println(student.getId()+":"+student.getName()+":"+student.getSal());
        }
        System.out.println("--------------");
        List<Student> studentList4 = dao.dynaSQLwithSelect(null,null);
        for(Student student : studentList4){
            System.out.println(student.getId()+":"+student.getName()+":"+student.getSal());
        }
        System.out.println("--------------");
    }
}

    StudentMapper.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="mynamespace">
    <select id="dynaSQLwithSelect" parameterType="map" resultType="loaderman.Student">
        select id,name,sal from students
        <where>
            <if test="pname!=null">
                and name=#{pname}
            </if>
            <if test="psal!=null">
                and sal=#{psal}
            </if>
        </where>     
    </select>
</mapper>

 

posted on 2018-12-04 15:36  LoaderMan  阅读(462)  评论(0编辑  收藏  举报

导航