Java tutorial
/** * Global Sensor Networks (GSN) Source Code * Copyright (c) 2006-2014, Ecole Polytechnique Federale de Lausanne (EPFL) * * This file is part of GSN. * * GSN is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 3 of the License, or * (at your option) any later version. * * GSN is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with GSN. If not, see <http://www.gnu.org/licenses/>. * * File: src/gsn/storage/StorageManager.java * * @author Jerome Rousselot * @author Mehdi Riahi * @author rhietala * @author gsn_devs * @author Ali Salehi * @author Timotee Maret * @author Mehdi Riahi * @author Sofiane Sarni * @author Milos Stojanovic * */ package gsn.storage; import gsn.Main; import gsn.beans.DataField; import gsn.beans.DataTypes; import gsn.beans.StreamElement; import gsn.http.datarequest.AbstractQuery; import gsn.storage.hibernate.DBConnectionInfo; import gsn.utils.GSNRuntimeException; import gsn.utils.ValidityTools; import java.io.Serializable; import java.sql.*; import java.util.ArrayList; import org.apache.commons.dbcp.*; import org.slf4j.LoggerFactory; import org.slf4j.Logger; public abstract class StorageManager { private static final transient Logger logger = LoggerFactory.getLogger(StorageManager.class); private String databaseDriver; private BasicDataSource pool; public void init(String databaseDriver, String username, String password, String databaseURL, int maxDBConnections) { this.databaseDriver = databaseDriver; pool = DataSources.getDataSource(new DBConnectionInfo(databaseDriver, databaseURL, username, password)); pool.setMaxActive(maxDBConnections); pool.setMaxIdle(maxDBConnections); pool.setRemoveAbandoned(true); // removing unused connections, used to clean after poorly written code pool.setRemoveAbandonedTimeout(300); // 5 minutes // Connection con = null; try { initDatabaseAccess(con = getConnection()); logger.info(new StringBuilder().append("StorageManager DB connection initialized successfuly. driver:") .append(databaseDriver).append(" url:").append(databaseURL).toString()); } catch (Exception e) { logger.error( new StringBuilder().append("Connecting to the database with the following properties failed :") .append("\n\t UserName :").append(username).append("\n\t Password : ").append(password) .append("\n\t Driver class : ").append(databaseDriver).append("\n\t Database URL : ") .append(databaseURL).toString()); logger.info(new StringBuilder().append(e.getMessage()) .append(", Please refer to the logs for more detailed information.").toString()); logger.info("Make sure in the gsn.xml file, the <storage ...> element is correct."); logger.error(e.getMessage(), e); } finally { close(con); } } public void initDatabaseAccess(Connection con) throws Exception { } public abstract byte convertLocalTypeToGSN(int jdbcType, int precision); public abstract String getStatementDropIndex(); public abstract String getStatementDropView(); public abstract int getTableNotExistsErrNo(); public abstract String addLimit(String query, int limit, int offset); public abstract StringBuilder getStatementUselessDataRemoval(String virtualSensorName, long storageSize); public byte convertLocalTypeToGSN(int jdbcType) { return convertLocalTypeToGSN(jdbcType, 0); } /** * Returns false if the table doesnt exist. Uses the current default * connection. * * @param tableName * @return False if the table doesn't exist in the current connection. * @throws SQLException */ public boolean tableExists(CharSequence tableName) throws SQLException { Connection connection = null; try { connection = getConnection(); return tableExists(tableName, new DataField[] {}, connection); } finally { close(connection); } } /** * Checks to see if the given tablename exists using the given connection. * * @param tableName * @param connection * @return * @throws SQLException */ public boolean tableExists(CharSequence tableName, Connection connection) throws SQLException { return tableExists(tableName, new DataField[] {}, connection); } public abstract StringBuilder getStatementRemoveUselessDataCountBased(String virtualSensorName, long storageSize); public StringBuilder getStatementRemoveUselessDataTimeBased(String virtualSensorName, long storageSize) { StringBuilder query = null; long timedToRemove = -1; Connection conn = null; try { ResultSet rs = Main.getStorage(virtualSensorName).executeQueryWithResultSet( new StringBuilder("SELECT MAX(timed) FROM ").append(virtualSensorName), conn = Main.getStorage(virtualSensorName).getConnection()); if (rs.next()) timedToRemove = rs.getLong(1); } catch (SQLException e) { logger.error(e.getMessage(), e); } finally { Main.getStorage(virtualSensorName).close(conn); } query = new StringBuilder().append("delete from ").append(virtualSensorName).append(" where ") .append(virtualSensorName).append(".timed < ").append(timedToRemove); query.append(" - ").append(storageSize); return query; } public DataField[] tableToStructure(CharSequence tableName, Connection connection) throws SQLException { StringBuilder sb = new StringBuilder("select * from ").append(tableName).append(" where 1=0 "); ResultSet rs = null; DataField[] toReturn = null; try { rs = executeQueryWithResultSet(sb, connection); ResultSetMetaData structure = rs.getMetaData(); ArrayList<DataField> toReturnArr = new ArrayList<DataField>(); for (int i = 1; i <= structure.getColumnCount(); i++) { String colName = structure.getColumnLabel(i); if (colName.equalsIgnoreCase("pk")) continue; int colType = structure.getColumnType(i); byte colTypeInGSN = convertLocalTypeToGSN(colType); if (colTypeInGSN == -100) { logger.error( "The type can't be converted to GSN form - error description: virtual sensor name is: " + tableName + ", field name is: " + colName + ", query is: " + sb); } toReturnArr.add(new DataField(colName, colTypeInGSN)); } toReturn = toReturnArr.toArray(new DataField[] {}); } finally { if (rs != null) close(rs); } return toReturn; } /* * Alternative method to 'tableToStructure' * Useful for correctly creating structure for fields with variable length (like char, varchar, binary, blob) * */ public DataField[] tableToStructureByString(String tableName, Connection connection) throws SQLException { StringBuilder sb = new StringBuilder("select * from ").append(tableName).append(" where 1=0 "); ResultSet rs = null; DataField[] toReturn = null; try { rs = executeQueryWithResultSet(sb, connection); ResultSetMetaData structure = rs.getMetaData(); ArrayList<DataField> toReturnArr = new ArrayList<DataField>(); for (int i = 1; i <= structure.getColumnCount(); i++) { String colName = structure.getColumnLabel(i); if (colName.equalsIgnoreCase("pk")) continue; if (colName.equalsIgnoreCase("timed")) continue; int colType = structure.getColumnType(i); String colTypeName = structure.getColumnTypeName(i); int precision = structure.getPrecision(i); byte colTypeInGSN = convertLocalTypeToGSN(colType); if (colTypeInGSN == -100) { logger.error( "The type can't be converted to GSN form - error description: virtual sensor name is: " + tableName + ", field name is: " + colName + ", query is: " + sb); } if ((colTypeInGSN == DataTypes.VARCHAR) || (colTypeInGSN == DataTypes.CHAR)) toReturnArr.add(new DataField(colName, colTypeName, precision, colName)); else toReturnArr.add(new DataField(colName, colTypeInGSN)); } toReturn = toReturnArr.toArray(new DataField[] {}); } finally { if (rs != null) close(rs); } return toReturn; } /** * Returns false if the table doesnt exist. If the table exists but the * structure is not compatible with the specified fields the method throws * GSNRuntimeException. Note that this method doesn't close the connection * * @param tableName * @param connection (this method will not close it and the caller is responsible * for closing the connection) * @return * @throws SQLException * @Throws GSNRuntimeException */ public boolean tableExists(CharSequence tableName, DataField[] fields, Connection connection) throws SQLException, GSNRuntimeException { if (!ValidityTools.isValidJavaVariable(tableName)) throw new GSNRuntimeException("Table name is not valid"); StringBuilder sb = new StringBuilder("select * from ").append(tableNameGeneratorInString(tableName)) .append(" where 1=0 "); ResultSet rs = null; try { rs = executeQueryWithResultSet(sb, connection); ResultSetMetaData structure = rs.getMetaData(); if (fields != null && fields.length > 0) nextField: for (DataField field : fields) { for (int i = 1; i <= structure.getColumnCount(); i++) { String colName = structure.getColumnLabel(i); int colType = structure.getColumnType(i); int colTypeScale = structure.getScale(i); if (field.getName().equalsIgnoreCase(colName)) { byte gsnType = convertLocalTypeToGSN(colType, colTypeScale); if (gsnType == -100) { logger.error( "The type can't be converted to GSN form - error description: virtual sensor name is: " + tableName + ", field name is: " + colName + ", query is: " + sb); } if (field.getDataTypeID() == gsnType) continue nextField; else throw new GSNRuntimeException("The column : " + colName + " in the >" + tableName + "< table is not compatible with type : " + field.getType() + ". The actual type for this table (currently in the database): " + colType); } } throw new GSNRuntimeException("The table " + tableName + " in the database, doesn't have the >" + field.getName() + "< column."); } } catch (SQLException e) { if (e.getErrorCode() == getTableNotExistsErrNo() || e.getMessage().contains("does not exist")) return false; else { logger.error(e.getMessage()); throw e; } } finally { close(rs); } return true; } public boolean tableExists(CharSequence tableName, DataField[] fields) throws SQLException { Connection conn = null; boolean to_return = true; try { conn = getConnection(); to_return = tableExists(tableName, fields, conn); } finally { close(conn); } return to_return; } /** * Returns true if the specified query has any result in it's result set. * The created result set will be closed automatically. * * @param sqlQuery * @return */ public boolean isThereAnyResult(StringBuilder sqlQuery) { boolean toreturn = false; Connection connection = null; try { connection = getConnection(); PreparedStatement prepareStatement = connection.prepareStatement(sqlQuery.toString()); ResultSet resultSet = prepareStatement.executeQuery(); toreturn = resultSet.next(); } catch (SQLException error) { logger.error(error.getMessage(), error); } finally { close(connection); } return toreturn; } /** * Executes the query of the database. Returns the specified colIndex of the * first row. Useful for image recovery of the web interface. * * @param query The query to be executed. * @return A resultset with only one row and one column. The user of the * method should first call next on the result set to make sure that * the row is there and then retrieve the value for the row. * @throws SQLException */ public ResultSet getBinaryFieldByQuery(StringBuilder query, String colName, long pk, Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(query.toString()); ps.setLong(1, pk); return ps.executeQuery(); } public void closeStatement(Statement stmt) { try { if (stmt != null) { stmt.close(); } } catch (SQLException e) { logger.error(e.getMessage(), e); } } public void close(ResultSet resultSet) { try { if (resultSet != null) { resultSet.close(); } } catch (SQLException e) { logger.error(e.getMessage(), e); } } public void close(PreparedStatement preparedStatement) { try { if (preparedStatement != null) { preparedStatement.close(); } } catch (SQLException e) { logger.error(e.getMessage(), e); } } public void close(Connection conn) { try { if (conn != null && !conn.isClosed()) { conn.close(); } } catch (SQLException e) { logger.debug(e.getMessage(), e); } } /** * @throws SQLException */ public void shutdown() throws SQLException { logger.warn("Closing the connection pool [done]."); } /** * ************************************************************************ * Various Statement Executors. * ************************************************************************ */ public void executeRenameTable(String oldName, String newName) throws SQLException { Connection conn = null; try { conn = getConnection(); executeRenameTable(oldName, newName, conn); } finally { close(conn); } } public void executeRenameTable(String oldName, String newName, Connection connection) throws SQLException { PreparedStatement prepareStatement = null; try { prepareStatement = connection.prepareStatement(getStatementRenameTable(oldName, newName)); prepareStatement.execute(); } finally { close(prepareStatement); } } public void executeDropTable(CharSequence tableName) throws SQLException { Connection conn = null; try { conn = getConnection(); executeDropTable(tableName, conn); } finally { close(conn); } } public void executeDropTable(CharSequence tableName, Connection connection) { PreparedStatement prepareStatement = null; try { String stmt = getStatementDropTable(tableName, connection).toString(); logger.debug("Dropping table structure: " + tableName + " With query: " + stmt); prepareStatement = connection.prepareStatement(stmt); prepareStatement.execute(); } catch (SQLException e) { logger.info(e.getMessage(), e); } } public void executeDropView(StringBuilder tableName) throws SQLException { Connection conn = null; try { conn = getConnection(); executeDropView(tableName, conn); } finally { close(conn); } } public void executeDropView(StringBuilder tableName, Connection connection) throws SQLException { logger.debug("Dropping table structure: " + tableName); PreparedStatement prepareStatement = connection .prepareStatement(getStatementDropView(tableName, connection).toString()); prepareStatement.execute(); close(prepareStatement); } public void executeCreateTable(CharSequence tableName, DataField[] structure, boolean unique) throws SQLException { Connection conn = null; try { conn = getConnection(); executeCreateTable(tableName, structure, unique, conn); } finally { close(conn); } } /** * Create a table with a index on the timed field. * * @param tableName * @param structure * @param unique , setting this true cause the system to create a unique index on time. * @param connection * @throws SQLException */ public void executeCreateTable(CharSequence tableName, DataField[] structure, boolean unique, Connection connection) throws SQLException { StringBuilder sql = getStatementCreateTable(tableName, structure, connection); logger.debug(new StringBuilder().append("The create table statement is : ").append(sql).toString()); PreparedStatement prepareStatement = connection.prepareStatement(sql.toString()); prepareStatement.execute(); prepareStatement.close(); sql = getStatementCreateIndexOnTimed(tableName, unique); logger.debug(new StringBuilder().append("The create index statement is : ").append(sql).toString()); prepareStatement = connection.prepareStatement(sql.toString()); prepareStatement.execute(); } public ResultSet executeQueryWithResultSet(StringBuilder query, Connection connection) throws SQLException { return connection.prepareStatement(query.toString()).executeQuery(); } public ResultSet executeQueryWithResultSet(AbstractQuery abstractQuery, Connection c) throws SQLException { if (abstractQuery.getLimitCriterion() == null) { return executeQueryWithResultSet(abstractQuery.getStandardQuery(), c); } String query = addLimit(abstractQuery.getStandardQuery().toString(), abstractQuery.getLimitCriterion().getSize(), abstractQuery.getLimitCriterion().getOffset()); return executeQueryWithResultSet(new StringBuilder(query), c); } public DataEnumerator executeQuery(StringBuilder query, boolean binaryFieldsLinked, Connection connection) throws SQLException { logger.debug("Executing query: " + query + "( Binary Field Linked:" + binaryFieldsLinked + ")"); return new DataEnumerator(this, connection.prepareStatement(query.toString()), binaryFieldsLinked); } /** * Attention: Caller should close the connection. * * @param abstractQuery * @param binaryFieldsLinked * @param connection * @return * @throws SQLException */ public DataEnumerator executeQuery(AbstractQuery abstractQuery, boolean binaryFieldsLinked, Connection connection) throws SQLException { if (abstractQuery.getLimitCriterion() == null) { return executeQuery(abstractQuery.getStandardQuery(), binaryFieldsLinked, connection); } String query = addLimit(abstractQuery.getStandardQuery().toString(), abstractQuery.getLimitCriterion().getSize(), abstractQuery.getLimitCriterion().getOffset()); logger.debug("Executing query: " + query + "(" + binaryFieldsLinked + ")"); return new DataEnumerator(this, connection.prepareStatement(query.toString()), binaryFieldsLinked); } public DataEnumerator streamedExecuteQuery(AbstractQuery abstractQuery, boolean binaryFieldsLinked, Connection connection) throws SQLException { if (abstractQuery.getLimitCriterion() == null) { return streamedExecuteQuery(abstractQuery.getStandardQuery().toString(), binaryFieldsLinked, connection); } String query = addLimit(abstractQuery.getStandardQuery().toString(), abstractQuery.getLimitCriterion().getSize(), abstractQuery.getLimitCriterion().getOffset()); logger.debug("Executing query: " + query + "(" + binaryFieldsLinked + ")"); return streamedExecuteQuery(query, binaryFieldsLinked, connection); } public DataEnumerator executeQuery(StringBuilder query, boolean binaryFieldsLinked) throws SQLException { return executeQuery(query, binaryFieldsLinked, getConnection()); } public DataEnumerator streamedExecuteQuery(String query, boolean binaryFieldsLinked, Connection conn) throws SQLException { return new DataEnumerator(this, conn.prepareStatement(query), binaryFieldsLinked); } public DataEnumerator streamedExecuteQuery(String query, boolean binaryFieldsLinked) throws SQLException { return streamedExecuteQuery(query, binaryFieldsLinked, getConnection()); } public void executeCreateView(CharSequence viewName, CharSequence selectQuery) throws SQLException { Connection connection = null; try { connection = getConnection(); executeCreateView(viewName, selectQuery, connection); } finally { close(connection); } } public void executeCreateView(CharSequence viewName, CharSequence selectQuery, Connection connection) throws SQLException { StringBuilder statement = getStatementCreateView(viewName, selectQuery); logger.debug("Creating a view:" + statement); final PreparedStatement prepareStatement = connection.prepareStatement(statement.toString()); prepareStatement.execute(); close(prepareStatement); } /** * This method executes the provided statement over the connection. If there * is an error retruns -1 otherwise it returns the output of the * executeUpdate method on the PreparedStatement class which reflects the * number of changed rows in the underlying table. * * @param sql * @param connection * @return Number of effected rows or -1 if there is an error. */ public void executeCommand(String sql, Connection connection) { Statement stmt = null; try { stmt = connection.createStatement(); stmt.execute(sql); } catch (SQLException error) { logger.error(error.getMessage() + " FOR: " + sql, error); } finally { try { if (stmt != null && !stmt.isClosed()) stmt.close(); } catch (SQLException e) { logger.error(e.getMessage(), e); } } } public int executeUpdate(String updateStatement, Connection connection) { int toReturn = -1; //PreparedStatement prepareStatement = null; try { //prepareStatement = connection.prepareStatement(updateStatement); //toReturn = prepareStatement.executeUpdate(); toReturn = connection.createStatement().executeUpdate(updateStatement); } catch (SQLException error) { logger.error(error.getMessage(), error); } return toReturn; } public int executeUpdate(StringBuilder updateStatement, Connection connection) { int to_return = -1; to_return = executeUpdate(updateStatement.toString(), connection); return to_return; } public int executeUpdate(StringBuilder updateStatement) throws SQLException { Connection connection = null; try { connection = getConnection(); return executeUpdate(updateStatement, connection); } finally { close(connection); } } public void executeInsert(CharSequence tableName, DataField[] fields, StreamElement se) throws SQLException { Connection connection = null; try { connection = getConnection(); executeInsert(tableName, fields, se, connection); } finally { close(connection); } } public void executeInsert(CharSequence tableName, DataField[] fields, StreamElement streamElement, Connection connection) throws SQLException { PreparedStatement ps = null; String query = getStatementInsert(tableName, fields).toString(); try { ps = connection.prepareStatement(query); int counter = 1; for (DataField dataField : fields) { if (dataField.getName().equalsIgnoreCase("timed")) continue; Serializable value = streamElement.getData(dataField.getName()); switch (dataField.getDataTypeID()) { case DataTypes.VARCHAR: if (value == null) ps.setNull(counter, Types.VARCHAR); else ps.setString(counter, value.toString()); break; case DataTypes.CHAR: if (value == null) ps.setNull(counter, Types.CHAR); else ps.setString(counter, value.toString()); break; case DataTypes.INTEGER: if (value == null) ps.setNull(counter, Types.INTEGER); else ps.setInt(counter, ((Number) value).intValue()); break; case DataTypes.SMALLINT: if (value == null) ps.setNull(counter, Types.SMALLINT); else ps.setShort(counter, ((Number) value).shortValue()); break; case DataTypes.TINYINT: if (value == null) ps.setNull(counter, Types.TINYINT); else ps.setByte(counter, ((Number) value).byteValue()); break; case DataTypes.DOUBLE: if (value == null) ps.setNull(counter, Types.DOUBLE); else ps.setDouble(counter, ((Number) value).doubleValue()); break; case DataTypes.FLOAT: if (value == null) ps.setNull(counter, Types.FLOAT); else ps.setFloat(counter, ((Number) value).floatValue()); break; case DataTypes.BIGINT: if (value == null) ps.setNull(counter, Types.BIGINT); else ps.setLong(counter, ((Number) value).longValue()); break; case DataTypes.BINARY: if (value == null) ps.setNull(counter, Types.BINARY); else ps.setBytes(counter, (byte[]) value); break; default: logger.error("The type conversion is not supported for : " + dataField.getName() + "(" + dataField.getDataTypeID() + ") : "); } counter++; } ps.setLong(counter, streamElement.getTimeStamp()); ps.execute(); } catch (GSNRuntimeException e) { //if (e.getType() == GSNRuntimeException.UNEXPECTED_VIRTUAL_SENSOR_REMOVAL) { // if (logger.isDebugEnabled()) // logger.debug("An stream element dropped due to unexpected virtual sensor removal. (Stream element: " + streamElement.toString() + ")+ Query: " + query, e); //} else logger.warn("Inserting a stream element failed : " + streamElement.toString(), e); } catch (SQLException e) { if (e.getMessage().toLowerCase().contains("duplicate entry")) logger.info("Error occurred on inserting data to the database, an stream element dropped due to: " + e.getMessage() + ". (Stream element: " + streamElement.toString() + ")+ Query: " + query); else logger.warn("Error occurred on inserting data to the database, an stream element dropped due to: " + e.getMessage() + ". (Stream element: " + streamElement.toString() + ")+ Query: " + query); throw e; } finally { close(ps); } } /*************************************************************************** * Statement Generators **************************************************************************/ /** * Creates a sql statement which can be used for inserting the specified * stream element in to the specified table. * * @param tableName The table which the generated sql will pointing to. * @param fields The stream element for which the sql statement is generated. * @return A sql statement which can be used for inserting the provided * stream element into the specified table. */ public StringBuilder getStatementInsert(CharSequence tableName, DataField fields[]) { StringBuilder toReturn = new StringBuilder("insert into ").append(tableName).append(" ( "); int numberOfQuestionMarks = 1; //Timed is always there. for (DataField dataField : fields) { if (dataField.getName().equalsIgnoreCase("timed")) continue; numberOfQuestionMarks++; toReturn.append(dataField.getName()).append(" ,"); } toReturn.append(" timed ").append(" ) values ("); for (int i = 1; i <= numberOfQuestionMarks; i++) toReturn.append("?,"); toReturn.deleteCharAt(toReturn.length() - 1); toReturn.append(")"); return toReturn; } public String getStatementRenameTable(String oldName, String newName) { return new StringBuilder("alter table ").append(oldName).append(" rename to ").append(newName).toString(); } public abstract StringBuilder getStatementDropTable(CharSequence tableName, Connection conn) throws SQLException; /** * First detects the appropriate DB Engine to use. Get's the drop index * statement syntax (which is DB dependent) and executes it. * * @param indexName * @param connection * @return * @throws SQLException */ public StringBuilder getStatementDropIndex(CharSequence indexName, CharSequence tableName, Connection connection) throws SQLException { return new StringBuilder(getStatementDropIndex().replace("#NAME", indexName).replace("#TABLE", tableName)); } public StringBuilder getStatementDropView(CharSequence viewName, Connection connection) throws SQLException { return new StringBuilder(getStatementDropView().replace("#NAME", viewName)); } public StringBuilder getStatementCreateIndexOnTimed(CharSequence tableName, boolean unique) throws SQLException { StringBuilder toReturn = new StringBuilder("CREATE "); if (unique) toReturn.append(" UNIQUE "); toReturn.append(" INDEX ").append(tableNamePostFixAppender(tableName, "_INDEX")).append(" ON ") .append(tableName).append(" (timed DESC)"); return toReturn; } public StringBuilder getStatementCreateTable(CharSequence tableName, DataField[] structure, Connection connection) throws SQLException { return getStatementCreateTable(tableName.toString(), structure); } public abstract StringBuilder getStatementCreateTable(String tableName, DataField[] structure); public StringBuilder getStatementCreateView(CharSequence viewName, CharSequence selectQuery) { return new StringBuilder("create view ").append(viewName).append(" AS ( ").append(selectQuery) .append(" ) "); } private String driver = null; /** * The prefix is in lower case * * @return */ public abstract String getJDBCPrefix(); public String getJDBCDriverClass() { return databaseDriver; } /* * Converts from internal GSN data types to a supported DB data type. * @param field The DataField to be converted @return convertedType The * datatype name used by the target database. */ public abstract String convertGSNTypeToLocalType(DataField gsnType); /** * Obtains the default database connection. * The conneciton comes from the data source which is configured through gsn.xml file. * @return * @throws SQLException */ public Connection getConnection() throws SQLException { logger.debug("Asking a con. to DB: " + pool.getUrl() + " => busy: " + pool.getNumActive() + ", max-size: " + pool.getMaxActive() + ", idle: " + pool.getNumIdle()); return pool.getConnection(); } /** * Retruns an approximation of the difference between the current time of the DB and that of the local system * * @return */ public long getTimeDifferenceInMillis() { String query = getStatementDifferenceTimeInMillis(); Connection connection = null; try { connection = getConnection(); PreparedStatement prepareStatement = connection.prepareStatement(query); long time1 = System.currentTimeMillis(); ResultSet resultSet; resultSet = prepareStatement.executeQuery(); resultSet.next(); long time2 = System.currentTimeMillis(); return resultSet.getLong(1) - time2 + (time2 - time1) / 2; } catch (SQLException error) { logger.error(error.getMessage(), error); } finally { close(connection); } return 0; } public abstract String getStatementDifferenceTimeInMillis(); public ArrayList<String> getInternalTables() throws SQLException { return new ArrayList<String>(); } // public String randomTableNameGenerator(int length) { byte oneCharacter; StringBuffer result = new StringBuffer(length); for (int i = 0; i < length; i++) { oneCharacter = (byte) ((Math.random() * ('z' - 'a' + 1)) + 'a'); result.append((char) oneCharacter); } return result.toString(); } public int tableNameGenerator() { return randomTableNameGenerator(15).hashCode(); } public StringBuilder tableNameGeneratorInString(CharSequence tableName) { return new StringBuilder(tableName); } public StringBuilder tableNameGeneratorInString(int code) { StringBuilder sb = new StringBuilder("_"); if (code < 0) sb.append("_"); sb.append(Math.abs(code)); return tableNameGeneratorInString(sb); } public String tableNamePostFixAppender(CharSequence table_name, String postFix) { String tableName = table_name.toString(); if (tableName.endsWith("\"")) return (tableName.substring(0, tableName.length() - 2)) + postFix + "\""; else return tableName + postFix; } // deprecated protected boolean isH2; protected boolean isMysql; protected boolean isOracle; protected boolean isSqlServer; protected boolean isPostgres; public boolean isOracle() { return isOracle; } public boolean isH2() { return isH2; } public boolean isSqlServer() { return isSqlServer; } public boolean isMysqlDB() { return isMysql; } public boolean isPostgres() { return isPostgres; } }