java代码实现数据源切换(连接池简单粗暴)-白红宇

java代码实现数据源切换(连接池简单粗暴)

阅读量：4553 次

发布时间：2019-06-08

本文共 4547 字，大约阅读时间需要 15 分钟。

--最佳实践配置（.yml）

gs.oracle:   #driverClassName: oracle.jdbc.driver.OracleDriver   driverClassName: oracle.jdbc.OracleDriver   username: myuser   password: root   url: jdbc:oracle:thin:@127.0.0.1:1521:orcl

--最佳实践（注意在springboot的.yml中配置gs.oracle,若使用gs_oracle,@@ConfigurationProperties会提示使用规范配置）

package com.dhht.config; import lombok.Data; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.stereotype.Component; /**  * @Author: sh  * @Description: oracleDataCfg 前置数据库配置类  * @Date: 9:26 2019/7/23  */ @Component @ConfigurationProperties(prefix = "gs.oracle") @Data public class OrcaleDataCfg {
        private String driverClassName;     private String username;     private String password;     private String url; }

--最佳实践DbConnect

package com.dhht.wechat.util; import com.alibaba.druid.pool.DruidDataSource; import com.dhht.config.OrcaleDataCfg; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Component; import javax.annotation.Resource; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.Date; import java.util.List; import java.util.Map; /**  * @Author: sh  * @Description: DbConnect  * @Date: 23:01 2019/7/22  */ @Component public class DbConnect {
         @Resource     OrcaleDataCfg orcaleDataCfg;     private static DruidDataSource dataSource = null;     private static JdbcTemplate jdbcTemplate = null;     /**      * 构造函数完成数据库的连接和连接对象的生成      *      * @throws Exception      */     private DbConnect() {
         }     public synchronized DruidDataSource GetDbConnect() throws Exception {
             try {
                 if (dataSource == null) {
                     dataSource = new DruidDataSource();                 //设置连接参数                 dataSource.setUrl(orcaleDataCfg.getUrl());                 dataSource.setDriverClassName(orcaleDataCfg.getDriverClassName());                 dataSource.setUsername(orcaleDataCfg.getUsername());                 dataSource.setPassword(orcaleDataCfg.getPassword());                 //配置初始化大小、最小、最大                 dataSource.setInitialSize(5);                 dataSource.setMinIdle(5);                 dataSource.setMaxActive(5);                 //连接泄漏监测                 dataSource.setRemoveAbandoned(true);                 dataSource.setRemoveAbandonedTimeout(30);                 //配置获取连接等待超时的时间                 dataSource.setMaxWait(20000);                 //配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒                 dataSource.setTimeBetweenEvictionRunsMillis(20000);                 //防止过期，Oracle必须添加 FROM DUAL!                 dataSource.setValidationQuery("SELECT 'x' FROM DUAL");                 dataSource.setTestWhileIdle(true);                 dataSource.setTestOnBorrow(true);                 return dataSource;             }         } catch (Exception e) {
                 return null;         }         return null;     }     /**      * 取得已经构造生成的数据库连接      *      * @return 返回数据库连接对象      * @throws Exception      */     public Connection getConnect() throws Exception {
             Connection con = null;         try {
                 GetDbConnect();             con = dataSource.getConnection();         } catch (Exception e) {
                 throw e;         }         return con;     }     public synchronized JdbcTemplate getJdbcTemplate() throws  Exception{
             DruidDataSource druidDataSource = GetDbConnect();         if(jdbcTemplate==null){
                 jdbcTemplate = new JdbcTemplate();             jdbcTemplate.setDataSource(druidDataSource);         }         return jdbcTemplate;     }     public static void main(String[] args) throws Exception {
             DbConnect dbConnect = new DbConnect();         Connection connection = dbConnect.getConnect();         String sql = "SELECT * from DZHY_GAYZ_KZLOG";         PreparedStatement ps = connection.prepareStatement(sql);         ResultSet resultSet = ps.executeQuery();         if (resultSet.next()) {
                 System.out.println(resultSet.getInt(1));         }         JdbcTemplate jdbcTemplate = new JdbcTemplate();         jdbcTemplate.setDataSource(dataSource);         List
     
      > list = jdbcTemplate.queryForList(sql);         String SQL = "insert into DZHY_GAYZ_KZLOG (id,uniscid,entname,startdate,enddate,kz_uniscid,kz_entname) values (?,?,?,?,?,?,?)";         jdbcTemplate.update(SQL,DateUtil.get32UUIDMilli(),"333","需要刻章33",new Date(),new Date(),"33-33","刻章店33");     } }