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

痛定思痛。 2021-11-26 13:32 250阅读 0赞

本示例通过JdbcTemplate访问SQLite和MySQL数据库,对于多数据源的支持这个是很重要的一个功能。

**目录**

Maven依赖配置

项目资源配置

数据源配置

接口实现示例

--------------------

## 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/> 
        </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>
            
            <dependency>
                <groupId>org.xerial</groupId>
                <artifactId>sqlite-jdbc</artifactId>
                <version>3.21.0.1</version>
            </dependency>
            
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>
            
            <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>
            
            <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);
        }
    
    
    }