Java examples for java.sql:PreparedStatement
Runs a SQL query that returns a single integer value.
/*// w w w. j av a2 s .c o m * Copyright (c) 2008 Bradley W. Kimmel * * Permission is hereby granted, free of charge, to any person * obtaining a copy of this software and associated documentation * files (the "Software"), to deal in the Software without * restriction, including without limitation the rights to use, * copy, modify, merge, publish, distribute, sublicense, and/or sell * copies of the Software, and to permit persons to whom the * Software is furnished to do so, subject to the following * conditions: * * The above copyright notice and this permission notice shall be * included in all copies or substantial portions of the Software. * * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES * OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND * NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT * HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, * WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR * OTHER DEALINGS IN THE SOFTWARE. */ //package com.java2s; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.sql.DataSource; public class Main { /** * Runs a SQL query that returns a single integer value. * @param ds The <code>DataSource</code> against which to run the query. * @param def The default value to return if the query returns no results. * @param query The SQL query to run. * @param param The parameters to the SQL query. * @return The value returned by the query, or <code>def</code> if the * query returns no results. It is assumed that the query * returns a result set consisting of a single row and column, and * that this value is an integer. Any additional rows or columns * returned will be ignored. * @throws SQLException If an error occurs while attempting to communicate * with the database. */ public static int queryInt(DataSource ds, int def, String query, Object... param) throws SQLException { Connection con = null; try { con = ds.getConnection(); return queryInt(con, def, query, param); } finally { close(con); } } /** * Runs a SQL query that returns a single integer value. * @param con The <code>Connection</code> against which to run the query. * @param def The default value to return if the query returns no results. * @param query The SQL query to run. * @param param The parameters to the SQL query. * @return The value returned by the query, or <code>def</code> if the * query returns no results. It is assumed that the query * returns a result set consisting of a single row and column, and * that this value is an integer. Any additional rows or columns * returned will be ignored. * @throws SQLException If an error occurs while attempting to communicate * with the database. */ public static int queryInt(Connection con, int def, String query, Object... param) throws SQLException { PreparedStatement stmt = null; try { stmt = con.prepareStatement(query); stmt.setMaxRows(1); for (int i = 0; i < param.length; i++) { stmt.setObject(i + 1, param[i]); } return queryInt(stmt, def); } finally { close(stmt); } } /** * Runs a SQL query that returns a single integer value. * @param stmt The <code>PreparedStatement</code> to run. * @param def The default value to return if the query returns no results. * @return The value returned by the query, or <code>def</code> if the * query returns no results. It is assumed that the query * returns a result set consisting of a single row and column, and * that this value is an integer. Any additional rows or columns * returned will be ignored. * @throws SQLException If an error occurs while attempting to communicate * with the database. */ public static int queryInt(PreparedStatement stmt, int def) throws SQLException { ResultSet rs = null; try { rs = stmt.executeQuery(); if (rs.next()) { int value = rs.getInt(1); if (!rs.wasNull()) { return value; } } return def; } finally { close(rs); } } /** * Closes a connection. Any <code>SQLException</code>s thrown are ignored. * @param con The <code>Connection</code> to close. If this value is * <code>null</code>, no action is performed. */ public static void close(Connection con) { if (con != null) { try { con.close(); } catch (SQLException e) { /* nothing to do. */ } } } /** * Closes a result set. Any <code>SQLException</code>s thrown are ignored. * @param con The <code>ResultSet</code> to close. If this value is * <code>null</code>, no action is performed. */ public static void close(ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException e) { /* nothing to do. */ } } } /** * Closes a statement. Any <code>SQLException</code>s thrown are ignored. * @param con The <code>Statement</code> to close. If this value is * <code>null</code>, no action is performed. */ public static void close(Statement stmt) { if (stmt != null) { try { stmt.close(); } catch (SQLException e) { /* nothing to do. */ } } } /** * Closes a result set, statement, and connection. Any * <code>SQLException</code>s thrown are ignored. * @param rs The <code>ResultSet</code> to close. If this value is * <code>null</code>, no <code>ResultSet</code> is closed. * @param stmt The <code>Statement</code> to close. If this value is * <code>null</code>, no <code>Statement</code> is closed. * @param con The <code>Connection</code> to close. If this value is * <code>null</code>, no <code>Connection</code> is closed. */ public static void close(ResultSet rs, Statement stmt, Connection con) { close(rs); close(stmt); close(con); } }