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:ca.qc.adinfo.rouge.social.db.SocialDb.java

public static Collection<Long> getFriends(DBManager dbManager, long userId) {

    Collection<Long> friends = new ArrayList<Long>();

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

    String sql = "SELECT `friend_user_id` FROM rouge_social_friends WHERE `user_id` = ? ";

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

        rs = stmt.executeQuery();

        while (rs.next()) {
            friends.add(rs.getLong("friend_user_id"));
        }

        return friends;

    } 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.splicemachine.derby.test.framework.SpliceIndexWatcher.java

/**
 * Use this static method in cases where you want to create an index after creating/loading table.
 * TODO: redirect starting(Description) to call this method
 * @param connection/*from www .  j a va  2  s. c om*/
 * @param schemaName
 * @param tableName
 * @param indexName
 * @param definition
 * @param unique
 * @throws Exception
 */
public static void createIndex(Connection connection, String schemaName, String tableName, String indexName,
        String definition, boolean unique) throws Exception {
    PreparedStatement statement = null;
    ResultSet rs = null;
    try {
        //            connection = SpliceNetConnection.getConnection();
        statement = connection.prepareStatement(SELECT_SPECIFIC_INDEX);
        statement.setString(1, schemaName);
        statement.setString(2, indexName);
        rs = statement.executeQuery();
        if (rs.next()) {
            SpliceIndexWatcher.executeDrop(connection, schemaName, indexName);
        }
        try (Statement s = connection.createStatement()) {
            System.out.println(String.format("create " + (unique ? "unique" : "") + " index %s.%s on %s.%s %s",
                    schemaName, indexName, schemaName, tableName, definition));
            s.execute(String.format("create " + (unique ? "unique" : "") + " index %s.%s on %s.%s %s",
                    schemaName, indexName, schemaName, tableName, definition));
        }
    } finally {
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(statement);
    }
}

From source file:dept_integration.Dept_Integbean.java

public static int getPages(Connection con) throws Exception {
    int totalcount = 0;
    PreparedStatement ps = null;
    ResultSet rs = null;//from  w ww .jav  a2s  .  c  o m
    try {
        String sql = "select ceil(count(*)/10) as totalpage from department_integration";
        ps = con.prepareStatement(sql);
        rs = ps.executeQuery();
        if (rs.next()) {
            totalcount = rs.getInt("totalpage");
        }
    } catch (Exception e) {
        System.out.println(e.getMessage());
    }
    return totalcount;
}

From source file:edu.psu.citeseerx.disambiguation.CsxDisambiguation.java

public static void createBlocks(ListableBeanFactory factory) throws Exception {
    String dirpath = "data/csauthors/blocks";

    DataSource dataSource = (DataSource) factory.getBean("csxDataSource");

    PreparedStatement st = dataSource.getConnection().prepareStatement("SELECT * FROM authors",
            ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    st.setFetchSize(Integer.MIN_VALUE);
    ResultSet rs = st.executeQuery();

    initDirectories(dirpath);/*from   w ww  .  j  a  v a 2  s.com*/

    CsxAuthorFilter filter = (CsxAuthorFilter) factory.getBean("csxAuthorFilter");
    //new CsxAuthorFilter("data/csauthors/name_stopwords.txt");
    BufferedWriter skip = new BufferedWriter(new FileWriter("skip.txt"));

    int count = 0;
    Map<String, List<String>> blocks = new HashMap<String, List<String>>();
    while (rs.next()) {
        count++;
        if ((count % 10000) == 0)
            System.out.println("#Auth:" + count);
        String rsname = rs.getString("name");
        if (!filter.isStopword(rsname) && !filter.isInstitute(rsname) && !filter.isPosition(rsname)) {

            CsxAuthor auth = new CsxAuthor(rs);
            String lname = auth.getLastName();
            String fname = auth.getFirstName();

            if ((lname != null) && (fname != null)) {
                if ((lname.charAt(0) >= 'A') && (lname.charAt(0) <= 'Z') && (fname.charAt(0) >= 'A')
                        && (fname.charAt(0) <= 'Z') && !((fname.length() == 1) && (lname.length() == 1))
                        && !(lname.matches(".*/.*"))) {

                    String l_init = lname.substring(0, 1).toUpperCase();
                    String f_init = fname.substring(0, 1).toUpperCase();
                    String key = l_init + f_init + "/" + lname.toLowerCase() + "_" + f_init.toLowerCase()
                            + ".txt";

                    List<String> list;
                    if (!blocks.containsKey(key)) {
                        list = new ArrayList<String>();
                        blocks.put(key, list);
                    } else {
                        list = blocks.get(key);
                    }
                    list.add(auth.getId());
                } else {
                    skip.write("SKIP: [" + rsname + "]\n");
                }
            }
        }
    }
    skip.close();

    for (String key : blocks.keySet()) {
        List<String> aids = blocks.get(key);
        // only care about cluster with more than one document
        if (aids.size() > 1) {
            BufferedWriter out = new BufferedWriter(new FileWriter(dirpath + "/" + key));
            for (String aid : aids) {
                out.write(aid + "\n");
            }
            out.close();
        }
    }
}

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

/**
 * returns users based on sort order defined
 * @param sortedSet object that defines sort order
 * @return sorted user list//from   w  w  w .  j  a  v a  2s .c om
 */
public static SortedSet getUserSet(SortedSet sortedSet) {

    ArrayList<User> userList = new ArrayList<>();

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

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            User user = new User();
            user.setId(rs.getLong("id"));
            user.setFirstNm(rs.getString(FIRST_NM));
            user.setLastNm(rs.getString(LAST_NM));
            user.setEmail(rs.getString(EMAIL));
            user.setUsername(rs.getString(USERNAME));
            user.setPassword(rs.getString(PASSWORD));
            user.setAuthType(rs.getString(AUTH_TYPE));
            user.setUserType(rs.getString(USER_TYPE));
            userList.add(user);

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

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

    sortedSet.setItemList(userList);
    return sortedSet;
}

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

/**
 * returns all admin users based on sort order defined
 * @param sortedSet object that defines sort order
 * @return sorted user list//from   ww  w .j a  v  a 2  s . co  m
 */
public static SortedSet getAdminUserSet(SortedSet sortedSet) {

    ArrayList<User> userList = new ArrayList<>();

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
        orderBy = "order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }
    String sql = "select * from  users where enabled=true and user_type like '" + User.ADMINISTRATOR + "' "
            + orderBy;

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            User user = new User();
            user.setId(rs.getLong("id"));
            user.setFirstNm(rs.getString(FIRST_NM));
            user.setLastNm(rs.getString(LAST_NM));
            user.setEmail(rs.getString(EMAIL));
            user.setUsername(rs.getString(USERNAME));
            user.setPassword(rs.getString(PASSWORD));
            user.setAuthType(rs.getString(AUTH_TYPE));
            user.setUserType(rs.getString(USER_TYPE));
            userList.add(user);

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

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

    sortedSet.setItemList(userList);
    return sortedSet;
}

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

/**
 * returns key placement status of system
 *
 * @param systemId system id/*www . java2  s.  c  o m*/
 * @param userId user id
 */
public static HostSystem getSystemStatus(Long systemId, Long userId) {

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

        PreparedStatement stmt = con.prepareStatement("select * from status where id=? and user_id=?");
        stmt.setLong(1, systemId);
        stmt.setLong(2, userId);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            hostSystem = SystemDB.getSystem(con, rs.getLong("id"));
            hostSystem.setStatusCd(rs.getString("status_cd"));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

}

From source file:index.IncrementIndex.java

public static ResultSet getResult(String storeId, String sqlPath, String defaultPath) throws Exception {
    // ??//from   w  w  w .  j  a v a 2s  . c o  m
    config.load(new FileInputStream(sqlPath));
    // 
    String jdbcDriverClassName = config.getProperty("jdbcDriverClassName");
    String jdbcUrl = "jdbc:db2://";
    if (jdbcDriverClassName.contains("db2")) {
        jdbcUrl = "jdbc:db2://";
    } else if (jdbcDriverClassName.contains("mysql")) {
        jdbcUrl = "jdbc:mysql://";
    } else if (jdbcDriverClassName.contains("sqlserver")) {
        jdbcUrl = "jdbc:sqlserver://";
    } else if (jdbcDriverClassName.contains("sourceforge")) {
        jdbcUrl = "jdbc:jtds:sqlserver://";
    } else if (jdbcDriverClassName.contains("oracle")) {
        jdbcUrl = "jdbc:oracle:thin:@";
    }
    // url
    jdbcUrl = jdbcUrl + config.getProperty("iampDBIp") + ":" + config.getProperty("iampDBPort") + "/"
            + config.getProperty("iampDBName");
    String jdbcUsername = config.getProperty("iampDBUserName");
    String jdbcPassword = config.getProperty("iampDBPassword");
    // ?
    Class.forName(jdbcDriverClassName).newInstance();
    Connection conn = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword);
    // ???
    config.load(new FileInputStream(defaultPath));
    PreparedStatement stmt = null;
    ResultSet rs = null;
    String sql;
    if ("0".equals(storeId)) {
        sql = config.getProperty("nullSql");
        stmt = conn.prepareStatement(sql);
        rs = stmt.executeQuery();
    } else {
        sql = config.getProperty("entitySql");

        System.out.print("sql=" + sql);
        stmt = conn.prepareStatement(sql);
        stmt.setString(1, storeId);
        // 
        rs = stmt.executeQuery();
    }

    return rs;
}

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

/**
 * returns key placement status of system
 *
 * @param systemId system id/*from ww  w .j ava  2s.c o  m*/
 * @param userId user id
 */
public static HostSystem getSystemStatus(Long systemId, Long userId) {

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

        PreparedStatement stmt = con.prepareStatement("select * from status where id=? and user_id=?");
        stmt.setLong(1, systemId);
        stmt.setLong(2, userId);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            hostSystem = SystemDB.getSystem(con, rs.getLong("id"));
            hostSystem.setStatusCd(rs.getString(STATUS_CD));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

}

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

public static synchronized OAuthConsumer getConsumer(final String consumer_key) {
    return DbPoolServlet.goSql("Get oauth consumer",
            "select callback_url, consumer_key, consumer_secret, consumer_description, container from oauth_consumers where consumer_key = ?",
            new SqlWorker<Consumer>() {
                @Override/*from  w  w w.  j a v  a2  s  .  c o m*/
                public Consumer go(Connection conn, PreparedStatement stmt) throws SQLException {
                    Consumer consumer = null;

                    stmt.setString(1, consumer_key);
                    ResultSet rs = stmt.executeQuery();
                    if (rs.next()) {
                        consumer = new Consumer(rs.getString("consumer_key"), rs.getString("consumer_secret"),
                                new MultivaluedMapImpl());
                        consumer.getAttributes().add("name", rs.getString("consumer_key"));
                        consumer.getAttributes().add("description", rs.getString("consumer_description"));
                        consumer.getAttributes().add("container", rs.getString("container"));
                    }
                    return consumer;
                }
            });
}