SpringBoot多数据源和定时任务@Scheduled

今天有一个紧急需求,需要定时从Oracle数据库中传输数据到SqlServer数据库中,之前没有做过类似的功能,记录一下完成过程。

我的设想是:因为时间比较紧急,所以准备通过Spring Boot @Scheduled 完成定时任务,Jpa配置双数据源,写几个Service。

一、项目配置

首先新建一个Spring Boot项目,在Maven中引入必需的几个依赖。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
<groupId>com.lai</groupId>
<artifactId>项目名</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>war</packaging>

<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/com.oracle/ojdbc6 -->
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.3</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<!-- 移除tomcat插件 -->
<exclusions>
<exclusion>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-tomcat</artifactId>
</exclusion>
</exclusions>
</dependency>
<!--本地启动-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-tomcat</artifactId>
<scope>provided</scope>
</dependency>
<!--外置tomcat仍需的依赖-->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>javax.xml.bind</groupId>
<artifactId>jaxb-api</artifactId>
<version>2.2.11</version>
</dependency>
<dependency>
<groupId>com.sun.xml.bind</groupId>
<artifactId>jaxb-core</artifactId>
<version>2.2.11</version>
</dependency>
<dependency>
<groupId>com.sun.xml.bind</groupId>
<artifactId>jaxb-impl</artifactId>
<version>2.2.11</version>
</dependency>
</dependencies>

<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-war-plugin</artifactId>
<configuration>
<warName>项目名</warName>
</configuration>
</plugin>
</plugins>
</build>

项目架构如下:

二、多数据源

  1. 配置application.properties
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#database
#SqlServer
spring.datasource.test2.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.test2.jdbc-url=jdbc:sqlserver://XXXX.XXXX.XXXX.XXXX;DatabaseName=XXXX
spring.datasource.test2.username=sa
spring.datasource.test2.password=XXXX

#Oracle
spring.datasource.test1.driver-class-name = oracle.jdbc.driver.OracleDriver
spring.datasource.test1.jdbc-url =jdbc:oracle:thin:@ XXXX.XXXX.XXXX.XXXX:1521/SERVICE_NAME
# 此处若为SERVICE_NAME /SERVICE_NAME
# 若为SID :SID
spring.datasource.test1.username: XXXX
spring.datasource.test1.password: XXXX

#控制台显示真实SQL
spring.jpa.show-sql = true
#hibernate实体类自动维护数据库表结构,update:启动时根据实体类生成表,类和表的更改会同步,validate:启动时验证实体类和表是否一致
spring.jpa.hibernate.ddl-auto=none
#解决路径问题
server.servlet.context-path=/项目名/
#解决Tomcat多个项目数据源导致其他项目无法访问
spring.jmx.default-domain = 项目名
  1. 实现装载配置类,配置数据源

数据源配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
package com.lai.reportrefresh.config;

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 javax.sql.DataSource;

/**
* @program: reportrefresh
* @description: 装载配置类
* @author: XXXX
* @create: 2018-08-31
*/
@Configuration
public class DataSourceConfig {
/**
* @return oracle数据库
*/
@Bean(name = "test1DataSource")
@Qualifier(value = "test1DataSource") //spring装配bean的唯一标识
@ConfigurationProperties(prefix = "spring.datasource.test1") //application.properties配置文件中该数据源的配置前缀
public DataSource test1DataSource(){
return DataSourceBuilder.create().build();
}

/**
* @return sqlserver数据库
*/
@Primary //配置该数据源为主数据源
@Bean(name = "test2DataSource")
@Qualifier(value = "test2DataSource")
@ConfigurationProperties(prefix = "spring.datasource.test2")
public DataSource test2DataSource(){
return DataSourceBuilder.create().build();
}
}

Oracle数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
package com.lai.reportrefresh.config.datasource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Map;

/**
* @program: reportrefresh
* @description: oracle数据库数据源声明装载
* @author: XXX
* @create: 2018-08-31
*/

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactoryTest1", //EntityManagerFactory引用
transactionManagerRef = "transactionManagerTest1", //transactionManager引用
basePackages = {"com.lai.reportrefresh.test1"}) //设置 test1DataSource应用到的包
public class test1DataSourceConfig {
/**
* 注入数据源
*/
@Autowired()
@Qualifier("test1DataSource")
private DataSource test1DataSource;

/**
* 注入JPA配置实体
*/
@Autowired
private JpaProperties jpaProperties;

/**
* 通过调用JPA配置实体中的解析方法,解析datasource中各属性的值
* @return 本数据源中各参数
* 这些和不同类型数据库密切相关的属性设置,不能设置在application.properties中,所以需要再不同的数据源中具体设置,赋值给JpaProperties
*/
private Map<String,Object> getVendorProperties(){
return jpaProperties.getHibernateProperties(new HibernateSettings());
}

/**
* 配置EntityManagerFactory实体
* @param builder
* @return 实体管理工厂
* packages 扫描@Entity注释的软件包名称
* persistenceUnit 持久性单元的名称。 如果只建立一个EntityManagerFactory,你可以省略这个,但是如果在同一个应用程序中有多个,你应该给它们不同的名字
* properties 标准JPA或供应商特定配置的通用属性。 这些属性覆盖构造函数中提供的任何值。
*
*/
@Primary
@Bean(name = "entityManagerFactoryTest1")
public LocalContainerEntityManagerFactoryBean entityManagerFactoryTest1(EntityManagerFactoryBuilder builder){
return builder
.dataSource(test1DataSource)
.properties(getVendorProperties())
.packages("com.lai.reportrefresh.test1")
.persistenceUnit("test1PersistenceUnit")
.build();
}

/**
* 配置EntityManager实体
* @param builder
* @return 实体管理器
*/
@Primary
@Bean(name = "entityManagerTest1")
public EntityManager entityManager(EntityManagerFactoryBuilder builder){
return entityManagerFactoryTest1(builder).getObject().createEntityManager();
}

/**
* 配置事务transactionManager
* @param builder
* @return 事务管理器
*/
@Primary
@Bean(name = "transactionManagerTest1")
public PlatformTransactionManager transactionManagerTest1(EntityManagerFactoryBuilder builder){
return new JpaTransactionManager(entityManagerFactoryTest1(builder).getObject());
}

}

SqlServer数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
package com.lai.reportrefresh.config.datasource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.Database;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
* @program: reportrefresh
* @description: sql server数据库中数据源的 声明装载类
* @author: XXX
* @create: 2018-08-31
*/
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactoryTest2", //实体管理引用
transactionManagerRef = "transactionManagerTest2", //事务管理引用
basePackages = {"com.lai.reportrefresh.test2"}) //设置 test2DataSource应用到的包
public class test2DataSourceConfig {
/**
* 注入数据源
*/
@Autowired()
@Qualifier("test2DataSource")
private DataSource test2DataSource;

/**
* 注入JPA配置实体
*/
@Autowired
private JpaProperties jpaProperties;

/**
* 通过调用JPA配置实体中的解析方法,解析datasource中各属性的值
* @return 本数据源中各参数
* 这些和不同类型数据库密切相关的属性设置,不能设置在application.properties中,所以需要再不同的数据源中具体设置,赋值给JpaProperties
*/
private Map<String, Object> getVendorProperties() {
return jpaProperties.getHibernateProperties(new HibernateSettings());
}

/**
* 配置EntityManagerFactory实体
*
* @param builder
* @return
* packages 扫描@Entity注释的软件包名称
* persistenceUnit 持久性单元的名称。 如果只建立一个EntityManagerFactory,你可以省略这个,但是如果在同一个应用程序中有多个,你应该给它们不同的名字
* properties 标准JPA或供应商特定配置的通用属性。 这些属性覆盖构造函数中提供的任何值。
*/
@Bean(name = "entityManagerFactoryTest2")
public LocalContainerEntityManagerFactoryBean entityManagerFactoryTest2(EntityManagerFactoryBuilder builder) {
return builder
.dataSource(test2DataSource)
.properties(getVendorProperties())
.packages("com.lai.reportrefresh.test2")
.persistenceUnit("test2PersistenceUnit")
.build();
}

/**
* 配置EntityManager实体
*
* @param builder
* @return 实体管理器
*/
@Bean(name = "entityManagerTest2")
public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
return entityManagerFactoryTest2(builder).getObject().createEntityManager();
}


/**
* 配置事务
*
* @param builder
* @return 事务管理器
*/
@Bean(name = "transactionManagerTest2")
public PlatformTransactionManager transactionManagerTest2(EntityManagerFactoryBuilder builder) {
return new JpaTransactionManager(entityManagerFactoryTest2(builder).getObject());
}
}
  1. 根据服务需求编写实体类,以及对应的Service,Oracle数据库主要是查询服务,SqlServer则是更新服务,要注意更新服务声明事务时要加value参数,否则会报告异常
    1
    2
    3
    4
    @Transactional(value = "transactionManagerTest2")
    public void update(……){
    ……
    }

    补充

    Jpa对Oracle查询分页时(Page+Pageable)失效,生成的SQL查询不是Oracle支持的分页查询语句。

对比SqlServer生成select top,Oracle不支持TOP通常用order by和rownum来代替。

所以需要配置Oracle对应的hibernate.dialect,要为不同的数据源配置不同的hibernate.dialect。

修改配置:

1
2
3
4
5
6
7
8
9
10
private Map<String,String> getVendorProperties(){
jpaProperties.setDatabase(Database.ORACLE);
Map<String,String> map = new HashMap<>();
map.put("hibernate.dialect","org.hibernate.dialect.Oracle10gDialect");
map.put("hibernate.hbm2ddl.auto","none");
map.put("hibernate.show.sql", "true");
map.put("hibernate.physical_naming_strategy","org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl");
jpaProperties.setProperties(map);
return jpaProperties.getProperties();
}

三、定时任务

  1. 在Application类加@EnableScheduling注释

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    @SpringBootApplication
    @EnableScheduling
    @ServletComponentScan
    public class ReportrefreshApplication extends SpringBootServletInitializer {
    /**
    * 实现SpringBootServletInitializer可以让spring-boot项目在web容器中运行
    */
    @Override
    protected SpringApplicationBuilder configure(SpringApplicationBuilder builder) {
    builder.sources(this.getClass());
    return super.configure(builder);
    }
    public static void main(String[] args) {
    SpringApplication.run(ReportrefreshApplication.class, args);
    }
    }
  2. 编写定时服务,每分钟更新一次

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
@Component
@EnableScheduling
public class ScheduleService {
private Logger logger = Logger.getLogger("ScheduleService");
private ReportService reportService;
private ReportIntoService reportIntoService;

@Autowired
public ScheduleService(ReportService reportService,
ReportIntoService reportIntoService){
this.reportService = reportService;
this.reportIntoService = reportIntoService;
}

@Scheduled(cron = "0 0/1 * * * ?")
public void autoTask(){
try{
System.out.println("执行定时任务");
System.out.println("当前时间:" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
//查询Oracle数据库已报到的数据

//查询SqlServer数据库中已报到的数据

//筛选出SqlServer需要更新的数据,减少更新数目

logger.info("Oracle已报道人数:" +);
logger.info("SqlServer已报道人数:" +);
logger.info("需要更新人数:" +);
reportIntoService.batchInto(results);
logger.info("---新生预报到更新完毕---");

//查询其他一些数据,更新

logger.info("宿舍分配办理完成的学生数: " +);
logger.info("现场缴费办理完成的学生数: " +);
logger.info("绿色通道办理完成的学生数: " +);
logger.info("打印报道单办理完成的学生数: " +);
reportIntoService.update();
logger.info("---办理人数更新完毕---");
}catch (Exception ex){
ex.printStackTrace();
logger.warning(ex.getMessage());
}
}
}

测试后,数据正确,任务完成。