Mybatis学习笔记(二) 增删改查

引用

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

查询

public interface UserMapper {
    public List<User> getUserList();
}

resultType:

1. 基本类型 ：resultType=基本类型
2. List类型： resultType=List中元素的类型
3. Map类型 resultType =map

<mapper namespace="com.com.hjc.mapper.UserMapper">
    <select id="getUserList" resultType="com.hjc.POJO.User">
    select * from mybatis.user
  </select>
</mapper>

增加

public interface UserMapper {
    public void inserUser(User user);
}

 

<mapper namespace="com.com.hjc.mapper.UserMapper">
    <insert id="insertUser" parameterType="com.hjc.POJO.User">
    insert into mybatis.user(id,name,pwd) values(#{id},#{name},#{pwd});
  </insert >
</mapper>

删除

public interface UserMapper {
    public void deleteUser(User user);
}

 

<mapper namespace="com.com.hjc.mapper.UserMapper">
    <delete id="deleteUser" parameterType="com.hjc.POJO.User">
    delete from mybatis.user where id = #{id};
  </delete >
</mapper>

修改

public interface UserMapper {
    public void updateUser(User user);
}

 

<mapper namespace="com.com.hjc.mapper.UserMapper">
    <update id="updateUser" parameterType="com.hjc.POJO.User">
    update  mybatis.user set name = #{name},pwd=#{pwd} where id = #{id};
  </update >
</mapper>

分页

    public List<User> getUserByLimit(@Param("start") int start, @Param("number") int number);

 

  <select id="getUserByLimit" resultType="user">
    select * from mybatis.user limit ${start},${number};
  </select>

多对一

多对一和一对多记得不能互相拥有对方的对象，否则就会死循环了

@Data
public class Phone {
    private String id;
    private String name;
    private int price;
    private User user;
}

 

<resultMap id="blogResult" type="Blog">
  <id property="id" column="blog_id" />
  <result property="title" column="blog_title"/>
  <association property="author" javaType="Author">
    <id property="id" column="author_id"/>
    <result property="username" column="author_username"/>
    <result property="password" column="author_password"/>
    <result property="email" column="author_email"/>
    <result property="bio" column="author_bio"/>
  </association>
</resultMap>
    <select id="getPhones" resultMap="phoneMap">
    select p.id as pid,p.name pname,p.price,u.id as uid ,u.name tname,u.pwd
    from phone p left outer join user u on p.userid = u.id;
  </select>

一对多

@Data
public class User {
    private int id;
    private String name;
    private String pwd;
    private List<Phone> phones;
}  

 

  <resultMap id="userMap" type="user">
    <id property="id" column="uid"/>
    <result property="name" column="tname"/>
    <result property="pwd" column="pwd"/>
    <collection property="phones" ofType="phone" resultMap="phoneMap"/>

  </resultMap>
  <resultMap id="phoneMap" type="phone">
    <id property="id" column="pid"/>
    <result property="name" column="pname"/>
    <result property="price" column="price"/>
  </resultMap>
    <select id="getUserList" resultMap="userMap">
    select p.id as pid,p.name pname,p.price,u.id as uid ,u.name tname,u.pwd
from user u left outer join phone p on  p.userid = u.id;
  </select>

动态SQL

本质上就是在拼接SQL，在SQL层面执行逻辑代码

IF判断

<select id="findActiveBlogWithTitleLike"
     resultType="Blog">
  SELECT * FROM BLOG
  WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
</select>

IFELSE判断

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

拼接AND去除,有可能第一个if不执行，那么拼接第二个if就需要去除AND

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG
  <where>
    <if test="state != null">
         state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

如果最后一个不执行，就会多一个逗号

<update id="updateAuthorIfNecessary">
  update Author
    <set>
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </set>
  where id=#{id}
</update>

foreach

列表遍历

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>
</select>