mybatis 主键自增,关联查询,动态sql

待我称王封你为后i 2024-03-27 11:16 71阅读 0赞

#### 文章目录 ####

*  mybatis 主键自增,关联查询,动态sql
 *   *  主键自增
     *   *  selectKey标签(注解)
         *  useGeneratedKeys属性、keyProperty属性
         *  非自增主键
     *  关联查询
     *   *  添加依赖
         *  使用@One注解实现一对一关联查询
         *  使用@Many注解实现一对多关联查询
     *  MyBatis动态SQL
     *   *  script
         *  < where>标签、< if>标签
         *  < sql>片段
         *  < foreach>标签
         *  < choose>标签、< when>标签、< otherwise>标签
         *  < trim>标签、< set>标签

## mybatis 主键自增,关联查询,动态sql ##

### 主键自增 ###

#### selectKey标签(注解) ####

selectKey标签

<insert id="insertUser" parameterType="com.mybatis.po.User">
        <selectKey keyProperty="userId" order="AFTER" resultType="java.lang.Integer">
            SELECT LAST_INSERT_ID()
        </selectKey>
        INSERT INTO tb_user(user_name,blog_url,remark)
        VALUES(#{
        userName},#{
        blogUrl},#{
        remark})
    </insert>

selectKey注解

@Insert(" insert into table(c1,c2) values (#{c1},#{c2}) ")
    @SelectKey(resultType = long.class,keyColumn = "id",before = false,statement = "SELECT LAST_INSERT_ID() AS id",keyProperty = "id")

参数解释：

*  before=false：由于mysql支持自增长主键，所以先执行插入语句，再获取自增长主键值
 *  keyColumn：自增长主键的字段名
 *  keyProperty: 实体类对应存放字段，注意数据类型和resultType一致
 *  tatement：实际执行的sql语句

SelectKey返回的值存在实体类中，线程安全，所以不论插入成功与否id都会安全自增

#### useGeneratedKeys属性、keyProperty属性 ####

xml文件方式

<insert id="insertUser" useGeneratedKeys="true" keyProperty="userId" parameterType="com.mybatis.po.User">
        INSERT INTO tb_user(user_name,blog_url,remark)
        VALUES(#{
        userName},#{
        blogUrl},#{
        remark})
    </insert>

注解方式

@Insert("INSERT INTO tb_user(user_name,blog_url,remark)  VALUES(#{userName},#{blogUrl},#{remark}")
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")

参数解释：

*  useGeneratedKeys属性表示使用自增主键
 *  keyProperty属性是Java包装类对象的属性名
 *  keyColumn属性是mysql表中的字段名

#### 非自增主键 ####

uuid类型和Oracle的序列主键nextval，它们都是在insert之前生成的，其实就是执行了SQL的uuid()方法及nextval()方法，所以SQL映射文件的配置与上面的配置类似，依然使用< selectKey>标签对，但是order属性被设置为before（因为是在insert之前执行），resultType根据主键实际类型设定

UUID配置

<selectKey keyProperty="userId" order="BEFORE" resultType="java.lang.String">
        SELECT uuid()
    </selectKey>

Oracle序列配置

<selectKey keyProperty="userId" order="BEFORE" resultType="java.lang.String">
        SELECT 序列名.nextval() FROM DUAL
    </selectKey>

### 关联查询 ###

#### 添加依赖 ####

<dependencies>
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
                <version>3.4.2</version>
            </dependency>
    
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>1.3.0</version>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.34</version>
            </dependency>
        </dependencies>

spring:
      #DataSource数据源
      datasource:
        url: jdbc:mysql://localhost:3306/mybatis_test?useSSL=false&amp
        username: root
        password: root
        driver-class-name: com.mysql.jdbc.Driver
    
    #MyBatis配置
    mybatis:
      type-aliases-package: com.mye.hl07mybatis.api.pojo #别名定义
      configuration:
        log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #指定 MyBatis 所用日志的具体实现，未指定时将自动查找
        map-underscore-to-camel-case: true #开启自动驼峰命名规则（camel case）映射
        lazy-loading-enabled: true #开启延时加载开关
        aggressive-lazy-loading: false #将积极加载改为消极加载（即按需加载）,默认值就是false
        lazy-load-trigger-methods: "" #阻挡不相干的操作触发，实现懒加载
        cache-enabled: true #打开全局缓存开关（二级环境），默认值就是true

#### 使用@One注解实现一对一关联查询 ####

> 需求：获取用户信息，同时获取一对多关联的权限列表

创建实体类

@Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class UserInfo {
        
        private int userId; //用户编号
        private String userAccount; //用户账号
        private String userPassword; //用户密码
        private String blogUrl; //博客地址
        private String remark; //备注
        private IdcardInfo idcardInfo; //身份证信息
    }
    
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class IdcardInfo {
        
        public int id; //身份证ID
        public int userId; //用户编号
        public String idCardCode; //身份证号码
    }

一对一关联查询

@Repository
    @Mapper
    public interface UserMapper {
        
        /**
         * 获取用户信息和身份证信息
         * 一对一关联查询
         */
        @Select("SELECT * FROM tb_user WHERE user_id = #{userId}")
        @Results(id = "userAndIdcardResultMap", value = {
        
                @Result(property = "userId", column = "user_id", javaType = Integer.class, jdbcType = JdbcType.INTEGER, id = true),
                @Result(property = "userAccount", column = "user_account",javaType = String.class, jdbcType = JdbcType.VARCHAR),
                @Result(property = "userPassword", column = "user_password",javaType = String.class, jdbcType = JdbcType.VARCHAR),
                @Result(property = "blogUrl", column = "blog_url",javaType = String.class, jdbcType = JdbcType.VARCHAR),
                @Result(property = "remark", column = "remark",javaType = String.class, jdbcType = JdbcType.VARCHAR),
                @Result(property = "idcardInfo",column = "user_id",
                        one = @One(select = "com.mye.hl07mybatis.api.mapper.UserMapper.getIdcardInfo", fetchType = FetchType.LAZY))
        })
        UserInfo getUserAndIdcardInfo(@Param("userId")int userId);
     
        /**
         * 根据用户ID，获取身份证信息
         */
        @Select("SELECT * FROM tb_idcard WHERE user_id = #{userId}")
        @Results(id = "idcardInfoResultMap", value = {
        
                @Result(property = "id", column = "id"),
                @Result(property = "userId", column = "user_id"),
                @Result(property = "idCardCode", column = "idCard_code")})
        IdcardInfo getIdcardInfo(@Param("userId")int userId);
    }

#### 使用@Many注解实现一对多关联查询 ####

> 需求：获取用户信息，同时获取一对多关联的权限列表

创建实体类

@Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class RoleInfo {
        
        private int id; //权限ID
        private int userId; //用户编号
        private String roleName; //权限名称
    }
    
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class UserInfo {
        
        private int userId; //用户编号
        private String userAccount; //用户账号
        private String userPassword; //用户密码
        private String blogUrl; //博客地址
        private String remark; //备注
        private IdcardInfo idcardInfo; //身份证信息
        private List<RoleInfo> roleInfoList; //权限列表
    }

一对多关联查询

/**
     * 获取用户信息和权限列表
     * 一对多关联查询
     * @author pan_junbiao
     */
    @Select("SELECT * FROM tb_user WHERE user_id = #{userId}")
    @Results(id = "userAndRolesResultMap", value = {
        
            @Result(property = "userId", column = "user_id", javaType = Integer.class, jdbcType = JdbcType.INTEGER, id = true),
            @Result(property = "userAccount", column = "user_account",javaType = String.class, jdbcType = JdbcType.VARCHAR),
            @Result(property = "userPassword", column = "user_password",javaType = String.class, jdbcType = JdbcType.VARCHAR),
            @Result(property = "blogUrl", column = "blog_url",javaType = String.class, jdbcType = JdbcType.VARCHAR),
            @Result(property = "remark", column = "remark",javaType = String.class, jdbcType = JdbcType.VARCHAR),
            @Result(property = "roleInfoList",column = "user_id", many = @Many(select = "com.pjb.mapper.UserMapper.getRoleList", fetchType = FetchType.LAZY))
    })
    public UserInfo getUserAndRolesInfo(@Param("userId")int userId);
     
    /**
     * 根据用户ID，获取权限列表
     * @author pan_junbiao
     */
    @Select("SELECT * FROM tb_role WHERE user_id = #{userId}")
    @Results(id = "roleInfoResultMap", value = {
        
            @Result(property = "id", column = "id"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "roleName", column = "role_name")})
    public List<RoleInfo> getRoleList(@Param("userId")int userId);

### MyBatis动态SQL ###

#### script ####

注解版下，使用动态SQL需要将sql语句包含在script标签里。

在 < script>< /script>内使用特殊符号，则使用java的转义字符，如 双引号`""` 使用`\"\"` 代替

#### < where>标签、< if>标签 ####

`if`：通过判断动态拼接sql语句，一般用于判断查询条件

当查询语句的查询条件由于输入参数的不同而无法确切定义时，可以使用< where>标签对来包裹需要动态指定的SQL查询条件，而在< where>标签对中，可以使用< if test=“…”>条件来分情况设置SQL查询条件

> 当使用标签对包裹 if 条件语句时，将会忽略查询条件中的第一个and或or

<select id="queryUserInfo" parameterType="com.mybatis.po.UserParam" resultType="com.mybatis.po.User">
        SELECT * FROM tb_user
        <where>
            <if test="userId > 0">
                and user_id = #{userId}
            </if>
            <if test="userName!= null and userName!=''">
                and user_name like '%${userName}%'
            </if>
            <if test="sex!=null and sex!=''">
                and sex = #{sex}
            </if>
        </where>
    </select>

@Select({
        "<script>" +
                " select * from tb_user " +
                "<where>" +
                "<if test = 'userId != null and userId !=\"\" '> " +
                "and user_Id = #{userId} " +
                "</if>" +
                "<if test = 'userPassword != null and userPassword !=\"\" '> " +
                "and user_password like CONCAT('%',#{userPassword},'%')" +
                "</if>" +
                "</where>" +
                "</script>"})

#### < sql>片段 ####

MyBatis提供了可以将复用性比较强的SQL语句封装成“SQL片段”，在需要使用该SQL片段的映射配置中声明一下，即可引入该SQL语句，声明SQL片段的格式如下：

例子：

<sql id="query_user_where">
        <if test="userId>0">
            AND user_id = #{userId}
        </if>
        <if test="userName!=null and userName!=''">
            AND user_name like '%${userName}%'
        </if>
        <if test="sex!=null and sex!=''">
            AND sex = #{sex}
        </if>
    </sql>
     
    
    <select id="queryUserInfo" parameterType="com.mybatis.po.UserParam" resultType="com.mybatis.po.User">
        SELECT * FROM tb_user
        <where>
            <include refid="query_user_where"/>
            
        </where>
    </select>

>  *  id是SQL片段的唯一标识，是不可重复的
>  *  SQL片段是支持动态SQL语句的，但建议，在SQL片段中不要使用< where>标签,而是在调用的SQL方法中写< where>标签，因为该SQL方法可能还会引入其他的SQL片段，如果这些多个的SQL片段中都有< where>标签，那么会引起语句冲突。
>  *  SQL映射配置还可以引入外部Mapper文件中的SQL片段，只需要在refid属性填写的SQL片段的id前添加其所在Mapper文件的namespace信息即可（如：test.query\_user\_where）

#### < foreach>标签 ####

**< foreach>标签属性说明：**

<table> 
 <thead> 
  <tr> 
   <th>属性</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>index</td> 
   <td>当迭代对象是数组，列表时，表示的是当前迭代的次数。</td> 
  </tr> 
  <tr> 
   <td>item</td> 
   <td>当迭代对象是数组，列表时，表示的是当前迭代的元素。</td> 
  </tr> 
  <tr> 
   <td>collection</td> 
   <td>当前遍历的对象。</td> 
  </tr> 
  <tr> 
   <td>open</td> 
   <td>遍历的SQL以什么开头。</td> 
  </tr> 
  <tr> 
   <td>close</td> 
   <td>遍历的SQL以什么结尾。</td> 
  </tr> 
  <tr> 
   <td>separator</td> 
   <td>遍历完一次后，在末尾添加的字符等。</td> 
  </tr> 
 </tbody> 
</table>

需求：

SELECT * FROM tb_user WHERE user_id=2 OR user_id=4 OR user_id=5;
    -- 或者
    SELECT * FROM tb_user WHERE user_id IN (2,4,5);

案例：

<sql id="query_user_or">
        <if test="ids!=null and ids.length>0">
            <foreach collection="ids" item="user_id" open="AND (" close=")" separator="OR">
                user_id=#{user_id}
            </foreach>
        </if>
    </sql>
    
    
    <sql id="query_user_in">
        <if test="ids!=null and ids.length>0">
            AND user_id IN
            <foreach collection="ids" item="user_id" open="(" close=")" separator=",">
                #{user_id}
            </foreach>
        </if>
    </sql>

@Insert("<script>" +
                "insert into tb_user(user_id,user_account,user_password,blog_url,blog_remark) values" +
                "<foreach collection = 'list' item = 'item' index='index' separator=','>" +
                "(#{item.userId},#{item.userAccount},#{item.userPassword},#{item.blogUrl},#{item.blogRemark})" +
                "</foreach>" +
                "</script>")
        int insertByList(@Param("list") List<UserInfo> userInfoList);
    
        @Select("<script>" +
                "select * from tb_user" +
                " WHERE user_id IN " +
                "<foreach collection = 'list' item = 'id' index='index' open = '(' separator= ',' close = ')'>" +
                "#{id}" +
                "</foreach>" +
                "</script>")
        List<UserInfo> selectByList(@Param("list") List<Integer> ids);
        
    	@Update({
        "<script>" +
                "<foreach item='item' collection='list' index='index' open='' close='' separator=';'>" +
                " UPDATE tb_user " +
                "<set>" +
                "<if test='item.userAccount != null'>user_account = #{item.userAccount},</if>" +
                "<if test='item.userPassword != null'>user_password=#{item.userPassword}</if>" +
                "</set>" +
                " WHERE user_id = #{item.userId} " +
                "</foreach>" +
                "</script>"})
       	int updateBatch(@Param("list")List<UserInfo> userInfoList);

#### < choose>标签、< when>标签、< otherwise>标签 ####

有时我们不想应用到所有的条件语句，而只想从中择其一项。针对这种情况，MyBatis提供了choose元素，它有点像Java中的switch语句。

<select id="queryUserChoose" parameterType="com.mybatis.po.UserParam" resultType="com.mybatis.po.User">
        SELECT * FROM tb_user
        <where>
            <choose>
                <when test="userId>0">
                    AND user_id = #{userId}
                </when>
                <when test="userName!=null and userName!=''">
                    AND user_name like '%${userName}%'
                </when>
                <otherwise>
                    AND sex = '女'
                </otherwise>
            </choose>
        </where>
    </select>

@Select("<script>"
                + "select * from tb_user "
                + "<where>"
                + "<choose>"
                + "<when test='userId != null and userId != \"\"'>"
                + "   and user_id = #{userId}"
                + "</when>"
                + "<otherwise test='userAccount != null and userAccount != \"\"'> "
                + "   and user_account like CONCAT('%', #{userAccount}, '%')"
                + "</otherwise>"
                + "</choose>"
                + "</where>"
                + "</script>")
        List<UserInfo> selectAll(UserInfo userInfo);

#### < trim>标签、< set>标签 ####

MyBatis还提供了< trim>标签，我们可以通过自定义< trim>标签来定制< where>标签的功能。比如，和< where>标签等价的自定义 < trim>标

prefixOverrides 属性会忽略通过管道分隔的文本序列（注意此例中的空格也是必要的）。它的作用是移除所有指定在 prefixOverrides 属性中的内容，并且插入 prefix 属性中指定的内容。

使用自定义< trim>标签来定制< where>标签的功能，获取用户信息:

<select id="queryUserTrim" parameterType="com.mybatis.po.UserParam" resultType="com..mybatis.po.User">
        SELECT * FROM tb_user
        <trim prefix="WHERE" prefixOverrides="AND |OR ">
            <if test="userId>0">
                and user_id = #{userId}
            </if>
            <if test="userName!=null and userName!=''">
                and user_name like '%${userName}%'
            </if>
            <if test="sex!=null and sex!=''">
                and sex = #{sex}
            </if>
        </trim>
    </select>

在修改用户信息的SQL配置方法中，使用< set>标签过滤多余的逗号：

<update id="updateUser" parameterType="com.pjb.mybatis.po.UserParam">
        UPDATE tb_user
        <set>
            <if test="userName != null">user_name=#{userName},</if>
            <if test="sex != null">sex=#{sex},</if>
            <if test="age >0 ">age=#{age},</if>
            <if test="blogUrl != null">blog_url=#{blogUrl}</if>
        </set>
        where user_id = #{userId}
    </update>

这里，< set>标签会动态前置SET关键字，同时也会删掉无关的逗号，因为用了条件语句之后很可能就会在生成的SQL语句的后面留下这些逗号。因为用的是“if”元素，若最后一个“if”没有匹配上而前面的匹配上，SQL 语句的最后就会有一个逗号遗留。