Package redempt.redlib.sql

Class SQLHelper

java.lang.Object
redempt.redlib.sql.SQLHelper
All Implemented Interfaces:
Closeable, AutoCloseable
public class SQLHelper extends Object implements Closeable
Wraps a Connection and offers helpful methods that don't need to be surrounded in a try/catch

  • Constructor Details

    • SQLHelper

      public SQLHelper(Connection connection)
      Constructs a SQLHelper from a Connection. Get the Connection using one of the static SQLHelper open methods.
      Parameters:
      connection - The SQL Connection to wrap

  • Method Details

    • openSQLite

      public static Connection openSQLite(Path file)
      Opens a SQLite database file
      Parameters:
      file - The path to the SQLite database file
      Returns:
      The Connection to this SQLite database

    • openMySQL

      public static Connection openMySQL(String ip, int port, String username, String password, String database)
      Opens a connection to a MySQL database
      Parameters:
      ip - The IP address to connect to
      port - The port to connect to
      username - The username to log in with
      password - The password to log in with
      database - The database to use, will be created if it doesn't exist
      Returns:
      The Connection to the MySQL database

    • openMySQL

      public static Connection openMySQL(String username, String password, String database)
      Opens a connection to a MySQL database at localhost:3306
      Parameters:
      username - The username to log in with
      password - The password to log in with
      database - The database to use, will be created if it doesn't exist
      Returns:
      The Connection to the MySQL database

    • createCache

      public SQLCache createCache(String tableName, String columnName, String... primaryKeyNames)
      Creates and adds cache for a certain column
      Parameters:
      tableName - The name of the table to create the cache for
      columnName - The name of the column to create the cache for
      primaryKeyNames - The primary keys used to access and mutate the column
      Returns:
      The cache

    • flushMatchingCaches

      public void flushMatchingCaches(String pattern, Object... primaryKeys)
      Finds matching caches by a pattern and flushes a specific entry from them.
      Parameters:
      pattern - The pattern used for getMatchingCaches(String)
      primaryKeys - The primary keys used to access the entry

    • removeFromMatchingCaches

      public void removeFromMatchingCaches(String pattern, Object... primaryKeys)
      Finds matching caches by a pattern and removes a specific entry from them. Useful for saving targeted cached rows when a column in a certain table is changed
      Parameters:
      pattern - The pattern used for getMatchingCaches(String)
      primaryKeys - The primary keys used to access the entry

    • flushAndRemoveFromMatchingCaches

      public void flushAndRemoveFromMatchingCaches(String pattern, Object... primaryKeys)
      Finds matching caches by a pattern and flushes, then removes a specific entry from them.
      Parameters:
      pattern - The pattern used for getMatchingCaches(String)
      primaryKeys - The primary keys used to access the entry

    • getMatchingCaches

      public List<SQLCache> getMatchingCaches(String pattern)
      Gets the caches matching a pattern
      Parameters:
      pattern - The pattern to match. Should be formatted as "tableName.primaryKeyColumnName". Use * to indicate all for either tableName or columnName. Use | to indicate or. Primary key column name matches any primary key with the given column name. Useful if you are updating a value in a table and want to flush/remove targeted values from the cache. Example: *.name|team
      Returns:
      The list of matching caches

    • getCaches

      public List<SQLCache> getCaches()
      Returns:
      The list of caches for this SQLHelper

    • flushAllCaches

      public void flushAllCaches()
      Calls SQLCache.flush() on all caches owned by this SQLHelper

    • clearAllCaches

      public void clearAllCaches()
      Calls SQLCache.clear() on all caches owned by this SQLHelper

    • execute

      public void execute(String command, Object... fields)
      Executes a SQL query as a prepared statement, setting its fields to the elements of the vararg passed
      Parameters:
      command - The SQL command to execute
      fields - A vararg of the fields to set in the prepared statement

    • executeUpdate

      public int executeUpdate(String command, Object... fields)
      Executes a SQL query as a prepared statement, setting its fields to the elements of the vararg passed
      Parameters:
      command - The SQL command to execute
      fields - A vararg of the fields to set in the prepared statement
      Returns:
      The number of updated rows

    • querySingleResult

      public <T> T querySingleResult(String query, Object... fields)
      Executes a SQL query as a prepared statement, setting its fields to the elements of the vararg passed, returning the value in the first column of the first row in the results
      Type Parameters:
      T - The type to cast the return value to
      Parameters:
      query - The SQL query to execute
      fields - A vararg of the fields to set in the prepared statement
      Returns:
      The value in the first column of the first row of the returned results, or null if none is present

    • querySingleResultString

      public String querySingleResultString(String query, Object... fields)
      Executes a SQL query as a prepared statement, setting its fields to the elements of the vararg passed, returning the value in the first column of the first row in the results as a String.
      Parameters:
      query - The SQL query to execute
      fields - A vararg of the fields to set in the prepared statement
      Returns:
      The String in the first column of the first row of the returned results, or null if none is present Note: This method exists because ResultSet.getObject(int) can return an Integer if the String in the column can be parsed into one.

    • querySingleResultBytes

      public byte[] querySingleResultBytes(String query, Object... fields)
      Executes a SQL query as a prepared statement, setting its fields to the elements of the vararg passed, returning the value in the first column of the first row in the results as a Bytes.
      Parameters:
      query - The SQL query to execute
      fields - A vararg of the fields to set in the prepared statement
      Returns:
      The bytes in the first column of the first row of the returned results, or null if none is present

    • querySingleResultLong

      public Long querySingleResultLong(String query, Object... fields)
      Executes a SQL query as a prepared statement, setting its fields to the elements of the vararg passed, returning the value in the first column of the first row in the results as a Long.
      Parameters:
      query - The SQL query to execute
      fields - A vararg of the fields to set in the prepared statement
      Returns:
      The String in the first column of the first row of the returned results, or null if none is present Note: This method exists because ResultSet.getObject(int) can return an Integer if the Long in the column can be parsed into one.

    • queryResultList

      public <T> List<T> queryResultList(String query, Object... fields)
      Executes a SQL query as a prepared statement, setting its fields to the elements of the vararg passed, returning a list of values in the first column of each row in the results
      Type Parameters:
      T - The type to populate the list with and return
      Parameters:
      query - The SQL query to execute
      fields - A vararg of the fields to set in the prepared statement
      Returns:
      A list of the value in the first column of each row returned by the query

    • queryResultStringList

      public List<String> queryResultStringList(String query, Object... fields)
      Executes a SQL query as a prepared statement, setting its fields to the elements of the vararg passed, returning a String list of values in the first column of each row in the results
      Parameters:
      query - The SQL query to execute
      fields - A vararg of the fields to set in the prepared statement
      Returns:
      A String list of the value in the first column of each row returned by the query Note: This method exists because ResultSet.getObject(int) can return an Integer if the String in the column can be parsed into one.

    • queryResults

      public SQLHelper.Results queryResults(String query, Object... fields)
      Executes a SQL query as a prepared statement, setting its fields to the elements of the vararg passed. Returns a SQLHelper.Results, which wraps a ResultSet for easier use
      Parameters:
      query - The SQL query to execute
      fields - A vararg of the fields to set in the prepared statement
      Returns:
      The results of the query

    • getConnection

      public Connection getConnection()
      Returns:
      The Connection this SQLHelper wraps

    • setAutoCommit

      public void setAutoCommit(boolean autoCommit)
      Sets the wrapped connection's auto-commit property. Calling this method will automatically disable the task started by setCommitInterval(int).
      Parameters:
      autoCommit - The auto-commit property - whether it will commit with every command

    • isAutoCommit

      public boolean isAutoCommit()
      Returns:
      The auto-commit property of the wrapped connection

    • setCommitInterval

      public void setCommitInterval(int ticks)
      Starts a task to call commit() on this SQLHelper every n ticks. Pass -1 to disable. Automatically sets autoCommit to false.
      Parameters:
      ticks - The number of ticks between commits, or -1 to disable

    • commit

      public void commit()
      Flushes all caches and commits the transaction

    • prepareStatement

      public PreparedStatement prepareStatement(String query, Object... fields)
      Prepares a statement, setting its fields to the elements of the vararg passed
      Parameters:
      query - The SQL query to prepare
      fields - A vararg of the fields to set in the prepared statement
      Returns:
      The PreparedStatement with its fields set

    • close

      public void close()
      Closes the underlying connection this SQLHelper wraps
      Specified by:
      close in interface AutoCloseable
      Specified by:
      close in interface Closeable