JDBCTemplate使用
业务上,手机App(离线状态下的数据)，在在线的时候需要往服务端上传，由于App上的SQLite数据库里的需要 同步数据的表 跟服务端的数据库表结构一致，所以为了同步数据的方便性，我们决定App在进行insert update delete 操作时，将SQL语句（已拼装好参数的sql）
记录到Sqlite内的一张记录表内，结构如下
package com.darkBlue.web.mobile; import java.util.Date; /** * Created by root on 2018/7/5 0005. */ public class OperateLog { private Long id; /** * 操作类型 */ private String type; /** * 拼装好参数的SQL语句 */ private String statement; /** * 拼装好参数的SQL语句 */ private Date operateDate; /** * 操作人 */ private String operateUser; /** * 操作的表名 */ private String tableName; } get set方法省略
这样的话，APP端需要同步数据，只需要查询这张表的数据，将其传到服务端即可，其中两个字段是必须的 即
type 表示，SQL的类型是update 还是delete 还是insert
statement 表示SQL语句
具体如何上传就不细说了，项目使用springMVC
服务端代码：
首先，服务端想使用jdbcTemplate，需要在applicationContext.xml中进行配置
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource" /> </bean>
同时，我们还需要进行事物配置，因为我们需要保证，所有的操作要么全部成功，要么全部失败
<!-- ===============事务控制的配置 ================--> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <!--控制住数据源 --> <property name="dataSource" ref="dataSource"></property> </bean> <!--开启基于注解的事务，使用xml配置形式的事务（必要主要的都是使用配置式） --> <tx:annotation-driven transaction-manager="transactionManager"/> <aop:aspectj-autoproxy proxy-target-class="true"/> <aop:config> <!-- 切入点表达式 --> <aop:pointcut expression="execution(* com.demo.service..*(..))" id="txPoint"/> <!-- 配置事务增强 --> <aop:advisor advice-ref="txAdvice" pointcut-ref="txPoint"/> </aop:config> <!--配置事务增强，事务如何切入 --> <tx:advice id="txAdvice" transaction-manager="transactionManager"> <tx:attributes> <!-- 所有方法都是事务方法 --> <tx:method name="*"/> <!--以get开始的所有方法 --> <tx:method name="get*" read-only="true"/> </tx:attributes> </tx:advice>
看看上传代码：
package com.demo.web.mobile; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Controller; import org.springframework.transaction.PlatformTransactionManager; import org.springframework.transaction.TransactionDefinition; import org.springframework.transaction.TransactionStatus; import org.springframework.transaction.support.DefaultTransactionDefinition; import org.springframework.web.bind.annotation.RequestBody; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.ResponseBody; import javax.annotation.Resource; import java.sql.Connection; import java.util.ArrayList; import java.util.List; import java.util.regex.Matcher; import java.util.regex.Pattern; import java.util.stream.Collectors; /** * Created by root on 2018/7/5 0005. */ @Controller @RequestMapping("mobile/uploadOfflineData") public class UploadOfflineDataController { private static final Logger logger = LoggerFactory.getLogger(UploadOfflineDataController.class); @Resource private JdbcTemplate jdbcTemplate; @Resource private PlatformTransactionManager transactionManager; private static Connection con = null; @RequestMapping("/uploadDB") @ResponseBody public BaseResponse uploadDB(@RequestBody List<OperateLog> operateLogs) { // 该bean是我自定义的返回Bean BaseResponse ret = new BaseResponse(); List<String> insertSqls = operateLogs.stream().filter(t -> "insert".equals(t.getType()) ).map(OperateLog::getStatement).collect(Collectors.toList()); List<String> updateSqls = operateLogs.stream().filter(t -> "update".equals(t.getType()) ).map(OperateLog::getStatement).collect(Collectors.toList()); List<String> deleteSqls = operateLogs.stream().filter(t -> "delete".equals(t.getType()) ).map(OperateLog::getStatement).collect(Collectors.toList()); // 定义事务隔离级别，传播行为 DefaultTransactionDefinition def = new DefaultTransactionDefinition(); def.setIsolationLevel(TransactionDefinition.ISOLATION_READ_COMMITTED); def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED); // 事务状态类，通过PlatformTransactionManager的getTransaction方法根据事务定义获取；获取事务状态后，Spring根据传播行为来决定如何开启事务 TransactionStatus transactionStatus = transactionManager.getTransaction(def); try { con = jdbcTemplate.getDataSource().getConnection(); // 这里设置是没有效果的[是不能实现事物操作的] // con.setAutoCommit(false); // 执行顺序不能错 execSQL(insertSqls); execSQL(updateSqls); execSQL(deleteSqls); // 提交status中绑定的事务 transactionManager.commit(transactionStatus); ret.setStatus(true); // 这里设置是没有效果的[是不能实现事物操作的] // con.commit(); } catch (Exception e) { try { // 这里设置是没有效果的[是不能实现事物操作的] // con.rollback(); // 提交status中绑定的事务 transactionManager.rollback(transactionStatus); } catch (Exception e1) { logger.error("上传数据,回滚报错，", e1); } logger.error("上传数据,SQL报错，", e); if (e instanceof UploadOfflineDataException) { ret.setData(e); } else { ret.setMsg("未知错误"); } ret.setStatus(false); } finally { try { con.close(); } catch (Exception e) { logger.error("上传数据,关闭链接报错，", e); } } return ret; } public void execSQL(List<String> sqls) throws UploadOfflineDataException { for (String sql : sqls) { try { jdbcTemplate.update(sql); } catch (Exception e) { // 这是我自定义的异常类 UploadOfflineDataException exception = new UploadOfflineDataException(); String tableName = matchSql(sql); String info = matchInfo(sql); if (null != tableName) { if (tableName.equals("t_jzw_fangjian")) { exception.setTableName("房间"); exception.setInfo(info); } else { exception.setTableName("人员"); exception.setInfo(info); } } else { exception.setTableName("未知错误"); } throw exception; } } } /** * 表名获取 * * @param sql lowcase * @return */ public static String matchSql(String sql) { Matcher matcher = null; //SELECT 列名称 FROM 表名称 //SELECT * FROM 表名称 if (sql.startsWith("select")) { matcher = Pattern.compile("select\\s.+from\\s(.+)where\\s(.*)").matcher(sql); if (matcher.find()) { return matcher.group(1); } } //INSERT INTO 表名称 VALUES (值1, 值2,....) //INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....) if (sql.startsWith("insert")) { matcher = Pattern.compile("insert\\sinto\\s(.+)\\(.*\\)\\s.*").matcher(sql); if (matcher.find()) { return matcher.group(1); } } //UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值 if (sql.startsWith("update")) { matcher = Pattern.compile("update\\s(.+)set\\s.*").matcher(sql); if (matcher.find()) { return matcher.group(1); } } //DELETE FROM 表名称 WHERE 列名称 = 值 if (sql.startsWith("delete")) { matcher = Pattern.compile("delete\\sfrom\\s(.+)where\\s(.*)").matcher(sql); if (matcher.find()) { return matcher.group(1); } } return null; } /** * @describe: 错误SQL获取错误的参数 * @params: * @Author: Kanyun * @Date: 2018/7/20 11:31 */ public static String matchInfo(String sql) { String[] infos = sql.split(" "); String regex = "^[\\u4e00-\\u9fa5]*$"; Pattern p = Pattern.compile(regex); List info = new ArrayList(); for (String s : infos) { Matcher m = p.matcher(s); if (m.find()) { info.add(s); } } return info.toString(); } }
我自定义的异常类 [按业务需求定义字段]
public class UploadOfflineDataException extends Exception { private String idCard; private String name; private String addr; private String tableName; private String info; }
我自定义的返回bean[按业务需求定义字段]
public class BaseResponse implements Serializable { private boolean status; private String msg; private Object data; ｝
基础类就已经写完了，主要关注的点是jdbcTemplate的事物控制
虽然Connection 可以设置setAutoCommit(false),但是并不能实现事物控制，
原因是因为：
因为jdbcTemplate.getDataSource().getConnection()获取的connection与每次jdbcTemplate.update用到的connection都是从连接池中获取的，不能保证是一个connection
同时需要注意的是，对于Mysql来说，存储引擎对事物的支持也是不一样的，InnoDB支持事物，MyISM不支持事物
所以我采用了spring的编程式事物[Spring事物分两种，一种是编程式事物，一种是声明式事物]
我这里采用编程式事物，主要是因为我要控制代码块，而编程式事物的优点就是事物的管理是代码块级的，而声明式的是方法级的(虽然可以通过重构方法达到和编程式事物一样的效果)
更多详见：https://blog.csdn.net/zhj870975587/article/details/75152604
