Implementing dynamic SQL on Mybatis

Mybatis supports dynamic sql by using if, choose or when tag inside mapper xml. (More on dynamic sql tags) Sometimes, however, such tags are not enough for some requirements. Some times ago, I had to build select statements based on random table name. In that case, all parts of select statement were really dynamic. But condition tags inside mapper xml could not meet the requirements.

Mybatis supports really dynamic sql by @SelectProvider, @InsertProvider, @UpdateProvider and @DeleteProvider. I am showing a short example. (Download full sample)

Test table

CREATE TABLE MYBATIS_TEST(
	COL1 VARCHAR2(10) PRIMARY KEY,
	COL2 VARCHAR2(10)
);

SQL builder class

package test.mapper;

public class QueryBuilder {
	public String getSql() {
		String sql = "SELECT COL1, COL2 FROM MYBATIS_TEST WHERE COL1 = #{COL1}";
		return sql;
	}
}

• Above sql builder is very simple. But you can apply really dynamic logic inside it.

Mapper class

package test.mapper;

import org.apache.ibatis.annotations.SelectProvider;
import java.util.Map;

public interface TestMapper {
	@SelectProvider(type=QueryBuilder.class, method="getSql")
	public Map selectARecord(String key);
}

• This mapper class is the core of the sample. The important point is using @SelectProvider and it’s “type” and “method” attribute (which are pointing sql builder class and it’s method)

Mybatis config.xml

• Above config.xml defines mapper class by using mapper tag and class attribute.

Test main app

package test;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.log4j.Logger;
import java.io.InputStream;

import test.mapper.TestMapper;
import java.util.Map;

public class TestDynamicSql {
	private Logger logger = Logger.getLogger(this.getClass());

	public static void main(String [] args) throws Exception{
<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>		TestDynamicSql testApp = new TestDynamicSql();
		testApp.testDynamicSql();
	}

	private SqlSessionFactory getSqlSessionFactory() throws Exception{
		String resource = "mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		return new SqlSessionFactoryBuilder().build(inputStream);
	}

	private void testDynamicSql() throws Exception{
		SqlSessionFactory sessionFactory = getSqlSessionFactory();
		SqlSession sqlSession = sessionFactory.openSession(true);
		TestMapper mapper = sqlSession.getMapper(TestMapper.class);
		Map recordMap = mapper.selectARecord("VALUE1");
		this.logger.info("COL2 Value : " + recordMap.get("COL2"));
		sqlSession.close();
	}

}