GoldenGate for Java adapter介绍二（代码篇）
本示例主要介绍通过实现OGG的接口函数，实现自定义处理增量数据，将数据实时写入到mariadb （OGG官方不支持此数据库，所以只能采用自定义方式实现）。以下是本次示例的4个类：
ConnectionFactory
| package sample.handler.jdbc; import java.sql.Connection; public interface ConnectionFactory { public Connection getConnection() throws Exception; } |
DriverClassConnectionFactory
package sample.handler.jdbc; import java.sql.Connection; import java.sql.Driver; import java.sql.SQLException; import java.util.Properties; import org.slf4j.Logger; import org.slf4j.LoggerFactory; public class DriverClassConnectionFactory implements ConnectionFactory { private static final Logger logger = LoggerFactory.getLogger(DriverClassConnectionFactory.class); protected String driver; protected String url; protected Properties arguments; @Override public Connection getConnection() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException { 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(); return driv.connect(url, arguments); } /** * @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); } } |
OGG接口实现类SimpleJDBCHandler
|
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; 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; 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; 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; 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(); } } }
|
最后是基于此Handler创建一个测试类。
TestMariadb
package sample.handler.jdbc; public class TestMariadb { public static void main(String[] args) { SimpleJDBCHandler mariadb = new SimpleJDBCHandler(); gbase.setUrl("jdbc:mariadb://192.168.26.44:5258/mydb? "); gbase.setDateFormat("com.mariadb.jdbc.Driver"); gbase.setUser("root"); gbase.setPassword("admin123"); } } |
通过本示例提供的代码，可以实现将增量数据，从OGG的源端，以完全自定义的方式写入到任何需要的目标端。
