How to call DB procedure with MyBatis and Java annotations

According to MyBatis pages: “MyBatis is a first class persistence framework with support for custom SQL, stored procedures and advanced mappings. MyBatis eliminates almost all of the JDBC code and manual setting of parameters and retrieval of results. MyBatis can use simple XML or Annotations for configuration and map primitives, Map interfaces and Java POJOs (Plain Old Java Objects) to database records.”
I often face MyBatis configuration hidden in mapping .xml files. I wanted to try something different. As I learned from Spring, configuration can be also stored not only in XML files, but also in Java annotations. Spring enabled this possibility in its later versions. I found on the net that the MyBatis configuration may be stored there as well, but on the other hand I lacked enough of good tutorials. That is why I decided to write a blog post about this topic. The information, that was needed, I found on and I gathered the pieces together here.
Let’s imagine a stored procedure, which is able to change a status of a car entity. For example we want to change the status of the car to ‘sold’. So we need to define an own Java annotation, which is registered in Spring configuration file. Then we define a mapper interface. If you are already familiar with MyBatis, you should have an idea, how this looks like. But on the contrary this time we create no .xml file in resources. We just define everything in the annotation. Let’s see an example:

public interface ChangeCarStatusMapper {
    @Insert(value = { 
            "{ call DATABASE.PACKAGE.CHANGE_CAR_STATUS ( #{car_id, mode=IN, jdbcType=VARCHAR, javaType=String },  #{car_status, mode=IN, jdbcType=VARCHAR, javaType=String },  #{result_code, mode=OUT, jdbcType=NUMERIC, javaType=Integer},  #{error_description, mode=OUT, jdbcType=VARCHAR, javaType=String} ) }"
    @Options(statementType = StatementType.CALLABLE)
    void changeCarStatus(Map<String, Object> parameters);

This deserves an explanation. To call a procedure successfully we need an @Insert annotation. We can see two input and two output parameters. Also we need to define type of the statement. That is all. So it’s a more compact and effective solution, but it has also some disadvantages. For procedures with large interfaces it may become confused. Also big problem is lack of friendliness of Java in case multiline String constants. Usually you have to split such long String to more lines and use always concatenation. Also the fact that you are combining more languages in one source file may be questionable from the clean code and design perspective. But I think that despite these disadvantages it can be still advantageous for small procedure interfaces.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s