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 ListsearchUser(@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(Mapparameters) { 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
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 ListsearchUser(@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 Listflags = 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); }