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.onebusaway.nyc.webapp.actions.admin.ReportingAction.java

public String submit() throws Exception {
    Session session = null;/*from   ww  w  .  jav a 2  s  .  c o m*/
    Connection connection = null;
    Statement statement = null;
    ResultSet rs = null;
    try {
        session = sessionFactory.openSession();
        connection = getConnectionFromSession(session);
        connection.setReadOnly(true);

        statement = connection.createStatement();
        rs = statement.executeQuery(query);

    } catch (Exception e) {
        // make sure everything is closed if an exception was thrown
        try {
            rs.close();
        } catch (Exception ex) {
        }
        try {
            statement.close();
        } catch (Exception ex) {
        }
        try {
            connection.close();
        } catch (Exception ex) {
        }
        try {
            session.close();
        } catch (Exception ex) {
        }

        reportError = e.getMessage();
        // not really "success", but we'll use the same template with the error displayed
        return SUCCESS;
    }

    // final so the output generator thread can close it
    final Session finalSession = session;
    final Connection finalConnection = connection;
    final Statement finalStatement = statement;
    final ResultSet finalRS = rs;

    final PipedInputStream pipedInputStream = new PipedInputStream();
    final PipedOutputStream pipedOutputStream = new PipedOutputStream(pipedInputStream);

    executorService.execute(new Runnable() {

        @Override
        public void run() {
            try {
                // column labels
                ResultSetMetaData metaData = finalRS.getMetaData();
                int columnCount = metaData.getColumnCount();

                for (int i = 0; i < columnCount; i++) {
                    String columnName = metaData.getColumnName(i + 1);
                    byte[] bytes = columnName.getBytes();

                    if (i > 0)
                        pipedOutputStream.write(columnDelimiter);

                    pipedOutputStream.write(bytes);
                }

                pipedOutputStream.write(newline);

                // column values
                while (finalRS.next()) {
                    for (int i = 0; i < columnCount; i++) {
                        String value = finalRS.getString(i + 1);

                        if (value == null)
                            value = "null";
                        else {
                            // remove returns
                            value = value.replaceAll("\n|\r", "");
                        }

                        byte[] valueBytes = value.getBytes();

                        if (i > 0)
                            pipedOutputStream.write(columnDelimiter);

                        pipedOutputStream.write(valueBytes);
                    }

                    pipedOutputStream.write(newline);
                }
            } catch (Exception e) {
            } finally {
                try {
                    pipedOutputStream.close();
                } catch (IOException e) {
                }
                try {
                    finalRS.close();
                } catch (SQLException e) {
                }
                try {
                    finalStatement.close();
                } catch (SQLException e) {
                }
                try {
                    finalConnection.close();
                } catch (SQLException e) {
                }
                try {
                    finalSession.close();
                } catch (Exception e) {
                }
            }
        }
    });

    // the input stream will get populated by the piped output stream
    inputStream = pipedInputStream;
    return "download";
}

From source file:cn.clickvalue.cv2.model.rowmapper.BeanPropertyRowMapper.java

/**
 * Extract the values for all columns in the current row.
 * <p>Utilizes public setters and result set metadata.
 * @see java.sql.ResultSetMetaData/*  w  w w. ja v a  2s.c  o  m*/
 */
public Object mapRow(ResultSet rs, int rowNumber) throws SQLException {
    Assert.state(this.mappedClass != null, "Mapped class was not specified");
    Object mappedObject = BeanUtils.instantiateClass(this.mappedClass);
    BeanWrapper bw = PropertyAccessorFactory.forBeanPropertyAccess(mappedObject);
    initBeanWrapper(bw);

    ResultSetMetaData rsmd = rs.getMetaData();
    int columnCount = rsmd.getColumnCount();
    for (int index = 1; index <= columnCount; index++) {
        String column = lookupColumnName(rsmd, index).toLowerCase();
        PropertyDescriptor pd = (PropertyDescriptor) this.mappedFields.get(column);
        if (pd != null) {
            try {
                Object value = getColumnValue(rs, index, pd);
                if (logger.isDebugEnabled() && rowNumber == 0) {
                    logger.debug("Mapping column '" + column + "' to property '" + pd.getName() + "' of type "
                            + pd.getPropertyType());
                }
                bw.setPropertyValue(pd.getName(), value);
            } catch (NotWritablePropertyException ex) {
                throw new DataRetrievalFailureException(
                        "Unable to map column " + column + " to property " + pd.getName(), ex);
            }
        }
    }

    return mappedObject;
}

From source file:org.h2gis.drivers.geojson.GeoJsonWriteDriver.java

/**
 * Write the spatial table to GeoJSON format.
 *
 * @param progress//from w ww . j a v  a 2s  . c o m
 * @throws SQLException
 */
private void writeGeoJson(ProgressVisitor progress) throws SQLException, IOException {
    FileOutputStream fos = null;
    try {
        fos = new FileOutputStream(fileName);
        // Read Geometry Index and type
        final TableLocation parse = TableLocation.parse(tableName,
                JDBCUtilities.isH2DataBase(connection.getMetaData()));
        List<String> spatialFieldNames = SFSUtilities.getGeometryFields(connection, parse);
        if (spatialFieldNames.isEmpty()) {
            throw new SQLException(String.format("The table %s does not contain a geometry field", tableName));
        }

        // Read table content
        Statement st = connection.createStatement();
        try {
            JsonFactory jsonFactory = new JsonFactory();
            JsonGenerator jsonGenerator = jsonFactory.createGenerator(new BufferedOutputStream(fos),
                    JsonEncoding.UTF8);

            // header of the GeoJSON file
            jsonGenerator.writeStartObject();
            jsonGenerator.writeStringField("type", "FeatureCollection");
            writeCRS(jsonGenerator,
                    SFSUtilities.getAuthorityAndSRID(connection, parse, spatialFieldNames.get(0)));
            jsonGenerator.writeArrayFieldStart("features");

            ResultSet rs = st.executeQuery(String.format("select * from `%s`", tableName));

            try {
                ResultSetMetaData resultSetMetaData = rs.getMetaData();
                int geoFieldIndex = JDBCUtilities.getFieldIndex(resultSetMetaData, spatialFieldNames.get(0));

                cacheMetadata(resultSetMetaData);
                while (rs.next()) {
                    writeFeature(jsonGenerator, rs, geoFieldIndex);
                }
                progress.endStep();
                // footer
                jsonGenerator.writeEndArray();
                jsonGenerator.writeEndObject();
                jsonGenerator.flush();
                jsonGenerator.close();

            } finally {
                rs.close();
            }
        } finally {
            st.close();
        }
    } catch (FileNotFoundException ex) {
        throw new SQLException(ex);

    } finally {
        try {
            if (fos != null) {
                fos.close();
            }
        } catch (IOException ex) {
            throw new SQLException(ex);
        }
    }
}

From source file:com.duowan.common.spring.jdbc.BeanPropertyRowMapper.java

/**
 * Extract the values for all columns in the current row.
 * <p>Utilizes public setters and result set metadata.
 * @see java.sql.ResultSetMetaData//from   www  . j a  v  a2  s  . co  m
 */
public T mapRow(ResultSet rs, int rowNumber) throws SQLException {
    Assert.state(this.mappedClass != null, "Mapped class was not specified");
    T mappedObject = BeanUtils.instantiate(this.mappedClass);
    BeanWrapper bw = PropertyAccessorFactory.forBeanPropertyAccess(mappedObject);
    initBeanWrapper(bw);

    ResultSetMetaData rsmd = rs.getMetaData();
    int columnCount = rsmd.getColumnCount();
    Set<String> populatedProperties = (isCheckFullyPopulated() ? new HashSet<String>() : null);

    for (int index = 1; index <= columnCount; index++) {
        String column = JdbcUtils.lookupColumnName(rsmd, index);
        PropertyDescriptor pd = this.mappedFields.get(column.replaceAll(" ", "").toLowerCase());
        if (pd != null) {
            try {
                Object value = getColumnValue(rs, index, pd);
                if (logger.isDebugEnabled() && rowNumber == 0) {
                    logger.debug("Mapping column '" + column + "' to property '" + pd.getName() + "' of type "
                            + pd.getPropertyType());
                }
                try {
                    bw.setPropertyValue(pd.getName(), value);
                } catch (TypeMismatchException e) {
                    if (value == null && primitivesDefaultedForNullValue) {
                        logger.debug("Intercepted TypeMismatchException for row " + rowNumber + " and column '"
                                + column + "' with value " + value + " when setting property '" + pd.getName()
                                + "' of type " + pd.getPropertyType() + " on object: " + mappedObject);
                    } else {
                        throw e;
                    }
                }
                if (populatedProperties != null) {
                    populatedProperties.add(pd.getName());
                }
            } catch (NotWritablePropertyException ex) {
                throw new DataRetrievalFailureException(
                        "Unable to map column " + column + " to property " + pd.getName(), ex);
            }
        }
    }

    if (populatedProperties != null && !populatedProperties.equals(this.mappedProperties)) {
        throw new InvalidDataAccessApiUsageException("Given ResultSet does not contain all fields "
                + "necessary to populate object of class [" + this.mappedClass + "]: " + this.mappedProperties);
    }

    return mappedObject;
}

From source file:com.centeractive.ws.builder.soap.XmlUtils.java

public static Document addResultSetXmlPart(Element resultsElement, ResultSet rs, Document xmlDocumentResult)
        throws SQLException {
    // resultSet = statement.getResultSet();
    // connection to an ACCESS MDB
    ResultSetMetaData rsmd = rs.getMetaData();
    Element resultSetElement = xmlDocumentResult.createElement("ResultSet");

    resultSetElement.setAttribute("fetchSize", String.valueOf(rs.getFetchSize()));
    resultsElement.appendChild(resultSetElement);

    int colCount = rsmd.getColumnCount();
    while (rs.next()) {
        Element rowElement = xmlDocumentResult.createElement("Row");
        rowElement.setAttribute("rowNumber", String.valueOf(rs.getRow()));

        resultsElement.appendChild(rowElement);
        for (int ii = 1; ii <= colCount; ii++) {
            String columnName = "";
            if (!StringUtils.isBlank(rsmd.getTableName(ii))) {
                columnName += (rsmd.getTableName(ii)).toUpperCase() + ".";
            }/*from   ww w  .jav a 2s .  com*/
            columnName += (rsmd.getColumnName(ii)).toUpperCase();
            String value = rs.getString(ii);
            Element node = xmlDocumentResult.createElement(createXmlName(columnName));
            if (!StringUtils.isBlank(value)) {
                Text textNode = xmlDocumentResult.createTextNode(value.toString());
                node.appendChild(textNode);
            }
            rowElement.appendChild(node);
        }
        resultSetElement.appendChild(rowElement);
    }
    return xmlDocumentResult;
}

From source file:com.xpfriend.fixture.cast.temp.Database.java

private DynaClass getDynaClass(ResultSet resultSet) throws SQLException {
    ResultSetMetaData md = resultSet.getMetaData();
    int count = md.getColumnCount();
    DynaProperty[] properties = new DynaProperty[count];
    for (int i = 0; i < properties.length; i++) {
        int column = i + 1;
        Class<?> type = TypeConverter.getJavaType(md.getColumnType(column), md.getColumnTypeName(column),
                md.getPrecision(column), md.getScale(column));
        String name = getColumnLabel(md, column);
        properties[i] = new DynaProperty(name, type);
    }/*from ww w  .  j  a  va  2 s . com*/
    return new BasicDynaClass(null, null, properties);
}

From source file:com.adaptris.jdbc.connection.FailoverConnection.java

private void testConnection() throws SQLException {
    Statement stmt = sqlConnection.createStatement();
    ResultSet rs = null;
    try {//from ww w .  j av a 2  s. c  o  m
        if (isEmpty(config.getTestStatement())) {
            return;
        }
        if (config.getAlwaysValidateConnection()) {
            if (isDebugMode()) {
                rs = stmt.executeQuery(config.getTestStatement());
                if (rs.next()) {
                    StringBuffer sb = new StringBuffer("TestStatement Results - ");
                    ResultSetMetaData rsm = rs.getMetaData();
                    for (int i = 1; i <= rsm.getColumnCount(); i++) {
                        sb.append("[");
                        sb.append(rsm.getColumnName(i));
                        sb.append("=");
                        try {
                            sb.append(rs.getString(i));
                        } catch (Exception e) {
                            sb.append("'unknown'");
                        }
                        sb.append("] ");
                    }
                    logR.trace(sb.toString());
                }
            } else {
                stmt.execute(config.getTestStatement());
            }
        }
    } finally {
        JdbcUtil.closeQuietly(rs);
        JdbcUtil.closeQuietly(stmt);
    }
}

From source file:com.p5solutions.core.jpa.orm.rowbinder.EntityRowBinder.java

@Override
public T mapRow(ResultSet rs, int rowNum) throws SQLException {
    EntityDetail<T> entityDetail = getEntityUtility().getEntityDetail(entityClass);
    T entity = entityDetail.newInstance();
    ResultSetMetaData metaData = rs.getMetaData();

    // add the entity to the join filter list.
    getJoinFilter().add(this.currentQueryIdentifier, entity);

    boolean isDebug = logger.isDebugEnabled();

    if (isDebug) {
        // attempt to parse the resultset into the entity.********
        logger.debug("Mapping row for entity type: " + entityClass);
    }/* w w w.j a va2s  .  c  o m*/

    for (ParameterBinder pb : entityDetail.getParameterBinders()) {

        // skip over transient methods
        if (pb.isTransient()) {
            continue;
        }

        String columnName = pb.getColumnNameAnyJoinOrColumn();

        if (columnName == null) {
            columnName = pb.getBindingNameUpper();
        }

        // find the column index based on column name, case insensitive
        // TODO deprecated as per change to adding meta-data code generation to EntityUtility# 
        int columnIndex = findColumnIndex(metaData, columnName);

        //int columnIndex = pb.getColumnMetaData().getColumnIndex();

        if (columnIndex == -1) {
            NoColumnFoundInResultSetException e = new NoColumnFoundInResultSetException(
                    entityDetail.getEntityClass(), columnName);
            logger.debug(e.toString());
            throw e;
        }

        // get the value
        Object resultValue = rs.getObject(columnIndex);

        if (doColumn(pb, entity, resultValue)) {

            if (isDebug) {
                logger.debug(" -> Column [" + pb.getColumnMetaData().getColumnName() + "] with value ["
                        + (resultValue == null ? " NULL " : resultValue) + "] to path " + pb.getBindingPath());
            }
        } else if (doJoinColumn(pb, entity, resultValue)) {
            // / TODO ??
        }
    }

    return entity;
}

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

/**
 * Verify that the driver navigates a resultset according to the JDBC rules.
 * In all cases, the resultset should be pointing to the first record, which can
 * be read without invoking {@code next()}.
 * @throws Exception  Fatal error.//from  w w w .j  ava 2s  . c  om
 */
@Test
public void testResultSetNavigation() throws Exception {
    Statement statement = con.createStatement();

    String truncate = "TRUNCATE regressiontest;";
    statement.execute(truncate);

    String insert1 = "INSERT INTO regressiontest (keyname,bValue,iValue) VALUES( 'key0',true, 2000);";
    statement.executeUpdate(insert1);

    String insert2 = "INSERT INTO regressiontest (keyname,bValue) VALUES( 'key1',false);";
    statement.executeUpdate(insert2);

    String select = "SELECT * from regressiontest;";

    ResultSet result = statement.executeQuery(select);

    ResultSetMetaData metadata = result.getMetaData();

    int colCount = metadata.getColumnCount();

    System.out.println("Before doing a next()");
    System.out.printf("(%d) ", result.getRow());
    for (int i = 1; i <= colCount; i++) {
        System.out.print(showColumn(i, result) + " ");
    }
    System.out.println();

    System.out.println("Fetching each row with a next()");
    while (result.next()) {
        metadata = result.getMetaData();
        colCount = metadata.getColumnCount();
        System.out.printf("(%d) ", result.getRow());
        for (int i = 1; i <= colCount; i++) {
            System.out.print(showColumn(i, result) + " ");
        }
        System.out.println();
    }
}

From source file:jp.primecloud.auto.tool.management.db.SQLExecuter.java

public List<List<Object>> showColumn(String sql) throws SQLException, Exception {
    Connection con = null;/*from  w ww .j  a v  a 2 s.co  m*/
    Statement stmt = null;
    ResultSet rs = null;
    log.info("[" + sql + "] ???");
    List<List<Object>> results = new ArrayList<List<Object>>();
    try {
        con = dbConnector.getConnection();
        stmt = con.createStatement();
        rs = stmt.executeQuery(sql);
        ResultSetMetaData rsMetaData = rs.getMetaData();

        int size = rsMetaData.getColumnCount();
        List<Object> columnNames = new ArrayList<Object>();
        for (int n = 1; n <= size; n++) {
            columnNames.add(rsMetaData.getColumnName(n));
        }
        results.add(columnNames);
        while (rs.next()) {
            List<Object> columns = new ArrayList<Object>();
            for (int i = 1; i <= size; i++) {
                columns.add(rs.getObject(i));
            }
            results.add(columns);
        }
        log.info("[" + sql + "] ????");
    } catch (SQLException e) {
        log.error(e.getMessage(), e);

        throw new SQLException(e);
    } catch (Exception e) {
        log.error(e.getMessage(), e);
        throw new Exception(e);
    } finally {
        try {
            dbConnector.closeConnection(con, stmt, rs);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    return results;
}