package sample.handler.jdbc;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Properties;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import oracle.goldengate.datasource.*;
import oracle.goldengate.datasource.GGDataSource.Status;
import oracle.goldengate.datasource.meta.*;
public class SimpleJDBCHandler extends AbstractHandler {
private static final Logger logger = LoggerFactory.getLogger(SimpleJDBCHandler.class);
protected ConnectionFactory connFactory;
protected Connection conn;
protected String driver;
protected String url;
protected Properties arguments;
protected PreparedStatement pstmt;
protected String lastOp;
protected int keyIndex;
protected int batchSize = 0;
protected int maxBatchSize = 10000;
protected long reportCount = 0;
protected long lastReportTime;
protected long opCount;
protected SimpleDateFormat dateFormat;
/**
* @param connectionFactory the Class name of connection factory to set
* @throws ClassNotFoundException
* @throws IllegalAccessException
* @throws InstantiationException
*/
public void setConnectionFactory(String connectionFactory) throws InstantiationException, IllegalAccessException, ClassNotFoundException {
connFactory = (ConnectionFactory)Class.forName(connectionFactory).newInstance();
}
// 获取目标端的DB连接
public Connection getConnection() throws Exception {
if (conn==null) {
if (connFactory==null) {
logger.info(new StringBuilder("Connect to [").append(url).append("] via [").append(driver).append("] with arguments: ").append(arguments).toString());
Driver driv = (Driver)Class.forName(driver).newInstance();
conn = driv.connect(url, arguments);
} else {
conn = connFactory.getConnection();
}
conn.setAutoCommit(false);
}
return conn;
}
/* (non-Javadoc)
* 一个事务的开始
*/
@Override
public Status transactionBegin(DsEvent e, DsTransaction tx) {
if (logger.isDebugEnabled())
logger.debug("Method transactionBegin invoked. TxID="+tx.getTranID());
//batchSize = 0;
return super.transactionBegin(e, tx);
}
/* (non-Javadoc)
*一个新的记录操作
*/
@Override
public Status operationAdded(DsEvent e, DsTransaction tx, DsOperation op) {
if (logger.isDebugEnabled())
logger.debug(new StringBuilder("Method operationAdded invoked. TxID=").append(tx.getTranID()).append(" OpNum=").append(tx.getTotalOps()).toString());
Status status = GGDataSource.Status.OK;
if (op.getOperationType().isInsert()) {
status = insertAdded(e, tx, op);
} else if (op.getOperationType().isUpdate()) {
status = updateAdded(e, tx, op);
} else if (op.getOperationType().isDelete()) {
status = deleteAdded(e, tx, op);
}
if (reportCount>=100) {
if (++opCount == reportCount) {
long duration = System.currentTimeMillis() - lastReportTime;
logger.info(new StringBuffer().append(opCount).append(" operations have been processed in last ").append(duration/1000).append(" seconds. Rate=").append(opCount*1000/duration).toString());
opCount = 0;
lastReportTime = System.currentTimeMillis();
}
}
return status;
}
// insert操作
protected Status insertAdded(DsEvent e, DsTransaction tx, DsOperation op) {
try {
String currentOp = op.getTableName()+".INSERT";
if (!currentOp.equals(lastOp)) {
if (batchSize>0 && pstmt!=null) {
executeBatch();
}
String sql = prepareInsertSql(e, tx, op);
System.out.println("insert-sql:"+ sql);
if (logger.isDebugEnabled())
logger.debug("Prepare insert. SQL=["+sql+"]");
pstmt = getConnection().prepareStatement(sql);
lastOp = currentOp;
}
TableMetaData tmeta = e.getMetaData().getTableMetaData(op.getTableName());
for (int i=0; iColumnMetaData cmeta = tmeta.getColumnMetaData(i);
DsColumn col = op.getColumn(i);
bindValue(pstmt, i+1, cmeta, col.getAfter());
}
pstmt.addBatch();
if (++batchSize == maxBatchSize) {
executeBatch();
}
return GGDataSource.Status.OK;
} catch (Exception ex) {
logger.error("Method insertAdded failed.", ex);
return GGDataSource.Status.ABEND;
}
}
// update操作
protected Status updateAdded(DsEvent e, DsTransaction tx, DsOperation op) {
try {
String sql = prepareUpdateSql(e, tx, op);
if (!sql.equals(lastOp)) {
if (batchSize>0 && pstmt!=null) {
executeBatch();
}
if (logger.isDebugEnabled())
logger.debug("Prepare update. SQL=["+sql+"]");
pstmt = getConnection().prepareStatement(sql);
lastOp = sql;
}
System.out.println("update-sql:"+ sql);
TableMetaData tmeta = e.getMetaData().getTableMetaData(op.getTableName());
for (int i=0, j=0, k=keyIndex; iColumnMetaData cmeta = tmeta.getColumnMetaData(i);
DsColumn col = op.getColumn(i);
if (!col.isMissing()) {
bindValue(pstmt, ++j, cmeta, col.getAfter());
}
if (cmeta.isKeyCol()) {
if (col.hasBeforeValue()) {
if (logger.isDebugEnabled())
logger.debug(new StringBuilder(op.getTableName().toString()).append(": Key ").append(cmeta.getColumnName()).append(" is updated. before=[").append(col.getBefore().getValue()).append("], after=[").append(col.getAfter().getValue()).append("]").toString());
bindValue(pstmt, ++k, cmeta, col.getBefore());
} else {
bindValue(pstmt, ++k, cmeta, col.getAfter());
}
}
}
pstmt.addBatch();
if (++batchSize == maxBatchSize) {
executeBatch();
}
return GGDataSource.Status.OK;
} catch (Exception ex) {
logger.error("Method updateAdded failed.", ex);
return GGDataSource.Status.ABEND;
}
}
// delete操作
protected Status deleteAdded(DsEvent e, DsTransaction tx, DsOperation op) {
try {
String currentOp = op.getTableName()+".DELETE";
if (!currentOp.equals(lastOp)) {
if (batchSize>0 && pstmt!=null) {
executeBatch();
}
String sql = prepareDeleteSql(e, tx, op);
System.out.println("delete-sql:"+ sql);
if (logger.isDebugEnabled())
logger.debug("Prepare delete. SQL=["+sql+"]");
pstmt = getConnection().prepareStatement(sql);
lastOp = currentOp;
}
TableMetaData tmeta = e.getMetaData().getTableMetaData(op.getTableName());
for (int i=0, j=0; iColumnMetaData cmeta = tmeta.getColumnMetaData(i);
if (cmeta.isKeyCol()) {
DsColumn col = op.getColumn(i);
bindValue(pstmt, ++j, cmeta, col.getBefore());
}
}
pstmt.addBatch();
if (++batchSize == maxBatchSize) {
executeBatch();
}
return GGDataSource.Status.OK;
} catch (Exception ex) {
logger.error("Method deleteAdded failed.", ex);
return GGDataSource.Status.ABEND;
}
}
// 多个操作批量执行,提升效率
protected int[] executeBatch() throws SQLException {
try {
int[] result = pstmt.executeBatch();
if (logger.isDebugEnabled())
logger.debug(new StringBuilder("Batch executed. Op=[").append(lastOp).append("] size=").append(batchSize).append(" result=").append(Arrays.toString(result)).toString());
batchSize = 0;
return result;
}
catch(Exception ex) {
System.out.println(ex.getMessage());
ex.printStackTrace();
return null;
}
}
protected String prepareInsertSql(DsEvent e, DsTransaction tx, DsOperation op) {
String tbName = op.getTableName().toString().toLowerCase().replace(" scetc.", " public.");
StringBuilder sql = new StringBuilder("insert into ").append(tbName).append("(");
StringBuilder valueClause = new StringBuilder(") values (");
for (ColumnMetaData cmeta : e.getMetaData().getTableMetaData(op.getTableName()).getColumnMetaData()) {
sql.append(cmeta.getColumnName()).append(",");
valueClause.append("?,");
}
sql.deleteCharAt(sql.length()-1).append(valueClause.deleteCharAt(valueClause.length()-1)).append(")");
return sql.toString();
}
protected String prepareUpdateSql(DsEvent e, DsTransaction tx, DsOperation op) {
String tbName = op.getTableName().toString().toLowerCase().replace(" scetc.", " public.");
StringBuilder sql = new StringBuilder("update ").append(tbName).append(" set ");
StringBuilder whereClause = new StringBuilder(" where 0=0");
TableMetaData tmeta = e.getMetaData().getTableMetaData(op.getTableName());
keyIndex = 0;
for (int i=0; iColumnMetaData cmeta = tmeta.getColumnMetaData(i);
DsColumn col = op.getColumn(i);
if (!col.isMissing()) {
sql.append(cmeta.getColumnName()).append("=?,");
keyIndex++;
}
if (cmeta.isKeyCol()) {
whereClause.append(" and ").append(cmeta.getColumnName()).append("=?");
}
}
sql.deleteCharAt(sql.length()-1).append(whereClause);
return sql.toString();
}
protected String prepareDeleteSql(DsEvent e, DsTransaction tx, DsOperation op) {
String tbName = op.getTableName().toString().toLowerCase().replace(" scetc.", " public.");
StringBuilder sql = new StringBuilder("delete from ").append(tbName).append(" where 0=0");
for (ColumnMetaData cmeta : e.getMetaData().getTableMetaData(op.getTableName()).getKeyColumns()) {
sql.append(" and ").append(cmeta.getColumnName()).append("=?");
}
return sql.toString();
}
protected void bindValue(PreparedStatement pstmt, int i,
ColumnMetaData cmeta, DsColumn col) throws SQLException, ParseException {
int jdbcType = cmeta.getDataType().getJDBCType();
if (logger.isDebugEnabled())
logger.debug(new StringBuilder("Bind value for column [").append(cmeta.getColumnName()).append("] Type=").append(cmeta.getDataType()).append(" Value=[").append(col.getValue()).append("]").toString());
if (col.isValueNull()) {
pstmt.setNull(i, jdbcType);
} else if (jdbcType==Types.CHAR || jdbcType==Types.VARCHAR) {
pstmt.setString(i, col.getValue());
} else if (jdbcType==Types.NUMERIC || jdbcType==Types.DECIMAL) {
pstmt.setBigDecimal(i, new BigDecimal(col.getValue()));
} else if (jdbcType==Types.DATE || jdbcType==Types.TIMESTAMP) {
//pstmt.setTimestamp(i, new Timestamp(dateFormat.parse(col.getValue()).getTime()));
pstmt.setString(i, col.getValue());
} else {
pstmt.setObject(i, col.getValue());
}
}
/* (non-Javadoc)
* @see com.goldengate.atg.datasource.AbstractHandler#transactionCommit(com.goldengate.atg.datasource.DsEvent, com.goldengate.atg.datasource.DsTransaction)
*/
@Override
public Status transactionCommit(DsEvent e, DsTransaction tx) {
if (logger.isDebugEnabled())
logger.debug("Method transactionCommit invoked. TxID="+tx.getTranID());
if (conn!=null)
try {
if (batchSize>0 && pstmt!=null) {
executeBatch();
}
conn.commit();
} catch (SQLException ex) {
logger.error("Transaction commit failed. TxID="+tx.getTranID(), ex);
}
return super.transactionCommit(e, tx);
}
/* (non-Javadoc)
* @see com.goldengate.atg.datasource.AbstractHandler#transactionRollback(com.goldengate.atg.datasource.DsEvent, com.goldengate.atg.datasource.DsTransaction)
*/
@Override
public Status transactionRollback(DsEvent e, DsTransaction tx) {
if (logger.isDebugEnabled())
logger.debug("Method transactionRollback invoked. TxID="+tx.getTranID());
if (conn!=null)
try {
conn.rollback();
} catch (SQLException ex) {
logger.error("Transaction rollback failed. TxID="+tx.getTranID(), ex);
}
return super.transactionRollback(e, tx);
}
/* (non-Javadoc)
* @see com.goldengate.atg.datasource.AbstractHandler#reportStatus()
*/
@Override
public String reportStatus() {
logger.debug("Method reportStatus invoked.");
return "SimpleJDBCHandler running...";
}
/* (non-Javadoc)
* @see com.goldengate.atg.datasource.AbstractHandler#destroy()
*/
@Override
public void destroy() {
logger.debug("Handler destroy...");
if (conn!=null)
try {
if (!conn.isClosed()) conn.close();
} catch (SQLException ex) {
logger.error("Connection close failed.", ex);
}
super.destroy();
}
/**
* @return the driver
*/
public String getDriver() {
return driver;
}
/**
* @param driver the driver to set
*/
public void setDriver(String driver) {
this.driver = driver;
}
/**
* @return the url
*/
public String getUrl() {
return url;
}
/**
* @param url the url to set
*/
public void setUrl(String url) {
this.url = url;
}
/**
* @return the arguments
*/
public Properties getArguments() {
return arguments;
}
/**
* @param arguments the arguments to set
*/
public void setArguments(Properties arguments) {
this.arguments = arguments;
}
/**
* @param user the user to set
*/
public void setUser(String user) {
if (arguments==null) {
arguments = new Properties();
}
arguments.setProperty("user", user);
}
/**
* @param password the password to set
*/
public void setPassword(String password) {
if (arguments==null) {
arguments = new Properties();
}
arguments.setProperty("password", password);
}
public void setDateFormat(String pattern) {
dateFormat = new SimpleDateFormat(pattern);
}
/**
* @return the maxBatchSize
*/
public int getMaxBatchSize() {
return maxBatchSize;
}
/**
* @param maxBatchSize the maxBatchSize to set
*/
public void setMaxBatchSize(int maxBatchSize) {
this.maxBatchSize = maxBatchSize;
}
/**
* @return the reportCount
*/
public long getReportCount() {
return reportCount;
}
/**
* @param reportCount the reportCount to set
*/
public void setReportCount(long reportCount) {
if (reportCount<100) {
logger.info("A reportCount less than 100 will turn off report.");
this.reportCount = 0;
} else if (this.reportCount != reportCount) {
this.reportCount = reportCount;
opCount = 0;
lastReportTime = System.currentTimeMillis();
}
}
}