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.manydesigns.portofino.persistence.QueryUtils.java

/**
 * Runs a SQL query against a session. The query can contain placeholders for the parameters, as supported by
 * {@link PreparedStatement}.//  w ww.  j ava  2 s. c o m
 * 
 * @param session the session
 * @param queryString the query
 * @param parameters parameters to substitute in the query
 * @return the results of the query as an Object[] (an array cell per column)
 */
// hongliangpan add
public static List<Map<String, Object>> runSqlReturnMap(Session session, final String queryString,
        final Object[] parameters) {
    final List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();

    try {
        session.doWork(new Work() {
            public void execute(Connection connection) throws SQLException {
                PreparedStatement stmt = connection.prepareStatement(queryString);
                ResultSet rs = null;
                try {
                    for (int i = 0; i < parameters.length; i++) {
                        stmt.setObject(i + 1, parameters[i]);
                    }
                    rs = stmt.executeQuery();
                    ResultSetMetaData md = rs.getMetaData();
                    int cc = md.getColumnCount();

                    while (rs.next()) {
                        Map<String, Object> t_row = new LinkedHashMap<String, Object>();
                        for (int i = 0; i < cc; i++) {
                            Object t_value = rs.getObject(i + 1);
                            t_row.put(md.getColumnLabel(i + 1), t_value);
                        }
                        result.add(t_row);
                    }
                } finally {
                    if (null != rs) {
                        rs.close();
                    }
                    if (null != stmt) {
                        stmt.close();
                    }
                }
            }
        });
    } catch (HibernateException e) {
        session.getTransaction().rollback();
        session.beginTransaction();
        throw e;
    }

    return result;
}

From source file:com.spend.spendService.DomainLearning.java

private void GetNewQuery() {
    try {//from w ww  .  j a v a  2  s  . c  om
        TimerTask timertask = new TimerTask() {
            public void run() {
                try {
                    domainList = new ArrayList<String>();
                    String[] seList = getSearchEngineNamesArray();
                    /* get urls from seedurlraw table */
                    PreparedStatement psmt = con.prepareStatement("SELECT url FROM seedurlraw");
                    ResultSet rs = psmt.executeQuery();
                    String regex = "[/]";
                    String regex2 = "[.]";
                    String PLDomain;
                    while (rs.next()) {
                        PLDomain = rs.getString("url");
                        PLDomain = PLDomain.replaceAll("http://|https://", "");
                        Pattern p = Pattern.compile(regex);
                        Matcher m = p.matcher(PLDomain);
                        if (m.find()) {
                            PLDomain = PLDomain.substring(0, m.start());
                        }
                        Pattern p2 = Pattern.compile(regex2);
                        Matcher m2 = p2.matcher(PLDomain);
                        int count = 0;
                        while (m2.find()) {
                            count++;
                        }
                        m2 = p2.matcher(PLDomain);
                        if (count > 1 && m2.find()) {
                            PLDomain = PLDomain.substring(m2.end());
                        }

                        //System.out.println(PLDomain);                        

                        if (!domainList.contains(PLDomain)) {
                            domainList.add(PLDomain);
                            newQuery = "sparql endpoint site:" + PLDomain;
                            for (Object se : seList) {
                                PreparedStatement psmt1 = con.prepareStatement(
                                        "INSERT INTO searchqueue(searchText,disabled,searchEngineName) VALUES(?,0,?);");
                                psmt1.setString(1, newQuery);
                                psmt1.setString(2, se.toString());
                                psmt1.executeUpdate();
                                psmt1.close();
                            }
                        }
                    }
                } catch (Exception ex) {
                    System.out
                            .println("DomainLearning.java timertask run function SQL ERROR " + ex.getMessage());
                }
            }
        };
        Timer timer = new Timer();
        DateFormat dateformat = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
        Date date = dateformat.parse("20-07-2017 00:00:00"); // set date and time
        timer.schedule(timertask, date, 1000 * 60 * 60 * 24 * 7); // for a week 1000*60*60*24*7
    } catch (Exception ex) {
        System.out.println("DomainLearning.java GetNewQuery function ERROR " + ex.getMessage());
    }
}

From source file:com.assignment4.products.Pro_details.java

/**
 * Retrieves representation of an instance of com.oracle.products.ProductResource
 * @return an instance of java.lang.String
 */// w w  w  . j a va 2s .  c o m
@GET
@Produces(MediaType.APPLICATION_JSON)
public String getAllProducts() throws SQLException {
    if (conn == null) {
        return "not connected";
    } else {
        String q = "Select * from products";
        PreparedStatement ps = conn.prepareStatement(q);
        ResultSet rs = ps.executeQuery();
        String r = "";
        JSONArray proArr = new JSONArray();
        while (rs.next()) {
            Map pm = new LinkedHashMap();
            pm.put("productID", rs.getInt("product_id"));
            pm.put("name", rs.getString("name"));
            pm.put("description", rs.getString("description"));
            pm.put("quantity", rs.getInt("quantity"));
            proArr.add(pm);
        }
        r = proArr.toString();
        return r.replace("},", "},\n");
    }

}

From source file:com.wso2telco.dep.verificationhandler.verifier.DatabaseUtils.java

/**
 * Gets the API id./*from   w  ww.ja v  a2 s  . c  o  m*/
 *
 * @param apiContext the api name
 * @return the API id
 * @throws NamingException the naming exception
 * @throws SQLException the SQL exception
 */
public static String getAPIId(String apiContext, String apiVersion) throws NamingException, SQLException {

    String apiId = null;

    /// String sql = "select * from am_subscription";
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {

        String sql = "select API_ID from AM_API where CONTEXT = ? ";

        if (apiVersion != null) {
            sql += " AND API_VERSION = ?";
        }

        conn = getAMDBConnection();
        ps = conn.prepareStatement(sql);

        ps.setString(1, apiContext);

        if (apiVersion != null) {
            ps.setString(2, apiVersion);
        }

        rs = ps.executeQuery();

        while (rs.next()) {
            apiId = rs.getString("API_ID");
        }

    } catch (SQLException e) {
        log.error("Error occured while writing southbound record.", e);
        throw e;
    } catch (NamingException e) {
        log.error("Error while finding the Datasource.", e);
        throw e;
    } finally {
        APIMgtDBUtil.closeAllConnections(ps, conn, rs);
    }

    return apiId;
}

From source file:com.ywang.alone.handler.task.AuthTask.java

/**
 *  { 'key':'2597aa1d37d432a','uid':'1020293' }
 * /*  w ww .  j a va  2  s  . c o  m*/
 * @param param
 * @return
 */
private static String getUserInfo(String msg) {

    JSONObject jsonObject = AloneUtil.newRetJsonObject();
    JSONObject param = JSON.parseObject(msg);
    String token = param.getString("key");
    String userId = null;

    if (StringUtils.isEmpty(token)) {
        jsonObject.put("ret", Constant.RET.NO_ACCESS_AUTH);
        jsonObject.put("errCode", Constant.ErrorCode.NO_ACCESS_AUTH);
        jsonObject.put("errDesc", Constant.ErrorDesc.NO_ACCESS_AUTH);
        return jsonObject.toJSONString();
    }

    Jedis jedis = JedisUtil.getJedis();
    Long tokenTtl = jedis.ttl("TOKEN:" + token);
    if (tokenTtl == -1) {
        jsonObject.put("ret", Constant.RET.NO_ACCESS_AUTH);
        jsonObject.put("errCode", Constant.ErrorCode.NO_ACCESS_AUTH);
        jsonObject.put("errDesc", Constant.ErrorDesc.NO_ACCESS_AUTH);
    } else {
        userId = jedis.get("TOKEN:" + token);
        LoggerUtil.logMsg("uid is " + userId);
    }

    JedisUtil.returnJedis(jedis);

    if (StringUtils.isEmpty(userId)) {
        jsonObject.put("ret", Constant.RET.NO_ACCESS_AUTH);
        jsonObject.put("errCode", Constant.ErrorCode.NO_ACCESS_AUTH);
        jsonObject.put("errDesc", Constant.ErrorDesc.NO_ACCESS_AUTH);
        return jsonObject.toJSONString();
    }

    String aimUid = param.getString("uid");//uid??
    if (StringUtils.isEmpty(aimUid)) {
        aimUid = userId;
    }
    DruidPooledConnection conn = null;
    PreparedStatement stmt = null;
    JSONObject data = new JSONObject();
    try {
        conn = DataSourceFactory.getInstance().getConn();

        conn.setAutoCommit(false);

        stmt = conn.prepareStatement("SELECT * FROM USERBASE WHERE USER_ID = ?",
                Statement.RETURN_GENERATED_KEYS);
        stmt.setString(1, aimUid);

        ResultSet userInfoRs = stmt.executeQuery();
        if (userInfoRs.next()) {
            UserInfo userInfo = new UserInfo();
            userInfo.setRegTime(userInfoRs.getLong("REG_TIME"));
            userInfo.setUserId(userInfoRs.getString("USER_ID"));
            userInfo.setAvatar(userInfoRs.getString("AVATAR"));
            userInfo.setNickName(userInfoRs.getString("NICKNAME"));
            userInfo.setAge(userInfoRs.getString("AGE"));
            userInfo.setHoroscope(userInfoRs.getString("HORO_SCOPE"));
            userInfo.setHeight(userInfoRs.getString("HEIGHT"));
            userInfo.setWeight(userInfoRs.getString("WEIGHT"));
            userInfo.setRoleName(userInfoRs.getString("ROLENAME"));
            userInfo.setAffection(userInfoRs.getString("AFFECTION"));
            userInfo.setPurpose(userInfoRs.getString("PURPOSE"));
            userInfo.setEthnicity(userInfoRs.getString("ETHNICITY"));
            userInfo.setOccupation(userInfoRs.getString("OCCUPATION"));
            userInfo.setLivecity(userInfoRs.getString("LIVECITY"));
            userInfo.setLocation(userInfoRs.getString("LOCATION"));
            userInfo.setTravelcity(userInfoRs.getString("TRAVELCITY"));
            userInfo.setMovie(userInfoRs.getString("MOVIE"));
            userInfo.setMusic(userInfoRs.getString("MUSIC"));
            userInfo.setBooks(userInfoRs.getString("BOOKS"));
            userInfo.setFood(userInfoRs.getString("FOOD"));
            userInfo.setOthers(userInfoRs.getString("OTHERS"));
            userInfo.setIntro(userInfoRs.getString("INTRO"));
            userInfo.setLastLoginTime(userInfoRs.getLong("LAST_LOGIN_TIME"));
            //            userInfo.setMessagePwd(userInfoRs
            //                  .getString("MESSAGE_PWD"));
            userInfo.setMessageUser(userInfoRs.getString("MESSAGE_USER"));
            //         
            userInfo.setOnline("1");

            data.put("userInfo", JSONObject.toJSON(userInfo));

            PreparedStatement pstmt = conn.prepareStatement(
                    "SELECT * FROM uploads WHERE USER_ID = ? and ENABLING=1", Statement.RETURN_GENERATED_KEYS);
            pstmt.setString(1, aimUid);

            ResultSet pSet = pstmt.executeQuery();
            JSONArray jsonArray = new JSONArray();
            while (pSet.next()) {
                jsonArray.add(pSet.getString("PHOTO_PATH"));
            }
            data.put("photos", JSONObject.toJSON(jsonArray));
            jsonObject.put("data", JSONObject.toJSON(data));

            pSet.close();
            pstmt.close();

        } else {
            jsonObject.put("ret", Constant.RET.SYS_ERR);
            jsonObject.put("errCode", Constant.ErrorCode.SYS_ERR);
            jsonObject.put("errDesc", Constant.ErrorDesc.SYS_ERR);
        }

        userInfoRs.close();
        conn.commit();
        conn.setAutoCommit(true);
    } catch (SQLException e) {
        LoggerUtil.logServerErr(e);
        jsonObject.put("ret", Constant.RET.SYS_ERR);
        jsonObject.put("errCode", Constant.ErrorCode.SYS_ERR);
        jsonObject.put("errDesc", Constant.ErrorDesc.SYS_ERR);
    } finally {
        try {
            if (null != stmt) {
                stmt.close();
            }
            if (null != conn) {
                conn.close();
            }
        } catch (SQLException e) {
            LoggerUtil.logServerErr(e.getMessage());
        }
    }

    return jsonObject.toJSONString();

}

From source file:com.l2jfree.gameserver.datatables.TeleportLocationTable.java

public void reloadAll() {
    _teleports = new FastMap<Integer, L2TeleportLocation>();

    Connection con = null;//from w  w  w  . j  av  a2 s  . c o  m
    try {
        con = L2DatabaseFactory.getInstance().getConnection(con);
        PreparedStatement statement = con
                .prepareStatement("SELECT Description, id, loc_x, loc_y, loc_z, price, fornoble FROM teleport");
        ResultSet rset = statement.executeQuery();
        L2TeleportLocation teleport;

        while (rset.next()) {
            teleport = new L2TeleportLocation();

            teleport.setTeleId(rset.getInt("id"));
            teleport.setLocX(rset.getInt("loc_x"));
            teleport.setLocY(rset.getInt("loc_y"));
            teleport.setLocZ(rset.getInt("loc_z"));
            if (Config.ALT_GAME_FREE_TELEPORT)
                teleport.setPrice(0);
            else
                teleport.setPrice(rset.getInt("price"));
            teleport.setIsForNoble(rset.getInt("fornoble") == 1);

            _teleports.put(teleport.getTeleId(), teleport);
        }

        rset.close();
        statement.close();

        _log.info("TeleportLocationTable: Loaded " + _teleports.size() + " Teleport Location Templates.");
    } catch (Exception e) {
        _log.error("error while creating teleport table ", e);
    } finally {
        L2DatabaseFactory.close(con);
    }
}

From source file:com.fpmislata.banco.persistencia.impl.SucursalBancariaDAOImplJDBC.java

@Override
public List<SucursalBancaria> findAll() {
    Connection connection = connectionFactory.getConnection();
    List<SucursalBancaria> sucursalesBancarias = new ArrayList<>();

    String SQL = "SELECT * FROM sucursalbancaria";

    try {/*from  w  ww  . j  ava 2s .  com*/
        PreparedStatement preparedStatement = connection.prepareStatement(SQL);

        ResultSet resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            SucursalBancaria sucursalBancaria = new SucursalBancaria();
            sucursalBancaria.setId(resultSet.getInt(1));
            sucursalBancaria.setEntidadPertenece(resultSet.getInt(2));
            sucursalBancaria.setCodigoSucursal(resultSet.getString(3));
            sucursalBancaria.setNombreSucursal(resultSet.getString(4));

            sucursalesBancarias.add(sucursalBancaria);
        }

        return sucursalesBancarias;
    } catch (Exception ex) {
        throw new RuntimeException(ex);
    } finally {
        try {
            connection.close();
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        }
    }

}

From source file:com.stratelia.webactiv.util.DBUtil.java

/**
 * Update query executor./*w w w  .  j  a  v  a  2s  .c om*/
 * @param con
 * @param updateQuery
 * @param parameters
 * @throws SQLException
 */
@SuppressWarnings("unchecked")
public static <ROW_ENTITY> List<ROW_ENTITY> select(Connection con, String updateQuery, Object parameters,
        SelectResultRowProcessor<ROW_ENTITY> rowProcess) throws SQLException {
    PreparedStatement st = null;
    ResultSet rs = null;
    try {
        st = con.prepareStatement(updateQuery);
        final Collection<Object> sqlParams;
        if (parameters instanceof Object[]) {
            sqlParams = Arrays.asList((Object[]) parameters);
        } else if (parameters instanceof Collection) {
            sqlParams = (Collection) parameters;
        } else {
            sqlParams = Arrays.asList(parameters);
        }
        DBUtil.setParameters(st, sqlParams);
        rs = st.executeQuery();
        List<ROW_ENTITY> entities = new ArrayList<ROW_ENTITY>();
        int i = 0;
        while (rs.next()) {
            if (rowProcess.limit > 0 && entities.size() >= rowProcess.limit) {
                break;
            }
            ROW_ENTITY entity = rowProcess.currentRow(i, rs);
            if (entity != null) {
                entities.add(entity);
            }
            i++;
        }
        return entities;
    } finally {
        DBUtil.close(rs, st);
    }
}

From source file:fr.mael.microrss.ttrss.TTRssImporter.java

private Map<Integer, Category> parseCategories(Connection connection) throws SQLException {
    Map<Integer, Category> cats = new HashMap<Integer, Category>();
    PreparedStatement statement = connection.prepareStatement("SELECT * FROM ttrss_feed_categories");
    ResultSet rs = null;/*  ww w  .  j  a  v a  2s  . co m*/
    try {
        rs = statement.executeQuery();
        while (rs.next()) {
            Category cat = new Category();
            cat.setTitle(rs.getString("title"));
            int parentId = rs.getInt("parent_cat");
            if (parentId != 0) {
                cat.setCategoryId(parentId);
            }
            cat.setUser(securityUtil.getCurrentUser());
            cat = categoryService.save(cat);
            cats.put(rs.getInt("id"), cat);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        rs.close();
    }
    return cats;
}

From source file:oobbit.orm.LinkConnections.java

public ArrayList<LinkConnection> getAll(int linkId) throws SQLException, NothingWasFoundException {
    PreparedStatement statement = getConnection()
            .prepareStatement("SELECT * FROM `connections` WHERE `source_link_id` = ?;");
    statement.setInt(1, linkId);/*from  www. j  av a2 s.c  om*/

    return parseResults(statement.executeQuery());
}