Example usage for java.sql ResultSetMetaData getColumnCount

List of usage examples for java.sql ResultSetMetaData getColumnCount

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

Introduction

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

Prototype

int getColumnCount() throws SQLException;

Source Link

Document

Returns the number of columns in this ResultSet object.

Usage

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

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

    sb.append("\n");
    maxcol = mdata.getColumnCount();

    for (int i = 1; i <= maxcol; i++) {
        sb.append(mdata.getColumnName(i) + "\t");
    }/*from ww w .j a v a  2 s .  co  m*/

    int rowcount = 0;

    sb.append("\n");
    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());
}

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  ww  w  . j av a 2  s . c o  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: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();
    System.out.println("Total Columns : " + maxcol);

    for (int i = 1; i <= maxcol; i++) {
        sb.append(mdata.getColumnName(i) + "\t");
    }/*w w w .  j  av a2s  .  co m*/

    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 w w  w . j av a 2s.  c om
    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:com.google.visualization.datasource.util.SqlDataSourceHelper.java

/**
 * Returns the table description which includes the ids, labels and types of
 * the table columns.//from   w  ww  .  j av a2  s.  co  m
 *
 * @param rs The result set holding the data from the sql table.
 * @param columnIdsList The list of the column ids in the data table.
 *
 * @return The table description.
 *
 * @throws SQLException Thrown when the connection to the database failed.
 */
static DataTable buildColumns(ResultSet rs, List<String> columnIdsList) throws SQLException {
    DataTable result = new DataTable();
    ResultSetMetaData metaData = rs.getMetaData();
    int numOfCols = metaData.getColumnCount();
    // For each column in the table, create the column description. SQL indexes
    // are 1-based.
    for (int i = 1; i <= numOfCols; i++) {
        String id = (columnIdsList == null) ? metaData.getColumnLabel(i) : columnIdsList.get(i - 1);
        ColumnDescription columnDescription = new ColumnDescription(id,
                sqlTypeToValueType(metaData.getColumnType(i)), metaData.getColumnLabel(i));
        result.addColumn(columnDescription);
    }
    return result;
}

From source file:com.hangum.tadpole.engine.sql.util.resultset.ResultSetUtils.java

/**
 * column of type/*from   ww  w  .java 2s  .co  m*/
 * 
 * @param isShowRowNum    ?  ?? .
 * @param rsm
 * @return
 * @throws SQLException
 */
public static Map<Integer, Integer> getColumnType(boolean isShowRowNum, ResultSetMetaData rsm)
        throws SQLException {
    Map<Integer, Integer> mapColumnType = new HashMap<Integer, Integer>();
    int intStartIndex = 0;

    if (isShowRowNum) {
        intStartIndex++;
        mapColumnType.put(0, java.sql.Types.INTEGER);
    }

    for (int i = 0; i < rsm.getColumnCount(); i++) {
        //         logger.debug("\t ==[column start]================================ ColumnName  :  "    + rsm.getColumnName(i+1));
        //         logger.debug("\tColumnLabel        :  "    + rsm.getColumnLabel(i+1));

        //         logger.debug("\t AutoIncrement     :  "    + rsm.isAutoIncrement(i+1));
        //         logger.debug("\t Nullable           :  "    + rsm.isNullable(i+1));
        //         logger.debug("\t CaseSensitive     :  "    + rsm.isCaseSensitive(i+1));
        //         logger.debug("\t Currency           :  "    + rsm.isCurrency(i+1));
        //         
        //         logger.debug("\t DefinitelyWritable :  "    + rsm.isDefinitelyWritable(i+1));
        //         logger.debug("\t ReadOnly           :  "    + rsm.isReadOnly(i+1));
        //         logger.debug("\t Searchable           :  "    + rsm.isSearchable(i+1));
        //         logger.debug("\t Signed              :  "    + rsm.isSigned(i+1));
        ////         logger.debug("\t Currency           :  "    + rsm.isWrapperFor(i+1));
        //         logger.debug("\t Writable           :  "    + rsm.isWritable(i+1));
        //         
        //         logger.debug("\t ColumnClassName     :  "    + rsm.getColumnClassName(i+1));
        //         logger.debug("\t CatalogName        :  "    + rsm.getCatalogName(i+1));
        //         logger.debug("\t ColumnDisplaySize  :  "    + rsm.getColumnDisplaySize(i+1));
        //         logger.debug("\t ColumnType        :  "    + rsm.getColumnType(i+1));
        //         logger.debug("\t ColumnTypeName    :  "    + rsm.getColumnTypeName(i+1));
        //
        // mysql json ? ?  1  ? , ?? pgsql? json ? ? 1111 .
        //                     - 2015.10.21 mysql 5.7
        if (StringUtils.equalsIgnoreCase("json", rsm.getColumnTypeName(i + 1))) {
            mapColumnType.put(i + intStartIndex, 1111);
        } else {
            mapColumnType.put(i + intStartIndex, rsm.getColumnType(i + 1));
        }
        //         logger.debug("\t Column Label " + rsm.getColumnLabel(i+1) );

        //         logger.debug("\t Precision          :  "    + rsm.getPrecision(i+1));
        //         logger.debug("\t Scale             :  "    + rsm.getScale(i+1));
        //         logger.debug("\t SchemaName          :  "    + rsm.getSchemaName(i+1));
        //         logger.debug("\t TableName          :  "    + rsm.getTableName(i+1));
        //         logger.debug("\t ==[column end]================================ ColumnName  :  "    + rsm.getColumnName(i+1));
    }

    return mapColumnType;
}

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:com.webbfontaine.valuewebb.model.util.Utils.java

public static List<Object[]> transformToList(ResultSet rs) throws SQLException {
    ResultSetMetaData rsMetaData = rs.getMetaData();

    int numberOfColumns = rsMetaData.getColumnCount();

    List<Object[]> result = new ArrayList<>(numberOfColumns);

    while (rs.next()) {
        Object[] row = new Object[numberOfColumns];
        for (int i = 0; i < numberOfColumns; i++) {
            if (rsMetaData.getColumnType(i + 1) == Types.TIMESTAMP) {
                row[i] = rs.getDate(i + 1);
            } else {
                row[i] = rs.getObject(i + 1);
            }//from  w ww .j a v  a  2 s .  c o  m
        }
        result.add(row);
    }

    return result;
}

From source file:dbconverter.dao.util.ToolKit.java

/**
 * Given a ResultSet, writes the contained data as JSON to a target file,
 *  with the expectation that said file will be used in an Elasticsearch
 *  bulk index operation./*from  ww  w  . jav a  2 s  .  c o m*/
 * This method supports arbitrary-sized ResultSets, provided interval is set low enough
 * @param resultSet The ResultSet to save to a file
 * @param obj A QueryObject which must contain the index and type of the target
 * @param interval Determines how many documents should be stored within Java at a time
 *                 If you run out of heap space, try decreasing this value
 * @param fileName The name of the file to write to
 * @author hightowe
 */
public static void writeResultSetToJson(ResultSet resultSet, QueryObject obj, int interval, String fileName) {
    assert resultSet != null : "ResultSet cannont be null!";

    List<String> resultsList = new ArrayList<>();

    try {
        ResultSetMetaData rsMetaData = resultSet.getMetaData();
        int columnNumbers = rsMetaData.getColumnCount();
        int count = 0;
        int prev = 0;
        while (resultSet.next()) {
            Map<String, Object> dataMap = new HashMap<>();

            // add all column names to the map key-set
            for (int i = 1; i <= columnNumbers; i++) {
                dataMap.put(rsMetaData.getColumnLabel(i), resultSet.getObject(i));
            }

            dataMap.put(TIME_STAMP, getISOTime(TIME_STAMP_FORMAT));

            // Add the data to List of Maps
            String json = ToolKit.convertMapToJson(dataMap);
            resultsList.add(json);
            count++;

            // write to file after every (interval)th run, then clear
            // resultsList to avoid heap space errors
            if (count % interval == 0) {
                writeJsonStringsToFile(resultsList, fileName, obj, prev);
                prev += interval;
                resultsList.clear();
            }
        }

        writeJsonStringsToFile(resultsList, fileName, obj, prev);

    } catch (SQLException e) {
        logger.error(e);
    }
}

From source file:dbconverter.dao.util.ToolKit.java

/**
 * Indexes every document within a ResultSet object
 * @param resultSet The ResultSet containing all documents to be indexed
 * @param bl Determines where to index the data
 * @param uploadInterval Determines how frequently to clear local memory
 * @return The number of documents indexed
 * @author hightowe//from  ww w.  j  av  a  2s  .c o  m
 */
public static int bulkIndexResultSet(ResultSet resultSet, BulkLoader bl, int uploadInterval) {
    assert resultSet != null : PARAMETER_ERROR;
    assert uploadInterval > 0 : PARAMETER_ERROR;
    assert bl != null && bl.isConfigured() : PARAMETER_ERROR;

    int count = 0;
    try {
        ResultSetMetaData rsMetaData = resultSet.getMetaData();
        int columnNumbers = rsMetaData.getColumnCount();
        List<Map> docsList = new ArrayList<>();

        while (resultSet.next()) {
            Map<String, Object> dataMap = new HashMap<>();
            for (int i = 1; i <= columnNumbers; i++) {
                dataMap.put(rsMetaData.getColumnLabel(i), resultSet.getString(i));
            }

            // append a timestamp of when this document was created
            dataMap.put(TIME_STAMP, getISOTime(TIME_STAMP_FORMAT));

            docsList.add(dataMap);
            count++;

            if (count % uploadInterval == 0) {
                bl.bulkIndex(docsList);
                logger.info("Indexed " + count + " documents " + getISOTime(TIME_STAMP_FORMAT));
                docsList.clear();
            }
        }

        if (docsList.size() > 0) {
            bl.bulkIndex(docsList);
            logger.info("Indexed " + count + " documents " + getISOTime(TIME_STAMP_FORMAT));
        }
    } catch (SQLException ex) {
        logger.error(ex);
    }

    logger.info("Total documents indexed: " + count + ", " + getISOTime(TIME_STAMP_FORMAT));

    return count;
}