Home > Java > Making MyBatis more reuseable

Making MyBatis more reuseable

I’ve been using MyBatis (formerly iBatis) since 2 or 3 years ago, and ive no doubt about this framework’s capability in handling my needs to do many database operations.

But, bad practice in using this can make your code ends painfully, especially when you have to do some code refactoring on it. I just want to share several tips and tricks to make your MyBatis application easier to refactor, more reuseable and less dependency.

  • Do the IF in Java level: as a Programmer, you will and always have many cases of IFs statement, for the simplest, when you want to change the output of null value from database to another value, you can do this query on mysql:
    SELECT IFNULL(a_column,'null replacement value') FROM a_table

AVOID that, because that means your application has heavy dependency on mysql, because IFNULL is mysql native function, you’ll be having problem if you have to change database platform eg: Sql Server or Oracle. It will be better to let Java handle this for you, like this:

aColumn="null replacement"; }
  • Avoid data formatting in MyBatis: We often change the format of the database output, example: Datetime is the most common datatype that needs to be formatted before it being shown to the screen we can do the formatting with something like this,
SELECT DATE_FORMAT(date_column,'%d-%m-%Y') FROM a_table

Avoid that, same as before, that code practice is having heavy dependency. Just use simple ANSI query

SELECT date_column as dateColumn FROM a_table.

And let Java take the formatting job with its available classes, SimpleDateFormat to do datetime formatting, NumberFormat for number formatting, etc. And as an added value you can put the pattern in constants or properties so it will be easier if you want to change the pattern in the future.

  • Pass value as parameter: For the example, In mysql you can retrieve current datetime information with now() function, and in case you want to put current date-time in table, you can write sql command like this:
    INSERT INTO a_table(column1, time_stamp) VALUES( #{paramValue},now())

    Well, stop doing this thing, a better solution, you can write more reuseable query by putting timestamp into parameter and pass it, just like this

    INSERT INTO a_table(column1, time_stamp) VALUES( #{paramValue},#{currentTimestamp}))
  • Group often used query with <sql> </sql>: In one MyBatis XML, we often facing several same part-of-queries and MyBatis is smart to add <sql> as a feature and make it capable to grouping the query, so we can use and reuse it. By using this feature, you can make your query more feasible and easier to refactor.

For example: i used to do data pagination, and i can write the worse solution just like this.

<!-- Not Recommended, AVOID -->
<select id="select">
SELECT column1, column2 FROM my_table

LIMIT #{startData}, #{dataPerPage}


but, with sql grouping feature, now i can write more elegant query just like this,

<sql id="pagingQuery">
     LIMIT #{startData}, #{dataPerPage}
<select id="select">
SELECT column1, column2 FROM my_table
    <include id="pagingQuery" />

So with this, now my pagingQuery can be used by another query by using <include>
I hope this short article can help you to write more elegant program using Java and MyBatis.

Categories: Java Tags: ,
  1. JJ
    31 August 2011 at 22:55

    Great pointers. With respect to the use of IFNULL(), a MySQL native function…

    …I’ve had problems getting MyBatis to recognize/parse SQL commands that include IFNULL(), but there is a valid alternative to use within SQL commands that does not introduce portability issues.

    You can use COALESCE() to achieve the same result, which is a standard SQL function that all platforms/sql databases support. Arguments passed to it are parsed and the first non-null parameter is used, so you can get the same result from the MySQL-specific function call:

    IFNULL(value-that-could-be-null, “non-null-replacement-value”)

    by using the SQL standard call:

    COALESCE(value-that-could-be-null, “non-null-replacement-value”)

    and still achieve portability to other SQL database engines besides MySQL.

    • RDeJourney
      1 September 2011 at 09:40

      Halo JJ, thanx for the reply, yes Coalesce is standard enough in DBMS. I took the example to simplify the problem and to explain it in basic daily use so it can be more understandable.

      But anyway, thanx for the reply, appreciate it. **thumb up**

  2. 22 July 2012 at 04:44

    If some one needs expert view about blogging and site-building afterward i advise him/her to visit this webpage, Keep up the
    pleasant job.

  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: