Examples with ResultSet java.sql.ResultSet used on opensource projects

Example 36 with ResultSet

use of java.sql.ResultSet in project sharding-jdbc by dangdangdotcom.

the class ShardingStatementTest method assertExecuteQuery.

@Test
public void assertExecuteQuery() throws SQLException {
    String sql = "SELECT COUNT(*) AS `orders_count` FROM `t_order` WHERE `status` = 'init'";
    try (Connection connection = shardingDataSource.getConnection();
        Statement stmt = connection.createStatement();
        ResultSet resultSet = stmt.executeQuery(sql)) {
        assertTrue(resultSet.next());
        assertThat(resultSet.getLong(1), is(40L));
    }
}

Example 37 with ResultSet

use of java.sql.ResultSet in project jetty.project by eclipse.

the class DataSourceLoginService method loadRoleInfo.

/* ------------------------------------------------------------ */
public String[] loadRoleInfo(UserPrincipal user) {
    DBUserPrincipal dbuser = (DBUserPrincipal) user;
    try {
        try (Connection connection = getConnection();
            PreparedStatement statement2 = connection.prepareStatement(_roleSql)) {
            List<String> roles = new ArrayList<String>();
            statement2.setInt(1, dbuser.getKey());
            try (ResultSet rs2 = statement2.executeQuery()) {
                while (rs2.next()) {
                    roles.add(rs2.getString(_roleTableRoleField));
                }
                return roles.toArray(new String[roles.size()]);
            }
        }
    } catch (NamingException e) {
        LOG.warn("No datasource for " + _jndiName, e);
    } catch (SQLException e) {
        LOG.warn("Problem loading user info for " + user.getName(), e);
    }
    return null;
}

Example 38 with ResultSet

use of java.sql.ResultSet in project jetty.project by eclipse.

the class DataSourceLoginService method prepareTables.

/* ------------------------------------------------------------ */
/**
     * @throws NamingException
     * @throws SQLException
     */
private void prepareTables() throws NamingException, SQLException {
    if (_createTables) {
        boolean autocommit = true;
        Connection connection = getConnection();
        try (Statement stmt = connection.createStatement()) {
            autocommit = connection.getAutoCommit();
            connection.setAutoCommit(false);
            DatabaseMetaData metaData = connection.getMetaData();
            //check if tables exist
            String tableName = (metaData.storesLowerCaseIdentifiers() ? _userTableName.toLowerCase(Locale.ENGLISH) : (metaData.storesUpperCaseIdentifiers() ? _userTableName.toUpperCase(Locale.ENGLISH) : _userTableName));
            try (ResultSet result = metaData.getTables(null, null, tableName, null)) {
                if (!result.next()) {
                    //user table default
                    /*
                         * create table _userTableName (_userTableKey integer,
                         * _userTableUserField varchar(100) not null unique,
                         * _userTablePasswordField varchar(20) not null, primary key(_userTableKey));
                         */
                    stmt.executeUpdate("create table " + _userTableName + "(" + _userTableKey + " integer," + _userTableUserField + " varchar(100) not null unique," + _userTablePasswordField + " varchar(20) not null, primary key(" + _userTableKey + "))");
                    if (LOG.isDebugEnabled())
                        LOG.debug("Created table " + _userTableName);
                }
            }
            tableName = (metaData.storesLowerCaseIdentifiers() ? _roleTableName.toLowerCase(Locale.ENGLISH) : (metaData.storesUpperCaseIdentifiers() ? _roleTableName.toUpperCase(Locale.ENGLISH) : _roleTableName));
            try (ResultSet result = metaData.getTables(null, null, tableName, null)) {
                if (!result.next()) {
                    //role table default
                    /*
                         * create table _roleTableName (_roleTableKey integer,
                         * _roleTableRoleField varchar(100) not null unique, primary key(_roleTableKey));
                         */
                    String str = "create table " + _roleTableName + " (" + _roleTableKey + " integer, " + _roleTableRoleField + " varchar(100) not null unique, primary key(" + _roleTableKey + "))";
                    stmt.executeUpdate(str);
                    if (LOG.isDebugEnabled())
                        LOG.debug("Created table " + _roleTableName);
                }
            }
            tableName = (metaData.storesLowerCaseIdentifiers() ? _userRoleTableName.toLowerCase(Locale.ENGLISH) : (metaData.storesUpperCaseIdentifiers() ? _userRoleTableName.toUpperCase(Locale.ENGLISH) : _userRoleTableName));
            try (ResultSet result = metaData.getTables(null, null, tableName, null)) {
                if (!result.next()) {
                    //user-role table
                    /*
                         * create table _userRoleTableName (_userRoleTableUserKey integer,
                         * _userRoleTableRoleKey integer,
                         * primary key (_userRoleTableUserKey, _userRoleTableRoleKey));
                         *
                         * create index idx_user_role on _userRoleTableName (_userRoleTableUserKey);
                         */
                    stmt.executeUpdate("create table " + _userRoleTableName + " (" + _userRoleTableUserKey + " integer, " + _userRoleTableRoleKey + " integer, " + "primary key (" + _userRoleTableUserKey + ", " + _userRoleTableRoleKey + "))");
                    stmt.executeUpdate("create index indx_user_role on " + _userRoleTableName + "(" + _userRoleTableUserKey + ")");
                    if (LOG.isDebugEnabled())
                        LOG.debug("Created table " + _userRoleTableName + " and index");
                }
            }
            connection.commit();
        } finally {
            try {
                connection.setAutoCommit(autocommit);
            } catch (SQLException e) {
                if (LOG.isDebugEnabled())
                    LOG.debug("Prepare tables", e);
            } finally {
                try {
                    connection.close();
                } catch (SQLException e) {
                    if (LOG.isDebugEnabled())
                        LOG.debug("Prepare tables", e);
                }
            }
        }
    } else if (LOG.isDebugEnabled()) {
        LOG.debug("createTables false");
    }
}

Example 39 with ResultSet

use of java.sql.ResultSet in project tomcat by apache.

the class JDBCRealm method getPassword.

/**
     * Get the password for the specified user.
     * @param username The user name
     * @return the password associated with the given principal's user name.
     */
@Override
protected synchronized String getPassword(String username) {
    // Look up the user's credentials
    String dbCredentials = null;
    // Number of tries is the number of attempts to connect to the database
    // during this login attempt (if we need to open the database)
    // This needs rewritten with better pooling support, the existing code
    // needs signature changes since the Prepared statements needs cached
    // with the connections.
    // The code below will try twice if there is a SQLException so the
    // connection may try to be opened again. On normal conditions (including
    // invalid login - the above is only used once.
    int numberOfTries = 2;
    while (numberOfTries > 0) {
        try {
            // Ensure that we have an open database connection
            open();
            PreparedStatement stmt = credentials(dbConnection, username);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    dbCredentials = rs.getString(1);
                }
                dbConnection.commit();
                if (dbCredentials != null) {
                    dbCredentials = dbCredentials.trim();
                }
                return dbCredentials;
            }
        } catch (SQLException e) {
            // Log the problem for posterity
            containerLog.error(sm.getString("jdbcRealm.exception"), e);
        }
        // Close the connection so that it gets reopened next time
        if (dbConnection != null) {
            close(dbConnection);
        }
        numberOfTries--;
    }
    return null;
}

Example 40 with ResultSet

use of java.sql.ResultSet in project tomcat by apache.

the class PoolableConnection method validate.

/**
     * Validates the connection, using the following algorithm:
     * <ol>
     *   <li>If {@code fastFailValidation} (constructor argument) is {@code true} and
     *       this connection has previously thrown a fatal disconnection exception,
     *       a {@code SQLException} is thrown. </li>
     *   <li>If {@code sql} is null, the driver's
     *       #{@link Connection#isValid(int) isValid(timeout)} is called.
     *       If it returns {@code false}, {@code SQLException} is thrown;
     *       otherwise, this method returns successfully.</li>
     *   <li>If {@code sql} is not null, it is executed as a query and if the resulting
     *       {@code ResultSet} contains at least one row, this method returns
     *       successfully.  If not, {@code SQLException} is thrown.</li>
     * </ol>
     * @param sql validation query
     * @param timeout validation timeout
     * @throws SQLException if validation fails or an SQLException occurs during validation
     */
public void validate(final String sql, int timeout) throws SQLException {
    if (_fastFailValidation && _fatalSqlExceptionThrown) {
        throw new SQLException(Utils.getMessage("poolableConnection.validate.fastFail"));
    }
    if (sql == null || sql.length() == 0) {
        if (timeout < 0) {
            timeout = 0;
        }
        if (!isValid(timeout)) {
            throw new SQLException("isValid() returned false");
        }
        return;
    }
    if (!sql.equals(lastValidationSql)) {
        lastValidationSql = sql;
        // Has to be the innermost delegate else the prepared statement will
        // be closed when the pooled connection is passivated.
        validationPreparedStatement = getInnermostDelegateInternal().prepareStatement(sql);
    }
    if (timeout > 0) {
        validationPreparedStatement.setQueryTimeout(timeout);
    }
    try (ResultSet rs = validationPreparedStatement.executeQuery()) {
        if (!rs.next()) {
            throw new SQLException("validationQuery didn't return a row");
        }
    } catch (final SQLException sqle) {
        throw sqle;
    }
}