I recently wrote about how a basic mybatis application can be set with Spring.
But there are situations when queries can be a bit complex than a simple insert or select. In such cases
@SelectProvider
can help us build a dynamic query.
For example, in my user mapper, I need to search based on Id OR status.
@SelectProvider(type = UserSQLBuilder.class, method = "getUsersProvider")
@ResultMap(MyConfig.USER_MAP)
public List searchUser(@Param("id") String id,@Param("status") String status);
My UserSQLBuilder class would have a method called getUsersProvider which will create a dynamic query based on paramaters I am passing here.
public class UserSQLBuilder {
public String getUsersProvider(Map parameters) {
String id = (String) parameters.get("id");
String status = (String) parameters.get("status");
StringBuilder query = new StringBuilder();
query.append("select id, user_name, user_address, status from users");
if (status.equals("NA") && !id.equals("NA")) {
query.append(" where id like '%" + id + "%'");
} else if (!status.equals("NA") && id.equals("NA")) {
query.append(" where status ='" + status + "'");
return query.toString();
}
}
Another important thing to note here is that I am using @ResultMap to map result of query to my User (List) object. If we look at the query, it returns user_name, user_address etc. Whereas in my User class I have userName, userAddress and so on. The challenge is to map query values to object values.
There are actually multiple ways.
1. I can simply modify my query – select user_name as userName, user_address as userAddress from users.
2. Explicitly tell my mapper to map the values from column to object like
@SelectProvider(type = UserSQLBuilder.class, method = "getUsersProvider")
@Results({ @Result(property = "userName", column = "user_name"),
@Result(property = "userAddress", column = "user_address") })
public List searchUser(@Param("id") String id,@Param("status") String status);
3. Create a @ResultMap which can be reused, useful in cases when same mapping has to be done more than once.
@ResultMap(MyConfig.USER_MAP)
statement tells the mapper to look for a map in config file.
in MyConfig.java, I will define
public static final String REPORT_MAP = "com.test.mapper.UserMapper.user";
//code here
private void registerUserMap(org.apache.ibatis.session.Configuration config) {
// registering result maps
List flags = new ArrayList();
List resultMappings = new ArrayList();
flags.add(ResultFlag.ID);
org.apache.ibatis.mapping.ResultMapping.Builder resultBuilder = new org.apache.ibatis.mapping.ResultMapping.Builder(
config, "userName", "user_name", String.class);
resultBuilder.flags(flags);
resultMappings.add(resultBuilder.build());
resultBuilder = new org.apache.ibatis.mapping.ResultMapping.Builder(config, "userAddess",
"user_address", String.class);
resultBuilder.flags(flags);
resultMappings.add(resultBuilder.build());
Builder resultMapBuilder = null;
resultMapBuilder = new Builder(config, "com.gcp.dao.mapper.ReportMapper.report", Report.class, resultMappings, true);
config.addResultMap(resultMapBuilder.build());
}
And while defining my session factory, I will register this Map.
@Bean
public SqlSessionFactory sqlSessionFactory() throws Exception {
org.apache.ibatis.mapping.Environment environment = new org.apache.ibatis.mapping.Environment("",
new JdbcTransactionFactory(), getDataSource());
org.apache.ibatis.session.Configuration config = new org.apache.ibatis.session.Configuration(environment);
registerReportMap(config);
return new SqlSessionFactoryBuilder().build(config);
}