Saturday

Playing with Ibatis *.*Provider to create dynamic query

Last week, in one of our project, which we decided to upgrade sql queries on ibatis needed dynamic sql query execution. As we decided to use annotations as much as possible on sql mapper - it's seems not a straight to build dynamic query. At first tried to do something as follows:
DONOT TRY THIS - IT'S WRONG WAY TO BUILD DYNAMIC QUERY
@Update("update ${schemaName}.fdc_uf uf" +
" set uf.reestr_date = #{reestDate}," +
" uf.reestr_number = #{reestNum}" +
" where uf.reestr_number IS NULL AND uf.reestr_date IS NULL and uf.id in " +
" <foreach item="item" index="index" collection="list" open="(" separator="," close=")">"+
" #{item}"+
" </foreach>")

After some tries, read the user guide attentively, found there are another a few way to build dynamic query, one of them to use Select/Insert/UpdateProvider.
These alternative SQL annotations allow you to specify a class name and a method that will return the SQL to run at execution time. Upon executing the mapped statement, iBATIS will instantiate the class, and execute the method, as specified by the provider.
Here is the required steps to achieve the goals:
1) First we will create the Builder to build the sql
package com.blue.ibatis.test.dao;

import java.sql.Date;
import java.util.List;
import java.util.Locale;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

import static org.apache.ibatis.jdbc.SelectBuilder.*;
//import static org.apache.ibatis.jdbc.SqlBuilder.*;
public class SqlBuilder {

public String simpleUpdate(final FdcUf fdcUf){
final DateFormat df = DateFormat.getDateInstance(DateFormat.MEDIUM, new Locale("ru"));
String date = df.format(fdcUf.getReestrDate());
StringBuilder sql= new StringBuilder("update fdc_uf uf " +
" set uf.reestr_number = '"+fdcUf.getReestrNom()+"' ," +
" uf.reestr_date = to_date('"+ date+"',"+" '"+getDatePattern()+"')"+
" where uf.id in (") ;
for(Long l : fdcUf.getIds()){
sql.append(l);
sql.append(",");
}
sql.deleteCharAt(sql.lastIndexOf(","));
sql.append(")");
System.out.println("Sql:"+ sql.toString());

return sql.toString();
}
public String getufById(long id){
return "select uf.nom_uf from fdc_uf uf where uf.id="+id;    
}
private String getDatePattern(){
return ((SimpleDateFormat)DateFormat.getDateInstance(DateFormat.MEDIUM, new Locale("ru"))).toPattern();
}
}

Ibatis user guide offers SelectBuilder and SqlBuilder class to build sql queries dynamical. I have found the SelectBuilder but couldn't found the SqlBuilder. For that reason uses Stringbuilder on above code fragment.

See the screen shot from the user guide about SqlBuilder, but i havn't found it.
Now we have to Edit Mapper class.
@UpdateProvider(type = com.blue.ibatis.test.dao.SqlBuilder.class, method = "simpleUpdate")
void simpleUpdate(final FdcUf fdcUf);

and we are ready to invoke mapper.
Here is the simple pojo of the FdcUf

package com.blue.ibatis.test.dao;

import java.sql.Date;
import java.util.List;

public class FdcUf {
private long id;
private String reestrNom;
private Date reestrDate;
private List <long> ids;
public FdcUf() {
}

public long getId() {
return id;
}

public void setId(long id) {
this.id = id;
}

public String getReestrNom() {
return reestrNom;
}

public void setReestrNom(String reestrNom) {
this.reestrNom = reestrNom;
}

public Date getReestrDate() {
return reestrDate;
}

public void setReestrDate(Date reestrDate) {
this.reestrDate = reestrDate;
}

public List <long> getIds() {
return ids;
}

public void setIds(List<long> ids) {
this.ids = ids;
}
}


Happy Coding