-
Notifications
You must be signed in to change notification settings - Fork 6
Home
One of the main difficulties I find with Mule when I’m working on integration projects, is something that perhaps should be one of the easiest things to do with Mule; integrate with databases. Databases make up a big part of any back-end system, and most projects that use Mule as an ESB will utilise a database in part of the system.
When we talk about SQL databases, it is almost assumed that some operations need to be bundled in a transaction. For example; imagine we have two tables, one representing users and the second representing email addresses, where one user can have many email address, but email addresses are unique.
A very common use case is that Mule be required to offer a REST interface for creating users. So basically we need a flow with an HTTP inbound and two JDBC outbound endpoints so we can do the inserts, one for the user, and one (encapsulated in a ‘for’ loop) for the email addresses. Ideally these operations are executed in a transaction. Why? So that if an email address already exists, the transaction is rolled back. One may argue that we could have performed a check beforehand, but this would still not work because we all know that such workaround is nowhere near close to being thread safe. And this is where Mule falls short (previous versions to 3.4.0).
Also there is another issue here. When we insert the user, if we are using auto generated ids, somehow we need to retrieve the generated key to be able to use it as foreign key in the address. Well, as stupid as it gets, in Mule you need to do another select statement to retrieve the key yourself. I wrote a patch back in 2012 to automatically retrieve the generated keys: http://www.mulesoft.org/jira/browse/MULE-6306. It was contributed back to the community, but this was never included into Mule JDBC transport. This is true even for Mule 3.4.0.
Prior to Mule 3.4.0, a transaction could only be started on an inbound endpoint. This made the above use case, ridiculously complicated to implement with just out of the box features.
There are two options as I see it. The first one is splitting the flow in 2. Start by receiving a message on the HTTP endpoint and use a VM or JMS queue to start an XA transaction in the second flow. But seriously, here we are talking about an XA transaction to be able to implement a simple use case. From an architectural perspective, this is utter ****.
An option to avoid the XA transaction, would be to first write the data into a temporary table, and then have a second flow which reads the temporary data, and in a transaction inserts the data in its proper place. Again, this is a far cry from an optimal solution.
A much cleaner solution would be to ditch the Mule support for transactions and handle this yourself using plain JDBC, or something like Spring JDBC support http://static.springsource.org/spring/docs/3.0.x/reference/jdbc.html, or JPA. But would this not defeat the main purpose of our ESB? Of course it would! So how about integrating other frameworks in Mule like JPA?
This is another option. A module for JPA was written for Mule: https://github.com/mulesoft/mule-module-jpa. Unfortunately, this module never made it to the core product and never provided out of the box with Mule (which is sad given the fact JPA has been a standard for quite a while). Also it does not give us the flexibility of transactions that we are looking for.
One other field where Mule falls short in providing an optimal solution is where dynamic SQL is required. For example, in our use case we want to provide a REST interface, which provides the functionality to search users by some fields like email, name and surname. The user is allowed to enter all combinations of the options. How would you implement this with Mule? If you start thinking about it, it gets hairy extremely quickly. Unless you implement a custom solution, you will have to ditch named queries, which means now you have a new problem, SQL injection.
So have we done something to help out here? Yes we did. It wouldn’t be nice to rant and not act, would it? I really like the idea of JPA, especially the fact that you do not need to implement transformers to convert your data into POJOs. However I don’t really like the fact that most of the JPA frameworks are bulky. Although I must say, the feature that code is written once and run on any database, is a killer. I also don’t like the fact that you don’t have 100% control on the SQL that the framework produces. Hence I fell in love with MyBatis. I’m not going to give a detailed explanation of what MyBatis can do, because you can read more about it here; http://mybatis.github.io. As a start, it has an ‘out of the box’ feature for dynamic SQL. Its very light, and I also like the fact that MyBatis has native support for Spring, all documented here; http://mybatis.github.io/spring. Furthermore, MyBatis excels when we need to integrate with legacy databases, where we have no control on the tables, names, relationships, and so on…
So this looks like a very good start. What if there existed an easy way to use MyBatis in Mule? Well now there is! I am glad to present you with the new MyBatis module for Mule. It is still in its early stages of development, however the basic functionality is there. And when I say basic, I should qualify that by saying that it does at least include a cure for all of the shortcomings mentioned in the beginning of this blog post…
So let’s kick off with showing you how easy it is now to implement the mentioned use case. Let’s ignore the transactions for now, and concentrate on the database integration. Also for the purpose of simplicity, I am going to ignore the REST part, including the transformation into Java objects, JSON or XML…
So lets say we have the following POJOs:
public class Address {
private Integer id;
private String address;
private Integer personId;
//getters and setters here
}
public class Person {
private Integer id;
private String name;
private String surname;
private Integer age;
private List<Address> addresses;
//getters and setters here
}And the following SQL DDL:
CREATE TABLE person (
id int auto_increment PRIMARY KEY,
name varchar(255),
surname varchar(255),
age int
);
CREATE TABLE address (
id int auto_increment PRIMARY KEY,
address varchar(255),
personId int
);
ALTER TABLE address
ADD CONSTRAINT address_person_fk
FOREIGN KEY (personId)
REFERENCES person (id);
view rawddl.sql hosted with ❤ by GitHub
The first items we need are the MyBatis mappers. These include the SQL statements, and mappers for result set to POJO transformation (most of the transformation is done automatically). This is all MyBatis here:<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.mybatis.sql.AddressMapper">
<select id="selectAddress" parameterType="int" resultType="Address">
SELECT * FROM address
WHERE id = #{id}
</select>
<select id="selectAddressesByPersonId" parameterType="int" resultType="Address">
SELECT * FROM address
WHERE personId = #{id}
</select>
<insert id="insertAddress" parameterType="Address"
useGeneratedKeys="true" keyProperty="id">
INSERT INTO address (address, personId)
VALUES (#{address}, #{personId})
</insert>
<update id="updateAddress" parameterType="Address">
UPDATE address SET
address=#{address}
WHERE id=#{id}
</update>
</mapper><?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.mybatis.sql.PersonMapper">
<resultMap id="personResultMap" type="Person">
<id property="id" column="id" />
<collection property="addresses" column="id" ofType="Address" select="org.mybatis.sql.AddressMapper.selectAddressesByPersonId"/>
</resultMap>
<select id="selectPerson" parameterType="int" resultMap="personResultMap">
SELECT * FROM person
WHERE id = #{id}
</select>
<insert id="insertPerson" parameterType="Person" useGeneratedKeys="true" keyProperty="id">
INSERT INTO person (name, surname, age)
VALUES (#{name}, #{surname}, #{age})
</insert>
<update id="updatePerson" parameterType="Person">
UPDATE person SET
name=#{name},
surname=#{surname},
age=#{age}
WHERE id=#{id}
</update>
</mapper>With this configuration, now we need no Mule transformers, MyBatis will return a populated POJO or list of POJOs where necessary.
Also from MyBatis 2.x, a new feature was introduced where these mappers can be linked to interfaces for better type handling. For the purpose of completion, we are including the interfaces here:
public interface AddressMapper {
public Address selectAddress(Integer id);
public List<Address> selectAddressesByPersonId(Integer id);
public void insertAddress(Address person);
public void updateAddress(Address person);
}
public interface PersonMapper {
public Person selectPerson(Integer id);
public void insertPerson(Person person);
public void updatePerson(Person person);
public void deletePerson(Integer id);
}MyBatis 2.x also supports the use of annotations directly on the interface to eliminate mappers and get a cleaner solution. While this is supported by our module, we are not going to cover it in this blog.
Once we have the mappers and the interfaces ready, we need a MyBatis configuration.
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<setting name="useGeneratedKeys" value="true" />
</settings>
<typeAliases>
<typeAlias alias="Person" type="org.mybatis.domain.Person" />
<typeAlias alias="Address" type="org.mybatis.domain.Address" />
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="org.h2.Driver" />
<property name="url" value="jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1" />
<property name="username" value="root" />
<property name="password" value="" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="org/mybatis/sql/person-mapper.xml" />
<mapper resource="org/mybatis/sql/address-mapper.xml" />
</mappers>
</configuration>What is all this saying? It starts off by setting some MyBatis options, like enabling the use of generated keys. Then we define type aliases, so wherever you see Person in the mappers, it actually means “org.mybatis.domain.Person”. The next section defines our datasource, while the last section instructs MyBatis were to find the mapper configuration files. For more detailed information about the MyBatis configuration, please visit http://mybatis.github.io/mybatis-3/configuration.html.
Now that we have completed all of the MyBatis configuration, let’s move on to our Mule flows. The idea is that the input to this flow is a POJO of type Person. First we insert the Person object, and MyBatis will automatically populate the new generated key in the id field.
Then we loop on the addresses, first we set the foreign key, then persist in the database. This is all there is to it:
<mybatis:config name="mybatis" configFile="mybatis-config.xml"/>
<sub-flow name="CreateUser">
<set-variable variableName="person" value="#[payload]" />
<mybatis:execute mapper="org.mybatis.sql.PersonMapper" method="insertPerson" />
<foreach collection="#[flowVars.person.addresses]">
<expression-component>
payload.personId = flowVars.person.id;
</expression-component>
<mybatis:execute mapper="org.mybatis.sql.AddressMapper" method="insertAddress" />
</foreach>
</sub-flow>It’s that easy!
Now for the cool stuff! How can we encapsulate all these operations in a transaction? Extremely easy! The module provides 3 processors to help with transactions:
<mybatis:begin-transaction />
<mybatis:commit-transaction />
<mybatis:rollback-transaction />This is how we would use the above in our new flow:
<flow name="CreateUserTransacted">
<mybatis:begin-transaction />
<flow-ref name=”CreateUser” />
<mybatis:commit-transaction />
<rollback-exception-strategy>
<mybatis:rollback-transaction />
</rollback-exception-strategy>
</flow>That’s it! MyBatis will take care of everything, including transaction management. If everything goes fine, the transaction will be committed. If an exception is thrown, like “Duplicated key exception”, the whole transaction will be rolled back.
For those using MyBatis the old style, (with only the mappers and no interfaces), you can still use the most common MyBatis features such as:
<mybatis:select-one statement="org.mybastis.sql.PersonMapper.selectOne"/>
<mybatis:select-list statement="org.mybastis.sql.PersonMapper.selectList"/>
<mybatis:select-map statement="org.mybastis.sql.PersonMapper.selectMap" mapKey="id"/>
<mybatis:update statement="org.mybastis.sql.PersonMapper.update"/>
<mybatis:insert statement="org.mybastis.sql.PersonMapper.selectOne"/>
<mybatis:delete statement="org.mybastis.sql.PersonMapper.selectOne"/>There is also support for MyBatis Spring module, which means some configuration of MyBatis can be shifted to Spring rather than the MyBatis configuration file.
<mybatis:config name="mybatis" sqlSessionFactory-ref="sqlSessionFactory" />
<spring:beans>
<spring:bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<spring:property name="driverClassName" value="org.h2.Driver" />
<spring:property name="url" value="jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1" />
<spring:property name="username" value="root" />
<spring:property name="password" value="" />
</spring:bean>
<spring:bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<spring:property name="dataSource" ref="dataSource" />
<spring:property name="configLocation" value="classpath:mybatis-config.xml" />
</spring:bean>
</spring:beans>The module is open source, and this use case (with some alterations) can be found as part of the test cases and as a working example.
What’s next? Well this module was designed for Mule prior to version 3.4.0, where transaction support was not really adequate for use cases like these. In Mule 3.4.0, there is a new feature called Transaction Scope, which allows you to start a transaction wherever you want in the flow (similar to what we do here).
The next item on the todo list for this module is to integrate the MyBatis transactions with Mule’s transaction scope.
This module does not support XA transactions at the moment, so this is also in the todo list. Hope you enjoy using this module as much as I enjoyed developing it, and helps you avoid some unnecessary pain! :-)