中间库方式同步数据
在 Spring 的 IoC 容器中配置一个 JdbcTemplate 的 bean,将 DataSource 注入进来,然后再把JdbcTemplate 注入到自定义DAO 中。
需求:将档案、部门基础数据写入中间库,以档案为例
-
trigger
查看所有trigger,查看trigger创建语句
1
2
|
show
create
TRIGGER
trg_staffinfo_delete
select
*
from
information_schema.`TRIGGERS`
|
在创建、修改和删除档案信息时,往同步状态表中加入数据,trigger创建语句如下:
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
|
#档案创建
CREATE
TRIGGER
`trg_staffinfo_insert`
AFTER
INSERT
ON
`t_per_staffinfo`
FOR
EACH ROW
BEGIN
INSERT
INTO
t_att_userorg_sync_mark(id,relateID,relateName,relateType,synchro,syncType,domain,createtime)
VALUES
(UUID(),new.staffId,new.staffName,1,
'N'
,1,
'C6'
,NOW());
END
#档案更新
CREATE
TRIGGER
`trg_staffinfo_update`
AFTER
UPDATE
ON
`t_per_staffinfo`
FOR
EACH ROW
BEGIN
if
new.staffId !=old.staffId
OR
new.staffName !=old.staffName
OR
new.sex !=old.sex
OR
new.recruitsDate !=old.recruitsDate
OR
new.personType !=old.personType
OR
new.leaveDate !=old.leaveDate
OR
new.techLevel !=old.techLevel
OR
new.postName !=old.postName
OR
new.postDirection !=old.postDirection
OR
new.cengjiPCode !=old.cengjiPCode
OR
new.deptCode !=old.deptCode
OR
new.deptName !=old.deptName
OR
new.staffStatus !=old.staffStatus
OR
new.officeAddress !=old.officeAddress
OR
new.sfcb !=old.sfcb
THEN
INSERT
INTO
t_att_userorg_sync_mark(id,relateID,relateName,relateType,synchro,syncType,domain,createtime)
VALUES
(UUID(),old.staffId,old.staffName,1,
'N'
,2,
'C6'
,NOW());
END
IF;
END
#档案删除
CREATE
TRIGGER
`trg_staffinfo_delete`
AFTER
DELETE
ON
`t_per_staffinfo`
FOR
EACH ROW
BEGIN
INSERT
INTO
t_att_userorg_sync_mark(id,relateID,relateName,relateType,synchro,syncType,domain,createtime)
VALUES
(UUID(),old.staffId,old.staffName,1,
'N'
,3,
'C6'
,NOW());
END
|
定时任务——采用easyscheduleJava代码(以添加为例)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
public
int
addHrEmp(
final
HrEmpPo t) {
return
jdbcTemplate.update(
"INSERT INTO HR_EMP(C_CODE,C_NAME,C_GENDER,C_IDCARD,C_HIREDATE,C_EMPLOYEESTATUS,LIZHIDATE,C_JOBNAME,JIBIE,C_UNITCODE,C_UNITNAME,LEIBIE,SynchFlagID,OfficeAdd,c_isputmoney,c_isputeffDate)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"
,
new
PreparedStatementSetter(){
@Override
public
void
setValues(PreparedStatement ps)
throws
SQLException {
ps.setString(
1
, t.getCcode());
ps.setString(
2
, t.getCname());
ps.setString(
3
, t.getCgender());
ps.setString(
4
, t.getIdcard());
ps.setString(
5
, t.getChiredate());
ps.setString(
6
, t.getCemployeestatus());
ps.setString(
7
, t.getLizhidate());
ps.setString(
8
, t.getCjobname());
ps.setString(
9
, t.getJibie());
ps.setString(
10
, t.getCunitcode());
ps.setString(
11
, t.getCunitname());
ps.setString(
12
, t.getLeibie());
ps.setString(
13
, t.getSynchflagID());
ps.setString(
14
, t.getOfficeAddress());
ps.setString(
15
, t.getC_isputmoney());
ps.setDate(
16
,
new
Date(t.getC_isputeffDate().getTime()));
}
});
}
|
spring-jdbc配置文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
<
context:property-placeholder
location
=
"classpath:jdbc.properties"
ignore-unresolvable
=
"true"
/>
<
bean
id
=
"hrEmpDao"
class
=
"com.fx.oa.module.att.attendance.server.dao.impl.HrEmpDao"
>
<
property
name
=
"jdbcTemplate"
ref
=
"jdbcTemplateC6"
/>
</
bean
>
<
bean
id
=
"jdbcTemplateC6"
class
=
"org.springframework.jdbc.core.JdbcTemplate"
>
<
property
name
=
"dataSource"
ref
=
"dataSourceC6"
/>
</
bean
>
<!-- C6交互中间库数据源 -->
<
bean
id
=
"dataSourceC6"
class
=
"com.mchange.v2.c3p0.ComboPooledDataSource"
>
<
property
name
=
"driverClass"
value
=
"${c6.jdbc.driverClassName}"
/>
<
property
name
=
"jdbcUrl"
value
=
"${c6.jdbc.url}"
/>
<
property
name
=
"user"
value
=
"${c6.jdbc.username}"
/>
<
property
name
=
"password"
value
=
"${c6.jdbc.password}"
/>
<
property
name
=
"autoCommitOnClose"
value
=
"true"
/>
<
property
name
=
"checkoutTimeout"
value
=
"${hpool.checkoutTimeout}"
/>
<
property
name
=
"initialPoolSize"
value
=
"${hpool.minPoolSize}"
/>
<
property
name
=
"minPoolSize"
value
=
"${hpool.minPoolSize}"
/>
<
property
name
=
"maxPoolSize"
value
=
"${hpool.maxPoolSize}"
/>
<
property
name
=
"maxIdleTime"
value
=
"${hpool.maxIdleTime}"
/>
<
property
name
=
"acquireIncrement"
value
=
"${hpool.acquireIncrement}"
/>
<
property
name
=
"maxIdleTimeExcessConnections"
value
=
"${hpool.maxIdleTimeExcessConnections}"
/>
</
bean
>
|
本文转自 gaochaojs 51CTO博客,原文链接:http://blog.51cto.com/jncumter/1851199,如需转载请自行联系原作者