多数据源
方式一:原生 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();*/
}