MyBatis的增删改查操作

搭建好mybatis之后 进行对数据库的操作

添加语句
在映射文件中添加语句

<mapper namespace="com.wn.dao.StudentDao">
     <insert id="insertStudent" parameterType="StudentBean">
        insert into student(name,age,score) values(#{name},#{age},#{score})
    </insert>
映射文件要放在与接口一个目录下
namespace：必须是对应接口的全限定名 
id ：dao中的方法名字
parameterType：传入的参数类型 可以省略

添加语句后 获取主键的值 赋值给主键

<insert id="insertStudentGetId" parameterType="StudentBean">
         insert into student(name,age,score) values(#{name},#{age},#{score})
         <!-- keyProperty:查询结果赋值给studentbean的id属性
             resultType：返回结果的数据类型
             order：执行顺序  mysql在数据添加后
          -->
         <selectKey keyProperty="id" resultType="int" order="AFTER">
         select  @@identity
         </selectKey>
     </insert>
2.删除语句

 <update id="updateStudent" >
         update student set name=#{name} ,age=#{age},score=#{score} 
         where id = #{id}
     </update>
3.查询语句

/**
     * 静态参数
     * @param student
     */
    void insertStudent(StudentBean student);
    List<StudentBean> selectStudentAll();
    //根据姓名模糊查询
    List<StudentBean> selectStudentByName(String name);
    
    //多参数查询  使用map作为方法参数
    List<StudentBean> selectStuByMap1(Map<String, Object> map);
    List<StudentBean> selectStuByMap2(Map<String, Object> map);
    
    List<StudentBean> selectStuByParameters1(String name , int age);
    List<StudentBean> selectStuByParameters2(String name , StudentBean student);
    List<StudentBean> selectStuByParameters3(@Param("name") String name , @Param("age")int age);
    List<StudentBean> selectStuByParameters4(@Param("name") String name , @Param("student") StudentBean student);

<select id="selectStudentAll" resultType="StudentBean">
    select * from student 
</select>
<select id="selectStudentByName" resultType="StudentBean">
    select * from student where name like  '%' #{name} '%'
</select>

<select id="selectStuByMap1" resultType="StudentBean">
    <!-- 查询的参数是Map   #{map的key}  使用的数据是key对应的value-->
    select * from student where name like  '%' #{name} '%' and age>#{age}
</select>

<select id="selectStuByMap2" resultType="StudentBean">
    <!-- 查询的参数是Map   #{map的key}  使用的数据是key对应的value
            map的key是student，value是student对象-->
    select * from student where name like  '%' #{name} '%' and age > #{student.age}
</select>

<select id="selectStuByParameters1" resultType="StudentBean">
    select * from student where name like  '%' #{0} '%' and age > #{1}
</select>

<select id="selectStuByParameters2"  resultType="StudentBean">
    select * from student where name like '%' #{0} '%' and age > #{1.age}
</select>
动态参数

/**
 * 动态参数
 */
//mybatis 动态参数类似域jstl《c:》

//if拼接  sql语句要跟上 where 1 =1 
List<StudentBean> selectStudentByIf(StudentBean student);

<select id="selectStudentByIf"  resultType="StudentBean">
    select * from student where 1=1
    <if test="name!=null and name!=''">
        and name like '%' #{name} '%'
    </if>
    <if test="age>0">
        and age > #{age}
    </if>
</select>

//不生成 1= 1  提高效率   自动在sql语句拼接的时候加上where  关键字
List<StudentBean> selectStudentByWhere(StudentBean student);

<select id="selectStudentByWhere"  resultType="StudentBean">
    select * from student
    <where>
        <if test="name!=null and name!=''">
        and name like '%' #{name} '%'
        </if>
        <if test="age>0">
        and age > #{age}
        </if>
    </where>
</select>

//多选一
List<StudentBean> selectStudentByChoose(StudentBean student);

<select id="selectStudentByChoose" resultType="StudentBean">
    select * from student 
    <choose>
        <when test="name!=null and name!=''">
            where name like '%' #{name} '%'
        </when>
        <when test="age>0">
            where age > #{age}
        </when>
        <otherwise>
            where 1 = 2
        </otherwise>        
    </choose> 
</select>

List<StudentBean> selectStudentByForeachArray(int[] ids);

    <select id="selectStudentByForeachArray" resultType="StudentBean">
    select * from student 
    <if test="array!=null and array.length>0">
        where id in
        <foreach collection="array" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>        
    </if>
    </select>

List<StudentBean> selectStudentByForeachList(List<Integer> ids);

<select id="selectStudentByForeachList" resultType="StudentBean">
    select * from student 
    <if test="list!=null and list.size>0">
        where id in
        <foreach collection="list" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>        
    </if>
</select>

List<StudentBean> selectStudentByForeachStudent(List<StudentBean> students);

<select id="selectStudentByForeachStudent" resultType="StudentBean">
    select * from student 
    <if test="list!=null and list.size>0">
        where id in
        <foreach collection="list" item="student" open="(" close=")" separator=",">
            #{student.id}
        </foreach>        
    </if>
</select>

List<StudentBean> selectStudentBySqlFragement();

<select id="selectStudentBySqlFragement" resultType="StudentBean">
    select <include refid="fragement" /> student     
</select>
<sql id="fragement"> * from</sql>

//统计一张表的总数据条数  分页的总条数
int selectStudentCount();

    <select id="selectStudentCount" resultType="int">
    select count(*) from student 
    </select>

<!-- 动态参数 -->
--------------------- 
作者：The nice day_150 
来源：CSDN 
原文：https://blog.csdn.net/Theniceday_99/article/details/91611105 
版权声明：本文为博主原创文章，转载请附上博文链接！