mybaties应用(常用sql函数)

我的搬砖日常

已于 2024-05-22 16:48:48 修改

阅读量9.5k

点赞数 18

分类专栏： spring 文章标签： mybatis java

于 2022-08-07 20:57:25 首次发布

本文链接：https://blog.csdn.net/weixin_44383484/article/details/126210144

版权

spring 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

1增删改查

阿里规范泰山版规范

禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
不要写一个大而全的数据更新接口。传入为 POJO 类，不管是不是自己的目标更新字段，都进行 update table set c1=value1,c2=value2,c3=value3; 这是不对的。执行 SQL 时，不要更新无改动的字段，一是易出错；二是效率低；三是增加 binlog 存储。
批量查询保持数据在 1000条数据即可
查询，响应也不要用 map.
不要使用 count(列名)或 count(常量)来替代 count(*)，count(*)是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。

insert

单个插入



public int  insertSelective(t_user user);

  
suffixOverrides="," 去除后缀多余的逗号
<insert id="insertSelective" useGeneratedKeys="true" keyProperty="id">
   <!--  不要用，这种方式仅限于mysql 
         keyProperty:t_user对象中主键对应的属性
         resultType:主键返回的类型
         order：取主键的语句的时机
            BEFORE:执行插入之前执行
            AFTER：执行插入之后执行   
         -->
        <selectKey keyProperty="id" resultType="long" order="AFTER">
            SELECT LAST_INSERT_ID()
        </selectKey>
        insert into t_user
        <trim prefix="(" suffix=")" suffixOverrides="," >
            <if test="id != null">t_id,</if>
            <if test="name != null and name !='' ">t_name,</if>
            <if test="age != null">t_age,</if>
            <if test="address != null and address !=''">t_address,</if>
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="name != null and name !=''">#{name},</if>
            <if test="age != null">#{age},</if>
            <if test="address != null and address !=''">#{address},</if>
        </trim>
    </insert>

批量插入

版本1

public int  insertList(List<t_user> user);

    <insert id="insertList" parameterType="java.util.List">
        insert into t_user (t_id,t_name,t_age,t_address)
        values
        <foreach collection="list" item="item" index="index" separator=",">
          (#{item.id},#{item.name},#{item.age},#{item.address})
        </foreach>
    </insert>

版本2 (@Param)

import org.apache.ibatis.annotations.Param;

public int  insertList(@Param("userList") List<t_user> user);

  parameterType="java.util.List" 可加可不加
<insert id="insertList" parameterType="java.util.List">
        insert into t_user (t_id,t_name,t_age,t_address)
        values
        <foreach collection="userList" item="item" index="index" separator=",">
          (#{item.id},#{item.name},#{item.age},#{item.address})
        </foreach>
    </insert>

更新

单个更新

public int  update(t_user user);

    <update id="update" parameterType="com.example.demomybaties.entity.t_user">
        update t_user
        <set>
            <if test="name != null and name !=''">
                t_name = #{name},
            </if>
            <if test="age != null">
                t_age=#{age},
            </if>
            <if test="address != null and address !=''">
                t_address= #{address},
            </if>
        </set>
        <where>
            t_id=#{id}
        </where>
    </update>

批量更新

注意：主键相同的两条数据只会更新第一条数据，第二条数据不会覆盖第一条数据。

public int  updateList(List<t_user> user);

<update id="updateList" parameterType="java.util.List" >
    <foreach collection="list" item="item" index="index" separator=";">
        update t_user
        <set>
            <if test="item.name != null and item.name !=''">
                t_name = #{item.name},
            </if>
            <if test="item.age != null">
                t_age=#{item.age},
            </if>
            <if test="item.address != null and item.address !=''">
                t_address= #{item.address},
            </if>
        </set>
        <where>
            t_id=#{item.id}
        </where>
    </foreach>
</update>

查询数据

字段相同能映射，不同resultMap映射，或者框架注解封装映射也可以

根据批量主键查询

 List<t_user> query(List<String> ids);

字段相同能映射，不同resultMap映射，或者框架注解封装映射也可以 
<select id="query" resultType="com.example.demomybaties.entity.t_user" parameterType="String">
        SELECT t_id AS id,t_name AS name FROM `t_user` WHERE t_id IN
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
    </select>

根据批量对象查询

主键重复只会查询第一个条件，更新也是，再次强调只看主键，不是看其他条件的本人又测试了一下附带其他条件，不会返回数据得。
特定情况下，入参的东西也可以响应回来

List<t_user> queryUsera(List<t_user> list);

  <select id="queryUsera"  resultType="com.example.demomybaties.entity.t_user" parameterType="com.example.demomybaties.entity.t_user">
        <foreach collection="list" index="index" item="item" separator=";" >
            SELECT t_id AS id,t_name AS name, #{item.connectionId} as connectionId  FROM `t_user` WHERE t_id =
            #{item.id}
        </foreach>
    </select>

一对一映射查询

模拟场景：一个用户只能有一个订单时

List<t_user> queryUserOrder();

一对多映射查询

模拟场景：一个用户有多个订单时

List<t_user> queryUserOrder();

标签使用

if标签

判断属性是否为null或者为空字符
多属性判断，可以加括号，or, and , == ,!=

choose标签

choose 标签作用是通过条件判断来拼接 SQL 语句，类似于 Java 中的 switch 语句，从上到下，当有匹配的条件时，跳出 choose 语句；如果所有条件都不成立则执行 otherwise 标签中的内容

<choose>
	<when test=条件1>
		满足条件1时执行，并且终止执行choose下面的标签。
	</when>
	<when test=条件2>
		不满足条件1时，但是满足条件2执行，并且终止执行choose下面的标签。
	</when>
	...
	<otherwise>
		顶上所有条件都不满足时，执行。
	</otherwise>
</choose>

<select id="findActiveBlogLike"
 resultType="Blog">
 SELECT * FROM BLOG WHERE state = ‘ACTIVE’
 <choose>
 <when test="title != null">
 AND title like #{title}
 </when>
 <when test="author != null and author.name != null">
 AND author_name like #{author.name}
 </when>
 <otherwise>
 AND featured = 1
 </otherwise>
 </choose>
</select>

where标签

<select id="findActiveBlogLike"
 resultType="Blog">
 SELECT * FROM BLOG
 WHERE
 <if test="state != null">
 state = #{state}
 </if>
</select>

可能会出现下面这个sql

SELECT * FROM BLOG WHERE

正确使用

<select id="findActiveBlogLike" resultType="Blog">
 SELECT * FROM BLOG
 <where>
 <if test="state != null">
 state = #{state}
 </if>
 </where>
</select>

trim标签

<trim prefix="WHERE" prefixOverrides="AND |OR ">
 ...
</trim>

<trim prefix="SET" suffixOverrides=",">
 ...
</trim>

set标签

<update id="updateAuthorIfNecessary">
 update Author
 <set>
 <if test="username != null">username=#{username},</if>
 <if test="password != null">password=#{password},</if>
 </set>
 where id=#{id}
</update>

bind

<select id="selectBlogsLike" resultType="Blog">
 <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
 SELECT * FROM BLOG
 WHERE title LIKE #{pattern}
</select>

数据库函数常用应用

1like模糊查询(CONCAT(str1,str2,...)字符串拼接应用)

 SELECT t_name AS name FROM `t_user` where t_name like '%${name}%' ;
 SELECT t_name AS name FROM `t_user` where t_name like CONCAT('%',#{name},'%');

2 时间

查询时间范围数据

<if test="priceStartDate != null">
    AND create_time  &gt;= CONCAT('${priceStartDate}',' 00:00:00')
</if>
<if test="priceEndDate != null">
    AND  create_time &lt;=  CONCAT('${priceEndDate}',' 23:59:59')
</if>

AND  DATE_FORMAT( create_time, '%Y-%m-%d' )>= DATE_SUB( curdate(), INTERVAL 2 MONTH )

and date_format(CREATE_TIME,'%Y-%m-%d') ='2024-03-11'

2.1FROM_UNIXTIME(时间戳转日期格式)

SELECT id,FROM_UNIXTIME(`create_time`, '%Y-%m-%d %H:%i') FROM `t_user`

2.2时间转字符串

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') as formatted_datetime;

2.3TIMESTAMPDIFF(求出时间差)

SECOND：秒 / MINUTE：分 / HOUR：时 / DAY：天 / MONTH：月 / YEAR：年）

SELECT TIMESTAMPDIFF(SECOND , `create_time` , `create_time`)  FROM `t_user`
 
SELECT TIMESTAMPDIFF(SECOND,'2018-07-01 09:00:00','2018-07-04 12:00:00') FROM `t_user`

3不想使用转义符号

xml中需要转义的时候但是你不想转义的时候使用 (大于，小于等等)

<![CDATA[ sql ]]>

IFNULL(name, '') (判断NULL)

IFNULL(name, ''), name为null就会给''

COALESCE(value,...) (加强版判断NULL)

使用场景：查询时判断字段是否为空，如果字段为空则取从第二个参数开始不为空的值，字段不为空则取表中数据

特点：后面可以无限加参数，跟IFNULL（）差不多，但是比他强大一些

替换
REPLACE(str,from_str,to_str)

CASE when

枚举字段可以使用

group_concat

是将分组中括号里对应的字符串进行连接.

能去重，能根据字段排序展示，能换分隔符

这个写的不错MySQL group_concat()详解_奋斗的龙猫的博客-CSDN博客_group_concat()

合并查询出来的数据（union，union all）

union 相同数据则去重，（是所有展示的字段相同则算为一条数据，而不是数据中存的数据）

union all 展示全部不会去重

MYSQL将两张表的数据合并显示_周董思密达的博客-CSDN博客_mysql两表数据合并

SUBSTRING 截取字符

例如：ABCDE

SUBSTRING(A.NAME, 1, 3)
结果：ABC

SUBSTRING(A.NAME,  3)
结果：DE

字段排序

1null值排序

1. 将NULL强制放在最前

　 IF(ISNULL(字段名),0,1) ASC  // ASC可以省略

2. 将null强制放在最后

    IF(ISNULL(字段名),0,1) DESC   
    IF(ISNULL(字段名),1,0)  ASC  // ASC可以省略

3按照字段大小排序，并且null值排在最后

    num字段 是大于等于0的数
   IF(ISNULL(num),-1,num) DESC 
  原因，num是null就赋值成-1,所以完成排序大小

3.1将null放在最前
实现代码： select * from user order by name asc nulls first;

3.2 将null放在最后
实现代码： select * from user order by nname desc nulls last;



组合排序 name前4个字排序，时间倒叙
 order by SUBSTRING(A.NAME, 1, 4) asc nulls last, A.TIME desc nulls last

https://blog.csdn.net/weixin_32467749/article/details/116343533