Mysql和Mybatis常用语句

0 高阶特性

c. mybatis的插件（链）（InterceptorChain）

org.apache.ibatis.plugin.InterceptorChain

Mybatis提供了可供拦截和定制化的钩子，一些三方插件，比如mybatis plus、pagehelper都是借此插件，借此插件，还可以打印sql执行时间、限制sql语句的长度,等等，可以玩出花来

b.如何在mybatis.xml中使用定制的动态sql语句

mybatis.xml的sql标签支持 OGNL语法,可 扩展原生的 = != and等简单操作符
<if test="@com.yourpackage.MyBatisUtils@matches(code, '.*aaaaaa.*')">

a. 如何从mysql table中读取到sql模板(动态sql)用来生成可执行sql

mybatis的sql provider 提供了扩展钩子可以实现
核心的代码片段:

@Mapper
public interface PersonMapper {
    @SelectProvider(type = SqlProvider.class, method = "selectPersonSql")
    List<Person> selectPerson(@Param("firstName") String firstName, @Param("lastName") String lastName, @Param("extraCondition") String extraCondition);

    class SqlProvider {
        private static SqlProviderService sqlProviderService;
        @Autowired
        public void setSqlProviderService(SqlProviderService sqlProviderService) {
            SqlProvider.sqlProviderService = sqlProviderService;
        }
        public static String selectPersonSql(@Param("firstName") String firstName, @Param("lastName") String lastName, @Param("extraCondition") String extraCondition) {
            // Fetch the SQL template from the service
            String sqlTemplate = sqlProviderService.getSql("selectPersonSql", extraCondition);
           // Return the modified SQL template
            return sqlTemplate;
        }
    }
}

1 MyBatisExceptionTranslator

可学习的方法命名,这个接口是用来 将mybatis 抛出的异常,统一封装成spring的DataAccessException的

2 mybatis 的 aggressiveLazyLoading lazyLoadingEnabled

TODO:细节

3 mysql字符集和校对规则

假如有这么一个建表语句：

`remark` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '备注'

• CHARACTER SET utf8mb4 ： 表示字符集是utf8mb4
• COLLATE utf8mb4_bin :表示校对规则是 utf8mb4_bin (区分大小写），utf8mb4_general_ci (不区分大小写)

4 _parameter

mybatis 有 2个内置参数, 其中一个是 _databaseId,基本没啥用; 另一个 _parameter代表整个参数.

对于单个参数：_parameter就是这个参数
对于多个参数：参数会被封装为一个map, _parameter就是代表这个map

<!-- List<Employee> getEmployees(Employee employee); -->
      <select id="getEmployees" resultType="com.hand.mybatis.bean.Employee">
          SELECT * FROM emp
          <if test="_parameter!=null">      _parameter相当于传入的参数employee,判断employee是否为空，若不为空则执行where条件
           where ename=#{_parameter.eName}  
          </if>
      </select>

5 MySQL如何批量修改行?

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT UNIQUE,
    name VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL(10, 2),
    version INT DEFAULT 1,
    UNIQUE KEY (employee_id)
);

INSERT INTO employees (employee_id, name, department, salary, version)
VALUES (101, 'John Doe', 'Engineering', 50000.00, 1)
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    department = VALUES(department),
    salary = VALUES(salary),
    version = version + 1;

注意这里的版本号，最后一行，并不是`version = values(version) + 1;` ，而是
`version = version + 1;` 这是对当前行的`version`值 + 1。

 VALUES (101, 'John Doe', 'Engineering', 50000.00, 1)  中`version`=1 的只作为插入成功后`version`的初始值，可将其改为任意整数。
 
这有一个 副作用，如果某行已经存在，那么affected rows总是2；只有在新增成功时才是 1；这可能对需要根
据 affected rows做业务判断造成不便

或者:

UPDATE table
SET column2 = (CASE column1 WHEN 1 THEN 'val1'
                 WHEN 2 THEN 'val2'
                 WHEN 3 THEN 'val3'
         END)
WHERE column1 IN(1, 2 ,3);

参考

6 How to INSERT If Row Does Not Exist (UPSERT) in MySQL

• INSERT IGNORE
• REPLACE INTO
• INSERT… ON DUPLICATE KEY UPDATE

一定要注意: ‘REPLACE INTO’ 和 ‘INSERT… ON DUPLICATE KEY UPDATE’ 是不同的!!!
前者: 如果当前存在, 就删除它; 然后重新插入 , 所以 affect rows = 2
后者: 如果当前存在,就更新它, affect rows = 1

参考链接

7 如何 “存在就更新,不存在就插入”

CRUD代码有一个很常见的场景: 先查次数据库看数据是否存在, 存在则更新; 不存在就插入. 代码需要二次数据库交互, 不过使用下面语句其实可以一步到位

insert into my_table(id, content, create_time) 
        values (#{id,jdbcType=BIGINT},
        #{content,jdbcType=VARCHAR},
        #{createTime,jdbcType=TIMESTAMP}
        )
on duplicate key update content = VALUES(content)

8 如何一次性通统计多行数据

SELECT COUNT(1) AS total,
  SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS status,
  SUM(CASE WHEN state = 1 THEN 1 ELSE 0 END) AS state
FROM test_table
WHERE _id = #{id,jdbcType=BIGINT}

9 mapper-insert (snippet)

-- * 简单插入一条数据（使用自增）

<insert id="insertAuthor" useGeneratedKeys="true"
    keyProperty="id">
  insert into Author (username,password,email,bio)
  values (#{username},#{password},#{email},#{bio})
</insert>

-- * 批量插入数据（使用自增）
<insert id="batchInsert" useGeneratedKeys="true"
        keyProperty="id">
    insert into  (column1, column2, column3, column4) values
    <foreach item="item" collection="list" separator=",">
        (#{item.}, #{item.}, #{item.}, #{item.})
    </foreach>
</insert>

-- * 插入（若DB不支持自增 或 自定义随机主键的）

-- 多数情况下， order="AFTER"更合适
<insert id="insertAuthor">
  <selectKey keyProperty="id" resultType="int" order="BEFORE">
    select CAST(RANDOM()*1000000 as INTEGER) a from SYSIBM.SYSDUMMY1
  </selectKey>
  insert into Author
    (id, username, password, email,bio, favourite_section)
  values
    (#{id}, #{username}, #{password}, #{email}, #{bio}, #{favouriteSection,jdbcType=VARCHAR})
</insert>

10 mapper-select(snippet)

select
<include refid="Base_Column_List"/>
from XXXXX
where env_id = #{envId,jdbcType=BIGINT} and is_deleted = 0
and XXXX in
<foreach collection="XXXXX" index="index" item="item"
         separator="," open="(" close=")">
   #{item}
</foreach>


<if test="xxxx != null and xxxx.size()>0 ">
    and xxxxxx in
    <foreach collection="xxxx" index="index" item="item"
             separator="," open="(" close=")">
        #{item}
    </foreach>
</if>

11 foreach(snippet)

<if test="query.idSet != null and query.idSet.size()>0">
	<foreach collection="query.idSet" item="item" open=" and xxxxx.id not in (" separator="," close=")">
		#{item}
	</foreach>
</if>