七、MyBatis的CRUD操作

七、MyBatis的CRUD操作【重点】

---

7.1 查询

标签：< select id="" resultType="" >

7.1.1 序号参数绑定

public interface UserDao {
        //使用原生参数绑定
    public User selectUserByIdAndPwd(Integer id , String pwd);
}

<select id="selectUserByIdAndPwd" resultType="user">
    SELECT * FROM t_users
    WHERE id = #{arg0} AND password = #{arg1} <!--arg0 arg1 arg2 ...-->
</select>
​
<select id="selectUserByIdAndPwd" resultType="user">
    SELECT * FROM t_users
    WHERE id = #{param1} AND password = #{param2} <!--param1 param2 param3 ...-->
</select>

7.1.2 注解参数绑定【推荐】

import org.apache.ibatis.annotations.Param; //引入注解
​
public interface UserDao {
    //使用MyBatis提供的@Param进行参数绑定
    public User selectUserByIdAndPwd(@Param("id") Integer id , @Param("pwd") String pwd);
}

<select id="selectUserByIdAndPwd" resultType="user">
    SELECT * FROM t_users
    WHERE id = #{id} AND password = #{pwd} <!-- 使用注解值 @Param("pwd") -->
</select>

7.1.3 Map参数绑定

public interface UserDao {
    //添加Map进行参数绑定
        public User selectUserByIdAndPwd_map(Map values);
}

Map values = new HashMap(); //测试类创建Map
values.put("myId",1); //自定义key，绑定参数
values.put("myPwd","123456");
User user = userDao.selectUserByIdAndPwd_map(values);

<select id="selectUserByIdAndPwd_map" resultType="user">
    SELECT * FROM t_users 
    WHERE id = #{myId} AND password = #{myPwd} <!-- 通过key获得value -->
</select>

7.1.4 对象参数绑定

public interface UserDao {
    //使用对象属性进行参数绑定
    public User selectUserByUserInfo(User user);
}

<select id="selectUserByUserInfo" resultType="user">
    SELECT * FROM t_users
    WHERE id = #{id} AND password = #{password} <!-- #{id}取User对象的id属性值、#{password}同理 -->
</select>

7.1.5 模糊查询

public interface UserDao {
    public List<User> selectUsersByKeyword(@Param("keyword") String keyword);
}

<mapper namespace="com.qf.mybatis.part1.different.UserDao">
    <select id="selectUsersByKeyword" resultType="user">
        SELECT * FROM t_users 
        WHERE name LIKE concat('%',#{keyword},'%') <!-- 拼接'%' -->
    </select>
</mapper>

7.2 删除

标签：< delete id="" parameterType="" >

 <delete id="deleteUser" parameterType="int">
    DELETE FROM t_users
    WHERE id = #{id} <!--只有一个参数时，#{任意书写}-->
</delete>

注意：进行增删改操作后，需要提交及关闭session对象

7.3 修改

标签：< update id="" parameterType="" >

<update id="updateUser" parameterType="user">
    UPDATE t_users SET name=#{name}, password=#{password}, sex=#{sex}, birthday=#{birthday}
    WHERE id = #{id} <!--方法参数为对象时，可直接使用#{属性名}进行获取-->
</update>

7.4 添加

标签：< insert id="" parameterType="" >

<insert id="insertUser" parameterType="user">
    <!-- 自动增长主键，以下两种方案均可 -->
    INSERT INTO t_users(id,name,password,sex) VALUES(#{id},#{name},#{password},#{sex})
</insert>

7.5 主键回填

标签：< selectKey id="" parameterType="" order="AFTER|BEFORE">

7.5.1 通过last_insert_id()查询主键

create table t_product(
  id int primary key auto_increment,
  name varchar(50)
)default charset = utf8;

class Product{
    private Integer id;
    private String name;
    //set+get ...
}

<mapper namespace="com.qf.mybatis.part1.basic.ProductDao">
    <insert id="insertProduct" parameterType="product">
        <selectKey keyProperty="id" resultType="int" order="AFTER"> <!-- 插入之后 -->
            SELECT LAST_INSERT_ID() <!-- 适用于整数类型自增主键 -->
        </selectKey>
​
        INSERT INTO t_product(id,name) VALUES(#{id},#{name})
    </insert>
</mapper>

7.5.2 通过uuid()查询主键

create table t_order(
  id varchar(60) primary key, # 字符型主键
  name varchar(50)
)default charset = utf8;

class Order{
    private String id;
    private String name;
    //set+get ...
}

<mapper namespace="com.qf.mybatis.part1.basic.OrderDao">
    <insert id="insertOrder" parameterType="order">
        <selectKey keyProperty="id" resultType="string" order="BEFORE"><!-- 插入之前 -->
            SELECT UUID()   <!-- 适用于字符类型主键 -->
        </selectKey>
​
        INSERT INTO t_order(id,name) VALUES(#{id},#{name})
    </insert>
</mapper>