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:com.haulmont.yarg.loaders.impl.SqlDataLoader.java

@Override
public List<Map<String, Object>> loadData(ReportQuery reportQuery, BandData parentBand,
        Map<String, Object> params) {
    List resList;/* ww  w  .j  ava  2 s.  c o  m*/
    final List<OutputValue> outputValues = new ArrayList<OutputValue>();

    String query = reportQuery.getScript();
    if (StringUtils.isBlank(query)) {
        return Collections.emptyList();
    }

    try {
        if (Boolean.TRUE.equals(reportQuery.getProcessTemplate())) {
            query = processQueryTemplate(query, parentBand, params);
        }
        final QueryPack pack = prepareQuery(query, parentBand, params);

        ArrayList<Object> resultingParams = new ArrayList<Object>();
        QueryParameter[] queryParameters = pack.getParams();
        for (QueryParameter queryParameter : queryParameters) {
            if (queryParameter.isSingleValue()) {
                resultingParams.add(queryParameter.getValue());
            } else {
                resultingParams.addAll(queryParameter.getMultipleValues());
            }
        }

        resList = runQuery(reportQuery, pack.getQuery(), resultingParams.toArray(),
                new ResultSetHandler<List>() {
                    @Override
                    public List handle(ResultSet rs) throws SQLException {
                        List<Object[]> resList = new ArrayList<Object[]>();

                        while (rs.next()) {
                            ResultSetMetaData metaData = rs.getMetaData();
                            if (outputValues.size() == 0) {
                                for (int columnIndex = 1; columnIndex <= metaData
                                        .getColumnCount(); columnIndex++) {
                                    String columnName = metaData.getColumnLabel(columnIndex);
                                    OutputValue outputValue = new OutputValue(columnName);
                                    setCaseSensitiveSynonym(columnName, outputValue);
                                    outputValues.add(outputValue);
                                }
                            }

                            Object[] values = new Object[metaData.getColumnCount()];
                            for (int columnIndex = 0; columnIndex < metaData.getColumnCount(); columnIndex++) {
                                values[columnIndex] = convertOutputValue(rs.getObject(columnIndex + 1));
                            }
                            resList.add(values);
                        }

                        return resList;
                    }

                    private void setCaseSensitiveSynonym(String columnName, OutputValue outputValue) {
                        Matcher matcher = Pattern.compile("(?i)as\\s*(" + columnName + ")")
                                .matcher(pack.getQuery());
                        if (matcher.find()) {
                            outputValue.setSynonym(matcher.group(1));
                        }
                    }
                });
    } catch (DataLoadingException e) {
        throw e;
    } catch (Throwable e) {
        throw new DataLoadingException(
                String.format("An error occurred while loading data for data set [%s]", reportQuery.getName()),
                e);
    }

    return fillOutputData(resList, outputValues);
}

From source file:com.base2.kagura.core.report.connectors.FreemarkerSQLDataReportConnector.java

/**
 * Returns a preparedStatement result into a List of Maps. Not the most efficient way of storing the values, however
 * the results should be limited at this stage.
 * @param rows The result set.//from w ww. j  av a2  s.com
 * @return Mapped results.
 * @throws RuntimeException upon any error, adds values to "errors"
 */
public List<Map<String, Object>> resultSetToMap(ResultSet rows) {
    try {
        List<Map<String, Object>> beans = new ArrayList<Map<String, Object>>();
        int columnCount = rows.getMetaData().getColumnCount();
        while (rows.next()) {
            LinkedHashMap<String, Object> bean = new LinkedHashMap<String, Object>();
            beans.add(bean);
            for (int i = 0; i < columnCount; i++) {
                Object object = rows.getObject(i + 1);
                String columnLabel = rows.getMetaData().getColumnLabel(i + 1);
                String columnName = rows.getMetaData().getColumnName(i + 1);
                bean.put(StringUtils.defaultIfEmpty(columnLabel, columnName),
                        object != null ? object.toString() : "");
            }
        }
        return beans;
    } catch (Exception ex) {
        errors.add(ex.getMessage());
        throw new RuntimeException(ex);
    }
}

From source file:esavo.tap.formatter.ResultSet2JsonFormatter.java

@Override
protected int writeData(UwsJob job, ResultSet queryResult, DBColumn[] selectedColumns, JSONWriter out,
        TAPExecutionReport execReport) throws IOException, TAPException, InterruptedException, JSONException {
    out.array();//  w ww.j  av  a2  s.  co m
    int nbRows = 0;
    try {
        int nbColumns = queryResult.getMetaData().getColumnCount();
        while (queryResult.next()) {
            if (job.isPhaseAborted()) {
                break;
            }
            if (execReport.parameters.getMaxRec() > 0 && nbRows >= execReport.parameters.getMaxRec()) // that's to say: OVERFLOW !
                break;

            out.array();
            Object value;
            for (int i = 1; i <= nbColumns; i++) {
                value = formatValue(queryResult.getObject(i), selectedColumns[i - 1]);
                writeFieldValue(value, selectedColumns[i - 1], out);
                //               if (thread.isInterrupted())
                //                  throw new InterruptedException();
            }
            out.endArray();
            nbRows++;

            //            if (thread.isInterrupted())
            //               throw new InterruptedException();
        }
    } catch (SQLException se) {
        throw new TAPException("Job N" + execReport.jobID + " - Impossible to get the " + (nbRows + 1)
                + "-th rows from the given ResultSet !", se);
    }

    out.endArray();
    return nbRows;
}

From source file:com.abixen.platform.service.businessintelligence.multivisualization.service.impl.AbstractDatabaseService.java

private DataValueWeb getDataFromColumn(ResultSet row, String columnName) {
    try {//  w w w  .jav a2  s.c o m
        ResultSetMetaData resultSetMetaData = row.getMetaData();
        String columnTypeName = resultSetMetaData.getColumnTypeName(row.findColumn(columnName)).toUpperCase();
        if ("BIGINT".equals(columnTypeName)) {
            columnTypeName = "INTEGER";
        }
        if ("VARCHAR".equals(columnTypeName)) {
            columnTypeName = "STRING";
        }
        if ("FLOAT8".equals(columnTypeName)) {
            columnTypeName = "DOUBLE";
        }
        return getValueAsDataSourceValue(row, columnName, DataValueType.valueOf(columnTypeName));
    } catch (SQLException e) {
        throw new DataSourceValueException("Error when getting value from column. " + e.getMessage());
    }
}

From source file:net.fatlenny.datacitation.service.GitCitationDBService.java

private TableModel retrieveDatasetForQuery(String directory, String query, ObjectId head)
        throws CitationDBException {

    try (Connection conn = getCSVConnection(directory)) {
        Statement stmt = conn.createStatement();
        ResultSet set = stmt.executeQuery(query);
        ResultSetMetaData metaData = set.getMetaData();
        int columnCount = metaData.getColumnCount();

        String[] columns = new String[columnCount];
        for (int i = 1; i <= columnCount; i++) {
            columns[i - 1] = metaData.getColumnName(i);
        }//www .  jav a2s.c  o m

        TableModelMetaData tableMetaData = new DefaultTableModelMetaData(new DefaultRevision(head.getName()));
        DefaultTableModel model = new DefaultTableModel(tableMetaData, Arrays.asList(columns));

        while (set.next()) {
            String[] row = new String[columnCount];
            for (int i = 1; i <= columnCount; i++) {
                row[i - 1] = (String) set.getObject(i);

            }
            model.addRow(row);
        }

        return model;
    } catch (SQLException e) {
        throw new CitationDBException(e);
    }
}

From source file:com.piusvelte.hydra.MSSQLConnection.java

private JSONArray getResult(ResultSet rs) throws SQLException {
    JSONArray rows = new JSONArray();
    ResultSetMetaData rsmd = rs.getMetaData();
    String[] columnsArr = new String[rsmd.getColumnCount()];
    for (int c = 0, l = columnsArr.length; c < l; c++)
        columnsArr[c] = rsmd.getColumnName(c);
    while (rs.next()) {
        JSONArray rowData = new JSONArray();
        for (String column : columnsArr)
            rowData.add((String) rs.getObject(column));
        rows.add(rowData);/*w w w .j av a2  s  . com*/
    }
    return rows;
}

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

private String toString(ResultSet result) throws SQLException {
    StringBuilder sb = new StringBuilder();

    while (result.next()) {
        ResultSetMetaData metadata = result.getMetaData();
        int colCount = metadata.getColumnCount();
        sb.append(String.format("(%d) ", result.getRow()));
        for (int i = 1; i <= colCount; i++) {
            sb.append(" " + showColumn(i, result));
        }// w  ww  .  j  ava  2  s .  c  om
        sb.append("\n");
    }
    return sb.toString();
}

From source file:jp.terasoluna.fw.web.struts.action.DBMessageBeanMappingSqlQuery.java

/**
 * ?sDBMessageBeanl?B//from   w  w  w.  j av a2 s.  c  o m
 * 
 * @param rs ?s
 * 
 * @return ?slDBMessageBean
 * 
 * @throws SQLException ?SQL??
 */
protected DBMessageBean createDBMessageBean(ResultSet rs) throws SQLException {
    DBMessageBean msg = new DBMessageBean();
    int columnCount = 0;
    String key = null;
    String value = null;

    // ?bZ?[W\?[Xe?[uK?`
    // OX??[?B
    columnCount = rs.getMetaData().getColumnCount();
    if (columnCount != 2) {
        // SQLr?[2???ASQL
        log.error("SQL for DB message returns " + columnCount + " column(s) result set.");
        throw new SystemException(null, DB_MESSAGE_BEAN_MAPPING_SQL_QUERY);
    }

    // key?
    key = rs.getString(1);
    if (key == null || "".equals(key)) {
        if (log.isWarnEnabled()) {
            log.warn("DBMessage resource keys contain null or empty.");
        }
        key = "";
    }
    msg.setKey(key);

    // value?
    value = rs.getString(2);
    if (value == null) {
        value = "";
    }
    msg.setValue(value);

    return msg;
}

From source file:com.thinkbiganalytics.hive.service.HiveService.java

public QueryResult query(String query) throws DataAccessException {
    final DefaultQueryResult queryResult = new DefaultQueryResult(query);
    final List<QueryResultColumn> columns = new ArrayList<>();
    final Map<String, Integer> displayNameMap = new HashMap<>();
    if (query != null && !query.toLowerCase().startsWith("show")) {
        query = safeQuery(query);/*from ww  w  .j a va  2s.com*/
    }
    try {
        //  Setting in order to query complex formats like parquet
        jdbcTemplate.execute("set hive.optimize.index.filter=false");
        jdbcTemplate.query(query, new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                if (columns.isEmpty()) {
                    ResultSetMetaData rsMetaData = rs.getMetaData();
                    for (int i = 1; i <= rsMetaData.getColumnCount(); i++) {
                        String colName = rsMetaData.getColumnName(i);
                        DefaultQueryResultColumn column = new DefaultQueryResultColumn();
                        column.setField(rsMetaData.getColumnName(i));
                        String displayName = rsMetaData.getColumnLabel(i);
                        column.setHiveColumnLabel(displayName);
                        //remove the table name if it exists
                        displayName = StringUtils.substringAfterLast(displayName, ".");
                        Integer count = 0;
                        if (displayNameMap.containsKey(displayName)) {
                            count = displayNameMap.get(displayName);
                            count++;
                        }
                        displayNameMap.put(displayName, count);
                        column.setDisplayName(displayName + "" + (count > 0 ? count : ""));

                        column.setTableName(StringUtils.substringAfterLast(rsMetaData.getColumnName(i), "."));
                        column.setDataType(ParserHelper.sqlTypeToHiveType(rsMetaData.getColumnType(i)));
                        columns.add(column);
                    }
                    queryResult.setColumns(columns);
                }
                Map<String, Object> row = new LinkedHashMap<>();
                for (QueryResultColumn column : columns) {
                    row.put(column.getDisplayName(), rs.getObject(column.getHiveColumnLabel()));
                }
                queryResult.addRow(row);
                return row;
            }
        });

    } catch (DataAccessException dae) {
        dae.printStackTrace();
        throw dae;
    }
    return queryResult;

}

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

@Test
public void testSchemas() throws SQLException {
    CassandraStatement statement = (CassandraStatement) con.createStatement();
    ResultSet result = MetadataResultSets.makeSchemas(statement, null);

    System.out.println("--- testSchemas() ---");
    System.out.println(getColumnNames(result.getMetaData()));

    System.out.println(toString(result));
    System.out.println();//  w  ww  .j  ava  2 s  .c om

    result = MetadataResultSets.makeSchemas(statement, KEYSPACE2);
    System.out.println(toString(result));
    System.out.println();
}