SpringBoot JdbcTemplate多数据源配置应用管理

boonya

已于 2024-09-06 15:25:47 修改

阅读量1.3k

点赞数 1

分类专栏： # SpringBoot 文章标签： SpringBoot JdbcTemplate 多数据源配置 MySQL SQLite

于 2019-07-24 09:20:13 首次发布

本文链接：https://blog.csdn.net/boonya/article/details/97100641

版权

SpringBoot 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

本示例通过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);
    }


}