Recipe10.3.Mapping SQL Data to Java Objects

Recipe 10.3. Mapping SQL Data to Java Objects

Problem

You want to map SQL statements to Java objects without having to employ a full-blown object-relational mapping framework.

Solution

Use iBATIS SQL maps.

Discussion

There is a middle-ground in Java database access, a shadowy land that lies between straight JDBC and full-blown ORM, a place known as the iBATIS zone. iBATIS (pronounced "eye-bay-tis"), created by Clinton Begin, provides an elegant framework for mapping SQL statements and results to Java objects. It offers the simplicity and control of straight JDBC, yet it supports data mapping, caching, and transactions, features usually available from complex object-relational mapping tools. This recipe introduces you to the core feature of iBATIS: SQL maps. It doesn't delve into the full-functionality of iBATIS; you can find additional reference documentation and tutorials online at http://www.ibatis.com.

SQL maps allow you to specify how Java objects map to the inputs and outputs of an SQL statement. The inputs traditionally take the form of parameters bound to an SQL where clause. SQL maps let you map object properties to statement parameters. For output, SQL maps let you specify a Java object that maps to the result set returned by an SQL statement.

You can use iBATIS to access and display the data used in Recipe 10-2. Start by downloading iBATIS from http://www.ibatis.com. For this example, iBATIS Version 2.0.7 was used. Extract the distribution into a directory such as /ibatis-2.0.7. Copy the ibatis-sqlmap-2.jar and ibatis-common-2.jar files to your WEB-INF/lib folder.

Next, you create the configuration files that specify how you connect to your database. iBATIS, by default, searches for its configuration files in the application's class path. This example stores these files in the application's top-level src directory; when the application is compiled, the configuration files are copied to the WEB-INF/classes directory.

iBATIS reads database connection settings from a properties file. Example 10-4 shows the sqlMapConfig.properties used to connect to a MySQL database.

Example 10-4. iBATIS database configuration properties

driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost/test username=gpburdell password=matech

iBATIS reads and uses these properties in its XML configuration file. Example 10-5 shows the sqlMapConfig.xml used in this recipe.

Example 10-5. iBATIS SQL map configuration file

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMapConfig    PUBLIC "-//iBATIS.com/DTD SQL Map Config 2.0//EN"    "http://www.ibatis.com/dtd/sql-map-config-2.dtd"> <sqlMapConfig>     <properties resource="sqlMapConfig.properties"/>     <transactionManager type="JDBC">         <dataSource type="SIMPLE">             <property name="JDBC.Driver" value="${driver}"/>             <property name="JDBC.ConnectionURL" value="${url}"/>             <property name="JDBC.Username" value="${username}"/>             <property name="JDBC.Password" value="${password}"/>                     </dataSource>     </transactionManager>     <sqlMap resource="UserSqlMap.xml"/> </sqlMapConfig>

The properties element makes the configuration properties of Example 10-4 available as variables (${propertyName}) that can be used throughout the rest of the XML configuration file. The sqlMap element references an SQL Map configuration file for use by this application. Typically, you'll have one SQL map file for each table.

For this example, you want to map the results of selecting all rows from the users table into a collection of objects. Figure 10-5 shows the structure of the users table.

Figure 10-5. Schema of the users table

Example 10-6 shows the UserSqlMap.xml file that enables this mapping.

Example 10-6. iBATIS SQL map for the users table

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMap    PUBLIC "-//iBATIS.com/DTD SQL Map 2.0//EN"    "http://www.ibatis.com/dtd/sql-map-2.dtd"> <sqlMap namespace="UserSqlMap">     <select  result>         SELECT user_name  as username,                first_name as firstName,                last_name  as lastName           FROM users     </select>      </sqlMap>

The select element defines how a select SQL select statement, contained in the element's body, maps to a Java class. When the query is executed, iBATIS will return a collection of Java objects of the type specified by the resultClass attribute. Example 10-7 shows the User object. By using the "column as name" syntax in the query, you automatically map a column to JavaBean property.

Example 10-7. Plain old User object

package com.oreilly.strutsckbk.ch10; public class User {     public String getUsername( ) {         return username;     }     public void setUsername(String username) {         this.username = username;     }     public String getPassword( ) {         return password;     }     public void setPassword(String password) {         this.password = password;     }     public String getFirstName( ) {         return firstName;     }     public void setFirstName(String firstName) {         this.firstName = firstName;     }     public String getLastName( ) {         return lastName;     }     public void setLastName(String lastName) {         this.lastName = lastName;     }     private String username;     private String password;     private String firstName;     private String lastName; }

Now you need to create the data access object that retrieves the collection of users. Example 10-8 shows the action used in this recipe that uses the SQL Maps API to execute the query and map the results.

Example 10-8. Data access object that utilizes iBATIS

package com.oreilly.strutsckbk.ch10; import java.io.Reader; import java.sql.SQLException; import java.util.List; import com.ibatis.common.resources.Resources; import com.ibatis.sqlmap.client.SqlMapClient; import com.ibatis.sqlmap.client.SqlMapClientBuilder; public class MyUserDao {     private static final SqlMapClient sqlMapClient;          static {         try {             Reader reader = Resources.getResourceAsReader("sqlMapConfig.xml");             sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader);         } catch (Exception e) {             e.printStackTrace( );             throw new RuntimeException("Unable to create iBATIS sql map                                         client.", e);         }     }     public List getAllUsers( ) throws SQLException {         return sqlMapClient.queryForList("getAllUsers", null);     } }

This data access object provides a getAllUsers( ) method to retrieve the results returned by the "getAllUsers" SQL map statement shown in Example 10-6. The SqlMapClient serves as a façade over the iBATIS API. This object is thread-safe; you can define and initialize it in a static block and use it throughout your action.

Example 10-9 shows the data access object which utilizes the iBATIS API.

Example 10-9. Simple action that calls the DAO

package com.oreilly.strutsckbk.ch10; import java.util.List; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.struts.action.Action; import org.apache.struts.action.ActionForm; import org.apache.struts.action.ActionForward; import org.apache.struts.action.ActionMapping; public class ViewMyUsersAction extends Action {     public ActionForward execute(ActionMapping mapping,                                   ActionForm form,                                  HttpServletRequest request,                                   HttpServletResponse response) throws                                   Exception {         MyUserDao dao = new MyUserDao( );         List users = dao.getAllUsers( );         request.setAttribute("users", users);         return mapping.findForward("success");     } }

The actual JSP page to render the users object is shown in Example 10-10. Unlike the solution for Recipe 10.2, the object used on the page is a well-known custom business object. You access its properties using run-of-the-mill Struts bean:write or JSTL c:out tags.

Example 10-10. Iterating through the list of users

<%@ page contentType="text/html;charset=UTF-8" language="java" %> <%@ taglib uri="http://struts.apache.org/tags-bean" prefix="bean" %> <%@ taglib uri="http://struts.apache.org/tags-logic" prefix="logic" %> <html> <head>   <title>Struts Cookbook - Chapter 10 : Using iBATIS</title> </head> <body>   <h3>Using iBATIS</h3>   <table border="2">     <tr>       <th>Username</th>       <th>First Name</th>       <th>Last Name</th>     </tr>     <logic:iterate  name="users">       <tr>         <td>           <bean:write name="user" property="username"/>         </td>                   <td>           <bean:write name="user" property="firstName"/>         </td>                   <td>           <bean:write name="user" property="lastName"/>         </td>                 </tr>     </logic:iterate>   </table> </body> </html>

This results in a display like that shown in Figure 10-6.

Figure 10-6. Rendered data retrieved using iBATIS

This recipe hints at the capabilities of iBATIS SQL maps. If your application needs some means of mapping objects to data and back, you should consider iBATIS. It may be exactly what you were looking for.

Recipe10.3.Mapping SQL Data to Java Objects