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.wso2.raspberrypi.Util.java

public static void reservePi(String owner, String macAddress) {
    System.out.println("Changing owner of RPi " + macAddress + " to " + owner);
    BasicDataSource ds = getBasicDataSource();
    Connection dbConnection = null;
    PreparedStatement prepStmt = null;
    ResultSet rs = null;/*from  w  ww  .ja  v a 2  s .  c o m*/
    try {
        dbConnection = ds.getConnection();
        prepStmt = dbConnection.prepareStatement("SELECT * FROM RASP_PI where mac='" + macAddress + "'");
        rs = prepStmt.executeQuery();
        boolean alreadyOwned = false;
        while (rs.next()) {
            String oldOwner = rs.getString("owner");
            if (oldOwner != null && !oldOwner.isEmpty()) {
                alreadyOwned = true;
            }
        }
        if (!alreadyOwned) {
            prepStmt = dbConnection.prepareStatement(
                    "UPDATE RASP_PI SET owner='" + owner + "' where mac='" + macAddress + "'");
            prepStmt.execute();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            if (dbConnection != null) {
                dbConnection.close();
            }
            if (prepStmt != null) {
                prepStmt.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

From source file:ca.qc.adinfo.rouge.mail.db.MailDb.java

public static Collection<Mail> getMails(DBManager dbManager, long userId, boolean unreadOnly) {

    Collection<Mail> mailbox = new ArrayList<Mail>();

    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;//from   w w  w.  j a v  a 2 s . c  om

    String sql = "SELECT `id`, `from`, `to`, `content`, `status`, `time_sent` "
            + "FROM rouge_mail WHERE `to` = ? ";

    if (unreadOnly) {
        sql += " AND `status` = 'unr'";
    } else {
        sql += " AND NOT `status` = 'del'";
    }

    try {
        connection = dbManager.getConnection();
        stmt = connection.prepareStatement(sql);
        stmt.setLong(1, userId);

        rs = stmt.executeQuery();

        while (rs.next()) {
            mailbox.add(new Mail(rs.getLong("id"), rs.getLong("from"), rs.getLong("to"),
                    new RougeObject(JSONObject.fromObject(rs.getString("content")))));
        }

        return mailbox;

    } catch (SQLException e) {
        log.error(stmt);
        log.error(e);
        return null;

    } finally {

        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(connection);
    }
}

From source file:com.concursive.connect.web.modules.common.social.rating.dao.Rating.java

public static int queryObjectRatingValue(Connection db, int objectId, String table, String uniqueField)
        throws SQLException {
    int count = -1;
    PreparedStatement pst = db
            .prepareStatement("SELECT rating_value FROM " + table + " " + "WHERE " + uniqueField + " = ? ");
    pst.setInt(1, objectId);/*from  w w  w . jav  a 2s.co m*/
    ResultSet rs = pst.executeQuery();
    if (rs.next()) {
        count = rs.getInt("rating_value");
    }
    rs.close();
    pst.close();
    return count;
}

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

/**
 * auth user and return auth token if valid auth
 *
 * @param auth username and password object
 * @return auth token if success/*from www.j av a 2  s .com*/
 */
public static String login(Auth auth) {
    //check ldap first
    String authToken = ExternalAuthUtil.login(auth);

    if (StringUtils.isEmpty(authToken)) {

        Connection con = null;

        try {
            con = DBUtils.getConn();

            //get salt for user
            String salt = getSaltByUsername(con, auth.getUsername());
            //login
            PreparedStatement stmt = con
                    .prepareStatement("select * from users where enabled=true and username=? and password=?");
            stmt.setString(1, auth.getUsername());
            stmt.setString(2, EncryptionUtil.hash(auth.getPassword() + salt));
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {

                auth.setId(rs.getLong("id"));
                authToken = UUID.randomUUID().toString();
                auth.setAuthToken(authToken);
                auth.setAuthType(Auth.AUTH_BASIC);
                updateLogin(con, auth);

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

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

        DBUtils.closeConn(con);
    }

    return authToken;

}

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

/**
 * auth user and return auth token if valid auth
 *
 * @param auth username and password object
 * @return auth token if success/*from  w w w .j  a  v a  2s  .co m*/
 */
public static String login(Auth auth) {
    String authToken = null;
    //admin just for locally
    if (!auth.getUsername().equals("admin")) {
        //check ldap first
        authToken = ExternalAuthUtil.login(auth);
    }

    if (StringUtils.isEmpty(authToken)) {

        Connection con = null;

        try {
            con = DBUtils.getConn();

            //get salt for user
            String salt = getSaltByUsername(con, auth.getUsername());
            //login
            PreparedStatement stmt = con
                    .prepareStatement("select * from users where enabled=true and username=? and password=?");
            stmt.setString(1, auth.getUsername());
            stmt.setString(2, EncryptionUtil.hash(auth.getPassword() + salt));
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {

                auth.setId(rs.getLong("id"));
                authToken = UUID.randomUUID().toString();
                auth.setAuthToken(authToken);
                auth.setAuthType(Auth.AUTH_BASIC);
                updateLogin(con, auth);

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

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

        DBUtils.closeConn(con);
    }

    return authToken;

}

From source file:com.concursive.connect.web.modules.wiki.utils.WikiUtils.java

public static HashMap<String, ImageInfo> buildImageInfo(Connection db, int projectId) throws SQLException {
    HashMap<String, ImageInfo> images = new HashMap<String, ImageInfo>();
    // Full size image
    PreparedStatement pst = db
            .prepareStatement("SELECT client_filename, filename, image_width, image_height, version "
                    + "FROM project_files " + "WHERE link_module_id = ? " + "AND link_item_id = ? ");
    pst.setInt(1, Constants.PROJECT_WIKI_FILES);
    pst.setInt(2, projectId);// ww w  .  ja v  a  2 s  .  co m
    ResultSet rs = pst.executeQuery();
    while (rs.next()) {
        ImageInfo image = new ImageInfo(rs);
        images.put(image.getFilename(), image);
    }
    rs.close();
    pst.close();
    return images;
}

From source file:com.concursive.connect.web.modules.setup.utils.SetupUtils.java

/**
 * Determines if there is an administrative user configured in the database
 *
 * @param db// w ww  .  j  ava  2s. c  o  m
 * @return
 */
public static boolean isAdminInstalled(Connection db) {
    int count = -1;
    try {
        PreparedStatement pst = db.prepareStatement(
                "SELECT count(*) AS recordcount " + "FROM users " + "WHERE access_admin = ? ");
        pst.setBoolean(1, true);
        ResultSet rs = pst.executeQuery();
        rs.next();
        count = rs.getInt("recordcount");
        rs.close();
        pst.close();
    } catch (Exception e) {
    }
    return count > 0;
}

From source file:gridool.util.jdbc.JDBCUtils.java

/**
 * Execute an SQL SELECT query with replacement parameters.  The
 * caller is responsible for closing the connection.
 * //from  www  .ja v a  2s.c om
 * @param conn The connection to execute the query in.
 * @param sql The query to execute.
 * @param params The replacement parameters.
 * @param rsh The handler that converts the results into an object.
 * @return The object returned by the handler.
 */
public static Object query(Connection conn, String sql, Object[] params, ResultSetHandler rsh)
        throws SQLException {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    Object result = null;
    try {
        stmt = conn.prepareStatement(sql);
        fillStatement(stmt, params);
        verboseQuery(sql, params);
        rs = stmt.executeQuery();
        result = rsh.handle(rs);
    } catch (SQLException e) {
        rethrow(e, sql, params);
    } finally {
        try {
            close(rs);
        } finally {
            close(stmt);
        }
    }
    return result;
}

From source file:com.concursive.connect.web.modules.setup.utils.SetupUtils.java

/**
 * Determines if a default project has been installed
 *
 * @param db/* w  w  w.j  av  a2  s. c  om*/
 * @return
 */
public static boolean isDefaultProjectInstalled(Connection db) {
    int count = -1;
    try {
        PreparedStatement pst = db.prepareStatement(
                "SELECT count(*) AS recordcount " + "FROM projects " + "WHERE system_default = ? ");
        pst.setBoolean(1, true);
        ResultSet rs = pst.executeQuery();
        rs.next();
        count = rs.getInt("recordcount");
        rs.close();
        pst.close();
    } catch (Exception e) {
    }
    return count > 0;
}

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

/**
 * returns public keys based on sort order defined
 *
 * @param sortedSet object that defines sort order
 * @param userId user id//w w  w  . ja  v  a  2  s  .  co  m
 * @return sorted script list
 */
public static SortedSet getPublicKeySet(SortedSet sortedSet, Long userId) {

    ArrayList<PublicKey> publicKeysList = new ArrayList<PublicKey>();

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
        orderBy = "order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }
    String sql = "select * from public_keys where user_id = ? and enabled=true " + orderBy;

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        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"));
            publicKeysList.add(publicKey);

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

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

    sortedSet.setItemList(publicKeysList);
    return sortedSet;
}