Description
The MySQLInsertV2Receiver 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 GenericSQLInsertV2Receiver, 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 GenericSQLInsertV2Receiver.
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 following parameters can be defined for the MySQLInsertV2Receiver. Items with an asterisk (*) are required.
- resourcePath* - Defines the directory of the resources that contains the database connection information on a user's local machine.
- resourceSubDir - Defines the subdirectory of the resource that contains the database connection information on a user's local machine.
- 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 and prevents it from being inserted twice.
- truncate* - Truncates the table before inserting rows by executing a 'delete from table' statement.
- statementSeparator* - Defines when a SQL statement has ended.
- booleanValue* - Format a Boolean object with the selected mask.
- rowDelimiter* - Defines the bytes that delimit each row.
- nullValue* - Represents Null value.
- quoteTextData* - Defines the character to use when quoting text data.
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 three 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 type of data for the given column.
- include - Determines if the Attribute will be included in the output.
The example image below shows the property key view for the set of Attributes of a Domain using the MySQLInsertReceiver.