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.gdcn.modules.db.jdbc.processor.CamelBeanProcessor.java

/**
 * Convert a <code>ResultSet</code> into a <code>List</code> of JavaBeans.
 * This implementation uses reflection and <code>BeanInfo</code> classes to
 * match column names to bean property names. Properties are matched to
 * columns based on several factors://from   www. j a va 2  s. c om
 * <br/>
 * <ol>
 *     <li>
 *     The class has a writable property with the same name as a column.
 *     The name comparison is case insensitive.
 *     </li>
 *
 *     <li>
 *     The column type can be converted to the property's set method
 *     parameter type with a ResultSet.get* method.  If the conversion fails
 *     (ie. the property was an int and the column was a Timestamp) an
 *     SQLException is thrown.
 *     </li>
 * </ol>
 *
 * <p>
 * Primitive bean properties are set to their defaults when SQL NULL is
 * returned from the <code>ResultSet</code>.  Numeric fields are set to 0
 * and booleans are set to false.  Object bean properties are set to
 * <code>null</code> when SQL NULL is returned.  This is the same behavior
 * as the <code>ResultSet</code> get* methods.
 * </p>
 * @param <T> The type of bean to create
 * @param rs ResultSet that supplies the bean data
 * @param type Class from which to create the bean instance
 * @throws SQLException if a database access error occurs
 * @return the newly created List of beans
 */
public <T> List<T> toBeanList(ResultSet rs, Class<T> type) throws SQLException {
    List<T> results = new ArrayList<T>();

    if (!rs.next()) {
        return results;
    }

    PropertyDescriptor[] props = this.propertyDescriptors(type);
    ResultSetMetaData rsmd = rs.getMetaData();
    int[] columnToProperty = this.mapColumnsToProperties(rsmd, props);

    do {
        results.add(this.createBean(rs, type, props, columnToProperty));
    } while (rs.next());

    return results;
}

From source file:computer_store.GUI.java

private void fillCompTable(javax.swing.JTable table, java.sql.ResultSet rs) {
    try {//  w  w w. j  a va2 s  .  com

        //To remove previously added rows
        while (table.getRowCount() > 0) {
            ((javax.swing.table.DefaultTableModel) table.getModel()).removeRow(0);
        }
        int columns = rs.getMetaData().getColumnCount();
        int rows = 0;
        java.util.ArrayList<String> helper = new java.util.ArrayList();
        //Adding column titles
        Object[] ids = new Object[columns];
        for (int i = 1; i <= columns; i++) {
            ids[i - 1] = rs.getMetaData().getColumnName(i);
        }
        ((javax.swing.table.DefaultTableModel) table.getModel()).setColumnIdentifiers(ids);
        //Adding rows from ResultSet to table model.
        while (rs.next()) {
            helper.add(rs.getString("name"));
            rows++;
            Object[] row = new Object[columns];
            for (int i = 1; i <= columns; i++) {
                row[i - 1] = rs.getObject(i);
            }
            ((javax.swing.table.DefaultTableModel) table.getModel()).insertRow(rs.getRow() - 1, row);
        }

        //Adding new column with restock numbers            
        Object[] restock = new Object[rows];
        for (int i = 0; i < restock.length; i++) {
            restock[i] = handler.getCompRestock(helper.get(i));
        }
        ((javax.swing.table.DefaultTableModel) table.getModel()).addColumn("# to Restock", restock);
        //Adding selling prices
        Object[] sellPrice = new Object[rows];
        for (int i = 0; i < sellPrice.length; i++) {
            sellPrice[i] = ((int) jTable1.getValueAt(i, 1)) * 1.3;
        }
        ((javax.swing.table.DefaultTableModel) table.getModel()).addColumn("Selling price", sellPrice);

        rs.close();
    } catch (Exception e) {
        System.out.print(e);
    }
}

From source file:net.tirasa.connid.bundles.soap.wssample.ProvisioningImpl.java

@Override
public List<WSUser> query(Operand query) {
    LOG.debug("Query request received");

    List<WSUser> results = new ArrayList<WSUser>();

    Connection conn = null;//from   www  . j ava2s  .  com
    try {

        String queryString = "SELECT * FROM user" + (query == null ? "" : " WHERE " + query.toString());

        queryString = queryString.replaceAll("__NAME__", "userId").replaceAll("__UID__", "userId")
                .replaceAll("__PASSWORD__", "password");

        LOG.debug("Execute query: {}", queryString);

        if (queryString == null || queryString.length() == 0) {
            throw new SQLException("Invalid query [" + queryString + "]");
        }

        conn = connect();
        Statement statement = conn.createStatement();

        ResultSet rs = statement.executeQuery(queryString);

        ResultSetMetaData metaData = rs.getMetaData();
        LOG.debug("Metadata: {}", metaData);

        while (rs.next()) {
            WSUser user = new WSUser();

            for (int i = 0; i < metaData.getColumnCount(); i++) {
                WSAttributeValue attr = new WSAttributeValue();
                attr.setName(metaData.getColumnLabel(i + 1));
                if (StringUtils.isNotBlank(rs.getString(i + 1))) {
                    attr.addValue(rs.getString(i + 1));
                }
                if ("userId".equalsIgnoreCase(metaData.getColumnName(i + 1))) {
                    attr.setKey(true);
                    user.setAccountid(rs.getString(i + 1));
                }

                user.addAttribute(attr);
            }

            results.add(user);
        }

        LOG.debug("Retrieved users: {}", results);
    } catch (SQLException e) {
        LOG.error("Search operation failed", e);
    } finally {
        if (conn != null) {
            try {
                close(conn);
            } catch (SQLException ignore) {
                // ignore exception
            }
        }
    }

    return results;
}

From source file:computer_store.GUI.java

private void fillSysTable(javax.swing.JTable table, java.sql.ResultSet rs) {
    try {/*  ww w.j ava  2 s . com*/

        //To remove previously added rows
        while (table.getRowCount() > 0) {
            ((javax.swing.table.DefaultTableModel) table.getModel()).removeRow(0);
        }
        int columns = rs.getMetaData().getColumnCount();
        int rows = 0;
        //Adding column headers
        Object[] ids = new Object[columns];
        for (int i = 1; i <= columns; i++) {
            ids[i - 1] = rs.getMetaData().getColumnName(i);
        }
        ((javax.swing.table.DefaultTableModel) table.getModel()).setColumnIdentifiers(ids);
        java.util.ArrayList<String> systems = new java.util.ArrayList();
        //Adding rows from ResultSet
        while (rs.next()) {
            systems.add(rs.getString(1));
            Object[] row = new Object[columns];
            for (int i = 1; i <= columns; i++) {
                row[i - 1] = rs.getObject(i);
            }
            ((javax.swing.table.DefaultTableModel) table.getModel()).insertRow(rs.getRow() - 1, row);
            rows++;
        }
        //Adding new column with prices
        Object[] prices = new Object[rows];
        for (int i = 0; i < prices.length; i++) {
            prices[i] = handler.getSystemPrice(systems.get(i));
        }
        ((javax.swing.table.DefaultTableModel) table.getModel()).addColumn("Price", prices);
        //Adding new column with selling prices
        Object[] sellPrices = new Object[rows];
        for (int i = 0; i < sellPrices.length; i++) {
            sellPrices[i] = handler.getSystemSellPrice(systems.get(i));
        }
        ((javax.swing.table.DefaultTableModel) table.getModel()).addColumn("Selling price", sellPrices);
        //Adding new column with current stock
        Object[] inStock = new Object[rows];
        for (int i = 0; i < prices.length; i++) {
            inStock[i] = handler.systemsInStock(systems.get(i));
        }
        ((javax.swing.table.DefaultTableModel) table.getModel()).addColumn("In stock", inStock);

        rs.close();
    } catch (Exception e) {
        System.out.print(e);
    }
}

From source file:com.termmed.statistics.Processor.java

/**
 * Prints the report./*from  w  ww  . j  av  a  2  s .co m*/
 *
 * @param bw the bw
 * @param detail the detail
 * @param listDescriptors 
 * @throws Exception the exception
 */
private void printReport(BufferedWriter bw, OutputDetailFile detail,
        List<HierarchicalConfiguration> listDescriptors) throws Exception {

    SQLStatementExecutor executor = new SQLStatementExecutor(connection);
    AdditionalList[] additionalList = null;
    if (listDescriptors != null) {
        additionalList = getAdditionalList(listDescriptors);
    }
    Integer ixSctIdInReport = detail.getSctIdIndex();
    if (ixSctIdInReport == null) {
        ixSctIdInReport = 1;
    }
    List<IReportListener> dependentReports = null;
    if (enableListeners) {
        ReportListeners reportListenersDescriptors = detail.getReportListeners();
        if (reportListenersDescriptors != null) {
            dependentReports = initListeners(reportListenersDescriptors, detail);
        }
    }
    for (StoredProcedure sProc : detail.getStoredProcedure()) {
        executor.executeStoredProcedure(sProc, ImportManager.params, null);
        ResultSet rs = executor.getResultSet();
        if (rs != null) {
            ResultSetMetaData meta = rs.getMetaData();
            String fieldValue;
            String sctId = "";
            String line;
            while (rs.next()) {
                line = "";
                for (int i = 0; i < meta.getColumnCount(); i++) {
                    if (rs.getObject(i + 1) != null) {
                        fieldValue = rs.getObject(i + 1).toString().replaceAll(",", "&#44;").trim();
                        if (ixSctIdInReport.intValue() == i) {
                            sctId = fieldValue;
                        }
                    } else {
                        fieldValue = "";
                        if (ixSctIdInReport.intValue() == i) {
                            sctId = "0";
                        }
                    }
                    line += fieldValue;
                    //                  bw.append(fieldValue);
                    if (i + 1 < meta.getColumnCount()) {
                        line += ",";
                        //                     bw.append(",");
                    } else {
                        if (additionalList != null && detail.getCreateInterestConceptList()) {
                            for (int ix = 0; i < additionalList.length; ix++) {

                                line += ",";
                                //                           bw.append(",");
                                if (additionalList[ix].getIds().contains(sctId)) {

                                    line += "1";
                                    //                              bw.append("1");
                                } else {
                                    line += "0";
                                    //                              bw.append("0");
                                }
                            }
                        }
                        bw.append(line);
                        bw.append("\r\n");
                        if (dependentReports != null) {
                            for (IReportListener dependentReport : dependentReports) {
                                dependentReport.actionPerform(line);
                            }
                        }
                    }
                }
            }
            meta = null;
            rs.close();
        }

    }
    executor = null;

    if (dependentReports != null) {
        for (IReportListener dependentReport : dependentReports) {
            dependentReport.finalizeListener();
        }
    }
}

From source file:i5.las2peer.services.videoListService.VideoListService.java

/**
 * /* w w  w.j ava  2s.co  m*/
 * getVideoList
 * 
 * 
 * @return HttpResponse
 * 
 */
@GET
@Path("/")
@Produces(MediaType.APPLICATION_JSON)
@Consumes(MediaType.TEXT_PLAIN)
@ApiResponses(value = { @ApiResponse(code = HttpURLConnection.HTTP_INTERNAL_ERROR, message = "internalError"),
        @ApiResponse(code = HttpURLConnection.HTTP_OK, message = "videoListAsJSONArray"),
        @ApiResponse(code = HttpURLConnection.HTTP_NOT_FOUND, message = "noVideosExist") })
@ApiOperation(value = "getVideoList", notes = "")
public HttpResponse getVideoList() {
    String result = "";
    String columnName = "";
    String selectquery = "";
    int columnCount = 0;
    Connection conn = null;
    PreparedStatement stmnt = null;
    ResultSet rs = null;
    ResultSetMetaData rsmd = null;
    JSONObject ro = null;
    JSONArray array = new JSONArray();
    try {
        // get connection from connection pool
        conn = dbm.getConnection();
        selectquery = "SELECT * FROM videodetails;";
        // prepare statement
        stmnt = conn.prepareStatement(selectquery);

        // retrieve result set
        rs = stmnt.executeQuery();
        rsmd = (ResultSetMetaData) rs.getMetaData();
        columnCount = rsmd.getColumnCount();

        // process result set
        while (rs.next()) {
            ro = new JSONObject();
            for (int i = 1; i <= columnCount; i++) {
                result = rs.getString(i);
                columnName = rsmd.getColumnName(i);
                // setup resulting JSON Object
                ro.put(columnName, result);

            }
            array.add(ro);
        }
        if (array.isEmpty()) {
            String er = "No results";
            HttpResponse noVideosExist = new HttpResponse(er, HttpURLConnection.HTTP_NOT_FOUND);
            return noVideosExist;
        } else {
            // return HTTP Response on success
            HttpResponse videoListAsJSONArray = new HttpResponse(array.toJSONString(),
                    HttpURLConnection.HTTP_OK);
            return videoListAsJSONArray;
        }
    } catch (Exception e) {
        String er = "Internal error: " + e.getMessage();
        HttpResponse internalError = new HttpResponse(er, HttpURLConnection.HTTP_INTERNAL_ERROR);
        return internalError;
    } finally {
        // free resources
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                Context.logError(this, e.getMessage());
                String er = "Internal error: " + e.getMessage();
                HttpResponse internalError = new HttpResponse(er, HttpURLConnection.HTTP_INTERNAL_ERROR);
                return internalError;
            }
        }
        if (stmnt != null) {
            try {
                stmnt.close();
            } catch (Exception e) {
                Context.logError(this, e.getMessage());
                String er = "Internal error: " + e.getMessage();
                HttpResponse internalError = new HttpResponse(er, HttpURLConnection.HTTP_INTERNAL_ERROR);
                return internalError;
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                Context.logError(this, e.getMessage());
                String er = "Internal error: " + e.getMessage();
                HttpResponse internalError = new HttpResponse(er, HttpURLConnection.HTTP_INTERNAL_ERROR);
                return internalError;
            }
        }
    }
}

From source file:atg.tools.dynunit.test.util.DBUtils.java

void dump(@NotNull ResultSet rs) throws SQLException {

    // the order of the rows in a cursor
    // are implementation dependent unless you use the SQL ORDER statement
    ResultSetMetaData meta = rs.getMetaData();
    int colmax = meta.getColumnCount();
    int i;/*  w  w  w  . j  av a 2 s . c o m*/
    Object o = null;

    // the result set is a cursor into the data. You can only
    // point to one row at a time
    // assume we are pointing to BEFORE the first row
    // rs.next() points to next row and returns true
    // or false if there is no next row, which breaks the loop
    for (; rs.next();) {
        for (i = 0; i < colmax; ++i) {
            o = rs.getObject(i + 1); // Is SQL the first column is indexed

            // with 1 not 0
            logger.info(o);
        }
    }
}

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

private void appendToCSVDocument(ResultSet allRows, int columnCount, String filePath)
        throws SQLException, IOException {
    //the flag 'true' means that the writer will go to the end of the file and append the new data
    BufferedWriter bfout = new BufferedWriter(new FileWriter(filePath, true));
    try {//from  w w w .  j  a  va  2 s  .c  o m
        while (allRows.next()) {
            String row = "";
            for (int j = 1; j <= columnCount; j++) {
                String dataType = allRows.getMetaData().getColumnTypeName(j);
                String value = allRows.getString(j);
                //if the value is null write null to csv file
                if (value == null) {
                    //                        avenet 20170215 - using "" is the copy csv format for Postgres
                    value = "";
                }
                //if the type is String/text etc and is not null put the value between double quotes
                else if (dataType.toLowerCase().startsWith("varchar")
                        || dataType.toLowerCase().startsWith("char")
                        || dataType.toLowerCase().startsWith("text") || dataType.equalsIgnoreCase("bpchar")
                        || dataType.equalsIgnoreCase("bit") || dataType.equalsIgnoreCase("mediumtext")
                        || dataType.equalsIgnoreCase("longtext") || dataType.equalsIgnoreCase("serial")
                        || dataType.equalsIgnoreCase("enum")) {
                    value = "\"" + value + "\"";
                }
                row = row + value + ",";
            }
            //take out the last ',' character
            row = row.substring(0, row.length() - 1);
            bfout.write(row);
            bfout.newLine();
        }
    } finally {
        bfout.close();
    }
}

From source file:com.alfaariss.oa.engine.attribute.gather.processor.jdbc.JDBCGatherer.java

/**
 * Gathers attributes from JDBC storage to the supplied attributes object.
 * @see com.alfaariss.oa.engine.core.attribute.gather.processor.IProcessor#process(java.lang.String, com.alfaariss.oa.api.attribute.IAttributes)
 *///from  ww w. jav  a2s.co m
@Override
public void process(String sUserId, IAttributes oAttributes) throws AttributeException {
    PreparedStatement oPreparedStatement = null;
    ResultSet oResultSet = null;
    Connection oConnection = null;
    try {
        oConnection = _oDataSource.getConnection();
        oPreparedStatement = oConnection.prepareStatement(_sSelectQuery);
        oPreparedStatement.setString(1, sUserId);
        oResultSet = oPreparedStatement.executeQuery();
        if (oResultSet.next()) {
            ResultSetMetaData oResultSetMetaData = oResultSet.getMetaData();
            int iCount = oResultSetMetaData.getColumnCount();
            for (int i = 1; i <= iCount; i++) {
                String sName = oResultSetMetaData.getColumnName(i);
                Object oValue = oResultSet.getObject(sName);

                String sMappedName = _htMapper.get(sName);
                if (sMappedName != null)
                    sName = sMappedName;

                if (oValue == null)
                    oValue = "";

                oAttributes.put(sName, oValue);
            }
        }
    } catch (SQLException e) {
        _logger.error("Could not gather attributes for user with id: " + sUserId, e);
        throw new AttributeException(SystemErrors.ERROR_RESOURCE_RETRIEVE);
    } catch (Exception e) {
        _logger.fatal("Could not initialize object", e);
        throw new AttributeException(SystemErrors.ERROR_INTERNAL);
    } finally {
        try {
            if (oResultSet != null)
                oResultSet.close();
        } catch (Exception e) {
            _logger.error("Could not close resultset", e);
        }

        try {
            if (oPreparedStatement != null)
                oPreparedStatement.close();
        } catch (Exception e) {
            _logger.error("Could not close statement", e);
        }

        try {
            if (oConnection != null)
                oConnection.close();
        } catch (Exception e) {
            _logger.error("Could not disconnect prepared statement", e);
        }
    }
}

From source file:com.gzj.tulip.jade.rowmapper.BeanPropertyRowMapper.java

/**
 * Extract the values for all columns in the current row.
 * <p>/*from   ww  w. j  ava 2  s .c  om*/
 * Utilizes public setters and result set metadata.
 * 
 * @see java.sql.ResultSetMetaData
 */
public Object mapRow(ResultSet rs, int rowNumber) throws SQLException {
    // spring's : Object mappedObject = BeanUtils.instantiateClass(this.mappedClass);
    // jade's : private Object instantiateClass(this.mappedClass);
    // why: ??mappedClass.newInstranceBeanUtils.instantiateClass(mappedClass)1?
    Object mappedObject = instantiateClass(this.mappedClass);
    BeanWrapper bw = new BeanWrapperImpl(mappedObject);

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

    boolean warnEnabled = logger.isWarnEnabled();
    boolean debugEnabled = logger.isDebugEnabled();
    Set<String> populatedProperties = (checkProperties ? new HashSet<String>() : null);

    for (int index = 1; index <= columnCount; index++) {
        String column = JdbcUtils.lookupColumnName(rsmd, index).toLowerCase();
        PropertyDescriptor pd = this.mappedFields.get(column);
        if (pd != null) {
            try {
                Object value = JdbcUtils.getResultSetValue(rs, index, pd.getPropertyType());
                if (debugEnabled && rowNumber == 0) {
                    logger.debug("Mapping column '" + column + "' to property '" + pd.getName() + "' of type "
                            + pd.getPropertyType());
                }
                bw.setPropertyValue(pd.getName(), value);
                if (populatedProperties != null) {
                    populatedProperties.add(pd.getName());
                }
            } catch (NotWritablePropertyException ex) {
                throw new DataRetrievalFailureException(
                        "Unable to map column " + column + " to property " + pd.getName(), ex);
            }
        } else {
            if (checkColumns) {
                throw new InvalidDataAccessApiUsageException("Unable to map column '" + column
                        + "' to any properties of bean " + this.mappedClass.getName());
            }
            if (warnEnabled && rowNumber == 0) {
                logger.warn("Unable to map column '" + column + "' to any properties of bean "
                        + this.mappedClass.getName());
            }
        }
    }

    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;
}