mybatis常见问题

1.MyBatis高级映射(一对一、一对多、多对多、延迟加载)

原文链接:https://blog.csdn.net/WangFengFans/article/details/80250159

2.MyBatis两个表 字段名称相同 数据被覆盖

原文链接:https://www.iteye.com/blog/java-007-2304720

方法一:column别名

<resultMap type="Desk" id="resultUserMap">  
        <result property="DESK_ID" column="DESK_ID" />  
        <result property="NAME" column="NAME" />  
        <result property="REMARK" column="REMARK" />  
        <collection property="books" ofType="Book" column="DESK_ID">  
            <id property="BOOK_ID" column="BOOK_ID" />      
            <result property="NAME" column="bNAME"/>  
            <result property="REMARK" column="bREMARK"/>     
            <result property="PRICE" column="PRICE"/>   
        </collection>  
    </resultMap>    
  
    <select id="getBooksByDesk" resultMap="resultUserMap" parameterType="String">  
        SELECT d.DESK_ID,d.NAME,d.REMARK,b.BOOK_ID,b.NAME as bNAME,
        b.REMARK as bREMARK,b.PRICE FROM tb_desk d,tb_book b WHERE 
        b.desk_id=d.desk_id AND d.desk_id=#{desk_id}   
    </select> 

方法二:columnPrefix别名

<resultMap type="Desk" id="resultUserMap" >  
        <result property="DESK_ID" column="DESK_ID" />  
        <result property="NAME" column="NAME" />  
        <result property="REMARK" column="REMARK" />  
        <collection property="books" ofType="Book" column="DESK_ID"  columnPrefix="ks_" >  
            <id property="BOOK_ID" column="BOOK_ID" />      
            <result property="NAME" column="NAME"/>  
            <result property="REMARK" column="REMARK"/>     
            <result property="PRICE" column="PRICE"/>   
        </collection>  
    </resultMap>    
  
    <select id="getBooksByDesk" resultMap="resultUserMap" parameterType="String">  
        SELECT d.DESK_ID,d.NAME,d.REMARK,b.BOOK_ID,b.NAME as ks_NAME,
        b.REMARK as ks_REMARK,b.PRICE FROM tb_desk d,tb_book b WHERE 
        b.desk_id=d.desk_id AND d.desk_id=#{desk_id}   
    </select> 

3.MyBatis常用查询

多条件选择查询:

 List<User> selectByCondition(@Param("params") Map<String, Object> params);

 <select id="selectByCondition" resultMap="BaseResultMap" parameterType="java.util.Map">
        select
        <include refid="Base_Column_List"/>,
        from user 
        <where>
            <if test="params!=null and params.name!=null">
                and name = #{params.name,jdbcType=VARCHAR}
            </if>
            <if test="params!=null and params.position!=null">
                and position = #{params.position,jdbcType=VARCHAR}
            </if>
            <if test="params!=null and params.employeeNoList!=null">
                and employee_no in
                <foreach collection="params.employeeNoList" item="employeeNo" open="" open="(" separator="," close=")">
                    #{employeeNo,jdbcType=VARCHAR}
                </foreach>
            </if>
 	       <if test="params!=null and params.startTime!=null">
                and create_time &gt; #{params.startTime}
            </if>
            <if test="params!=null and params.endTime!=null">
                and create_time &lt; #{params.endTime}
            </if>
            <if test="params!=null and params.keyWord!=null">
                and (employee_no like #{params.keyWord} or name like #{params.keyWord} )
            </if>
        </where>
		 order by create_time desc
    </select>

返回值类型map:

List<Map<String, Object>> queryInfo(@Param("name") String name, @Param("endTime") Date endTime);

  <select id="queryVehicleRelatedInfo" resultType="java.util.Map">
	 SELECT u.name name,u.position position,u.employee_no employeeNo from user u
  		<if test="name!=null">
           and name = #{name,jdbcType=VARCHAR}
        </if>
		<if test="endTime!=null">
           and create_time &lt; #{endTime}
        </if>	
  </select>

MyBatis在insert插入操作时返回主键ID的配置

<insert id="insert" parameterType="....user" useGeneratedKeys="true" keyProperty="id">

    @Test
    public void test1() {
        User user = new User();
        user.setName("a");
        user.setAge(50);
        Long aLong = userMapperExt.insertSelectiveAndReturnId(user);
        System.out.println(aLong);
    }

条件查询 ,参数为对象
传入参数为NULL,查询所有.

    <select id="selectByUser" parameterType="....user" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from user
        where 1=1
        <if test="id != null">
            and id = #{id,jdbcType=BIGINT}
        </if>
        <if test="name != null">
           and `name` = #{name,jdbcType=VARCHAR}
        </if>
        <if test="age != null">
          and  age = #{age,jdbcType=INTEGER}
        </if>
    </select>

 List<User>  selectByUser(User user);

批量插入: 前者批量插入效率高,后者一条一条插入

    <insert id="batchInsertSelective">
        INSERT INTO user( name,age, create_time)
        VALUES
        <foreach collection="total" item="s" separator=",">
            (#{s.name},#{s.age},  #{s.createTime})
        </foreach>
    </insert>

 <insert id="batchInsertSelective1">
        <foreach collection="total" item="s" index="" open="" close="" separator=";">
            insert into user
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="s.name!= null">
                    name,
                </if>
                <if test="s.age!= null">
                    age,
                </if>
                <if test="s.createTime != null">
                    create_time,
                </if>
            </trim>
            <trim prefix="values (" suffix=")" suffixOverrides=",">
                <if test="s.name!= null">
                    #{s.name,jdbcType=VARCHAR},
                </if>
                <if test="s.area != null">
                    #{s.age,jdbcType=INTEGER},
                </if>    
                <if test="s.createTime != null">
                    #{s.createTime,jdbcType=TIMESTAMP},
                </if>
            </trim>
        </foreach>
    </insert>

CREATE TABLE `teacher` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `teacher_no` varchar(20) DEFAULT NULL COMMENT '老师编号',
  `teacher_name` varchar(20) DEFAULT NULL COMMENT '老师姓名',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `teacher_id` bigint(20) DEFAULT NULL COMMENT '老师id',
  `student_name` varchar(20) DEFAULT NULL COMMENT '老师姓名',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

public class TeacherDto {
    private Long teacherId;
    private String teacherNo;
    private Integer studentCount;
    private Date updateTime;
}

查询不同老师 有学生数量.更新时间为学生表 学生最近时间. 如果老师没有学生,时间为老师新增时间.
(查询学生数量为null,则为0)

  <select id="selectCustomerByStorage" resultType="com...TeacherDto">
           	SELECT
             t.`id`,
             t.`teacher_name`,
             IFNULL(s.studentCount, 0) AS studentCount,
             IF (
               s.update_time IS NULL,
               t.update_time,
               s.update_time
             ) updateTime
           FROM
             teacher t
             LEFT JOIN
               (SELECT
                 teacher_id,
                 COUNT(`student_name`) studentCount,
                 MAX(update_time) update_time
               FROM
                 student
               GROUP BY teacher_id ) s
               ON t.`id` = s.`teacher_id`
    </select>