mybatis主键自增,关联查询,动态sql方式
目录
- 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属性是Javpythona包装类对象的属性名
- keyColumn属性是mysql表中的字段名
非自增主键
uuid类型和oracle的序列主键nextval,它们都是在insert之前生成的,其实就是执行了SQL的uuid()方法及nextval()方法,所以SQL映射文件的配置与上面的配置类似,依然使用< selectKey>标签对,但是order属性被设置为before(因为是在insert之前执行),resultType根据主键实际类型设定
UUID配置
<selectKey keyProperty="userId" order="Bwww.devze.comEFORE" 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& username: root password: root driver-class-name: com.mysql.jdbc.Driver #MyBatis配置 mybatis: type-aliases-package: com.mye.hl07www.devze.commybatis.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需要将sq编程客栈l语句包含在script标签里。
在 < script>< /script>内使用特殊符号,则使用java的转义字符,如 双引号 ""
使用\"\"
代替
<script></script>
< 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"> <!-- 要复用的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"/> <!-- 这里可能还会引入其他的SQL片段 --> </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>标签属性说明:
属性 | 说明 |
---|---|
index | 当迭代对象是数组,列表时,表示的是当前迭代的次数。 |
item | 当迭代对象是数组,列表时,表示的是当前迭代的元素。 |
collection | 当前遍历的对象。 |
open | 遍历的SQL以什么开头。 |
close | 遍历的SQL以什么结尾。 |
separator | 遍历完一次后,在末尾添加的字符等。 |
需求:
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);
案例:
<!-- 使用foreach标签,拼接or语句 --> <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> <!-- 使用foreach标签,拼接in语句 --> <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&gjst;" + "</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 语句的最后就会有一个逗号遗留。
总结
以上为个人经验,希望能给大家一个参考,也希望大家多多支持编程客栈(www.devze.com)。
精彩评论