Example usage for java.sql ResultSetMetaData columnNullable

List of usage examples for java.sql ResultSetMetaData columnNullable

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSetMetaData
  6. columnNullable

Introduction

In this page you can find the example usage for java.sql ResultSetMetaData columnNullable.

Prototype

int columnNullable

To view the source code for java.sql ResultSetMetaData columnNullable.

Click Source Link

Document

The constant indicating that a column allows NULL values.

Usage

From source file:Main.java

public static void main(String[] args) throws Exception {
    Class.forName(DRIVER);/*from ww  w .  j  av  a 2s .  c o m*/
    Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);

    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery("SELECT id, username FROM users");
    ResultSetMetaData metadata = resultSet.getMetaData();
    int nullability = metadata.isNullable(1);

    if (nullability == ResultSetMetaData.columnNullable) {
        System.out.println("Columns ID can have a null value");
    } else if (nullability == ResultSetMetaData.columnNoNulls) {
        System.out.println("Columns ID does not allowed to have a null value");
    } else if (nullability == ResultSetMetaData.columnNullableUnknown) {
        System.out.println("Nullability unknown");
    }
}

From source file:org.jtalks.poulpe.util.databasebackup.persistence.DbTableData.java

/**
 * Constructs a new ColumnMetaData objects from given ResultSetMetaData with provided column default values map and
 * the object's index.// w  ww.  j  av  a  2 s. c om
 * 
 * @param rsmd
 *            A ResultSetMetaData which contains meta information about all columns for the table.
 * @param columnDefaultValues
 *            A map of possibly defined values by default for columns.
 * @param i
 *            Index of column which should be constructed.
 * @return A constructed ColumnMetaData object.
 * @throws SQLException
 *             Is thrown in case any errors during work with database occur.
 */
private ColumnMetaData getColumnMetaData(ResultSetMetaData rsmd, Map<String, String> columnDefaultValues, int i)
        throws SQLException {
    SqlTypes columnType = SqlTypes.getSqlTypeByJdbcSqlType(rsmd.getColumnType(i));

    ColumnMetaData column = ColumnMetaData.getInstance(rsmd.getColumnName(i), columnType)
            .setNullable(rsmd.isNullable(i) == ResultSetMetaData.columnNullable)
            .setAutoincrement(rsmd.isAutoIncrement(i));
    if (columnDefaultValues.containsKey(rsmd.getColumnName(i))) {
        column.setDefaultValue(columnDefaultValues.get(rsmd.getColumnName(i)));
    }
    if (columnType.isHasSize()) {
        column.setSize(rsmd.getColumnDisplaySize(i));
    }
    return column;
}

From source file:com.egt.core.db.xdp.RecursoCachedRowSetDataProvider.java

private boolean isNullableColumn(String fieldId) {
    try {//from   ww w.ja  v a  2s  . co  m
        ResultSetMetaData rsmd = this.getCachedRowSet().getMetaData();
        int numberOfColumns = rsmd.getColumnCount();
        for (int i = 1; i <= numberOfColumns; i++) {
            if (rsmd.getColumnName(i).equalsIgnoreCase(fieldId)) {
                if (rsmd.isNullable(i) == ResultSetMetaData.columnNullable) {
                    return true;
                } else {
                    return false;
                }
            }
        }
    } catch (Exception ex) {
        TLC.getBitacora().fatal(ex);
    }
    return false;
}

From source file:ips1ap101.lib.core.db.xdp.RecursoCachedRowSetDataProvider.java

private boolean isNullableColumn(String fieldId) {
    try {/* w ww  .j a  va 2s  . co m*/
        ResultSetMetaData rsmd = getCachedRowSet().getMetaData();
        int numberOfColumns = rsmd.getColumnCount();
        for (int i = 1; i <= numberOfColumns; i++) {
            if (rsmd.getColumnName(i).equalsIgnoreCase(fieldId)) {
                if (rsmd.isNullable(i) == ResultSetMetaData.columnNullable) {
                    return true;
                } else {
                    return false;
                }
            }
        }
    } catch (Exception ex) {
        TLC.getBitacora().fatal(ex);
    }
    return false;
}

From source file:net.starschema.clouddb.jdbc.BQResultsetMetaData.java

/**
 * <p>/*from  w w  w  .  ja  v a  2  s.  co  m*/
 * <h1>Implementation Details:</h1><br>
 * Always returns ResultSetMetaData.columnNullable
 * </p>
 *
 * @return ResultSetMetaData.columnNullable
 */
@Override
public int isNullable(int column) throws SQLException {
    return ResultSetMetaData.columnNullable;
}

From source file:org.acmsl.queryj.customsql.handlers.customsqlvalidation.RetrieveResultPropertiesHandler.java

/**
 * Creates a property from given {@link ResultSetMetaData}.
 * @param metadata the result set metadata.
 * @param index the index./*from  ww  w  .j  a  v a2 s.  co m*/
 * @return the associated {@link Property}.
 * @throws SQLException if the property information is unavailable.
 */
@NotNull
protected Property<String> createPropertyFrom(@NotNull final ResultSetMetaData metadata, final int index)
        throws SQLException {
    @NotNull
    final String t_strColumnName = metadata.getColumnName(index);
    @NotNull
    final String t_strType = metadata.getColumnTypeName(index);
    final boolean t_bNullable = (metadata.isNullable(index) == ResultSetMetaData.columnNullable);

    return new PropertyElement<>(t_strColumnName, t_strColumnName, index, t_strType, t_bNullable);
}

From source file:org.apache.hadoop.hive.jdbc.TestJdbcDriver.java

public void testResultSetMetaData() throws SQLException {
    Statement stmt = con.createStatement();

    ResultSet res = stmt.executeQuery("select c1, c2, c3, c4, c5 as a, c6, c7, c8, c9, c10, c11, c12, "
            + "c1*2, sentences(null, null, null) as b, c17, c18, c20 from " + dataTypeTableName + " limit 1");
    ResultSetMetaData meta = res.getMetaData();

    ResultSet colRS = con.getMetaData().getColumns(null, null, dataTypeTableName.toLowerCase(), null);

    assertEquals(17, meta.getColumnCount());

    assertTrue(colRS.next());/*  ww w .j  a v a 2s .c o m*/

    assertEquals("c1", meta.getColumnName(1));
    assertEquals(Types.INTEGER, meta.getColumnType(1));
    assertEquals("int", meta.getColumnTypeName(1));
    assertEquals(11, meta.getColumnDisplaySize(1));
    assertEquals(10, meta.getPrecision(1));
    assertEquals(0, meta.getScale(1));

    assertEquals("c1", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.INTEGER, colRS.getInt("DATA_TYPE"));
    assertEquals("int", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(1), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(1), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c2", meta.getColumnName(2));
    assertEquals("boolean", meta.getColumnTypeName(2));
    assertEquals(Types.BOOLEAN, meta.getColumnType(2));
    assertEquals(1, meta.getColumnDisplaySize(2));
    assertEquals(1, meta.getPrecision(2));
    assertEquals(0, meta.getScale(2));

    assertEquals("c2", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.BOOLEAN, colRS.getInt("DATA_TYPE"));
    assertEquals("boolean", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(2), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(2), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c3", meta.getColumnName(3));
    assertEquals(Types.DOUBLE, meta.getColumnType(3));
    assertEquals("double", meta.getColumnTypeName(3));
    assertEquals(25, meta.getColumnDisplaySize(3));
    assertEquals(15, meta.getPrecision(3));
    assertEquals(15, meta.getScale(3));

    assertEquals("c3", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.DOUBLE, colRS.getInt("DATA_TYPE"));
    assertEquals("double", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(3), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(3), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c4", meta.getColumnName(4));
    assertEquals(Types.VARCHAR, meta.getColumnType(4));
    assertEquals("string", meta.getColumnTypeName(4));
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(4));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(4));
    assertEquals(0, meta.getScale(4));

    assertEquals("c4", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE"));
    assertEquals("string", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(4), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(4), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("a", meta.getColumnName(5));
    assertEquals(Types.VARCHAR, meta.getColumnType(5));
    assertEquals("string", meta.getColumnTypeName(5));
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(5));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(5));
    assertEquals(0, meta.getScale(5));

    assertEquals("c5", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE"));
    assertEquals("array<int>", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(5), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(5), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c6", meta.getColumnName(6));
    assertEquals(Types.VARCHAR, meta.getColumnType(6));
    assertEquals("string", meta.getColumnTypeName(6));
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(6));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(6));
    assertEquals(0, meta.getScale(6));

    assertEquals("c6", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE"));
    assertEquals("map<int,string>", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(6), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(6), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c7", meta.getColumnName(7));
    assertEquals(Types.VARCHAR, meta.getColumnType(7));
    assertEquals("string", meta.getColumnTypeName(7));
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(7));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(7));
    assertEquals(0, meta.getScale(7));

    assertEquals("c7", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE"));
    assertEquals("map<string,string>", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(7), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(7), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c8", meta.getColumnName(8));
    assertEquals(Types.VARCHAR, meta.getColumnType(8));
    assertEquals("string", meta.getColumnTypeName(8));
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(8));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(8));
    assertEquals(0, meta.getScale(8));

    assertEquals("c8", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE"));
    assertEquals("struct<r:string,s:int,t:double>", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(8), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(8), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c9", meta.getColumnName(9));
    assertEquals(Types.TINYINT, meta.getColumnType(9));
    assertEquals("tinyint", meta.getColumnTypeName(9));
    assertEquals(4, meta.getColumnDisplaySize(9));
    assertEquals(3, meta.getPrecision(9));
    assertEquals(0, meta.getScale(9));

    assertEquals("c9", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.TINYINT, colRS.getInt("DATA_TYPE"));
    assertEquals("tinyint", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(9), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(9), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c10", meta.getColumnName(10));
    assertEquals(Types.SMALLINT, meta.getColumnType(10));
    assertEquals("smallint", meta.getColumnTypeName(10));
    assertEquals(6, meta.getColumnDisplaySize(10));
    assertEquals(5, meta.getPrecision(10));
    assertEquals(0, meta.getScale(10));

    assertEquals("c10", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.SMALLINT, colRS.getInt("DATA_TYPE"));
    assertEquals("smallint", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(10), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(10), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c11", meta.getColumnName(11));
    assertEquals(Types.FLOAT, meta.getColumnType(11));
    assertEquals("float", meta.getColumnTypeName(11));
    assertEquals(24, meta.getColumnDisplaySize(11));
    assertEquals(7, meta.getPrecision(11));
    assertEquals(7, meta.getScale(11));

    assertEquals("c11", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.FLOAT, colRS.getInt("DATA_TYPE"));
    assertEquals("float", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(11), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(11), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c12", meta.getColumnName(12));
    assertEquals(Types.BIGINT, meta.getColumnType(12));
    assertEquals("bigint", meta.getColumnTypeName(12));
    assertEquals(20, meta.getColumnDisplaySize(12));
    assertEquals(19, meta.getPrecision(12));
    assertEquals(0, meta.getScale(12));

    assertEquals("c12", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.BIGINT, colRS.getInt("DATA_TYPE"));
    assertEquals("bigint", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(12), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(12), colRS.getInt("DECIMAL_DIGITS"));

    assertEquals("_c12", meta.getColumnName(13));
    assertEquals(Types.INTEGER, meta.getColumnType(13));
    assertEquals("int", meta.getColumnTypeName(13));
    assertEquals(11, meta.getColumnDisplaySize(13));
    assertEquals(10, meta.getPrecision(13));
    assertEquals(0, meta.getScale(13));

    assertEquals("b", meta.getColumnName(14));
    assertEquals(Types.VARCHAR, meta.getColumnType(14));
    assertEquals("string", meta.getColumnTypeName(14));
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(14));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(14));
    assertEquals(0, meta.getScale(14));

    assertEquals("c17", meta.getColumnName(15));
    assertEquals(Types.TIMESTAMP, meta.getColumnType(15));
    assertEquals("timestamp", meta.getColumnTypeName(15));
    assertEquals(29, meta.getColumnDisplaySize(15));
    assertEquals(29, meta.getPrecision(15));
    assertEquals(9, meta.getScale(15));

    assertEquals("c18", meta.getColumnName(16));
    assertEquals(Types.DECIMAL, meta.getColumnType(16));
    assertEquals("decimal", meta.getColumnTypeName(16));
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(16));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(16));
    assertEquals(Integer.MAX_VALUE, meta.getScale(16));

    assertEquals("c20", meta.getColumnName(17));
    assertEquals(Types.DATE, meta.getColumnType(17));
    assertEquals("date", meta.getColumnTypeName(17));
    assertEquals(10, meta.getColumnDisplaySize(17));
    assertEquals(10, meta.getPrecision(17));
    assertEquals(0, meta.getScale(17));

    for (int i = 1; i <= meta.getColumnCount(); i++) {
        assertFalse(meta.isAutoIncrement(i));
        assertFalse(meta.isCurrency(i));
        assertEquals(ResultSetMetaData.columnNullable, meta.isNullable(i));
    }
}

From source file:org.apache.hive.jdbc.TestJdbcDriver2.java

@Test
public void testResultSetMetaData() throws SQLException {
    Statement stmt = con.createStatement();

    ResultSet res = stmt.executeQuery("select c1, c2, c3, c4, c5 as a, c6, c7, c8, c9, c10, c11, c12, "
            + "c1*2, sentences(null, null, null) as b, c17, c18, c20, c21, c22, c23 from " + dataTypeTableName
            + " limit 1");
    ResultSetMetaData meta = res.getMetaData();

    ResultSet colRS = con.getMetaData().getColumns(null, null, dataTypeTableName.toLowerCase(), null);

    assertEquals(20, meta.getColumnCount());

    assertTrue(colRS.next());/*from   w  ww  .ja v a  2 s  .c  o m*/

    assertEquals("c1", meta.getColumnName(1));
    assertEquals(Types.INTEGER, meta.getColumnType(1));
    assertEquals("int", meta.getColumnTypeName(1));
    assertEquals(11, meta.getColumnDisplaySize(1));
    assertEquals(10, meta.getPrecision(1));
    assertEquals(0, meta.getScale(1));

    assertEquals("c1", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.INTEGER, colRS.getInt("DATA_TYPE"));
    assertEquals("int", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(1), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(1), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c2", meta.getColumnName(2));
    assertEquals("boolean", meta.getColumnTypeName(2));
    assertEquals(Types.BOOLEAN, meta.getColumnType(2));
    assertEquals(1, meta.getColumnDisplaySize(2));
    assertEquals(1, meta.getPrecision(2));
    assertEquals(0, meta.getScale(2));

    assertEquals("c2", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.BOOLEAN, colRS.getInt("DATA_TYPE"));
    assertEquals("boolean", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getScale(2), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c3", meta.getColumnName(3));
    assertEquals(Types.DOUBLE, meta.getColumnType(3));
    assertEquals("double", meta.getColumnTypeName(3));
    assertEquals(25, meta.getColumnDisplaySize(3));
    assertEquals(15, meta.getPrecision(3));
    assertEquals(15, meta.getScale(3));

    assertEquals("c3", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.DOUBLE, colRS.getInt("DATA_TYPE"));
    assertEquals("double", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(3), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(3), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c4", meta.getColumnName(4));
    assertEquals(Types.VARCHAR, meta.getColumnType(4));
    assertEquals("string", meta.getColumnTypeName(4));
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(4));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(4));
    assertEquals(0, meta.getScale(4));

    assertEquals("c4", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE"));
    assertEquals("string", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(4), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(4), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("a", meta.getColumnName(5));
    assertEquals(Types.ARRAY, meta.getColumnType(5));
    assertEquals("array", meta.getColumnTypeName(5));
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(5));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(5));
    assertEquals(0, meta.getScale(5));

    assertEquals("c5", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.ARRAY, colRS.getInt("DATA_TYPE"));
    assertEquals("array<int>", colRS.getString("TYPE_NAME").toLowerCase());

    assertTrue(colRS.next());

    assertEquals("c6", meta.getColumnName(6));
    assertEquals(Types.JAVA_OBJECT, meta.getColumnType(6));
    assertEquals("map", meta.getColumnTypeName(6));
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(6));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(6));
    assertEquals(0, meta.getScale(6));

    assertEquals("c6", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.JAVA_OBJECT, colRS.getInt("DATA_TYPE"));
    assertEquals("map<int,string>", colRS.getString("TYPE_NAME").toLowerCase());

    assertTrue(colRS.next());

    assertEquals("c7", meta.getColumnName(7));
    assertEquals(Types.JAVA_OBJECT, meta.getColumnType(7));
    assertEquals("map", meta.getColumnTypeName(7));
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(7));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(7));
    assertEquals(0, meta.getScale(7));

    assertEquals("c7", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.JAVA_OBJECT, colRS.getInt("DATA_TYPE"));
    assertEquals("map<string,string>", colRS.getString("TYPE_NAME").toLowerCase());

    assertTrue(colRS.next());

    assertEquals("c8", meta.getColumnName(8));
    assertEquals(Types.STRUCT, meta.getColumnType(8));
    assertEquals("struct", meta.getColumnTypeName(8));
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(8));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(8));
    assertEquals(0, meta.getScale(8));

    assertEquals("c8", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.STRUCT, colRS.getInt("DATA_TYPE"));
    assertEquals("struct<r:string,s:int,t:double>", colRS.getString("TYPE_NAME").toLowerCase());

    assertTrue(colRS.next());

    assertEquals("c9", meta.getColumnName(9));
    assertEquals(Types.TINYINT, meta.getColumnType(9));
    assertEquals("tinyint", meta.getColumnTypeName(9));
    assertEquals(4, meta.getColumnDisplaySize(9));
    assertEquals(3, meta.getPrecision(9));
    assertEquals(0, meta.getScale(9));

    assertEquals("c9", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.TINYINT, colRS.getInt("DATA_TYPE"));
    assertEquals("tinyint", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(9), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(9), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c10", meta.getColumnName(10));
    assertEquals(Types.SMALLINT, meta.getColumnType(10));
    assertEquals("smallint", meta.getColumnTypeName(10));
    assertEquals(6, meta.getColumnDisplaySize(10));
    assertEquals(5, meta.getPrecision(10));
    assertEquals(0, meta.getScale(10));

    assertEquals("c10", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.SMALLINT, colRS.getInt("DATA_TYPE"));
    assertEquals("smallint", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(10), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(10), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c11", meta.getColumnName(11));
    assertEquals(Types.FLOAT, meta.getColumnType(11));
    assertEquals("float", meta.getColumnTypeName(11));
    assertEquals(24, meta.getColumnDisplaySize(11));
    assertEquals(7, meta.getPrecision(11));
    assertEquals(7, meta.getScale(11));

    assertEquals("c11", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.FLOAT, colRS.getInt("DATA_TYPE"));
    assertEquals("float", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(11), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(11), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c12", meta.getColumnName(12));
    assertEquals(Types.BIGINT, meta.getColumnType(12));
    assertEquals("bigint", meta.getColumnTypeName(12));
    assertEquals(20, meta.getColumnDisplaySize(12));
    assertEquals(19, meta.getPrecision(12));
    assertEquals(0, meta.getScale(12));

    assertEquals("c12", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.BIGINT, colRS.getInt("DATA_TYPE"));
    assertEquals("bigint", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(12), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(12), colRS.getInt("DECIMAL_DIGITS"));

    assertEquals("_c12", meta.getColumnName(13));
    assertEquals(Types.INTEGER, meta.getColumnType(13));
    assertEquals("int", meta.getColumnTypeName(13));
    assertEquals(11, meta.getColumnDisplaySize(13));
    assertEquals(10, meta.getPrecision(13));
    assertEquals(0, meta.getScale(13));

    assertEquals("b", meta.getColumnName(14));
    assertEquals(Types.ARRAY, meta.getColumnType(14));
    assertEquals("array", meta.getColumnTypeName(14));
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(14));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(14));
    assertEquals(0, meta.getScale(14));

    // Move the result of getColumns() forward to match the columns of the query
    assertTrue(colRS.next()); // c13
    assertTrue(colRS.next()); // c14
    assertTrue(colRS.next()); // c15
    assertTrue(colRS.next()); // c16
    assertTrue(colRS.next()); // c17

    assertEquals("c17", meta.getColumnName(15));
    assertEquals(Types.TIMESTAMP, meta.getColumnType(15));
    assertEquals("timestamp", meta.getColumnTypeName(15));
    assertEquals(29, meta.getColumnDisplaySize(15));
    assertEquals(29, meta.getPrecision(15));
    assertEquals(9, meta.getScale(15));

    assertEquals("c17", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.TIMESTAMP, colRS.getInt("DATA_TYPE"));
    assertEquals("timestamp", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(15), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(15), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c18", meta.getColumnName(16));
    assertEquals(Types.DECIMAL, meta.getColumnType(16));
    assertEquals("decimal", meta.getColumnTypeName(16));
    assertEquals(18, meta.getColumnDisplaySize(16));
    assertEquals(16, meta.getPrecision(16));
    assertEquals(7, meta.getScale(16));

    assertEquals("c18", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.DECIMAL, colRS.getInt("DATA_TYPE"));
    assertEquals("decimal", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(16), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(16), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next()); // skip c19, since not selected by query
    assertTrue(colRS.next());

    assertEquals("c20", meta.getColumnName(17));
    assertEquals(Types.DATE, meta.getColumnType(17));
    assertEquals("date", meta.getColumnTypeName(17));
    assertEquals(10, meta.getColumnDisplaySize(17));
    assertEquals(10, meta.getPrecision(17));
    assertEquals(0, meta.getScale(17));

    assertEquals("c20", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.DATE, colRS.getInt("DATA_TYPE"));
    assertEquals("date", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(17), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(17), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c21", meta.getColumnName(18));
    assertEquals(Types.VARCHAR, meta.getColumnType(18));
    assertEquals("varchar", meta.getColumnTypeName(18));
    // varchar columns should have correct display size/precision
    assertEquals(20, meta.getColumnDisplaySize(18));
    assertEquals(20, meta.getPrecision(18));
    assertEquals(0, meta.getScale(18));

    assertEquals("c21", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE"));
    assertEquals("varchar", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(18), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(18), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c22", meta.getColumnName(19));
    assertEquals(Types.CHAR, meta.getColumnType(19));
    assertEquals("char", meta.getColumnTypeName(19));
    // char columns should have correct display size/precision
    assertEquals(15, meta.getColumnDisplaySize(19));
    assertEquals(15, meta.getPrecision(19));
    assertEquals(0, meta.getScale(19));

    assertEquals("c23", meta.getColumnName(20));
    assertEquals(Types.BINARY, meta.getColumnType(20));
    assertEquals("binary", meta.getColumnTypeName(20));
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(20));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(20));
    assertEquals(0, meta.getScale(20));

    assertEquals("c22", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.CHAR, colRS.getInt("DATA_TYPE"));
    assertEquals("char", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(19), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(19), colRS.getInt("DECIMAL_DIGITS"));

    for (int i = 1; i <= meta.getColumnCount(); i++) {
        assertFalse(meta.isAutoIncrement(i));
        assertFalse(meta.isCurrency(i));
        assertEquals(ResultSetMetaData.columnNullable, meta.isNullable(i));
    }
}

From source file:org.geoserver.taskmanager.tasks.CopyTableTaskTypeImpl.java

@Override
public TaskResult run(TaskContext ctx) throws TaskException {
    // TODO: check for ctx.isInterruptMe() in loops and cancel task

    final DbSource sourcedb = (DbSource) ctx.getParameterValues().get(PARAM_SOURCE_DB_NAME);
    final DbSource targetdb = (DbSource) ctx.getParameterValues().get(PARAM_TARGET_DB_NAME);
    final DbTable table = (DbTable) ctx.getBatchContext().get(ctx.getParameterValues().get(PARAM_TABLE_NAME));

    final DbTable targetTable = ctx.getParameterValues().containsKey(PARAM_TARGET_TABLE_NAME)
            ? (DbTable) ctx.getParameterValues().get(PARAM_TARGET_TABLE_NAME)
            : new DbTableImpl(targetdb, table.getTableName());
    final String tempTableName = SqlUtil.qualified(SqlUtil.schema(targetTable.getTableName()),
            "_temp_" + UUID.randomUUID().toString().replace('-', '_'));
    ctx.getBatchContext().put(targetTable, new DbTableImpl(targetdb, tempTableName));

    try (Connection sourceConn = sourcedb.getDataSource().getConnection()) {
        sourceConn.setAutoCommit(false);
        try (Connection destConn = targetdb.getDataSource().getConnection()) {
            try (Statement stmt = sourceConn.createStatement()) {
                stmt.setFetchSize(BATCH_SIZE);
                try (ResultSet rs = stmt
                        .executeQuery("SELECT * FROM " + sourcedb.getDialect().quote(table.getTableName()))) {

                    ResultSetMetaData rsmd = rs.getMetaData();

                    String tempSchema = SqlUtil.schema(tempTableName);
                    String sqlCreateSchemaIfNotExists = tempSchema == null ? ""
                            : targetdb.getDialect().createSchema(destConn,
                                    targetdb.getDialect().quote(tempSchema));

                    // create the temp table structure
                    StringBuilder sb = new StringBuilder(sqlCreateSchemaIfNotExists);
                    sb.append("CREATE TABLE ").append(targetdb.getDialect().quote(tempTableName)).append(" ( ");
                    int columnCount = rsmd.getColumnCount();

                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = targetdb.getDialect().quote(rsmd.getColumnLabel(i));
                        String typeName = rsmd.getColumnTypeName(i);
                        sb.append(columnName).append(" ").append(typeName);
                        if (("char".equals(typeName) || "varchar".equals(typeName))
                                && rsmd.getColumnDisplaySize(i) > 0
                                && rsmd.getColumnDisplaySize(i) < Integer.MAX_VALUE) {
                            sb.append(" (").append(rsmd.getColumnDisplaySize(i)).append(" ) ");
                        }/*ww w . j  a v a2 s.  c  o m*/
                        switch (sourcedb.getDialect().isNullable(rsmd.isNullable(i))) {
                        case ResultSetMetaData.columnNoNulls:
                            sb.append(" NOT NULL");
                            break;
                        case ResultSetMetaData.columnNullable:
                            sb.append(" NULL");
                            break;
                        }
                        sb.append(", ");
                    }
                    String primaryKey = getPrimaryKey(sourceConn, table.getTableName());
                    boolean hasPrimaryKeyColumn = !primaryKey.isEmpty();
                    if (!hasPrimaryKeyColumn) {
                        // create a Primary key column if none exist.
                        sb.append(GENERATE_ID_COLUMN_NAME + " int PRIMARY KEY, ");
                        columnCount++;
                    }

                    sb.setLength(sb.length() - 2);
                    sb.append(" ); ");

                    // creating indexes
                    Map<String, Set<String>> indexAndColumnMap = getIndexesColumns(sourceConn,
                            table.getTableName());
                    Set<String> uniqueIndexes = getUniqueIndexes(sourceConn, table.getTableName());
                    Set<String> spatialColumns = sourcedb.getDialect().getSpatialColumns(sourceConn,
                            table.getTableName(), sourcedb.getSchema());

                    for (String indexName : indexAndColumnMap.keySet()) {
                        Set<String> columnNames = indexAndColumnMap.get(indexName);
                        boolean isSpatialIndex = columnNames.size() == 1
                                && spatialColumns.contains(columnNames.iterator().next());

                        sb.append(targetdb.getDialect().createIndex(tempTableName, columnNames, isSpatialIndex,
                                uniqueIndexes.contains(indexName)));
                    }
                    // we are copying a view and need to create the spatial index.
                    if (indexAndColumnMap.isEmpty() && !spatialColumns.isEmpty()) {
                        sb.append(
                                targetdb.getDialect().createIndex(tempTableName, spatialColumns, true, false));
                    }

                    String dump = sb.toString();
                    LOGGER.log(Level.FINE, "creating temporary table: " + dump);

                    try (Statement stmt2 = destConn.createStatement()) {
                        stmt2.executeUpdate(dump);
                    }

                    // copy the data
                    sb = new StringBuilder("INSERT INTO ").append(targetdb.getDialect().quote(tempTableName))
                            .append(" VALUES (");
                    for (int i = 0; i < columnCount; i++) {
                        if (i > 0) {
                            sb.append(",");
                        }
                        sb.append("?");
                    }
                    sb.append(")");

                    LOGGER.log(Level.FINE, "inserting records: " + sb.toString());

                    try (PreparedStatement pstmt = destConn.prepareStatement(sb.toString())) {
                        int batchSize = 0;
                        int primaryKeyValue = 0;
                        while (rs.next()) {
                            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                                pstmt.setObject(i, rs.getObject(i));
                            }
                            // generate the primary key value
                            if (!hasPrimaryKeyColumn) {
                                pstmt.setObject(columnCount, primaryKeyValue);
                            }
                            pstmt.addBatch();
                            batchSize++;
                            if (batchSize >= BATCH_SIZE) {
                                pstmt.executeBatch();
                                batchSize = 0;
                            }
                            primaryKeyValue++;
                        }
                        if (batchSize > 0) {
                            pstmt.executeBatch();
                        }
                    }
                }
            }
        }
    } catch (SQLException e) {
        // clean-up if necessary
        try (Connection conn = targetdb.getDataSource().getConnection()) {
            try (Statement stmt = conn.createStatement()) {
                stmt.executeUpdate("DROP TABLE IF EXISTS " + targetdb.getDialect().quote(tempTableName));
            }
        } catch (SQLException e2) {
        }

        throw new TaskException(e);
    }

    return new TaskResult() {
        @Override
        public void commit() throws TaskException {
            try (Connection conn = targetdb.getDataSource().getConnection()) {
                try (Statement stmt = conn.createStatement()) {
                    stmt.executeUpdate(
                            "DROP TABLE IF EXISTS " + targetdb.getDialect().quote(targetTable.getTableName()));
                    stmt.executeUpdate("ALTER TABLE " + targetdb.getDialect().quote(tempTableName)
                            + " RENAME TO "
                            + targetdb.getDialect().quote(SqlUtil.notQualified(targetTable.getTableName())));
                }

                ctx.getBatchContext().delete(targetTable);
            } catch (SQLException e) {
                throw new TaskException(e);
            }
        }

        @Override
        public void rollback() throws TaskException {
            try (Connection conn = targetdb.getDataSource().getConnection()) {
                try (Statement stmt = conn.createStatement()) {
                    stmt.executeUpdate("DROP TABLE " + targetdb.getDialect().quote(tempTableName) + "");
                }
            } catch (SQLException e) {
                throw new TaskException(e);
            }
        }
    };
}

From source file:org.omnaest.utils.table.impl.adapter.TableToResultSetAdapter.java

@Override
public ResultSetMetaData getMetaData() throws SQLException {
    return new ResultSetMetaData() {

        @Override//  w w w  . j  a  va2 s. c  o m
        public <T> T unwrap(Class<T> iface) throws SQLException {
            throw new UnsupportedOperationException();
        }

        @Override
        public boolean isWrapperFor(Class<?> iface) throws SQLException {
            throw new UnsupportedOperationException();
        }

        @Override
        public boolean isWritable(int column) throws SQLException {
            return true;
        }

        @Override
        public boolean isSigned(int column) throws SQLException {
            throw new UnsupportedOperationException();
        }

        @Override
        public boolean isSearchable(int column) throws SQLException {
            throw new UnsupportedOperationException();
        }

        @Override
        public boolean isReadOnly(int column) throws SQLException {
            return false;
        }

        @Override
        public int isNullable(int column) throws SQLException {
            return ResultSetMetaData.columnNullable;
        }

        @Override
        public boolean isDefinitelyWritable(int column) throws SQLException {
            throw new UnsupportedOperationException();
        }

        @Override
        public boolean isCurrency(int column) throws SQLException {
            throw new UnsupportedOperationException();
        }

        @Override
        public boolean isCaseSensitive(int column) throws SQLException {
            return true;
        }

        @Override
        public boolean isAutoIncrement(int column) throws SQLException {
            return false;
        }

        @Override
        public String getTableName(int column) throws SQLException {
            Object tableName = TableToResultSetAdapter.this.table.getTableName();
            return tableName != null ? String.valueOf(tableName) : null;
        }

        @Override
        public String getSchemaName(int column) throws SQLException {
            throw new UnsupportedOperationException();
        }

        @Override
        public int getScale(int column) throws SQLException {
            throw new UnsupportedOperationException();
        }

        @Override
        public int getPrecision(int column) throws SQLException {
            throw new UnsupportedOperationException();
        }

        @Override
        public String getColumnTypeName(int column) throws SQLException {
            throw new UnsupportedOperationException();
        }

        @Override
        public int getColumnType(int column) throws SQLException {
            throw new UnsupportedOperationException();
        }

        @Override
        public String getColumnName(int column) throws SQLException {
            //
            String retval = null;

            //
            Object columnTitleValue = null;
            try {
                columnTitleValue = TableToResultSetAdapter.this.table.getColumnTitle(column - 1);
                retval = columnTitleValue != null ? String.valueOf(columnTitleValue) : null;
            } catch (Exception e) {
            }

            //
            return retval;
        }

        @Override
        public String getColumnLabel(int column) throws SQLException {
            return this.getColumnName(column);
        }

        @Override
        public int getColumnDisplaySize(int column) throws SQLException {
            return this.getColumnName(column).length();
        }

        @Override
        public int getColumnCount() throws SQLException {
            return TableToResultSetAdapter.this.table.columnSize();
        }

        @Override
        public String getColumnClassName(int column) throws SQLException {
            throw new UnsupportedOperationException();
        }

        @Override
        public String getCatalogName(int column) throws SQLException {
            throw new UnsupportedOperationException();
        }
    };
}