MyBatis增删改查

1.select

传参数方式

(#传参数据字符串类型，默认加引号，$不加引号传入的是原值)

占位符传值:#{0}，#{1}

用@Param注解传值：Dao方法里的参数前面加上@Param（"provinceId")，即@Param("provinceId") Integer provinceId，那么在mapper中的sql语句里，就可以用#{provinceId}来获取参数值了。

JavaBean:只要设置parameterType="类名"即可

<select id="findA" parametertype="com.learn.params.RoleParam" resultMap="roleMap">  
    SELECT  
    id,role_name,note FROM t_role  
    WHERE role_name like concat  
    ('%',#{roleName},'%')  
    and note like concat('%',#{note},'%')  
</select>  

public class RoleParam {  
    private String roleName;  
    private String note;  
    public String getRoleName() {  
        return roleName;  
    }    
    public String getNote() {  
        return note;  
    }    
    public void setRoleName(String roleName) {  
        this.roleName = roleName;  
    }    
    public void setNote(String note) {  
        this.note = note;  
    }   
}  

 

取返回值

resultType是直接表示返回类型的，而resultMap则是映射实体类的数据类型，但是resultType跟resultMap不能同时存在。

public class Person {
    private Integer personId;
    private String name;
    private Integer gender;
    private String personAddr;
    private Date birthday;
    @Override
    public String toString() {
        return "Person [personId=" + personId + ", name=" + name + ", gender="
                + gender + ", personAddr=" + personAddr + ", birthday="
                + birthday + "]";
    }
}

<?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 namespace="com.hao947.sql.mapper.PersonMapper">
    <!-- type:映射实体类的数据类型 id：resultMap的唯一标识 -->
    <resultMap type="person" id="BaseResultMap">
        <!-- column:库表的字段名 property:实体类里的属性名 -->
        <id column="person_id" property="personId" />
        <result column="name" property="name" />
        <result column="gender" property="gender" />
        <result column="person_addr" property="personAddr" />
        <result column="birthday" property="birthday" />
    </resultMap>
    <!--id:当前sql的唯一标识
         parameterType：输入参数的数据类型 
         resultType：返回值的数据类型 
         #{}:用来接受参数的，如果是传递一个参数#{id}内容任意，如果是多个参数就有一定的规则,采用的是预编译的形式select 
        * from person p where p.id = ? ，安全性很高 -->

    <!-- sql语句返回值类型使用resultMap -->
    <select id="selectPersonById" parameterType="java.lang.Integer"
        resultMap="BaseResultMap">
        select * from person p where p.person_id = #{id}
    </select>
    <!-- resultMap:适合使用返回值是自定义实体类的情况 
    resultType：适合使用返回值的数据类型是非自定义的，即jdk的提供的类型 -->
    <select id="selectPersonCount" resultType="java.lang.Integer">
        select count(*) from
        person
    </select>

    <select id="selectPersonByIdWithMap" parameterType="java.lang.Integer"
        resultType="java.util.Map">
        select * from person p where p.person_id= #{id}
        </select>

</mapper>

 

2.insert

<insert id="insertStudentAutoKey" parameterType="java.util.List">
    INSERT INTO STUDENT_TBL (STUDENT_NAME,  
                                 STUDENT_SEX,  
                                 STUDENT_BIRTHDAY,  
                                 CLASS_ID)  
                              VALUES   
  <foreach collection="list" item="item" index="index" separator=",">
      ( #{item.studentName},#{item.studentSex},#{item.studentBirthday},#{item.classEntity.classID})
  </foreach>
  </insert>

 

3.update

<update id="updateBatch"  parameterType="java.util.List">  
    <foreach collection="list" item="item" index="index" open="" close="" separator=";">
        update course
        <set>
            name=${item.name}
        </set>
        where id = ${item.id}
    </foreach>      
</update>

 

4.delete

<!-- 通过主键集合批量删除记录 -->

<delete id="batchRemoveUserByPks" parameterType="java.util.List">

DELETE FROM LD_USER WHERE ID in 

<foreach item="item" index="index" collection="list" open="(" separator="," close=")">

#{item}

</foreach>

</delete>