Example usage for java.sql ResultSetMetaData getColumnTypeName

List of usage examples for java.sql ResultSetMetaData getColumnTypeName

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

Introduction

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

Prototype

String getColumnTypeName(int column) throws SQLException;

Source Link

Document

Retrieves the designated column's database-specific type name.

Usage

From source file:Student.java

public static void checkData() throws Exception {
        Class.forName("org.hsqldb.jdbcDriver");
        Connection conn = DriverManager.getConnection("jdbc:hsqldb:data/tutorial", "sa", "");
        Statement st = conn.createStatement();

        ResultSet mrs = conn.getMetaData().getTables(null, null, null, new String[] { "TABLE" });
        while (mrs.next()) {
            String tableName = mrs.getString(3);
            System.out.println("\n\n\n\nTable Name: " + tableName);

            ResultSet rs = st.executeQuery("select * from " + tableName);
            ResultSetMetaData metadata = rs.getMetaData();
            while (rs.next()) {
                System.out.println(" Row:");
                for (int i = 0; i < metadata.getColumnCount(); i++) {
                    System.out.println("    Column Name: " + metadata.getColumnLabel(i + 1) + ",  ");
                    System.out.println("    Column Type: " + metadata.getColumnTypeName(i + 1) + ":  ");
                    Object value = rs.getObject(i + 1);
                    System.out.println("    Column Value: " + value + "\n");
                }//from w  ww .j a  v a 2 s.c  om
            }
        }
    }

From source file:com.xqdev.sql.MLSQL.java

private static void addResultSet(Element root, ResultSet rs) throws SQLException {
    Namespace sql = root.getNamespace();

    ResultSetMetaData rsmd = rs.getMetaData();
    int columnCount = rsmd.getColumnCount();
    while (rs.next()) {
        Element tuple = new Element("tuple", sql);
        for (int i = 1; i <= columnCount; i++) {
            String colName = rsmd.getColumnName(i); // names aren't guaranteed OK in xml
            String colTypeName = rsmd.getColumnTypeName(i);

            // Decode a BLOB if one is found and place it into the result as a encoded Base 64 string
            String colValue = "";
            if ("BLOB".equalsIgnoreCase(colTypeName)) {
                Blob b = rs.getBlob(i);
                if (b != null && b.length() > 0) {
                    Base64 b64 = new Base64();
                    String b64Blob = b64.encodeBase64String(b.getBytes(1, (int) b.length()));
                    colValue = b64Blob;
                } else
                    colValue = "";
            } else {
                colValue = rs.getString(i);
            }//from   w ww. jav a  2 s .  c  o  m

            boolean wasNull = rs.wasNull();
            Element elt = new Element(colName);
            if (wasNull) {
                elt.setAttribute("null", "true");
            }
            if ("UNKNOWN".equalsIgnoreCase(colTypeName)) {
                tuple.addContent(elt.setText("UNKNOWN TYPE")); // XXX ugly
            } else {
                tuple.addContent(elt.setText(colValue));
            }
        }
        root.addContent(tuple);
    }

}

From source file:es.tena.foundation.util.POIUtil.java

private static void generate(Writer out, ResultSet rset, String encoding) throws Exception {
    SimpleDateFormat sdfecha = new SimpleDateFormat("dd/MM/yyyy");
    SpreadSheetWriter sw = new SpreadSheetWriter(out);
    sw.beginSheet();//from  w ww  .j av a2  s . c o m
    ResultSetMetaData rmd = rset.getMetaData();

    HashMap<Integer, String> columnas = new HashMap<Integer, String>();
    //creamos la cabecera del excel
    sw.insertRow(0);
    int numeroColumnas = rmd.getColumnCount();
    for (int numeroColumna = 1; numeroColumna <= numeroColumnas; numeroColumna++) {
        String nombreColumna = rmd.getColumnName(numeroColumna).toUpperCase();
        columnas.put(numeroColumna, rmd.getColumnTypeName(numeroColumna));
        sw.createCell(numeroColumna - 1, nombreColumna);
    }

    sw.endRow();

    //creamos los datos del excel
    int rowNum = 1;
    while (rset.next()) {
        sw.insertRow(rowNum);
        for (int columna = 1; columna <= numeroColumnas; columna++) {
            if (columnas.get(columna).equals("CHAR") || columnas.get(columna).equals("VARCHAR2")) {
                String valor = "";
                valor = rset.getString(columna);
                valor = fixPOICellValue(valor, encoding);
                if (valor == null || valor.toLowerCase().equals("null")) {
                    valor = "";
                }
                sw.createCell(columna - 1, valor);
            } else if (columnas.get(columna).equals("DATE")) {
                Date fecha = new Date();
                fecha = rset.getDate(columna);
                String valor = (fecha != null) ? sdfecha.format(fecha) : null;
                if (valor == null || valor.toLowerCase().equals("null")) {
                    valor = "";
                }
                sw.createCell(columna - 1, valor);
            } else if (columnas.get(columna).equals("NUMBER")) {
                String valor = "";
                valor = rset.getString(columna);
                if (valor == null || valor.toLowerCase().equals("null")) {
                    valor = "";
                }
                sw.createCell(columna - 1, valor);
            } else {
                String valor = "";
                valor = rset.getString(columna);
                valor = fixPOICellValue(valor, encoding);
                if (valor == null || valor.toLowerCase().equals("null")) {
                    valor = "";
                }
                sw.createCell(columna - 1, valor);
            }
        }
        sw.endRow();
        rowNum++;
    }
    sw.endSheet();
}

From source file:com.cloudera.impala.testutil.ImpalaJdbcClient.java

/**
 * Executes one or more queries using the given ImpalaJdbcClient. Multiple queries
 * should be seperated using semi-colons.
 * @throws SQLException//ww w .  ja  va  2s.co m
 */
private static void execQuery(ImpalaJdbcClient client, String queryString)
        throws SQLException, NumberFormatException {

    String[] queries = queryString.trim().split(";");
    for (String query : queries) {
        query = query.trim();
        if (query.indexOf(" ") > -1) {
            if (query.substring(0, query.indexOf(" ")).equalsIgnoreCase("use")) {
                String[] split_query = query.split(" ");
                String db_name = split_query[split_query.length - 1];
                client.changeDatabase(db_name);
                client.getStatement().close();
                continue;
            }
        }
        long startTime = System.currentTimeMillis();
        ResultSet res = client.execQuery(query);
        ResultSetMetaData meta = res.getMetaData();
        ArrayList<String> arrayList = Lists.newArrayList();

        // This token (and the [END] token) are used to help parsing the result output
        // for test verification purposes.
        LOG.info("----[START]----");
        int rowCount = 0;
        while (res.next()) {
            arrayList.clear();
            for (int i = 1; i <= meta.getColumnCount(); ++i) {
                // Format the value based on the column type
                String colVal = formatColumnValue(res.getString(i), meta.getColumnTypeName(i));
                arrayList.add(colVal);
            }
            LOG.info(Joiner.on(",").join(arrayList));
            ++rowCount;
        }
        LOG.info("----[END]----");
        long endTime = System.currentTimeMillis();
        float seconds = (endTime - startTime) / 1000F;
        LOG.info("Returned " + rowCount + " row(s) in " + seconds + "s");

        // TODO: To work around a JDBC driver issue (CDH-10035), make sure the Statement
        // is closed after every query.
        client.getStatement().close();
    }
}

From source file:org.apache.impala.testutil.ImpalaJdbcClient.java

/**
 * Executes one or more queries using the given ImpalaJdbcClient. Multiple queries
 * should be seperated using semi-colons.
 * @throws SQLException//from w  w w . j av a2s  .  c o m
 */
private static void execQuery(ImpalaJdbcClient client, String queryString)
        throws SQLException, NumberFormatException {

    String[] queries = queryString.trim().split(";");
    for (String query : queries) {
        query = query.trim();
        if (query.indexOf(" ") > -1) {
            if (query.substring(0, query.indexOf(" ")).equalsIgnoreCase("use")) {
                String[] split_query = query.split(" ");
                String db_name = split_query[split_query.length - 1];
                client.changeDatabase(db_name);
                client.getStatement().close();
                continue;
            }
        }
        long startTime = System.currentTimeMillis();
        ResultSet res = client.execQuery(query);
        ResultSetMetaData meta = res.getMetaData();
        ArrayList<String> arrayList = Lists.newArrayList();

        // This token (and the [END] token) are used to help parsing the result output
        // for test verification purposes.
        LOG.info("----[START]----");
        int rowCount = 0;
        while (res.next()) {
            arrayList.clear();
            for (int i = 1; i <= meta.getColumnCount(); ++i) {
                // Format the value based on the column type
                String colVal = formatColumnValue(res.getString(i), meta.getColumnTypeName(i));
                arrayList.add(colVal);
            }
            LOG.info(Joiner.on(",").join(arrayList));
            ++rowCount;
        }
        LOG.info("----[END]----");
        long endTime = System.currentTimeMillis();
        float seconds = (endTime - startTime) / 1000F;
        LOG.info("Returned " + rowCount + " row(s) in " + seconds + "s");

        // Make sure the Statement is closed after every query.
        client.getStatement().close();
    }
}

From source file:org.orbisgis.corejdbc.ReadTable.java

/**
 * Return a concatened and human readable format of provided result set
 * @param rs result set to read//from   w  ww  . ja  v a  2s.c o m
 * @param maxFieldLength Maximum field length to print
 * @param maxPrintedRows Maximum printed rows
 * @param addColumns Add column header
 * @param alignColumns Align columns by using padding
 * @param resultSetFilter Accept or refuse rows by implementing this interface
 * @return human readable format of provided result set
 * @throws SQLException
 */
public static String resultSetToString(ResultSet rs, int maxFieldLength, int maxPrintedRows, boolean addColumns,
        boolean alignColumns, ResultSetFilter resultSetFilter) throws SQLException {
    // Print headers
    ResultSetMetaData metaData = rs.getMetaData();
    int columnCount = metaData.getColumnCount();
    StringBuilder lines = new StringBuilder();
    StringBuilder formatStringBuilder = new StringBuilder();
    String[] header = new String[columnCount];
    for (int idColumn = 1; idColumn <= columnCount; idColumn++) {
        header[idColumn - 1] = metaData.getColumnLabel(idColumn) + "(" + metaData.getColumnTypeName(idColumn)
                + ")";
        if (alignColumns) {
            formatStringBuilder.append("%-");
            formatStringBuilder.append(maxFieldLength);
            formatStringBuilder.append("s ");
        } else {
            formatStringBuilder.append("%s ");
        }
    }
    if (addColumns) {
        lines.append(String.format(formatStringBuilder.toString(), header));
        lines.append("\n");
    }
    int shownLines = 0;
    NumberFormat decimalFormat = NumberFormat.getInstance(Locale.getDefault());
    decimalFormat.setGroupingUsed(false);
    decimalFormat.setMaximumFractionDigits(16);
    while (rs.next() && shownLines < maxPrintedRows) {
        if (resultSetFilter.printRow(rs)) {
            String[] row = new String[columnCount];
            for (int idColumn = 1; idColumn <= columnCount; idColumn++) {
                Object valObj = rs.getObject(idColumn);
                String value;
                if (valObj instanceof Number) {
                    value = decimalFormat.format(valObj);
                } else {
                    value = rs.getString(idColumn);
                }
                if (value != null) {
                    if (columnCount > 1 && value.length() > maxFieldLength) {
                        value = value.substring(0, maxFieldLength - 2) + "..";
                    }
                } else {
                    value = "NULL";
                }
                row[idColumn - 1] = value;
            }
            shownLines++;
            lines.append(String.format(formatStringBuilder.toString(), row));
            lines.append("\n");
        }
    }
    if (lines.length() != 0) {
        return lines.toString();
    } else {
        return I18N.tr("No attributes to show");
    }
}

From source file:org.jabsorb.ext.DataList.java

/**
 * Build an array of ColumnMetaData object from a ResultSetMetaData object.
 *
 * @param rmd ResultSetMetaData to build ColumnMetaData from.
 * @return ColumnMetaData array or null if ResultSetMetaData is null.
 *
 * @throws SQLException if there is a problem processing the 
 * ResultSetMetaData object.//ww  w. ja v a2 s . c  o  m
 */
public static ColumnMetaData[] buildColumnMetaDataFromResultSetMetaData(ResultSetMetaData rmd)
        throws SQLException {
    if (rmd == null) {
        return null;
    }

    int j = rmd.getColumnCount();

    ColumnMetaData[] cmd = new ColumnMetaData[j];

    for (int i = 1; i <= j; i++) {
        ColumnMetaData c = new ColumnMetaData();

        c.setColumnName(rmd.getColumnName(i));
        c.setCatalogName(rmd.getCatalogName(i));
        c.setColumnClassName(rmd.getColumnClassName(i));
        c.setColumnDisplaySize(rmd.getColumnDisplaySize(i));
        c.setColumnLabel(rmd.getColumnLabel(i));
        c.setColumnType(rmd.getColumnType(i));
        c.setColumnTypeName(rmd.getColumnTypeName(i));
        c.setPrecision(rmd.getPrecision(i));
        c.setScale(rmd.getScale(i));
        c.setSchemaName(rmd.getSchemaName(i));
        c.setTableName(rmd.getTableName(i));
        c.setAutoIncrement(rmd.isAutoIncrement(i));
        c.setCaseSensitive(rmd.isCaseSensitive(i));
        c.setCurrency(rmd.isCurrency(i));
        c.setNullable(rmd.isNullable(i));
        c.setReadOnly(rmd.isReadOnly(i));
        c.setSearchable(rmd.isSearchable(i));
        c.setSigned(rmd.isSigned(i));
        c.setWritable(rmd.isWritable(i));
        c.setDefinitelyWritable(rmd.isDefinitelyWritable(i));

        cmd[i - 1] = c;
    }
    return cmd;
}

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);
        }/*  ww w  .ja  v a 2s.  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:jongo.handler.JongoResultSetHandler.java

/**
 * Converts a ResultSet to a Map. Important to note that DATE, TIMESTAMP & TIME objects generate
 * a {@linkplain org.joda.time.DateTime} object using {@linkplain org.joda.time.format.ISODateTimeFormat}.
 * @param resultSet a {@linkplain java.sql.ResultSet}
 * @return a Map with the column names as keys and the values. null if something goes wrong.
 *///from w  w w.  j a  v a 2 s .com
public static Map<String, String> resultSetToMap(ResultSet resultSet) {
    Map<String, String> map = new HashMap<String, String>();
    try {
        int columnCount = resultSet.getMetaData().getColumnCount();

        l.trace("Mapping a result set with " + columnCount + " columns to a Map");

        ResultSetMetaData meta = resultSet.getMetaData();
        for (int i = 1; i < columnCount + 1; i++) {
            String colName = meta.getColumnName(i).toLowerCase();
            int colType = meta.getColumnType(i);
            String v = resultSet.getString(i);
            if (colType == Types.DATE) {
                v = new DateTime(resultSet.getDate(i)).toString(dateFTR);
                l.trace("Mapped DATE column " + colName + " with value : " + v);
            } else if (colType == Types.TIMESTAMP) {
                v = new DateTime(resultSet.getTimestamp(i)).toString(dateTimeFTR);
                l.trace("Mapped TIMESTAMP column " + colName + " with value : " + v);
            } else if (colType == Types.TIME) {
                v = new DateTime(resultSet.getTimestamp(i)).toString(timeFTR);
                l.trace("Mapped TIME column " + colName + " with value : " + v);
            } else {
                l.trace("Mapped " + meta.getColumnTypeName(i) + " column " + colName + " with value : " + v);
            }
            map.put(colName, v);
        }
    } catch (SQLException e) {
        l.error("Failed to map ResultSet");
        l.error(e.getMessage());
        return null;
    }

    return map;
}

From source file:org.jboss.bqt.client.results.xml.XMLCompareResults.java

/**
 * Helper to convert results into records and record first batch response
 * time.//from  w  w w. java  2  s.c o  m
 * 
 * @param results
 * @param batchSize
 * @param resultsHolder
 *            Modified - results added by this method.
 * @return List of sorted results.
 * @throws QueryTestFailedException
 *             replaced SQLException.
 */
private static final long convertResults(final ResultSet results, final long batchSize,
        ExpectedResultsHolder resultsHolder) throws QueryTestFailedException {

    long firstBatchResponseTime = 0;
    final List<List<Object>> records = new ArrayList<List<Object>>();
    final List<String> columnTypeNames = new ArrayList<String>();
    final List<String> columnTypes = new ArrayList<String>();

    final ResultSetMetaData rsMetadata;
    final int colCount;

    if (results != null) {
        // Get column info
        try {
            rsMetadata = results.getMetaData();
            colCount = rsMetadata.getColumnCount();
            // Read types of all columns
            for (int col = 1; col <= colCount; col++) {
                columnTypeNames.add(rsMetadata.getColumnName(col));
                columnTypes.add(rsMetadata.getColumnTypeName(col));
            }
        } catch (SQLException qre) {
            throw new QueryTestFailedException("Can't get results metadata: " + qre.getMessage()); //$NON-NLS-1$
        }

        // Get rows
        try {
            // Read all the rows
            for (int row = 0; results.next(); row++) {
                final List<Object> currentRecord = new ArrayList<Object>(colCount);
                // Read values for this row
                for (int col = 1; col <= colCount; col++) {
                    currentRecord.add(results.getObject(col));
                }
                records.add(currentRecord);
                // If this row is the (fetch size - 1)th row, record first batch
                // response time
                if (row == batchSize) {
                    firstBatchResponseTime = System.currentTimeMillis();
                }
            }
        } catch (SQLException qre) {
            throw new QueryTestFailedException("Can't get results: " + qre.getMessage()); //$NON-NLS-1$
        }
    }

    // Set info on resultsHolder
    resultsHolder.setRows(records);
    resultsHolder.setIdentifiers(columnTypeNames);
    resultsHolder.setTypes(columnTypes);

    return firstBatchResponseTime;
}