Package io.jmix.reports.yarg.util.db

Class QueryRunner

java.lang.Object
io.jmix.reports.yarg.util.db.QueryRunner
public class QueryRunner extends Object
Executes SQL queries with pluggable strategies for handling ResultSets. This class is thread safe.
See Also:

  • Field Details

    • ds

      protected DataSource ds
      The DataSource to retrieve connections from.

  • Constructor Details

    • QueryRunner

      public QueryRunner()
      Constructor for QueryRunner.

    • QueryRunner

      public QueryRunner(DataSource ds)
      Constructor for QueryRunner. Methods that do not take a Connection parameter will retrieve connections from this DataSource.
      Parameters:
      ds - The DataSource to retrieve connections from.

  • Method Details

    • batch

      public int[] batch(Connection conn, String sql, Object[][] params) throws SQLException
      Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
      Parameters:
      conn - The Connection to use to run the query. The caller is responsible for closing this Connection.
      sql - The SQL to execute.
      params - An array of query replacement parameters. Each row in this array is one set of batch replacement values.
      Returns:
      The number of rows updated per statement.
      Throws:
      SQLException - if a database access error occurs
      Since:
      DbUtils 1.1

    • batch

      public int[] batch(Connection conn, String sql, Object[][] params, int[] paramTypes) throws SQLException
      Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
      Parameters:
      conn - The Connection to use to run the query. The caller is responsible for closing this Connection.
      sql - The SQL to execute.
      params - An array of query replacement parameters. Each row in this array is one set of batch replacement values.
      paramTypes - Query replacement parameters types; null is a valid value to pass in.
      Returns:
      The number of rows updated per statement.
      Throws:
      SQLException - if a database access error occurs

    • batch

      public int[] batch(String sql, Object[][] params) throws SQLException
      Execute a batch of SQL INSERT, UPDATE, or DELETE queries. The Connection is retrieved from the DataSource set in the constructor. This Connection must be in auto-commit mode or the update will not be saved.
      Parameters:
      sql - The SQL to execute.
      params - An array of query replacement parameters. Each row in this array is one set of batch replacement values.
      Returns:
      The number of rows updated per statement.
      Throws:
      SQLException - if a database access error occurs
      Since:
      DbUtils 1.1

    • batch

      public int[] batch(String sql, Object[][] params, int[] paramTypes) throws SQLException
      Execute a batch of SQL INSERT, UPDATE, or DELETE queries. The Connection is retrieved from the DataSource set in the constructor. This Connection must be in auto-commit mode or the update will not be saved.
      Parameters:
      sql - The SQL to execute.
      params - An array of query replacement parameters. Each row in this array is one set of batch replacement values.
      paramTypes - Query replacement parameters types; null is a valid value to pass in.
      Returns:
      The number of rows updated per statement.
      Throws:
      SQLException - if a database access error occurs

    • fillStatement

      protected void fillStatement(PreparedStatement stmt, Object[] params) throws SQLException
      Fill the PreparedStatement replacement parameters with the given objects.
      Parameters:
      stmt - PreparedStatement to fill
      params - Query replacement parameters; null is a valid value to pass in.
      Throws:
      SQLException - if a database access error occurs

    • fillStatement

      protected void fillStatement(PreparedStatement stmt, Object[] params, int[] paramTypes) throws SQLException
      Fill the PreparedStatement replacement parameters with the given objects.
      Parameters:
      stmt - PreparedStatement to fill
      params - Query replacement parameters; null is a valid value to pass in.
      paramTypes - Query replacement parameters types; null is a valid value to pass in.
      Throws:
      SQLException - if a database access error occurs

    • getDataSource

      public DataSource getDataSource()
      Returns the DataSource this runner is using. QueryRunner methods always call this method to get the DataSource so subclasses can provide specialized behavior.
      Returns:
      DataSource the runner is using

    • prepareStatement

      protected PreparedStatement prepareStatement(Connection conn, String sql) throws SQLException
      Factory method that creates and initializes a PreparedStatement object for the given SQL. QueryRunner methods always call this method to prepare statements for them. Subclasses can override this method to provide special PreparedStatement configuration if needed. This implementation simply calls conn.prepareStatement(sql).
      Parameters:
      conn - The Connection used to create the PreparedStatement
      sql - The SQL statement to prepare.
      Returns:
      An initialized PreparedStatement.
      Throws:
      SQLException - if a database access error occurs

    • prepareConnection

      protected Connection prepareConnection() throws SQLException
      Factory method that creates and initializes a Connection object. QueryRunner methods always call this method to retrieve connections from its DataSource. Subclasses can override this method to provide special Connection configuration if needed. This implementation simply calls ds.getConnection().
      Returns:
      An initialized Connection.
      Throws:
      SQLException - if a database access error occurs
      Since:
      DbUtils 1.1

    • query

      public <T> T query(Connection conn, String sql, Object param, ResultSetHandler<T> rsh) throws SQLException
      Execute an SQL SELECT query with a single replacement parameter. The caller is responsible for closing the connection.
      Parameters:
      conn - The connection to execute the query in.
      sql - The query to execute.
      param - The replacement parameter.
      rsh - The handler that converts the results into an object.
      Returns:
      The object returned by the handler.
      Throws:
      SQLException - if a database access error occurs

    • query

      public <T> T query(Connection conn, String sql, Object[] params, ResultSetHandler<T> rsh) throws SQLException
      Execute an SQL SELECT query with replacement parameters. The caller is responsible for closing the connection.
      Parameters:
      conn - The connection to execute the query in.
      sql - The query to execute.
      params - The replacement parameters.
      rsh - The handler that converts the results into an object.
      Returns:
      The object returned by the handler.
      Throws:
      SQLException - if a database access error occurs

    • query

      public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh) throws SQLException
      Execute an SQL SELECT query without any replacement parameters. The caller is responsible for closing the connection.
      Parameters:
      conn - The connection to execute the query in.
      sql - The query to execute.
      rsh - The handler that converts the results into an object.
      Returns:
      The object returned by the handler.
      Throws:
      SQLException - if a database access error occurs

    • query

      public <T> T query(String sql, Object param, ResultSetHandler<T> rsh) throws SQLException
      Executes the given SELECT SQL with a single replacement parameter. The Connection is retrieved from the DataSource set in the constructor.
      Parameters:
      sql - The SQL statement to execute.
      param - The replacement parameter.
      rsh - The handler used to create the result object from the ResultSet.
      Returns:
      An object generated by the handler.
      Throws:
      SQLException - if a database access error occurs

    • query

      public <T> T query(String sql, Object[] params, ResultSetHandler<T> rsh) throws SQLException
      Executes the given SELECT SQL query and returns a result object. The Connection is retrieved from the DataSource set in the constructor.
      Parameters:
      sql - The SQL statement to execute.
      params - Initialize the PreparedStatement's IN parameters with this array.
      rsh - The handler used to create the result object from the ResultSet.
      Returns:
      An object generated by the handler.
      Throws:
      SQLException - if a database access error occurs

    • query

      public <T> T query(String sql, ResultSetHandler<T> rsh) throws SQLException
      Executes the given SELECT SQL without any replacement parameters. The Connection is retrieved from the DataSource set in the constructor.
      Parameters:
      sql - The SQL statement to execute.
      rsh - The handler used to create the result object from the ResultSet.
      Returns:
      An object generated by the handler.
      Throws:
      SQLException - if a database access error occurs

    • rethrow

      protected void rethrow(SQLException cause, String sql, Object[] params) throws SQLException
      Throws a new exception with a more informative error message.
      Parameters:
      cause - The original exception that will be chained to the new exception when it's rethrown.
      sql - The query that was executing when the exception happened.
      params - The query replacement parameters; null is a valid value to pass in.
      Throws:
      SQLException - if a database access error occurs

    • setDataSource

      public void setDataSource(DataSource dataSource)
      Sets the DataSource this runner will use to get database connections from. This should be called after creating a runner with the default constructor if you intend to use the execute methods without passing in a Connection.
      Parameters:
      dataSource - The DataSource to use.

    • update

      public int update(Connection conn, String sql) throws SQLException
      Execute an SQL INSERT, UPDATE, or DELETE query without replacement parameters.
      Parameters:
      conn - The connection to use to run the query.
      sql - The SQL to execute.
      Returns:
      The number of rows updated.
      Throws:
      SQLException - if a database access error occurs

    • update

      public int update(Connection conn, String sql, Object param) throws SQLException
      Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement parameter.
      Parameters:
      conn - The connection to use to run the query.
      sql - The SQL to execute.
      param - The replacement parameter.
      Returns:
      The number of rows updated.
      Throws:
      SQLException - if a database access error occurs

    • update

      public int update(Connection conn, String sql, Object[] params) throws SQLException
      Execute an SQL INSERT, UPDATE, or DELETE query.
      Parameters:
      conn - The connection to use to run the query.
      sql - The SQL to execute.
      params - The query replacement parameters.
      Returns:
      The number of rows updated.
      Throws:
      SQLException - if a database access error occurs

    • update

      public int update(Connection conn, String sql, Object[] params, int[] paramTypes) throws SQLException
      Execute an SQL INSERT, UPDATE, or DELETE query.
      Parameters:
      conn - The connection to use to run the query.
      sql - The SQL to execute.
      params - The query replacement parameters.
      paramTypes - The query replacement parameters.
      Returns:
      The number of rows updated.
      Throws:
      SQLException - if a database access error occurs

    • update

      public int update(String sql) throws SQLException
      Executes the given INSERT, UPDATE, or DELETE SQL statement without any replacement parameters. The Connection is retrieved from the DataSource set in the constructor. This Connection must be in auto-commit mode or the update will not be saved.
      Parameters:
      sql - The SQL statement to execute.
      Returns:
      The number of rows updated.
      Throws:
      SQLException - if a database access error occurs

    • update

      public int update(String sql, Object param) throws SQLException
      Executes the given INSERT, UPDATE, or DELETE SQL statement with a single replacement parameter. The Connection is retrieved from the DataSource set in the constructor. This Connection must be in auto-commit mode or the update will not be saved.
      Parameters:
      sql - The SQL statement to execute.
      param - The replacement parameter.
      Returns:
      The number of rows updated.
      Throws:
      SQLException - if a database access error occurs

    • update

      public int update(String sql, Object[] params) throws SQLException
      Executes the given INSERT, UPDATE, or DELETE SQL statement. The Connection is retrieved from the DataSource set in the constructor. This Connection must be in auto-commit mode or the update will not be saved.
      Parameters:
      sql - The SQL statement to execute.
      params - Initializes the PreparedStatement's IN (i.e. '?') parameters.
      Returns:
      The number of rows updated.
      Throws:
      SQLException - if a database access error occurs

    • wrap

      protected ResultSet wrap(ResultSet rs)
      Wrap the ResultSet in a decorator before processing it. This implementation returns the ResultSet it is given without any decoration.

      Often, the implementation of this method can be done in an anonymous inner class like this: 

       QueryRunner run = new QueryRunner() {
     protected ResultSet wrap(ResultSet rs) {
         return StringTrimmedResultSet.wrap(rs);
     }
 };
      Parameters:
      rs - The ResultSet to decorate; never null.
      Returns:
      The ResultSet wrapped in some decorator.

    • close

      protected void close(Connection conn) throws SQLException
      Close a Connection. This implementation avoids closing if null and does not suppress any exceptions. Subclasses can override to provide special handling like logging.
      Parameters:
      conn - Connection to close
      Throws:
      SQLException - if a database access error occurs
      Since:
      DbUtils 1.1

    • close

      protected void close(Statement stmt) throws SQLException
      Close a Statement. This implementation avoids closing if null and does not suppress any exceptions. Subclasses can override to provide special handling like logging.
      Parameters:
      stmt - Statement to close
      Throws:
      SQLException - if a database access error occurs
      Since:
      DbUtils 1.1

    • close

      protected void close(ResultSet rs) throws SQLException
      Close a ResultSet. This implementation avoids closing if null and does not suppress any exceptions. Subclasses can override to provide special handling like logging.
      Parameters:
      rs - ResultSet to close
      Throws:
      SQLException - if a database access error occurs
      Since:
      DbUtils 1.1