Example usage for java.sql ResultSet getMetaData

List of usage examples for java.sql ResultSet getMetaData

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSet
  6. getMetaData

Introduction

In this page you can find the example usage for java.sql ResultSet getMetaData.

Prototype

ResultSetMetaData getMetaData() throws SQLException;

Source Link

Document

Retrieves the number, types and properties of this ResultSet object's columns.

Usage

From source file:org.string_db.jdbc.Postgres2HSQLDB.java

void dumpTableData(final String schema, final String table, String filter, final StringBuilder result,
        final String[] columnsToInclude) {
    final String selectedColumns = columnsToInclude != null ? Joiner.on(',').join(columnsToInclude) : "*";
    final String sql = "SELECT " + selectedColumns + " FROM " + schema + "." + table
            + (filter != null ? " WHERE " + filter : "");
    final String columns = columnsToInclude != null ? "(" + Joiner.on(", ").join(columnsToInclude) + ")" : null;

    result.append("SET SCHEMA ").append(schema).append(";\n");
    jdbcTemplate.query(sql, new RowCallbackHandler() {
        @Override//  w w  w.j a v  a2s  .  com
        public void processRow(ResultSet rs) throws SQLException {
            result.append("INSERT INTO ").append(table);
            if (columns != null)
                result.append(columns);
            result.append(" VALUES(");
            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                if (i > 0) {
                    result.append(", ");
                }
                Object value = rs.getObject(i + 1);
                if (value == null) {
                    result.append("NULL");
                } else {
                    String outputValue = value.toString();
                    // In a string started with ' (singlequote) use '' (two singlequotes) to create a ' (singlequote).
                    //see http://www.hsqldb.org/doc/guide/ch09.html#expression-section
                    //XXX use Connection.escapeString ?
                    outputValue = outputValue.replaceAll("'", "''");
                    result.append("'" + outputValue + "'");
                }
            }
            result.append(");\n");
        }
    });
}

From source file:com.flexive.core.search.genericSQL.GenericSQLForeignTableSelector.java

protected GenericSQLForeignTableSelector(String mainColumn, String tableName, String linksOn,
        boolean hasTranslationTable, String translatedColumn) {
    FxSharedUtils.checkParameterNull(tableName, "tableName");
    FxSharedUtils.checkParameterNull(linksOn, "linksOn");
    Connection con = null;/*from  w ww  . j a v  a2 s .c om*/
    Statement stmt = null;
    this.tableName = tableName;
    this.linksOn = linksOn;
    this.mainColumn = mainColumn;
    this.hasTranslationTable = hasTranslationTable;
    this.translatedColumn = translatedColumn != null ? translatedColumn.toUpperCase() : null;
    try {
        con = Database.getDbConnection();
        stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName + StorageManager.getLimit(false, 0));
        ResultSetMetaData md = rs.getMetaData();
        for (int pos = 1; pos <= md.getColumnCount(); pos++) {
            String columnName = md.getColumnName(pos);
            FxDataType columnType;
            switch (md.getColumnType(pos)) {
            case java.sql.Types.CHAR:
                if (md.getPrecision(pos) == 1) {
                    columnType = FxDataType.Boolean; //oracle
                    break;
                }
            case java.sql.Types.VARCHAR:
            case java.sql.Types.LONGVARCHAR:
            case java.sql.Types.CLOB:
                columnType = FxDataType.String1024;
                break;
            case java.sql.Types.BOOLEAN:
            case java.sql.Types.BIT:
                columnType = FxDataType.Boolean;
                break;
            case java.sql.Types.TINYINT:
            case java.sql.Types.SMALLINT:
            case java.sql.Types.INTEGER:
                columnType = FxDataType.Number;
                break;
            case java.sql.Types.DECIMAL:
                columnType = FxDataType.Double;
                break;
            case java.sql.Types.FLOAT:
                columnType = FxDataType.Float;
                break;
            case java.sql.Types.NUMERIC:
            case java.sql.Types.BIGINT:
                if ("CREATED_AT".equalsIgnoreCase(columnName) || "MODIFIED_AT".equalsIgnoreCase(columnName))
                    columnType = FxDataType.DateTime;
                else if ("CAT_TYPE".equals(columnName) && "FXS_ACL".equals(tableName)) {
                    columnType = FxDataType.Number;
                } else
                    columnType = FxDataType.LargeNumber;
                break;
            case java.sql.Types.DATE:
                columnType = FxDataType.Date;
                break;
            case java.sql.Types.TIME:
            case java.sql.Types.TIMESTAMP:
                columnType = FxDataType.DateTime;
                break;
            default:
                if (LOG.isInfoEnabled()) {
                    LOG.info("Assigning String to " + tableName + "." + columnName + " found type="
                            + md.getColumnType(pos));
                }
                columnType = FxDataType.String1024;
            }
            columns.put(columnName.toUpperCase(), columnType);
        }

    } catch (Throwable t) {
        @SuppressWarnings({ "ThrowableInstanceNeverThrown" })
        FxSqlSearchException ex = new FxSqlSearchException(LOG, "ex.sqlSearch.fieldSelector.initializeFailed",
                tableName, t.getMessage());
        LOG.error(ex.getMessage(), ex);
        throw ex.asRuntimeException();
    } finally {
        Database.closeObjects(GenericSQLForeignTableSelector.class, con, stmt);
    }
}

From source file:com.sfs.dao.BackupDAOImpl.java

/**
 * Gets the table contents./* w w w  .  ja  v  a  2  s .co  m*/
 *
 * @param sql the sql string
 *
 * @return the table contents
 */
@SuppressWarnings("unchecked")
private Collection<String> getTableContents(final String sql) {
    Collection<String> tableContents = null;
    try {
        tableContents = this.getJdbcTemplateReader().query(sql, new RowMapper() {
            public Object mapRow(final ResultSet rs, final int rowNum) throws SQLException {

                int colCount = rs.getMetaData().getColumnCount();

                final StringBuffer sqlValues = new StringBuffer();

                sqlValues.append("(");

                for (int x = 1; x < colCount + 1; x++) {
                    String data = null;
                    try {
                        data = rs.getString(x);
                    } catch (SQLException sqe) {
                        dataLogger.debug("Error getting data field: " + sqe.getMessage());
                    }

                    sqlValues.append("\'");
                    if (data != null) {
                        sqlValues.append(filterData(data));
                    }
                    sqlValues.append("\'");
                    if (x < colCount) {
                        sqlValues.append(", ");
                    }
                }
                sqlValues.append(")");

                return sqlValues.toString();
            }
        });
    } catch (IncorrectResultSizeDataAccessException ie) {
        dataLogger.debug("No table contents results found: " + ie.getMessage());
    }
    if (tableContents == null) {
        tableContents = new ArrayList<String>();
    }
    return tableContents;
}

From source file:com.micromux.cassandra.jdbc.MetadataResultSetsTest.java

@Test
public void testTableName() throws SQLException {
    CassandraPreparedStatement statement = (CassandraPreparedStatement) con
            .prepareStatement("select * from " + KEYSPACE1 + ".test1");
    ResultSet result = statement.executeQuery();

    System.out.println("--- testTableName() ---");
    ResultSetMetaData meta = result.getMetaData();
    assertEquals("test1", meta.getTableName(1));

}

From source file:com.streamsets.pipeline.lib.jdbc.multithread.CDCJdbcRunnable.java

@Override
public void createAndAddRecord(ResultSet rs, TableRuntimeContext tableRuntimeContext, BatchContext batchContext)
        throws SQLException, StageException {
    ResultSetMetaData md = rs.getMetaData();

    LinkedHashMap<String, Field> fields = jdbcUtil.resultSetToFields(rs, commonSourceConfigBean,
            errorRecordHandler, tableJdbcConfigBean.unknownTypeAction, recordHeader);

    Map<String, String> columnOffsets = new HashMap<>();

    // Generate Offset includes __$start_lsn, __$seqval, and __$operation
    columnOffsets.put(MSQueryUtil.CDC_START_LSN, rs.getString(MSQueryUtil.CDC_START_LSN));
    columnOffsets.put(MSQueryUtil.CDC_SEQVAL, rs.getString(MSQueryUtil.CDC_SEQVAL));

    try {/*ww  w . j a v  a  2  s. com*/
        columnOffsets.put(MSQueryUtil.CDC_OPERATION, rs.getString(MSQueryUtil.CDC_OPERATION));
    } catch (Exception ex) {
        LOG.trace("$__operation is not supported in this SQL Server");
    }

    if (commonSourceConfigBean.txnWindow > 0) {
        columnOffsets.put(MSQueryUtil.CDC_TXN_WINDOW, Integer.toString(commonSourceConfigBean.txnWindow));
    }

    String offsetFormat = OffsetQueryUtil.getOffsetFormat(columnOffsets);

    Record record = context.createRecord(tableRuntimeContext.getQualifiedName() + "::" + offsetFormat);
    record.set(Field.createListMap(fields));

    //Set Column Headers
    jdbcUtil.setColumnSpecificHeaders(record,
            Collections.singleton(tableRuntimeContext.getSourceTableContext().getTableName()), md,
            JDBC_NAMESPACE_HEADER);

    //Set SDC Operation Header
    int op = MSOperationCode.convertToJDBCCode(rs.getInt(MSQueryUtil.CDC_OPERATION));
    record.getHeader().setAttribute(OperationType.SDC_OPERATION_TYPE, String.valueOf(op));

    for (String fieldName : recordHeader) {
        try {
            record.getHeader().setAttribute(JDBC_NAMESPACE_HEADER + fieldName,
                    rs.getString(fieldName) != null ? rs.getString(fieldName) : "NULL");
        } catch (SQLException ex) {
            //no-op
            LOG.trace("the column name {} does not exists in the table: {}", fieldName,
                    tableRuntimeContext.getQualifiedName());
        }
    }

    batchContext.getBatchMaker().addRecord(record);

    offsets.put(tableRuntimeContext.getOffsetKey(), offsetFormat);
}

From source file:db.migration.V055__UpdateECTS.java

public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
    LOG.info("migrate()...");

    // Get all organisaatiometadatas where there are strings to process
    List<Map> resultSet = jdbcTemplate.query(
            "SELECT id,hakutoimistoectsemail,hakutoimistoectsnimi,hakutoimistoectspuhelin,hakutoimistoectstehtavanimike FROM organisaatiometadata WHERE hakutoimistoectsemail<>'' OR hakutoimistoectsnimi<>'' OR hakutoimistoectspuhelin<>'' OR hakutoimistoectstehtavanimike<>''",
            new RowMapper<Map>() {
                @Override/*ww w  .  j a  va2s  .c  o m*/
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map r = new HashMap<String, Object>();

                    ResultSetMetaData metadata = rs.getMetaData();
                    for (int i = 1; i <= metadata.getColumnCount(); i++) {
                        String cname = metadata.getColumnName(i);
                        int ctype = metadata.getColumnType(i);

                        switch (ctype) {
                        case Types.VARCHAR: // hakutoimistoectsemail,hakutoimistoectsnimi,hakutoimistoectspuhelin,hakutoimistoectstehtavanimike
                            r.put(cname, rs.getString(cname));
                            break;

                        case Types.BIGINT: // id
                            r.put(cname, rs.getInt(cname));
                            break;

                        default:
                            break;
                        }
                    }

                    LOG.debug("  read from db : organisaatiometadata = {}", r);

                    return r;
                }
            });

    // Move strings to monikielinenteksti_values
    for (Map orgmd : resultSet) {

        handleOrganisaatiometadata(orgmd, jdbcTemplate);

    }

    LOG.info("migrate()... done.");
}

From source file:de.tudarmstadt.lt.nlkg.DT.java

void queryDT(String word, int max_results) {

    try {/*from  w  ww . ja  v  a  2  s.  co m*/
        // Class.forName(_mysql_driver).newInstance();
        Connection _mysql_conn = DriverManager.getConnection(
                _mysql_url() + "?useUnicode=true&characterEncoding=UTF-8", _mysql_userName, _mysql_password);
        System.out.println("Connected to the database");
        String query = String.format("SELECT * FROM `dt` WHERE word1 = '%s' ORDER BY count DESC LIMIT 1,%d;",
                word, max_results);
        Statement st = _mysql_conn.createStatement();
        ResultSet rs = st.executeQuery(query);

        // System.out.println(rs.getMetaData().getColumnCount());
        // System.out.println(rs.getFetchSize());
        int nc = rs.getMetaData().getColumnCount();
        String format_str = new String(new char[nc + 1]).replace("\0", "%-20s\t") + "%n";
        // System.out.println(format_str);

        List<String> values = new ArrayList<String>();
        // header
        values.add("row");
        for (int i = 1; i <= nc; i++)
            values.add(rs.getMetaData().getColumnName(i));
        System.out.format(format_str, values.toArray());

        // data
        while (rs.next()) {
            values.clear();
            values.add(String.valueOf(rs.getRow()));
            for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++)
                values.add(rs.getString(i));
            System.out.format(format_str, values.toArray());
        }

        st.close();
        _mysql_conn.close();

    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:gridool.util.csv.CsvWriter.java

public int writeAll(@Nonnull final ResultSet rs, @Nonnull final String nullStr, final boolean includeHeaders)
        throws SQLException {
    final ResultSetMetaData meta = rs.getMetaData();
    if (includeHeaders) {
        writeColumnNames(meta);// w  w w . j  a v a 2 s.c  om
    }
    final int numColumns = meta.getColumnCount();
    final String[] columnClasses = new String[numColumns + 1];
    for (int i = 1; i <= numColumns; i++) {
        String className = meta.getColumnClassName(i);
        columnClasses[i] = JAVA_STRING_CLASS_NAME.equals(className) ? JAVA_STRING_CLASS_NAME : className;
    }
    int numRows = 0;
    while (rs.next()) {
        for (int i = 1; i <= numColumns; i++) {
            if (i != 1) {
                write(separator);
            }
            final String column = rs.getString(i);
            if (column == null) {
                write(nullStr);
            } else if (JAVA_STRING_CLASS_NAME == columnClasses[i]) { // for speed optimization
                write(QUOTE);
                write(quoteData(column));
                write(QUOTE);
            } else {
                write(column);
            }
        }
        write(lineSeparator);
        numRows++;
    }
    flush();
    return numRows;
}

From source file:com.github.tosdan.utils.sql.BasicRowProcessorMod.java

/**
 * Convert a <code>ResultSet</code> row into a <code>Map</code>.  This
 * implementation returns a <code>Map</code> with case insensitive column
 * names as keys.  Calls to <code>map.get("COL")</code> and
 * <code>map.get("col")</code> return the same value.
 * @see org.apache.commons.dbutils.RowProcessor#toMap(java.sql.ResultSet)
 * @param rs ResultSet that supplies the map data
 * @throws SQLException if a database access error occurs
 * @return the newly created Map/*from w  w w  .ja v a2 s. c  om*/
 */
@Override
public Map<String, Object> toMap(ResultSet rs) throws SQLException {
    Map<String, Object> result = new LinkedHashMap<String, Object>();
    ResultSetMetaData rsmd = rs.getMetaData();
    int cols = rsmd.getColumnCount();

    for (int i = 1; i <= cols; i++) {
        Object obj = rs.getObject(i);
        result.put(rsmd.getColumnName(i), obj);
    }

    return result;
}

From source file:com.recomdata.transmart.data.export.util.FileWriterUtil.java

@SuppressWarnings("unused")
private boolean validate(ResultSet resultSet, String[] headerValues) throws Exception {
    boolean valid = true;
    try {//from  ww w  .  ja v a 2s . co  m
        ResultSetMetaData rsmd = resultSet.getMetaData();
        if (null == resultSet || (null != resultSet && rsmd.getColumnCount() <= 0)) {
            valid = false;
            log.error((null != outputFile) ? outputFile.getAbsolutePath() : "" + " :: Empty resultSet");
        }

        if (null == outputFile) {
            valid = false;
            log.error("Invalid outputFile");
        }
    } catch (SQLException e) {
        valid = false;
        log.error((null != outputFile) ? outputFile.getAbsolutePath() : "" + " :: Empty resultSet");
    }

    return valid;
}