MyBatis - how to do it simple

As I wrote yesterday I'm working with software development. Currently I'm working on a common database layer for our portals.

I found that many of the current frameworks for ORM(Object-Relational-Mapping) like Hibernate and openJPA doesn't support "custom" SQL. That basically means that your database should 100% reflect your application model or you should have views instead of actual tables, this would be livable for me since I don't need to do any updates on the data I'm retrieving, but that might be an issue later on, and as you probably guessed I don't have any views available, I have Stored Procedures. And that where the custom SQL comes in, none of them supports this feature and that left our current application a coding mess of SQL commands in strings and parameter binding on SP's with JDBC directly in code, not abstracted and generalized for use by other apps/portals.

So is this just a whiner post or what? Absolutely not. I'm going to show you how you simply can make use of your current SP's in your Java code. I recommend everyone looking for this kind of functionality to take a look at MyBatis and read their user guide[1], it contains tons of information you never thought you would use.



Configuration
First you need to configure MyBatis to access your database. A simple XML Configuration file is needed, and if you want some flexibility you use a *.properties file togheter with the XML file.

<!-- DOCTYPE LEFT OUT -->
<configuration>
  <environments default="yourcrazyenvironmentname">
    <environment id="yourcrazyenvironmentname">
      <transactionManager type="JDBC" />
      <dataSource type="POOLED">
        <property name="driver" value="${driver}" />
        <property name="url" value="${url}" />
        <property name="username" value="${username}" />
        <property name="password" value="${password}" />
      </dataSource>
    </environment>
  </environments>
  <mappers>
    <mapper resource="path/to/your/insane/file/for/mapper.xml" />
  </mappers>
</configuration>

The above configuration makes room for the earlier mentioned properties file, you can however hardcode the values in your myBatisConfiguration, just replace the values encapsulated by ${...} and their encapsulation with your own values.

Mapping
The you create your mapper file. This is one of the simplest yet most challenging tasks when starting out MyBatis. Sadly I'm no creative soul, so I won't start making examples here, instead I will refer to the excellent section in the user guide about this part of the process.

Mappings is basically a piece of XML code where the body-content of the tag is a piece of SQL code. This is interesting from the ORM perspective but from a coding perspective the next part is much more fun

The interface
This is where you make your application go typesafe, forget about the other mentioned method in the book, this is the shit. For me an interface might look like:

package dk.nwillems.data.mappings

import ...;

public interface ExampleMapper{
    public Collection getCrazyDataType();
    public Collection getCrazyDataTypeEasyFilter(String param);
}

This looks straight forward. but if you would like a function called

getCrazyataTypeAdvancedFilter(String param1, String param2);

That wouldn't work. why you ask? This sounds like the holy grail of ORM, sadly its not. To make the above piece of code work, assuming that you have multiple parameters for your SP or query, you should do the following:

getCrazyataTypeAdvancedFilter(
        @Param("param1NameInMapper")String param1,
        @Param("param2NameInMapper")String param2);

And now you should be good to go with the rest of the user guide, I spent a long time finding this "issue" about the annotations. Now I've spelled it out, I hope someone out there can use this know-how for some constructive work.

In future blog posts I will check out some of the other features and probably write a more in-depth post about MyBatis and how-to get started. This post was just to get my confusion out and put some knowledge in the world.

Happy coding - now with MyBatis
Nicolai

[1] = MyBatis user guide http://mybatis.googlecode.com/svn/trunk/doc/en/MyBatis-3-User-Guide.pdf