java 进程 同步_JAVA开发的一个同步程序

package com.palic.elis.tjs.ceis.dao;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import tjs.plugins.jdbc.DBOperateFactory;

import com.beetle.framework.log.SysLogger;

public class SyncVipToLcchCeisCustomerContactTmp {

SysLogger logger = SysLogger.getInstance("JobRunLog");

// 中心库

private static final String ELIS_DATA_SOURCE = "ELIS_DB";

private static final String VIP_DATA_SOURCE = "VIP_DB";

// JOB名字

private static final String PRAISE_JOB_NAME = "SyncVipToLcchCeisCustomerContactTmpJob";// TJS同步数据到CEIS临时表

private static final String SQL_SWITCH_QUERY = "SELECT TO_CHAR(CONTROL_DATE,'yyyy-mm-dd') LAST_SYNC,to_char(sysdate,'yyyy-mm-dd') TODAY, CONTROL_CHAR SWITCH_FLAG FROM ceis_control WHERE control_id = 'SyncVipToLcchCeisCustomerContactTmpJob'";

private static final String SQL_SWITCH_UPDATE = "UPDATE ceis_control SET CONTROL_DATE =SYSDATE WHERE control_id = 'SyncVipToLcchCeisCustomerContactTmpJob'";

// VIP系统同步数据到CEIS临时表查询语句

private static final String SQL_QUERY_Userdefined_LIST = "select  Contact_Type_Id,"

+ "Contact_Id,"

+ "Channel_Id,"

+ "Date_Start,"

+ "'02' motive_type,"

+ "SYSTEM_ID,"

+ "Motive_Result_Id,"

+ "Activity_Id,"

+ "Activity_Type,"

+ "Url_Content,"

+ "Match_Flag,"

+ "Mem_Name,"

+ "Sex,"

+ "to_char(Date_Birthday,'yyyy-mm-dd') Date_Birthday,"

+ "ID_Type,"

+ "ID_No," + "Row_id" + " from  " + " order_info_to_lcch_temp";

// 根据五项信息查询客户号信息

private static final String SQL_QUERY_UserClientno_LIST = "SELECT CLIENTNO FROM ecif_client_info WHERE id_type = decode(ltrim(rtrim(?)),'***','1','护照','2','军人证','3','少儿证','4','异常***','5','港澳台证件','6','户口本','0','转换不详','9','出生证','A','9')"

+ " AND ((id_type = '1' AND elis_ecif_package.func_is_idno_equal(idno,  ltrim(rtrim(?))) = 'Y') OR (id_type <> '1' AND upper(idno) = upper( ltrim(rtrim(?))))) "

+ " AND client_name =  ltrim(rtrim(?)) "

+ "AND trunc(birth_date, 'DD') = trunc(to_date(?,'yyyy-mm-dd'), 'DD') "

+ "AND sex = decode(ltrim(rtrim(?)),'男','M','女','F','9') and rownum=1 ";

// 插入到CEIS的CEIS_CUSTOMER_CONTACT_TMP表

private static final String SQL_INSERT_KIT_INFO_LIST = "insert into ceis_customer_contact_new_tmp (module_code,contact_type_id,contact_id,channel_id,date_start,client_no ,motive_type ,system_id ,motive_result_id,activity_id ,activity_type  ,url_content , match_flag) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";

// 更新VIP系统同步标志

private static final String SQL_VIP_UPDATE = "update order_info_to_lcch_temp set is_synchronized=?, CREATED=SYSDATE, CREATED_BY=USER ,LAST_UPD=SYSDATE,  LAST_UPD_BY=USER ";

// 维护ceis_activity_table基表

private static final String SQL_Query_PROD_INFO_TO_LCCH_TEMP = "select trim(row_id) row_id, trim(a.prod_code)prod_code ,trim(a.prod_name)prod_name from PROD_INFO_TO_LCCH_TEMP a  where a.is_synchronized='01'";

// 查询基表是否有

private static final String SQL_Query_Ceis_Activity_Table_CNT = "select  count(*)cnt from ceis_activity_table a where a.activity_type=?";

// 插入基表

private static final String SQL_Insert_Ceis_Activity_Table = "insert into ceis_activity_table( activity_type,module_code,description) values(?,?,?)";

// 更新VIP产品表的同步标志

private static final String SQL_UPDATE_PROD_INFO_TO_LCCH_TEMP = "update  prod_info_to_lcch_temp a set a.is_synchronized=?,a.created=sysdate,a.created_by=user,a.last_upd=sysdate,a.last_upd_by=user";

// 记录日志

private static final String SQL_TJS_CEIS_TRACE_LOG = "insert into  ceis_trace(ctrlno,trace_info,trace_type)values('SyncVipToLcchCeisCustomerContactTmpJob',?,'-1')";

private static final String SQL_TJS_MONITOR_INS = "insert into elis_tjs_jobs_monitor(job_name,execute_date,source,target,source_count,target_count) values(?,sysdate,?,?,?,?)";

private final Connection connElis;

private final Connection connVip;

private final int commitCnt = 10;

public SyncVipToLcchCeisCustomerContactTmp() {

connElis = DBOperateFactory.getConncetion(ELIS_DATA_SOURCE);

connVip = DBOperateFactory.getConncetion(VIP_DATA_SOURCE);

}

// 获取开关

public Map getJobSwitch() {

try {

return queryForMap(connElis, SQL_SWITCH_QUERY);

} catch (Exception ex) {

logger.info("getJobSwitch ", ex);

}

return new HashMap();

}

// 更新开关日期

public final int updateJobSwitch() {

try {

return updateSql(connElis, SQL_SWITCH_UPDATE);

} catch (SQLException ex) {

logger.info("updateJobSwitch", ex);

}

return 0;

}

// 运行

@SuppressWarnings({ "rawtypes" })

public void run() {

try {

// 查询开关日期，防止重复同步

Map switchMap = getJobSwitch();

String last = (String) switchMap.get("LAST_SYNC");

String today = (String) switchMap.get("TODAY");

if (last == null || last.compareTo(today) < 0) {

// VIP同步数据到CEIS临时表语句

syncDepraiseData(last);

// 更新开关日期

updateJobSwitch();

}

} catch (Exception e) {

e.printStackTrace();

}

}

// VIP同步数据到CEIS临时表语句

public void syncDepraiseData(String day) throws SQLException {

int cnt = 0;

int count = 0;

StringBuffer st = new StringBuffer();

ResultSet rtype = null;

ResultSet qcnt = null;

String pcode = null;

String Rowid = null;

PreparedStatement quertype = null;

PreparedStatement querycnt = null;

PreparedStatement insertable = null;

PreparedStatement update = null;

List CommitProdList = new ArrayList();

// 维护基表

logger.info("------维护基表开始-----");

try {

// 查询VIP系统没有同步的业务类型

quertype = connVip

.prepareStatement(SQL_Query_PROD_INFO_TO_LCCH_TEMP);

// 查询基表是否已经存在

querycnt = connElis

.prepareStatement(SQL_Query_Ceis_Activity_Table_CNT);

// 如果没有则插入到基表

insertable = connElis

.prepareStatement(SQL_Insert_Ceis_Activity_Table);

// 更新VIP产品表的同步标志

update = connVip

.prepareStatement(SQL_UPDATE_PROD_INFO_TO_LCCH_TEMP);

// 查詢產品表

rtype = quertype.executeQuery();

/*

* 基表维护 1、首先取出VIP系统的产品表同步标志为没有同步的数据。

* 2、根据不同的VIP增值服务类型给产品编码拼接前缀查询ceis_activity_table的业务类型如果没有需要插入进去。

* 3、每插入一条到ceis_activity_table表就需要更新VIP系统的产品表同步标志。 4、如果報錯記錄日誌。

* VIP增值服务申请/预约= VIPSV001(需要和产品组合) VIP增值服务订单取消= VIPSV002(需要和产品组合)

* VIP增值服务订单咨询= VIPSV003(不需要和产品组合) VIP增值服务投诉= VIPSV004(不需要和产品组合)

* VIP增值服务使用= VIPSV005(需要和产品组合)

*/

while (rtype.next()) {

logger.info("source Row_id " + rtype.getString("Row_id"));

String[] a = { "VIPSV001_", "VIPSV002_", "VIPSV005_" };

String[] b = { "增值服务申请/预约_", "VIP增值服务订单取消_", "VIP增值服务使用_" };

CommitProdList.add(rtype.getString("Row_id"));

for (int i = 0; i < a.length; i++) {

pcode = null;

String description = null;

pcode = a[i] + rtype.getString("prod_code");

description = b[i] + rtype.getString("prod_name");

querycnt.setString(1, pcode);

qcnt = querycnt.executeQuery();

while (qcnt.next()) {

if (qcnt.getString("cnt").equals("0")) {

insertable.setString(1, null);

insertable.setString(2, "30");

insertable.setString(3, description);

insertable.execute();

}

}

}

CommitProdList.clear();

}

} catch (Exception e) {

logger.info("VIP系统同步到CEIS基表数据报:" + e.getMessage());

for (String rowid : CommitProdList) {

insert_ceis_trace_Log("VIP系统同步到CEIS基表数据报错主键为:"

+ rowid+ "错误信息为:"

+ e.getMessage());

}

CommitProdList.clear();

} finally {

// 关闭连接

try {

if (quertype != null) {

quertype.executeBatch();

quertype.close();

}

if (querycnt != null) {

querycnt.executeBatch();

querycnt.close();

}

if (insertable != null) {

insertable.executeBatch();

insertable.close();

}

if (update != null) {

update.setString(1, "02");

update.execute();

update.close();

}

} catch (SQLException e) {

e.printStackTrace();

logger.info("关闭链接异常" + e.getMessage());

}

}

logger.info("------维护基表结束-----");

/*

* 同步VIP数据到CEIS临时表 1、首先是JOB开关打开、查询VIP系统是否有没有同步的数据。

* 2、逐条处理，根据VIP系统的五项信息查询查询出客户号。 3、查询出来的信息插入到CEIS临时表。 4、更新VIP系统的同步标志。

* 5、关闭连接、记录日志。

*/

try {

// 查询VIP未同步的数据

String sql = SQL_QUERY_Userdefined_LIST;

if (day != null && !"".equals(day)) {

sql = SQL_QUERY_Userdefined_LIST + " where  created> to_date('"

+ day + "','yyyy-mm-dd')and Is_Synchronized='01'";

}

logger.info("VIP系统同步到CEIS临时表查询SQL语句：" + sql);

PreparedStatement psql = connVip.prepareStatement(sql);

// 插入到CEIS的CEIS_CUSTOMER_CONTACT_TMP表

PreparedStatement isql = connElis

.prepareStatement(SQL_INSERT_KIT_INFO_LIST);

// 根据五项信息查询客户号信息

PreparedStatement qclientno = connElis

.prepareStatement(SQL_QUERY_UserClientno_LIST);

// 记录错误日志

PreparedStatement ceislog = connElis

.prepareStatement(SQL_TJS_CEIS_TRACE_LOG);

// 更新VIP系统同步标志

PreparedStatement vipsign = connVip

.prepareStatement(SQL_VIP_UPDATE);

ResultSet rs = null;

ResultSet scup = null;

ResultSet rno = null;

String clientno = null;

List commitDataList = new ArrayList();

try {

psql.setFetchSize(1000);

isql.setFetchSize(1000);

// 查询出VIP系统的同步数据

rs = psql.executeQuery();

ResultSetMetaData rsmt = rs.getMetaData();

// 循环逐条处理

while (rs.next()) {

cnt++;

try {

clientno = null;

// 根据五项信息定位唯一客户

qclientno.setString(1, rs.getString("ID_TYPE"));

qclientno.setString(2, rs.getString("ID_NO"));

qclientno.setString(3, rs.getString("ID_NO"));

qclientno.setString(4, rs.getString("MEM_NAME"));

qclientno.setString(5, rs.getString("DATE_BIRTHDAY"));

qclientno.setString(6, rs.getString("SEX"));

System.out.println("source ID_TYPE "

+ rs.getString("ID_TYPE") + " IDNO "

+ rs.getString("ID_NO") + " CLIENT_NAME "

+ rs.getString("MEM_NAME") + " DATE_BIRTHDAY "

+ rs.getString("DATE_BIRTHDAY") + " SEX "

+ rs.getString("SEX"));

// 查询客户号

rno = qclientno.executeQuery();

rno.next();

if (rno.getRow() == 0) {

logger.info("以上五项信息没有取到客户号");

ceislog.setString(1,

"主键Row_id为" + rs.getString("Row_id")

+ "根据五项信息没有查到对应的客户号");

vipsign.setString(1, "2");

vipsign.executeUpdate();

ceislog.executeUpdate();

} else if (rno.getRow() == 1) {

count++;

// 取到一个客户号，同步数据

clientno = rno.getString("CLIENTNO");

logger.info("clientno取出来的客户号：" + clientno);

commitDataList.add(rs.getString("Row_id"));

isql.setString((1), "30");

isql.setString((2), rs.getString("contact_type_id"));

isql.setString((3), rs.getString("contact_id"));

isql.setString((4), rs.getString("channel_id"));

isql.setTimestamp((5),

rs.getTimestamp("date_start"));

isql.setString(6, clientno);

isql.setString((7), rs.getString("motive_type"));

isql.setString((8), rs.getString("system_id"));

isql.setString((9),

rs.getString("motive_result_id"));

isql.setString((10), rs.getString("activity_id"));

isql.setString((11), rs.getString("activity_type"));

isql.setString((12), rs.getString("url_content"));

isql.setString((13), rs.getString("match_flag"));

// 成功后更新VIP表的同步状态

vipsign.setString(1, "02");

isql.addBatch();

vipsign.addBatch();

}

if (cnt % commitCnt == 0) {// 每1000条提交

isql.executeBatch();

// vipsign.executeBatch();

if (ceislog != null)

ceislog.executeBatch();

commitDataList.clear();

}

} catch (Exception e) {

if (vipsign != null) {

vipsign.close();

}

connElis.rollback();

connVip.rollback();

vipsign = connVip.prepareStatement(SQL_VIP_UPDATE);

for (String rowid : commitDataList) {

insert_ceis_trace_Log("VIP系统同步到CEIS临时表数据报错主键为:"

+ rowid + "错误信息为" + e.getMessage());

count = count - 1;

}

commitDataList.clear();

}

}

// 执行最后一次批量

if (cnt % commitCnt != 0) {

try {

if (isql != null) {

isql.executeBatch();

}

if (vipsign != null) {

vipsign.executeBatch();

}

if (ceislog != null) {

ceislog.executeBatch();

}

commitDataList.clear();

} catch (Exception e) {

if (vipsign != null)

vipsign.close();

connElis.rollback();

vipsign = connVip.prepareStatement(SQL_VIP_UPDATE);

for (String rowid : commitDataList) {

logger.info("VIP系统同步到CEIS临时表数据报错主键为:" + rowid

+ "错误信息为" + e.getMessage());

insert_ceis_trace_Log("VIP系统同步到CEIS临时表数据报错主键为:"

+ rowid + "错误信息为" + e.getMessage());

count = count - 1;

}

commitDataList.clear();

}

}

} catch (Exception e) {

logger.info("VIP系统同步到CEIS临时表数据报错:" + e.getMessage());

} finally {

if (psql != null) {

psql.close();

}

if (isql != null) {

isql.close();

}

if (qclientno != null) {

qclientno.close();

}

if (vipsign != null) {

vipsign.close();

}

if (ceislog != null) {

ceislog.close();

}

}

} catch (Exception e) {

count = (cnt - commitCnt) < 0 ? 0 : (cnt - commitCnt);

logger.info("VIP系统同步到CEIS临时表数据报错" + e.getMessage());

insert_ceis_trace_Log(e.getMessage());

} finally {

insertTjsLog(PRAISE_JOB_NAME, String.valueOf(cnt),

String.valueOf(count));

}

}

// 插入到TJS对账日志表

public void insertTjsLog(String jobName, String sourceCnt, String targetCnt) {

PreparedStatement psql;

try {

psql = connElis.prepareStatement(SQL_TJS_MONITOR_INS);

psql.setString(1, jobName);

psql.setString(2, VIP_DATA_SOURCE);

psql.setString(3, ELIS_DATA_SOURCE);

psql.setString(4, sourceCnt);

psql.setString(5, targetCnt);

psql.executeUpdate();

} catch (SQLException e) {

logger.info("记录TJS日志报错" + e);

e.printStackTrace();

}

}

// 关闭数据库连接

public void close() {

try {

if (connElis != null) {

connElis.close();

}

if (connVip != null) {

connVip.close();

}

} catch (SQLException e) {

logger.error("关闭数据库连接失败", e);

}

}

// 插入到ceis_trace

public void insert_ceis_trace_Log(String trace_info) {

PreparedStatement psql;

try {

psql = connElis.prepareStatement(SQL_TJS_CEIS_TRACE_LOG);

psql.setString(1, trace_info);

psql.execute();

} catch (SQLException e) {

logger.info("记录ceis_trace日志报错" + e);

e.printStackTrace();

}

}

private Map queryForMap(final Connection conn,

final String sql) throws SQLException {

List> list = queryForList(conn, sql);

return list.isEmpty() ? new HashMap() : list.get(0);

}

private int updateSql(final Connection conn, final String sql)

throws SQLException {

PreparedStatement psql = conn.prepareStatement(sql);

return psql.executeUpdate();

}

private List> queryForList(final Connection conn,

final String sql) throws SQLException {

List> resultList = new ArrayList>();

PreparedStatement psql = conn.prepareStatement(sql);

Map dataRow = null;

ResultSet rs = null;

psql.setFetchSize(1000);

rs = psql.executeQuery();

ResultSetMetaData rsmt = rs.getMetaData();

while (rs.next()) {

dataRow = new HashMap();

for (int i = 1; i <= rsmt.getColumnCount(); i++) {

if ("java.lang.String".equals(rsmt.getColumnClassName(i))) {

dataRow.put(rsmt.getColumnLabel(i), rs.getString(i));

} else if ("java.sql.Timestamp".equals(rsmt

.getColumnClassName(i))) {

dataRow.put(rsmt.getColumnLabel(i), rs.getTimestamp(i));

} else if ("java.math.BigDecimal".equals(rsmt

.getColumnClassName(i))) {

dataRow.put(rsmt.getColumnLabel(i), rs.getBigDecimal(i));

} else {

throw new SQLException("Unsupport column type");

}

}

resultList.add(dataRow);

}

return resultList;

}

}