Java tutorial
/** * Licensed to JumpMind Inc under one or more contributor * license agreements. See the NOTICE file distributed * with this work for additional information regarding * copyright ownership. JumpMind Inc licenses this file * to you under the GNU General Public License, version 3.0 (GPLv3) * (the "License"); you may not use this file except in compliance * with the License. * * You should have received a copy of the GNU General Public License, * version 3.0 (GPLv3) along with this library; if not, see * <http://www.gnu.org/licenses/>. * * Unless required by applicable law or agreed to in writing, * software distributed under the License is distributed on an * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY * KIND, either express or implied. See the License for the * specific language governing permissions and limitations * under the License. */ package org.jumpmind.symmetric.db.derby; import java.sql.Blob; import java.sql.Clob; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Types; import java.util.Hashtable; import org.apache.commons.codec.binary.Base64; import org.apache.commons.lang.StringUtils; import org.apache.derby.iapi.db.Factory; import org.apache.derby.iapi.db.TriggerExecutionContext; import org.apache.derby.iapi.sql.conn.LanguageConnectionContext; import org.apache.derby.impl.jdbc.EmbedConnection; public class DerbyFunctions { private static final String CURRENT_CONNECTION_URL = "jdbc:default:connection"; private static final int MAX_STRING_LENGTH = 32672; // Base64 will output roughly 1.37% size of input private static final int MAX_BINARY_LENGTH = 23700; private static Hashtable<String, Boolean> syncDisabledTable = new Hashtable<String, Boolean>(); private static Hashtable<String, String> syncNodeDisabledTable = new Hashtable<String, String>(); public static String getTransactionId() throws SQLException { return getLanguageConnection().getTransactionExecute().getTransactionIdString(); } public static String getSessionId() throws SQLException { return getLanguageConnection().getDbname() + "-" + getLanguageConnection().getInstanceNumber(); } public static int isSyncDisabled() throws SQLException { return syncDisabledTable.get(getSessionId()) != null ? 1 : 0; } public static String getSyncNodeDisabled() throws SQLException { return syncNodeDisabledTable.get(getSessionId()); } public static String setSyncNodeDisabled(String nodeId) throws SQLException { if (nodeId == null) { return syncNodeDisabledTable.remove(getSessionId()); } else { return syncNodeDisabledTable.put(getSessionId(), nodeId); } } public static int setSyncDisabled(int disabledIndicator) throws SQLException { if (disabledIndicator == 0) { syncDisabledTable.remove(getSessionId()); return 0; } else { syncDisabledTable.put(getSessionId(), Boolean.TRUE); return 1; } } protected static int findColumnIndex(ResultSetMetaData metaData, String columnName) throws SQLException { for (int i = 1; i <= metaData.getColumnCount(); i++) { if (columnName.equals(metaData.getColumnName(i))) { return i; } } return -1; } protected static void appendCsvString(String tableName, String[] columnNames, String[] pkColumnNames, ResultSet rs, StringBuilder builder) throws SQLException { ResultSetMetaData metaData = rs.getMetaData(); for (String columnName : columnNames) { if (StringUtils.isNotBlank(columnName)) { int index = findColumnIndex(metaData, columnName); if (index >= 0) { int type = metaData.getColumnType(index); switch (type) { case Types.BLOB: builder.append( blobToString(columnName, tableName, getPrimaryKeyWhereString(pkColumnNames, rs))); builder.append(","); break; case Types.CLOB: builder.append( clobToString(columnName, tableName, getPrimaryKeyWhereString(pkColumnNames, rs))); builder.append(","); break; default: builder.append(escape(rs.getString(index))); builder.append(","); break; } } else { builder.append(","); } } else { builder.append(","); } } } public static void insertData(int enabled, String schemaName, String prefixName, String tableName, String channelName, String dmlType, int triggerHistId, String transactionId, String externalData, String columnNames, String pkColumnNames) throws SQLException { if (enabled == 1) { TriggerExecutionContext context = Factory.getTriggerExecutionContext(); String rowData = null; String pkData = null; String oldData = null; String[] parsedColumnNames = StringUtils.splitPreserveAllTokens(columnNames, ','); String[] parsedPkColumnNames = StringUtils.splitPreserveAllTokens(pkColumnNames, ','); if (dmlType.equals("I") || dmlType.equals("U")) { StringBuilder dataBuilder = new StringBuilder(); appendCsvString(tableName, parsedColumnNames, parsedPkColumnNames, context.getNewRow(), dataBuilder); rowData = dataBuilder.substring(0, dataBuilder.length() - 1); } if (dmlType.equals("U") || dmlType.equals("D")) { StringBuilder dataBuilder = new StringBuilder(); appendCsvString(tableName, parsedColumnNames, parsedPkColumnNames, context.getOldRow(), dataBuilder); oldData = dataBuilder.substring(0, dataBuilder.length() - 1); dataBuilder = new StringBuilder(); appendCsvString(tableName, parsedPkColumnNames, parsedPkColumnNames, context.getOldRow(), dataBuilder); pkData = dataBuilder.substring(0, dataBuilder.length() - 1); } Connection conn = DriverManager.getConnection(CURRENT_CONNECTION_URL); StringBuilder sql = new StringBuilder("insert into "); sql.append(schemaName); sql.append(prefixName); sql.append( "_data (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) "); sql.append(" values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, current_timestamp)"); PreparedStatement ps = conn.prepareStatement(sql.toString()); ps.setString(1, tableName); ps.setString(2, dmlType); ps.setLong(3, triggerHistId); ps.setString(4, pkData); ps.setString(5, rowData); ps.setString(6, oldData); ps.setString(7, channelName); ps.setString(8, transactionId); ps.setString(9, getSyncNodeDisabled()); ps.setString(10, externalData); ps.executeUpdate(); ps.close(); conn.close(); } } @Deprecated public static void insertData(String schemaName, String prefixName, String tableName, String channelName, String dmlType, int triggerHistId, String transactionId, String externalData, String pkData, String rowData, String oldRowData) throws SQLException { if (((dmlType.equals("I") || dmlType.equals("U")) && rowData != null) || dmlType.equals("D")) { Connection conn = DriverManager.getConnection(CURRENT_CONNECTION_URL); StringBuilder sql = new StringBuilder("insert into "); sql.append(schemaName); sql.append(prefixName); sql.append( "_data (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) "); sql.append(" values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, current_timestamp)"); PreparedStatement ps = conn.prepareStatement(sql.toString()); ps.setString(1, tableName); ps.setString(2, dmlType); ps.setLong(3, triggerHistId); ps.setString(4, pkData); ps.setString(5, rowData); ps.setString(6, oldRowData); ps.setString(7, channelName); ps.setString(8, transactionId); ps.setString(9, getSyncNodeDisabled()); ps.setString(10, externalData); ps.executeUpdate(); ps.close(); conn.close(); } } public static String escape(String str) { if (str != null) { return "\"" + StringUtils.replace(StringUtils.replace(str, "\\", "\\\\"), "\"", "\\\"") + "\""; } return ""; } public static String blobToString(String columnName, String tableName, String whereClause) throws SQLException { String str = null; if (StringUtils.isNotBlank(whereClause)) { Connection conn = DriverManager.getConnection(CURRENT_CONNECTION_URL); String sql = "select " + columnName + " from " + tableName + " where " + whereClause; PreparedStatement ps = conn.prepareStatement(sql); ResultSet rs = ps.executeQuery(); if (rs.next()) { byte[] bytes = null; int type = rs.getMetaData().getColumnType(1); if (type == Types.BINARY || type == Types.VARBINARY || type == Types.LONGVARBINARY) { bytes = rs.getBytes(1); } else { Blob blob = rs.getBlob(1); if (blob != null) { bytes = blob.getBytes(1, MAX_BINARY_LENGTH); } } if (bytes != null) { str = new String(Base64.encodeBase64(bytes)); } } ps.close(); conn.close(); } return str == null ? "" : "\"" + str + "\""; } public static String clobToString(String columnName, String tableName, String whereClause) throws SQLException { String str = null; if (StringUtils.isNotBlank(whereClause)) { Connection conn = DriverManager.getConnection(CURRENT_CONNECTION_URL); String sql = "select " + columnName + " from " + tableName + " where " + whereClause; PreparedStatement ps = conn.prepareStatement(sql); ResultSet rs = ps.executeQuery(); if (rs.next()) { Clob clob = rs.getClob(1); if (clob != null) { str = clob.getSubString(1, MAX_STRING_LENGTH); } } ps.close(); conn.close(); } return str == null ? "" : escape(str); } public static String getPrimaryKeyWhereString(String[] pkColumnNames, ResultSet rs) throws SQLException { final String AND = " and "; ResultSetMetaData metaData = rs.getMetaData(); StringBuilder b = new StringBuilder(); for (int i = 0; i < pkColumnNames.length; i++) { String columnName = pkColumnNames[i]; int index = findColumnIndex(metaData, columnName); int type = metaData.getColumnType(index); if (type != Types.BINARY && type != Types.BLOB && type != Types.LONGVARBINARY && type != Types.VARBINARY) { b.append("\"").append(columnName).append("\"="); switch (type) { case Types.BIT: case Types.TINYINT: case Types.SMALLINT: case Types.INTEGER: case Types.BIGINT: case Types.FLOAT: case Types.REAL: case Types.DOUBLE: case Types.NUMERIC: case Types.DECIMAL: case Types.BOOLEAN: b.append(rs.getObject(index)); break; case Types.CHAR: case Types.VARCHAR: case Types.LONGVARCHAR: b.append("\"").append(rs.getString(index)).append("\""); break; case Types.DATE: case Types.TIMESTAMP: b.append("{ts '"); b.append(rs.getString(index)); b.append("'}"); break; } b.append(AND); } } b.replace(b.length() - AND.length(), b.length(), ""); return b.toString(); } private static LanguageConnectionContext getLanguageConnection() throws SQLException { EmbedConnection conn = (EmbedConnection) DriverManager.getConnection(CURRENT_CONNECTION_URL); return conn.getLanguageConnection(); } }