Package net.bugreaper.modules.db.jdbc.interfaces

Interface SqlPost

All Known Implementing Classes:
CustomDb, JdbcExecutor, MariaDb, MsSQL, MySQL, OracleDb, PostgreSQL
public interface SqlPost
Defines operations for executing SQL data-modifying queries.

Implementations of this interface are responsible for performing state-changing SQL operations

This interface must not include any read-only operations. Use SqlGet for SELECT queries.

  • Method Details

    • insertIntoTable

      void insertIntoTable(String tableName, Object[][] templateArray, Object[][] providedArray)
      Merge arrays with data for insert into table

      Wrap this method with table name and template to provide only providedArray

      example: 

       tableName: "schema.table1"
 templateArray {{"id", 2}, {"name", "Alex"}, {"age", 21}, {"fee", "33.05"}}
 providedArray {{"name", "Nikita"}, {"fee", null}}

 result:
 INSERT INTO schema.table1
 (id, name, age, fee)
 VALUES(2, 'Nikita', 21, null)
      Parameters:
      tableName - table name in the format schema_name.table_name
      templateArray - Object[][] with template {{"column1", "value1"},...}
      providedArray - Object[][] with test data for merge {{"column1", "test_value1"},...}
      NOTE: for oracleDb dateTime insert supported 3 elements {"CREATE_DATE", "2020-01-01 10:00:00", "TIMESTAMP"}
      CREATE_DATE = TO_DATE('2020-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),

    • insertIntoTable

      void insertIntoTable(String tableName, Object[][] providedArray)
      Merge arrays with template data

      example:
      tableName: "schema.table1"
      (will search in resources file templates/{database_type}/schema.table1.json)
      database_types : DbTypes
      default path can be changed by JdbcExecutor.setTemplatesDirectory(String)

      {
     "id": 2,
     "name": "Alex",
     "age": 21,
     "fee": 33.05
 }
      providedArray {{"name", "Nikita"}, {"fee", null}}
      result:
      INSERT INTO schema.table1
      (id, name, age, fee)
      VALUES(2, 'Nikita', 21, null)
      NOTE: for oracleDb dateTime insert supported 3 elements in json {"CREATE_DATE:TIMESTAMP", "2020-01-01 10:00:00"}
      CREATE_DATE = TO_DATE('2020-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),
      Parameters:
      tableName - table name in the format schema_name.table_name
      providedArray - Object[][] with test data for merge {{"column1", "test_value1"},...}
      NOTE: for oracleDb dateTime insert supported 3 elements {"CREATE_DATE", "2020-01-01 10:00:00", "TIMESTAMP"}
      CREATE_DATE = TO_DATE('2020-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),

    • insertIntoTable

      void insertIntoTable(String tableName, String providedJson)
      Merge arrays with template data

      example:
      tableName: "schema.table1"
      (will search in resources file templates/${database_type}/schema.table1.json)
      database_types : DbTypes
      default path can be changed by JdbcExecutor.setTemplatesDirectory(String) 

      {
     "id": 2,
     "name": "Alex",
     "age": 21,
     "fee": 33.05
 }
      providedJson: 
      {
     "name": "Nikita",
     "fee": null
 }
      result:
      INSERT INTO schema.table1
      (id, name, age, fee)
      VALUES(2, 'Nikita', 21, null)
      Parameters:
      tableName - table name in the format schema_name.table_name
      providedJson - String with json to merge """{"column1": "test_value1", ...}"""
      NOTE: for oracleDb dateTime insert supported 3 elements in json {"CREATE_DATE:TIMESTAMP", "2020-01-01 10:00:00"}
      CREATE_DATE = TO_DATE('2020-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),

    • runScriptFromFile

      void runScriptFromFile(String path)
      Executes an SQL script from a file.

      Used to initialize the database at test startup or in other cases.

      Parameters:
      path - path to the file in test resources

    • runScript

      void runScript(String script)
      Executes the specified SQL script manually.

      Used for manual scripts and not recommended for regular test operations.

      Parameters:
      script - hardcoded SQL script

    • truncateTable

      void truncateTable(String tableName)
      Truncates the table.
      Parameters:
      tableName - table name in the format schema_name.table_name

    • truncateTables

      void truncateTables(String... tablesNames)
      Truncates the tables.
      Parameters:
      tablesNames - list of tables

    • deleteFromTable

      void deleteFromTable(String tableName)
      Deletes all records from the table.
      Parameters:
      tableName - table name in the format schema_name.table_name

    • deleteFromTable

      void deleteFromTable(String tableName, Object[][] conditionsArray)
      Deletes all records from the table matching the specified conditions (in an Object[][] array).
      Parameters:
      tableName - table name in the format schema_name.table_name
      conditionsArray - Object[][] {{"column1", "value1"},...}

    • deleteFromTable

      void deleteFromTable(String tableName, String conditions)
      Deletes all records from the table matching the specified conditions (hardcoded SQL condition string).
      Parameters:
      tableName - table name in the format schema_name.table_name
      conditions - String "where id=1"

    • updateTable

      void updateTable(String tableName, Object[][] setArray)
      Updates all records in the table.
      Parameters:
      tableName - table name in the format schema_name.table_name
      setArray - values to update in the format {{"column1", "value1"}, ...}

    • updateTable

      void updateTable(String tableName, Object[][] setArray, Object[][] conditionsArray)
      Updates all records in the table matching the specified conditions (in an Object[][] array).
      Parameters:
      tableName - table name in the format schema_name.table_name
      setArray - Object[][] {{"column1", "value1"},...}
      conditionsArray - Object[][] {{"column1", "value1"},...}

    • updateTable

      void updateTable(String tableName, String conditions)
      Updates all records in the table matching the specified conditions (hardcoded SQL condition string).
      Parameters:
      tableName - table name in the format schema_name.table_name
      conditions - String "SET USERNAME='Alex' where ID=5"

    • renameTable

      void renameTable(String tableName)
      Renames the specified table by adding the "_X_" suffix.

      Note: Roll back the table name after the test using rollbackTable(java.lang.String).

      Parameters:
      tableName - table name in the format schema_name.table_name

    • rollbackTable

      void rollbackTable(String tableName)
      Rolls back the table name by removing the "_X_" suffix added by renameTable(java.lang.String).
      Parameters:
      tableName - table name in the format schema_name.table_name