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 experiencing 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, that way will make your program has heavy dependency on mysql, because IFNULL is mysql native function, you’ll experiencing 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:

if(aColumn==null){
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 need to be formatted before it being an output, 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 will has heavy dependency. Just use simple ANSI query

SELECT date_column as dateColumn FROM a_table.

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

• 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" resultType="my.resultBean" parameterType="my.paramBean">
SELECT column1, column2 FROM my_table

LIMIT #{startData}, #{dataPerPage}

</select>

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

<sql id="pagingSql">
LIMIT #{startData}, #{dataPerPage}
</sql>

<select id="select" resultType="my.resultBean" parameterType="my.paramBean">
SELECT column1, column2 FROM my_table

<include refid="pagingSql" />

</select>

So with this, now my pagingSql can be used by another query by using <include>
Now, i’m finish and hoping this short article can help you to write more elegant program using Java and MyBatis.

Advertisement