@Test @Ignore public void testAlterColumn() throws Exception { String tableName = getFreeTableName(); createMyTable(tableName); DBAccessorImpl dbAccessor = injector.getInstance(DBAccessorImpl.class); ResultSet rs; DBColumnInfo fromColumn; DBColumnInfo toColumn; Statement statement = dbAccessor.getConnection().createStatement(); final String dataString = "Data for inserting column."; toColumn = new DBColumnInfo("name", String.class, 500, null, true); statement.execute( String.format("INSERT INTO %s(id, name) VALUES (1, '%s')", tableName, dataString)); dbAccessor.alterColumn(tableName, toColumn); rs = statement.executeQuery( String.format("SELECT name FROM %s", tableName)); while (rs.next()) { ResultSetMetaData rsm = rs.getMetaData(); assertEquals(rs.getString(toColumn.getName()), dataString); assertEquals(rsm.getColumnTypeName(1), "VARCHAR"); assertEquals(rsm.getColumnDisplaySize(1), 500); } rs.close(); toColumn = new DBColumnInfo("name", java.sql.Clob.class, 999, null, true); dbAccessor.alterColumn(tableName, toColumn); rs = statement.executeQuery( String.format("SELECT name FROM %s", tableName)); while (rs.next()) { ResultSetMetaData rsm = rs.getMetaData(); Clob clob = rs.getClob(toColumn.getName()); assertEquals(dataString, clob.getSubString(1, (int) clob.length())); assertEquals("CLOB", rsm.getColumnTypeName(1)); } rs.close(); toColumn = new DBColumnInfo("name_blob_to_clob", java.sql.Clob.class, 567, null, true); fromColumn = new DBColumnInfo("name_blob_to_clob", byte[].class, 20000, null, true); dbAccessor.addColumn(tableName, fromColumn); String sql = String.format( "insert into %s(id, name_blob_to_clob) values (2, ?)", tableName); PreparedStatement preparedStatement = dbAccessor.getConnection().prepareStatement( sql); preparedStatement.setBinaryStream(1, new ByteArrayInputStream(dataString.getBytes()), dataString.getBytes().length); preparedStatement.executeUpdate(); preparedStatement.close(); dbAccessor.alterColumn(tableName, toColumn); rs = statement.executeQuery( String.format("SELECT name_blob_to_clob FROM %s WHERE id=2", tableName)); while (rs.next()) { ResultSetMetaData rsm = rs.getMetaData(); Clob clob = rs.getClob(toColumn.getName()); assertEquals(clob.getSubString(1, (int) clob.length()), dataString); assertEquals(rsm.getColumnTypeName(1), "CLOB"); assertEquals(rsm.getColumnDisplaySize(1), 567); } rs.close(); toColumn = new DBColumnInfo("name_blob_to_clob", char[].class, 1500, null, true); dbAccessor.alterColumn(tableName, toColumn); rs = statement.executeQuery( String.format("SELECT name_blob_to_clob FROM %s WHERE id=2", tableName)); while (rs.next()) { ResultSetMetaData rsm = rs.getMetaData(); Clob clob = rs.getClob(toColumn.getName()); assertEquals(clob.getSubString(1, (int) clob.length()), dataString); assertEquals(rsm.getColumnTypeName(1), "CLOB"); assertEquals(rsm.getColumnDisplaySize(1), 1500); } rs.close(); dbAccessor.dropTable(tableName); }
@Override public void alterColumn(String tableName, DBColumnInfo columnInfo) throws SQLException { if (dbmsHelper.supportsColumnTypeChange()) { String statement = dbmsHelper.getAlterColumnStatement(tableName, columnInfo); executeQuery(statement); } else { DBColumnInfo columnInfoTmp = new DBColumnInfo( columnInfo.getName() + "_TMP", columnInfo.getType(), columnInfo.getLength()); String statement = dbmsHelper.getAddColumnStatement(tableName, columnInfoTmp); executeQuery(statement); updateTable(tableName, columnInfo, columnInfoTmp); dropColumn(tableName, columnInfo.getName()); renameColumn(tableName, columnInfoTmp.getName(), columnInfo); } if (isColumnNullable(tableName, columnInfo.getName()) != columnInfo.isNullable()) { setColumnNullable(tableName, columnInfo, columnInfo.isNullable()); } }
DBAccessorImpl implements DBAccessor { @Override public void alterColumn(String tableName, DBColumnInfo columnInfo) throws SQLException { if (dbmsHelper.supportsColumnTypeChange()) { String statement = dbmsHelper.getAlterColumnStatement(tableName, columnInfo); executeQuery(statement); } else { DBColumnInfo columnInfoTmp = new DBColumnInfo( columnInfo.getName() + "_TMP", columnInfo.getType(), columnInfo.getLength()); String statement = dbmsHelper.getAddColumnStatement(tableName, columnInfoTmp); executeQuery(statement); updateTable(tableName, columnInfo, columnInfoTmp); dropColumn(tableName, columnInfo.getName()); renameColumn(tableName, columnInfoTmp.getName(), columnInfo); } if (isColumnNullable(tableName, columnInfo.getName()) != columnInfo.isNullable()) { setColumnNullable(tableName, columnInfo, columnInfo.isNullable()); } } }
DBAccessorImpl implements DBAccessor { @Override public void alterColumn(String tableName, DBColumnInfo columnInfo) throws SQLException { if (dbmsHelper.supportsColumnTypeChange()) { String statement = dbmsHelper.getAlterColumnStatement(tableName, columnInfo); executeQuery(statement); } else { DBColumnInfo columnInfoTmp = new DBColumnInfo( columnInfo.getName() + "_TMP", columnInfo.getType(), columnInfo.getLength()); String statement = dbmsHelper.getAddColumnStatement(tableName, columnInfoTmp); executeQuery(statement); updateTable(tableName, columnInfo, columnInfoTmp); dropColumn(tableName, columnInfo.getName()); renameColumn(tableName, columnInfoTmp.getName(), columnInfo); } if (isColumnNullable(tableName, columnInfo.getName()) != columnInfo.isNullable()) { setColumnNullable(tableName, columnInfo, columnInfo.isNullable()); } } @Inject  DBAccessorImpl(Configuration configuration); }
DBAccessorImpl implements DBAccessor { @Override public void alterColumn(String tableName, DBColumnInfo columnInfo) throws SQLException { if (dbmsHelper.supportsColumnTypeChange()) { String statement = dbmsHelper.getAlterColumnStatement(tableName, columnInfo); executeQuery(statement); } else { DBColumnInfo columnInfoTmp = new DBColumnInfo( columnInfo.getName() + "_TMP", columnInfo.getType(), columnInfo.getLength()); String statement = dbmsHelper.getAddColumnStatement(tableName, columnInfoTmp); executeQuery(statement); updateTable(tableName, columnInfo, columnInfoTmp); dropColumn(tableName, columnInfo.getName()); renameColumn(tableName, columnInfoTmp.getName(), columnInfo); } if (isColumnNullable(tableName, columnInfo.getName()) != columnInfo.isNullable()) { setColumnNullable(tableName, columnInfo, columnInfo.isNullable()); } } @Inject  DBAccessorImpl(Configuration configuration); @Override Connection getConnection(); @Override Connection getNewConnection(); @Override String quoteObjectName(String name); @Override void createTable(String tableName, List<DBColumnInfo> columnInfo,
      String... primaryKeyColumns); @Override boolean tableExists(String tableName); @Override DbType getDbType(); @Override boolean tableHasData(String tableName); @Override boolean tableHasColumn(String tableName, String columnName); @Override boolean tableHasColumn(String tableName, String... columnName); @Override boolean tableHasForeignKey(String tableName, String fkName); String getCheckedForeignKey(String tableName, String fkName); @Override boolean tableHasForeignKey(String tableName, String refTableName,
          String columnName, String refColumnName); @Override boolean tableHasForeignKey(String tableName, String referenceTableName, String[] keyColumns,
          String[] referenceColumns); @Override boolean tableHasIndex(String tableName, boolean unique, String indexName); @Override void createIndex(String indexName, String tableName,
          String... columnNames); @Override void createIndex(String indexName, String tableName, boolean isUnique,
                          String... columnNames); @Override void addFKConstraint(String tableName, String constraintName,
          String keyColumn, String referenceTableName,
          String referenceColumn, boolean ignoreFailure); @Override void addFKConstraint(String tableName, String constraintName,
          String keyColumn, String referenceTableName,
          String referenceColumn, boolean shouldCascadeOnDelete,
          boolean ignoreFailure); @Override void addFKConstraint(String tableName, String constraintName,
          String[] keyColumns, String referenceTableName,
          String[] referenceColumns,
          boolean ignoreFailure); @Override void addFKConstraint(String tableName, String constraintName,
          String[] keyColumns, String referenceTableName,
          String[] referenceColumns, boolean shouldCascadeOnDelete,
          boolean ignoreFailure); boolean tableHasConstraint(String tableName, String constraintName); @Override void addUniqueConstraint(String tableName, String constraintName, String... columnNames); @Override void addPKConstraint(String tableName, String constraintName, boolean ignoreErrors, String... columnName); @Override void addPKConstraint(String tableName, String constraintName, String... columnName); @Override void renameColumn(String tableName, String oldColumnName,
          DBColumnInfo columnInfo); @Override void addColumn(String tableName, DBColumnInfo columnInfo); @Override void alterColumn(String tableName, DBColumnInfo columnInfo); @Override void updateTable(String tableName, DBColumnInfo columnNameFrom,
          DBColumnInfo columnNameTo); @Override boolean insertRow(String tableName, String[] columnNames, String[] values, boolean ignoreFailure); @Override boolean insertRowIfMissing(String tableName, String[] columnNames, String[] values, boolean ignoreFailure); @Override int updateTable(String tableName, String columnName, Object value,
          String whereClause); @Override int executeUpdate(String query); @Override int executeUpdate(String query, boolean ignoreErrors); @Override void executeQuery(String query, String tableName, String hasColumnName); @Override void executeQuery(String query); @Override void executeQuery(String query, boolean ignoreFailure); @Override void dropTable(String tableName); @Override void truncateTable(String tableName); @Override void dropColumn(String tableName, String columnName); @Override void dropSequence(String sequenceName); @Override void dropFKConstraint(String tableName, String constraintName); @Override void dropFKConstraint(String tableName, String constraintName, boolean ignoreFailure); @Override void dropUniqueConstraint(String tableName, String constraintName, boolean ignoreFailure); @Override void dropUniqueConstraint(String tableName, String constraintName); @Override void dropPKConstraint(String tableName, String constraintName, String columnName, boolean cascade); @Override void dropPKConstraint(String tableName, String constraintName, boolean ignoreFailure, boolean cascade); @Override void dropPKConstraint(String tableName, String constraintName, boolean cascade); @Override /** * Execute script with autocommit and error tolerance, like psql and sqlplus * do by default */ void executeScript(String filePath); @Override DatabaseSession getNewDatabaseSession(); @Override boolean tableHasPrimaryKey(String tableName, String columnName); @Override int getColumnType(String tableName, String columnName); @Override Class getColumnClass(String tableName, String columnName); @Override boolean isColumnNullable(String tableName, String columnName); @Override void setColumnNullable(String tableName, DBAccessor.DBColumnInfo columnInfo, boolean nullable); @Override void setColumnNullable(String tableName, String columnName, boolean nullable); @Override void changeColumnType(String tableName, String columnName, Class fromType, Class toType); @Override List<String> getIndexesList(String tableName, boolean unique); @Override String getPrimaryKeyConstraintName(String tableName); @Override void dropPKConstraint(String tableName, String defaultConstraintName); @Override void addDefaultConstraint(String tableName, DBColumnInfo column); }
DBAccessorImpl implements DBAccessor { @Override public void alterColumn(String tableName, DBColumnInfo columnInfo) throws SQLException { if (dbmsHelper.supportsColumnTypeChange()) { String statement = dbmsHelper.getAlterColumnStatement(tableName, columnInfo); executeQuery(statement); } else { DBColumnInfo columnInfoTmp = new DBColumnInfo( columnInfo.getName() + "_TMP", columnInfo.getType(), columnInfo.getLength()); String statement = dbmsHelper.getAddColumnStatement(tableName, columnInfoTmp); executeQuery(statement); updateTable(tableName, columnInfo, columnInfoTmp); dropColumn(tableName, columnInfo.getName()); renameColumn(tableName, columnInfoTmp.getName(), columnInfo); } if (isColumnNullable(tableName, columnInfo.getName()) != columnInfo.isNullable()) { setColumnNullable(tableName, columnInfo, columnInfo.isNullable()); } } @Inject  DBAccessorImpl(Configuration configuration); @Override Connection getConnection(); @Override Connection getNewConnection(); @Override String quoteObjectName(String name); @Override void createTable(String tableName, List<DBColumnInfo> columnInfo,
      String... primaryKeyColumns); @Override boolean tableExists(String tableName); @Override DbType getDbType(); @Override boolean tableHasData(String tableName); @Override boolean tableHasColumn(String tableName, String columnName); @Override boolean tableHasColumn(String tableName, String... columnName); @Override boolean tableHasForeignKey(String tableName, String fkName); String getCheckedForeignKey(String tableName, String fkName); @Override boolean tableHasForeignKey(String tableName, String refTableName,
          String columnName, String refColumnName); @Override boolean tableHasForeignKey(String tableName, String referenceTableName, String[] keyColumns,
          String[] referenceColumns); @Override boolean tableHasIndex(String tableName, boolean unique, String indexName); @Override void createIndex(String indexName, String tableName,
          String... columnNames); @Override void createIndex(String indexName, String tableName, boolean isUnique,
                          String... columnNames); @Override void addFKConstraint(String tableName, String constraintName,
          String keyColumn, String referenceTableName,
          String referenceColumn, boolean ignoreFailure); @Override void addFKConstraint(String tableName, String constraintName,
          String keyColumn, String referenceTableName,
          String referenceColumn, boolean shouldCascadeOnDelete,
          boolean ignoreFailure); @Override void addFKConstraint(String tableName, String constraintName,
          String[] keyColumns, String referenceTableName,
          String[] referenceColumns,
          boolean ignoreFailure); @Override void addFKConstraint(String tableName, String constraintName,
          String[] keyColumns, String referenceTableName,
          String[] referenceColumns, boolean shouldCascadeOnDelete,
          boolean ignoreFailure); boolean tableHasConstraint(String tableName, String constraintName); @Override void addUniqueConstraint(String tableName, String constraintName, String... columnNames); @Override void addPKConstraint(String tableName, String constraintName, boolean ignoreErrors, String... columnName); @Override void addPKConstraint(String tableName, String constraintName, String... columnName); @Override void renameColumn(String tableName, String oldColumnName,
          DBColumnInfo columnInfo); @Override void addColumn(String tableName, DBColumnInfo columnInfo); @Override void alterColumn(String tableName, DBColumnInfo columnInfo); @Override void updateTable(String tableName, DBColumnInfo columnNameFrom,
          DBColumnInfo columnNameTo); @Override boolean insertRow(String tableName, String[] columnNames, String[] values, boolean ignoreFailure); @Override boolean insertRowIfMissing(String tableName, String[] columnNames, String[] values, boolean ignoreFailure); @Override int updateTable(String tableName, String columnName, Object value,
          String whereClause); @Override int executeUpdate(String query); @Override int executeUpdate(String query, boolean ignoreErrors); @Override void executeQuery(String query, String tableName, String hasColumnName); @Override void executeQuery(String query); @Override void executeQuery(String query, boolean ignoreFailure); @Override void dropTable(String tableName); @Override void truncateTable(String tableName); @Override void dropColumn(String tableName, String columnName); @Override void dropSequence(String sequenceName); @Override void dropFKConstraint(String tableName, String constraintName); @Override void dropFKConstraint(String tableName, String constraintName, boolean ignoreFailure); @Override void dropUniqueConstraint(String tableName, String constraintName, boolean ignoreFailure); @Override void dropUniqueConstraint(String tableName, String constraintName); @Override void dropPKConstraint(String tableName, String constraintName, String columnName, boolean cascade); @Override void dropPKConstraint(String tableName, String constraintName, boolean ignoreFailure, boolean cascade); @Override void dropPKConstraint(String tableName, String constraintName, boolean cascade); @Override /** * Execute script with autocommit and error tolerance, like psql and sqlplus * do by default */ void executeScript(String filePath); @Override DatabaseSession getNewDatabaseSession(); @Override boolean tableHasPrimaryKey(String tableName, String columnName); @Override int getColumnType(String tableName, String columnName); @Override Class getColumnClass(String tableName, String columnName); @Override boolean isColumnNullable(String tableName, String columnName); @Override void setColumnNullable(String tableName, DBAccessor.DBColumnInfo columnInfo, boolean nullable); @Override void setColumnNullable(String tableName, String columnName, boolean nullable); @Override void changeColumnType(String tableName, String columnName, Class fromType, Class toType); @Override List<String> getIndexesList(String tableName, boolean unique); @Override String getPrimaryKeyConstraintName(String tableName); @Override void dropPKConstraint(String tableName, String defaultConstraintName); @Override void addDefaultConstraint(String tableName, DBColumnInfo column);  }