Skip to main content

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

Comments

Popular posts from this blog

Send e-mail with attachment through OSB

Oracle Service Bus (OSB) contains a good collection of adapter to integrate with any legacy application, including ftp, email, MQ, tuxedo. However e-mail still recognize as a stable protocol to integrate with any application asynchronously. Send e-mail with attachment is a common task of any business process. Inbound e-mail adapter which, integrated with OSB support attachment but outbound adapter doesn't. This post is all about sending attachment though JavaCallout action. There are two ways to handle attachment in OSB: 1) Use JavaCallout action to pass the binary data for further manipulation. It means write down a small java library which will get the attachment and send the e-mail. 2) Use integrated outbound e-mail adapter to send attachment, here you have to add a custom variable named attachment and assign the binary data to the body of the attachment variable. First option is very common and easy to implement through javax.mail api, however a much more developer manage t

Tip: SQL client for Apache Ignite cache

A new SQL client configuration described in  The Apache Ignite book . If it got you interested, check out the rest of the book for more helpful information. Apache Ignite provides SQL queries execution on the caches, SQL syntax is an ANSI-99 compliant. Therefore, you can execute SQL queries against any caches from any SQL client which supports JDBC thin client. This section is for those, who feels comfortable with SQL rather than execute a bunch of code to retrieve data from the cache. Apache Ignite out of the box shipped with JDBC driver that allows you to connect to Ignite caches and retrieve distributed data from the cache using standard SQL queries. Rest of the section of this chapter will describe how to connect SQL IDE (Integrated Development Environment) to Ignite cache and executes some SQL queries to play with the data. SQL IDE or SQL editor can simplify the development process and allow you to get productive much quicker. Most database vendors have their own front-en

Load balancing and fail over with scheduler

Every programmer at least develop one Scheduler or Job in their life time of programming. Nowadays writing or developing scheduler to get you job done is very simple, but when you are thinking about high availability or load balancing your scheduler or job it getting some tricky. Even more when you have a few instance of your scheduler but only one can be run at a time also need some tricks to done. A long time ago i used some data base table lock to achieved such a functionality as leader election. Around 2010 when Zookeeper comes into play, i always preferred to use Zookeeper to bring high availability and scalability. For using Zookeeper you have to need Zookeeper cluster with minimum 3 nodes and maintain the cluster. Our new customer denied to use such a open source product in their environment and i was definitely need to find something alternative. Definitely Quartz was the next choose. Quartz makes developing scheduler easy and simple. Quartz clustering feature brings the HA and