MyBatis | Dynamically Generate SQL in a Program

MyBatis provides a way to define SQL by using annotations.

However, when assembling SQL through string concatenation, there are many details to watch for, such as comma separation and AND or OR separation.

MyBatis provides an API that hides those tedious details.

Source Code

sample_mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="sample.mybatis.TestTableMapper"> <!-- Specify Mapper in the namespace -->
</mapper>

TestTableMapper.java

package sample.mybatis;

import java.util.List;

import org.apache.ibatis.annotations.SelectProvider;

// Create it as a Mapper interface
public interface TestTableMapper {
    // Specify the class method that generates SQL with @SelectProvider
    @SelectProvider(type=TestTableSqlProvider.class, method="getSelectTestSql")
    List<TestTable> selectTest();
}

TestTableSqlProvider.java

package sample.mybatis;

import org.apache.ibatis.jdbc.SQL;

// Class that generates SQL
public class TestTableSqlProvider {

    // Method that generates SQL
    public String getSelectTestSql() {
        // Generate SQL by using the SQL class.
        SQL sql = new SQL() {{ // Create an anonymous class
            SELECT("id, hoge_value");
            SELECT("fuga_value");
            SELECT("piyo_value");
            FROM("test_table");
        }};

        return sql.toString();
    }
}

Main.java

package sample.mybatis;

import java.io.InputStream;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class Main {

    public static void main(String[] args) throws Exception {
        try (InputStream in = Main.class.getResourceAsStream("/mybatis-config.xml")) {
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);

            try (SqlSession session = factory.openSession()) {
                TestTableMapper mapper = session.getMapper(TestTableMapper.class);
                mapper.selectTest();
            }
        }
    }
}

Execution Result

[DEBUG] s.m.T.selectTest - ==>  Preparing: SELECT id, hoge_value, fuga_value, piyo_value FROM test_table WHERE (hoge_value is not null) 
[DEBUG] s.m.T.selectTest - ==> Parameters: 

Description

• Specify the annotation on the Mapper method with @SelectProvider.
  • Set type to the class that generates SQL, and set method to the name of the method that generates SQL.
  • For INSERT or UPDATE, there are @InsertProvider and @UpdateProvider.
• Use the org.apache.ibatis.jdbc.SQL class to generate SQL.
• Create an SQL class instance as an anonymous class and generate SQL by using the SELECT() and FROM() methods in the instance initialization block.
  • Each argument passes a SQL item as a string.
  • At this point, comma separation and similar formatting are added as needed.
• The generated SQL can be retrieved as a string from the toString() method of the generated SQL instance.

• Dynamically generate SQL in a program
  • How to use each method