Description

The MySQLInsertReceiver is used to insert a Domain's generated data directly into a MySQL database table via a JDBC connection. This Receiver is similar to the GenericSQLInsertReceiver, except for some features that are specific to a MySQL database. To insert data directly into a database table that is not a MySQL database, use the GenericInsertSQLReceiver.  


MySQL-Specific Functionality

Specifically, when the option to truncate a table, before populating the table is set to true, then the following SQL statements are executed. Specifically, lines 4 and 8 are unique to the MySQL database engine. 


private truncateTable() {
    if (truncate) {
      try {
        PreparedStatement stmt = connection.prepareStatement("SET FOREIGN_KEY_CHECKS=0;")
        stmt.execute()
        stmt = connection.prepareStatement("truncate ${databaseName}.${tableName};")
        stmt.execute()
        stmt = connection.prepareStatement("SET FOREIGN_KEY_CHECKS=1;")
        stmt.execute()
      } catch (Exception e) {
        throw new GenRocketException(e.getMessage())
      }
    }
  }


Parameters

The GenericSQLInsertReceiver requires that the following parameters are defined:

  • resourceName - Defines the name of the resource that contains the database connection information on a user's local machine.
  • databaseName - Defines the name of the database to access.
  • tableName - Defines the name of the database table to insert rows into.
  • uniqueAttribute - Defines a Domain Attribute the Receiver will use to identify a unique value that has already been inserted into the table to prevent duplicate data entry where a unique constraint is required.
  • truncate - Truncates the table before inserting rows by executing a 'delete from table' statement.
  • statementSeparator - Defines when a SQL statement has ended.


Column Data Types

This Receiver uses the following MySQL specific column data types:  

  • VARCHAR
  • INTEGER
  • BIGINT
  • BOOLEAN
  • DATE
  • DOUBLE
  • DECIMAL
  • FLOAT
  • TIME
  • TIMESTAMP


JDBC Configuration File

This Receiver uses a configuration file, located on the user's local computer, as a resource to attain the necessary information for connecting to a user's local database via JDBC. The required properties to connect to a database via JDBC are the following:

  • driver - the path and file name of the JDBC library for the database to connect to.
  • user - the name used to connect as a user to the database.
  • password - the password used to connect as the given user to the database.
  • url - the JDBC universal resource locator required to connect to the given database.
  • batchCount - defines the number of rows of generated data that are batched together before writing to the database.


Example JDBC Configuration File

Below is an example of a JDBC configuration file defined to connect to a MySQL database called, acme:


driver=com.mysql.jdbc.Driver
user=root
password=openSaysMe
url=jdbc:mysql://localhost:3306/acme?rewriteBatchedStatements=true
batchCount=1000


Receiver Attribute Property Keys

The Receiver defines two property keys that can be modified on any of its associated Domain Attributes:

  • columnName - Defines the name of the column as it is in the actual database table.
  • dataType - Defines the column data type.


Example Setting of Receiver Property Key Values

The example image below shows the property key view for the set of Attributes of a Domain using the MySQLInsertReceiver.