DBUnit & MySQL: DataTypeFactory warning

I've been playing with DBUnit to help setup a database for my integration testing, and the experience has been very good. When my dataset was being loaded into my MySQL database, I found this warning:

    [WARN ] [org.prystasj.myproject.it.MyIntegrationTestCase.setUpDatabase] - Potential problem found: 
    The configured data type factory 'class org.dbunit.dataset.datatype.DefaultDataTypeFactory'
    might cause problems with the current database 'MySQL' (e.g. some datatypes may not be 
    supported properly). 
    In rare cases you might see this message because the list of supported database products is 
    incomplete (list=[derby]). If so please request a java-class update via the forums.

I found the following DBUnit FAQ Entry on the DBUnit website that helped me out a bunch, the code from the entry:

    IDatabaseConnection connection = new DatabaseConnection(jdbcConnection, schema);
    DatabaseConfig config = connection.getConfig();
    config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new OracleDataTypeFactory());

My code additions:

    // http://dbunit.sourceforge.net/faq.html#typefactory
    def dbConfig = dbConnection.getConfig();
    dbConfig.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new MySqlDataTypeFactory()

And all my Groovy DBUnit related code:

import org.dbunit.database.DatabaseConfig
import org.dbunit.database.DatabaseConnection
import org.dbunit.dataset.xml.XmlDataSet
import org.dbunit.ext.mysql.MySqlDataTypeFactory
import org.dbunit.operation.DatabaseOperation
...

class MyIntegrationTestCase extends GroovyTestCase {

    ...

    def setUpDatabase() {
        def connection = getConnection()
        def data = getDataSet()

        try {
            DatabaseOperation.CLEAN_INSERT.execute(connection, data)
        } 
        finally {
            connection.close()
        }
    }

    def getConnection() {
        Class.forName(jdbcDriver)
        def jdbcConnection = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword)
        def dbConnection = new DatabaseConnection(jdbcConnection)
        
        // http://dbunit.sourceforge.net/faq.html#typefactory
        def dbConfig = dbConnection.getConfig();
        dbConfig.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new MySqlDataTypeFactory())

        return dbConnection
    }

    def getDataSet() throws Exception {
        def stream = getClass().getClassLoader().getResourceAsStream(DATASET)
        def dataset = new XmlDataSet(stream)
        return dataset
    }    
        
}

Groovy & MySQL: ALTER TABLE

I was having trouble executing a query similar to the following with in Groovy against a MySQL database:

    ALTER TABLE MAP AUT0_INCREMENT = 109

The code:

    import groovy.sql.Sql
    ...
    def setAutoIncrement(table, value) {
        def sql = Sql.newInstance(jdbcUrl, jdbcUsername, jdbcPassword, jdbcDriver)
        def query = "ALTER TABLE $table AUTO_INCREMENT = $value"
        sql.execute(query)
    }

I was getting the folowing error:

    com.mysql.jdbc.exceptions.MySQLSyntaxErrorException:
    You have an error in your SQL syntax; check the manual
    that corresponds to your MySQL server version for the right
    syntax to use near ''MAP' AUTO_INCREMENT = '109'' at line 1

Seems like Groovy was adding single quotes around the table name and the value. I resolved this by strong-typing the query variable to a String to subvert the GString interpolation:

   String query = "ALTER TABLE $table AUTO_INCREMENT = $value"

Quoting the table and value in MySQL is invalid when running the MySQL command-line tool as well.

JPA: Mapping Created and Updated Columns

To quickly follow up on my last post about CREATED and UPDATED columns for an entity named Map in MySQL, I thought I would add the equivalent JPA declarative ORM definition from my orm.xml:

  <entity class="Map" name="Map" access="FIELD">
    <table name="MAP"/>
    <attributes>
      <id name="id">
        <column name="ID" nullable="false"/>
        <generated-value strategy="SEQUENCE"/>
      </id>
      <basic name="updated">
         <column name="UPDATED" nullable="true"/>
         <temporal>DATETIME</temporal>
      </basic>
      <basic name="updated">
         <column name="UPDATED" nullable="true"/>
         <temporal>TIMESTAMP</temporal>
      </basic>
    </entity>

MySQL: Adding Created and Updated Columns

I had a requirement to add a Last-Modified header to GET requests for an entity called Map in a service I was developing. I figured it would be a good idea to track that information in the MySQL database backing the service instead of in the application itself with a column named UPDATED. In other words, I wouldn't have to explicitly set andupdate the values during the lifetime of the Map.

While I was at it, I also thought it would be a good idea to add a CREATEDcolumn to track when the Map came into existence in case that information would be useful later.

At table creation time you can add the columns with the following SQL (which omits any other useful attributes or columns):

  CREATE TABLE MAP (
      ID           INT          NOT NULL AUTO_INCREMENT,
      CREATED      DATETIME     NOT NULL DEFAULT '1900-01-01 00:00:00',
      UPDATED      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  );

The columns can also be added after the table has been created, assigning the defaults to each table row:

  ALTER TABLE MAP ADD CREATED DATETIME NOT NULL DEFAULT '1900-01-01 00:00:00';
  ALTER TABLE MAP ADD UPDATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

Finally a trigger can be used, which can be added at anytime, to have the CREATED column be given a value on an INSERT:

  CREATE TRIGGER MAP_CREATED BEFORE INSERT ON MAP
  FOR EACH ROW SET NEW.CREATED = NOW();

With the trigger, the default on the CREATED column wouldn't exactly be needed, and in fact the table could be created without it, leaving the NOT NULL clause intact.

The resulting table definition:

mysql> DESCRIBE MAP;
+---------+-----------+------+-----+---------------------+----------------+
| Field   | Type      | Null | Key | Default             | Extra          |
+---------+-----------+------+-----+---------------------+----------------+
| ID      | int(11)   | NO   | PRI | NULL                | auto_increment | 
| CREATED | datetime  | NO   |     | 1900-01-01 00:00:00 |                | 
| UPDATED | timestamp | NO   |     | CURRENT_TIMESTAMP   |                | 
+---------+-----------+------+-----+---------------------+----------------+
3 rows in set (0.00 sec)

Mule: Sending a JMS Message to WebSphere MQ

The Mule Client documentation example for sending a JMS message to a JMS queue seems simple enough:

    MuleClient client = new MuleClient();
    client.dispatch("jms://my.queue", "Message Payload" null);

I was having trouble sending a JMS message to endpoint hooked to the following WebSphere MQ connector. The connector has a JMS to Object transformer for the incoming message. Sending a String as in the example produced an execption related to the connection factory stating that the incoming object was of type Class and not a JMS message. The connector:

  <jms:connector name="jmsConnectorWMQ"
                 connectionFactory-ref="WMQConnectionFactory"
                 maxRedelivery="5"
                 specification="1.1"
                 persistentDelivery="true"
                 numberOfConcurrentTransactedReceivers="5">
    <service-overrides inboundTransformer="Jms2Obj"/>
  </jms:connector>
  
  <jms:jmsmessage-to-object-transformer name="Jms2Obj"/>

The connection factory:

  <spring:bean name="WMQConnectionFactory" class="com.ibm.mq.jms.MQQueueConnectionFactory">
    <spring:property name="transportType" value="1"/>
    <spring:property name="hostName" value="${jms.connection.hostname}"/>
    <spring:property name="port" value="${jms.connection.port}"/>
    <spring:property name="queueManager" value="${jms.queue.manager}"/>
    <spring:property name="channel" value="${jms.connection.channel}"/>
  </spring:bean>

I was able to create and send a JMS message with the following method. Without giving the ObjectToJMSMessage transformer an endpoint, an exception was thrown stating the transformer requires a javax.jms.Session. The String to send is transformed to a JMS message to make the connector happy.

The URL returned by the getUrl() method below was the address of a JMS queue, for example: jms://request.queue. Im using MuleClient.send() because I want the response returned synchronously.

The method:

protected String sendRequest(Object request) throws Exception {
                
    EndpointFactory endpointFactory = new DefaultEndpointFactory();
    MuleContext context = MuleServer.getMuleContext();
    endpointFactory.setMuleContext(context);
    ImmutableEndpoint endpoint = endpointFactory.getInboundEndpoint(getUrl());
        
    ObjectToJMSMessage transformer = new ObjectToJMSMessage();
    transformer.setEndpoint(endpoint); 
        
    Object transformedMessage = transformer.doTransform(request, "UTF-8");

    MuleClient client = new MuleClient();
    MuleMessage responseMessage = client.send(getUrl(), transformedMessage, null);
    String response = responseMessage.getPayloadAsString(ENCODING);
        
    return response;
}

This seems like a lot of work to me to send a simple JMS message to WebSphere MQ, but it works. A suggestions for alternative would be greatly appreciated.

A better approach: I left this as a comment but it doesn't display too nicely...

With com.ibm.mqjms.jar, sending the JMS message is much easier. I wish I would of found this earlier.

With the following imports:

    import com.ibm.jms.JMSTextMessage;
    import com.ibm.mq.jms.MQJMSStringResources;

The original method is greatly simplified to:

    protected String sendRequest(Object request) throws Exception {
                        
        MQJMSStringResources resources = new MQJMSStringResources();        
        JMSTextMessage jmsMessage = new JMSTextMessage(resources, (String)request);
        jmsMessage.setText((String)request);
        
        MuleClient client = new MuleClient();
        MuleMessage responseMessage = client.send(getUrl(), jmsMessage, null);

        String response = responseMessage.getPayloadAsString(ENCODING);

        return response;
    }

Using tidy to Format XML

Here's an exmaple of using tidy to format XML:

tidy -utf8 -xml -w 255 -i -c -q -asxml

I like to use an alias to work with files easily:

$ alias xmltidy="tidy -utf8 -xml -w 255 -i -c -q -asxml"

$ xmltidy some-file.xml > tidy-file.xml

You can also change a file in place:

$ xmltidy -m some-file.xml

Groovy: Pretty Printing XML

A script for pretty printing XML in Groovy:

#!/usr/share/groovy/bin/groovy
import org.xml.sax.SAXParseException

def xml = new File(args[0]).text
def stringWriter = new StringWriter()
def printWriter = new PrintWriter(stringWriter)

try {
    def node = new XmlParser().parseText(xml)
    new XmlNodePrinter(printWriter).print(node)
    println stringWriter.toString()
} catch(SAXParseException spe) {
    println spe.getMessage()
}

Suggestions for alternatives would be appreciated!