List of usage examples for org.apache.commons.dbutils ResultSetHandler handle
T handle(ResultSet rs) throws SQLException;
ResultSet into an Object. From source file:org.openconcerto.sql.utils.SQLUtils.java
/** * Execute all queries at once if possible. * // www . java 2 s . c o m * @param sysRoot where to execute. * @param queries what to execute. * @param handlers how to process the result sets, items can be <code>null</code>. * @return the results of the handlers. * @throws SQLException if an error occur * @throws RTInterruptedException if the current thread is interrupted. * @see SQLSystem#isMultipleResultSetsSupported() */ static public List<?> executeMultiple(final DBSystemRoot sysRoot, final List<String> queries, final List<? extends ResultSetHandler> handlers) throws SQLException, RTInterruptedException { final int size = handlers.size(); if (queries.size() != size) throw new IllegalArgumentException("Size mismatch " + queries + " / " + handlers); final List<Object> results = new ArrayList<Object>(size); final SQLSystem system = sysRoot.getServer().getSQLSystem(); if (system.isMultipleResultSetsSupported()) { final long timeMs = System.currentTimeMillis(); final long time = System.nanoTime(); final long afterCache = time; final StringBuilder sb = new StringBuilder(256 * size); for (final String q : queries) { sb.append(q); if (!q.trim().endsWith(";")) sb.append(';'); sb.append('\n'); } final String query = sb.toString(); sysRoot.getDataSource().useConnection(new ConnectionHandlerNoSetup<Object, SQLException>() { @Override public Object handle(SQLDataSource ds) throws SQLException { final Connection conn = ds.getConnection(); if (system == SQLSystem.MYSQL) { final ConnectionProperties connectionProperties = (ConnectionProperties) ((DelegatingConnection) conn) .getInnermostDelegate(); if (!connectionProperties.getAllowMultiQueries()) { throw new IllegalStateException( "Multi queries not allowed and the setting can only be set before connecting"); } } final long afterQueryInfo = System.nanoTime(); final long afterExecute, afterHandle; final Statement stmt = conn.createStatement(); try { if (Thread.currentThread().isInterrupted()) throw new RTInterruptedException("Interrupted before executing : " + query); stmt.execute(query); afterExecute = System.nanoTime(); for (final ResultSetHandler h : handlers) { if (Thread.currentThread().isInterrupted()) throw new RTInterruptedException("Interrupted while handling results : " + query); results.add(h == null ? null : h.handle(stmt.getResultSet())); stmt.getMoreResults(); } afterHandle = System.nanoTime(); } finally { stmt.close(); } SQLRequestLog.log(query, "executeMultiple", conn, timeMs, time, afterCache, afterQueryInfo, afterExecute, afterHandle, System.nanoTime()); return null; } }); } else { // use the same connection to allow some insert/update followed by a select sysRoot.getDataSource().useConnection(new ConnectionHandlerNoSetup<Object, SQLException>() { @Override public Object handle(SQLDataSource ds) throws SQLException { for (int i = 0; i < size; i++) { final ResultSetHandler rsh = handlers.get(i); // since the other if clause cannot support cache and this clause doesn't // have any table to fire, don't use cache results.add(sysRoot.getDataSource().execute(queries.get(i), rsh == null ? null : new IResultSetHandler(rsh, false))); } return null; } }); } return results; }
From source file:org.openlogics.gears.jdbc.DataStore.java
/** * @param preparedSt//from w w w .j av a 2 s .co m * @param handler * @param <T> * @return * @throws SQLException * @deprecated Used in an older version of teh CoreJavaBeans, but maybe useful yet, unitil find more features */ @Deprecated protected <T> T select(PreparedStatement preparedSt, ResultSetHandler<? extends T> handler) throws SQLException { logger.debug("Attempting to execute a preparedStatement QUERY: " + preparedSt + ", mapped to "); ResultSet rs = null; try { rs = preparedSt.executeQuery(); return handler.handle(rs); } finally { if (rs != null) rs.close(); DbUtils.close(preparedSt); if (isAutoClose()) { closeDBConn(); } } }
From source file:swp.bibjsf.persistence.Data.java
/** * Generic retrieval of elements from a given table fulfilling given * constraints and sorted by given order. Only the elements from...to in * that order are returned.// w w w. j a v a2 s .c om * * @param constraints * constraints to be fulfilled * @param from * index of first relevant element (index of very first element * is 0) * @param to * index of last relevant element * @param order * the ordering * @param table * name of the table from which to retrieve the elements * @param clazz * the class of the elements to be retrieved, i.e., Element.class * @return matching elements * @throws DataSourceException */ public <Element extends BusinessObject> List<Element> getElements(List<Constraint> constraints, final int from, final int to, List<OrderBy> order, String table, Class<Element> clazz) throws DataSourceException { // We want to retrieve only some of the matching results, but not // all. For a very large data set, we might otherwise run into // memory problems. And since this code is run by a server serving // multiple clients at once, memory consumption and computing // time is an issue. // // In Derby 10.7 upward, limiting the search for certain number of // results would be possible using the FETCH and OFFSET keywords as // follows: // // Sort T using column I, then fetch rows 11 through 20 of the sorted // rows (inclusive) // SELECT * FROM T ORDER BY I OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY // // Glashfish 3.1, however, ships with Derby 10.6.2.1. That version of // Derby // does not support FETCH/OFFSET. // If we ever migrate to a more current Derby version, we should use // FETCH/OFFSET // instead. // // For this reason, we follow a pagination strategy described at: // http://www.onjava.com/pub/a/onjava/2007/01/31/tuning-derby.html // // Notice that we set the max rows value to the last row that we need // (incremented by one). So, with this solution we fetch not only the // rows that we wanted (from - to), but first fetched a all rows up to // 'to' // and then filter to the rows of interest. Unfortunately, there is no // way // to tell the JDBC driver to start with a certain row, so we must // specify the // maximum row of the page that will be displayed. This means that // performance will be good for early pages and drop in performance as // the user browses results. The good news is that in most cases, the // user // will not go far, but will usually either find what he's looking for // in // the first few pages or refine the search query. logger.debug("get elements for table " + table); ArrayList<Element> allResults = new ArrayList<Element>(); try { Connection connection = dataSource.getConnection(); try { String query = "SELECT * FROM " + table + toQuery(constraints) + toOrderByClause(order); logger.debug("getElements " + query); PreparedStatement stmt = connection.prepareStatement(query); try { try { stmt.setMaxRows(to + 1); } catch (SQLException e) { // ignore this exception and try to run the query anyway } fillInArguments(constraints, stmt); ResultSet rs = stmt.executeQuery(); try { // Use the BeanHandler implementation to convert the // first // ResultSet row into a Reader JavaBean. ResultSetHandler<Element> handler = new BeanHandler<Element>(clazz); int i = 0; Element reader; while ((reader = handler.handle(rs)) != null) { if (from <= i && i <= to) { allResults.add(reader); } else if (i > to) { break; } i++; } } finally { rs.close(); } } finally { stmt.close(); } } finally { connection.close(); } } catch (SQLException e) { logger.error(e.getLocalizedMessage()); throw new DataSourceException(e.getLocalizedMessage()); } return allResults; }