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.aurel.track.dbase.InitReportTemplateBL.java

private static void addReportTemplateToDatabase(Integer oid, String name, String expfmt, String description) {

    String stmt = "INSERT INTO TEXPORTTEMPLATE (OBJECTID,NAME,EXPORTFORMAT,REPOSITORYTYPE,DESCRIPTION,PROJECT,PERSON,REPORTTYPE)"
            + "VALUES (" + oid + ",'" + name + "','" + expfmt + "',2,'" + description
            + "',NULL,1,'Jasper Report')";

    Connection coni = null;//from w ww.ja  v a 2  s .co  m
    Connection cono = null;
    ResultSet rs = null;
    try {
        coni = InitDatabase.getConnection();
        cono = InitDatabase.getConnection();
        PreparedStatement istmt = coni
                .prepareStatement("SELECT MAX(OBJECTID) FROM TEXPORTTEMPLATE WHERE OBJECTID < 100");
        Statement ostmt = cono.createStatement();

        rs = istmt.executeQuery();
        Integer maxInt = 0;
        if (rs != null) {
            rs.next();
            maxInt = rs.getInt(1);
        }
        if (oid.intValue() <= maxInt.intValue()) {
            return;
        }

        istmt = coni.prepareStatement("SELECT * FROM TEXPORTTEMPLATE WHERE OBJECTID = ?");
        istmt.setInt(1, oid);

        rs = istmt.executeQuery();
        if (rs == null || !rs.next()) {
            LOGGER.info("Adding report template with OID " + oid + ": " + name);
            try {
                ostmt.executeUpdate(stmt);
            } catch (Exception exc) {
                LOGGER.error("Problem...: " + exc.getMessage());
            }
        }
    } catch (Exception e) {
        LOGGER.debug(ExceptionUtils.getStackTrace(e));
    } finally {
        try {
            if (rs != null)
                rs.close();
            if (coni != null)
                coni.close();
            if (cono != null)
                cono.close();
        } catch (Exception e) {
            LOGGER.debug(ExceptionUtils.getStackTrace(e));
        }
    }
}

From source file:edu.jhu.pha.vospace.oauth.MySQLOAuthProvider2.java

public static synchronized Token getRequestToken(final String tokenStr) {

    Token tokenObj = DbPoolServlet.goSql("Get oauth token",
            "select request_token, token_secret, consumer_key, callback_url, identity, container_name, accessor_write_permission "
                    + "from oauth_accessors "
                    + "join oauth_consumers on oauth_consumers.consumer_id = oauth_accessors.consumer_id "
                    + "left outer join containers on containers.container_id = oauth_accessors.container_id "
                    + "left outer join users on users.user_id = containers.user_id "
                    + "left outer join user_identities on users.user_id = user_identities.user_id "
                    + "where request_token = ? limit 1",
            new SqlWorker<Token>() {
                @Override//  www .j av  a 2 s .co  m
                public Token go(Connection conn, PreparedStatement stmt) throws SQLException {
                    Token token = null;

                    stmt.setString(1, tokenStr);
                    ResultSet rs = stmt.executeQuery();
                    if (rs.next()) {
                        token = new Token(rs.getString("request_token"), rs.getString("token_secret"),
                                rs.getString("consumer_key"), rs.getString("callback_url"),
                                new MultivaluedMapImpl());

                        if (null != rs.getString("container_name"))
                            token.getAttributes().add("root_container", rs.getString("container_name"));
                    }

                    return token;
                }
            });

    return tokenObj;
}

From source file:com.example.querybuilder.server.Jdbc.java

public static ResultSet executeQuery(PreparedStatement preparedStatement, Object... parameters) {
    try {//  ww w.j av  a 2s  . c o m
        initializeParameters(preparedStatement, parameters);
        return preparedStatement.executeQuery();
    } catch (SQLException e) {
        throw new SqlRuntimeException(e);
    }
}

From source file:com.sql.SECExceptions.java

/**
 * Gathers a list of errors based on type and count total of them.
 *
 * @return/*from w  ww .  j a  v a 2  s.com*/
 */
public static List<SystemErrorModel> getErrorCounts() {
    List<SystemErrorModel> list = new ArrayList();
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "SELECT exceptionType, COUNT(*) AS 'num' " + "FROM SECExceptions "
                + "WHERE timeOccurred >= CAST(CURRENT_TIMESTAMP AS DATE) " + "GROUP BY exceptionType";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()) {
            SystemErrorModel item = new SystemErrorModel();
            item.setExceptionType(rs.getString("exceptionType") == null ? "" : rs.getString("exceptionType"));
            item.setNumber(rs.getInt("num"));
            list.add(item);
        }
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
    return list;
}

From source file:com.tethrnet.manage.db.SessionAuditDB.java

/**
 * returns a list of terminal sessions for session id
 *
 * @param sessionId session id/*from  ww w.  j  a  va  2 s. com*/
 * @return terminal sessions with host information
 */
public static SessionAudit getSessionsTerminals(Long sessionId) {
    //get db connection
    Connection con = null;
    SessionAudit sessionAudit = new SessionAudit();

    String sql = "select * from session_log, users where users.id= session_log.user_id and session_log.id = ? ";
    try {

        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        stmt.setLong(1, sessionId);

        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            sessionAudit.setId(rs.getLong("session_log.id"));
            sessionAudit.setSessionTm(rs.getTimestamp("session_tm"));
            sessionAudit.setUser(UserDB.getUser(con, rs.getLong("user_id")));
            sessionAudit.setHostSystemList(getHostSystemsForSession(con, sessionId));

        }

        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    //close db connection
    DBUtils.closeConn(con);

    return sessionAudit;

}

From source file:org.red5.server.plugin.admin.dao.UserDAO.java

public static UserDetails getUser(String username) {
    UserDetails details = null;/*from  w w  w .  ja  v  a2 s  .  c o m*/
    Connection conn = null;
    PreparedStatement stmt = null;
    try {
        // JDBC stuff
        DataSource ds = UserDatabase.getDataSource();

        conn = ds.getConnection();
        //make a statement
        stmt = conn.prepareStatement("SELECT * FROM APPUSER WHERE username = ?");
        stmt.setString(1, username);
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            log.debug("User found");
            details = new UserDetails();
            details.setEnabled("enabled".equals(rs.getString("enabled")));
            details.setPassword(rs.getString("password"));
            details.setUserid(rs.getInt("userid"));
            details.setUsername(rs.getString("username"));
            //
            rs.close();
            //get role            
            stmt = conn.prepareStatement("SELECT authority FROM APPROLE WHERE username = ?");
            stmt.setString(1, username);
            rs = stmt.executeQuery();
            if (rs.next()) {
                Collection<? extends GrantedAuthority> authorities;
                //                  authorities.addAll((Collection<?>) new GrantedAuthorityImpl(rs.getString("authority")));
                //                  details.setAuthorities(authorities);
                //
                //if (daoAuthenticationProvider != null) {
                //User usr = new User(username, details.getPassword(), true, true, true, true, authorities);
                //daoAuthenticationProvider.getUserCache().putUserInCache(usr);               
                //}
            }
        }
        rs.close();
    } catch (Exception e) {
        log.error("Error connecting to db", e);
    } finally {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
            }
        }
    }
    return details;
}

From source file:ch.newscron.referral.ReferralManager.java

public static int numberOfRegisteredUsers(String shortURL) {
    Connection connection = null;
    PreparedStatement query = null;
    ResultSet rs = null;//from  w  ww . j  a  v a  2 s.c  o  m
    try {
        connection = connect();
        query = connection.prepareStatement(
                "SELECT COUNT(*) as total FROM User, ShortURL WHERE User.campaignId = ShortURL.id AND ShortURL.shortUrl = ?");
        query.setString(1, shortURL);
        rs = query.executeQuery();
        rs.next();
        int totalNumbUsers = rs.getInt("total");
        return totalNumbUsers;
    } catch (Exception ex) {
        Logger.getLogger(ReferralManager.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        disconnect(connection, query, rs);
    }
    return -1;
}

From source file:edu.jhu.pha.vospace.oauth.MySQLOAuthProvider2.java

public static synchronized Token getAccessToken(final String tokenStr) {
    Token tokenObj = DbPoolServlet.goSql("Get oauth token",
            "select access_token, token_secret, consumer_key, callback_url, identity, container_name, accessor_write_permission, share_id "
                    + "from oauth_accessors "
                    + "join oauth_consumers on oauth_consumers.consumer_id = oauth_accessors.consumer_id "
                    + "left outer join containers on containers.container_id = oauth_accessors.container_id "
                    + "left outer join container_shares on oauth_accessors.share_key = container_shares.share_key "
                    + "left outer join users on users.user_id = containers.user_id "
                    + "left outer join user_identities on users.user_id = user_identities.user_id "
                    + "where access_token = ? limit 1",
            new SqlWorker<Token>() {
                @Override//  w w  w  .  jav  a 2s .c  o  m
                public Token go(Connection conn, PreparedStatement stmt) throws SQLException {
                    Token token = null;

                    stmt.setString(1, tokenStr);
                    ResultSet rs = stmt.executeQuery();
                    if (rs.next()) {

                        Set<USER_ROLES> rolesSet = new HashSet<USER_ROLES>();
                        if (null == rs.getString("share_id")) {
                            rolesSet.add(USER_ROLES.user);
                        } else {
                            if (rs.getBoolean("accessor_write_permission"))
                                rolesSet.add(USER_ROLES.rwshareuser);
                            else
                                rolesSet.add(USER_ROLES.roshareuser);
                        }
                        token = new Token(rs.getString("access_token"), rs.getString("token_secret"),
                                rs.getString("consumer_key"), rs.getString("callback_url"),
                                new SciDriveUser(rs.getString("identity"), rs.getString("container_name"),
                                        rs.getBoolean("accessor_write_permission")),
                                rolesSet, new MultivaluedMapImpl());

                    }
                    return token;
                }
            });

    return tokenObj;
}

From source file:fll.Team.java

/**
 * Builds a team object from its database info given the team number.
 * /* w w  w  . j  a  va2 s  .c om*/
 * @param connection Database connection.
 * @param teamNumber Number of the team for which to build an object.
 * @return The new Team object or null if the team was not found in the
 *         database.
 * @throws SQLException on a database access error.
 */
public static Team getTeamFromDatabase(final Connection connection, final int teamNumber) throws SQLException {
    // First, handle known non-database team numbers...
    if (teamNumber == NULL_TEAM_NUMBER) {
        return NULL;
    }
    if (teamNumber == TIE_TEAM_NUMBER) {
        return TIE;
    }
    if (teamNumber == BYE_TEAM_NUMBER) {
        return BYE;
    }

    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {

        stmt = connection.prepareStatement(
                "SELECT Division, Organization, TeamName FROM Teams" + " WHERE TeamNumber = ?");
        stmt.setInt(1, teamNumber);
        rs = stmt.executeQuery();
        if (rs.next()) {
            final String division = rs.getString(1);
            final String org = rs.getString(2);
            final String name = rs.getString(3);

            final Team x = new Team(teamNumber, org, name, division);
            return x;
        } else {
            return null;
        }
    } finally {
        SQLFunctions.close(rs);
        SQLFunctions.close(stmt);
    }
}

From source file:com.keybox.manage.db.SessionAuditDB.java

/**
 * returns a list of terminal sessions for session id
 *
 * @param sessionId session id//from  ww  w. jav  a 2s .  co  m
 * @return terminal sessions with host information
 */
public static SessionAudit getSessionsTerminals(Long sessionId) {
    //get db connection
    Connection con = null;
    SessionAudit sessionAudit = new SessionAudit();

    String sql = "select * from session_log, users where users.id= session_log.user_id and session_log.id = ? ";
    try {

        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        stmt.setLong(1, sessionId);

        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            sessionAudit.setId(rs.getLong("session_log.id"));
            sessionAudit.setSessionTm(rs.getTimestamp(SESSION_TM));
            sessionAudit.setUser(UserDB.getUser(con, rs.getLong(USER_ID)));
            sessionAudit.setHostSystemList(getHostSystemsForSession(con, sessionId));

        }

        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    //close db connection
    DBUtils.closeConn(con);

    return sessionAudit;

}