MyBatis实用总结

一时失言乱红尘 2023-02-13 05:54 6阅读 0赞

### 目录 ###

*   *   *  xml映射文件
         *   *  与mapper接口的对应关系
             *  parameterType、resultType
             *  参数个数的问题
             *  参数名不同的问题
             *  特殊字符转义
             *  \#\{ \}、$\{ \}的区别
             *  resultMap的使用
         *  关联映射
         *  动态sql
         *   *  <if>
             *  <where>
             *  <set>
             *  <trim>
             *  <choose>、<when>、<otherwise> 相当于switch
             *  <foreach> 迭代集合，常用于批量操作
             *  <bind>
         *  其它常见操作
         *   *  insert插入实体后获取记录的主键字段的值
             *  跨库操作
             *  判断增删改的操作结果
             *  同时执行多条sql语句 | 批量操作
         *  常见问题
         *   *  BindingException: Invalid bound statement (not found) mapper接口、xml映射文件对不上
             *  bad SQL grammar \[ \] 数组参数错误
             *  SQLIntegrityConstraintViolationException: Column 'xxx' in field list is ambiguous
             *  PersistenceException: invalid comparison: java.util.Date and java.lang.String 时间日期类型转String出错
             *  字段使用了数据库关键字
             *  多表关联查询时结果集不映射
             *  动态 sql 的 if 语句拼接问题

### xml映射文件 ###

#### 与mapper接口的对应关系 ####

*  namespace和接口名一致（xml文件名不需要和接口名一致）
 *  id和接口中的方法名一致
 *  参数类型、返回值类型与接口方法中的一致

#### parameterType、resultType ####

<typeAliases>
        <package name="com.chy.mall.model"/>
    </typeAliases>

#在springboot中配置实体类别名，有多个路径时用逗号或者分号隔开
    mybatis.type-aliases-package=com.chy.mall.model

*  配置别名后，类名不用写全路径，不区分大小写，但建议使用camel写法。使用自定义的实体类的别名时，IDEA可能会报红，实际可以正常运行，不影响。
 *  如果返会的结果集中有多条记录（List），resultType不能写成list，和返回单条记录一样都是写元素类型，eg. List<User> => user ，如果返回了多条记录，会自动映射为指定类型的List。
 *  涉及到数据库字段的，一律使用包装类型代替基本类型

#### 参数个数的问题 ####

**如果使用了parameterType，只能传入一个参数，如果要传入多个参数，可以放到list、map中传入**

<select id="xxx" parameterType="list" resultType="xxx">
    	
        SELECT * FROM tb_user WHERE name=#{list[2]}
    </select>
    
    
    
    <select id="xxx" parameterType="user" resultType="xxx">
    	
        SELECT * FROM tb_user WHERE name=#{name}
    </select>
    
    
    <select id="xxx" parameterType="map" resultType="xxx">
    	
        SELECT * FROM tb_user WHERE name=#{name}
    </select>

**如果不使用parameterType，可直接传入多个参数**

public interface UserMapper{ 
        User findUser(Integer id, String name);
    }

<select id="findUser" resultType="user">
        SELECT * FROM tb_user WHERE id=#{id} and name=#{name}
    </select>

#### 参数名不同的问题 ####

@Param可解决mapper接口、xml映射文件参数名不同的问题

public interface UserMapper{ 
        // 通过@Param绑定xml中的name变量
        User findUser(Integer id, @Param("name") String username);
    }

<select id="findUser" resultType="user">
        SELECT * FROM tb_user WHERE id=#{id} and name=#{name}
    </select>

#### 特殊字符转义 ####

xml映射文件本身是一个xml文件，包含多个标签，使用的特殊字符需要转义，比如test条件中的大于 <、小于 >与html标签的<>冲突，需要转义。

常见的转义字符如下

<  小于
    >  大于
    
    <=   小于等于
    >=  大于等于
    
    "  双引
    '  单引

#### \#\{ \}、$\{ \}的区别 ####

\#\{ \}用于取值，$\{ \}除了有\#\{ \}的功能，还可以拼接字符串，但使用$\{ \}拼接字符串有sql注入的风险，一律禁用$\{ \}。

以模糊查询为例

<select id="xxx" parameterType="string" resultType="user">
    	SELECT * FROM tb_user WHERE name LIKE '%${name}%'
    </select>
    
    
    <select id="xxx" parameterType="string" resultType="user">
    	
    	SELECT * FROM tb_user WHERE name LIKE concat('%',#{name},'%')
    </select>

#### resultMap的使用 ####

<resultMap> 用于自定义结果集映射

<select id="" parameterType="" resultMap="userMap" fetchSize="10">
    
    </select>
    
    
    
    <resultMap id="userMap" type="user">
        
        <id property="id" column="user_id"/>
        <result property="username" column="user_name"/>
    </resultMap>
    
    
    <select id="" parameterType="" resultMap="userMap">
    
    </select>

此外，<resultMap>还有2个常用子元素<association>、<collection>，用于关联映射。

### 关联映射 ###

**关系数据库中，多表之间有三种关联关系**

*  一对一：一张身份证对应一个人 <=> 一个人也只对应一张身份证
 *  一对多：一个用户可以有多个订单 <=> 这多个订单都属于同一个用户
 *  多对多：一个订单可以包含多种商品 <=> 一种商品可以属于多个订单

**设计数据库时如何处理三种关联关系？**

*  一对一：在一方引入另一方的主键作为外键
 *  一对多：在多的一方，引入一的一方的主键作为外键
 *  多对多：新建一张中间表，引入2张表的主键作为外键，可以使用这2个外键作为中间表的联合主键，也可以新建id字段作为中间表的主键

**java如何处理、描述三种关联关系？**

// 一对一
    class A{ 
        B b;
    }
    
    class B{ 
        A a;
    }

// 一对多，A是一，B是多
    class A{ 
        List<B> b;
    }
    
    class B{ 
        A a;
    }

// 多对多
    class A{ 
        List<B> b;
    }
    
    class B{ 
        List<A> a;
    }

**如果多表查询返回的结果集中包含另一方的字段，如何映射？**

<select id="findStudentById" parameterType="integer" resultMap="studentWithScoreMap">
        
        SELECT st.*,sc.* FROM tb_student st ,tb_score sc  WHERE st.id=#{id} AND st.id=sc.student_id
     </select>
    
    
    <resultMap id="studentWithScoreMap" type="student">
        
        <id property="" column=""/>
        <result property="" column=""/>
    
        
        <association property="score" javaType="score" />
    
        
        <association property="score" javaType="score">
            <id property="" column="" />
            <result property="" column=""/>
        </association>
    
    
        
        <collection property="" ofType="" />
    
        
        <collection property="" ofType="">
            <id property="" column="" />
            <result property="" column=""/>
        </collection>
        
    </resultMap>

### 动态sql ###

动态sql是mybatis的强大特性之一，可以实现sql语句的动态组装，大大减少了需要编写的方法（sql语句）数量。

比如 查询满足条件的用户

findUserById(Integer id);
    
    findUserByName(String name);
    
    findUserByTel(String tel);

使用动态sql之后，写一个 findUser(User user); 即可，用 <where>+<if> 拼接查询条件。

#### <if> ####

<if>常常搭配<where>、<set>之类的使用，也可以单独使用。对 <where>、<set>之类的标签而言，<if>不是必需的。

<select id="findUser" resultType="user">
    	SELECT * FROM tb_user where name=#{name}
        <if test="gender!=null and gender!=''">
            AND gender=#{gender}
        </if>
    </select>

test的等价判断用 ==、!=，逻辑与只能用and、不能用&&，逻辑或可以用 or、||

#### <where> ####

如果有内容就在内容开头添加关键字 where，会自动去除拼接条件的多余的 and、or

<select id="findUser" resultType="user">
    	SELECT * FROM tb_user
    	<where>
    	   <if test="name!=null and name!=''">
    	       name=#{name}
    	   </if>
    	   <if test="gender!=null and gender!=''">
    	       AND gender=#{gender}
    	   </if>
    	</where>
    </select>

#### <set> ####

如果有内容就在内容开头添加关键字 set，会自动去除拼接条件的多余的 逗号

<select id="updateUser" parameterType="user">
    	UPDATE tb_user
    	<set>
    	    <if test="name!=null and name!=''">
    	        name=#{name},
    	    </if>
    	    <if test="tel!=null and tel!=''">
    	        tel=#{tel},
    	    </if>
    	</set>
    	where id=#{id}
    </select>

执行update语句时至少要有一个字段更新，不然会报语法错误。

#### <trim> ####

可以指定内容的前缀、后缀，去除内容开头、末尾多余的字符，十分强大，可以代替 <where>、<set>。

属性

*  prefix：内容前缀，有内容时会自动在内容开头添加
 *  suffix：内容后缀
 *  prefixOverrides：前缀覆盖，如果内容以指定字符串开头，会自动去除|覆盖该字符串
 *  suffixOverrides：

prefixOverrides、suffixOverrides可以指定多个字符，| 分隔，示例：suffixOverrides=“AND | OR”

<insert id="add">
        INSERT INTO video
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="title!=null and title!=''">
                title,
            </if>
            <if test="url!=null and url!=''">
                url,
            </if>
            <if test="cover!=null and cover!=''">
                cover,
            </if>
            <if test="duration!=null and duration!=''">
                duration,
            </if>
        </trim>
        VALUE
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="title!=null and title!=''">
                #{title},
            </if>
            <if test="url!=null and url!=''">
                #{url},
            </if>
            <if test="cover!=null and cover!=''">
                #{cover},
            </if>
            <if test="duration!=null and duration!=''">
                #{duration},
            </if>
        </trim>
    </insert>

#### <choose>、<when>、<otherwise> 相当于switch ####

<when>相当于case，<otherwise>相当于default，条件为true就加上对应的sql语句

<select id="findStudent" resultType="student">
    	SELECT * FROM tb_student WHERE dep=#{dep}
    	<choose>
    	    <when test="englishLevel=='cet4'">
    	        AND english_level='cet4'
    	    </when>
    	    <when test="englishLevel=='cet6'">
    	        AND english_level='cet6'
    	    </when>
    	    <otherwise>
    	        AND english_level<>'cet4' AND english_level<>'cet6'
    	    </otherwise>
    	</choose>
    </select>

#### <foreach> 迭代集合，常用于批量操作 ####

属性

*  collection  指定要迭代的集合名称，需要和形参名保持一致
 *  index  当前迭代元素的下标，从0开始
 *  item  当前迭代到的元素
 *  open  拼接这整段字符串时以什么开头
 *  close  拼接这整段字符串时以什么结尾
 *  separator  分隔符

collection必需，其余均可选。

1、使用<foreach>迭代基本类型的list

Integer batchAddMsgLog(Integer task_id, List<String> telList);

<insert id="batchAddMsgLog">
        insert into tb_msg_log (task_id, user_tel) values
        <foreach collection="telList" item="tel" separator=",">
            (#{task_id}, #{tel})
        </foreach>
    </insert>

2、使用<foreach>迭代实体类的list

Integer addAllTemplate(List<Template> templateList);

<insert id="addAllTemplate">
        insert into tb_template values
        <foreach collection="templateList" item="template" separator=",">
            (#{template.template_id}, #{template.title}, #{template.content}, #{template.example})
        </foreach>
    </insert>

#### <bind> ####

$\{ \}可拼接字符串，但有sql注入的风险；使用数据库自带的方式拼接字符串没有sql注入的风险，mysql可以使用concat()拼接字符串，oracle可以使用 || 拼接字符串，但都只能针对特定的数据库使用，不利于项目移植。

<bind>可拼接字符串，没有sql注入的风险，所有数据库都可用。

<select id="queryUser" parameterType="string" resultType="user">
    	<bind name="pattern_name" value="'%'+name+'%'"/>
    	SELECT * FROM user_tb WHERE name LIKE #{pattern_name}
    </select>

### 其它常见操作 ###

#### insert插入实体后获取记录的主键字段的值 ####

有2种情况

1、使用的是支持主键自增的数据库，比如mysql、sql server，设计表时勾选主键自增

<insert id="addUser" parameterType="user" keyProperty="id" useGeneratedKeys="true">
    	insert into tb_user(nickname,tel) values (#{nickname},#{tel})
    </insert>

insert语句中不需要设置主键字段的值。

2、使用的是不支持主键自增的数据库，比如oracle；或者使用的是支持主键自增的数据库，但设计表时没有勾选主键自增。

需要增加一个select来查询主键字段应取的值

<insert id="addUser" parameterType="user">
    	
    	<selectKey keyProperty="id" resultType="integer" order="BEFORE">
    	    select if(max(id) is null, 1, max(id)+1) from tb_user
    	</selectKey>
    	insert into tb_user(id,nickname,tel) values (#{id},#{nickname},#{tel})
    </insert>

before的执行过程：  
执行select获取主键字段应该的值，自动调用setter方法设置实体类主键字段的值，执行insert插入记录。insert中需要设置主键字段的值。

情况1也可以这样写

<insert id="addUser" parameterType="user">
    	
    	<selectKey keyProperty="id" resultType="integer" order="AFTER">
    	    select id from tb_user where nickname=#{nickname} and tel=#{tel}
    	</selectKey>
    	insert into tb_user(nickname,tel) values (#{nickname},#{tel})
    </insert>

after的执行过程：  
先执行insert插入记录，再执行select获取主键字段的值，自动调用setter方法设置实体类中主键字段的值。insert中不需要设置主键字段的值。

before可以检查返回的受影响的记录数是否为1来判断插入是否成功（期间是否有新的insert，导致主键字段的值变化），不用加乐观锁；而after的select不能直接选取max(id)，因为insert之后、select之前可能有其他的insert导致max(id)不是当前记录的id。

before、after的sql语句写法是不同的。

#### 跨库操作 ####

#url中不写具体的具体库
    jdbc:mysql://127.0.0.1:3306/?serverTimezone=UTC

<delete id="deleteAll">
    	delete from `库名`.`表名`;
    </delete>

#### 判断增删改的操作结果 ####

*  在mapper接口中把增删改的返回值类型声明为 int
 *  在数据库连接的 url 中加一个配置项

#返回受影响的记录数
    useAffectedRows=true

如果不加，默认返回的是匹配的记录数（执行成功+失败的记录数），而我们需要的是受影响的记录数（执行成功的记录数）。

#### 同时执行多条sql语句 | 批量操作 ####

在数据库连接的url中加一个配置项

#允许批量操作
    allowMultiQueries=true

这样就可以在sql语句中使用分号拼接多条sql，从而实现一次性执行多条sql，常用于批量插入、更新。

### 常见问题 ###

#### BindingException: Invalid bound statement (not found) mapper接口、xml映射文件对不上 ####

*  yml中的mybatis配置的xml映射文件路径是否正确
 *  xml映射文件的namespace和接口名是否一致，namespace要是接口名的全路径，id和接口中的函数名是否一致，参数类型、返回值类型是否一致‘’
 *  xml映射文件是否忘写了后缀.xml  
    ![在这里插入图片描述][20200528131305203.PNG]

#### bad SQL grammar \[ \] 数组参数错误 ####

原因：参数列表、数组类型，但传入的是一个空列表、空数组。

解决方案：在service层或controller层校验对应的数组是否为null、是否有元素。

#### SQLIntegrityConstraintViolationException: Column ‘xxx’ in field list is ambiguous ####

> ambiguous 模糊不清的，模棱两可的；不明确的，不明朗的；有歧义的  
> 原

常见原因：多表关联查询时 `select xxx` 直接选取字段，该字段再使用的多张表中存在，未指定使用哪张表中的。

解决方法：`select xxx.xxx` ， 多表查询时以 `表名.字段名` 选取字段

#### PersistenceException: invalid comparison: java.util.Date and java.lang.String 时间日期类型转String出错 ####

2020-09-24 02:13:15.739  WARN 31080 --- [nio-8080-exec-9] .m.m.a.ExceptionHandlerExceptionResolver : Resolved [org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
    ### Error updating database. Cause: java.lang.IllegalArgumentException: invalid comparison: java.util.Date and java.lang.String
    ### The error may exist in file [C:\Users\chy\Desktop\mall\target\classes\mapper\UserMapper.xml]
    ### The error may involve com.xxx.adult.mapper.UserMapper.add
    ### The error occurred while executing an update
    ### Cause: java.lang.IllegalArgumentException: invalid comparison: java.util.Date and java.lang.String]

报错中已经指出了出问题的mapper文件、方法，一般是在xml映射文件中校验日期类型时，加了不等于空串这个判断，mybatis会把Date类型转换为String进行比较

mybatis本身的Date转String存在bug，容易出错，解决方案是Date类型不要 !=’’ ，不和字符串比较

#### 字段使用了数据库关键字 ####

在IDEA中，xml映射文件，sql语句中的 sql 关键字是黄色的，字段名是白色的，如果字段名是黄色的，多半是占用了sql关键字，容易导致 sql 语句不合法。

常见的解决方案

*  加反引号 \` \`
 *  如果加了反引号还是黄色，可以改字段名，比如 type 可以改为 xxx\_type

#### 多表关联查询时结果集不映射 ####

原因：select以 `表名.列名` 的形式选取字段，结果集映射时识别不了 `表名.列名` 形式的列名

解决方案：给列起别名，使用别名进行映射

#### 动态 sql 的 if 语句拼接问题 ####

有 <if> 条件判断时，尽量用动态sql自带的 <where>、<set>之类的标签，避免直接使用 sql 的where、when。

多个 <if> 中往往有条件拼接，比如 and、or、逗号

UPDATE xxx
    <set>
        <if test="name!=null and name!=''">
            name=#{name},
        </if>
        <if test="birthday!=null">
            birthday=#{birthday},
        </if>
        <if test="height!=null and height!=''">
            height=#{height},
        </if>
        <if test="weight!=null and weight!=''">
            weight=#{weight},
        </if>
        <if test="cup!=null and cup!=''">
            cup=#{cup},
        </if>
        <if test="actressType!=null and actressType!=''">
            actress_type=#{actressType},
        </if>
    </set>
    WHERE id=#{id}

使用 <set>、<where> 之类的的外层标签时，会自动去除内部多余的 and、or、逗号，不用管条件的拼接问题；直接使用 <if>则容易出错，比如以上代码，如果不使用 <set> ，sql 语句的末尾是逗号，这显然是错的。

[20200528131305203.PNG]: https://img-blog.csdnimg.cn/20200528131305203.PNG