Category Archives: Mybatis

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

Using Mybatis with Spring

Mybatis is Java Persistence framework, build upn JDBC. I look at it mostly as a bridge between conventional JDBC and an ORM solution like hibernate. The flexibility Mybatis provide is to write queries similar to JDBC, yet takes away all the complexity and boilerplate code for creating and maintaining connections and transactions. It can also map your objects to Tables like ORM solution, without getting into complexities of an ORM fraework, but at a cost of some additional code.

Mappers: Mappers are core of Mybatis implementation. A Mapper class would contain queries to be executed by mybatis. A good practice is to create a mapper for each table in database.

Here is a simple Mapper class

public interface UserMapper {

	/**
	 * This method validates the credentials for any user.
	 * 
	 * @param userName
	 * @param password
	 * @return
	 */
	@Select("select id from myuser where username=#{userName} and password=#{password}")
	public Integer verifyAdminUser(@Param("userName") String userName,
			@Param("password") String password);

	/**
	 * This method returns list of all users.
	 * 
	 * @return
	 */
	@Select("select username, password from myuser")
	public List getUsers();

In the getUsers method, you can see mybatis will automatically convert the results into a Java List with User objects.

Coming to Setting up Mybatis with Spring, if you are using Java based configuration, all you need to add is

@MapperScan("com.test.mapper")

and tell location of your mappers package(s).

In addition you would want spring to manage transactions for you by

@MapperScan("com.test.mapper")
public class MyConfig implements TransactionManagementConfigurer{

//JNDI lookup for database
private DataSource getDataSource() {
		InitialContext ic;
		DataSource ds = null;
		try {
			ic = new InitialContext();
			String jndiName ="jndi";
			ds = (DataSource) ic.lookup(jndiName);
		} 
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return ds;
	}

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

			return new SqlSessionFactoryBuilder().build(config);
		}

	@Override
	public PlatformTransactionManager annotationDrivenTransactionManager() {
		return  new DataSourceTransactionManager(getDataSource());
	}

You are all set to use Mappers just by Autowiring in any class.