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; 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();

}

}

}

從以上程式碼可以看出，可以通過過載不同的介面函式，獲得事務、記錄的相關資訊，包括操作的表、時間、欄位、操作型別及變化的資料等。

最後是基於此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的源端，以完全自定義的方式寫入到任何需要的目標端。