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.thinkbiganalytics.util.JdbcCommon.java

/**
 * Examines the result set of a JDBC query and creates an Avro schema with appropriately mapped types to accept rows from the JDBC result.
 *
 * @param rs A result set used to obtain data type information
 * @return an instance of Avro Schema/*from w w w. ja  v a  2  s  .c om*/
 * @throws SQLException if errors occur while reading data from the database
 */
public static Schema createSchema(final ResultSet rs) throws SQLException {
    final ResultSetMetaData meta = rs.getMetaData();
    final int nrOfColumns = meta.getColumnCount();
    String tableName = "";
    try {
        tableName = meta.getTableName(1);
    } catch (SQLException e) {

    }
    if (StringUtils.isBlank(tableName)) {
        tableName = "NiFi_ExecuteSQL_Record";
    }

    final FieldAssembler<Schema> builder = SchemaBuilder.record(tableName).namespace("any.data").fields();

    /**
     * Some missing Avro types - Decimal, Date types. May need some additional work.
     */
    for (int i = 1; i <= nrOfColumns; i++) {
        switch (meta.getColumnType(i)) {
        case CHAR:
        case LONGNVARCHAR:
        case LONGVARCHAR:
        case NCHAR:
        case NVARCHAR:
        case VARCHAR:
            builder.name(meta.getColumnName(i)).type().unionOf().nullBuilder().endNull().and().stringType()
                    .endUnion().noDefault();
            break;
        case BIT:
        case BOOLEAN:
            builder.name(meta.getColumnName(i)).type().unionOf().nullBuilder().endNull().and().booleanType()
                    .endUnion().noDefault();
            break;

        case INTEGER:
            if (meta.isSigned(i)) {
                builder.name(meta.getColumnName(i)).type().unionOf().nullBuilder().endNull().and().intType()
                        .endUnion().noDefault();
            } else {
                builder.name(meta.getColumnName(i)).type().unionOf().nullBuilder().endNull().and().longType()
                        .endUnion().noDefault();
            }
            break;

        case SMALLINT:
        case TINYINT:
            builder.name(meta.getColumnName(i)).type().unionOf().nullBuilder().endNull().and().intType()
                    .endUnion().noDefault();
            break;

        case BIGINT:
            builder.name(meta.getColumnName(i)).type().unionOf().nullBuilder().endNull().and().longType()
                    .endUnion().noDefault();
            break;

        // java.sql.RowId is interface, is seems to be database
        // implementation specific, let's convert to String
        case ROWID:
            builder.name(meta.getColumnName(i)).type().unionOf().nullBuilder().endNull().and().stringType()
                    .endUnion().noDefault();
            break;

        case FLOAT:
        case REAL:
            builder.name(meta.getColumnName(i)).type().unionOf().nullBuilder().endNull().and().floatType()
                    .endUnion().noDefault();
            break;

        case DOUBLE:
            builder.name(meta.getColumnName(i)).type().unionOf().nullBuilder().endNull().and().doubleType()
                    .endUnion().noDefault();
            break;

        // Did not find direct suitable type, need to be clarified!!!!
        case DECIMAL:
        case NUMERIC:
            builder.name(meta.getColumnName(i)).type().unionOf().nullBuilder().endNull().and().stringType()
                    .endUnion().noDefault();
            break;

        // Did not find direct suitable type, need to be clarified!!!!
        case DATE:
        case TIME:
        case TIMESTAMP:
            builder.name(meta.getColumnName(i)).type().unionOf().nullBuilder().endNull().and().stringType()
                    .endUnion().noDefault();
            break;

        case BINARY:
        case VARBINARY:
        case LONGVARBINARY:
        case ARRAY:
        case BLOB:
        case CLOB:
            builder.name(meta.getColumnName(i)).type().unionOf().nullBuilder().endNull().and().bytesType()
                    .endUnion().noDefault();
            break;

        default:
            throw new IllegalArgumentException("createSchema: Unknown SQL type " + meta.getColumnType(i)
                    + " cannot be converted to Avro type");
        }
    }

    return builder.endRecord();
}

From source file:Main.java

public Main() throws Exception {
    ArrayList columnNames = new ArrayList();
    ArrayList data = new ArrayList();
    String url = "jdbc:mysql://localhost:3306/yourdb";
    String userid = "root";
    String password = "sesame";
    String sql = "SELECT * FROM animals";

    Connection connection = DriverManager.getConnection(url, userid, password);
    Statement stmt = connection.createStatement();
    ResultSet rs = stmt.executeQuery(sql);
    ResultSetMetaData md = rs.getMetaData();
    int columns = md.getColumnCount();
    for (int i = 1; i <= columns; i++) {
        columnNames.add(md.getColumnName(i));
    }/*from   www. j av a  2 s. c o m*/
    while (rs.next()) {
        ArrayList row = new ArrayList(columns);
        for (int i = 1; i <= columns; i++) {
            row.add(rs.getObject(i));
        }
        data.add(row);
    }
    Vector columnNamesVector = new Vector();
    Vector dataVector = new Vector();
    for (int i = 0; i < data.size(); i++) {
        ArrayList subArray = (ArrayList) data.get(i);
        Vector subVector = new Vector();
        for (int j = 0; j < subArray.size(); j++) {
            subVector.add(subArray.get(j));
        }
        dataVector.add(subVector);
    }
    for (int i = 0; i < columnNames.size(); i++)
        columnNamesVector.add(columnNames.get(i));
    JTable table = new JTable(dataVector, columnNamesVector) {
        public Class getColumnClass(int column) {
            for (int row = 0; row < getRowCount(); row++) {
                Object o = getValueAt(row, column);
                if (o != null) {
                    return o.getClass();
                }
            }
            return Object.class;
        }
    };
    JScrollPane scrollPane = new JScrollPane(table);
    getContentPane().add(scrollPane);
    JPanel buttonPanel = new JPanel();
    getContentPane().add(buttonPanel, BorderLayout.SOUTH);
}

From source file:MainClass.java

public MainClass() {
    try {//  w  w w  . j  a v a2s  . co m
        Class.forName("COM.cloudscape.core.RmiJdbcDriver");
        Connection connection = DriverManager.getConnection("jdbc:cloudscape:rmi:books");
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT * FROM authors");
        ResultSetMetaData metaData = resultSet.getMetaData();
        int numberOfColumns = metaData.getColumnCount();

        for (int i = 1; i <= numberOfColumns; i++) {
            System.out.println(metaData.getColumnName(i) + "\t");
        }

        while (resultSet.next()) {

            for (int i = 1; i <= numberOfColumns; i++) {
                System.out.println(resultSet.getObject(i) + "\t");
            }

            System.out.println("\n");
        }

        statement.close();
        connection.close();
    } catch (SQLException sqlException) {
        System.out.println(sqlException.getMessage());
    } catch (ClassNotFoundException classNotFound) {
        System.out.println("Driver Not Found");
        System.exit(1);
    }
}

From source file:com.kumarvv.setl.utils.SqlRunner.java

/**
 * retrieves single row Map from datasource
 *
 * @param sql// w w w. j av  a2  s  . c o  m
 * @param ds
 * @return
 */
public Map<String, Object> getSingleRowMap(String sql, DS ds) {
    Map<String, Object> map = new HashMap<>();
    if (StringUtils.isEmpty(sql)) {
        return map;
    }

    try (JdbcRowSet jrs = rowSetUtil.getRowSet(ds)) {
        jrs.setCommand(sql);
        jrs.execute();
        if (!jrs.next()) {
            return map;
        }
        ResultSetMetaData meta = jrs.getMetaData();
        for (int i = 1; i <= meta.getColumnCount(); i++) {
            map.put(meta.getColumnName(i), jrs.getObject(i));
        }
    } catch (Exception e) {
    }
    return map;
}

From source file:org.batoo.jpa.core.test.ColumnNameListHandler.java

/**
 * {@inheritDoc}/*from  ww w  . j a v a 2s .c o  m*/
 * 
 */
@Override
public String handle(ResultSet rs) throws SQLException {
    final List<String> list = Lists.newArrayList();

    final ResultSetMetaData metaData = rs.getMetaData();

    final int columnCount = metaData.getColumnCount();
    for (int i = 1; i <= columnCount; i++) {
        list.add(metaData.getColumnName(i).toLowerCase(Locale.ENGLISH));
    }

    Collections.sort(list);

    return list.toString();
}

From source file:net.orpiske.ssps.common.db.SimpleRsHandler.java

@Override
public T handle(ResultSet rs) throws SQLException {

    // No records to handle :O
    if (!rs.next()) {
        return null;
    }//from w ww  . j  av a  2 s  .c  o  m

    ResultSetMetaData meta = rs.getMetaData();

    for (int i = 1; i <= meta.getColumnCount(); i++) {
        Object value = rs.getObject(i);
        String name = meta.getColumnName(i);

        try {
            /*
             * We convert the column name to a more appropriate and java like name 
             * because some columns are usually named as some_thing whereas Java 
             * properties are named someThing. This call does this conversion.
             */
            String javaProperty = NameConverter.sqlToProperty(name);

            PropertyUtils.setSimpleProperty(dto, javaProperty, value);
        } catch (Exception e) {
            throw new SQLException("Unable to set property " + name + " for bean" + dto.getClass(), e);
        }
    }

    return dto;
}

From source file:com.chiorichan.database.DatabaseEngine.java

public static Map<String, Object> convertRow(ResultSet rs) throws SQLException {
    Map<String, Object> result = Maps.newLinkedHashMap();
    ResultSetMetaData rsmd = rs.getMetaData();

    int numColumns = rsmd.getColumnCount();

    for (int i = 1; i < numColumns + 1; i++) {
        String columnName = rsmd.getColumnName(i);

        // Loader.getLogger().info( "Column: " + columnName + " <-> " + rsmd.getColumnTypeName( i ) );

        if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) {
            result.put(columnName, rs.getArray(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) {
            result.put(columnName, rs.getInt(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) {
            result.put(columnName, rs.getInt(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.BIT) // Sometimes tinyints are read as bits
        {/*from   w  w w .j a v a 2s  .c om*/
            result.put(columnName, rs.getInt(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) {
            result.put(columnName, rs.getBoolean(columnName));
        } else if (rsmd.getColumnTypeName(i).contains("BLOB")
                || rsmd.getColumnType(i) == java.sql.Types.BINARY) {
            // BLOG = Max Length 65,535. Recommended that you use a LONGBLOG.
            byte[] bytes = rs.getBytes(columnName);
            result.put(columnName, bytes);
            /*
             * try
             * {
             * result.put( columnName, new String( bytes, "ISO-8859-1" ) );
             * }
             * catch ( UnsupportedEncodingException e )
             * {
             * e.printStackTrace();
             * }
             */
        } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) {
            result.put(columnName, rs.getDouble(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) {
            result.put(columnName, rs.getFloat(columnName));
        } else if (rsmd.getColumnTypeName(i).equals("INT")) {
            result.put(columnName, rs.getInt(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) {
            result.put(columnName, rs.getNString(columnName));
        } else if (rsmd.getColumnTypeName(i).equals("VARCHAR")) {
            result.put(columnName, rs.getString(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) {
            result.put(columnName, rs.getInt(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) {
            result.put(columnName, rs.getDate(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) {
            result.put(columnName, rs.getTimestamp(columnName));
        } else {
            result.put(columnName, rs.getObject(columnName));
        }
    }

    return result;
}

From source file:net.orpiske.ssps.common.dependencies.cache.MultiRsHandler.java

@Override
protected DependencyCacheDto handleRow(ResultSet rs) throws SQLException {
    DependencyCacheDto dto = new DependencyCacheDto();

    ResultSetMetaData meta = rs.getMetaData();

    for (int i = 1; i <= meta.getColumnCount(); i++) {
        Object value = rs.getObject(i);
        String name = meta.getColumnName(i);

        try {//from  ww w .ja  v a2 s  .  c  om
            /*
             * We convert the column name to a more appropriate and java like name 
             * because some columns are usually named as some_thing whereas Java 
             * properties are named someThing. This call does this conversion.
             */
            String javaProperty = NameConverter.sqlToProperty(name);

            if (javaProperty.equals("version")) {
                Version version = Version.toVersion((String) value);

                PropertyUtils.setSimpleProperty(dto, javaProperty, version);
            } else {
                PropertyUtils.setSimpleProperty(dto, javaProperty, value);
            }
        } catch (Exception e) {
            throw new SQLException("Unable to set property " + name + " for bean" + dto.getClass(), e);
        }
    }

    return dto;
}

From source file:net.orpiske.ssps.common.registry.MultiRsHandler.java

@Override
protected SoftwareInventoryDto handleRow(ResultSet rs) throws SQLException {
    SoftwareInventoryDto dto = new SoftwareInventoryDto();

    ResultSetMetaData meta = rs.getMetaData();

    for (int i = 1; i <= meta.getColumnCount(); i++) {
        Object value = rs.getObject(i);
        String name = meta.getColumnName(i);

        try {/*w w w  .  j  a va 2s .com*/
            /*
             * We convert the column name to a more appropriate and java like name 
             * because some columns are usually named as some_thing whereas Java 
             * properties are named someThing. This call does this conversion.
             */
            String javaProperty = NameConverter.sqlToProperty(name);

            if (javaProperty.equals("version")) {
                Version version = Version.toVersion((String) value);

                PropertyUtils.setSimpleProperty(dto, javaProperty, version);
            } else {
                PropertyUtils.setSimpleProperty(dto, javaProperty, value);
            }
        } catch (Exception e) {
            throw new SQLException("Unable to set property " + name + " for bean" + dto.getClass(), e);
        }
    }

    return dto;
}

From source file:net.orpiske.ssps.common.repository.search.cache.MultiRsHandler.java

@Override
protected PackageInfo handleRow(ResultSet rs) throws SQLException {
    PackageInfo dto = new PackageInfo();

    ResultSetMetaData meta = rs.getMetaData();

    for (int i = 1; i <= meta.getColumnCount(); i++) {
        Object value = rs.getObject(i);
        String name = meta.getColumnName(i);

        try {// ww  w .  j  av  a 2  s  .  c  o  m
            /*
             * We convert the column name to a more appropriate and java like name 
             * because some columns are usually named as some_thing whereas Java 
             * properties are named someThing. This call does this conversion.
             */
            String javaProperty = NameConverter.sqlToProperty(name);

            if (javaProperty.equals("version")) {
                Version version = Version.toVersion((String) value);

                PropertyUtils.setSimpleProperty(dto, javaProperty, version);
            } else {
                PropertyUtils.setSimpleProperty(dto, javaProperty, value);
            }
        } catch (Exception e) {
            throw new SQLException("Unable to set property " + name + " for bean" + dto.getClass(), e);
        }
    }

    return dto;
}