本示例通过JdbcTemplate访问SQLite和MySQL数据库,对于多数据源的支持这个是很重要的一个功能。
目录
Maven依赖配置
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.boonya</groupId>
<artifactId>sb-sqlite</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>sb-sqlite</name>
<description>Demo project for Spring Boot</description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.4.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
<mybatis-version>1.3.2</mybatis-version>
<commons-lang3>3.4</commons-lang3>
<druid-version>1.1.9</druid-version>
<pagehelper-version>1.2.5</pagehelper-version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>${mybatis-version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>${commons-lang3}</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-core</artifactId>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.datatype</groupId>
<artifactId>jackson-datatype-joda</artifactId>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.module</groupId>
<artifactId>jackson-module-parameter-names</artifactId>
</dependency>
<!--sqlite-->
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>3.21.0.1</version>
</dependency>
<!--jdbc -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!--mysql-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- 分页插件 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>${pagehelper-version}</version>
</dependency>
<!-- alibaba的druid数据库连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>${druid-version}</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>5.0.8.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>5.0.8.RELEASE</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
项目资源配置
application.yml
server:
port: 8888
spring:
profiles:
active:
datasource:
name: mixtured-dbs
sqlite:
driver-class-name: org.sqlite.JDBC
username: sa
password: sa
jdbcurl: jdbc:sqlite:C:/Users/admin/Desktop/springboot/sb-sqlite/src/main/db/15843911112_zmn15843911112.db
mysql:
jdbcurl: jdbc:mysql://192.168.1.111:3306/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
username: root
password: root
driver-class-name: com.mysql.jdbc.Driver
数据源配置
DataSourceConfig.java
package com.boonya.sbsqlite.config;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
@Configuration
public class DataSourceConfig {
private static Logger logger = LoggerFactory.getLogger(DataSourceConfig.class);
@Bean(name = "SQLiteDataSource")
@Qualifier("SQLiteDataSource")
@ConfigurationProperties(prefix="spring.datasource.sqlite")
public DataSource SQLiteDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "MySQLDataSource")
@Qualifier("MySQLDataSource")
@ConfigurationProperties(prefix="spring.datasource.mysql")
@Primary
public DataSource MySQLDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "sqliteJdbcTemplate")
public JdbcTemplate sqliteJdbcTemplate(@Qualifier("SQLiteDataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
@Bean(name = "mysqlJdbcTemplate")
public JdbcTemplate mysqlJdbcTemplate(@Qualifier("MySQLDataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}
接口实现示例
以下包含两个接口定义:
- XhHlyService.java ------MySQL
- XhNoticeService.java ----SQLite
package com.boonya.sbsqlite.service;
import java.util.List;
import java.util.Map;
public interface XhHlyService {
public Map<String,Object> select(long id);
/**
* 查询业务类型主表记录
* @param number
* @return
*/
public Map<String,Object> select(String number) ;
/**
* 查询业务类型子表记录
* @param number
* @param dwCode
* @return
*/
public Map<String,Object> select(String number,String dwCode);
/**
* 查询所有重复业务类型数据(需要处理的数据原始表)
* @return
*/
public List< Map<String,Object>> selectAll() ;
/**
* 查询所有重复业务类型数据(需要处理的数据中间表)
* @return
*/
public List<Map<String,Object>> selectRepairList();
/**
* 是否手机号已处理
* @return
*/
public boolean hasHandledNumber(String number);
/**
* 更新序列自增表记录
* @return
*/
public boolean updateSequenceHlyId(String newHlyId) throws Exception;
/**
* 更新映射表记录
* @return
*/
public boolean updateRepairHlyId(String number,String newHlyId) throws Exception;
/**
* 更新业务类型主表记录
* @return
*/
public boolean updateHlyZbId(String number,String newHlyId) throws Exception;
/**
* 更新业务类型分表记录
* @return
*/
public boolean updateHlyFbId(String number,String dwCode,String newHlyId) throws Exception;
/**
* 记录已处理的手机号防止二次处理
* @return
*/
public boolean saveHandledNumber(String number) throws Exception;
/**
* 修复业务类型重复ID(更新排序重复--保留一半处理一半)
* @return
*/
public Map<String,Object> repairRepeatIds();
}
package com.boonya.sbsqlite.service;
import java.util.List;
import java.util.Map;
public interface XhNoticeService {
public Map<String,Object> select(long id);
public List< Map<String,Object>> selectAll() ;
}
XhHlyService实现:
package com.boonya.sbsqlite.service.impl;
import com.boonya.sbsqlite.config.DataSourceConfig;
import com.boonya.sbsqlite.service.XhHlyService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;
@Service
public class XhHlyServiceImpl implements XhHlyService {
private static Logger logger = LoggerFactory.getLogger(DataSourceConfig.class);
@Autowired
@Qualifier("mysqlJdbcTemplate")
private JdbcTemplate jdbcTemplate;
public Map<String,Object> select(long id) {
String sql="SELECT * FROM XH_HLY_TB_ZB WHERE OBJECTID="+id;
return jdbcTemplate.queryForList(sql).get(0);
}
public Map<String,Object> select(String number) {
String sql="SELECT * FROM XH_HLY_TB_ZB WHERE USER_TEL='"+number+"'";
return jdbcTemplate.queryForList(sql).get(0);
}
public Map<String,Object> select(String number,String dwCode) {
String sql="SELECT * FROM XH_HLY_TB_"+dwCode+" WHERE USER_TEL='"+number+"'";
return jdbcTemplate.queryForList(sql).get(0);
}
public List<Map<String,Object>> selectAll() {
String sql="select m.* from xh_hly_tb_zb m inner join ("
+"SELECT a.HLY_ID FROM"
+" ("
+" select HLY_ID,count(HLY_ID) NUMBER from xh_hly_tb_zb group by HLY_ID order by NUMBER DESC"
+" ) a WHERE a.NUMBER>1 "
+") n "
+"on m.hly_id=n.hly_ID ORDER BY m.HLY_ID ASC";
return jdbcTemplate.queryForList(sql);
}
public List<Map<String,Object>> selectRepairList() {
String sql="SELECT * FROM XH_DATA_REPAIR ORDER BY HLY_ID ASC";
return jdbcTemplate.queryForList(sql);
}
public boolean hasHandledNumber(String number){
String sql="SELECT * FROM XH_DATA_NUMBER WHERE number='"+number+"'";
List<Map<String,Object>> result= jdbcTemplate.queryForList(sql);
if(null==result||result.size()==0){
return false;
}
return true;
}
public long getCurrentHlyId(){
String sql="SELECT current_val FROM fs_sequence WHERE seq_name='XH_HLY_TB_ZB'";
return Long.valueOf(jdbcTemplate.queryForList(sql).get(0).get("current_val").toString());
}
@Transactional(rollbackFor =Exception.class )
public boolean updateSequenceHlyId(String newHlyId) throws Exception{
String sql="UPDATE fs_sequence SET current_val="+newHlyId+" WHERE seq_name='XH_HLY_TB_ZB'";
try{
jdbcTemplate.execute(sql);
return true;
}catch (Exception e){
e.printStackTrace();
throw e;
}
}
@Transactional(rollbackFor =Exception.class )
public boolean updateRepairHlyId(String number,String newHlyId) throws Exception{
String sql="UPDATE XH_DATA_REPAIR SET HLY_ID_NEW='"+newHlyId+"' WHERE USER_TEL='"+number+"'";
try{
jdbcTemplate.execute(sql);
return true;
}catch (Exception e){
e.printStackTrace();
throw e;
}
}
@Transactional(rollbackFor =Exception.class )
public boolean updateHlyZbId(String number,String newHlyId) throws Exception{
String sql="UPDATE XH_HLY_TB_ZB SET HLY_ID='"+newHlyId+"' WHERE USER_TEL='"+number+"'";
try{
jdbcTemplate.execute(sql);
return true;
}catch (Exception e){
e.printStackTrace();
throw e;
}
}
@Transactional(rollbackFor =Exception.class )
public boolean updateHlyFbId(String number,String dwCode,String newHlyId) throws Exception{
String sql="UPDATE XH_HLY_TB_"+dwCode+" SET HLY_ID='"+newHlyId+"' WHERE USER_TEL='"+number+"'";
try{
jdbcTemplate.execute(sql);
return true;
}catch (Exception e){
e.printStackTrace();
throw e;
}
}
@Transactional(rollbackFor =Exception.class )
public boolean saveHandledNumber(String number) throws Exception{
String sql="INSERT INTO XH_DATA_NUMBER(NUMBER) VALUES('"+number+"')";
try{
jdbcTemplate.execute(sql);
return true;
}catch (Exception e){
e.printStackTrace();
throw e;
}
}
@Transactional(rollbackFor =Exception.class )
public boolean updateAllData(String number){
try{
// 是否含有分表
boolean hasFb=false;
String dwCode="";
Map<String,Object> zbItem=this.select(number);
if(null!=zbItem.get("DW_CODE")&&!"".equals(zbItem.get("DW_CODE").toString().trim())){
hasFb=true;
dwCode=zbItem.get("DW_CODE").toString();
}
// 获取更新的ID值
long newHlyId= this.getCurrentHlyId()+1;
logger.info("最新巡护员ID={}",newHlyId);
// 更新自增序列
this.updateSequenceHlyId(newHlyId+"");
// 更新映射表记录
this.updateRepairHlyId(number,newHlyId+"");
// 更新主表记录
this.updateHlyZbId(number,newHlyId+"");
// 更新分表记录
if(hasFb){
this.updateHlyFbId(number,dwCode,newHlyId+"");
}
// 保存手机号处理记录
this.saveHandledNumber(number);
logger.info("{}处理成功ID={}",number,newHlyId);
return true;
}catch (Exception e){
e.printStackTrace();
return false;
}
}
@Override
public Map<String,Object> repairRepeatIds() {
Map<String,Object> map=new HashMap<String,Object>();
List<String> numbers=new ArrayList<String>();
// 获取所有重复记录
List<Map<String,Object>> list= this.selectRepairList();
for (Map<String,Object> m:list ) {
if(!map.keySet().contains(m.get("HLY_ID").toString())){
map.put(m.get("HLY_ID").toString(),m.get("HLY_ID").toString());
}else{
numbers.add(m.get("USER_TEL").toString());
}
}
AtomicInteger success=new AtomicInteger(0);
AtomicInteger real=new AtomicInteger(0);
AtomicInteger failed=new AtomicInteger(0);
// 更新ID编码
if(null!=numbers&&numbers.size()>0){
for (String number:numbers) {
boolean handled=this.hasHandledNumber(number);
if(handled){
success.incrementAndGet();
}else{
boolean flag=this.updateAllData(number);
if(flag){
real.incrementAndGet();
}else{
failed.incrementAndGet();
}
}
}
}
Map<String,Object> result=new HashMap<String,Object>();
result.put("update_numbers",numbers);
result.put("success",success);
result.put("real",real);
result.put("failed",failed);
return result;
}
}
XhNoticeService实现:
package com.boonya.sbsqlite.service.impl;
import com.boonya.sbsqlite.service.XhNoticeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Map;
@Service
public class XhNoticeServiceImpl implements XhNoticeService {
@Autowired
@Qualifier("sqliteJdbcTemplate")
private JdbcTemplate jdbcTemplate;
public Map<String,Object> select(long id) {
String sql="SELECT * FROM TABLE_NOTICE WHERE id="+id;
return jdbcTemplate.queryForList(sql).get(0);
}
public List<Map<String,Object>> selectAll() {
String sql="SELECT * FROM TABLE_NOTICE";
return jdbcTemplate.queryForList(sql);
}
}