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.sql.WebCase.java

public static List<WEBCaseModel> getWebCaseList() {
    List<WEBCaseModel> list = new ArrayList();
    Connection conn = null;/*from w  w w  .  j a  v a2  s  .  co m*/
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = DBConnection.connectToDB();

        String sql = "SELECT" + " CMDSCase.CaseYear," + " CMDSCase.caseNumber," + " CMDSCase.casemonth,"
                + " CMDSCase.casetype," + " Users.initials AS ALJ," + " CMDSCase.groupNumber"
                + " FROM CMDSCase " + " LEFT JOIN Users ON" + " CMDSCase.aljID = Users.id"
                + " WHERE (CMDSCase.caseYear > YEAR(GETDATE()) - 5) "
                + " ORDER BY CMDSCase.caseyear, CMDSCase.caseNumber";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()) {
            WEBCaseModel item = new WEBCaseModel();
            item.setYear(rs.getString("caseyear") == null ? "" : rs.getString("caseyear"));
            item.setCaseSeqNumber(rs.getString("caseNumber") == null ? "" : rs.getString("caseNumber"));
            item.setMonth(rs.getString("casemonth") == null ? "" : rs.getString("casemonth"));
            item.setType(rs.getString("casetype") == null ? "" : rs.getString("casetype"));
            item.setALJ(rs.getString("ALJ") == null ? "" : rs.getString("ALJ"));
            item.setGroupNumber(rs.getString("groupNumber") == null ? "" : rs.getString("groupNumber"));
            list.add(item);
        }
    } catch (SQLException ex) {
        Logger.getLogger(WebCase.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
    return list;
}

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

/**
 * updates password for admin using auth token
 *///from ww w .  ja va  2 s .  c om
public static boolean updatePassword(Auth auth) {
    boolean success = false;

    Connection con = null;
    try {
        con = DBUtils.getConn();

        String prevSalt = getSaltByAuthToken(con, auth.getAuthToken());
        PreparedStatement stmt = con
                .prepareStatement("select * from users where auth_token like ? and password like ?");
        stmt.setString(1, auth.getAuthToken());
        stmt.setString(2, EncryptionUtil.hash(auth.getPrevPassword() + prevSalt));
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {

            String salt = EncryptionUtil.generateSalt();
            stmt = con.prepareStatement("update users set password=?, salt=? where auth_token like ?");
            stmt.setString(1, EncryptionUtil.hash(auth.getPassword() + salt));
            stmt.setString(2, salt);
            stmt.setString(3, auth.getAuthToken());
            stmt.execute();
            success = true;
        }

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

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

From source file:com.wso2.raspberrypi.Util.java

public static String getValue(String key) {
    String value = null;//from   w w  w. j  av  a2 s .  c o  m
    BasicDataSource ds = getBasicDataSource();

    Connection dbConnection = null;
    PreparedStatement prepStmt = null;
    ResultSet rs = null;
    try {
        dbConnection = ds.getConnection();
        prepStmt = dbConnection.prepareStatement("SELECT * FROM KV_PAIR WHERE k='" + key + "'");
        rs = prepStmt.executeQuery();

        while (rs.next()) {
            value = rs.getString("v");
        }
    } 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();
        }
    }
    return value;
}

From source file:com.wso2.raspberrypi.Util.java

public static List<RaspberryPi> getSelectedPis() {
    List<RaspberryPi> results = new ArrayList<RaspberryPi>();

    BasicDataSource ds = getBasicDataSource();

    Connection dbConnection = null;
    PreparedStatement prepStmt = null;
    ResultSet rs = null;/*from ww  w.  j  av  a 2  s . c om*/
    try {
        dbConnection = ds.getConnection();
        prepStmt = dbConnection.prepareStatement("SELECT * FROM RASP_PI WHERE selected=true");
        rs = prepStmt.executeQuery();

        while (rs.next()) {
            RaspberryPi pi = toRaspberryPi(rs);
            results.add(pi);
        }
    } 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();
        }
    }
    return results;
}

From source file:com.concursive.connect.web.webdav.WebdavManager.java

public static int validateUser(Connection db, HttpServletRequest req) throws Exception {
    String argHeader = req.getHeader("Authorization");
    HashMap params = getAuthenticationParams(argHeader);
    String username = (String) params.get("username");

    if (md5Helper == null) {
        md5Helper = MessageDigest.getInstance("MD5");
    }//w  w w . j a  va  2  s.  c  o  m

    int userId = -1;
    String password = null;
    PreparedStatement pst = db.prepareStatement(
            "SELECT user_id, webdav_password " + "FROM users " + "WHERE username = ? " + "AND enabled = ? ");
    pst.setString(1, username);
    pst.setBoolean(2, true);
    ResultSet rs = pst.executeQuery();
    if (rs.next()) {
        userId = rs.getInt("user_id");
        password = rs.getString("webdav_password");
    }
    rs.close();
    pst.close();
    if (userId == -1) {
        return userId;
    }
    String method = req.getMethod();
    String uri = (String) params.get("uri");
    String a2 = MD5Encoder.encode(md5Helper.digest((method + ":" + uri).getBytes()));
    String digest = MD5Encoder
            .encode(md5Helper.digest((password + ":" + params.get("nonce") + ":" + a2).getBytes()));
    if (!digest.equals(params.get("response"))) {
        userId = -1;
    }
    return userId;
}

From source file:com.wso2.raspberrypi.Util.java

public static List<RaspberryPi> getRaspberryPis(String orderBy) {
    System.out.println("Listing registered Raspberry Pis...");

    if (orderBy == null) {
        orderBy = "ip";
    }//from w  w  w .  j  av  a 2s. c o m
    List<RaspberryPi> results = new ArrayList<RaspberryPi>();

    BasicDataSource ds = getBasicDataSource();

    Connection dbConnection = null;
    PreparedStatement prepStmt = null;
    ResultSet rs = null;
    try {
        dbConnection = ds.getConnection();
        prepStmt = dbConnection.prepareStatement("SELECT * FROM RASP_PI ORDER BY " + orderBy);
        rs = prepStmt.executeQuery();

        while (rs.next()) {
            RaspberryPi pi = toRaspberryPi(rs);
            results.add(pi);
        }
    } 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();
        }
    }
    return results;
}

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

/**
 * Get a list of all the users in the system.
 * @return an Arraylist of Users representing all the users in the system.
 */// ww  w  .  jav a2 s . c o  m
public static ArrayList<User> getUserList() {
    ArrayList<User> list = new ArrayList<User>();

    Connection conn = Conn.initialize(); // Establish connection
    if (conn != null) {
        try {
            PreparedStatement getUserList = conn.prepareStatement(GET_USER_LIST);
            ResultSet userData = getUserList.executeQuery();

            while (userData.next()) {
                String username = userData.getString(Global.USER_NAME);
                list.add(getUserData(username));
            }
            userData.close();

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

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

public static int queryObjectRatingCount(Connection db, int objectId, String table, String uniqueField)
        throws SQLException {
    int count = -1;
    PreparedStatement pst = db
            .prepareStatement("SELECT rating_count FROM " + table + " WHERE " + uniqueField + " = ? ");
    pst.setInt(1, objectId);/*from www .j a  v  a 2 s .  c om*/
    ResultSet rs = pst.executeQuery();
    if (rs.next()) {
        count = rs.getInt("rating_count");
    }
    rs.close();
    pst.close();
    return count;
}

From source file:com.wso2.raspberrypi.Util.java

public static RaspberryPi getRaspberryPi(String macAddress) {
    System.out.println("Listing Raspberry Pi with Mac Address: " + macAddress);
    RaspberryPi pi = null;/* w  ww.j  a va2 s . co m*/

    BasicDataSource ds = getBasicDataSource();

    Connection dbConnection = null;
    PreparedStatement prepStmt = null;
    ResultSet rs = null;
    try {
        dbConnection = ds.getConnection();
        prepStmt = dbConnection.prepareStatement("SELECT * FROM RASP_PI WHERE mac='" + macAddress + "'");
        rs = prepStmt.executeQuery();

        while (rs.next()) {
            pi = toRaspberryPi(rs);
            break;
        }
    } 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();
        }
    }
    return pi;
}

From source file:com.wso2.raspberrypi.Util.java

public static void registerRaspberryPi(String macAddress, String ipAddress) {
    System.out.println("Registering Raspberry Pi: " + macAddress + "/" + ipAddress);
    BasicDataSource ds = getBasicDataSource();
    Connection dbConnection = null;
    PreparedStatement prepStmt = null;
    ResultSet rs = null;// www  . j  a  v a2  s.c  o  m
    try {
        dbConnection = ds.getConnection();
        prepStmt = dbConnection.prepareStatement("SELECT * FROM RASP_PI WHERE mac='" + macAddress + "'");
        rs = prepStmt.executeQuery();

        if (rs.next()) { // If it exists
            prepStmt = dbConnection.prepareStatement("UPDATE RASP_PI SET ip='" + ipAddress + "',last_updated='"
                    + System.currentTimeMillis() + "' WHERE mac='" + macAddress + "'");
            prepStmt.executeUpdate();
        } else {
            prepStmt = dbConnection.prepareStatement("INSERT INTO RASP_PI (mac,ip,last_updated) VALUES ('"
                    + macAddress + "','" + ipAddress + "','" + System.currentTimeMillis() + "' )");
            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();
        }
    }
}