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.bigdata.etl.util.DwUtil.java

public static void bulkInsert(String tableName, List<Map<String, String>> lst) {

    ResultSet rs = null;/*from  w ww .j a  v  a  2 s.c  om*/
    java.sql.Statement stmt = null;

    try (java.sql.Connection conn = DataSource.getConnection()) {
        stmt = conn.createStatement();
        rs = stmt.executeQuery("select top 0 * from " + tableName);
        try (SQLServerBulkCopy bulk = new SQLServerBulkCopy(url + "user=" + user + ";password=" + password)) {
            SQLServerBulkCopyOptions sqlsbc = new SQLServerBulkCopyOptions();
            sqlsbc.setBulkCopyTimeout(60 * 60 * 1000);
            bulk.setBulkCopyOptions(sqlsbc);
            bulk.setDestinationTableName(tableName);
            ResultSetMetaData rsmd = rs.getMetaData();
            if (lst == null) {
                return;
            }
            // System.out.println(LocalTime.now() + " "+Thread.currentThread().getId()+" "+lst.size());
            try (CachedRowSetImpl x = new CachedRowSetImpl()) {
                x.populate(rs);
                for (int k = 0; k < lst.size(); k++) {
                    Map<String, String> map = lst.get(k);
                    x.last();
                    x.moveToInsertRow();
                    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                        String name = rsmd.getColumnName(i).toUpperCase();
                        int type = rsmd.getColumnType(i);//package java.sql.Type?

                        try {
                            switch (type) {
                            case Types.VARCHAR:
                            case Types.NVARCHAR:
                                int len = rsmd.getColumnDisplaySize(i);
                                String v = map.get(name);
                                if (map.containsKey(name)) {
                                    x.updateString(i, v.length() > len ? v.substring(0, len) : v);
                                } else {
                                    x.updateNull(i);
                                }
                                break;
                            case Types.BIGINT:
                                if (map.containsKey(name) && map.get(name).matches("\\d{1,}")) {
                                    x.updateLong(i, Long.valueOf(map.get(name)));
                                } else {
                                    //   x.updateLong(i, 0);
                                    x.updateNull(i);
                                }
                                break;
                            case Types.FLOAT:
                                if (map.containsKey(name) && map.get(name).matches("([+-]?)\\d*\\.\\d+$")) {
                                    x.updateFloat(i, Float.valueOf(map.get(name)));
                                } else {
                                    x.updateNull(i);

                                }
                                break;
                            case Types.DOUBLE:
                                if (map.containsKey(name) && map.get(name).trim().length() > 0
                                        && StringUtils.isNumeric(map.get(name))) {
                                    x.updateDouble(i, Double.valueOf(map.get(name)));
                                } else {
                                    x.updateNull(i);
                                }
                                break;

                            case Types.INTEGER:
                                if (map.containsKey(name) && map.get(name).matches("\\d{1,}")) {
                                    x.updateInt(i, Integer.valueOf(map.get(name)));
                                } else {
                                    x.updateNull(i);
                                }
                                break;

                            default:
                                throw new RuntimeException("? " + type);
                            }
                            /*
                            if(map.containsKey("SYS_TELECOM"))
                            System.err.println(map.get("SYS_TELECOM"));
                             */
                        } catch (RuntimeException | SQLException e) {
                            Logger.getLogger(DwUtil.class.getName()).log(Level.SEVERE,
                                    "? name=" + name + " v=" + map.get(name), e);
                        }

                    }
                    x.insertRow();
                    x.moveToCurrentRow();
                    //x.acceptChanges();
                }

                long start = System.currentTimeMillis();
                bulk.writeToServer(x);
                long end = System.currentTimeMillis();
                System.out.println(LocalTime.now() + " " + Thread.currentThread().getId() + " "
                        + (end - start) + "ms" + " " + x.size());
            }
        }

    } catch (SQLException e) {
        Logger.getLogger(DwUtil.class.getName()).log(Level.SEVERE, null, e);
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(DwUtil.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
}

From source file:br.bookmark.db.util.ResultSetUtils.java

/**
 * Return a Collection of Maps, each representing
 * a row from the ResultSet.//from w w w  . j  av a  2 s  .co m
 * The keys of the map are the column names,
 * as returned by the metadata.
 * The values are the columns as Objects.
 *
 * @param resultSet The ResultSet to process.
 * @exception SQLException if an error occurs.
 */
public static Collection getMaps(ResultSet resultSet) throws SQLException {

    // Acquire resultSet MetaData
    ResultSetMetaData metaData = resultSet.getMetaData();
    int cols = metaData.getColumnCount();

    // Use ArrayList to maintain ResultSet sequence
    ArrayList list = new ArrayList();

    // Scroll to each record, make map of row, add to list
    while (resultSet.next()) {
        HashMap row = new HashMap(cols, 1);
        for (int i = 1; i <= cols; i++) {
            row.put(metaData.getColumnName(i), resultSet.getString(i));
        }
        list.add(row);
    } // end while

    return ((Collection) list);

}

From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.DBExcelUtility.java

public static void debugResultSet(ResultSet rs) throws Exception {
    int maxcol = 20;
    StringBuffer sb = new StringBuffer();
    ResultSetMetaData mdata = rs.getMetaData();

    sb.append("\n");
    maxcol = mdata.getColumnCount();//  w  w w .  j av a 2  s.  c  o m
    System.out.println("Total Columns : " + maxcol);

    for (int i = 1; i <= maxcol; i++) {
        sb.append(mdata.getColumnName(i) + "\t");
    }

    int rowcount = 0;

    while (rs.next()) {
        sb.append("\n");
        ++rowcount;
        for (int i = 1; i <= maxcol; i++) {
            try {
                sb.append(rs.getObject(i) + "\t");
            } catch (Exception e) {
                System.out.println("Exception " + e);
            }
        }
    }
    System.out.println(sb.toString());
    System.out.println("Total Rows : " + rowcount);
}

From source file:model.SQLiteModel.java

private static List<Map<String, String>> select(String query) {
    //query = StringEscapeUtils.escapeJavaScript(query);
    //System.out.println(query);
    ResultSet rs = null;//from  ww w.  ja  v  a  2  s  .  c  o m
    Statement stmt = null;
    int first = 1;
    List<String> columnNames = new ArrayList<String>();
    List<Map<String, String>> data = new ArrayList<Map<String, String>>();
    try {
        stmt = c.createStatement();
        rs = stmt.executeQuery(query);
        while (rs.next()) {
            ResultSetMetaData rsmd = rs.getMetaData();
            int count = rsmd.getColumnCount();
            if (first == 1) {
                for (int i = 1; i <= count; i++) {
                    columnNames.add(rsmd.getColumnName(i));
                }
            }
            Map<String, String> curr = new HashMap<String, String>();
            for (int i = 1; i <= count; i++) {
                curr.put(columnNames.get(i - 1), rs.getString(i));
            }
            data.add(curr);
            first++;
        }
        stmt.close();
    } catch (Exception e) {
        System.err.println(e.getClass().getName() + ": " + e.getMessage());
        System.out.println("Unsuccessful select query: " + query);
        writeLineToLog("Unsuccessful select query: " + query);
    }
    return data;
}

From source file:br.bookmark.db.util.ResultSetUtils.java

/**
 * Map JDBC objects to Java equivalents.
 * Used by getBean() and getBeans()./*  w  w  w. j a  va  2  s .c o m*/
 * <p>
 * Some types not supported.
 * Many not work with all drivers.
 * <p>
 * Makes binary conversions of BIGINT, DATE, DECIMAL, DOUBLE, FLOAT, INTEGER,
 * REAL, SMALLINT, TIME, TIMESTAMP, TINYINT.
 * Makes Sting conversions of CHAR, CLOB, VARCHAR, LONGVARCHAR, BLOB, LONGVARBINARY,
 * VARBINARY.
 * <p>
 * DECIMAL, INTEGER, SMALLINT, TIMESTAMP, CHAR, VARCHAR tested with MySQL and Poolman.
 * Others not guaranteed.
 * @param classeDestino 
 * @throws NoSuchFieldException 
 * @throws SecurityException 
 */
private static void putEntry(Map properties, ResultSetMetaData metaData, ResultSet resultSet, int i,
        Class classeDestino) throws Exception {

    /*
    In a perfect universe, this would be enough
    properties.put(
        metaData.getColumnName(i),
        resultSet.getObject(i));
    But only String, Timestamp, and Integer seem to get through that way.
    */

    String columnName = metaData.getColumnName(i);

    // Testa se  uma FK
    /*Field[] fields = classeDestino.getDeclaredFields();
    for (int j = 0; j < fields.length; j++) {
    if (fields[j].getAnnotation(DBFK.class) != null) {
        properties.put(columnName, resultSet.getString(i));
    }
    }*/
    //System.out.println(i+"-"+metaData.getColumnType(i));
    switch (metaData.getColumnType(i)) {

    // http://java.sun.com/j2se/1.3.0/docs/api/java/sql/Types.html

    case Types.BIGINT:
        properties.put(columnName, new Long(resultSet.getLong(i)));
        break;

    case Types.DATE:
        properties.put(columnName, resultSet.getDate(i));
        break;

    case Types.DECIMAL:
    case Types.DOUBLE:
        properties.put(columnName, new Double(resultSet.getDouble(i)));
        break;

    case Types.FLOAT:
        properties.put(columnName, new Float(resultSet.getFloat(i)));
        break;

    case Types.INTEGER:
        int valor = 0;
        try { // Se o campo esta vazio d erro
            valor = resultSet.getInt(i);
        } catch (SQLException e) {
        }
        properties.put(columnName, new Integer(valor));
        break;

    case Types.REAL:
        properties.put(columnName, new Double(resultSet.getString(i)));
        break;

    case Types.SMALLINT:
        properties.put(columnName, new Short(resultSet.getShort(i)));
        break;

    case Types.TIME:
        properties.put(columnName, resultSet.getTime(i));
        break;

    case Types.TIMESTAMP:
        properties.put(columnName, resultSet.getTimestamp(i));
        break;

    // :FIXME: Throws java.lang.ClassCastException: java.lang.Integer
    // :FIXME: with Poolman and MySQL unless use getString.
    case Types.TINYINT:
        properties.put(columnName, new Byte(resultSet.getString(i)));
        break;

    case Types.CHAR:
    case Types.CLOB:
    case Types.VARCHAR:
    case Types.LONGVARCHAR:
        // :FIXME: Handle binaries differently?
    case Types.BLOB:
    case Types.LONGVARBINARY:
    case Types.VARBINARY:
        properties.put(columnName, resultSet.getString(i));
        break;

    /*
        :FIXME: Add handlers for
        ARRAY
        BINARY
        BIT
        DISTINCT
        JAVA_OBJECT
        NULL
        NUMERIC
        OTHER
        REF
        STRUCT
    */

    // Otherwise, pass as *String property to be converted
    default:
        properties.put(columnName + "String", resultSet.getString(i));
        break;
    } // end switch

}

From source file:com.espertech.esper.epl.db.DatabasePollingViewableFactory.java

private static Map<String, DBOutputTypeDesc> compileResultMetaData(ResultSetMetaData resultMetaData,
        ColumnSettings columnSettings) throws SQLException {
    Map<String, DBOutputTypeDesc> outputProperties = new HashMap<String, DBOutputTypeDesc>();
    for (int i = 0; i < resultMetaData.getColumnCount(); i++) {
        String columnName = resultMetaData.getColumnLabel(i + 1);
        if (columnName == null) {
            columnName = resultMetaData.getColumnName(i + 1);
        }//from   w w w.j  a v  a 2 s .  co  m
        int columnType = resultMetaData.getColumnType(i + 1);
        String javaClass = resultMetaData.getColumnTypeName(i + 1);

        ConfigurationDBRef.ColumnChangeCaseEnum caseEnum = columnSettings.getColumnCaseConversionEnum();
        if ((caseEnum != null) && (caseEnum == ConfigurationDBRef.ColumnChangeCaseEnum.LOWERCASE)) {
            columnName = columnName.toLowerCase();
        }
        if ((caseEnum != null) && (caseEnum == ConfigurationDBRef.ColumnChangeCaseEnum.UPPERCASE)) {
            columnName = columnName.toUpperCase();
        }

        DatabaseTypeBinding binding = null;
        String javaTypeBinding = null;
        if (columnSettings.getJavaSqlTypeBinding() != null) {
            javaTypeBinding = columnSettings.getJavaSqlTypeBinding().get(columnType);
        }
        if (javaTypeBinding != null) {
            binding = DatabaseTypeEnum.getEnum(javaTypeBinding).getBinding();
        }
        DBOutputTypeDesc outputType = new DBOutputTypeDesc(columnType, javaClass, binding);
        outputProperties.put(columnName, outputType);
    }
    return outputProperties;
}

From source file:org.apache.nifi.util.hive.HiveJdbcCommon.java

/**
 * Creates an Avro schema from a result set. If the table/record name is known a priori and provided, use that as a
 * fallback for the record name if it cannot be retrieved from the result set, and finally fall back to a default value.
 *
 * @param rs         The result set to convert to Avro
 * @param recordName The a priori record name to use if it cannot be determined from the result set.
 * @return A Schema object representing the result set converted to an Avro record
 * @throws SQLException if any error occurs during conversion
 *///from w  w  w.j a v a 2 s .c  o m
public static Schema createSchema(final ResultSet rs, String recordName) throws SQLException {
    final ResultSetMetaData meta = rs.getMetaData();
    final int nrOfColumns = meta.getColumnCount();
    String tableName = StringUtils.isEmpty(recordName) ? "NiFi_SelectHiveQL_Record" : recordName;
    try {
        if (nrOfColumns > 0) {
            // Hive JDBC doesn't support getTableName, instead it returns table.column for column name. Grab the table name from the first column
            String firstColumnNameFromMeta = meta.getColumnName(1);
            int tableNameDelimiter = firstColumnNameFromMeta.lastIndexOf(".");
            if (tableNameDelimiter > -1) {
                String tableNameFromMeta = firstColumnNameFromMeta.substring(0, tableNameDelimiter);
                if (!StringUtils.isBlank(tableNameFromMeta)) {
                    tableName = tableNameFromMeta;
                }
            }
        }
    } catch (SQLException se) {
        // Not all drivers support getTableName, so just use the previously-set default
    }

    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++) {
        String columnNameFromMeta = meta.getColumnName(i);
        // Hive returns table.column for column name. Grab the column name as the string after the last period
        int columnNameDelimiter = columnNameFromMeta.lastIndexOf(".");
        String columnName = columnNameFromMeta.substring(columnNameDelimiter + 1);
        switch (meta.getColumnType(i)) {
        case CHAR:
        case LONGNVARCHAR:
        case LONGVARCHAR:
        case NCHAR:
        case NVARCHAR:
        case VARCHAR:
        case ARRAY:
        case STRUCT:
        case JAVA_OBJECT:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion()
                    .noDefault();
            break;

        case BIT:
        case BOOLEAN:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().booleanType().endUnion()
                    .noDefault();
            break;

        case INTEGER:
            // Default to signed type unless otherwise noted. Some JDBC drivers don't implement isSigned()
            boolean signedType = true;
            try {
                signedType = meta.isSigned(i);
            } catch (SQLException se) {
                // Use signed types as default
            }
            if (signedType) {
                builder.name(columnName).type().unionOf().nullBuilder().endNull().and().intType().endUnion()
                        .noDefault();
            } else {
                builder.name(columnName).type().unionOf().nullBuilder().endNull().and().longType().endUnion()
                        .noDefault();
            }
            break;

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

        case BIGINT:
            builder.name(columnName).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(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion()
                    .noDefault();
            break;

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

        case DOUBLE:
            builder.name(columnName).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(columnName).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(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion()
                    .noDefault();
            break;

        case BINARY:
        case VARBINARY:
        case LONGVARBINARY:
        case BLOB:
        case CLOB:
            builder.name(columnName).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:com.healthmarketscience.jackcess.ImportUtil.java

/**
 * Copy an existing JDBC ResultSet into a new (or optionally existing) table
 * in this database./* w  ww. j  av a  2s  .  com*/
 * 
 * @param name Name of the new table to create
 * @param source ResultSet to copy from
 * @param filter valid import filter
 * @param useExistingTable if {@code true} use current table if it already
 *                         exists, otherwise, create new table with unique
 *                         name
 *
 * @return the name of the imported table
 * 
 * @see Builder
 */
public static String importResultSet(ResultSet source, Database db, String name, ImportFilter filter,
        boolean useExistingTable) throws SQLException, IOException {
    ResultSetMetaData md = source.getMetaData();

    name = Database.escapeIdentifier(name);
    Table table = null;
    if (!useExistingTable || ((table = db.getTable(name)) == null)) {

        List<Column> columns = new LinkedList<Column>();
        for (int i = 1; i <= md.getColumnCount(); i++) {
            Column column = new Column();
            column.setName(Database.escapeIdentifier(md.getColumnName(i)));
            int lengthInUnits = md.getColumnDisplaySize(i);
            column.setSQLType(md.getColumnType(i), lengthInUnits);
            DataType type = column.getType();
            // we check for isTrueVariableLength here to avoid setting the length
            // for a NUMERIC column, which pretends to be var-len, even though it
            // isn't
            if (type.isTrueVariableLength() && !type.isLongValue()) {
                column.setLengthInUnits((short) lengthInUnits);
            }
            if (type.getHasScalePrecision()) {
                int scale = md.getScale(i);
                int precision = md.getPrecision(i);
                if (type.isValidScale(scale)) {
                    column.setScale((byte) scale);
                }
                if (type.isValidPrecision(precision)) {
                    column.setPrecision((byte) precision);
                }
            }
            columns.add(column);
        }

        table = createUniqueTable(db, name, columns, md, filter);
    }

    List<Object[]> rows = new ArrayList<Object[]>(COPY_TABLE_BATCH_SIZE);
    int numColumns = md.getColumnCount();

    while (source.next()) {
        Object[] row = new Object[numColumns];
        for (int i = 0; i < row.length; i++) {
            row[i] = source.getObject(i + 1);
        }
        row = filter.filterRow(row);
        if (row == null) {
            continue;
        }
        rows.add(row);
        if (rows.size() == COPY_TABLE_BATCH_SIZE) {
            table.addRows(rows);
            rows.clear();
        }
    }
    if (rows.size() > 0) {
        table.addRows(rows);
    }

    return table.getName();
}

From source file:edu.ucsb.nceas.MCTestCase.java

protected static Vector<Hashtable<String, Object>> dbSelect(String sqlStatement, String methodName)
        throws SQLException {
    Vector<Hashtable<String, Object>> resultVector = new Vector<Hashtable<String, Object>>();

    DBConnectionPool connPool = DBConnectionPool.getInstance();
    DBConnection dbconn = DBConnectionPool.getDBConnection(methodName);
    int serialNumber = dbconn.getCheckOutSerialNumber();

    PreparedStatement pstmt = null;

    debug("Selecting from db: " + sqlStatement);
    pstmt = dbconn.prepareStatement(sqlStatement);
    pstmt.execute();/*  w  ww . j av a 2s . c  o m*/

    ResultSet resultSet = pstmt.getResultSet();
    ResultSetMetaData rsMetaData = resultSet.getMetaData();
    int numColumns = rsMetaData.getColumnCount();
    debug("Number of data columns: " + numColumns);
    while (resultSet.next()) {
        Hashtable<String, Object> hashTable = new Hashtable<String, Object>();
        for (int i = 1; i <= numColumns; i++) {
            if (resultSet.getObject(i) != null) {
                hashTable.put(rsMetaData.getColumnName(i), resultSet.getObject(i));
            }
        }
        debug("adding data row to result vector");
        resultVector.add(hashTable);
    }

    resultSet.close();
    pstmt.close();
    DBConnectionPool.returnDBConnection(dbconn, serialNumber);

    return resultVector;
}

From source file:com.qualogy.qafe.business.integration.rdb.MetaDataRowMapper.java

/**
 * Method for mapping rows inherited from RowMapper.
 * Method maps rows to a Map using columnname.
 *///from   ww w .ja  v a2  s .  com
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
    //        Map<String, Object> output = new CaseInsensitiveMap();
    Map<String, Object> output = new DataMap();
    ResultSetMetaData md = rs.getMetaData();
    for (int i = 1; i <= md.getColumnCount(); i++) {
        output.put(md.getColumnName(i), rs.getObject(i));
    }
    return output;
}