Mybatis SQL语句编写
1.插入多条数据并获取主键
一个非常简单的插入sql
<code>insert into table (aa,bb,cc) values(xx,xx,xx),(yy,yy,yy)<code>
在Mapper中写入对应的sql模板
<insert id="insertUser" keyProperty="id" useGeneratedKeys="id"> insert into xxxx (xx) value (xxx) </insert>
2.批量插入数据 并返回主键
<insert id="insertBatch" parameterType="java.util.List" useGeneratedKeys="true"> <selectKey resultType="long" keyProperty="id" order="AFTER"> SELECT LAST_INSERT_ID() </selectKey> insert into table (aa, bb,cc) values <foreach collection="list" item="item" index="index" separator=","> ( #{item.aa},#{item.bb},#{item.cc} ) </foreach> </insert>
3.批量执行多条sql
首先数据库连接字符串需要加上如下参数,允许一次提交多条sql
allowMultiQueries=true
<update id="batchUpdate" parameterType="java.util.Map"> <!-- 接收list参数,循环着组装sql语句,注意for循环的写法 separator=";" 代表着每次循环完,在sql后面放一个分号 item="cus" 循环List的每条的结果集 collection="list" list 即为 map传过来的参数key --> <foreach collection="list" separator=";" item="cus"> update t_customer set c_name = #{cus.name}, c_age = #{cus.age}, c_sex = #{cus.sex}, c_ceroNo = #{cus.ceroNo}, c_ceroType = #{cus.ceroType} where id = #{cus.id} </foreach> </update>
4.通过 resultMap 获取一个包含一个子属性是javabean的查询结果List
<resultMap id="studentResultMap" type="com.text.Student"> <id property="id" column="id"> <result property="name" column="name" /> <result property="age" column="age" /> <association proprety="clazz" column="clazz_id" javaType="com.test.clazz" select="selectClazzWithId" /> </resultMap> <select id="selectStudent" resultMap="StudentResultMap"> select * from student </select> <select id ="selectClazzWithId" resultType="com.test.clazz"> select * from clazz where id = #{id} </select>
5.通过 resultMap 获取一个包含一个子属性是List的查询结果List
<resultMap id="clazzResultMap" type="com.text.Clazz"> <id property="id" column="id"> <result property="code" column="code" /> <collection properety="students" javaType="ArrayList" column="id" ofType="com.test.Student" select="selectStudentWithId"> </resultMap> <select id="selectStudentWithId" > select * from student where clazz_id = #{id} </select> <select id ="selectClazz" resultType="com.test.clazz" resultMap="clazzResultMap"> select * from clazz where </select>
6.一对多查询中使用懒加载模式
1.sql 属性 添加 fetchType="lazy"
2.mybatis设置开启懒加载
7.动态创建sql
语法: if choose(when otherwise) set foreach bind
if
:条件判断语句 一般作为where子句的一部分
test的值为一个表达式 多个逻辑判断使用and连接
<select id="saveOldPassword" parameterType="Map"> UPDATE user_account SET `password` = #{password} <if test="id != null"> WHERE id = #{id} </if> </select>
choose(when otherwise):
choose,when,otherwise组合使用,达到使用switch的效果
<select id="findUserInfoByOneParam" parameterType="Map" resultMap="UserInfoResult"> select * from userinfo <choose> <when test="searchBy=='department'"> where department=#{department} </when> <when test="searchBy=='position'"> where position=#{position} </when> <otherwise> where gender=#{gender} </otherwise> </choose> </select>
where:
where元素主要解决多个if判断内包含where条件的问题,加入第一个if的where条件不成立,则之后条件成立的if语句内的sql会多一个and,因为它以及变整个sql上的第一个where条件了。
例如:
select * from user where <if test="id ! = null"> id = #{id} </if> <if test="name != null"> and name = #{name} </if>
假如id值为null,name则变成 select * from user where and name = #{name}
现在改成如下代码,则在id为null的情况下,会自动过滤第二个条件中的and
select * from user where <where> <if test="id ! = null"> id = #{id} </if> <if test="name != null"> and name = #{name} </if> </where>
set:
set用于update语句,在update语法中,有个set关键词用于设置值,比如 update users set name = 'new name' where id = #{id}
我们发现更新的字段和更新的值都是一一对应的,假如我的sql并不确定要更新那些列,需要根据传入的参数,如果值不为null则更新。这个是要就可以使用set元素。
update user <set> <if test="name">name = #{name} <if test="age">name = #{gae} <if test="sex">name = #{sex} <if test="class">name = #{class} </set> where id = #{id}
foreach:
foreach 主要用于遍历一个集合。
collection表示要遍历的元素 index 表示索引 item 表示每个子元素 open close 表示 首位 用什么字符串包括,separator表示分隔符
select * from user where id in <foreach collection="ids" index="index" item="id" open="(" close=")" separator=","> #{id} </foreach>
bind:
在sql定义内部定义一个临时变量
<select> <bind name="parrern" value="'%'+ _parameter.getName()+'%'"> select * from user where name like #{pattern} </select>
trim:
trim配合set或者where完成格式化,主要解决多个动态列之间的关联问题
因为使用if条件之后并不确定最终那些字段是开头,那个字段是末尾。可能导致开头会多个and 末尾多个逗号。
trim的属性使用prefix设置起始字符串,suffix设定末尾字符串 prefixoverride指定需要消除的多余的起始字符串,suffixoverride指定需要消除的多余的末尾字符串
<trim prefix="values (" suffix=")" suffixOverrides="," > #{id, jdbcType=VARCHAR}, #{memberId, jdbcType=VARCHAR}, <if test='messageClassify != null and messageClassify != "" '> #{messageClassify, jdbcType=VARCHAR}, </if> <if test='messageCode != null and messageCode != "" '> #{messageCode, jdbcType=VARCHAR}, </if> <if test='messageContent != null and messageContent != "" '> #{messageContent, jdbcType=VARCHAR}, </if> </trim>
8.使用Provider注解调用自定义的sql构建方法
mybatis中具体某个查询的定义可以写在一个mapper中,也可以写在查询定义接口的注解上。
如果在mapper中,则可以使用 foreach when之类的语法动态构建SQL。
而使用注解定义SQL的话,则可以使用@SelectProvider @UpdateProvider等注解自定义动态SQL的构建方法。
相应的属性使用@Options定义。Provider类一般命名为【***DynaSqlProvider】
例如:
@SelectProvider(type = UrlInterceptor.class,method = "getList") ListgetList(); public String getList() { return new SQL() {{ SELECT("*"); FROM("table_name"); WHERE("status = 1"); }}.toString(); }
在SQL类中直接使用了实例初始化块构建了一个SQL,这也是一种比较常见的类初始化方式。在SQL类中,提供了 SELECT FROM JOIN LEFT_JOIN OR AND 等语法。基本和sql的语法一致。
使用了Provider之后,我们可以通过java代码实现动态SQL构建,而不是一定要在mapper中定义。