Mybatis- Using SelectProvider and ResultMap

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);
		}

Leave a Reply