mybatis--day01动态查询、动态更新、动态删除、动态插入

动态查询

<?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="studentNamespace">   

    <resultMap type="cn.itcast.javaee.mybatis.app11.Student" id="studentMap">
        <id property="id" column="students_id"/>
        <result property="name" column="students_name"/>
        <result property="sal" column="students_sal"/>
    </resultMap>

    //传入的是map对象，会自动取map中相同名的值
    <select id="findAll" parameterType="map" resultMap="studentMap">
        select * from students
        <where>
            <if test="pid!=null">
                and students_id = #{pid}
            </if>
            <if test="pname!=null">
                and students_name = #{pname}
            </if>
            <if test="psal!=null">
                and students_sal = #{psal}
            </if>
        </where>
    </select>

</mapper>

//测试
/**
 * 持久层 
 * @author AdminTC
 */
public class StudentDao {
    /**
     * 有条件的查询所有学生
     */
    public List<Student> findAll(Integer id,String name,Double sal) throws Exception{
        SqlSession sqlSession = null;
        try{
            sqlSession = MybatisUtil.getSqlSession();

            Map<String,Object> map = new LinkedHashMap<String,Object>();
            map.put("pid",id);
            map.put("pname",name);
            map.put("psal",sal);

            return sqlSession.selectList("studentNamespace.findAll",map);
        }catch(Exception e){
            e.printStackTrace();
            throw e;
        }finally{
            MybatisUtil.closeSqlSession();
        }
    }



    public static void main(String[] args) throws Exception{
        StudentDao dao = new StudentDao();
        List<Student> studentList = dao.findAll(5,"哈哈",7000D);
        for(Student s : studentList){
            System.out.println(s.getId()+":"+s.getName()+":"+s.getSal());
        }
    }
}

动态更新

<?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="studentNamespace">   

    <resultMap type="cn.itcast.javaee.mybatis.app12.Student" id="studentMap">
        <id property="id" column="students_id"/>
        <result property="name" column="students_name"/>
        <result property="sal" column="students_sal"/>
    </resultMap>

    <!-- set标签自动判断哪个是最后一个字段，会自动去掉最后一个,号 -->
    <update id="dynaUpdate" parameterType="map">
        update students 
        <set>
            <if test="pname!=null">
                students_name = #{pname},
            </if>
            <if test="psal!=null">
                students_sal = #{psal},         
            </if>
        </set>
        where students_id = #{pid}
    </update>
</mapper>

    /**
     * 有条件更新学生
     */
    public void dynaUpdate(Integer id,String name,Double sal) throws Exception{
        SqlSession sqlSession = null;
        try{
            sqlSession = MybatisUtil.getSqlSession();

            Map<String,Object> map = new HashMap<String, Object>();
            map.put("pid",id);
            map.put("pname",name);
            map.put("psal",sal);
            sqlSession.update("studentNamespace.dynaUpdate",map);
            sqlSession.commit();

        }catch(Exception e){
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }finally{
            MybatisUtil.closeSqlSession();
        }
    }

    public static void main(String[] args) throws Exception{
        StudentDao dao = new StudentDao();
        //关注SQL的变化
        //dao.dynaUpdate(1,null,9000D);//update students set sal=? where id=?
        //dao.dynaUpdate(1,"笨笨",null);//update students set name=? where id=?
        dao.dynaUpdate(1,"笨笨",10000D);//update students set name=? and sal=? where id=?
    }

动态删除

<?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="studentNamespace">   

    <resultMap type="cn.itcast.javaee.mybatis.app13.Student" id="studentMap">
        <id property="id" column="students_id"/>
        <result property="name" column="students_name"/>
        <result property="sal" column="students_sal"/>
    </resultMap>

    <delete id="dynaDeleteArray">
        delete from students where students_id in

        <!-- foreach用于迭代数组元素
             open表示开始符号
             close表示结束符合
             separator表示元素间的分隔符
             item表示迭代的数组，属性值可以任意，但提倡与方法的数组名相同
             #{ids}表示数组中的每个元素值
         -->
        <foreach collection="array" open="(" close=")" separator="," item="ids">
            #{ids}
        </foreach>
    </delete>   

    <delete id="dynaDeleteList">
        delete from students where students_id in
        <foreach collection="list" open="(" close=")" separator="," item="ids">
            #{ids}
        </foreach>
    </delete>

</mapper>

/**
 * 持久层 
 * @author AdminTC
 */
public class StudentDao {
    /**
     * 根据ID批量删除学生(数组版本)
     */
    public void dynaDeleteArray(int... ids) throws Exception{
        SqlSession sqlSession = null;
        try{
            sqlSession = MybatisUtil.getSqlSession();
            sqlSession.delete("studentNamespace.dynaDeleteArray",ids);
            sqlSession.commit();
        }catch(Exception e){
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }finally{
            MybatisUtil.closeSqlSession();
        }
    }
    /**
     * 根据ID批量删除学生(集合版本)
     */
    public void dynaDeleteList(List<Integer> ids) throws Exception{
        SqlSession sqlSession = null;
        try{
            sqlSession = MybatisUtil.getSqlSession();
            sqlSession.delete("studentNamespace.dynaDeleteList",ids);
            sqlSession.commit();
        }catch(Exception e){
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }finally{
            MybatisUtil.closeSqlSession();
        }
    }

    //测试
    public static void main(String[] args) throws Exception{
        StudentDao dao = new StudentDao();
        //dao.dynaDeleteArray(new int[]{1,3,5,7,77});
        //dao.dynaDeleteArray(1,3,5,7,77);
        //dao.dynaDeleteArray(2,4,444);

        List<Integer> ids = new ArrayList<Integer>();
        ids.add(6);
        ids.add(8);
        ids.add(9);
        dao.dynaDeleteList(ids);
    }
}

动态插入

<?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="studentNamespace">   

    <resultMap type="cn.itcast.javaee.mybatis.app14.Student" id="studentMap">
        <id property="id" column="students_id"/>
        <result property="name" column="students_name"/>
        <result property="sal" column="students_sal"/>
    </resultMap>


    <!-- sql片段对应字段名,id属性值任意 -->
    <sql id="key">
        <!-- 去掉最后一个, -->
        <trim suffixOverrides=",">
            <if test="id!=null">
                students_id,
            </if>
            <if test="name!=null">
                students_name,
            </if>
            <if test="sal!=null">
                students_sal,
            </if>
        </trim>
    </sql>


    <!-- sql片段对应?,id属性值任意 -->
    <sql id="value">
        <!-- 去掉最后一个, -->
        <trim suffixOverrides=",">
            <if test="id!=null">
                #{id},
            </if>
            <if test="name!=null">
                #{name},
            </if>
            <if test="sal!=null">
                #{sal},
            </if>
        </trim>
    </sql>


    <!-- <include refid="key"/>和<include refid="value"/>表示引用上面定义的sql片段 -->
    <insert id="dynaInsert" parameterType="cn.itcast.javaee.mybatis.app14.Student">
        insert into students(<include refid="key"/>) values(<include refid="value"/>)
    </insert>

</mapper>

/**
 * 持久层 
 * @author AdminTC
 */
public class StudentDao {
    /**
     * 插入学生
     */
    public void dynaInsert(Student student) throws Exception{
        SqlSession sqlSession = null;
        try{
            sqlSession = MybatisUtil.getSqlSession();
            sqlSession.insert("studentNamespace.dynaInsert",student);
            sqlSession.commit();
        }catch(Exception e){
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }finally{
            MybatisUtil.closeSqlSession();
        }
    }


    public static void main(String[] args) throws Exception{
        StudentDao dao = new StudentDao();
        //dao.dynaInsert(new Student(1,"哈哈",7000D));//insert into 表名(*,*,*) values(?,?,?)
        //dao.dynaInsert(new Student(2,"哈哈",null));//insert into 表名(*,*) values(?,?)
        //dao.dynaInsert(new Student(3,null,7000D));//insert into 表名(*,*) values(?,?)
        dao.dynaInsert(new Student(4,null,null));//insert into 表名(*) values(?)
    }
}