|
Recipe 10.3. Mapping SQL Data to Java ObjectsProblemYou want to map SQL statements to Java objects without having to employ a full-blown object-relational mapping framework. SolutionUse iBATIS SQL maps. DiscussionThere 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 propertiesdriver=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 tableExample 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 objectpackage 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 iBATISpackage 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 DAOpackage 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 iBATISThis 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. See AlsoDocumentation, examples, and tutorials for iBATIS can be found at http://www.ibatis.com. You will also find the iBATIS data access object (DAO) framework. This framework is highly extensible yet provides a simple means of getting started with iBATIS. This DAO layer can even be used against non-iBATIS mapped data. |
|