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:at.ac.tuwien.inso.subcat.reporter.Reporter.java

private void exportRows(ExporterConfig config, Project project, int commitDictId, int bugDictId,
        Settings settings, final ReportWriter formatter, String outputPath, Map<String, Object> vars)
        throws SQLException, Exception {
    formatter.init(project, settings, outputPath);
    model.rawForeach(config.getQuery(), vars, new ResultCallback() {

        @Override// w w  w. ja  va2s  .c  o  m
        public void processResult(ResultSet res) throws SemanticException, SQLException, Exception {
            ResultSetMetaData meta = res.getMetaData();
            String[] titles = new String[meta.getColumnCount()];
            for (int i = 0; i < titles.length; i++) {
                titles[i] = meta.getColumnLabel(i + 1);
            }

            formatter.writeHeader(titles);

            while (res.next()) {
                String[] data = new String[titles.length];
                for (int i = 0; i < data.length; i++) {
                    data[i] = res.getString(i + 1);
                }

                formatter.writeSet(data);
            }

            formatter.writeFooter(titles);
        }
    });
}

From source file:edu.education.ucsb.muster.MusterServlet.java

private String getOutputAsJson(String database, String query, long limit) throws SQLException {

    // The output string
    StringBuffer out = new StringBuffer();

    // Cache StringBuffer length as needed
    int len;//from ww  w  .j  a  va2s.  com

    // Database operations
    DatabaseDefinition db = conf.getDatabase(database);

    // //register the driver
    registerDriver(db.driver, db.url);

    // // Connect to the database
    Connection connection = DriverManager.getConnection(db.url, db.username, db.password);

    // // Perform the query
    PreparedStatement statement = connection.prepareStatement(query);
    statement.execute();
    ResultSet results = statement.getResultSet();

    // Get and write the column names
    ResultSetMetaData meta = results.getMetaData();
    int columnCount = meta.getColumnCount();
    LinkedList<String> columns = new LinkedList<String>();
    for (int i = 1; i < columnCount + 1; i++) {
        // We're only dealing with JSON, so the column names should be
        // JavaScript-friendly.
        columns.add(StringEscapeUtils.escapeJavaScript(meta.getColumnName(i)));
    }
    out.append("{\n  \"columns\" : [ ");

    // Add column names in JSON format
    for (String column : columns) {
        out.append('"' + column + "\", ");
    }

    // remove the trailing ", " and add a line break and close the array
    len = out.length();
    out.delete(len - 2, len);
    out.append(" ],\n");

    // Add column values
    out.append("  \"results\" : [ \n");

    for (int i = 0; i < limit && results.next(); i++) {
        out.append(rowAsJson(results, columns));
    }

    // remove the trailing ", "
    len = out.length();
    out.delete(len - 2, len);
    out.append("\n  ]\n");
    out.append("}");

    return out.toString();
}

From source file:org.jfree.data.jdbc.JDBCCategoryDataset.java

/**
 * Populates the dataset by executing the supplied query against the
 * existing database connection.  If no connection exists then no action
 * is taken.//from   w  ww  . j  a  va2  s. c o  m
 * <p>
 * The results from the query are extracted and cached locally, thus
 * applying an upper limit on how many rows can be retrieved successfully.
 *
 * @param con  the connection.
 * @param query  the query.
 *
 * @throws SQLException if there is a problem executing the query.
 */
public void executeQuery(Connection con, String query) throws SQLException {

    Statement statement = null;
    ResultSet resultSet = null;
    try {
        statement = con.createStatement();
        resultSet = statement.executeQuery(query);
        ResultSetMetaData metaData = resultSet.getMetaData();

        int columnCount = metaData.getColumnCount();

        if (columnCount < 2) {
            throw new SQLException("JDBCCategoryDataset.executeQuery() : insufficient columns "
                    + "returned from the database.");
        }

        // Remove any previous old data
        int i = getRowCount();
        while (--i >= 0) {
            removeRow(i);
        }

        while (resultSet.next()) {
            // first column contains the row key...
            Comparable rowKey = resultSet.getString(1);
            for (int column = 2; column <= columnCount; column++) {

                Comparable columnKey = metaData.getColumnName(column);
                int columnType = metaData.getColumnType(column);

                switch (columnType) {
                case Types.TINYINT:
                case Types.SMALLINT:
                case Types.INTEGER:
                case Types.BIGINT:
                case Types.FLOAT:
                case Types.DOUBLE:
                case Types.DECIMAL:
                case Types.NUMERIC:
                case Types.REAL: {
                    Number value = (Number) resultSet.getObject(column);
                    if (this.transpose) {
                        setValue(value, columnKey, rowKey);
                    } else {
                        setValue(value, rowKey, columnKey);
                    }
                    break;
                }
                case Types.DATE:
                case Types.TIME:
                case Types.TIMESTAMP: {
                    Date date = (Date) resultSet.getObject(column);
                    Number value = new Long(date.getTime());
                    if (this.transpose) {
                        setValue(value, columnKey, rowKey);
                    } else {
                        setValue(value, rowKey, columnKey);
                    }
                    break;
                }
                case Types.CHAR:
                case Types.VARCHAR:
                case Types.LONGVARCHAR: {
                    String string = (String) resultSet.getObject(column);
                    try {
                        Number value = Double.valueOf(string);
                        if (this.transpose) {
                            setValue(value, columnKey, rowKey);
                        } else {
                            setValue(value, rowKey, columnKey);
                        }
                    } catch (NumberFormatException e) {
                        // suppress (value defaults to null)
                    }
                    break;
                }
                default:
                    // not a value, can't use it (defaults to null)
                    break;
                }
            }
        }

        fireDatasetChanged(new DatasetChangeInfo());
        //TODO: fill in real change info
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (Exception e) {
                // report this?
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (Exception e) {
                // report this?
            }
        }
    }
}

From source file:com.norconex.collector.http.db.impl.derby.DerbyCrawlURLDatabase.java

private CrawlURL toCrawlURL(ResultSet rs) throws SQLException {
    if (rs == null) {
        return null;
    }//from  w w  w  .j a  v a  2  s  .  c o m
    int colCount = rs.getMetaData().getColumnCount();
    CrawlURL crawlURL = new CrawlURL(rs.getString("url"), rs.getInt("depth"));
    if (colCount > COLCOUNT_SITEMAP) {
        crawlURL.setSitemapChangeFreq(rs.getString("smChangeFreq"));
        BigDecimal bigP = rs.getBigDecimal("smPriority");
        if (bigP != null) {
            crawlURL.setSitemapPriority(bigP.floatValue());
        }
        BigDecimal bigLM = rs.getBigDecimal("smLastMod");
        if (bigLM != null) {
            crawlURL.setSitemapLastMod(bigLM.longValue());
        }
        if (colCount > COLCOUNT_ALL) {
            crawlURL.setDocChecksum(rs.getString("docchecksum"));
            crawlURL.setHeadChecksum(rs.getString("headchecksum"));
            crawlURL.setStatus(CrawlStatus.valueOf(rs.getString("status")));
        }
    }
    return crawlURL;
}

From source file:it.unibas.spicy.persistence.csv.ExportCSVInstances.java

public void appendCSVInstances(MappingTask mappingTask, HashMap<String, String> directoryPaths, int scenarioNo)
        throws SQLException, DAOException, IOException {
    //connection to Postgres
    IConnectionFactory connectionFactory = new SimpleDbConnectionFactory();
    Connection connection = getConnectionToPostgres(connectionFactory);
    try {//w w  w .jav a 2s.  c om
        Statement statement = connection.createStatement();

        //get table names from target database
        DatabaseMetaData databaseMetaData = connection.getMetaData();
        String[] tableTypes = new String[] { "TABLE" };
        ResultSet tableResultSet = databaseMetaData.getTables(SpicyEngineConstants.MAPPING_TASK_DB_NAME,
                SpicyEngineConstants.TARGET_SCHEMA_NAME + scenarioNo, null, tableTypes);
        //for each table
        while (tableResultSet.next()) {
            String tableName = tableResultSet.getString("TABLE_NAME");
            String filePath = directoryPaths.get(tableName);

            if ((filePath != null) && (!filePath.equals(""))) {
                ResultSet allRows = statement.executeQuery("SELECT * FROM "
                        + SpicyEngineConstants.TARGET_SCHEMA_NAME + scenarioNo + ".\"" + tableName + "\";");
                //no of columns
                int columnCount = allRows.getMetaData().getColumnCount();
                //column names
                String[] columnNames = new String[columnCount];
                for (int i = 1; i <= columnCount; i++) {
                    columnNames[i - 1] = allRows.getMetaData().getColumnName(i);
                }
                if (checkDocForCSVColumns(columnCount, columnNames, filePath))
                    appendToCSVDocument(allRows, columnCount, filePath);
                else {
                    throw new DAOException("Column names do not match those of the csv file");
                }
                allRows.close();
            } else {
                System.out.println("The CSV file " + tableName + " cannot be found!");
                System.exit(-1);
            }
        }
    } finally {
        //close connection
        if (connection != null)
            connectionFactory.close(connection);
    }
}

From source file:kenh.xscript.database.beans.ResultSetBean.java

/**
 * Use result set to initial a bean.//w w  w  .  j  a va2 s .  c  o  m
 * 
 * @param rs
 * @param includeFieldName
 * @throws SQLException
 * @throws IllegalAccessException
 * @throws InstantiationException
 */
public ResultSetBean(ResultSet rs, boolean includeFieldName)
        throws SQLException, IllegalAccessException, InstantiationException {
    include_field_name = includeFieldName;

    LazyDynaClass beanClass = new LazyDynaClass();

    ResultSetMetaData m = rs.getMetaData();
    for (int i = 1; i <= m.getColumnCount(); i++) {
        Column c = new Column();

        try {
            c.catalogName = m.getCatalogName(i);
        } catch (SQLException e) {
        }
        try {
            c.className = m.getColumnClassName(i);
        } catch (SQLException e) {
        }
        try {
            c.displaySize = m.getColumnDisplaySize(i);
        } catch (SQLException e) {
        }
        try {
            c.label = m.getColumnLabel(i);
        } catch (SQLException e) {
        }
        try {
            c.name = m.getColumnName(i);
        } catch (SQLException e) {
        }
        try {
            c.type = m.getColumnType(i);
        } catch (SQLException e) {
        }
        try {
            c.typeName = m.getColumnTypeName(i);
        } catch (SQLException e) {
        }
        try {
            c.precision = m.getPrecision(i);
        } catch (SQLException e) {
        }
        try {
            c.scale = m.getScale(i);
        } catch (SQLException e) {
        }
        try {
            c.schemaName = m.getSchemaName(i);
        } catch (SQLException e) {
        }
        try {
            c.tableName = m.getTableName(i);
        } catch (SQLException e) {
        }

        beanClass.add(m.getColumnLabel(i).toLowerCase());
        beanClass.add("" + i);

        cols.add(c);
    }

    DynaBean colBean = beanClass.newInstance();
    int i = 1;
    for (Column col : cols) {
        String field = col.getLabel().toLowerCase();
        colBean.set(field, col.getLabel());
        colBean.set("" + i, col.getLabel());
        i++;
    }

    if (include_field_name)
        rows.add(colBean);

    while (rs.next()) {
        DynaBean bean = beanClass.newInstance();
        i = 1;
        for (Column c : cols) {
            String field = c.getLabel().toLowerCase();
            Object obj = rs.getObject(field);
            bean.set(field, obj);
            bean.set("" + i, obj);
            i++;
        }
        rows.add(bean);
    }

}

From source file:com.iih5.smartorm.model.DbExecutor.java

/**
 * Model/*w  w w .  ja  v  a 2  s. c  o m*/
 * @param sql
 * @param paras
 * @param model
 * @param <T>
 * @return
 * @
 */
<T> List<T> queryList(String sql, Object[] paras, final Class<T> model) {
    final Set<String> columnMeta = new HashSet<String>();
    return jdbc.query(sql, paras, new RowMapper<T>() {
        public T mapRow(ResultSet rs, int rowNum) throws SQLException {
            try {
                if (columnMeta.size() == 0) {
                    for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                        String column = rs.getMetaData().getColumnLabel(i + 1);
                        columnMeta.add(column);
                    }
                }
                Model mModel = (Model) model.newInstance();
                Field[] fields = mModel.getClass().getFields();
                if (0 < fields.length) {
                    for (Field f : fields) {
                        if (columnMeta.contains(f.getName())) {
                            f.set(mModel, rs.getObject(f.getName()));
                        }
                    }
                } else {
                    ResultSetMetaData rad = rs.getMetaData();
                    int columnCount = rad.getColumnCount();
                    Map<String, Object> attrs = mModel.getAttrs();
                    for (int i = 1; i <= columnCount; i++) {
                        Object value = rs.getObject(i);
                        attrs.put(rad.getColumnName(i), value);
                    }
                }
                return (T) mModel;
            } catch (Exception e) {
                e.printStackTrace();
            }
            return null;
        }
    });
}

From source file:io.github.huherto.springyRecords.RecordMapper.java

/**
 * Extract the values for all columns in the current row.
 * <p>Utilizes public setters and result set metadata.
 * @see java.sql.ResultSetMetaData//from w w w  .j a  v a 2  s  .  c om
 */
@Override
public T mapRow(ResultSet rs, int rowNumber) throws SQLException {
    Assert.state(this.mappedClass != null, "Mapped class was not specified");

    T mappedObject;
    try {
        mappedObject = mappedClass.newInstance();
    } catch (Exception ex) {
        throw new RuntimeException(ex);
    }

    ResultSetMetaData rsmd = rs.getMetaData();
    int columnCount = rsmd.getColumnCount();

    for (int index = 1; index <= columnCount; index++) {
        String column = JdbcUtils.lookupColumnName(rsmd, index);
        Field field = this.mappedFields.get(column.replaceAll(" ", ""));
        if (field != null) {
            Object value = getColumnValue(rs, index, field);
            if (logger.isTraceEnabled() && rowNumber == 0) {
                logger.trace("Mapping column '" + column + "' to property '" + field.getName() + "' of type "
                        + field.getType());
            }
            try {
                field.set(mappedObject, value);
            } catch (IllegalArgumentException e) {
                if (value == null && primitivesDefaultedForNullValue) {
                    logger.debug("Intercepted IllegalArgumentException for row " + rowNumber + " and column '"
                            + column + "' with value " + value + " when setting property '" + field.getName()
                            + "' of type " + field.getType() + " on object: " + mappedObject);
                } else {
                    throw e;
                }
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
    }

    return mappedObject;
}

From source file:jeeves.resources.dbms.Dbms.java

private Element buildResponse(ResultSet rs, Hashtable<String, String> formats) throws SQLException {
    ResultSetMetaData md = rs.getMetaData();

    int colNum = md.getColumnCount();

    // --- retrieve name and type of fields

    Vector<String> vHeaders = new Vector<String>();
    Vector<Integer> vTypes = new Vector<Integer>();

    for (int i = 0; i < colNum; i++) {
        vHeaders.add(md.getColumnLabel(i + 1).toLowerCase());
        vTypes.add(new Integer(md.getColumnType(i + 1)));
    }//w w  w.ja  v  a  2  s. com

    // --- build the jdom tree

    Element root = new Element(Jeeves.Elem.RESPONSE);

    while (rs.next()) {
        Element record = new Element(Jeeves.Elem.RECORD);

        for (int i = 0; i < colNum; i++) {
            String name = vHeaders.get(i).toString();
            int type = ((Integer) vTypes.get(i)).intValue();
            record.addContent(buildElement(rs, i, name, type, formats));
        }
        root.addContent(record);
    }
    return root;
}

From source file:it.unibas.spicy.persistence.idgenerator.utils.ReadDB.java

public ArrayList<InputDataModel> readSourceDatabase(ArrayList<ColumnMatcherModel> cmmList)
        throws SQLException, IOException, ClassNotFoundException {
    ArrayList<InputDataModel> inputData = new ArrayList<>();
    ArrayList<String> configurationProperties = getExportDatabaseConfig();
    Connection connection = null;
    try {/*from  w  ww  .j a  v a  2 s  .  c o  m*/
        connection = getConnectionToDatabase(configurationProperties.get(0),
                configurationProperties.get(1) + configurationProperties.get(4), configurationProperties.get(2),
                configurationProperties.get(3));
        Statement statement = connection.createStatement();

        String columnsToQuery = "";
        for (ColumnMatcherModel cmm : cmmList) {
            if (!cmm.getSourceColumn().equalsIgnoreCase("CONSTANT_VALUE_SOURCE")) {
                columnsToQuery += cmm.getSourceColumn() + ",";
            }
        }
        ResultSet tableRows = null;
        if (columnsToQuery.length() > 0) {
            columnsToQuery = columnsToQuery.substring(0, columnsToQuery.length() - 1);
            tableRows = statement.executeQuery("SELECT " + columnsToQuery + " FROM " + table + ";");
        } else {
            tableRows = statement.executeQuery("SELECT * FROM " + table + ";");
        }

        ResultSetMetaData rsmd = tableRows.getMetaData();
        int columnsNumber = rsmd.getColumnCount();
        while (tableRows.next()) {
            InputDataModel idm = new InputDataModel();
            for (int i = 1; i <= columnsNumber; i++) {
                idm.addValue(String.valueOf(tableRows.getObject(i)));
                if (columnsToQuery.length() > 0) {
                    idm.addKey(columnsToQuery.split(",")[i - 1]);
                } else {
                    idm.addKey("none");
                }
            }
            inputData.add(idm);
        }
    } catch (ClassNotFoundException | SQLException e) {
        System.err.println(e.getMessage());
        System.exit(-1);
    } finally {
        if (connection != null)
            connection.close();
    }
    return inputData;
}