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.erbjuder.logger.server.rest.util.ResultSetConverter.java

private List<LogMessage> toLogMessageInternal(ResultSet rs, List<LogMessage> logMessages) {

    try {//from   w  w  w. j  a v a  2s . co  m

        // we will need the column names.
        java.sql.ResultSetMetaData rsmd = rs.getMetaData();
        //loop through the ResultSet
        while (rs.next()) {

            //figure out how many columns there are
            int numColumns = rsmd.getColumnCount();

            //each row in the ResultSet will be converted to a Object
            LogMessage obj = new LogMessage();

            // loop through all the columns 
            for (int i = 1; i < numColumns + 1; i++) {
                String column_name = rsmd.getColumnName(i);

                if (column_name.equals("ID")) {
                    obj.setId(rs.getBigDecimal(column_name).longValueExact());
                }

                if (column_name.equals("APPLICATIONNAME")) {
                    obj.setApplicationName(rs.getNString(column_name));
                }

                if (column_name.equals("EXPIREDDATE")) {
                    obj.setExpiredDate(rs.getDate(column_name));
                }

                if (column_name.equals("FLOWNAME")) {
                    obj.setFlowName(rs.getNString(column_name));
                }

                if (column_name.equals("FLOWPOINTNAME")) {
                    obj.setFlowPointName(rs.getNString(column_name));
                }

                if (column_name.equals("ISERROR")) {
                    obj.setIsError(rs.getBoolean(column_name));
                }

                if (column_name.equals("TRANSACTIONREFERENCEID")) {
                    obj.setTransactionReferenceID(rs.getNString(column_name));
                }

                if (column_name.equals("UTCLOCALTIMESTAMP")) {
                    obj.setUtcLocalTimeStamp(rs.getTimestamp(column_name));
                }

                if (column_name.equals("UTCSERVERTIMESTAMP")) {
                    obj.setUtcServerTimeStamp(rs.getTimestamp(column_name));
                }
            } //end foreach
            logMessages.add(obj);
        } //end while
    } catch (Exception e) {
        e.printStackTrace();
    }

    return logMessages;
}

From source file:com.smhdemo.common.report.generate.factory.ChartFactory.java

/**
 * ?/*from   w  ww.ja  v a2s .co m*/
 *
 * @param reportDataSet ??
 * @param sql SQL?
 * @return DefaultCategoryDataset
 * @throws BaseException
 */
@SuppressWarnings("rawtypes")
private DefaultCategoryDataset buildDataset(Chart report, Map<String, String> pageParams) throws BaseException {
    DefaultCategoryDataset dataset = new DefaultCategoryDataset();
    Connection con = null;

    DataSourceServiceable service = null;
    int colCount;
    try {
        Base dataSet = report.getBaseDS();
        String executableSQL = report.getChartSql();
        executableSQL = replaceParam(pageParams, report.getParameters(), executableSQL, true);

        if (dataSet == null) {
            con = dataSource.getConnection();
        } else {
            DataSourceFactoryable factory = (DataSourceFactoryable) initDataSourceFactory
                    .getBean(dataSet.getClass());
            service = factory.createService(dataSet);
            con = service.openConnection();
        }

        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(executableSQL);

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

        if (colCount == 2) {
            while (rs.next()) {
                try {
                    try {
                        dataset.addValue(rs.getDouble(1), "", (Comparable) rs.getObject(2));
                    } catch (Exception e) {
                        dataset.addValue(rs.getDouble(2), "", (Comparable) rs.getObject(1));
                    }
                } catch (Exception e) {
                    logger.error("SQL?", e);
                    throw new BaseException("SQL?", "SQL?");
                }
            }
        } else if (colCount == 3) {
            while (rs.next()) {
                try {
                    try {
                        dataset.addValue(rs.getDouble(3), (Comparable) rs.getObject(1),
                                (Comparable) rs.getObject(2));
                    } catch (Exception e) {
                        try {
                            dataset.addValue(rs.getDouble(2), (Comparable) rs.getObject(1),
                                    (Comparable) rs.getObject(3));
                        } catch (Exception ex) {
                            dataset.addValue(rs.getDouble(1), (Comparable) rs.getObject(2),
                                    (Comparable) rs.getObject(3));
                        }
                    }
                } catch (Exception e) {
                    logger.error("SQL?", e);
                    throw new BaseException("SQL?", "SQL?");
                }
            }
        } else {
            logger.error("SQL??12");
            throw new BaseException("?12", "?12");
        }
        st.close();
        rs.close();
    } catch (Exception e) {
        throw new BaseException(e.toString(), e.toString());
    } finally {
        if (service != null) {
            service.closeConnection();
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
            }
            con = null;
        }
    }
    return dataset;
}

From source file:com.gatf.executor.dataprovider.SQLDatabaseTestDataSource.java

public List<Map<String, String>> provide(GatfTestDataProvider provider, AcceptanceTestContext context) {

    List<Map<String, String>> result = new ArrayList<Map<String, String>>();

    Resource res = null;/*  www .java2s  . c om*/
    try {

        Assert.assertNotNull("queryString cannot be empty", provider.getQueryStr());
        Assert.assertNotNull("variableNames cannot be empty", provider.getProviderProperties());

        String queryStr = provider.getQueryStr();
        String providerProperties = provider.getProviderProperties();

        Assert.assertFalse("queryString cannot be empty", queryStr.isEmpty());
        Assert.assertTrue("only select queries allowed", queryStr.toLowerCase().indexOf("select ") == 0);

        List<String> variableNamesArr = new ArrayList<String>();
        for (String varName : providerProperties.split(",")) {
            if (!varName.trim().isEmpty()) {
                variableNamesArr.add(varName);
            }
        }
        Assert.assertTrue("need to define at-least a single variable name", !providerProperties.isEmpty()
                && providerProperties.split(",").length > 0 && variableNamesArr.size() > 0);

        StringBuilder build = new StringBuilder();
        build.append("Provider configuration [\n");
        build.append(String.format("dataSource name is %s\n", getDataSourceName()));
        build.append(String.format("queryString is %s\n", queryStr));
        build.append(String.format("variableNames is %s]", providerProperties));
        logger.info(build.toString());

        Statement statement = null;
        ResultSet resultSet = null;

        try {

            res = getResource();
            Connection conn = (Connection) res.object;

            statement = conn.createStatement();
            resultSet = statement.executeQuery(queryStr);
            if (resultSet.getMetaData().getColumnCount() != variableNamesArr.size())
                throw new AssertionError(
                        "The number of columns from the query does not match the variable Names provided");
            for (int i = 0; i < variableNamesArr.size(); i++) {
                Assert.assertTrue(
                        String.format("Invalid variable/sql type, only strings,boolean,numbers allowed for %s",
                                variableNamesArr.get(i)),
                        isValidDataType(resultSet.getMetaData().getColumnType(i + 1)));
            }
            while (resultSet.next()) {
                Map<String, String> row = new HashMap<String, String>();
                for (int i = 0; i < variableNamesArr.size(); i++) {
                    row.put(variableNamesArr.get(i), resultSet.getString(i + 1));
                }
                result.add(row);
            }
        } catch (Exception e) {
            throw new AssertionError(
                    String.format("Fetching Test Data failed while executing query %s with the error %s",
                            queryStr, ExceptionUtils.getStackTrace(e)));
        } finally {
            try {
                if (resultSet != null)
                    resultSet.close();
            } catch (SQLException e) {
            }
            try {
                if (statement != null)
                    statement.close();
            } catch (SQLException e) {
            }
        }
    } catch (Exception t) {
        t.printStackTrace();
    } finally {
        if (res != null)
            releaseToPool(res);
    }
    return result;
}

From source file:hw.java

public static DefaultTableModel buildTableModel(ResultSet rs) throws SQLException {

    ResultSetMetaData metaData = rs.getMetaData();

    Vector<String> columnNames = new Vector<String>();
    int columnCount = metaData.getColumnCount();
    for (int column = 1; column <= columnCount; column++) {
        columnNames.add(metaData.getColumnName(column));
    }//w ww .  j  a  va2  s .  c  o m

    Vector<Vector<Object>> data = new Vector<Vector<Object>>();
    while (rs.next()) {
        Vector<Object> vector = new Vector<Object>();
        for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
            vector.add(rs.getObject(columnIndex));
        }
        data.add(vector);
    }

    return new DefaultTableModel(data, columnNames);

}

From source file:com.nextep.designer.synch.services.impl.DataCaptureService.java

private IVersionable<IDataSet> fetchDataSet(Connection conn, DBVendor vendor, IBasicTable t,
        List<IBasicColumn> columns, IProgressMonitor m) throws SQLException {
    final String taskName = "Capturing " + t.getName() + " data";
    SubMonitor monitor = SubMonitor.convert(m, taskName, 100);
    monitor.subTask(taskName);//  w  w  w. j a  v  a 2 s.c  o m
    final IVersionable<IDataSet> v = VersionableFactory.createVersionable(IDataSet.class);
    final IDataSet dataSet = v.getVersionnedObject().getModel();
    final Collection<IDataLine> datalineBuffer = new ArrayList<IDataLine>(BUFFER_SIZE);
    // Configuring dataset
    dataSet.setTable(t);
    // Aligning captured data set with repository dataset name
    if (!t.getDataSets().isEmpty()) {
        // Taking first one
        final IDataSet set = t.getDataSets().iterator().next();
        // Captured data set will be named just like the repository dataset to force name synch
        dataSet.setName(set.getName());
        // Captured columns are restricted to defined data set columns only
        columns = set.getColumns();
    } else {
        dataSet.setName(t.getName());
    }
    for (IBasicColumn c : columns) {
        dataSet.addColumn(c);
    }
    // Fetching data
    Statement stmt = null;
    ResultSet rset = null;
    long counter = 0;
    try {
        stmt = conn.createStatement();
        final String dataSelect = buildDataSelect(vendor, t, columns);
        monitor.subTask(taskName + " - querying data");
        rset = stmt.executeQuery(dataSelect);
        final ResultSetMetaData md = rset.getMetaData();
        int bufferCount = 0;
        while (rset.next()) {
            // Handling cancellation
            if (monitor.isCanceled()) {
                return v;
            } else {
                if (counter++ % 100 == 0) {
                    monitor.worked(100);
                }
            }
            // Preparing dataline
            final IDataLine line = typedObjectFactory.create(IDataLine.class);

            // Iterating over result set columns
            for (int i = 1; i <= md.getColumnCount(); i++) {
                // Fetching result set column value
                Object value = null;
                try {
                    value = rset.getObject(i);
                } catch (SQLException e) {
                    LOGGER.error("Data import problem on " + t.getName() + " column " + i + " of line "
                            + counter + " failed to fetch data, NULL will be used instead [" + e.getMessage()
                            + "]", e); //$NON-NLS-1$
                }
                // Preparing column value
                final IColumnValue colValue = typedObjectFactory.create(IColumnValue.class);
                colValue.setDataLine(line);
                colValue.setColumn(columns.get(i - 1));
                colValue.setValue(value);
                line.addColumnValue(colValue);
            }
            datalineBuffer.add(line);
            if (++bufferCount >= BUFFER_SIZE) {
                dataService.addDataline(dataSet, datalineBuffer.toArray(new IDataLine[datalineBuffer.size()]));
                datalineBuffer.clear();
                bufferCount = 0;
                monitor.subTask(taskName + " - " + counter + " lines fetched"); //$NON-NLS-1$
            }
        }
        // Flushing end of buffer
        if (!datalineBuffer.isEmpty()) {
            dataService.addDataline(dataSet, datalineBuffer.toArray(new IDataLine[datalineBuffer.size()]));
        }
        LOGGER.info("Captured " + counter + " data lines from " + t.getName());
    } catch (SQLException e) {
        LOGGER.error("Unable to fetch data from table " + t.getName()
                + ": this table may need structure synchronization: " + e.getMessage(), e);
    } finally {
        if (rset != null) {
            rset.close();
        }
        if (stmt != null) {
            stmt.close();
        }
    }
    monitor.done();
    // Only returning dataset if at least one row was fetched
    return counter == 0 ? null : v;
}

From source file:edu.ku.brc.specify.toycode.mexconabio.CopyPlantsFromGBIF.java

/**
 * /*www  . j a  v  a2s  . com*/
 */
public void processNullKingdom() {
    PrintWriter pw = null;
    try {
        pw = new PrintWriter("gbif_plants_from_null.log");

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

    System.out.println("----------------------- Searching NULL ----------------------- ");

    String gbifWhereStr = "FROM raw WHERE kingdom IS NULL";

    long startTime = System.currentTimeMillis();

    String cntGBIFSQL = "SELECT COUNT(*) " + gbifWhereStr;// + " LIMIT 0,1000";
    String gbifSQL = gbifSQLBase + gbifWhereStr;

    System.out.println(cntGBIFSQL);

    long totalRecs = BasicSQLUtils.getCount(srcConn, cntGBIFSQL);
    long procRecs = 0;
    int secsThreshold = 0;

    String msg = String.format("Query: %8.2f secs", (double) (System.currentTimeMillis() - startTime) / 1000.0);
    System.out.println(msg);
    pw.println(msg);
    pw.flush();

    startTime = System.currentTimeMillis();

    Statement gStmt = null;
    PreparedStatement pStmt = null;

    try {
        pw = new PrintWriter("gbif_plants_from_null.log");

        pStmt = dstConn.prepareStatement(pSQL);

        System.out.println("Total Records: " + totalRecs);
        pw.println("Total Records: " + totalRecs);

        gStmt = srcConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        gStmt.setFetchSize(Integer.MIN_VALUE);

        ResultSet rs = gStmt.executeQuery(gbifSQL);
        ResultSetMetaData rsmd = rs.getMetaData();

        while (rs.next()) {
            String genus = rs.getString(16);
            if (genus == null)
                continue;

            String species = rs.getString(17);

            if (isPlant(colStmtGN, colStmtGNSP, genus, species)
                    || isPlant(colDstStmtGN, colDstStmtGNSP, genus, species)) {

                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    Object obj = rs.getObject(i);
                    pStmt.setObject(i, obj);
                }

                try {
                    pStmt.executeUpdate();

                } catch (Exception ex) {
                    System.err.println("For Old ID[" + rs.getObject(1) + "]");
                    ex.printStackTrace();
                    pw.print("For Old ID[" + rs.getObject(1) + "] " + ex.getMessage());
                    pw.flush();
                }

                procRecs++;
                if (procRecs % 10000 == 0) {
                    long endTime = System.currentTimeMillis();
                    long elapsedTime = endTime - startTime;

                    double avergeTime = (double) elapsedTime / (double) procRecs;

                    double hrsLeft = (((double) elapsedTime / (double) procRecs) * (double) totalRecs
                            - procRecs) / HRS;

                    int seconds = (int) (elapsedTime / 60000.0);
                    if (secsThreshold != seconds) {
                        secsThreshold = seconds;

                        msg = String.format(
                                "Elapsed %8.2f hr.mn   Ave Time: %5.2f    Percent: %6.3f  Hours Left: %8.2f ",
                                ((double) (elapsedTime)) / HRS, avergeTime,
                                100.0 * ((double) procRecs / (double) totalRecs), hrsLeft);
                        System.out.println(msg);
                        pw.println(msg);
                        pw.flush();
                    }
                }
            }
        }

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

    } finally {
        try {
            if (gStmt != null) {
                gStmt.close();
            }
            if (pStmt != null) {
                pStmt.close();
            }
            pw.close();

        } catch (Exception ex) {

        }
    }
    System.out.println("Done transferring.");
    pw.println("Done transferring.");
}

From source file:com.clican.pluto.common.support.spring.BeanPropertyRowMapper.java

/**
 * Extract the values for all columns in the current row.
 * <p>//from  w w w . ja v  a 2s.c o  m
 * Utilizes public setters and result set metadata.
 * 
 * @see java.sql.ResultSetMetaData
 */
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();
    Set<String> populatedProperties = (isCheckFullyPopulated() ? new HashSet<String>() : null);

    for (int index = 1; index <= columnCount; index++) {
        String column = JdbcUtils.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);
                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.slemarchand.sqlqueryscripting.scripting.sqlquery.SQLQueryExecutor.java

private List<List<Object>> _execQuery(String sqlQuery, int maxRows, List<String> columnLabels)
        throws SQLException {

    List<List<Object>> rows = null;

    Connection con = null;/*from   w w w  . j a  va2s . c om*/
    Statement stmt = null;
    ResultSet rs = null;

    try {
        con = DataAccess.getConnection();

        con.setAutoCommit(false); // Prevent data updates

        stmt = con.createStatement();
        stmt.setMaxRows(maxRows);
        rs = stmt.executeQuery(sqlQuery);

        ResultSetMetaData md = rs.getMetaData();
        int cc = md.getColumnCount();

        rows = new ArrayList<List<Object>>(cc);

        columnLabels.clear();

        for (int c = 1; c <= cc; c++) {
            String cl = md.getColumnLabel(c);
            columnLabels.add(cl);
        }

        while (rs.next()) {
            List<Object> row = new ArrayList<Object>(cc);
            for (int c = 1; c <= cc; c++) {
                Object value = rs.getObject(c);
                row.add(value);
            }
            rows.add(row);
        }

    } finally {
        DataAccess.cleanUp(con, stmt, rs);
    }

    return rows;
}

From source file:com.gdcn.modules.db.jdbc.processor.CamelBeanProcessor.java

/**
 * Convert a <code>ResultSet</code> row into a JavaBean.  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:// ww w  . j a  v  a  2s. 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 bean
 */
public <T> T toBean(ResultSet rs, Class<T> type) throws SQLException {

    PropertyDescriptor[] props = this.propertyDescriptors(type);

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

    return this.createBean(rs, type, props, columnToProperty);
}

From source file:WeblogicDbServlet.java

public void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, java.io.IOException {

    String sql = "select * from athlete";
    Connection conn = null;//from   w  w  w . j  av  a  2s. c o m
    Statement stmt = null;
    ResultSet rs = null;
    ResultSetMetaData rsm = null;

    response.setContentType("text/html");
    java.io.PrintWriter out = response.getWriter();
    out.println("<html><head><title>Weblogic Database Access</title></head><body>");
    out.println("<h2>Database info</h2>");
    out.println("<table border='1'><tr>");

    try {

        conn = pool.getConnection();
        stmt = conn.createStatement();

        rs = stmt.executeQuery(sql);
        rsm = rs.getMetaData();
        int colCount = rsm.getColumnCount();

        //print column names
        for (int i = 1; i <= colCount; ++i) {

            out.println("<th>" + rsm.getColumnName(i) + "</th>");
        }

        out.println("</tr>");

        while (rs.next()) {

            out.println("<tr>");

            for (int i = 1; i <= colCount; ++i)
                out.println("<td>" + rs.getString(i) + "</td>");

            out.println("</tr>");
        }

    } catch (Exception e) {

        throw new ServletException(e.getMessage());

    } finally {

        try {

            stmt.close();
            conn.close();

        } catch (SQLException sqle) {
        }

    }
    out.println("</table></body></html>");
    out.close();

}