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:dk.netarkivet.common.utils.DBUtils.java

/**
 * Execute an SQL statement and return the list of Long-objects
 * in its result set./*from   w w  w . j  a  v a  2  s .  c  o  m*/
 *
 * NB: the provided connection is not closed.
 *
 * @param connection connection to the database.
 * @param query the given sql-query (must not be null or empty string)
 * @param args The arguments to insert into this query
 * @return the list of Long-objects in its result set
 */
public static List<Long> selectLongList(Connection connection, String query, Object... args) {
    ArgumentNotValid.checkNotNull(connection, "Connection connection");
    ArgumentNotValid.checkNotNullOrEmpty(query, "String query");
    ArgumentNotValid.checkNotNull(args, "Object... args");
    PreparedStatement s = null;
    try {
        s = prepareStatement(connection, query, args);
        ResultSet result = s.executeQuery();
        List<Long> results = new ArrayList<Long>();
        while (result.next()) {
            if (result.getLong(1) == 0L && result.wasNull()) {
                String warning = "NULL value encountered in query: " + query;
                log.warn(warning);
            }
            results.add(result.getLong(1));
        }
        return results;
    } catch (SQLException e) {
        throw new IOFailure("Error preparing SQL statement " + query + " args " + Arrays.toString(args) + "\n"
                + ExceptionUtils.getSQLExceptionCause(e), e);
    } finally {
        closeStatementIfOpen(s);
    }
}

From source file:dk.netarkivet.common.utils.DBUtils.java

/**
 * Execute an SQL statement and return the set of Long-objects
 * in its result set.//from   w w w .j  a v  a2s .  com
 *
 * NB: the provided connection is not closed.
 *
 * @param connection connection to the database.
 * @param query the given sql-query (must not be null or empty string)
 * @param args The arguments to insert into this query
 * @return the set of Long-objects in its result set
 */
public static Set<Long> selectLongSet(Connection connection, String query, Object... args) {
    ArgumentNotValid.checkNotNull(connection, "Connection connection");
    ArgumentNotValid.checkNotNullOrEmpty(query, "String query");
    ArgumentNotValid.checkNotNull(args, "Object... args");
    PreparedStatement s = null;
    try {
        s = prepareStatement(connection, query, args);
        ResultSet result = s.executeQuery();
        Set<Long> results = new TreeSet<Long>();
        while (result.next()) {
            if (result.getLong(1) == 0L && result.wasNull()) {
                String warning = "NULL value encountered in query: " + query;
                log.warn(warning);
            }
            results.add(result.getLong(1));
        }
        return results;
    } catch (SQLException e) {
        throw new IOFailure("Error preparing SQL statement " + query + " args " + Arrays.toString(args) + "\n"
                + ExceptionUtils.getSQLExceptionCause(e), e);
    } finally {
        closeStatementIfOpen(s);
    }
}

From source file:com.wso2telco.proxy.util.DBUtils.java

/**
 * Get MSISDN properties by operator Id.
 *
 * @param operatorId   operator Id.//from  w ww .ja  v  a2  s  . c  o  m
 * @param operatorName operator Name.
 * @return MSISDN properties of given operator.
 * @throws SQLException    on errors
 * @throws NamingException on errors
 */
public static List<MSISDNHeader> getMSISDNPropertiesByOperatorId(int operatorId, String operatorName)
        throws SQLException, NamingException {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    List<MSISDNHeader> msisdnHeaderList = new ArrayList<MSISDNHeader>();
    String queryToGetOperatorProperty = "SELECT  msisdnHeaderName, isHeaderEncrypted, encryptionImplementation, "
            + "msisdnEncryptionKey, priority FROM operators_msisdn_headers_properties WHERE operatorId = ? ORDER BY"
            + " priority ASC";
    try {
        connection = getConnectDBConnection();
        preparedStatement = connection.prepareStatement(queryToGetOperatorProperty);
        preparedStatement.setInt(1, operatorId);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            MSISDNHeader msisdnHeader = new MSISDNHeader();
            msisdnHeader.setMsisdnHeaderName(resultSet.getString(AuthProxyConstants.MSISDN_HEADER_NAME));
            msisdnHeader.setHeaderEncrypted(resultSet.getBoolean(AuthProxyConstants.IS_HEADER_ENCRYPTED));
            msisdnHeader.setHeaderEncryptionMethod(
                    resultSet.getString(AuthProxyConstants.ENCRYPTION_IMPLEMENTATION));
            msisdnHeader.setHeaderEncryptionKey(resultSet.getString(AuthProxyConstants.MSISDN_ENCRYPTION_KEY));
            msisdnHeader.setPriority(resultSet.getInt(AuthProxyConstants.PRIORITY));
            msisdnHeaderList.add(msisdnHeader);
        }
    } catch (SQLException e) {
        throw new SQLException(
                "Error occurred while retrieving operator MSISDN properties of operator : " + operatorName, e);
    } catch (NamingException e) {
        throw new ConfigurationException("DataSource could not be found in mobile-connect.xml");
    } finally {
        closeAllConnections(preparedStatement, connection, resultSet);
    }
    return msisdnHeaderList;
}

From source file:dk.netarkivet.common.utils.DBUtils.java

/** Execute an SQL statement and return the first long in the result set,
 * or null if resultset is empty./* www.  j a va  2s .  com*/
 *
 * NB: the provided connection is not closed.
 *
 * @param connection connection to database.
 * @param query a query with ? for parameters (must not be null
 * or empty string)
 * @param args parameters of type string, int, long or boolean
 * @return The long result, or will return null in one of the two following
 * cases: There is no results, or the first result is a null-value.
 * @throws IOFailure on SQL errors.
 */
public static Long selectFirstLongValueIfAny(Connection connection, String query, Object... args) {
    ArgumentNotValid.checkNotNull(connection, "Connection connection");
    ArgumentNotValid.checkNotNullOrEmpty(query, "String query");
    ArgumentNotValid.checkNotNull(args, "Object... args");
    PreparedStatement s = null;
    try {
        s = DBUtils.prepareStatement(connection, query, args);
        ResultSet rs = s.executeQuery();
        if (rs.next()) {
            return DBUtils.getLongMaybeNull(rs, 1);
        } else {
            return null;
        }
    } catch (SQLException e) {
        String message = "SQL error executing '" + query + "'" + "\n" + ExceptionUtils.getSQLExceptionCause(e);
        log.warn(message, e);
        throw new IOFailure(message, e);
    } finally {
        closeStatementIfOpen(s);
    }
}

From source file:dk.netarkivet.common.utils.DBUtils.java

/** Execute an SQL statement and return the list of strings
 * in its result set. This uses specifically the harvester database.
 *
 * NB: the provided connection is not closed.
 *
 * @param connection connection to the database.
 * @param query the given sql-query (must not be null or empty)
 * @param args The arguments to insert into this query (must not be null)
 * @throws IOFailure If this query fails
 * @return the list of strings in its result set
 *//*w  w w . jav a 2  s.c  om*/
public static List<String> selectStringList(Connection connection, String query, Object... args) {
    ArgumentNotValid.checkNotNull(connection, "Connection connection");
    ArgumentNotValid.checkNotNullOrEmpty(query, "String query");
    ArgumentNotValid.checkNotNull(args, "Object... args");
    PreparedStatement s = null;
    try {
        s = prepareStatement(connection, query, args);
        ResultSet result = s.executeQuery();
        List<String> results = new ArrayList<String>();
        while (result.next()) {
            if (result.getString(1) == null) {
                String warning = "NULL pointer found in resultSet from query: " + query;
                log.warn(warning);
                throw new IOFailure(warning);
            }
            results.add(result.getString(1));
        }
        return results;
    } catch (SQLException e) {
        throw new IOFailure("Error preparing SQL statement " + query + " args " + Arrays.toString(args) + "\n"
                + ExceptionUtils.getSQLExceptionCause(e), e);
    } finally {
        closeStatementIfOpen(s);
    }
}

From source file:dk.netarkivet.common.utils.DBUtils.java

/** Execute an SQL statement and return the list of strings -> id mappings
 * in its result set.//from  w  w w.  j a  v a 2 s .c  om
 *
 * NB: the provided connection is not closed.
 *
 * @param connection connection to the database.
 * @param query the given sql-query (must not be null or empty string)
 * @param args The arguments to insert into this query
 * @throws SQLException If this query fails
 * @return the list of strings -> id mappings
 */
public static Map<String, Long> selectStringLongMap(Connection connection, String query, Object... args)
        throws SQLException {
    ArgumentNotValid.checkNotNull(connection, "Connection connection");
    ArgumentNotValid.checkNotNullOrEmpty(query, "String query");
    ArgumentNotValid.checkNotNull(args, "Object... args");
    PreparedStatement s = null;
    try {
        s = prepareStatement(connection, query, args);
        ResultSet result = s.executeQuery();
        Map<String, Long> results = new HashMap<String, Long>();
        while (result.next()) {
            String resultString = result.getString(1);
            long resultLong = result.getLong(2);
            if ((resultString == null) || (resultLong == 0L && result.wasNull())) {
                String warning = "NULL pointers found in entry (" + resultString + "," + resultLong
                        + ") in resultset from query: " + query;
                log.warn(warning);
            }
            results.put(resultString, resultLong);
        }
        return results;
    } finally {
        closeStatementIfOpen(s);
    }

}

From source file:dk.netarkivet.common.utils.DBUtils.java

/**
 * Return an iterator to a list of Longs.
 * @param connection an open connection to the database
 * @param query The given sql-query (must not be null or empty string)
 * @param args The arguments to insert into this query
 * @return an iterator to a list of Longs.
 *//*from   ww  w  .j a  va  2 s  . co m*/
public static Iterator<Long> selectLongIterator(Connection connection, String query, Object... args) {
    ArgumentNotValid.checkNotNull(connection, "Connection connection");
    ArgumentNotValid.checkNotNullOrEmpty(query, "String query");
    ArgumentNotValid.checkNotNull(args, "Object... args");
    PreparedStatement s = null;
    try {
        s = prepareStatement(connection, 8192, query, args);
        ResultSet result = s.executeQuery();
        Iterator<Long> results = new ResultSetIterator<Long>(s, result) {
            @Override
            public Long filter(ResultSet result) {
                try {
                    return result.getLong(1);
                } catch (SQLException e) {
                    log.warn("Error retrieving long from resultset\n" + ExceptionUtils.getSQLExceptionCause(e),
                            e);
                    return 0L;
                }
            }
        };
        return results;
    } catch (SQLException e) {
        closeStatementIfOpen(s);
        throw new IOFailure("Error preparing SQL statement " + query + " args " + Arrays.toString(args) + "\n"
                + ExceptionUtils.getSQLExceptionCause(e), e);
    }
}

From source file:Main.java

protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");

    response.setContentType("text/html");
    PrintWriter writer = response.getWriter();

    Connection connection = getConnection();
    if (connection != null) {
        String sql = "SELECT SYSDATE FROM DUAL";
        try {/*ww  w.j av  a  2s  .  c o  m*/
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet rs = statement.executeQuery();

            while (rs.next()) {
                Date date = rs.getDate("SYSDATE");
                writer.println("The current date is " + dateFormat.format(date));
            }
            connection.close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}

From source file:dao.TagsQuery.java

public List run(Connection conn, int numTags) throws BaseDaoException {

    ResultSet rs = null;/*from   w  w  w.  j  av  a 2s .co m*/
    String sqlQuery = "select * from tags order by hits DESC limit " + numTags + "";
    //logger.info("sqlQuery = " + sqlQuery);

    try {
        PreparedStatement stmt = conn.prepareStatement(sqlQuery);
        rs = stmt.executeQuery();

        Vector columnNames = null;
        Yourkeywords tag = null;
        List tagList = new ArrayList();

        if (rs != null) {
            columnNames = dbutils.getColumnNames(rs);
        } else {
            return null;
        }

        while (rs.next()) {
            tag = (Yourkeywords) eop.newObject(DbConstants.YOURKEYWORDS);
            for (int j = 0; j < columnNames.size(); j++) {
                tag.setValue((String) columnNames.elementAt(j),
                        (String) rs.getString((String) columnNames.elementAt(j)));
            }
            tagList.add(tag);
        }
        return tagList;
    } catch (Exception e) {
        logger.warn("Error occured while executing tagsQuery run query" + sqlQuery, e);
        throw new BaseDaoException("Error occured while executing tagsQuery run query " + sqlQuery, e);
    }
}

From source file:dao.PblogMonthlyQuery.java

public HashSet run(Connection conn, String pblogid, String month, String year) {

    //String sqlQuery = "select LEFT(entrydate,10) as entrydate from pblogtopics where pblogid='"+pblogid+"' and MONTH(entrydate)='"+month+"' and year(entrydate)='"+year+"'";
    String sqlQuery = "select LEFT(entrydate,10) as entrydate from pblogtopics where pblogid='" + pblogid
            + "' and MONTH(entrydate)='" + month + "' and year(entrydate)='" + year + "'";

    try {//from   w ww. j  a v  a2s. com
        PreparedStatement stmt = conn.prepareStatement(sqlQuery);
        ResultSet rs = stmt.executeQuery();
        Vector columnNames = null;
        HashSet blogSet = new HashSet();
        Blog blog = null;

        if (rs != null) {
            columnNames = dbutils.getColumnNames(rs);
        }
        while (rs.next()) {
            blog = (Blog) eop.newObject(DbConstants.BLOG);
            for (int j = 0; j < columnNames.size(); j++) {
                blog.setValue((String) columnNames.elementAt(j),
                        (String) rs.getString((String) columnNames.elementAt(j)));
            }
            blogSet.add(blog);
        }
        return blogSet;
    } catch (Exception e) {
        logger.warn("Error occured while executing PblogMonthlyQuery run query" + sqlQuery, e);
        throw new BaseDaoException("Error occured while executing PblogMonthlyQuery run query " + sqlQuery, e);
    }
}