java 多数据源

多数据源

方式一：原生 JDBC

// 1、引入依赖
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>sqljdbc4</artifactId>
    <version>4.0</version>
</dependency>

// 2、配置文件（需要引用相对应得数据库驱动依赖）
@Configuration
public class DataBaseConfig {

    public static final String JDBC_BEAN_PROJECT = "ProjectJdbcTemplate";
    private static final String jdbcUrl = "jdbc:sqlserver://*.*.*.*:1433;DatabaseName=*";
    private static final String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    private static final String userName = "userName";
    private static final String password = "password";

    @Bean(name = JDBC_BEAN_PROJECT)
    public JdbcTemplate Jdbc() {
        DruidDataSource source = new DruidDataSource();
        source.setUrl(jdbcUrl);
        source.setUsername(userName);
        source.setPassword(password);
        source.setDriverClassName(driver);
        return new JdbcTemplate(source);
    }
}

// 3、在使用业务类中 引入Bean
@Resource(name = DataBaseConfig.JDBC_BEAN_PROJECT)
private JdbcTemplate jdbcTemplate;

// 4、业务使用
private List<ProjectInfo> findProjectListSlave() {
    String sql = "SELECT 项目编号 as number, 工程名称 as name, 地址 as address, 项目负责人 as manager, "
        + "CONVERT(bigint, DATEDIFF(S,'1970-01-01 00:00:00',开工日期 ) - 8 * 3600) *1000 as start_time, "
        + "CONVERT(bigint, DATEDIFF(S,'1970-01-01 00:00:00',完工日期 ) - 8 * 3600) *1000 as end_time ,工程概况 as description "
        + "FROM [dbo].[21项目信息]";
    List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
    if (CollectionUtils.isEmpty(list)) {
        return Collections.emptyList();
    }
    return JSONArray.parseArray(JSON.toJSONString(list), ProjectInfo.class);
}

方式二：AOP方式-动态数据源

// 1、引入依赖
 <!--动态数据源-->
 <dependency>
     <groupId>com.baomidou</groupId>
     <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
     <version>3.3.2</version>
 </dependency>
     
// 2、配置文件 配置多个数据源信息
spring.datasource.dynamic.datasource.master.username=username
spring.datasource.dynamic.datasource.master.password=password
spring.datasource.dynamic.datasource.master.url=jdbc:postgresql://127.0.0.1:5432/data1
spring.datasource.dynamic.datasource.master.driver-class-name=org.postgresql.Driver

spring.datasource.dynamic.datasource.slave1.username=username
spring.datasource.dynamic.datasource.slave1.password=password
spring.datasource.dynamic.datasource.slave1.url=jdbc:postgresql://127.0.0.1:5432/data1
spring.datasource.dynamic.datasource.slave1.driver-class-name=org.postgresql.Driver

// 3、在使用方法中添加注解 （默认用主数据源）
@DS("slave1")
public void deleteById(Integer id, Integer type) {
    entityDao.deleteData(id, type);
}
  

其他（EntityManager 的使用）

// 1、引入 EntityManager 
@PersistenceContext
private EntityManager entityManager;

// 2、使用（添加事务 @Transactional）
    @Transactional
    public void savePipePointToTable(String tableName, List<PipePoint> data){
        // 获取表名称
        String name = findTableName(tableName, Constants.MATCH_ONE);
        // 插入数据
        StringBuilder stringBuilder = new StringBuilder();
        String sql = getPipePointSql(data);
        stringBuilder.append("INSERT INTO ").append(name).append("(\"modelid\", \"expno\", \"geom\") VALUES ")
                .append(" ").append(sql);
        Query query = entityManager.createNativeQuery(stringBuilder.toString());
        query.executeUpdate();
        // 修改geom 空间数据
        String geomSql = "update %s set geom = ST_GeomFromText(geoms, 4490),geoms='' where geom is null" ;
        Query query1 = entityManager.createNativeQuery(String.format(geomSql, name));
        query1.executeUpdate();
        // 删除数据
/*        String deleteSql = "delete from %s " ;
        Query query2 = entityManager.createNativeQuery(String.format(deleteSql, name));
        query2.executeUpdate();*/
    }