Example usage for java.sql ResultSetMetaData getColumnCount

List of usage examples for java.sql ResultSetMetaData getColumnCount

Introduction

In this page you can find the example usage for java.sql ResultSetMetaData getColumnCount.

Prototype

int getColumnCount() throws SQLException;

Source Link

Document

Returns the number of columns in this ResultSet object.

Usage

From source file:com.waveerp.systemDBDirect.java

public ArrayList loadTableEntries() {

    // Added by Jammi Dee 05/03/2012
    registrySystem rss = new registrySystem();

    url = rss.readRegistry("NA", "NA", "NA", "DBURL");
    dbName = rss.readRegistry("NA", "NA", "NA", "DBDATABASE");
    driver = rss.readRegistry("NA", "NA", "NA", "DBDRIVER");
    user = rss.readRegistry("NA", "NA", "NA", "DBUSER");
    password = rss.readRegistry("NA", "NA", "NA", "DBPASSWORD");

    // Added by Jammi Dee 05/03/2012
    // Call the encryption management system
    desEncryption de = new desEncryption();
    de.Encrypter("", "");

    ArrayList trList = new ArrayList();

    try {/* w w  w  . j av a2 s .  c om*/
        Class.forName(getDriver());
        con = DriverManager.getConnection(url + dbName, user, password);
        ps = con.createStatement();

        rs = ps.executeQuery(querystring);

        /////////////////////////////////////////////
        // Get the number of columns here. I need
        // this to add dynaminism to my table loader
        /////////////////////////////////////////////
        ResultSetMetaData rsmd = rs.getMetaData();
        setColCount(rsmd.getColumnCount());

        ///////////////////////////////////////////
        // Load the column types to an array
        // Never access it directly, java simply
        // returns NULL, whew I don't know why
        ///////////////////////////////////////////
        String[] colTypes = new String[colCount];
        for (int j = 0; j <= getColCount() - 1; j++) {
            colTypes[j] = rsmd.getColumnTypeName(j + 1);
        }

        while (rs.next()) {

            //System.out.println(rs.getString(1));
            String[] item = new String[colCount];

            for (int j = 0; j < getColCount(); j++) {

                if (colTypes[j] == "VARCHAR") {
                    item[j] = rs.getString(j + 1);
                }
                if (colTypes[j] == "VARCHAR2") {
                    item[j] = rs.getString(j + 1);
                }
                if (colTypes[j] == "NUMBER") {
                    item[j] = Double.toString(rs.getDouble(j + 1));
                }
                if (colTypes[j] == "DATE") {
                    item[j] = rs.getDate(j + 1).toString();
                }
                if (colTypes[j] == "DATETIME") {
                    item[j] = colTypes[j]; // lrs.getTimestamp(j+1).toString();
                }
                if (colTypes[j] == "TIMESTAMP") {
                    item[j] = colTypes[j];
                }

            }

            trList.add(item);

        }

        ps.close();
        con.close();

    } catch (Exception e) {

        //System.out.println(e.getMessage());
        e.printStackTrace();

    }

    return trList;
}

From source file:com.waveerp.jsonLibrary02.java

public String loadDataEntries() {

    // Added by Jammi Dee 05/03/2012
    registrySystem rss = new registrySystem();
    url = rss.readRegistry("NA", "NA", "NA", "DBURL");
    dbName = rss.readRegistry("NA", "NA", "NA", "DBDATABASE");
    driver = rss.readRegistry("NA", "NA", "NA", "DBDRIVER");
    user = rss.readRegistry("NA", "NA", "NA", "DBUSER");
    password = rss.readRegistry("NA", "NA", "NA", "DBPASSWORD");

    // Added by Jammi Dee 05/03/2012
    // Call the encryption management system
    desEncryption de = new desEncryption();
    de.Encrypter("", "");

    try {/*from   www  .ja va 2  s.c  o m*/
        Class.forName(getDriver());
        con = DriverManager.getConnection(url + dbName, user, password);
        ps = con.createStatement();

        rs = ps.executeQuery(querystring);

        /////////////////////////////////////////////
        // Get the number of columns here. I need
        // this to add dynaminism to my table loader
        /////////////////////////////////////////////
        ResultSetMetaData rsmd = rs.getMetaData();
        setColCount(rsmd.getColumnCount());

        ///////////////////////////////////////////
        // Load the column types to an array
        // Never access it directly, java simply
        // returns NULL, whew I don't know why
        ///////////////////////////////////////////
        String[] colTypes = new String[colCount];
        for (int j = 0; j <= getColCount() - 1; j++) {
            colTypes[j] = rsmd.getColumnTypeName(j + 1);
        }

        /**
         * Initialize the working arrays here for the process
         * Added by Jammi Dee 06/06/2012
        */
        while (rs.next()) {
            String id = rs.getString(1);
        }
        rs.last();
        int rowCount = rs.getRow();
        nodeid = new String[rowCount];
        nodedesc = new String[rowCount];
        nodeparent = new String[rowCount];

        int ipoint = 0;
        rs.beforeFirst();

        while (rs.next()) {

            nodeid[ipoint] = rs.getString(1);
            nodedesc[ipoint] = rs.getString(2);
            nodeparent[ipoint] = rs.getString(3);

            // Increment the pointer
            ipoint = ipoint + 1;

        }

        ps.close();
        con.close();

    } catch (Exception e) {
        System.out.println(e.getMessage());
        e.printStackTrace();
        return "FAILED";

    }

    return "SUCCESS";
}

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)
 *//* w  w w  .  j a  v  a2  s .  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:jp.primecloud.auto.tool.management.db.SQLExecuter.java

public List<List<Object>> showColumn(String sql) throws SQLException, Exception {
    Connection con = null;/*  w w w  .ja  v a2 s . c om*/
    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;
}

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  www  . j av  a 2 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.novartis.opensource.yada.format.ResultSetResultJSONConverter.java

/**
 * Converts data from a {@link java.sql.ResultSet} into a {@link JSONArray} containing
 * one {@link JSONObject} per row//www . j  a  v  a 2  s. c  om
 * @param rs the result set containing the data to convert to JSON
 * @return a json array containing the data
 * @throws SQLException when iteration or access to {@code rs} fails
 */
protected JSONArray getJSONRows(ResultSet rs) throws SQLException {
    JSONArray rows = new JSONArray();
    ResultSetMetaData rsmd = rs.getMetaData();
    if (rsmd == null)
        rsmd = new RowSetMetaDataImpl();
    List<String> convertedResult = new ArrayList<>();
    while (rs.next()) {
        JSONObject row = new JSONObject();
        String colValue;
        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
            String origColName = rsmd.getColumnName(i);
            if (!origColName.toLowerCase().equals(JDBCAdaptor.ROWNUM_ALIAS)) {
                boolean harmonize = isHarmonized();
                boolean prune = harmonize
                        ? ((JSONObject) this.harmonyMap).has(Harmonizer.PRUNE)
                                && ((JSONObject) this.harmonyMap).getBoolean(Harmonizer.PRUNE)
                        : false;
                String col = origColName;
                if (harmonize) {
                    if (((JSONObject) this.harmonyMap).has(origColName)) {
                        col = ((JSONObject) this.harmonyMap).getString(origColName);
                    } else if (prune) {
                        col = "";
                    }
                }

                //TODO handle empty result set more intelligently
                // OLD WAY adds headers to empty object when rs is empty
                if (!"".equals(col)) {
                    if (null == rs.getString(origColName) || NULL.equals(rs.getString(origColName))) {
                        colValue = NULL_REPLACEMENT;
                    } else {
                        colValue = rs.getString(origColName);
                    }
                    row.put(col, colValue);
                }
            }

        }
        rows.put(row);
        convertedResult.add(row.toString());
    }
    if (rows.length() > 0) {
        for (String key : JSONObject.getNames(rows.getJSONObject(0))) {
            getYADAQueryResult().addConvertedHeader(key);
        }
        getYADAQueryResult().getConvertedResults().add(convertedResult);
    }
    return rows;
}

From source file:eu.stratosphere.api.io.jdbc.JDBCInputFormat.java

/**
 * Stores the next resultSet row in a Record
 * //  www  . jav  a  2s. co m
 * @param record
 *        target Record
 * @return boolean value indicating that the operation was successful
 */
@Override
public boolean nextRecord(Record record) {
    try {
        resultSet.next();
        ResultSetMetaData rsmd = resultSet.getMetaData();
        int column_count = rsmd.getColumnCount();
        record.setNumFields(column_count);

        for (int pos = 0; pos < column_count; pos++) {
            int type = rsmd.getColumnType(pos + 1);
            retrieveTypeAndFillRecord(pos, type, record);
        }
        return true;
    } catch (SQLException e) {
        throw new IllegalArgumentException("Couldn't read data:\t" + e.getMessage());
    } catch (NotTransformableSQLFieldException e) {
        throw new IllegalArgumentException(
                "Couldn't read data because of unknown column sql-type:\t" + e.getMessage());
    } catch (NullPointerException e) {
        throw new IllegalArgumentException("Couldn't access resultSet:\t" + e.getMessage());
    }
}

From source file:org.syncope.core.util.ImportExport.java

private void doExportTable(final TransformerHandler handler, final Connection conn, final String tableName)
        throws SQLException, SAXException {

    AttributesImpl atts = new AttributesImpl();

    PreparedStatement stmt = null;
    ResultSet rs = null;/*from w  ww  . j a v  a2s  .c  o  m*/
    try {
        stmt = conn.prepareStatement("SELECT * FROM " + tableName + " a");
        rs = stmt.executeQuery();
        for (int rowNo = 0; rs.next(); rowNo++) {
            atts.clear();

            ResultSetMetaData metaData = rs.getMetaData();
            for (int i = 0; i < metaData.getColumnCount(); i++) {
                String columnName = metaData.getColumnName(i + 1);
                String value = rs.getString(columnName);
                if (value != null) {
                    atts.addAttribute("", "", columnName, "CDATA", value);
                }
            }

            handler.startElement("", "", tableName, atts);
            handler.endElement("", "", tableName);
        }
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                LOG.error("While closing result set", e);
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                LOG.error("While closing result set", e);
            }
        }
    }
}

From source file:com.cws.esolutions.security.dao.reference.impl.SecurityReferenceDAOImpl.java

/**
 * @see com.cws.esolutions.security.dao.reference.interfaces.ISecurityReferenceDAO#obtainSecurityQuestionList()
 *//*  w  w  w  .  j ava 2 s.  c  o  m*/
public synchronized List<String> obtainSecurityQuestionList() throws SQLException {
    final String methodName = ISecurityReferenceDAO.CNAME + "#obtainSecurityQuestionList() throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<String> questionList = null;

    try {
        sqlConn = dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);
        stmt = sqlConn.prepareCall("{CALL retrieve_user_questions()}");

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (stmt.execute()) {
            resultSet = stmt.getResultSet();
            resultSet.last();
            int iRowCount = resultSet.getRow();

            if (iRowCount == 0) {
                throw new SQLException("No security questions are currently configured.");
            }

            resultSet.first();
            ResultSetMetaData resultData = resultSet.getMetaData();

            int iColumns = resultData.getColumnCount();

            questionList = new ArrayList<String>();

            for (int x = 1; x < iColumns + 1; x++) {
                if (DEBUG) {
                    DEBUGGER.debug("resultSet.getObject: {}", resultSet.getObject(resultData.getColumnName(x)));
                }

                // check if column is null
                resultSet.getObject(resultData.getColumnName(x));

                // if the column was null, insert n/a, otherwise, insert the column's contents
                questionList.add((String) (resultSet.wasNull() ? "N/A"
                        : resultSet.getObject(resultData.getColumnName(x))));
            }
        }
    } catch (SQLException sqx) {
        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }

        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    return questionList;
}

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

public List<DataSourceColumnWeb> getColumns(Connection connection, String tableName) {

    List<DataSourceColumnWeb> columns = new ArrayList<>();

    try {//  www . j a  va  2  s .  c om
        ResultSetMetaData rsmd = getDatabaseMetaData(connection, tableName);

        int columnCount = rsmd.getColumnCount();

        IntStream.range(1, columnCount + 1).forEach(i -> {
            try {
                columns.add(prepareDataSourceColumns(rsmd, i));
            } catch (SQLException e) {
                throw new PlatformRuntimeException(e);
            }
        });

    } catch (SQLException e) {
        throw new PlatformRuntimeException(e);
    }

    return columns;
}