Java tutorial
/* * Copyright 2003 - 2016 The eFaps Team * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * 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.efaps.db.databases; import java.io.ByteArrayInputStream; import java.io.IOException; import java.io.UnsupportedEncodingException; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.Map; import java.util.Set; import java.util.zip.Adler32; import java.util.zip.CheckedInputStream; import org.apache.commons.dbutils.BasicRowProcessor; import org.apache.commons.dbutils.RowProcessor; import org.efaps.db.databases.information.TableInformation; import org.efaps.util.EFapsException; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import edu.umd.cs.findbugs.annotations.SuppressFBWarnings; /** * The database driver is used for Oracle databases starting with version 9i. * It does not support auto generated keys. To generate a new id number, * the Oracle sequences are used. * * @author The eFaps Team * */ public class OracleDatabase extends AbstractDatabase<OracleDatabase> { /** * Logging instance used in this class. */ private static final Logger LOG = LoggerFactory.getLogger(OracleDatabase.class); /** * Select statement to select all unique keys for current logged in * PostgreSQL database user. * * @see #initTableInfoUniqueKeys(Connection, String, Map) */ private static final String SQL_UNIQUE_KEYS = "select " + "a.index_name as INDEX_NAME, " + "a.table_name as TABLE_NAME, " + "b.column_name as COLUMN_NAME, " + "b.position as ORDINAL_POSITION " + "from " + "user_constraints a, " + "user_cons_columns b " + "where " + "a.constraint_type='U' " + "and a.index_name = b.constraint_name"; /** * Select statement for all foreign keys for current logged in PostgreSQL * database user. * * @see #initTableInfoForeignKeys(Connection, String, Map) */ private static final String SQL_FOREIGN_KEYS = "select " + "ucc1.TABLE_NAME as TABLE_NAME, " + "uc.constraint_name as FK_NAME, " + "ucc1.column_name as FKCOLUMN_NAME, " + "case " + "when uc.delete_rule='NO ACTION' then '" + DatabaseMetaData.importedKeyNoAction + "' " + "when uc.delete_rule='CASCASE' then '" + DatabaseMetaData.importedKeyCascade + "' " + "else '' end as DELETE_RULE, " + "ucc2.table_name as PKTABLE_NAME, " + "ucc2.column_name as PKCOLUMN_NAME " + "from " + "user_constraints uc, " + "user_cons_columns ucc1, " + "user_cons_columns ucc2 " + "where " + "uc.constraint_name = ucc1.constraint_name " + "and uc.r_constraint_name = ucc2.constraint_name " + "and ucc1.POSITION = ucc2.POSITION " + "and uc.constraint_type = 'R'"; /** * Singleton processor instance that handlers share to save memory. Notice * the default scoping to allow only classes in this package to use this * instance. */ private static final RowProcessor ROWPROCESSOR = new BasicRowProcessor() { /** * Convert a <code>ResultSet</code> row into an <code>Object[]</code>. * This implementation copies column values into the array in the same * order they're returned from the <code>ResultSet</code>. Array * elements will be set to <code>null</code> if the column was SQL NULL. * * @see org.apache.commons.dbutils.RowProcessor#toArray(java.sql.ResultSet) * @param _rs ResultSet that supplies the array data * @throws SQLException if a database access error occurs * @return the newly created array */ @Override public Object[] toArray(final ResultSet _rs) throws SQLException { final ResultSetMetaData metaData = _rs.getMetaData(); final int cols = metaData.getColumnCount(); final Object[] result = new Object[cols]; for (int i = 0; i < cols; i++) { switch (metaData.getColumnType(i + 1)) { case java.sql.Types.TIMESTAMP: result[i] = _rs.getTimestamp(i + 1); break; case java.sql.Types.NUMERIC: if (metaData.getScale(i + 1) > 0) { result[i] = _rs.getBigDecimal(i + 1); } else { result[i] = _rs.getLong(i + 1); } break; default: result[i] = _rs.getObject(i + 1); } } return result; } }; /** * The instance is initialised and sets the columns map used for this * database. */ public OracleDatabase() { super(); addMapping(ColumnType.INTEGER, "number(*,0)", "null", "number(38,0)"); addMapping(ColumnType.DECIMAL, "numeric", "null", "decimal", "numeric"); addMapping(ColumnType.REAL, "number", "null", "number"); addMapping(ColumnType.STRING_SHORT, "varchar2", "null", "varchar2", "char"); addMapping(ColumnType.STRING_LONG, "varchar2", "null", "varchar2"); addMapping(ColumnType.DATETIME, "timestamp", "null", "timestamp", "timestamp(6)", "date"); addMapping(ColumnType.BLOB, "blob", "null", "blob"); addMapping(ColumnType.CLOB, "nclob", "null", "nclob"); addMapping(ColumnType.BOOLEAN, "number", "null", "number"); } /** * {@inheritDoc} * @throws SQLException */ @Override public boolean isConnected(final Connection _connection) throws SQLException { boolean ret = false; final Statement stmt = _connection.createStatement(); try { final ResultSet resultset = stmt .executeQuery("select product from product_component_version where product like 'Oracle%'"); ret = resultset.next(); resultset.close(); } finally { stmt.close(); } return ret; } /** * {@inheritDoc} */ @Override public int getMaxExpressions() { return 999; } /** * The method returns string <code>sysdate</code> which let Oracle set the * timestamp automatically from the database server. * * @return string <code>sysdate</code> */ @Override public String getCurrentTimeStamp() { return "sysdate"; } /** * {@inheritDoc} */ @Override public String getTimestampValue(final String _isoDateTime) { final String format = "'yyyy-mm-dd\"T\"hh24:mi:ss.ff3'"; return "to_timestamp('" + _isoDateTime + "', " + format + ")"; } /** * {@inheritDoc} */ @Override public Object getBooleanValue(final Boolean _value) { Integer ret = 0; if (_value) { ret = 1; } return ret; } /** * This is the Oracle specific implementation of an all deletion. Following * order is used to remove all eFaps specific information of the current * Oracle database user: * <ul> * <li>remove all user views</li> * <li>remove all user tables</li> * <li>remove all user sequences</li> * </ul> * Attention! If application specific tables, views or constraints are * defined, this database objects are also removed! * * @param _con sql connection * @throws SQLException if delete of the views, tables or sequences failed */ @Override @SuppressFBWarnings("SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE") public void deleteAll(final Connection _con) throws SQLException { final Statement stmtSel = _con.createStatement(); final Statement stmtExec = _con.createStatement(); try { // remove all views if (OracleDatabase.LOG.isInfoEnabled()) { OracleDatabase.LOG.info("Remove all Views"); } ResultSet rs = stmtSel.executeQuery("select VIEW_NAME from USER_VIEWS"); while (rs.next()) { final String viewName = rs.getString(1); if (OracleDatabase.LOG.isDebugEnabled()) { OracleDatabase.LOG.debug(" - View '" + viewName + "'"); } stmtExec.execute("drop view " + viewName); } rs.close(); // remove all tables if (OracleDatabase.LOG.isInfoEnabled()) { OracleDatabase.LOG.info("Remove all Tables"); } rs = stmtSel.executeQuery("select TABLE_NAME from USER_TABLES"); while (rs.next()) { final String tableName = rs.getString(1); if (OracleDatabase.LOG.isDebugEnabled()) { OracleDatabase.LOG.debug(" - Table '" + tableName + "'"); } stmtExec.execute("drop table " + tableName + " cascade constraints"); } rs.close(); // remove all sequences if (OracleDatabase.LOG.isInfoEnabled()) { OracleDatabase.LOG.info("Remove all Sequences"); } rs = stmtSel.executeQuery("select SEQUENCE_NAME from USER_SEQUENCES"); while (rs.next()) { final String seqName = rs.getString(1); if (OracleDatabase.LOG.isDebugEnabled()) { OracleDatabase.LOG.debug(" - Sequence '" + seqName + "'"); } stmtExec.execute("drop sequence " + seqName); } rs.close(); } finally { stmtSel.close(); stmtExec.close(); } } /** * {@inheritDoc} */ @Override public OracleDatabase deleteView(final Connection _con, final String _name) throws SQLException { final Statement stmtExec = _con.createStatement(); stmtExec.execute("drop view " + _name); return this; } /** * For the database from vendor Oracle. An eFaps SQL table * is created in this steps: * <ul> * <li>sql table itself with column <code>ID</code> and unique key on the * column is created</li> * <li>sequence with same name of table and suffix <code>_SEQ</code> is * created</li> * </ul> * An eFaps sql table with parent table is created in this steps: * <ul> * <li>sql table itself with column <code>ID</code> and unique key on the * column is created</li> * <li>the foreign key to the parent table is automatically set</li> * </ul> * * @param _con SQL connection * @param _table name of the table to create * @throws SQLException if the table or sequence could not be created * @return this */ @Override public OracleDatabase createTable(final Connection _con, final String _table) throws SQLException { final Statement stmt = _con.createStatement(); try { // create table itself final StringBuilder cmd = new StringBuilder().append("create table ").append(getTableName(_table)) .append(" (").append(" ID number not null,").append(" constraint "); final String consName = getConstrainName(_table + "_UK_ID"); cmd.append(consName).append(" unique(ID)"); cmd.append(")"); stmt.executeUpdate(cmd.toString()); } catch (final EFapsException e) { e.printStackTrace(); } finally { stmt.close(); } return this; } /** * {@inheritDoc} */ @Override public OracleDatabase defineTableAutoIncrement(final Connection _con, final String _table) throws SQLException { throw new Error("not implemented"); } /** * A new id for given column of a SQL table is returned (with * sequences!). * * @param _con sql connection * @param _table sql table for which a new id must returned * @param _column sql table column for which a new id must returned * @return new ID of the used sequence * @throws SQLException if a new id could not be retrieved */ @Override public long getNewId(final Connection _con, final String _table, final String _column) throws SQLException { long ret = 0; final Statement stmt = _con.createStatement(); try { final StringBuilder cmd = new StringBuilder().append("select ").append(_table) .append("_SEQ.nextval from DUAL"); final ResultSet rs = stmt.executeQuery(cmd.toString()); if (rs.next()) { ret = rs.getLong(1); } rs.close(); } finally { stmt.close(); } return ret; } /** * {@inheritDoc} */ @Override public OracleDatabase createSequence(final Connection _con, final String _name, final long _startValue) throws SQLException { final Statement stmt = _con.createStatement(); // create sequence final StringBuilder cmd = new StringBuilder().append("create sequence ").append(_name) .append(" increment by 1 ").append(" start with ").append(_startValue).append(" nocache"); try { stmt.executeUpdate(cmd.toString()); } finally { stmt.close(); } nextSequence(_con, _name); return this; } /** * {@inheritDoc} * @throws SQLException */ @Override public OracleDatabase deleteSequence(final Connection _con, final String _name) throws SQLException { final String cmd = new StringBuilder().append("drop sequence ").append(_name).toString(); final Statement stmt = _con.createStatement(); try { stmt.executeUpdate(cmd); } finally { stmt.close(); } return this; } /** * {@inheritDoc} */ @Override @SuppressFBWarnings("SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE") public boolean existsSequence(final Connection _con, final String _name) throws SQLException { final boolean ret; final String cmd = new StringBuilder() .append("SELECT sequence_name FROM user_sequences WHERE sequence_name='") .append(_name.toLowerCase()).append("'").toString(); final Statement stmt = _con.createStatement(); try { final ResultSet resultset = stmt.executeQuery(cmd); ret = resultset.next(); resultset.close(); } finally { stmt.close(); } return ret; } /** * {@inheritDoc} */ @Override public long nextSequence(final Connection _con, final String _name) throws SQLException { final long ret; final String cmd = new StringBuilder().append("SELECT " + _name + ".nextval from dual").toString(); final Statement stmt = _con.createStatement(); try { final ResultSet resultset = stmt.executeQuery(cmd); if (resultset.next()) { ret = resultset.getLong(1); } else { throw new SQLException("fetching new value from sequence '" + _name + "' failed"); } resultset.close(); } finally { stmt.close(); } return ret; } /** * {@inheritDoc} */ @Override public OracleDatabase setSequence(final Connection _con, final String _name, final long _value) throws SQLException { deleteSequence(_con, _name); createSequence(_con, _name, _value); return this; } /** * Overwrites the original method to specify SQL statement * {@link #SQL_UNIQUE_KEYS} as replacement because the JDBC driver for * PostgreSQL does not handle matching table names. * * @param _con SQL connection * @param _sql SQL statement (not used) * @param _cache4Name map used to fetch depending on the table name the * related table information * @throws SQLException if unique keys could not be fetched * @see #SQL_UNIQUE_KEYS */ @Override protected void initTableInfoUniqueKeys(final Connection _con, final String _sql, final Map<String, TableInformation> _cache4Name) throws SQLException { super.initTableInfoUniqueKeys(_con, OracleDatabase.SQL_UNIQUE_KEYS, _cache4Name); } /** * Overwrites the original method to specify SQL statement * {@link #SQL_FOREIGN_KEYS} as replacement because the JDBC driver for * PostgreSQL does not handle matching table names. * * @param _con SQL connection * @param _sql SQL statement (not used) * @param _cache4Name map used to fetch depending on the table name the * related table information * @throws SQLException if foreign keys could not be fetched * @see #SQL_FOREIGN_KEYS */ @Override protected void initTableInfoForeignKeys(final Connection _con, final String _sql, final Map<String, TableInformation> _cache4Name) throws SQLException { super.initTableInfoForeignKeys(_con, OracleDatabase.SQL_FOREIGN_KEYS, _cache4Name); } @Override protected void initTableInfoColumns(final Connection _con, final String _sql, final Map<String, TableInformation> _cache4Name) throws SQLException { Statement stmt = null; final ResultSet rs; if (_sql == null) { rs = _con.getMetaData().getColumns(getCatalog(), getSchemaPattern(), "%", "%"); } else { stmt = _con.createStatement(); rs = stmt.executeQuery(_sql); } try { while (rs.next()) { final String tableName = rs.getString("TABLE_NAME").toUpperCase(); if (_cache4Name.containsKey(tableName)) { final String colName = rs.getString("COLUMN_NAME").toUpperCase(); final String typeName = rs.getString("TYPE_NAME").toLowerCase(); final Set<AbstractDatabase.ColumnType> colTypes = OracleDatabase.this .getReadColumnTypes(typeName); if (colTypes == null) { throw new SQLException("read unknown column type '" + typeName + "' in column '" + colName + "' for table '" + tableName + "'"); } final int size = rs.getInt("COLUMN_SIZE"); final int scale = rs.getInt("DECIMAL_DIGITS"); final boolean isNullable = !"NO".equalsIgnoreCase(rs.getString("IS_NULLABLE")); _cache4Name.get(tableName).addColInfo(colName, colTypes, size, scale, isNullable); } } } finally { rs.close(); if (stmt != null) { stmt.close(); } } } /** * {@inheritDoc} */ @Override public String getConstrainName(final String _name) throws EFapsException { return getName4DB(_name, 30); } /** * @param _name name * @param _maxLength maximum length * @return new name * @throws EFapsException on error */ protected String getName4DB(final String _name, final int _maxLength) throws EFapsException { String ret = _name; if (_name.length() > 30) { try { final byte[] buffer = _name.getBytes("UTF8"); final ByteArrayInputStream bais = new ByteArrayInputStream(buffer); final CheckedInputStream cis = new CheckedInputStream(bais, new Adler32()); final byte[] readBuffer = new byte[5]; long value = 0; while (cis.read(readBuffer) >= 0) { value = cis.getChecksum().getValue(); } final String valueSt = String.valueOf(value); ret = ret.substring(0, 30); final int sizeSuf = ret.length() - valueSt.length(); ret = ret.substring(0, sizeSuf) + value; } catch (final UnsupportedEncodingException e) { throw new EFapsException("UnsupportedEncodingException", e); } catch (final IOException e) { throw new EFapsException("IOException", e); } } return ret; } /** * {@inheritDoc} */ @Override public String getTableName(final String _name) throws EFapsException { return getName4DB(_name, 30); } /** * Returns a single " used to select columns within * SQL statements for a Oracle database.. * * @return always single reversed apostrophe */ @Override public String getColumnQuote() { return "\""; } /** * {@inheritDoc} */ @Override public String getHibernateDialect() { return "org.hibernate.dialect.Oracle10gDialect"; } /** * {@inheritDoc} */ @Override protected StringBuilder getAlterColumn(final String _columnName, final org.efaps.db.databases.AbstractDatabase.ColumnType _columnType) { final StringBuilder ret = new StringBuilder().append(" modify ").append(getColumnQuote()) .append(_columnName).append(getColumnQuote()).append(" ").append(getWriteSQLTypeName(_columnType)); return ret; } /** * {@inheritDoc} */ @Override protected StringBuilder getAlterColumnIsNotNull(final String _columnName, final boolean _isNotNull) { final StringBuilder ret = new StringBuilder().append(" modify ").append(getColumnQuote()) .append(_columnName).append(getColumnQuote()).append(" "); if (_isNotNull) { ret.append(" not null "); } else { ret.append(" null "); } return ret; } /** * {@inheritDoc} */ @Override protected boolean check4NullValues(final Connection _con, final String _tableName, final String _columnName) throws SQLException { boolean ret = true; final StringBuilder cmd = new StringBuilder(); cmd.append("select count(*) from ").append(getTableQuote()).append(_tableName).append(getTableQuote()) .append(" where ").append(getColumnQuote()).append(_columnName).append(getColumnQuote()) .append(" is null"); OracleDatabase.LOG.debug(" ..SQL> {}", cmd); final Statement stmt = _con.createStatement(); ResultSet rs = null; try { rs = stmt.executeQuery(cmd.toString()); rs.next(); ret = rs.getInt(1) > 0; } finally { if (rs != null) { rs.close(); } stmt.close(); } return ret; } /** * {@inheritDoc} */ @Override public RowProcessor getRowProcessor() { return OracleDatabase.ROWPROCESSOR; } }