MyBatis 常用语法

文章目录


原作者: 包包gitee 地址.
https://gitee.com/bao-chaofei/learning-bi-ji/blob/master/Mybatis常用语法.md#choose--when--otherwise

Mybatis/Mysql常用语法

常用CRUD

<select id="select" resultType="cn.bao.domain.User">
    select <include refid="column"/>
    from sys_user a
    <where>
<!-- 使用test时，如果变量不是字符类型，不能加xxx != '' -->
		<if test="status != null">
        	status = #{status}
        </if>
		<if test="loginName != null and loginName != ''">
        	a.del_flag = 0 and a.login_name = #{loginName}
        </if>
        a.del_flag = 0
<!-- 存在or条件，如果是同一个条件需要用()包裹 -->
        and ((a.password = #{password} and a.login_name = #{name}) or (a.password = #{password} and a.email = #{name}))
    </where>
</select>

<update id="update" parameterType="cn.bao.domain.Images">
    update images
    <set>
        <if test="id != null">
        	id = #{id},
        </if>
        <if test="url != null and url != ''">
        	url = #{url},
        </if>
        <if test="createDate != null">
        	create_date = #{createDate}
        </if>
    </set>
    where id = #{id}
</update>

<!-- 批量更新 -->
<update id="updateStudentList">
	<foreach collection="studentEntitys" item="item" index="index" open="" close="" separator=";">
		update t_student
			<set>
				<if test="item.classesId!='' and item.classesId!=null">
					classes_id=${item.classesId}
				</if>
				<if test="item.code!='' and item.code!=null">
                	code=${item.code}
                </if>
                <if test="item.roomId!='' and item.roomId!=null">
                	room_id=${item.roomId}
                </if>
            </set>
            where id = ${item.id}
	</foreach>
</update>
<!-- 使用useGeneratedKeys="true" keyProperty="id" 插入后会将生成的key返回到对象本身，id对应的是主键 -->
<insert id="insert" parameterType="cn.bao.domain.Images" useGeneratedKeys="true" keyProperty="id">
	insert into images(author , url , create_date)
	values (#{author} , #{url} , #{createDate})
</insert>

<!-- 批量插入 -->
<insert id="insertList" parameterType="java.util.List">
	insert into file_md5(md5, path, file_size, status)
	values
	<!-- 使用for循环写sql插入语句 -->
	<foreach collection="fileMD5s" separator="," item="item" index="index">
		(#{item.md5}, #{item.filePath}, #{item.fileSize}, #{item.status})
	</foreach>
</insert>

<delete id="delete" parameterType="cn.bao.commons.entity.FileMD5">
	delete
	from file_md5
	where md5 = #{md5}
</delete>

常用符号

第一种写法：
 
原符号       <        <=      >       >=       &        '        "
替换符号    &lt;    &lt;=   &gt;    &gt;=   &amp;   &apos;  &quot;
例如：sql如下：
create_date_time &gt;= #{startTime} and  create_date_time &lt;= #{endTime}
 
第二种写法：
大于等于
<![CDATA[ >= ]]>
小于等于
<![CDATA[ <= ]]>
例如：sql如下：
create_date_time <![CDATA[ >= ]]> #{startTime} and  create_date_time <![CDATA[ <= ]]> #{endTime}

常用判断

if test

<if test="userName != null and userName !=''">
	and user_name like concat('%',#{userName},'%')
</if>
<if test="userEmail !=null">
	and user_email=#{userEmail}
</if>

choose … when … otherwise

<select id="getEmpByConditionIf" resultType="cn.bao.commons.entity.Employee">
        select * from tbl_employee 
        <where>
            <choose>
                <when test="id!=null">
                    id=#{id}
                </when>
                <when test="lastName!=null">
                    last_name like #{lastName}
                </when>
                <when test="email!=null">
                    email=#{email}
                </when>
                <otherwise>
                    ...
                </otherwise>
            </choose>
        </where>
</select>

<!-- 也可以大嵌套 -->
<select id="getEmpByConditionIf" resultType="cn.bao.commons.entity.Employee">
	<choose>
    	<when test = "boardName != null and boardName != ''">
        	select id,name from table where boardName = #{boardName}
    	</when>
    	<when test = "boardNo != null and boardNo != 0">
        	select id,name from table where boardId = #{boardId}
    	</when>
    	<otherwise>
        	select id,name from table
    	</otherwise>
	</choose>
</select>

case … when … else … end

SELECT
`产品号码`,
`产品名称`,
`产品单价`,
CASE
WHEN `产品单价`<(SELECT AVG(`产品单价`) FROM `产品信息`) THEN 1
WHEN `产品单价`>=2*(SELECT AVG(`产品单价`) FROM `产品信息`) THEN 2
ELSE 3
END AS `顾客级别`
FROM `产品信息`;

if

SELECT
	`id` '用户ID',
	`name` '用户名称',
	IF(state=1,'启用','禁用') '用户状态'
FROM
	`user`

ifnull

-- IFNULL(expr1,expr2)
-- 如果expr1不是NULL，则返回第一个参数（expr1）。 否则，IFNULL函数返回第二个参数。
SELECT `NAME`,IFNULL(sex,'未知') from student;

elt

-- 语法:
-- ELT(N,str1,str2,str3,...)
-- 如果 N = 1，返回 str1，如果N = 2，返回 str2，等等。如果 N 小于 1 或大于参数的数量，返回 NULL。

SELECT username,ELT(sex,'男','女','未知') as sex FROM user

find_in_set

-- 判断逗号分隔的字符串包含某个字符串
FIND_IN_SET('1', '1,2,3')
-- oracle中使用一下语法，意义不同：前者是否包含后者
instr('1,2,3', '1') > 0

常用字符操作

replace

REPLACE(UUID(),'-','')

left

-- left(str,n)从左侧截取str的前n位
left('mysql',2)
-- 结果：my

ascii

-- 获取第一个字符的ASCII码
ascii('ts') --> 116

regexp

-- 正则验证 mysql中正则表达式不区分大小写
-- 判断两个逗号分隔的字符串是否有合集
str1 regexp replace(str2, ',', '|')
-- 是否匹配
SELECT prod_name
	From Products
		Where prod_name REGEXP'.000'

匹配字符类（Posix字符类）

使用的时候需要外面加一层[]，例如[[:digit:]]

类说明
[:alnum:]任意字母和数字（同[a-zA-Z0-9]）
[:alpha:]任意字母（同[a-zA-Z]）
[:blank:]空格和制表（同[\t]）
[:cntrl:]ASCII控制字符（ASCII0到31和127）
[:digit:]任意数字（同[0-9]）
[:graph:]和[[:print:]]相同，但不包含空格
[:lower:]任意小写字母（同[a-z]）
[:print:]任意可打印字符
[:punct:]即不在[[:alnum:]]又不在[[:cntrl:]]中的字符
[:space:]包括空格在内的任意空白字符（同[\f\n\r\t\v]）
[:upper:]任意大写字母（同[A-Z]）
[:xdigit:]任意16进制数字（同[a-fA-F0-9]）

使用

SELECT * FROM `mytable`
Where name REGEXP'name[[:digit:]]'; --> name1 name6

substr

-- 截取字符
substr(str,2,4)

ord

mid

upper lower

-- 将所有字符串更改为大写，然后返回
SELECT UPPER('Hey'); --> HEY
SELECT LOWER('Hey'); --> hey

json操作

注：MariaDB语法有不同，参考10.2

条件查询
SELECT * from dept WHERE json_value->'$.deptLeaderId'='5';
SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and json_value->'$.deptId'='5';
SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and dept='部门3';
多表关联
SELECT * from dept,dept_leader WHERE dept.json_value->'$.deptLeaderId'=dept_leader.json_value->'$.id' ;
函数

JSON_EXTRACT() 从JSON文档返回数据

select id,json_extract(json_value,'$.deptName') as deptName from dept;

JSON_CONTAINS() JSON格式数据是否在字段中包含特定对象

select * from dept WHERE JSON_CONTAINS(json_value, JSON_OBJECT("deptName","部门5"))

JSON_OBJECT() 将一个键值对列表转换成json对象

SELECT * from (
SELECT *,json_value->'$.deptName' as deptName FROM dept
) t WHERE JSON_CONTAINS(deptName,JSON_OBJECT("depp","dd"));

JSON_ARRAY() 创建JSON数组

SELECT * from dept WHERE JSON_CONTAINS(json_value->'$.deptName',JSON_ARRAY("1"))

JSON_TYPE() 查询某个json字段属性类型

SELECT json_value->'$.deptName' ,JSON_TYPE(json_value->'$.deptName') as type from dept

JSON_KEYS() JSON文档中的键数组

SELECT JSON_KEYS(json_value) FROM dept

JSON_SET() 将数据插入JSON格式中，有key则替换，无key则新增

update dept set json_value=JSON_SET('{"deptName": "部门2", "deptId": "2", "deptLeaderId": "4"}','$.deptName','新增的部门1','$.newData','新增的数据') WHERE id=2;

JSON_INSERT() 插入值（往json中插入新值，但不替换已经存在的旧值）

UPDATE dept set json_value=JSON_INSERT('{"a": "1", "b": "2"}', '$.deptName', '新增的部门2','$.newData2','新增的数据2') 
WHERE id=2

JSON_REPLACE() 更新指定key的值

UPDATE dept set json_value=JSON_REPLACE('{"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "新增的数据2"}', '$.newData2', '更新的数据2') WHERE id =2;

JSON_REMOVE() 从JSON文档中删除数据

UPDATE dept set json_value=JSON_REMOVE('{"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "更新的数据2"}','$.a') WHERE id =2;

JSON_SEARCH() 用于在json格式中查询并返回符合条件的节点

SELECT * from dept WHERE JSON_SEARCH(json_value,'','')

resultMap的一对多封装

java对象

public class Dept {
       private Integer deptNo;
       private String dname;
       private String loc;
       private List<Employee> empList;
       // setter和getter方法省略 
}

resultMap

<resultMap id="deptMap" type="dept">
        <id column="dept_deptno" property="deptNo" />
        <result column="dname" property="dname" />
        <result column="loc" property="loc" />
        <!-- collection集合标签中的column属性应该填写select返回字段中来自于一方表的主键对应的字段名 -->
        <collection property="empList" ofType="employee" column="dept_deptno">
            <id column="empno" property="empNo"></id>
        </collection>
    </resultMap>
    
    <!-- 查询当前部门下所有职员信息及当前部门的基本信息 -->
    <select id="deptFindById" resultMap="deptMap">
        select d.deptno as dept_deptno, d.dname, d.loc,
               e.empno, e.ename, e.job, e.sal
          from dept d
          left join emp e
            on d.deptno = e.deptno
         where d.deptno = #{deptno}
    </select>

手动分页

java页码纠正方法

//分页参数页码纠正 -mysql
private void startPage(QualityQuery query) {
    if (query.getPageSize() == null) query.setPageSize(15L);
    if (query.getPageNum() == null) {
        query.setPageNum(0L);
    } else {
        query.setPageNum((query.getPageNum() - 1) * query.getPageSize());
    }
}

//分页结尾范围超出纠正
private void fixPage(QualityQuery query, Long total) {
    Long pageNum = (query.getPageNum() / query.getPageSize()) + 1;
    if (pageNum * query.getPageSize() > total)
        query.setPageNum((total / query.getPageSize()) * query.getPageSize());
}

//设置分页对象
private <T> Page<T> endPage(QualityQuery query, Long total, List<T> list) {
    Page<T> page = new Page<>();
    Long pageNum = query.getPageNum();
    pageNum = pageNum / query.getPageSize() + 1;
    return page.setTotal(total)
            .setSize(query.getPageSize())
            .setCurrent(pageNum)
            .setRecords(list);
}

mysql

-- 需要编写一条查找总条数的sql
<select id="count" resultMap="int">
    select count(1)
        from dept d
          left join emp e
            on d.deptno = e.deptno
         where d.deptno = #{deptno}
</select>
-- 选择列表的sql最后要加上
<select id="deptFindById" resultMap="deptMap">
    select d.deptno as dept_deptno, d.dname, d.loc, e.empno, e.ename, e.job, e.sal
        from dept d
          left join emp e
            on d.deptno = e.deptno
         where d.deptno = #{deptno}
	limit #{pageNum},#{pageSize}
</select>

oracle

select
    *
FROM
    (
        SELECT
            res.*,
            ROWNUM linenum
        FROM
            (
                -- 这里写查询语句
            ) res
        WHERE
            ROWNUM <= '结束行数'
    )
WHERE
    linenum > '起始行数';
版权声明：本文为m0_56814750原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。