jdbc批量更新_微服务架构下，利用sharding-jdbc做Mysql高可用读写分离踩坑笔记

欢迎关注我的头条号：Wooola，10年Java软件开发及架构设计经验，专注于Java、Go语言、微服务架构，致力于每天分享原创文章、快乐编码和开源技术。

Mysql高可用读写分离架构技术栈

1. mha + keeplived+mysql (Mysql一主两从：Master-Salve)
2. Sharding-jdbc
3. mybatisplus
4. Springboot
5. SpringCloud

Springboot程序配置

yml文件之数据库连接池配置

采用sharding-jdbc配置Springboot程序读写分离：主库(写：mvip)和从库(svip：读)

Druid配置类

采用阿里巴巴Druid连接池管理数据库连接

采坑之不支持批量更新

异常信息

nested exception is java.sql.SQLException: sql injection violation, multi-statement not allow : update

原因分析

Druid默认不支持批量sql语句执行，经源码走查，Druid的multiStatementAllow默认是false，需要开启，设置成true

批量Sql更新语句

 update `cms_article` top_sort = #{item.topSort}cover_sort = #{item.coverSort}recommend_sort = #{item.recommendSort} where id = #{item.id} 

解决方案

1、把Druid的multiStatementAllow设置成true

2、数据库url连接字符串加上 allowMultiQueries=true

jdbc:mysql://${ha.basedb.mvip.ip}:${ha.basedb.mvip.port}/testdb?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false

采坑之数据库ssl警告

Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification

解决方案

数据库url连接字符串加上 allowMultiQueries=false

采坑之mybatis的update子句select子句写法

原更新sql写法：

 UPDATE sys_dept SET del_flag = #{status} WHERE dept_id in