Package com.marcpg.libpg.data.database.sql

Class SQLConnection<T>

java.lang.Object
com.marcpg.libpg.data.database.sql.SQLConnection<T>
Type Parameters:
T - The primary key's type.
Direct Known Subclasses:
AutoCatchingSQLConnection
public class SQLConnection<T> extends Object
Represents a connection to a database with built-in features for accessing the database with proper values with primary keys, etc.
This database connection is meant to be used with databases that follow the simple scheme of having a the type <T> as the first column and set to be the primary-key.
Since:
0.0.6

  • Constructor Details

    • SQLConnection

      public SQLConnection(@NotNull @NotNull SQLConnection.DatabaseType type, String url, String username, String password, String table, String primaryKeyName) throws SQLException, ClassNotFoundException
      Creates a connection to a SQL compatible database.
      Parameters:
      type - The database type.
      url - The URL to the database (jdbc:type://ip:port/name)
      username - Username of the account to access the database with.
      password - Password of the account to access the database with.
      table - The table in the database that will accessed. Can be changed later on using changeTable(String).
      primaryKeyName - The primary key's name.
      Throws:
      SQLException - if the connection wasn't successful, which is likely due to a wrong URL or wrong credentials.
      ClassNotFoundException - if the dependency of the database type is missing.
      See Also:

    • SQLConnection

      public SQLConnection(SQLConnection.DatabaseType type, String ip, int port, String databaseName, String username, String password, String table, String primaryKeyName) throws SQLException, ClassNotFoundException
      Creates a connection to a SQL compatible database.
      Parameters:
      type - The database type.
      ip - The IP of the database. Can be localhost.
      port - The port that the database runs on. If set to 0, will use the database type's default port.
      databaseName - The database to name, this should connect to.
      username - Username of the account to access the database with.
      password - Password of the account to access the database with.
      table - The table in the database that will accessed. Can be changed later on using changeTable(String).
      primaryKeyName - The primary key's name.
      Throws:
      SQLException - if the connection wasn't successful, which is likely due to a wrong URL or wrong credentials.
      ClassNotFoundException - if the dependency of the database type is missing.
      See Also:

  • Method Details

    • closeConnection

      public void closeConnection() throws SQLException
      Closes the connection to the database. Recommended to use this before shutting down the program, to not cause any connection issues with the database.
      Throws:
      SQLException - if there was an issue while closing the connection or the connection is already closed.

    • connection

      public Connection connection()
      Get the current connection that's being used. No actual use.
      Returns:
      The currently used connection to the database.

    • createNewStatement

      public void createNewStatement() throws SQLException
      Generates a new statement to use for queries. Can be useful for clearing some memory.
      Throws:
      SQLException - if there was an error while creating the new statement.

    • table

      public String table()
      Get the current table to get data from.
      Returns:
      The table that's currently being used.
      See Also:

    • changeTable

      public void changeTable(String table)
      Changes what table to get data from and execute all queries on.
      Parameters:
      table - The new table to get data from.
      See Also:

    • primaryKeyName

      public String primaryKeyName()
      Get the primary key's name.
      Returns:
      The table's primary key's name.

    • executeQuery

      public <T2> T2 executeQuery(String sql, Object @NotNull ... params) throws SQLException
      Executes a parameterized SQL query and returns the result. When the result has multiple values, it will choose the first column index.
      Type Parameters:
      T2 - The type of the result to be returned.
      Parameters:
      sql - The parameterized query to be executed.
      params - The parameters that should be set into the sql query input.
      Returns:
      The result of the query execution, or null if it's empty.
      Throws:
      SQLException - if there was an error while executing the query.

    • executeQuery

      @NotNull public <T2> T2 executeQuery(T2 def, String sql, Object @NotNull ... params)
      Executes a parameterized SQL query and returns the result, with a default value.
      Type Parameters:
      T2 - The type of the result to be returned.
      Parameters:
      def - The default value if an error occurred or the result is null.
      sql - The parameterized query to be executed.
      params - The parameters that should be set into the sql query input.
      Returns:
      The result of the query execution, or the default if it's empty.
      See Also:

    • getRowArray

      public Object[] getRowArray(T primaryKey) throws SQLException
      Get a whole row from the table based on the primary key.
      Parameters:
      primaryKey - The primary key to get the row from.
      Returns:
      All objects of the row as an array.
      Throws:
      SQLException - if there was an error while executing the query.

    • getRowArray

      @NotNull public @NotNull Object[] getRowArray(T primaryKey, @NotNull @NotNull Object[] def)
      Get a whole row from the table based on the primary key, with a default value.
      Parameters:
      def - The default value if an error occurred or the result is null.
      primaryKey - The primary key to get the row from.
      Returns:
      All objects of the row as an array.
      See Also:

    • getRowMap

      public Map<String,Object> getRowMap(T primaryKey) throws SQLException
      Get a whole row with column names and values from the table based on the primary key.
      Parameters:
      primaryKey - The primary key to get the row from.
      Returns:
      All objects of the row as a map.
      Throws:
      SQLException - if there was an error while executing the query.

    • getRowMap

      @NotNull public @NotNull Object[] getRowMap(T primaryKey, @NotNull @NotNull Object[] def)
      Get a whole row with column names and values from the table based on the primary key, with a default value.
      Parameters:
      def - The default value if an error occurred or the result is null.
      primaryKey - The primary key to get the row from.
      Returns:
      All objects of the row as a map.
      See Also:

    • getRow

      public ResultSet getRow(T primaryKey) throws SQLException
      Get the result of getting a row based on the primary key.
      Parameters:
      primaryKey - The primary key to get the row from.
      Returns:
      The result of the query.
      Throws:
      SQLException - if there was an error while executing the query.

    • getRow

      public ResultSet getRow(T primaryKey, ResultSet def)
      Get the result of getting a row based on the primary key, with a default value.
      Parameters:
      def - The default value if an error occurred or the result is null.
      primaryKey - The primary key to get the row from.
      Returns:
      The result of the query.
      See Also:

    • get

      public Object get(T primaryKey, String column) throws SQLException
      Get a specified column, based on the primary key and the column name.
      Parameters:
      primaryKey - The primary key to get the value from.
      column - The column/value's name.
      Returns:
      The value in the specified field.
      Throws:
      SQLException - if there was an error while executing the query.

    • get

      @NotNull public @NotNull Object get(T primaryKey, String column, Object def)
      Get a specified column, with a default value.
      Parameters:
      def - The default value if an error occurred or the result is null.
      primaryKey - The primary key to get the value from.
      column - The column/value's name.
      Returns:
      The value in the specified field.
      See Also:

    • get

      public Object get(T primaryKey, int column) throws SQLException
      Get a specified column, based on the primary key and the column index.
      Parameters:
      primaryKey - The primary key to get the value from.
      column - The column/value's index (starting at 1).
      Returns:
      The value in the specified field.
      Throws:
      SQLException - if there was an error while executing the query.

    • get

      @NotNull public @NotNull Object get(T primaryKey, int column, Object def)
      Get a specified column, with a default value.
      Parameters:
      def - The default value if an error occurred or the result is null.
      primaryKey - The primary key to get the value from.
      column - The column/value's index (starting at 1).
      Returns:
      The value in the specified field.
      See Also:

    • set

      public void set(T primaryKey, String column, Object newValue) throws SQLException
      Set a value of a specified row to a new value, based on the primary key and the column name.
      Parameters:
      primaryKey - The primary key to get the value from.
      column - The column/value's name.
      newValue - The new value for the specified field.
      Throws:
      SQLException - if there was an error while executing the query.

    • set

      public void set(T primaryKey, int column, Object newValue) throws SQLException
      Set a value of a specified row to a new value, based on the primary key and the column index.
      Parameters:
      primaryKey - The primary key to get the value from.
      column - The column/value's index (starting at 1).
      newValue - The new value for the specified field.
      Throws:
      SQLException - if there was an error while executing the query.

    • add

      public void add(@NotNull @NotNull Map<String,Object> values) throws SQLException
      Inserts a new row with the specified values into the table.
      Parameters:
      values - All values for the new row. The keys are the column names and the values are the values. Has to be valid according to database settings, otherwise throws an Exception.
      Throws:
      SQLException - if there was an error while executing the query. Can be caused by invalid values.

    • remove

      public void remove(T primaryKey) throws SQLException
      Removed a row based on the primary key.
      Parameters:
      primaryKey - The primary key to remove.
      Throws:
      SQLException - if there was an error while executing the query. Can be caused by removing a non-existent row.

    • contains

      public boolean contains(T primaryKey) throws SQLException
      Checks whether the table contains an entry/row with the primary key or not.
      Parameters:
      primaryKey - The primary key to check for.
      Returns:
      true if the table contains the primary key, false otherwise.
      Throws:
      SQLException - if there was an error while executing the query.

    • getAllRowArrays

      public List<Object[]> getAllRowArrays() throws SQLException
      Get all rows of the table, where the list represents the rows and the arrays are each row's columns.
      Returns:
      A list containing all columns as an array.
      Throws:
      SQLException - if there was an error while executing the query.

    • getAllRowArrays

      @NotNull public @NotNull List<Object[]> getAllRowArrays(List<Object[]> def)
      Get all rows of the table, with a default value.
      Parameters:
      def - The default value if an error occurred or the result is null.
      Returns:
      A list containing all columns as an array.
      See Also:

    • getAllRowMaps

      public List<Map<String,Object>> getAllRowMaps() throws SQLException
      Get all rows of the table, where the list represents the rows and the maps are each row's columns with their name first and then their value.
      Returns:
      A map containing all columns with their names and values.
      Throws:
      SQLException - if there was an error while executing the query.

    • getAllRowMaps

      @NotNull public @NotNull List<Map<String,Object>> getAllRowMaps(List<Map<String,Object>> def)
      Get all rows of the table, with a default value.
      Parameters:
      def - The default value if an error occurred or the result is null.
      Returns:
      A map containing all columns with their names and values.
      See Also:

    • getRowArraysContaining

      public List<Object[]> getRowArraysContaining(Object object, String... checkedColumns) throws SQLException
      Get all rows of the table containing a specific value in any specified column as a List of arrays, where the list represents the rows and the arrays are each row's columns.
      Parameters:
      object - What object must be contained in at least one of the rows.
      checkedColumns - All columns that are checked for the object.
      Returns:
      A list containing all columns as an array.
      Throws:
      SQLException - if there was an error while executing the query.

    • getRowArraysContaining

      @NotNull public @NotNull List<Object[]> getRowArraysContaining(List<Object[]> def, Object object, String... checkedColumns)
      Get all rows of the table containing something, with a default value.
      Parameters:
      def - The default value if an error occurred or the result is null.
      object - What object must be contained in at least one of the rows.
      checkedColumns - All columns that are checked for the object.
      Returns:
      A list containing all columns as an array.
      See Also:

    • getRowMapsContaining

      public List<Map<String,Object>> getRowMapsContaining(Object object, String... checkedColumns) throws SQLException
      Get all rows of the table containing a specific value in any specified column as a List of maps, where the list represents the rows and the maps are each row's columns with their name first and then their value.
      Parameters:
      object - What object must be contained in at least one of the rows.
      checkedColumns - All columns that are checked for the object.
      Returns:
      A map containing all columns with their names and values.
      Throws:
      SQLException - if there was an error while executing the query.

    • getRowMapsContaining

      @NotNull public @NotNull List<Map<String,Object>> getRowMapsContaining(List<Map<String,Object>> def, Object object, String... checkedColumns)
      Get all rows of the table containing something, with a default value.
      Parameters:
      def - The default value if an error occurred or the result is null.
      object - What object must be contained in at least one of the rows.
      checkedColumns - All columns that are checked for the object.
      Returns:
      A map containing all columns with their names and values.
      See Also:

    • getRowArraysMatching

      public Collection<Object[]> getRowArraysMatching(String wherePredicate, Object @NotNull ... replacements) throws SQLException
      Get all rows matching a specified WHERE predicate as a Collection of arrays, where the list represents the rows and the arrays are each row's columns with their name first and then their value.
      Parameters:
      wherePredicate - The SQL WHERE predicate, like column = ? AND another_column = ?. This is inserted right after the WHERE.
      replacements - What objects the question marks should be replaced with.
      Returns:
      All rows that match the WHERE predicate as arrays.
      Throws:
      SQLException - if there was an error while executing the query or the WHERE predicate was invalid.

    • getRowArraysMatching

      @NotNull public @NotNull Collection<Object[]> getRowArraysMatching(Collection<Object[]> def, String wherePredicate, Object @NotNull ... replacements)
      Get all rows matching a predicate, with a default value.
      Parameters:
      def - The default value if an error occurred or the result is null.
      wherePredicate - The SQL WHERE predicate, like column = ? AND another_column = ?. This is inserted right after the WHERE.
      replacements - What objects the question marks should be replaced with.
      Returns:
      All rows that match the WHERE predicate as arrays.
      See Also:

    • getRowMapsMatching

      public Collection<Map<String,Object>> getRowMapsMatching(String wherePredicate, Object @NotNull ... replacements) throws SQLException
      Get all rows matching a specified WHERE predicate as a Collection of arrays, where the collection represents the rows and the arrays are each row's columns with their name first and then their value.
      Parameters:
      wherePredicate - The SQL WHERE predicate, like column = ? AND another_column = ?. This is inserted right after the WHERE.
      replacements - What objects the question marks should be replaced with.
      Returns:
      All rows that match the WHERE predicate as arrays.
      Throws:
      SQLException - if there was an error while executing the query or the WHERE predicate was invalid.

    • getRowMapsMatching

      @NotNull public @NotNull Collection<Map<String,Object>> getRowMapsMatching(Collection<Map<String,Object>> def, String wherePredicate, Object @NotNull ... replacements)
      Get all rows matching a predicate, with a default value.
      Parameters:
      def - The default value if an error occurred or the result is null.
      wherePredicate - The SQL WHERE predicate, like column = ? AND another_column = ?. This is inserted right after the WHERE.
      replacements - What objects the question marks should be replaced with.
      Returns:
      All rows that match the WHERE predicate as arrays.
      See Also: