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.att.pirates.controller.GlobalDataController.java

public static List<String> GetAllPRISMIDs() {
    List<String> apps = new ArrayList<String>();

    ResultSet rs = null;/*  ww w .j a v a 2  s.  c  o m*/
    Connection conn = null;
    PreparedStatement preparedStatement = null;

    try {
        conn = DBUtility.getDBConnection();
        // SQL query command
        String SQL = " SELECT [PRISMId] " + "  FROM [Projects] ";

        preparedStatement = conn.prepareStatement(SQL);
        rs = preparedStatement.executeQuery();

        while (rs.next()) {
            String appName = rs.getString("PRISMId");
            apps.add(appName);
        }
    } catch (SQLException e) {
        logger.error(e.getMessage());
    } catch (Exception e) {
        logger.error(e.getMessage());
    } finally {
        try {
            if (rs != null)
                rs.close();
        } catch (Exception e) {
        }
        ;
        try {
            if (preparedStatement != null)
                preparedStatement.close();
        } catch (Exception e) {
        }
        ;
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
        }
        ;
    }
    return apps;
}

From source file:dsd.dao.DAOProvider.java

/**
 * This is the secure Version of SelectTable. To use this function in the
 * right way, you use ? instead of parameters and give the parameters
 * instead as string into the parameter-array.
 * //from w w w  .j a va2s .  c  o m
 * For Example: Instead of : "SELECT * FROM tb1 WHERE user= 'foo' and name
 * ='bar' use "SELECT * FROM tb1 WHERE user= ? and name = ?" and as
 * parameters[0]='foo' and parameters[1]='bar'
 * 
 * The order in the parameters are important!!
 * 
 * @param table
 * @param select
 * @param where
 * @param order
 * @param con
 * @param parameters
 * @return
 * @throws SQLException
 */
public static ResultSet SelectTableSecure(String table, String select, String where, String order,
        Connection con, Object[] parameters) throws SQLException {
    ResultSet resultSet = null;
    try {
        PreparedStatement command = con.prepareStatement(String.format("select %s from %s %s %s", select, table,
                (where.trim().equals("") ? "" : "where " + where),
                (order.trim().equals("") ? "" : "order by " + order)));
        if (parameters != null) {
            for (int i = 0; i < parameters.length; i++) {
                SetParameter(command, parameters[i], i + 1);
            }
        }
        resultSet = command.executeQuery();
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return resultSet;
}

From source file:edu.lafayette.metadb.model.userman.UserManDAO.java

/**
 * Get a user's data./* w ww  .  j a v a2 s. c  om*/
 * 
 * @param userName The username of the desired user.    
 * @return a User object with username, password and type for the requested user.
 * 
 */
public static User getUserData(String userName) {
    User requestedUser = null; // initialize return object

    Connection conn = Conn.initialize(); // Establish connection
    if (conn != null) {
        try {
            if (!MetaDbHelper.userExists(userName)) // If user doesn't exist
                return new User("", "", "", "", 0, ""); //Return a dummy user
            PreparedStatement getUserQuery = conn.prepareStatement(GET_USER_DATA);

            getUserQuery.setString(1, userName); // set parameter
            ResultSet userData = getUserQuery.executeQuery();

            if (userData != null) // The query result was not null
            {
                userData.next();
                // Get parameters
                String password = userData.getString(Global.USER_PASSWORD);
                String type = userData.getString(Global.USER_TYPE);
                String authType = userData.getString(Global.USER_AUTH_TYPE);
                long last_login = userData.getLong(Global.USER_LAST_LOGIN);
                String lastProj = userData.getString(Global.USER_LAST_ACCESS);
                requestedUser = new User(userName, password, type, authType, last_login, lastProj);

            }

            getUserQuery.close();
            userData.close();

            conn.close();
        } catch (Exception e) {
            MetaDbHelper.logEvent(e);
        }
    }
    return requestedUser;
}

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

/**
 * method to do order by based on the sorted set object for systems
 *
 * @param sortedSet sorted set object/*from   ww w . j  a  v  a 2  s  .com*/
 * @return sortedSet with list of host systems
 */
public static SortedSet getSystemSet(SortedSet sortedSet) {
    List<HostSystem> hostSystemList = new ArrayList<HostSystem>();

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
        orderBy = "order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }
    String sql = "select * from  system s ";
    //if profile id exists add to statement
    sql += StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))
            ? ",system_map m where s.id=m.system_id and m.profile_id=?"
            : "";
    sql += orderBy;

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))) {
            stmt.setLong(1, Long.valueOf(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID)));
        }
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            HostSystem hostSystem = new HostSystem();
            hostSystem.setId(rs.getLong("id"));
            hostSystem.setDisplayNm(rs.getString("display_nm"));
            hostSystem.setUser(rs.getString("user"));
            hostSystem.setHost(rs.getString("host"));
            hostSystem.setPort(rs.getInt("port"));
            hostSystem.setAuthorizedKeys(rs.getString("authorized_keys"));
            hostSystem.setStatusCd(rs.getString("status_cd"));
            hostSystemList.add(hostSystem);
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    sortedSet.setItemList(hostSystemList);
    return sortedSet;

}

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

/**
 * method to do order by based on the sorted set object for systems
 *
 * @param sortedSet sorted set object/*from  w w w. jav a  2s  .co  m*/
 * @return sortedSet with list of host systems
 */
public static SortedSet getSystemSet(SortedSet sortedSet) {
    List<HostSystem> hostSystemList = new ArrayList<>();

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
        orderBy = "order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }
    String sql = "select * from  system s ";
    //if profile id exists add to statement
    sql += StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))
            ? ",system_map m where s.id=m.system_id and m.profile_id=?"
            : "";
    sql += orderBy;

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))) {
            stmt.setLong(1, Long.valueOf(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID)));
        }
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            HostSystem hostSystem = new HostSystem();
            hostSystem.setId(rs.getLong("id"));
            hostSystem.setDisplayNm(rs.getString(DISPLAY_NM));
            hostSystem.setUser(rs.getString("user"));
            hostSystem.setHost(rs.getString("host"));
            hostSystem.setPort(rs.getInt("port"));
            hostSystem.setAuthorizedKeys(rs.getString(AUTHORIZED_KEYS));
            hostSystem.setStatusCd(rs.getString(STATUS_CD));
            hostSystemList.add(hostSystem);
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    sortedSet.setItemList(hostSystemList);
    return sortedSet;

}

From source file:com.chaosinmotion.securechat.server.commands.UpdateForgottenPassword.java

public static boolean processRequest(Login.UserInfo userinfo, JSONObject requestParams)
        throws ClassNotFoundException, SQLException, IOException {
    String newPassword = requestParams.getString("password");
    String requestToken = requestParams.getString("token");

    /*/*from w  w w.  j a  v  a2  s  .c om*/
     * Determine if the token matches for this user record. We are in the
     * unique situation of having a logged in user, but he doesn't know
     * his password. We also ignore any requests with an expired
     * token.
     */

    Connection c = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        /*
         * Delete old requests
         */
        c = Database.get();
        ps = c.prepareStatement("DELETE FROM forgotpassword WHERE expires < LOCALTIMESTAMP");
        ps.execute();

        ps.close();
        ps = null;

        /*
         * Verify the token we passed back was correct
         */
        ps = c.prepareStatement(
                "SELECT token " + "FROM forgotpassword " + "WHERE userid = ? " + "AND token = ?");
        ps.setInt(1, userinfo.getUserID());
        ps.setString(2, requestToken);
        rs = ps.executeQuery();
        if (!rs.next())
            return false; // token does not exist or expired.

        rs.close();
        rs = null;
        ps.close();
        ps = null;

        /*
         * Step 2: Modify the password.
         */

        ps = c.prepareStatement("UPDATE Users SET password = ? WHERE userid = ?");
        ps.setString(1, newPassword);
        ps.setInt(2, userinfo.getUserID());
        ps.execute();

        return true;
    } finally {
        if (rs != null)
            rs.close();
        if (ps != null)
            ps.close();
        if (c != null)
            c.close();
    }
}

From source file:com.hangum.tadpole.rdb.core.util.bander.cubrid.CubridExecutePlanUtils.java

/**
 * cubrid execute plan// w w w.  j a va 2 s.c om
 * 
 * @param userDB
 * @param sql
 * @return
 * @throws Exception
 */
public static String plan(UserDBDAO userDB, String sql) throws Exception {
    if (!sql.toLowerCase().startsWith("select")) {
        logger.error("[cubrid execute plan ]" + sql);
        throw new Exception("This statment not select. please check.");
    }
    Connection conn = null;
    ResultSet rs = null;
    PreparedStatement pstmt = null;

    try {
        Class.forName("cubrid.jdbc.driver.CUBRIDDriver");
        conn = DriverManager.getConnection(userDB.getUrl(), userDB.getUsers(), userDB.getPasswd());
        conn.setAutoCommit(false); //     auto commit? false  .      

        sql = StringUtils.trim(sql).substring(6);
        if (logger.isDebugEnabled())
            logger.debug("[qubrid modifying query]" + sql);
        sql = "select " + RECOMPILE + sql;

        pstmt = conn.prepareStatement(sql);
        ((CUBRIDStatement) pstmt).setQueryInfo(true);
        rs = pstmt.executeQuery();

        String plan = ((CUBRIDStatement) pstmt).getQueryplan(); //  ?    .
        conn.commit();

        if (logger.isDebugEnabled())
            logger.debug("cubrid plan text : " + plan);

        return plan;

    } finally {
        if (rs != null)
            rs.close();
        if (pstmt != null)
            pstmt.close();
        if (conn != null)
            conn.close();
    }
}

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

/**
 * checks fingerprint to determine if key is disabled
 * /*from   www .  java  2 s.  com*/
 * @param fingerprint public key fingerprint
 * @return true if disabled
 */
public static boolean isKeyDisabled(String fingerprint) {
    boolean isDisabled = false;

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con
                .prepareStatement("select * from  public_keys where fingerprint like ? and enabled=false");
        stmt.setString(1, fingerprint);
        ResultSet rs = stmt.executeQuery();

        if (rs.next()) {
            isDisabled = true;
        }

        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);
    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);

    return isDisabled;

}

From source file:net.sf.l2j.gameserver.model.entity.L2JOneoRusEvents.DM.java

public static void loadData() {
    _eventName = new String();
    _eventDesc = new String();
    _joiningLocationName = new String();
    _savePlayers = new Vector<String>();
    _players = new Vector<L2PcInstance>();
    _topPlayer = null;//from w ww  . j  ava  2s .com
    _npcSpawn = null;
    _joining = false;
    _teleport = false;
    _started = false;
    _sitForced = false;
    _npcId = 0;
    _npcX = 0;
    _npcY = 0;
    _npcZ = 0;
    _rewardId = 0;
    _rewardAmount = 0;
    _topKills = 0;
    _minlvl = 0;
    _maxlvl = 0;
    _playerColors = 0;
    _playerX = 0;
    _playerY = 0;
    _playerZ = 0;
    java.sql.Connection con = null;
    try {
        PreparedStatement statement;
        ResultSet rs;
        con = L2DatabaseFactory.getInstance().getConnection();
        statement = con.prepareStatement("Select * from dm");
        rs = statement.executeQuery();
        while (rs.next()) {
            _eventName = rs.getString("eventName");
            _eventDesc = rs.getString("eventDesc");
            _joiningLocationName = rs.getString("joiningLocation");
            _minlvl = rs.getInt("minlvl");
            _maxlvl = rs.getInt("maxlvl");
            _npcId = rs.getInt("npcId");
            _npcX = rs.getInt("npcX");
            _npcY = rs.getInt("npcY");
            _npcZ = rs.getInt("npcZ");
            _rewardId = rs.getInt("rewardId");
            _rewardAmount = rs.getInt("rewardAmount");
            _playerColors = rs.getInt("color");
            _playerX = rs.getInt("playerX");
            _playerY = rs.getInt("playerY");
            _playerZ = rs.getInt("playerZ");
        }
        statement.close();
    } catch (Exception e) {
        _log.error("Exception: DM.loadData(): " + e.getMessage());
    } finally {
        try {
            con.close();
        } catch (Exception e) {
        }
    }
}

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

/**
 * select all unique public keys for user
 *
 * @param userId user id/* w ww  .j a va2  s .com*/
 * @return public  key list for user
 */
public static List<PublicKey> getUniquePublicKeysForUser(Long userId) {

    Connection con = null;
    Map<String, PublicKey> keyMap = new LinkedHashMap();
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(
                "select * from public_keys where user_id=? and enabled=true order by key_nm asc");
        stmt.setLong(1, userId);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {

            PublicKey publicKey = new PublicKey();
            publicKey.setId(rs.getLong("id"));
            publicKey.setKeyNm(rs.getString("key_nm"));
            publicKey.setPublicKey(rs.getString("public_key"));
            publicKey.setProfile(ProfileDB.getProfile(con, rs.getLong("profile_id")));
            publicKey.setType(SSHUtil.getKeyType(publicKey.getPublicKey()));
            publicKey.setFingerprint(SSHUtil.getFingerprint(publicKey.getPublicKey()));
            publicKey.setCreateDt(rs.getTimestamp("create_dt"));
            keyMap.put(publicKey.getKeyNm() + " (" + publicKey.getFingerprint() + ")", publicKey);

        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    return new ArrayList(keyMap.values());

}