June 15, 2010

How to retrieve LabelValueBeans from SQL using MyBatis

In this article I want to show you how you can use MyBatis and Strut's LabelValueBean to fetch your SQL results directly into a LabelValueBean Array.

You may find this useful in cases where you are loading data from a simple mapping table.

In my case have a mapping table which contains two columns search_type_id and search_type_name.

search_type_id search_type_name
1 Search Type 1
2 Search Type 2
3 Search Type 3

So here's how to fetch your SQL results directly into a LabelValueBean Array.

Step 1. Define type mappings shortcuts

In order to make thing easier, use typeAliases in your MyBatis configuration file, so you don't have to type your fully qualified class names.

In our case we want MyBatis to know that LabelValueBean refers to org.apache.struts.util.LabelValueBean

Add the following to the configuration file

 <?xml version="1.0" encoding="UTF-8" ?>
 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
 <configuration>
 ...
  <typeAliases>
   <typeAlias alias="LabelValueBean" type="org.apache.struts.util.LabelValueBean"/>
  </typeAliases>
 ...
  <!-- define the mapper xml files you want to use -->
  <mappers>     
   <mapper resource="com/yourproject/package/TestMappers.xml"/>
  </mappers>
   
 </configuration>

Step 2. Create your Mapper definitions

In your mapper xml file TestMappers.xml you can now define the sql statement you want to retreive into an Array of LabelValueBean objects:

 <?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="com.yourproject.com.TestMapper">
 
  <select id="selectSearchTypes" resultType="LabelValueBean">
   select  search_type_id as "value",
     search_type_name as "label" 
   from search_type
  </select>
  
 </mapper>

Step 3. The Java code

Now you are ready to let MyBatis do its job and take care of sending the sql and retreiving the results form the database:

First, make sure you initialize your MyBatis environment first

 String resource = "configuration.xml";
 Reader reader = Resources.getResourceAsReader(resource);  
 SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(reader);

Second, call your mapper's sql statement through MyBatis's awesome methods.

If you use jUnit4 you can use the test method below, but you can put the code anywhere you want (eg. inside a DAO class) 

 @Test
 public void testGetSearchTypes(){
  SqlSession session = sf.openSession();
  try
  {   
   ArrayList<LabelValueBean> searchTypes = (ArrayList<LabelValueBean>)session.selectList("com.yourproject.package.TestMappers.selectSearchTypes"); 
   assertNotNull(searchTypes);        
   printLvbArray(list);
  }
  finally
  {
   session.close();
  }  
 }
 
 //Utility method to dump your array to a log
 private static void printLvbArray(ArrayList<LabelValueBean> a){
  for(int i = 0;i<a.size();i++){
   log.info(a.get(i).getLabel() + ":" + a.get(i).getValue());
  }
 }

Watch out for this

MyBatis apparently won't complain if it can't find a bean property name.

The first time I wrote the code I mistakenly put in name instead of label as the LabelValueBean property to associate results from the search_type_id field. 

 <select id="selectSearchTypes" resultType="LabelValueBean">
  select  search_type_id as "name",
    search_type_name as "label" 
  from search_type
 </select>

When I ran it through the printLvbArray() method above I got this

null Search Type 1
null Search Type 2
null Search Type 3

instead of this:

1 Search Type 1
2 Search Type 2
3 Search Type 3

The output of course corresponds to the search_type_id,search_type_name columns in my original table.

Tags: ,