Example usage for java.sql PreparedStatement executeQuery

List of usage examples for java.sql PreparedStatement executeQuery

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

Introduction

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

Prototype

ResultSet executeQuery() throws SQLException;

Source Link

Document

Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.

Usage

From source file:com.silverpeas.gallery.dao.MediaDAO.java

/**
 * Adding all data of sounds./*w w  w. j  a  v a 2  s . co m*/
 * @param con
 * @param media
 * @param sounds
 * @throws SQLException
 */
private static void decorateSounds(final Connection con, List<Media> media, Map<String, Sound> sounds)
        throws SQLException {
    if (!sounds.isEmpty()) {
        Collection<Collection<String>> idGroups = CollectionUtil.split(new ArrayList<String>(sounds.keySet()));
        StringBuilder queryBase = new StringBuilder(SELECT_INTERNAL_MEDIA_PREFIX)
                .append("S.bitrate, S.duration from SC_Gallery_Internal I join SC_Gallery_Sound S on I.mediaId "
                        + "= S.mediaId where I.mediaId in ");
        for (Collection<String> mediaIds : idGroups) {
            PreparedStatement prepStmt = null;
            ResultSet rs = null;
            try {
                prepStmt = con.prepareStatement(DBUtil.appendListOfParameters(queryBase, mediaIds).toString());
                DBUtil.setParameters(prepStmt, mediaIds);
                rs = prepStmt.executeQuery();
                while (rs.next()) {
                    String mediaId = rs.getString(1);
                    mediaIds.remove(mediaId);
                    Sound currentSound = sounds.get(mediaId);
                    decorateInternalMedia(rs, currentSound);
                    currentSound.setBitrate(rs.getLong(8));
                    currentSound.setDuration(rs.getLong(9));
                }
            } finally {
                DBUtil.close(rs, prepStmt);
            }
            // Not found
            for (String mediaIdNotFound : mediaIds) {
                Sound currentSound = sounds.remove(mediaIdNotFound);
                media.remove(currentSound);
                SilverTrace.warn(GalleryComponentSettings.COMPONENT_NAME, "MediaDAO.decorateSounds()",
                        "root.MSG_GEN_PARAM_VALUE",
                        "sound not found (removed from result): " + mediaIdNotFound);
            }
        }
    }
}

From source file:com.silverpeas.gallery.dao.MediaDAO.java

/**
 * Adding all data of streamings./*from  w w w .ja  v a 2  s .c  o m*/
 * @param con
 * @param media
 * @param streamings
 * @throws SQLException
 */
private static void decorateStreamings(final Connection con, List<Media> media,
        Map<String, Streaming> streamings) throws SQLException {
    if (!streamings.isEmpty()) {
        Collection<Collection<String>> idGroups = CollectionUtil
                .split(new ArrayList<String>(streamings.keySet()));
        StringBuilder queryBase = new StringBuilder(
                "select S.mediaId, S.homepageUrl, S.provider from SC_Gallery_Streaming S where S"
                        + ".mediaId in ");
        for (Collection<String> mediaIds : idGroups) {
            PreparedStatement prepStmt = null;
            ResultSet rs = null;
            try {
                prepStmt = con.prepareStatement(DBUtil.appendListOfParameters(queryBase, mediaIds).toString());
                DBUtil.setParameters(prepStmt, mediaIds);
                rs = prepStmt.executeQuery();
                while (rs.next()) {
                    String mediaId = rs.getString(1);
                    mediaIds.remove(mediaId);
                    Streaming currentStreaming = streamings.get(mediaId);
                    currentStreaming.setHomepageUrl(rs.getString(2));
                    currentStreaming.setProvider(StreamingProvider.from(rs.getString(3)));
                }
            } finally {
                DBUtil.close(rs, prepStmt);
            }
            // Not found
            for (String mediaIdNotFound : mediaIds) {
                Streaming currentStreaming = streamings.remove(mediaIdNotFound);
                media.remove(currentStreaming);
                SilverTrace.warn(GalleryComponentSettings.COMPONENT_NAME, "MediaDAO.decorateStreamings()",
                        "root.MSG_GEN_PARAM_VALUE",
                        "streaming not found (removed from result): " + mediaIdNotFound);
            }
        }
    }
}

From source file:com.silverpeas.gallery.dao.MediaDAO.java

/**
 * Adding all data of videos./*from  w ww  .  j  av a  2s .  com*/
 * @param con
 * @param media
 * @param videos
 * @throws SQLException
 */
private static void decorateVideos(final Connection con, List<Media> media, Map<String, Video> videos)
        throws SQLException {
    if (!videos.isEmpty()) {
        Collection<Collection<String>> idGroups = CollectionUtil.split(new ArrayList<String>(videos.keySet()));
        StringBuilder queryBase = new StringBuilder(SELECT_INTERNAL_MEDIA_PREFIX)
                .append("V.resolutionW, V.resolutionH, V.bitrate, V.duration from SC_Gallery_Internal I join "
                        + "SC_Gallery_Video V on I.mediaId = V.mediaId where I.mediaId in ");
        for (Collection<String> mediaIds : idGroups) {
            PreparedStatement prepStmt = null;
            ResultSet rs = null;
            try {
                prepStmt = con.prepareStatement(DBUtil.appendListOfParameters(queryBase, mediaIds).toString());
                DBUtil.setParameters(prepStmt, mediaIds);
                rs = prepStmt.executeQuery();
                while (rs.next()) {
                    String mediaId = rs.getString(1);
                    mediaIds.remove(mediaId);
                    Video currentVideo = videos.get(mediaId);
                    decorateInternalMedia(rs, currentVideo);
                    currentVideo.setDefinition(Definition.of(rs.getInt(8), rs.getInt(9)));
                    currentVideo.setBitrate(rs.getLong(10));
                    currentVideo.setDuration(rs.getLong(11));
                }
            } finally {
                DBUtil.close(rs, prepStmt);
            }
            // Not found
            for (String mediaIdNotFound : mediaIds) {
                Video currentVideo = videos.remove(mediaIdNotFound);
                media.remove(currentVideo);
                SilverTrace.warn(GalleryComponentSettings.COMPONENT_NAME, "MediaDAO.decorateVideos()",
                        "root.MSG_GEN_PARAM_VALUE",
                        "video not found (removed from result): " + mediaIdNotFound);
            }
        }
    }
}

From source file:dk.netarkivet.common.utils.DBUtils.java

/** Execute an SQL query and return whether the result contains any rows.
 *
 * NB: the provided connection is not closed.
 *
 * @param connection connection to the database.
 * @param query a query with ? for parameters (must not be null or
 * an empty String)//from  w ww  . j  av  a  2s .  c  o  m
 * @param args parameters of type string, int, long or boolean
 * @return True if executing the query resulted in at least one row.
 * @throws IOFailure if there were problems with the SQL query
 */
public static boolean selectAny(Connection connection, String query, Object... args) {
    ArgumentNotValid.checkNotNull(connection, "Connection connection");
    ArgumentNotValid.checkNotNullOrEmpty(query, "String query");
    ArgumentNotValid.checkNotNull(args, "Object... args");

    PreparedStatement s = null;
    try {
        s = prepareStatement(connection, query, args);
        return s.executeQuery().next();
    } catch (SQLException e) {
        throw new IOFailure("Error preparing SQL statement " + query + " args " + Arrays.toString(args) + "\n"
                + ExceptionUtils.getSQLExceptionCause(e), e);
    } finally {
        closeStatementIfOpen(s);
    }
}

From source file:com.novartis.opensource.yada.util.YADAUtils.java

/**
 * One-liner execution of a sql statement, returning an SQL {@link java.sql.ResultSet}.
 * <strong>Note: This method opens a db connection but DOES NOT CLOSE IT. 
 * Use the static method {@link ConnectionFactory#releaseResources(ResultSet)} to close it from 
 * the calling method</strong>//  w w w.j  ava  2s  .  co m
 * @param sql the query to execute
 * @param params the data values to map to query columns
 * @return a {@link java.sql.ResultSet} object containing the result of the query
 * @throws YADAConnectionException when the datasource is inaccessible
 * @throws YADASQLException when the JDBC configuration or execution fails
 */
public static ResultSet executePreparedStatement(String sql, Object[] params)
        throws YADAConnectionException, YADASQLException {
    ResultSet rs = null;
    try {
        Connection c = ConnectionFactory.getConnectionFactory().getConnection(ConnectionFactory.YADA_APP);
        PreparedStatement p = c.prepareStatement(sql);
        for (int i = 1; i <= params.length; i++) {
            Object param = params[i - 1];
            if (param instanceof String) {
                p.setString(i, (String) param);
            } else if (param instanceof Date) {
                p.setDate(i, (Date) param);
            } else if (param instanceof Integer) {
                p.setInt(i, ((Integer) param).intValue());
            } else if (param instanceof Float) {
                p.setFloat(i, ((Float) param).floatValue());
            }
        }
        rs = p.executeQuery();
    } catch (SQLException e) {
        throw new YADASQLException(e.getMessage(), e);
    }
    return rs;
}

From source file:com.flexive.core.security.FxDBAuthentication.java

/**
 * @param username the username/*from  w  ww . ja  va  2s. co m*/
 * @param password the password
 * @param currentTicket the UserTicket requesting the password match
 * @param ds thedatasource
 * @return returns true if the login and password match
 * @throws FxDbException on db errors
 * @throws FxLoginFailedException on authentication errors
 */
public static boolean checkLogin(String username, String password, UserTicket currentTicket, DataSource ds)
        throws FxDbException, FxLoginFailedException {
    FxContext inf = FxContext.get();

    // Avoid null pointer exceptions
    if (password == null)
        password = "";
    if (username == null)
        username = "";

    String curSql;
    PreparedStatement ps = null;
    Connection con = null;
    try {
        // Obtain a database connection
        con = ds.getConnection();
        //               1      2           3
        curSql = "SELECT a.ID,a.USERNAME,a.PASSWORD " + "FROM " + TBL_ACCOUNTS + " a " + "LEFT JOIN "
                + " (SELECT ID,ISLOGGEDIN,LAST_LOGIN,LAST_LOGIN_FROM,FAILED_ATTEMPTS,AUTHSRC FROM "
                + TBL_ACCOUNT_DETAILS
                + " WHERE APPLICATION=?) d ON a.ID=d.ID WHERE UPPER(a.LOGIN_NAME)=UPPER(?)";
        ps = con.prepareStatement(curSql);
        ps.setString(1, inf.getApplicationId());
        ps.setString(2, username);
        final ResultSet rs = ps.executeQuery();

        // Anything found
        if (rs == null || !rs.next())
            throw new FxLoginFailedException("Invalid user or password",
                    FxLoginFailedException.TYPE_USER_OR_PASSWORD_NOT_DEFINED);

        // check if the hashed password matches the hash stored in the database
        final long id = rs.getLong(1);
        final String dbUserName = rs.getString(2);
        final String hashedPass = rs.getString(3);

        // current user authorised to perform the check (ticket user id matches db user id?)
        if (id != currentTicket.getUserId() && !currentTicket.isGlobalSupervisor())
            throw new FxLoginFailedException("User not authorized to perform login check",
                    FxLoginFailedException.TYPE_USER_OR_PASSWORD_NOT_DEFINED);

        return FxSharedUtils.hashPassword(id, dbUserName, password).equals(hashedPass)
                // before 3.2.0 the default supervisor password was incorrectly hashed against the lower-cased login name
                || ("SUPERVISOR".equals(username)
                        && FxSharedUtils.hashPassword(id, "supervisor", password).equals(hashedPass));

    } catch (SQLException exc) {
        throw new FxDbException("Database error: " + exc.getMessage(), FxLoginFailedException.TYPE_SQL_ERROR);
    } finally {
        Database.closeObjects(FxDBAuthentication.class, con, ps);
    }
}

From source file:dk.netarkivet.common.utils.DBUtils.java

/** Execute an SQL statement and return the single integer
 * in the result set./* ww w  .  ja va 2  s  .c om*/
 *
 * @param s A prepared statement
 * @return The integer result, or null if the result value was null.
 * @throws IOFailure if the statement didn't result in exactly one integer.
 */
public static Integer selectIntValue(PreparedStatement s) {
    ArgumentNotValid.checkNotNull(s, "PreparedStatement s");
    try {
        ResultSet res = s.executeQuery();
        if (!res.next()) {
            throw new IOFailure("No results from " + s);
        }
        Integer resultInt = res.getInt(1);
        if (res.wasNull()) {
            resultInt = null;
        }
        if (res.next()) {
            throw new IOFailure("Too many results from " + s);
        }
        return resultInt;
    } catch (SQLException e) {
        throw new IOFailure(
                "SQL error executing statement " + s + "\n" + ExceptionUtils.getSQLExceptionCause(e), e);
    }
}

From source file:dk.netarkivet.common.utils.DBUtils.java

/** Execute an SQL statement and return the single long in the result set.
 *
 * @param s A prepared statement//w  ww .  j a v a  2  s. c o  m
 * @return The long result, or null if the result was a null value
 * Note that a null value is not the same as no result rows.
 * @throws IOFailure if the statement didn't result in exactly one row with
 * a long or null value
 */
public static Long selectLongValue(PreparedStatement s) {
    ArgumentNotValid.checkNotNull(s, "PreparedStatement s");
    try {
        ResultSet res = s.executeQuery();
        if (!res.next()) {
            throw new IOFailure("No results from " + s);
        }
        Long resultLong = res.getLong(1);
        if (res.wasNull()) {
            resultLong = null;
        }
        if (res.next()) {
            throw new IOFailure("Too many results from " + s);
        }
        return resultLong;
    } catch (SQLException e) {
        throw new IOFailure(
                "SQL error executing statement " + s + "\n" + ExceptionUtils.getSQLExceptionCause(e), e);
    }
}

From source file:dk.netarkivet.common.utils.DBUtils.java

/** Execute an SQL statement and return the single string in the result set.
 *
 * @param s A prepared statement// www .  j  a v a 2s  .c o m
 * @return The string result, or null if the result was a null value
 * Note that a null value is not the same as no result rows.
 * @throws IOFailure if the statement didn't result in exactly one row with
 * a string or null value
 */
public static String selectStringValue(PreparedStatement s) {
    ArgumentNotValid.checkNotNull(s, "PreparedStatement s");
    try {
        ResultSet res = s.executeQuery();
        if (!res.next()) {
            throw new IOFailure("No results from " + s);
        }
        String resultString = res.getString(1);
        if (res.wasNull()) {
            resultString = null;
        }
        if (res.next()) {
            throw new IOFailure("Too many results from " + s);
        }
        return resultString;
    } catch (SQLException e) {
        throw new IOFailure(
                "SQL error executing statement " + s + "\n" + ExceptionUtils.getSQLExceptionCause(e), e);
    }
}

From source file:dk.netarkivet.common.utils.DBUtils.java

/** Return a description of where an object is used elsewhere in the
 * database, or null.//w w  w.j a  v  a  2 s. c  o  m
 *
 * NB: the provided connection is not closed.
 *
 * @param connection connection to the database.
 * @param select A select statement finding the names of other uses.  The
 * statement should result in exactly one column of string values.
 * @param victim The object being used.
 * @param args Any objects that may be used to prepare the select statement.
 * @return A string describing the usages, or null if no usages were found.
 */
public static String getUsages(Connection connection, String select, Object victim, Object... args) {
    ArgumentNotValid.checkNotNull(connection, "Connection connection");
    PreparedStatement s = null;
    try {
        s = prepareStatement(connection, select, args);
        ResultSet res = s.executeQuery();
        if (res.next()) {
            List<String> usedIn = new ArrayList<String>();
            do {
                usedIn.add(res.getString(1));
            } while (res.next());
            return usedIn.toString();
        }
        return null;
    } catch (SQLException e) {
        final String message = "SQL error checking for usages of " + victim + "\n"
                + ExceptionUtils.getSQLExceptionCause(e);
        log.warn(message, e);
        throw new IOFailure(message, e);
    } finally {
        closeStatementIfOpen(s);
    }
}