Package com.blackrook.sql

Class SQL

java.lang.Object
com.blackrook.sql.SQL
public final class SQL extends Object
Core database utilities object.
Author:
Matthew Tropiano

  • Method Details

    • getRow

      public static SQLRow getRow(Connection connection, String query, Object... parameters)
      Performs a query on a connection and extracts the data into a single SQLRow.
      Parameters:
      connection - the connection to create a prepared statement and execute from.
      query - the query statement to execute.
      parameters - list of parameters for parameterized queries.
      Returns:
      the single result row returned, or null if no row returned.
      Throws:
      SQLRuntimeException - if the query cannot be executed or the query causes an error.
      See Also:

    • getRow

      public static <T> T getRow(Connection connection, Class<T> type, String query, Object... parameters)
      Performs a query on a connection and creates an object from it from the first row, setting relevant fields.

      Each result row is applied via the target object's public fields and setter methods.

      For instance, if there is a column is a row called "color", its value will be applied via the public field "color" or the setter "setColor()". Public fields take precedence over setters.

      Only certain types are converted without issue. Below is a set of source types and their valid target types:

      Conversion of Types
      Boolean Boolean, all numeric primitives and their autoboxed equivalents, String.
      Number Boolean (zero is false, nonzero is true), all numeric primitives and their autoboxed equivalents, String, Date, Timestamp.
      Timestamp Long (both primitive and object as milliseconds since the Epoch), Timestamp, Date, String
      Date Long (both primitive and object as milliseconds since the Epoch), Timestamp, Date, String
      String Boolean, all numeric primitives and their autoboxed equivalents, String, byte[], char[].
      Clob Boolean, all numeric primitives and their autoboxed equivalents, String, byte[], char[].
      Blob String, byte[], char[].
      NClob Boolean, all numeric primitives and their autoboxed equivalents, String, byte[], char[].
      byte[] String, byte[], char[].
      char[] Boolean, all numeric primitives and their autoboxed equivalents, String, byte[], char[].
      Type Parameters:
      T - the returned data type.
      Parameters:
      connection - the connection to create a prepared statement and execute from.
      type - the class type to instantiate.
      query - the query statement to execute.
      parameters - list of parameters for parameterized queries.
      Returns:
      an instantiated object with the pertinent fields set, or null if no rows.
      Throws:
      SQLRuntimeException - if the query cannot be executed or the query causes an error.
      ClassCastException - if one object type cannot be converted to another.

    • getResult

      public static SQLResult getResult(Connection connection, String query, Object... parameters)
      Performs a query on a connection and extracts the data into a SQLResult.
      Parameters:
      connection - the connection to create a prepared statement and execute from.
      query - the query statement to execute.
      parameters - list of parameters for parameterized queries.
      Returns:
      the result of the query.
      Throws:
      SQLRuntimeException - if the query cannot be executed or the query causes an error.

    • getResult

      public static <T> T[] getResult(Connection connection, Class<T> type, String query, Object... parameters)
      Performs a query on a connection and creates objects from it, setting relevant fields.

      Each result row is applied via the target object's public fields and setter methods.

      For instance, if there is a column is a row called "color", its value will be applied via the public field "color" or the setter "setColor()". Public fields take precedence over setters.

      Only certain types are converted without issue. Below is a set of source types and their valid target types:

      Conversion of Types
      Boolean Boolean, all numeric primitives and their autoboxed equivalents, String.
      Number Boolean (zero is false, nonzero is true), all numeric primitives and their autoboxed equivalents, String, Date, Timestamp.
      Timestamp Long (both primitive and object as milliseconds since the Epoch), Timestamp, Date, String
      Date Long (both primitive and object as milliseconds since the Epoch), Timestamp, Date, String
      String Boolean, all numeric primitives and their autoboxed equivalents, String, byte[], char[].
      Clob Boolean, all numeric primitives and their autoboxed equivalents, String, byte[], char[].
      Blob String, byte[], char[].
      NClob Boolean, all numeric primitives and their autoboxed equivalents, String, byte[], char[].
      byte[] String, byte[], char[].
      char[] Boolean, all numeric primitives and their autoboxed equivalents, String, byte[], char[].
      Type Parameters:
      T - the returned data type.
      Parameters:
      connection - the connection to create a prepared statement and execute from.
      type - the class type to instantiate.
      query - the query statement to execute.
      parameters - list of parameters for parameterized queries.
      Returns:
      an array of instantiated objects with the pertinent fields set for each row.
      Throws:
      SQLRuntimeException - if the query cannot be executed or the query causes an error.
      ClassCastException - if one object type cannot be converted to another.

    • getUpdateResult

      public static SQLResult getUpdateResult(Connection connection, String query, Object... parameters)
      Performs an update query (INSERT, DELETE, UPDATE, or other commands that do not return rows) on a connection and extracts the data/affected data/generated data into a SQLResult.
      Parameters:
      connection - the connection to create a prepared statement and execute from.
      query - the query statement to execute.
      parameters - list of parameters for parameterized queries.
      Returns:
      the update result returned (usually number of rows affected and or generated ids).
      Throws:
      SQLRuntimeException - if the query cannot be executed or the query causes an error.

    • getUpdateBatch

      public static int[] getUpdateBatch(Connection connection, String query, Object[][] parameterList)
      Performs a series of update queries on a single statement on a connection and returns the batch result.
      Parameters:
      connection - the connection to create a prepared statement and execute from.
      query - the query statement to execute.
      parameterList - the list of parameter sets to pass to the query for each update.
      Returns:
      the update result returned (usually number of rows affected and or generated ids).
      Throws:
      SQLRuntimeException - if the query cannot be executed or the query causes an error.
      UnsupportedOperationException - if not implemented by the driver.
      Since:
      1.1.0, 1.2.0, returns int[]. See getUpdateLargeBatch(Connection, String, Object[][]) for long[].

    • getUpdateBatch

      public static int[] getUpdateBatch(Connection connection, String query, int granularity, Object[][] parameterList)
      Performs a series of update queries on a single statement on a connection and returns the batch result.
      Parameters:
      connection - the connection to create a prepared statement and execute from.
      query - the query statement to execute.
      granularity - the amount of statements to execute at a time. If 0 or less, no granularity.
      parameterList - the list of parameter sets to pass to the query for each update.
      Returns:
      the update result returned (usually number of rows affected and or generated ids).
      Throws:
      SQLRuntimeException - if the query cannot be executed or the query causes an error.
      UnsupportedOperationException - if not implemented by the driver.
      Since:
      1.1.0, 1.2.0, returns int[]. See getUpdateLargeBatch(Connection, String, int, Object[][]) for long[].

    • getUpdateBatch

      public static int[] getUpdateBatch(Connection connection, String query, Collection<Object[]> parameterList)
      Performs a series of update queries on a single statement on a connection and returns the batch result.
      Parameters:
      connection - the connection to create a prepared statement and execute from.
      query - the query statement to execute.
      parameterList - the list of parameter sets to pass to the query for each update.
      Returns:
      the update result returned (usually number of rows affected and or generated ids).
      Throws:
      SQLRuntimeException - if the query cannot be executed or the query causes an error.
      UnsupportedOperationException - if not implemented by the driver.
      Since:
      1.1.0, 1.2.0, returns int[]. See getUpdateLargeBatch(Connection, String, int, Collection) for long[].

    • getUpdateBatch

      public static int[] getUpdateBatch(Connection connection, String query, int granularity, Collection<Object[]> parameterList)
      Performs a series of update queries on a single statement on a connection and returns the batch result.
      Parameters:
      connection - the connection to create a prepared statement and execute from.
      query - the query statement to execute.
      granularity - the amount of statements to execute at a time. If 0 or less, no granularity.
      parameterList - the list of parameter sets to pass to the query for each update.
      Returns:
      the update result returned (usually number of rows affected and or generated ids).
      Throws:
      SQLRuntimeException - if the query cannot be executed or the query causes an error.
      UnsupportedOperationException - if not implemented by the driver.
      Since:
      1.1.0, 1.2.0, returns int[]. See getUpdateLargeBatch(Connection, String, int, Collection) for long[].

    • getUpdateLargeBatch

      public static long[] getUpdateLargeBatch(Connection connection, String query, Object[][] parameterList)
      Performs a series of update queries on a single statement on a connection and returns the batch result.
      Parameters:
      connection - the connection to create a prepared statement and execute from.
      query - the query statement to execute.
      parameterList - the list of parameter sets to pass to the query for each update.
      Returns:
      the update result returned (usually number of rows affected and or generated ids).
      Throws:
      SQLRuntimeException - if the query cannot be executed or the query causes an error.
      UnsupportedOperationException - if not implemented by the driver.
      Since:
      1.2.0

    • getUpdateLargeBatch

      public static long[] getUpdateLargeBatch(Connection connection, String query, int granularity, Object[][] parameterList)
      Performs a series of update queries on a single statement on a connection and returns the batch result.
      Parameters:
      connection - the connection to create a prepared statement and execute from.
      query - the query statement to execute.
      granularity - the amount of statements to execute at a time. If 0 or less, no granularity.
      parameterList - the list of parameter sets to pass to the query for each update.
      Returns:
      the update result returned (usually number of rows affected and or generated ids).
      Throws:
      SQLRuntimeException - if the query cannot be executed or the query causes an error.
      UnsupportedOperationException - if not implemented by the driver.
      Since:
      1.2.0

    • getUpdateLargeBatch

      public static long[] getUpdateLargeBatch(Connection connection, String query, Collection<Object[]> parameterList)
      Performs a series of update queries on a single statement on a connection and returns the batch result.
      Parameters:
      connection - the connection to create a prepared statement and execute from.
      query - the query statement to execute.
      parameterList - the list of parameter sets to pass to the query for each update.
      Returns:
      the update result returned (usually number of rows affected and or generated ids).
      Throws:
      SQLRuntimeException - if the query cannot be executed or the query causes an error.
      UnsupportedOperationException - if not implemented by the driver.
      Since:
      1.2.0

    • getUpdateLargeBatch

      public static long[] getUpdateLargeBatch(Connection connection, String query, int granularity, Collection<Object[]> parameterList)
      Performs a series of update queries on a single statement on a connection and returns the batch result.
      Parameters:
      connection - the connection to create a prepared statement and execute from.
      query - the query statement to execute.
      granularity - the amount of statements to execute at a time. If 0 or less, no granularity.
      parameterList - the list of parameter sets to pass to the query for each update.
      Returns:
      the update result returned (usually number of rows affected and or generated ids).
      Throws:
      SQLRuntimeException - if the query cannot be executed or the query causes an error.
      UnsupportedOperationException - if not implemented by the driver.
      Since:
      1.2.0

    • getUpdateBatchResult

      public static SQLResult[] getUpdateBatchResult(Connection connection, String query, Object[][] parameterList)
      Performs an update query (INSERT, DELETE, UPDATE, or other commands that do not return rows) and extracts each set of result data into a SQLResult.

      This is usually more efficient than multiple calls of getUpdateResult(Connection, String, Object...), since it uses the same prepared statement. However, it is not as efficient as getUpdateBatch(Connection, String, int, Collection), but for this method, you will get the generated ids in each result, if any.

      Parameters:
      connection - the connection to create a prepared statement and execute from.
      query - the query statement to execute.
      parameterList - the list of parameter sets to pass to the query for each update.
      Returns:
      the list of update results returned, each corresponding to an update.
      Throws:
      SQLRuntimeException - if the query cannot be executed or the query causes an error.
      Since:
      1.1.0

    • getUpdateBatchResult

      public static SQLResult[] getUpdateBatchResult(Connection connection, String query, Collection<Object[]> parameterList)
      Performs an update query (INSERT, DELETE, UPDATE, or other commands that do not return rows) and extracts each set of result data into a SQLResult.

      This is usually more efficient than multiple calls of getUpdateResult(Connection, String, Object...), since it uses the same prepared statement. However, it is not as efficient as getUpdateBatch(Connection, String, int, Collection), but for this method, you will get the generated ids in each result, if any.

      Parameters:
      connection - the connection to create a prepared statement and execute from.
      query - the query statement to execute.
      parameterList - the list of parameter sets to pass to the query for each update.
      Returns:
      the list of update results returned, each corresponding to an update.
      Throws:
      SQLRuntimeException - if the query cannot be executed or the query causes an error.
      Since:
      1.1.0

    • getTransaction

      public static SQLConnection.Transaction getTransaction(Connection connection, SQLConnection.TransactionLevel transactionLevel) throws SQLException
      Starts a transaction with a provided level.

      The connection gets Connection.setAutoCommit(boolean) called on it with a FALSE parameter, and sets the transaction isolation level. These settings are restored when the transaction is finished via SQLConnection.Transaction.close(), SQLConnection.Transaction.commit(), or SQLConnection.Transaction.abort(). It is recommended to use an auto-closing mechanism to ensure that the transaction is completed and the connection transaction state is restored, or else this connection will be left in a bad state!

      Parameters:
      connection - the connection to create a prepared statement and execute from.
      transactionLevel - the transaction level to set on this transaction.
      Returns:
      a new transaction.
      Throws:
      SQLException - if this transaction could not be prepared.
      Since:
      1.1.0

    • getTransactionAnd

      public static void getTransactionAnd(Connection connection, SQLConnection.TransactionLevel transactionLevel, SQLTransactionConsumer handler)
      Starts a transaction with a provided level, performs actions on it, then auto-closes it.

      The connection gets Connection.setAutoCommit(boolean) called on it with a FALSE parameter, and sets the transaction isolation level. These settings are restored when the transaction is finished via SQLConnection.Transaction.close(), SQLConnection.Transaction.commit(), or SQLConnection.Transaction.abort(). It is recommended to use an auto-closing mechanism to ensure that the transaction is completed and the connection transaction state is restored.

      Parameters:
      connection - the connection to create a prepared statement and execute from.
      transactionLevel - the transaction level to set on this transaction.
      handler - the consumer function that accepts the retrieved connection and returns a value.
      Since:
      1.1.0

    • getTransactionAnd

      public static <R> R getTransactionAnd(Connection connection, SQLConnection.TransactionLevel transactionLevel, SQLTransactionFunction<R> handler)
      Starts a transaction with a provided level, performs actions on it, returns a value, then auto-closes it.

      The connection gets Connection.setAutoCommit(boolean) called on it with a FALSE parameter, and sets the transaction isolation level. These settings are restored when the transaction is finished via SQLConnection.Transaction.close(), SQLConnection.Transaction.commit(), or SQLConnection.Transaction.abort(). It is recommended to use an auto-closing mechanism to ensure that the transaction is completed and the connection transaction state is restored.

      Type Parameters:
      R - the return type.
      Parameters:
      connection - the connection to create a prepared statement and execute from.
      transactionLevel - the transaction level to set on this transaction.
      handler - the consumer function that accepts the retrieved connection and returns a value.
      Returns:
      the return value of the handler function.
      Since:
      1.1.0

    • getAllColumnNamesFromResultSet

      public static String[] getAllColumnNamesFromResultSet(ResultSet set) throws SQLException
      Returns the names of the columns in a ResultSet in the order that they appear in the result.
      Parameters:
      set - the ResultSet to get the columns from.
      Returns:
      an array of all of the columns.
      Throws:
      SQLException - if something goes wrong.

    • createObjectsFromResultSet

      public static <T> T[] createObjectsFromResultSet(Class<T> objectType, ResultSet resultSet) throws SQLException
      Creates new objects from the result set and sets the fields on them using row information. The column names are pulled from the ResultSet metadata.
      Type Parameters:
      T - the returned data type.
      Parameters:
      objectType - the object type to instantiate.
      resultSet - the result set.
      Returns:
      a new object with the relevant fields set.
      Throws:
      SQLException - if a SQL exception occurs.
      ClassCastException - if any incoming types cannot be converted.
      Since:
      1.1.0

    • createObjectFromResultRow

      public static <T> T createObjectFromResultRow(Class<T> objectType, ResultSet resultSet) throws SQLException
      Creates a new object from the current result row and sets the fields on it using row information. The column names are pulled from the metadata.
      Type Parameters:
      T - the returned data type.
      Parameters:
      objectType - the object type to instantiate.
      resultSet - the result set.
      Returns:
      a new object with the relevant fields set.
      Throws:
      SQLException - if a SQL exception occurs.
      ClassCastException - if any incoming types cannot be converted.
      Since:
      1.1.0

    • createObjectFromResultRow

      public static <T> T createObjectFromResultRow(Class<T> objectType, ResultSet resultSet, String[] columnNames) throws SQLException
      Creates a new object from the current result row and sets the fields on it using row information.
      Type Parameters:
      T - the returned data type.
      Parameters:
      objectType - the object type to instantiate.
      resultSet - the result set.
      columnNames - the names of the columns in the set.
      Returns:
      a new object with the relevant fields set.
      Throws:
      ArrayIndexOutOfBoundsException - if the length of columnNames is less than the amount of columns in the set.
      SQLException - if a SQL exception occurs.
      ClassCastException - if any incoming types cannot be converted.
      Since:
      1.1.0, exposed as public.

    • callStatement

      public static SQLResult callStatement(PreparedStatement statement, boolean update, Object... parameters) throws SQLException
      Performs a query on a connection and extracts the data into a SQLResult object.
      Parameters:
      statement - the statement to execute.
      update - if true, this is an update query. If false, it is a result query.
      parameters - the parameters to pass to the
      Returns:
      the query result returned.
      Throws:
      SQLException - if a SQL exception occurs.

    • callBatch

      public static int[] callBatch(PreparedStatement statement, Object[][] parameterList) throws SQLException
      Performs a series of update queries on a single statement on a connection and returns the batch result, using a default batching amount (1024).
      Parameters:
      statement - the statement to execute.
      parameterList - the list of parameter sets to pass to the query for each update.
      Returns:
      the amount of affected rows of each of the updates, each index corresponding to the index of the set of parameters used. May also return Statement.SUCCESS_NO_INFO or Statement.EXECUTE_FAILED per update.
      Throws:
      SQLException - if a SQL exception occurs.
      UnsupportedOperationException - if not implemented by the driver.
      Since:
      1.1.0, 1.2.0, returns int[]. See callLargeBatch(PreparedStatement, Object[][]) for long[].

    • callBatch

      public static int[] callBatch(PreparedStatement statement, int granularity, Object[][] parameterList) throws SQLException
      Performs a series of update queries on a single statement on a connection and returns the batch result.
      Parameters:
      statement - the statement to execute.
      granularity - the amount of statements to execute at a time. If 0 or less, no granularity.
      parameterList - the list of parameter sets to pass to the query for each update.
      Returns:
      the amount of affected rows of each of the updates, each index corresponding to the index of the set of parameters used. May also return Statement.SUCCESS_NO_INFO or Statement.EXECUTE_FAILED per update.
      Throws:
      SQLException - if a SQL exception occurs.
      UnsupportedOperationException - if not implemented by the driver.
      Since:
      1.1.0, 1.2.0, returns int[]. See callLargeBatch(PreparedStatement, int, Object[][]) for long[].

    • callBatch

      public static int[] callBatch(PreparedStatement statement, Collection<Object[]> parameterList) throws SQLException
      Performs a series of update queries on a single statement on a connection and returns the batch result, using a default batching amount (1024).
      Parameters:
      statement - the statement to execute.
      parameterList - the list of parameter sets to pass to the query for each update.
      Returns:
      the amount of affected rows of each of the updates, each index corresponding to the index of the set of parameters used. May also return Statement.SUCCESS_NO_INFO or Statement.EXECUTE_FAILED per update.
      Throws:
      SQLException - if a SQL exception occurs.
      UnsupportedOperationException - if not implemented by the driver.
      Since:
      1.1.0, 1.2.0, returns int[]. See callLargeBatch(PreparedStatement, Collection) for long[].

    • callBatch

      public static int[] callBatch(PreparedStatement statement, int granularity, Collection<Object[]> parameterList) throws SQLException
      Performs a series of update queries on a single statement on a connection and returns the batch result.
      Parameters:
      statement - the statement to execute.
      granularity - the amount of statements to execute at a time. If 0 or less, no granularity.
      parameterList - the list of parameter sets to pass to the query for each update.
      Returns:
      the amount of affected rows of each of the updates, each index corresponding to the index of the set of parameters used. May also return Statement.SUCCESS_NO_INFO or Statement.EXECUTE_FAILED per update.
      Throws:
      SQLException - if a SQL exception occurs.
      UnsupportedOperationException - if not implemented by the driver.
      Since:
      1.1.0, 1.2.0, returns int[]. See callLargeBatch(PreparedStatement, int, Collection) for long[].

    • callLargeBatch

      public static long[] callLargeBatch(PreparedStatement statement, Object[][] parameterList) throws SQLException
      Performs a series of update queries on a single statement on a connection and returns the batch result, using a default batching amount (1024).
      Parameters:
      statement - the statement to execute.
      parameterList - the list of parameter sets to pass to the query for each update.
      Returns:
      the amount of affected rows of each of the updates, each index corresponding to the index of the set of parameters used. May also return Statement.SUCCESS_NO_INFO or Statement.EXECUTE_FAILED per update.
      Throws:
      SQLException - if a SQL exception occurs.
      UnsupportedOperationException - if not implemented by the driver.
      Since:
      1.2.0

    • callLargeBatch

      public static long[] callLargeBatch(PreparedStatement statement, int granularity, Object[][] parameterList) throws SQLException
      Performs a series of update queries on a single statement on a connection and returns the batch result.
      Parameters:
      statement - the statement to execute.
      granularity - the amount of statements to execute at a time. If 0 or less, no granularity.
      parameterList - the list of parameter sets to pass to the query for each update.
      Returns:
      the amount of affected rows of each of the updates, each index corresponding to the index of the set of parameters used. May also return Statement.SUCCESS_NO_INFO or Statement.EXECUTE_FAILED per update.
      Throws:
      SQLException - if a SQL exception occurs.
      UnsupportedOperationException - if not implemented by the driver.
      Since:
      1.2.0

    • callLargeBatch

      public static long[] callLargeBatch(PreparedStatement statement, Collection<Object[]> parameterList) throws SQLException
      Performs a series of update queries on a single statement on a connection and returns the batch result, using a default batching amount (1024).
      Parameters:
      statement - the statement to execute.
      parameterList - the list of parameter sets to pass to the query for each update.
      Returns:
      the amount of affected rows of each of the updates, each index corresponding to the index of the set of parameters used. May also return Statement.SUCCESS_NO_INFO or Statement.EXECUTE_FAILED per update.
      Throws:
      SQLException - if a SQL exception occurs.
      UnsupportedOperationException - if not implemented by the driver.
      Since:
      1.2.0

    • callLargeBatch

      public static long[] callLargeBatch(PreparedStatement statement, int granularity, Collection<Object[]> parameterList) throws SQLException
      Performs a series of update queries on a single statement on a connection and returns the batch result.
      Parameters:
      statement - the statement to execute.
      granularity - the amount of statements to execute at a time. If 0 or less, no granularity.
      parameterList - the list of parameter sets to pass to the query for each update.
      Returns:
      the amount of affected rows of each of the updates, each index corresponding to the index of the set of parameters used. May also return Statement.SUCCESS_NO_INFO or Statement.EXECUTE_FAILED per update.
      Throws:
      SQLException - if a SQL exception occurs.
      UnsupportedOperationException - if not implemented by the driver.
      Since:
      1.2.0