Example usage for java.sql PreparedStatement getResultSet

List of usage examples for java.sql PreparedStatement getResultSet

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. getResultSet

Introduction

In this page you can find the example usage for java.sql PreparedStatement getResultSet.

Prototype

ResultSet getResultSet() throws SQLException;

Source Link

Document

Retrieves the current result as a ResultSet object.

Usage

From source file:org.apache.jmeter.protocol.jdbc.sampler.JDBCSampler.java

private String resultSetsToString(PreparedStatement pstmt, boolean result, int[] out)
        throws SQLException, UnsupportedEncodingException {
    StrBuilder sb = new StrBuilder();
    int updateCount = 0;
    if (!result) {
        updateCount = pstmt.getUpdateCount();
    }/*from  w w  w  .j  a v  a2  s .  c o  m*/
    do {
        if (result) {
            ResultSet rs = null;
            try {
                rs = pstmt.getResultSet();
                sb.append(getStringFromResultSet(rs)).append("\n"); // $NON-NLS-1$
            } finally {
                close(rs);
            }
        } else {
            sb.append(updateCount).append(" updates.\n");
        }
        result = pstmt.getMoreResults();
        if (!result) {
            updateCount = pstmt.getUpdateCount();
        }
    } while (result || (updateCount != -1));
    if (out != null && pstmt instanceof CallableStatement) {
        CallableStatement cs = (CallableStatement) pstmt;
        sb.append("Output variables by position:\n");
        for (int i = 0; i < out.length; i++) {
            if (out[i] != java.sql.Types.NULL) {
                sb.append("[");
                sb.append(i + 1);
                sb.append("] ");
                sb.append(cs.getObject(i + 1));
                sb.append("\n");
            }
        }
    }
    return sb.toString();
}

From source file:org.zaproxy.zap.extension.sse.db.TableEventStream.java

/** Create tables if not already available */
@Override/*  w w w.  j a va 2 s .co m*/
protected void reconnect(Connection conn) throws DatabaseException {
    try {
        if (!DbUtils.hasTable(conn, "EVENT_STREAM")) {
            // need to create the tables
            PreparedStatement stmt = conn.prepareStatement("CREATE CACHED TABLE event_stream ("
                    + "stream_id BIGINT PRIMARY KEY," + "host VARCHAR(255) NOT NULL," + "port INTEGER NOT NULL,"
                    + "url VARCHAR(255) NOT NULL," + "start_timestamp TIMESTAMP NOT NULL,"
                    + "end_timestamp TIMESTAMP NULL," + "history_id INTEGER NULL,"
                    + "FOREIGN KEY (history_id) REFERENCES HISTORY(HISTORYID) ON DELETE SET NULL ON UPDATE SET NULL"
                    + ")");
            DbUtils.executeAndClose(stmt);

            stmt = conn
                    .prepareStatement("CREATE CACHED TABLE event_stream_event (" + "event_id BIGINT NOT NULL,"
                            + "stream_id BIGINT NOT NULL," + "timestamp TIMESTAMP NOT NULL,"
                            + "last_event_id VARCHAR(255) NOT NULL," + "data CLOB(16M) NOT NULL,"
                            + "event_type VARCHAR(255) NOT NULL," + "reconnection_time BIGINT NULL,"
                            + "raw_event CLOB(16M) NOT NULL," + "PRIMARY KEY (event_id, stream_id),"
                            + "FOREIGN KEY (stream_id) REFERENCES event_stream(stream_id)" + ")");
            DbUtils.executeAndClose(stmt);

            streamIds = new HashSet<>();
        } else {
            streamIds = null;
        }

        streamCache = new LRUMap(20);

        // STREAMS
        psSelectMaxStreamId = conn
                .prepareStatement("SELECT MAX(s.stream_id) as stream_id " + "FROM event_stream AS s");

        psSelectStreams = conn
                .prepareStatement("SELECT s.* " + "FROM event_stream AS s " + "ORDER BY s.stream_id");

        // id goes last to be consistent with update query
        psInsertStream = conn.prepareStatement("INSERT INTO "
                + "event_stream (host, port, url, start_timestamp, end_timestamp, history_id, stream_id) "
                + "VALUES (?,?,?,?,?,?,?)");

        psUpdateStream = conn.prepareStatement("UPDATE event_stream SET "
                + "host = ?, port = ?, url = ?, start_timestamp = ?, end_timestamp = ?, history_id = ? "
                + "WHERE stream_id = ?");

        psUpdateHistoryFk = conn
                .prepareStatement("UPDATE event_stream SET " + "history_id = ? " + "WHERE stream_id = ?");

        psDeleteStream = conn.prepareStatement("DELETE FROM event_stream " + "WHERE stream_id = ?");

        // EVENTS
        psSelectEvent = conn.prepareStatement(
                "SELECT e.* " + "FROM event_stream_event AS e " + "WHERE e.event_id = ? AND e.stream_id = ?");

        psInsertEvent = conn.prepareStatement("INSERT INTO "
                + "event_stream_event (event_id, stream_id, timestamp, last_event_id, data, event_type, reconnection_time, raw_event) "
                + "VALUES (?,?,?,?,?,?,?,?)");

        psDeleteEventsByStreamId = conn
                .prepareStatement("DELETE FROM event_stream_event " + "WHERE stream_id = ?");

        if (streamIds == null) {
            streamIds = new HashSet<>();
            PreparedStatement psSelectStreamIds = conn.prepareStatement(
                    "SELECT s.stream_id " + "FROM event_stream AS s " + "ORDER BY s.stream_id");
            try {
                psSelectStreamIds.execute();

                ResultSet rs = psSelectStreamIds.getResultSet();
                while (rs.next()) {
                    streamIds.add(rs.getInt(1));
                }
            } finally {
                try {
                    psSelectStreamIds.close();
                } catch (SQLException e) {
                    if (logger.isDebugEnabled()) {
                        logger.debug(e.getMessage(), e);
                    }
                }
            }
        }
    } catch (SQLException e) {
        throw new DatabaseException(e);
    }
}

From source file:org.hawkular.inventory.impl.tinkerpop.sql.impl.SqlGraph.java

@Override
public synchronized SqlVertex getVertex(Object id) {
    Long realId = getId(id);// w  w w .  ja  v a  2  s . c o m

    if (realId == null) {
        return null;
    }

    WeakReference<SqlVertex> ref = vertexCache.get(realId);

    SqlVertex v = ref == null ? null : ref.get();
    if (v != null) {
        return v;
    }

    initConnection();
    try {
        PreparedStatement stmt = statements.getGetVertex(realId);
        if (!stmt.execute()) {
            return null;
        }

        try (ResultSet rs = stmt.getResultSet()) {
            return cache(statements.fromVertexResultSet(rs));
        }
    } catch (SQLException e) {
        throw new SqlGraphException(e);
    }
}

From source file:org.openmrs.web.filter.update.UpdateFilter.java

/**
 * Checks the given user to see if they have been given the
 * {@link OpenmrsConstants#SUPERUSER_ROLE} role. This method does not look at child roles.
 *
 * @param connection the java sql connection to use
 * @param userId the user id to look at/*from  w w  w  .  jav a2 s.c o  m*/
 * @return true if the given user is a super user
 * @should return true if given user has superuser role
 * @should return false if given user does not have the super user role
 */
protected boolean isSuperUser(Connection connection, Integer userId) throws Exception {
    // the 'Administrator' part of this string is necessary because if the database was upgraded
    // by OpenMRS 1.6 alpha then System Developer was renamed to that. This has to be here so we
    // can roll back that change in 1.6 beta+
    String select = "select 1 from user_role where user_id = ? and (role = ? or role = 'Administrator')";
    PreparedStatement statement = connection.prepareStatement(select);
    statement.setInt(1, userId);
    statement.setString(2, RoleConstants.SUPERUSER);
    if (statement.execute()) {
        ResultSet results = statement.getResultSet();
        if (results.next()) {
            return results.getInt(1) == 1;
        }
    }

    return false;
}

From source file:org.openbravo.test.accounting.RecordID2Test.java

private Date getBalancedDate(String strRecordID2, String strAccountId) {
    String sql = "select max(datebalanced) " //
            + "from fact_Acct where record_ID2 = ?" + " and account_id = ?";

    PreparedStatement sqlQuery = null;
    ResultSet rs = null;/*w  w w .  java  2  s.c o  m*/
    try {
        sqlQuery = new DalConnectionProvider(false).getPreparedStatement(sql);
        sqlQuery.setString(1, strRecordID2);
        sqlQuery.setString(2, strAccountId);
        sqlQuery.execute();
        sqlQuery.setMaxRows(1);
        rs = sqlQuery.getResultSet();
        while (rs.next()) {
            return rs.getDate(1);
        }
    } catch (Exception e) {
        assertFalse(true);
        log.error("Error when executing query", e);
    } finally {
        try {
            if (sqlQuery != null) {
                sqlQuery.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (Exception e) {
            log.error("Error when closing statement", e);
        }
    }
    return null;
}

From source file:org.openbravo.test.accounting.RecordID2Test.java

private String getInTransitAccount(FIN_FinancialAccount account, String strCAcctSchemaId) {
    String sql = "select  account_id " //
            + "from fin_financial_account_acct, c_validcombination where fin_in_intransit_acct = c_validcombination_id and fin_financial_account_acct.fin_financial_account_id = ? and fin_financial_account_acct.c_acctschema_id = ?";

    PreparedStatement sqlQuery = null;
    ResultSet rs = null;/*from  w ww.j  a v  a 2  s . com*/
    try {
        sqlQuery = new DalConnectionProvider(false).getPreparedStatement(sql);
        sqlQuery.setString(1, account.getId());
        sqlQuery.setString(2, strCAcctSchemaId);
        sqlQuery.execute();
        sqlQuery.setMaxRows(1);
        rs = sqlQuery.getResultSet();
        while (rs.next()) {
            return rs.getString(1);
        }
    } catch (Exception e) {
        assertFalse(true);
        log.error("Error when executing query", e);
    } finally {
        try {
            if (sqlQuery != null) {
                sqlQuery.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (Exception e) {
            log.error("Error when closing statement", e);
        }
    }
    return null;
}

From source file:org.openbravo.test.accounting.RecordID2Test.java

private String getDepositAccount(FIN_FinancialAccount account, String strCAcctSchemaId) {
    String sql = "select  account_id " //
            + "from fin_financial_account_acct, c_validcombination where fin_deposit_acct = c_validcombination_id and fin_financial_account_acct.fin_financial_account_id = ? and fin_financial_account_acct.c_acctschema_id = ?";

    PreparedStatement sqlQuery = null;
    ResultSet rs = null;/*from  w w  w .  ja  v  a2s  .co  m*/
    try {
        sqlQuery = new DalConnectionProvider(false).getPreparedStatement(sql);
        sqlQuery.setString(1, account.getId());
        sqlQuery.setString(2, strCAcctSchemaId);
        sqlQuery.execute();
        sqlQuery.setMaxRows(1);
        rs = sqlQuery.getResultSet();
        while (rs.next()) {
            return rs.getString(1);
        }
    } catch (Exception e) {
        assertFalse(true);
        log.error("Error when executing query", e);
    } finally {
        try {
            if (sqlQuery != null) {
                sqlQuery.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (Exception e) {
            log.error("Error when closing statement", e);
        }
    }
    return null;
}

From source file:org.openbravo.test.accounting.RecordID2Test.java

private String getReceivablesAccount(BusinessPartner businessPartner, String strCAcctSchemaId) {
    String sql = "select  account_id " //
            + "from c_bp_customer_acct, c_validcombination where c_receivable_acct = c_validcombination_id and c_bp_customer_acct.c_bpartner_id = ? and c_bp_customer_acct.c_acctschema_id = ?";

    PreparedStatement sqlQuery = null;
    ResultSet rs = null;//from w w  w  .  j  a v a 2 s .  co m
    try {
        sqlQuery = new DalConnectionProvider(false).getPreparedStatement(sql);
        sqlQuery.setString(1, businessPartner.getId());
        sqlQuery.setString(2, strCAcctSchemaId);
        sqlQuery.execute();
        sqlQuery.setMaxRows(1);
        rs = sqlQuery.getResultSet();
        while (rs.next()) {
            return rs.getString(1);
        }
    } catch (Exception e) {
        assertFalse(true);
        log.error("Error when executing query", e);
    } finally {
        try {
            if (sqlQuery != null) {
                sqlQuery.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (Exception e) {
            log.error("Error when closing statement", e);
        }
    }
    return null;
}

From source file:org.zaproxy.zap.extension.sse.db.TableEventStream.java

public List<ServerSentEventStream> getStreams(ServerSentEventStream criteria) throws DatabaseException {
    try {//from www.j a v  a2 s.c  om
        String query = "SELECT s.* " + "FROM event_stream AS s " + "<where> "
                + "ORDER BY s.start_timestamp, s.stream_id";

        PreparedStatement stmt;
        try {
            stmt = buildEventCriteriaStatement(query, criteria);
        } catch (DatabaseException e) {
            if (getConnection().isClosed()) {
                return new ArrayList<>(0);
            }

            throw e;
        }

        stmt.execute();

        return buildStreams(stmt.getResultSet());
    } catch (SQLException e) {
        throw new DatabaseException(e);
    }
}

From source file:org.s23m.cell.repository.RelationalDatabaseRepository.java

protected List<SearchResultType> search(final String queryString) {
    final List<SearchResultType> searchResults = new ArrayList<SearchResultType>();
    Connection connection = null;
    ResultSet rs = null;// w w w  .j ava 2  s .  c  om
    try {
        connection = DriverManager.getConnection("jdbc:apache:commons:dbcp:" + REPOSITORY_CONNECTION_POOL_ID); //$NON-NLS-1$
        connection.setAutoCommit(false);
        final PreparedStatement searchStatement = connection.prepareStatement(getSearchQueryString()); //$NON-NLS-1$
        searchStatement.setString(1, "%" + queryString.trim() + "%");
        searchStatement.execute();
        rs = searchStatement.getResultSet();
        final ObjectFactory factory = ObjectFactoryHolder.getInstance();

        while (rs.next()) {
            final SearchResultType searchResult = factory.createSearchResultType();
            final InstanceIdentityType instanceId = factory.createInstanceIdentityType();
            instanceId.setName(rs.getString("name"));
            instanceId.setPluralName(rs.getString("pluralName"));
            instanceId.setUuid(rs.getString("uuid"));
            searchResult.setInstanceIdentity(instanceId);
            final InstanceIdentityType containerId = factory.createInstanceIdentityType();
            containerId.setName(rs.getString("containerName"));
            containerId.setPluralName(rs.getString("containerPluralName"));
            containerId.setUuid(rs.getString("containerId"));
            searchResult.setContainerIdentity(containerId);
            final InstanceIdentityType metaInstanceId = factory.createInstanceIdentityType();
            metaInstanceId.setUuid(rs.getString("metaElementId"));
            metaInstanceId.setName(rs.getString("metaElementName"));
            searchResult.setMetaInstanceIdentity(metaInstanceId);
            searchResults.add(searchResult);
        }
    } catch (final SQLException ex) {
        throw new IllegalStateException("Error in sql execution: " + ex.getMessage(), ex); //$NON-NLS-1$
    } finally {
        returnConnectionToPool(connection);
    }
    return searchResults;
}