Example usage for java.sql ResultSetMetaData getColumnName

List of usage examples for java.sql ResultSetMetaData getColumnName

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

Introduction

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

Prototype

String getColumnName(int column) throws SQLException;

Source Link

Document

Get the designated column's name.

Usage

From source file:com.glaf.core.util.DBUtils.java

public static String getAlterTable(TableDefinition classDefinition) {
    StringBuffer buffer = new StringBuffer();
    List<String> cloumns = new java.util.ArrayList<String>();
    Connection connection = null;
    Statement stmt = null;/*w  w  w. j ava 2s. c o  m*/
    ResultSet rs = null;
    try {
        connection = DBConnectionFactory.getConnection();
        String dbType = DBConnectionFactory.getDatabaseType(connection);
        stmt = connection.createStatement();
        rs = stmt.executeQuery("select * from " + classDefinition.getTableName() + " where 1=0 ");
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        for (int i = 1; i <= columnCount; i++) {
            String column = rsmd.getColumnName(i);
            cloumns.add(column.toUpperCase());
        }

        Collection<ColumnDefinition> fields = classDefinition.getColumns();
        for (ColumnDefinition field : fields) {
            if (field.getColumnName() != null && !cloumns.contains(field.getColumnName().toUpperCase())) {
                String str = getAddColumnSql(dbType, classDefinition.getTableName(), field);
                buffer.append(str);
                buffer.append("\r\r");
            }
        }

    } catch (Exception ex) {
        throw new RuntimeException(ex);
    } finally {
        JdbcUtils.close(rs);
        JdbcUtils.close(stmt);
        JdbcUtils.close(connection);
    }

    return buffer.toString();
}

From source file:com.glaf.core.util.DBUtils.java

public static void alterTable(Connection connection, TableDefinition tableDefinition) {
    List<String> cloumns = new java.util.ArrayList<String>();
    Statement statement = null;/*from   w  w w  .  j  av  a2s .  c o  m*/
    Statement stmt = null;
    ResultSet rs = null;
    try {
        String dbType = DBConnectionFactory.getDatabaseType(connection);
        stmt = connection.createStatement();
        rs = stmt.executeQuery("select * from " + tableDefinition.getTableName() + " where 1=0 ");
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        for (int i = 1; i <= columnCount; i++) {
            String column = rsmd.getColumnName(i);
            cloumns.add(column.toUpperCase());
        }

        logger.debug(tableDefinition.getTableName() + " cloumns:" + cloumns);

        JdbcUtils.close(stmt);
        JdbcUtils.close(rs);

        Collection<ColumnDefinition> fields = tableDefinition.getColumns();
        for (ColumnDefinition field : fields) {
            if (field.getColumnName() != null && !cloumns.contains(field.getColumnName().toUpperCase())) {
                String sql = getAddColumnSql(dbType, tableDefinition.getTableName(), field);
                if (sql != null && sql.length() > 0) {
                    statement = connection.createStatement();
                    logger.info("alter table " + tableDefinition.getTableName() + ":\n" + sql);
                    statement.execute(sql);
                    JdbcUtils.close(statement);
                }
            }
        }
    } catch (Exception ex) {
        ex.printStackTrace();
        throw new RuntimeException(ex);
    } finally {
        JdbcUtils.close(rs);
        JdbcUtils.close(stmt);
        JdbcUtils.close(statement);
    }
}

From source file:com.glaf.core.util.DBUtils.java

public static void alterTable(TableDefinition tableDefinition) {
    List<String> cloumns = new java.util.ArrayList<String>();
    Connection connection = null;
    Statement statement = null;//from w w  w.  j  a  v a2s  .c om
    Statement stmt = null;
    ResultSet rs = null;
    try {
        connection = DBConnectionFactory.getConnection();
        connection.setAutoCommit(false);
        String dbType = DBConnectionFactory.getDatabaseType(connection);
        stmt = connection.createStatement();
        rs = stmt.executeQuery("select * from " + tableDefinition.getTableName() + " where 1=0 ");
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        for (int i = 1; i <= columnCount; i++) {
            String column = rsmd.getColumnName(i);
            cloumns.add(column.toUpperCase());
        }

        logger.debug(tableDefinition.getTableName() + " cloumns:" + cloumns);

        Collection<ColumnDefinition> fields = tableDefinition.getColumns();
        for (ColumnDefinition field : fields) {
            if (field.getColumnName() != null && !cloumns.contains(field.getColumnName().toUpperCase())) {
                String sql = getAddColumnSql(dbType, tableDefinition.getTableName(), field);
                if (sql != null && sql.length() > 0) {
                    statement = connection.createStatement();
                    logger.info("alter table " + tableDefinition.getTableName() + ":\n" + sql);
                    statement.execute(sql);
                    JdbcUtils.close(statement);
                }
            }
        }
        connection.commit();
    } catch (Exception ex) {
        ex.printStackTrace();
        throw new RuntimeException(ex);
    } finally {
        JdbcUtils.close(stmt);
        JdbcUtils.close(rs);
        JdbcUtils.close(statement);
        JdbcUtils.close(connection);
    }
}

From source file:com.glaf.core.util.DBUtils.java

public static void alterTable(String systemName, TableDefinition tableDefinition) {
    List<String> cloumns = new java.util.ArrayList<String>();
    Connection connection = null;
    Statement statement = null;//from  w  w  w . j av a  2 s. co m
    Statement stmt = null;
    ResultSet rs = null;
    try {
        connection = DBConnectionFactory.getConnection(systemName);
        connection.setAutoCommit(false);
        String dbType = DBConnectionFactory.getDatabaseType(connection);
        stmt = connection.createStatement();
        rs = stmt.executeQuery("select * from " + tableDefinition.getTableName() + " where 1=0 ");
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        for (int i = 1; i <= columnCount; i++) {
            String column = rsmd.getColumnName(i);
            cloumns.add(column.toUpperCase());
        }

        logger.debug(tableDefinition.getTableName() + " cloumns:" + cloumns);

        JdbcUtils.close(stmt);
        JdbcUtils.close(rs);

        Collection<ColumnDefinition> fields = tableDefinition.getColumns();
        for (ColumnDefinition field : fields) {
            if (field.getColumnName() != null && !cloumns.contains(field.getColumnName().toUpperCase())) {
                String sql = getAddColumnSql(dbType, tableDefinition.getTableName(), field);
                if (sql != null && sql.length() > 0) {
                    statement = connection.createStatement();
                    logger.info("alter table " + tableDefinition.getTableName() + ":\n" + sql);
                    statement.execute(sql);
                    JdbcUtils.close(statement);
                }
            }
        }
        connection.commit();
    } catch (Exception ex) {
        ex.printStackTrace();
        throw new RuntimeException(ex);
    } finally {
        JdbcUtils.close(stmt);
        JdbcUtils.close(rs);
        JdbcUtils.close(statement);
        JdbcUtils.close(connection);
    }
}

From source file:net.certifi.audittablegen.GenericDMRTest.java

/**
 * Test of getColumns method, of class GenericDMR.
 *//*from  ww  w  .ja  v a2 s.c o m*/
@Test
public void testGetColumns() throws SQLException {
    System.out.println("getColumns");

    String tableName = "myTable";
    String verifiedSchema = "public";
    ResultSet rs = mock(ResultSet.class);
    ResultSetMetaData rsmd = mock(ResultSetMetaData.class);
    ResultSet rsTypes = mock(ResultSet.class);
    dmr.verifiedSchema = verifiedSchema;

    when(dmd.getColumns(null, verifiedSchema, tableName, null)).thenReturn(rs);
    when(rs.getMetaData()).thenReturn(rsmd);
    when(rsmd.getColumnCount()).thenReturn(2);
    when(rs.isBeforeFirst()).thenReturn(true);
    when(rs.next()).thenReturn(true, true, false);
    when(rsmd.getColumnName(anyInt())).thenReturn("metaTestCol1", "metaTestCol2");
    when(rs.getString(anyInt())).thenReturn("value1", "value2");
    when(rs.getString("COLUMN_NAME")).thenReturn("myTableId", "myTableData");
    when(rs.getString("TYPE_NAME")).thenReturn("integer", "char");

    //mock getting data types from db
    when(dmd.getTypeInfo()).thenReturn(rsTypes);
    when(rsTypes.isBeforeFirst()).thenReturn(Boolean.TRUE);
    when(rsTypes.next()).thenReturn(true, true, false);
    when(rsTypes.getString("TYPE_NAME")).thenReturn("integer", "char");
    when(rsTypes.getString("CREATE_PARAMS")).thenReturn(null, "length");

    List result = dmr.getColumns(tableName);

    verify(rs, times(2)).getString(1);
    verify(rs, times(2)).getString(2);
    verify(rs, times(2)).getString("COLUMN_NAME");
    verify(rs, times(3)).next();
    verify(rsmd, times(1)).getColumnCount();
    verify(rsmd, times(2)).getColumnName(1);
    verify(rsmd, times(2)).getColumnName(2);
    verifyNoMoreInteractions(rsmd);

    assertEquals(2, result.size());

}

From source file:es.juntadeandalucia.panelGestion.negocio.utiles.JDBCConnector.java

public List<ColumnVO> getColumnsMetaDataExceptGeom(String sql, String geometryName) throws Exception {
    Exception error = null;//from   w  w w.  j ava 2s .  co m

    List<ColumnVO> tableColumns = new LinkedList<ColumnVO>();

    Connection connection = null;
    PreparedStatement preparedStmnt = null;

    try {
        DataSource dataSource = poolDataSources.get(schemaId);
        connection = dataSource.getConnection();
        connection.setAutoCommit(false);

        preparedStmnt = connection.prepareStatement(sql);
        ResultSet rs = preparedStmnt.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        String geometryFieldName = geometryName;
        if (StringUtils.isEmpty(geometryFieldName)) {
            geometryFieldName = "the_geom";
        }
        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
            String columnName = rsmd.getColumnName(i);
            if (!columnName.equals(geometryFieldName)) {
                String columnType = rsmd.getColumnTypeName(i);
                int columnSqlType = rsmd.getColumnType(i);
                int columnLength = rsmd.getColumnDisplaySize(i);
                int columnPrecision = rsmd.getPrecision(i);

                ColumnVO column = new ColumnVO();
                column.setNameOnTable(columnName);
                column.setType(columnType);
                column.setSqlType(columnSqlType);
                column.setLength(columnLength);
                column.setPrecision(columnPrecision);
                column.setInTable(true);

                tableColumns.add(column);
            }

        }
    } catch (SQLException e) {
        error = e;
    } finally {
        if (preparedStmnt != null) {
            try {
                preparedStmnt.close();
            } catch (SQLException se2) {
                log.warn("No se pudo cerrar el statment: ".concat(se2.getLocalizedMessage()));
            }
        }
        if (connection != null) {
            try {
                if (error != null) {
                    connection.rollback();
                }
            } catch (SQLException se) {
                log.warn("Se produjo un error al manejar la conexin: ".concat(se.getLocalizedMessage()));
            }
            try {
                connection.close();
            } catch (SQLException se) {
                log.warn("Se produjo un error al intentar cerrar la conexin: "
                        .concat(se.getLocalizedMessage()));
            }
        }
    }
    if (error != null) {
        throw error;
    }
    return tableColumns;
}

From source file:org.apache.tajo.QueryTestCaseBase.java

/**
 * It transforms a ResultSet instance to rows represented as strings.
 *
 * @param resultSet ResultSet that contains a query result
 * @return String//from  www . ja  va  2 s .  c  o m
 * @throws SQLException
 */
public String resultSetToString(ResultSet resultSet, boolean sort) throws SQLException {
    StringBuilder sb = new StringBuilder();
    ResultSetMetaData rsmd = resultSet.getMetaData();
    int numOfColumns = rsmd.getColumnCount();

    for (int i = 1; i <= numOfColumns; i++) {
        if (i > 1)
            sb.append(",");
        String columnName = rsmd.getColumnName(i);
        sb.append(columnName);
    }
    sb.append("\n-------------------------------\n");

    List<String> results = new ArrayList<>();
    while (resultSet.next()) {
        StringBuilder line = new StringBuilder();
        for (int i = 1; i <= numOfColumns; i++) {
            if (i > 1)
                line.append(",");
            String columnValue = resultSet.getString(i);
            if (resultSet.wasNull()) {
                columnValue = "null";
            }
            line.append(columnValue);
        }
        results.add(line.toString());
    }
    if (sort) {
        Collections.sort(results);
    }
    for (String line : results) {
        sb.append(line).append('\n');
    }
    return sb.toString();
}

From source file:com.glaf.core.jdbc.QueryHelper.java

public Map<String, Object> toMap(ResultSet rs) throws SQLException {
    Map<String, Object> result = new CaseInsensitiveHashMap();
    ResultSetMetaData rsmd = rs.getMetaData();
    int count = rsmd.getColumnCount();
    for (int i = 1; i <= count; i++) {
        String columnName = rsmd.getColumnLabel(i);
        if (StringUtils.isEmpty(columnName)) {
            columnName = rsmd.getColumnName(i);
        }/*  www . j ava2 s. c  o m*/
        Object object = rs.getObject(i);
        columnName = columnName.toLowerCase();
        String name = StringTools.camelStyle(columnName);
        result.put(name, object);
        result.put(columnName, object);
    }
    return result;
}

From source file:org.alinous.plugin.mysql.MySQLDataSource.java

private List<Record> executeSelectSQL(Object connectionHandle, String sql) throws DataSourceException {
    Connection con = (Connection) connectionHandle;
    Statement stmt = null;//from  w ww .  j  a  va2  s.  c  om
    List<Record> retList = new LinkedList<Record>();

    try {
        stmt = con.createStatement();
        stmt.execute(sql);

        ResultSet rs = stmt.getResultSet();
        ResultSetMetaData metaData = rs.getMetaData();
        while (rs.next()) {
            int cnt = metaData.getColumnCount();
            Record rec = new Record();
            for (int i = 0; i < cnt; i++) {
                String colName = metaData.getColumnName(i + 1);

                String labelName = metaData.getColumnLabel(i + 1);
                if (labelName != null && !labelName.equals("")) {
                    colName = labelName;
                }

                String value = rs.getString(i + 1);

                int colType = metaData.getColumnType(i + 1);
                rec.addFieldValue(colName, value, colType);
            }

            retList.add(rec);
        }

    } catch (SQLException e) {
        throw new DataSourceException(e);
    } finally {
        try {
            stmt.close();
        } catch (SQLException ignore) {
        }
    }

    return retList;
}

From source file:com.flexive.core.storage.GenericDivisionExporter.java

/**
 * Dump a generic table to XML/*www  .  j  av  a  2  s . c  o m*/
 *
 * @param tableName     name of the table
 * @param stmt          an open statement
 * @param out           output stream
 * @param sb            an available and valid StringBuilder
 * @param xmlTag        name of the xml tag to write per row
 * @param idColumn      (optional) id column to sort results
 * @param onlyBinaries  process binary fields (else these will be ignored)
 * @throws SQLException on errors
 * @throws IOException  on errors
 */
private void dumpTable(String tableName, Statement stmt, OutputStream out, StringBuilder sb, String xmlTag,
        String idColumn, boolean onlyBinaries) throws SQLException, IOException {
    ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName
            + (StringUtils.isEmpty(idColumn) ? "" : " ORDER BY " + idColumn + " ASC"));
    final ResultSetMetaData md = rs.getMetaData();
    String value, att;
    boolean hasSubTags;
    while (rs.next()) {
        hasSubTags = false;
        if (!onlyBinaries) {
            sb.setLength(0);
            sb.append("  <").append(xmlTag);
        }
        for (int i = 1; i <= md.getColumnCount(); i++) {
            value = null;
            att = md.getColumnName(i).toLowerCase();
            switch (md.getColumnType(i)) {
            case java.sql.Types.DECIMAL:
            case java.sql.Types.NUMERIC:
            case java.sql.Types.BIGINT:
                if (!onlyBinaries) {
                    value = String.valueOf(rs.getBigDecimal(i));
                    if (rs.wasNull())
                        value = null;
                }
                break;
            case java.sql.Types.INTEGER:
            case java.sql.Types.SMALLINT:
            case java.sql.Types.TINYINT:
                if (!onlyBinaries) {
                    value = String.valueOf(rs.getLong(i));
                    if (rs.wasNull())
                        value = null;
                }
                break;
            case java.sql.Types.DOUBLE:
            case java.sql.Types.FLOAT:
            case java.sql.Types.REAL:
                if (!onlyBinaries) {
                    value = String.valueOf(rs.getDouble(i));
                    if (rs.wasNull())
                        value = null;
                }
                break;
            case java.sql.Types.TIMESTAMP:
            case java.sql.Types.DATE:
                if (!onlyBinaries) {
                    final Timestamp ts = rs.getTimestamp(i);
                    if (rs.wasNull())
                        value = null;
                    else
                        value = FxFormatUtils.getDateTimeFormat().format(ts);
                }
                break;
            case java.sql.Types.BIT:
            case java.sql.Types.CHAR:
            case java.sql.Types.BOOLEAN:
                if (!onlyBinaries) {
                    value = rs.getBoolean(i) ? "1" : "0";
                    if (rs.wasNull())
                        value = null;
                }
                break;
            case java.sql.Types.CLOB:
            case java.sql.Types.BLOB:
            case java.sql.Types.LONGVARBINARY:
            case java.sql.Types.LONGVARCHAR:
            case java.sql.Types.VARBINARY:
            case java.sql.Types.VARCHAR:
            case java.sql.Types.BINARY:
            case SQL_LONGNVARCHAR:
            case SQL_NCHAR:
            case SQL_NCLOB:
            case SQL_NVARCHAR:

                hasSubTags = true;
                break;
            default:
                LOG.warn("Unhandled type [" + md.getColumnType(i) + "] for [" + tableName + "." + att + "]");
            }
            if (value != null && !onlyBinaries)
                sb.append(' ').append(att).append("=\"").append(value).append("\"");
        }
        if (hasSubTags) {
            if (!onlyBinaries)
                sb.append(">\n");
            for (int i = 1; i <= md.getColumnCount(); i++) {
                switch (md.getColumnType(i)) {
                case java.sql.Types.VARBINARY:
                case java.sql.Types.LONGVARBINARY:
                case java.sql.Types.BLOB:
                case java.sql.Types.BINARY:
                    if (idColumn == null)
                        throw new IllegalArgumentException("Id column required to process binaries!");
                    String binFile = FOLDER_BINARY + "/BIN_" + String.valueOf(rs.getLong(idColumn)) + "_" + i
                            + ".blob";
                    att = md.getColumnName(i).toLowerCase();
                    if (onlyBinaries) {
                        if (!(out instanceof ZipOutputStream))
                            throw new IllegalArgumentException(
                                    "out has to be a ZipOutputStream to store binaries!");
                        ZipOutputStream zip = (ZipOutputStream) out;
                        InputStream in = rs.getBinaryStream(i);
                        if (rs.wasNull())
                            break;

                        ZipEntry ze = new ZipEntry(binFile);
                        zip.putNextEntry(ze);

                        byte[] buffer = new byte[4096];
                        int read;
                        while ((read = in.read(buffer)) != -1)
                            zip.write(buffer, 0, read);
                        in.close();
                        zip.closeEntry();
                        zip.flush();
                    } else {
                        InputStream in = rs.getBinaryStream(i); //need to fetch to see if it is empty
                        if (rs.wasNull())
                            break;
                        in.close();
                        sb.append("    <").append(att).append(">").append(binFile).append("</").append(att)
                                .append(">\n");
                    }
                    break;
                case java.sql.Types.CLOB:
                case SQL_LONGNVARCHAR:
                case SQL_NCHAR:
                case SQL_NCLOB:
                case SQL_NVARCHAR:
                case java.sql.Types.LONGVARCHAR:
                case java.sql.Types.VARCHAR:
                    if (!onlyBinaries) {
                        value = rs.getString(i);
                        if (rs.wasNull())
                            break;
                        att = md.getColumnName(i).toLowerCase();
                        sb.append("    <").append(att).append('>');
                        escape(sb, value);
                        sb.append("</").append(att).append(">\n");
                    }
                    break;
                }
            }
            if (!onlyBinaries)
                sb.append("  </").append(xmlTag).append(">\n");
        } else {
            if (!onlyBinaries)
                sb.append("/>\n");
        }
        if (!onlyBinaries)
            write(out, sb);
    }
}