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

Interface SqlPost

All Known Implementing Classes:
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 - 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 - 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 - 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)
      Execute sql script from file used for init db on test startUp
      Parameters:
      path - path to file in resources

    • runScript

      void runScript(String script)
      Execute sql script manual used for manual scripts (not recommended)
      Parameters:
      script - hardcode SQL script

    • truncateTable

      void truncateTable(String tableName)
      Truncate(clean) table
      Parameters:
      tableName - schema_name.table_name

    • truncateTables

      void truncateTables(String... tablesNames)
      Truncate(clean) tables
      Parameters:
      tablesNames - list of tables

    • deleteFromTable

      void deleteFromTable(String tableName)
      Delete all records in table
      Parameters:
      tableName - schema_name.table_name

    • deleteFromTable

      void deleteFromTable(String tableName, Object[][] conditionsArray)
      Delete from table by conditions
      Parameters:
      tableName - schema_name.table_name
      conditionsArray - Object[][] {{"column1", "value1"},...}

    • deleteFromTable

      void deleteFromTable(String tableName, String conditions)
      Delete from table by conditions
      Parameters:
      tableName - schema_name.table_name
      conditions - String "where id=1"

    • updateTable

      void updateTable(String tableName, Object[][] setArray)
      Update all records in table
      Parameters:
      tableName - schema_name.table_name
      setArray - Object[][] {{"column1", "value1"},...}

    • updateTable

      void updateTable(String tableName, Object[][] setArray, Object[][] conditionsArray)
      Update records in table by conditions
      Parameters:
      tableName - schema_name.table_name
      setArray - Object[][] {{"column1", "value1"},...}
      conditionsArray - Object[][] {{"column1", "value1"},...}

    • updateTable

      void updateTable(String tableName, String conditions)
      Update records in table by conditions
      Parameters:
      tableName - schema_name.table_name
      conditions - String "SET USERNAME='Alex' where ID=5"