Selecting Data

In this section, we discuss how to select rows from the database together with how to pass parameters from DAO implementations to the select statements. We start by discussing select operations from a single table that uses a single domain object, then we move on to discuss selecting data from 1:1, 1:N, and finally M:N database relationships.

Simple Selects

We start with a select all statement that does not need any parameters and selects the data from only one table into one domain object (see Listing 10-12); the only addition to the Test.xml sqlMap file is the select node.

Listing 10-12: sqlMap File for the Test Domain Object

<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" ¿ "http://www.ibatis.com/dtd/sql-map-2.dtd">   <sqlMap>          <typeAlias type="com.apress.prospring.ch12.domain.Test" alias="test"/>          <resultMap  bold">result">         <result property="testId" column="TestId"/>         <result property="name" column="Name"/>         <result property="runDate" column="RunDate"/>     </resultMap>              <select  resultMap="result">         select * from Test     </select> </sqlMap>

This simple <select> node actually tells iBATIS that there is a SQL statement named getAllTests and that this statement is a select statement. The SQL code for the statement is select * from Test and the resultMap is result, which in turn represents test, aka the Test domain object. In Listing 10-13, we implement the TestDao's getAll() method to actually see all this in action.

Listing 10-13: TestDao getAll() iBATIS Implementation

package com.apress.prospring.ch12.data;      import java.util.List;      import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;      import com.apress.prospring.ch12.domain.Test;      public class SqlMapClientTestDao      extends SqlMapClientDaoSupport      implements TestDao {          public List getAll() {         return getSqlMapClientTemplate().queryForList("getAllTests", null);     }          // other TestDao methods are implemented as stubs } 

This is really all the code you need to write! The getAll() method delegates the database call, domain object instantiation to iBATIS. All we need to provide is the name of the statement, which must be defined in one of the sqlMap files referenced from the SqlMapConfig file. iBATIS locates the statement, checks that it is a select statement, performs the database operation, processes the resultSet, and then for each row, instantiates the domain object, sets its properties, and adds it to the resulting List.

In Listing 10-14, we make a final change to the Main class to utilize the new implementation.

Listing 10-14: Modified Main Class

package com.apress.prospring.ch12;      public class Main {          private ApplicationContext context;          private void run() {         System.out.println("Initializing application");         context = new ClassPathXmlApplicationContext("applicationContext.xml");                  System.out.println("Getting testDao");         TestDao testDao = (TestDao)context.getBean("testDao");         List tests = testDao.getAll();         for (Iterator i = tests.iterator(); i.hasNext();) {             Test test = (Test)i.next();             System.out.println(test);         }         System.out.println("Done");     }     // the rest of the code ommited for brevity }

When we run the application, it creates the Spring ApplicationContext, which is used to get a TestDao bean and finally, we call its getAll() method to get a List of Test domain objects. The output is exactly what we expect, as shown in Listing 10-15.

Listing 10-15: Running the Sample Application

Buildfile: d:\projects\pro-spring\ch12\build.xml init: compile:     [javac] Compiling 1 source file to D:\projects\pro-spring\ch12\build run:      [java] Initializing application      [java] Debug messages ommited      [java] Getting testDao      [java] Test { testId: 1, name: foo, runDate: Thu Jan 01 00:00:00 GMT 2004 }      [java] Done BUILD SUCCESSFUL Total time: 3 seconds 

Eureka! The application runs, connects to the database, selects the data, and returns a List of our domain objects. In most cases, however, you do need to filter out the rows that are selected. In other words, you need to pass parameters to the select statements. In Listing 10-16, we begin with a select by primary key operation, implemented in the getById(int) method.

Listing 10-16: sqlMap File for the Test Domain Object

<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" ¿ "http://www.ibatis.com/dtd/sql-map-2.dtd">   <sqlMap>     <!-- as previous -->     <select bold">getTestById" resultMap="result" parameter >         select * from Test where TestId=#value#     </select> </sqlMap>

Here, we added the getTestById select statement with a parameter class of int, which is a type alias for java.lang.Integer. The implementation of the getById(int) method (shown in Listing 10-17) is quite simple as well. All we need to do is pass the primary key value to iBATIS.

Listing 10-17: TestDao getById(int) iBATIS Implementation

package com.apress.prospring.ch12.data;      import java.util.List;      import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;      import com.apress.prospring.ch12.domain.Test;      public class SqlMapClientTestDao      extends SqlMapClientDaoSupport      implements TestDao {          public Test getById(int testId) {         return (Test)getSqlMapClientTemplate().queryForObject(             "getTestById", new Integer(testId));     }          // other TestDao methods are implemented as stubs } 

The implementation calls the queryForObject() method that returns a single java.lang.Object or a null that results from executing the select statement. If more than one row is selected, a DataAccessException is thrown. Even though the result of queryForObject() is java.lang.Object, we can safely cast it to Test because the resultMap used states that the object type is Test. We are also passing an Integer object with its value set to the primary key value as a parameter instance to the iBATIS call. Again, the code is very straightforward and easy to follow.

There are some cases where you will want to pass more than one value to the database operation. You are free to use any type, but in most cases, you will find yourself using either the domain object or a java.util.Map. There is no difference between using a Map or a concrete domain object in sqlMap files; however, in the Java code, you gain type safety if you use the domain objects. The rule of thumb is that if you find yourself updating only a few fields in a table with a large number of columns, you are better off using a Map. If, on the other hand, you are updating all or most of the columns in a table, it is better to use the domain object. In Listing 10-18, we show you both implementations.

Listing 10-18: sqlMap File for the Test Domain Object

<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" ¿ "http://www.ibatis.com/dtd/sql-map-2.dtd">   <sqlMap>     <!-- as previous -->     <select bold">getTestsByNameAndRunDateMap"          resultMap="result" parameter >         select * from Test where Name like #name# and RunDate=#runDate#     </select>          <select bold">getTestsByNameAndRunDateDO"          resultMap="result" parameter >         select * from Test where Name like #name# and RunDate=#runDate#     </select>      </sqlMap>

Here, we added two select statements: one uses the parameterClass of the map, and the other one uses the Test domain object. Apart from this, the body of the select statements is exactly the same. In Listing 10-19, you can see that the Java implementation of TestDao is also very similar.

Listing 10-19: TestDao getByNameAndRunDate*() iBATIS Implementation

package com.apress.prospring.ch12.data;      import java.util.List;      import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;      import com.apress.prospring.ch12.domain.Test;      public class SqlMapClientTestDao      extends SqlMapClientDaoSupport      implements TestDao {          public List getByNameAndRunDateMap(String name, Date runDate) {         Map params = new HashMap();         params.put("name", name);         params.put("runDate", runDate);         return getSqlMapClientTemplate().queryForList(             "getTestsByNameAndRunDateMap", params);     }          public List getByNameAndRunDateDO(String name, Date runDate) {         Test test = new Test();         test.setName(name);         test.setRunDate(runDate);         return getSqlMapClientTemplate().queryForList(             "getTestsByNameAndRunDateMap", test);     }          // other TestDao methods are implemented as stubs }

The two methods demonstrate different ways in which we can pass data to the select statements. In the getByNameAndRunDateMap method, we create a Map instance and put the values required in the select statement, while the code in the getByNameAndRunDateDO method instantiates the Test domain object, sets the property values, and passes the instance of the domain object to the iBATIS call. You'll experience a slight performance hit when you use Map to update the same number of fields. We have tested 25,000 select calls using a Map and the domain object as parameters to the iBATIS calls. The Map implementation took 13,594 ms, whereas the domain object implementation took 12,328 ms.

One-to-One Selects

To demonstrate how to select data in 1:1 mapping, we add another table and another domain object. To demonstrate this, let's create a few tables and domain objects. In Listing 10-20, we create the Customers and CustomerDetails tables.

Listing 10-20: One-to-One Relationship SQL Script

create table CustomerDetails (     CustomerDetailId serial not null,     Data varchar(255) not null,          constraint PK_CustomerDetailId primary key (CustomerDetailId) );      create table Customers (     CustomerId serial not null,     FirstName varchar(50) not null,     LastName varchar(50) not null,     CustomerDetail int not null,     CustomerGossip int null,          constraint PK_CustomerId primary key (CustomerId),     constraint FK_CustomerDetail foreign key (CustomerDetail)          references CustomerDetails(CustomerDetailId) on delete cascade,     constraint FK_CustomerGossip foreign key (CustomerGossip)          references CustomerDetails(CustomerDetailId) on delete cascade );      create index IX_Customers_CustomerDetail on Customers      using btree (CustomerDetail); create index IX_Customers_CustomerGossip on Customers      using btree (CustomerGossip);      insert into CustomerDetails (Data) values ('Doeth!'); insert into CustomerDetails (Data) values ('The Force is strong with him'); insert into CustomerDetails (Data) values ('Will tell for a bottle of Tequilla'); insert into Customers (FirstName, LastName, CustomerDetail, CustomerGossip)      values ('Jan', 'Machacek', 1, null); insert into Customers (FirstName, LastName, CustomerDetail, CustomerGossip)      values ('Rob', 'Harrop', 2, 3);

As you can see from the SQL script, a record in the Customers table must have one record in the CustomerDetails table referenced by CustomerDetail and may have one record in the CustomerDetails table referenced by CustomerGossip.

On the Java front, we create the two domain objects: CustomerDetail and Customer (as shown in Listing 10-21).

Listing 10-21: Customer and CustomerDetail Domain Objects

// Customer.java: package com.apress.prospring.ch12.domain; public class Customer {          private int customerId;     private String firstName;     private String lastName;     private CustomerDetail customerDetail;     private CustomerDetail customerGossip;     // getters and setters as usual }      // CustomerDetail.java: package com.apress.prospring.ch12.domain; public class CustomerDetail {          private int customerDetailId;     private String data; }

The domain objects also override the toString() method to return a nice text representation of the instance data.

Next, in Listing 10-22, we create an sqlMap for the domain objects.

Listing 10-22: Customer.xml sqlMap File

<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" ¿ "http://www.ibatis.com/dtd/sql-map-2.dtd">   <sqlMap>          <typeAlias type="com.apress.prospring.ch12.domain.Customer" alias="customer"/>     <typeAlias type="com.apress.prospring.ch12.domain.CustomerDetail"          alias="customerDetail"/>          <resultMap  >         <result property="customerId" column="CustomerId"/>         <result property="firstName" column="FirstName"/>         <result property="lastName" column="LastName"/>     </resultMap>          <resultMap  >         <result property="customerDetailId" column="CustomerDetailId"/>         <result property="data" column="Data"/>     </resultMap>          <resultMap   extends="result">         <result property="customerDetail.customerDetailId"              column="CustomerDetailId"/>         <result property="customerDetail.data" column="CustomerDetailData"/>         <result property="customerGossip" select="getCustomerGossipById"              column="CustomerGossip"/>     </resultMap>              <select  resultMap="resultDetail" parameter>         select              c.CustomerId as CustomerId,             c.FirstName as FirstName,             c.LastName as LastName,             c.CustomerDetail as CustomerDetail,             c.CustomerGossip as CustomerGossip,             cd.CustomerDetailId as CustomerDetailId,             cd.Data as CustomerDetailData         from              Customers c inner join CustomerDetails cd on              c.CustomerDetail = cd.CustomerDetailId          where             c.CustomerId=#value#     </select>          <select  resultMap="gossipResult"          parameter>         select * from CustomerDetails where CustomerDetailId=#value#     </select>      </sqlMap>

This sqlMap file has a lot of new features, so let's take a closer look at them. First of all, we are using resultMap inheritance. This is useful for situations where you want to create a resultMap that adds more fields to the super resultMap. Let's say you're implementing a search method that returns a list of customers based on their lastName. You are not interested in customerDetail or customerGossip properties, so you can leave them set to their default value, null. However, if you are getting a customer by customerId, you want to get all available information about the customer. This is why we created a simple result resultMap and then created the resultDetail resultMap that extends the result and adds definitions for customerDetail and customerGossip.

In Listing 10-23, you can take a closer look at the resultDetail, which also reveals that we are telling iBATIS that there will always be data for a customerDetail object, while there may not be data for customerGossip.

Listing 10-23: Detail of the resultDetail resultMap

<sqlMap>     <resultMap   extends="result">         <result property="customerDetail.customerDetailId"              column="CustomerDetailId"/>         <result property="customerDetail.data" column="CustomerDetailData"/>         <result property="customerGossip" select="getCustomerGossipById"              column="CustomerGossip"/>     </resultMap> </sqlMap>

The code in bold in Listing 10-23 states that the CustomerDetailId and CustomerDetailData columns will always be present in the resultSet, and that they should be set on the customerDetail object's customerDetailId and data properties. The customerGossip object, on the other hand, should be set to the result of the getCustomerGossipById select statement. This statement takes a single int parameter. The value of this parameter should be taken from the CustomerGossip column of the Customers table. In other words, the customerDetail property is never null, whereas customerGossip can be null. On the other hand, the CustomerDao interface and its implementation are very simple, as you can see in Listing 10-24.

Listing 10-24: The CustomerDao Interface and Its Implementation

// CustomerDao.java package com.apress.prospring.ch12.data;      import com.apress.prospring.ch12.domain.Customer;      public interface CustomerDao {     public Customer getById(int customerId); }      // SqlMapClientCustomerDao.java package com.apress.prospring.ch12.data;      import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport; import com.apress.prospring.ch12.domain.Customer;      public class SqlMapClientCustomerDao      extends SqlMapClientDaoSupport implements CustomerDao {     public Customer getById(int customerId) {         return (Customer)getSqlMapClientTemplate().             queryForObject("getCustomerById", new Integer(customerId));     } }

We must not forget to include the new Customer.xml sqlMap in the SqlMapConfig.xml file and to add the customerDao bean to the applicationContext.xml file. Once all the changes to the configuration files are complete, we can add the code in Listing 10-25 to the Main class.

Listing 10-25: New Code to Test 1:1 Relationship

CustomerDao customerDao = (CustomerDao)context.getBean("customerDao");      Customer janm = customerDao.getById(1); Customer robh = customerDao.getById(2);      System.out.println(janm); System.out.println(robh); 

Running the Main class using the test data from the SQL script produces the following output:

Buildfile: d:\projects\pro-spring\ch12\build.xml init: compile: run:      [java] Initializing application      [java] Customer { customerId: 1, firstName: Jan, lastName: Machacek, ¿ customerDetail: CustomerDetail { customerDetailId: 1, data: Doeth! }, ¿ customerGossip: null }      [java] Customer { customerId: 2, firstName: Rob, lastName: Harrop, ¿ customerDetail: CustomerDetail { customerDetailId: 2, data: ¿ The Force is strong with him }, ¿ customerGossip: CustomerDetail { customerDetailId: 3, data: ¿ Will tell for a bottle of Tequilla } }      [java] Done BUILD SUCCESSFUL Total time: 2 seconds

Performance

There is one performance issue you need to consider: using a select statement to set a property on a domain object results in N+1 select operations being performed. Consider a situation where you load a single User object: an extra query is run to select the UserDetail row to set the userGossip property. This may become a major issue. Imagine a situation where we issue a select statement that returns 100 User domain objects. iBATIS now needs to run 100 separate selects to set the userGossip property, making the total number of queries 101.

We have seen a way around this, though—we can return the userDetail object as a part of the result set and then no more queries need to be executed. Unfortunately, we cannot use inner joins if the 1:1 relationship is optional.

The best way to solve this situation is to set the properties only if you really need them. Use a simplified resultMap that sets only the basic properties for selects that return a large number of rows, and use this basic resultMap as a super map for a detailed map that sets all properties.

One-to-one relationships are not very common; the most common relationship ever is one-to-many, which we are going to discuss next.

One-to-Many Selects

Let's move away from the nasty customerGossip objects we have used in the previous section and implement standard order tables. Each order can have zero or more lines. As you can see in Listing 10-26, adding to SQL script is simple enough.

Listing 10-26: Adding to the Create SQL Script

create table Orders (     OrderId serial not null,     Customer int not null,          constraint PK_OrderId primary key (OrderId),     constraint FK_Customer foreign key (Customer) references Customers(CustomerId) );      create index IX_Orders_Customer on Orders using btree (Customer);      create table OrderLines (     OrderLineId serial not null,     "Order" int not null,     Product varchar(200) not null,      Price decimal(10, 2) not null,          constraint PK_OrderLineId primary key (OrderLineId),     constraint FK_Order foreign key ("Order") references Orders(OrderId) );      create index IX_OrderLines_Order on OrderLines using btree ("Order");      insert into Orders (Customer) values (1); insert into OrderLines ("Order", Product, Price)      values (1, 'Punch people over the internet client application', 19.95); insert into OrderLines ("Order", Product, Price)      values (1, 'The Mangelfreuzer Switch', 12.95);

The Java domain objects are also going to be pretty standard. The Order object is going to contain all the properties for columns in the Orders table plus a List for holding instances of the OrderLine domain object, which, in turn, is going to have properties for all columns in the OrderLines table.

In Listing 10-27, we begin the implementation of the iBATIS mapping files by creating the sqlMap file, Order.xml.

Listing 10-27: The Order.xml sqlMap File

<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" ¿ "http://www.ibatis.com/dtd/sql-map-2.dtd">   <sqlMap>          <typeAlias type="com.apress.prospring.ch12.domain.Order" alias="order"/>     <typeAlias type="com.apress.prospring.ch12.domain.OrderLine"          alias="orderLine"/>          <resultMap  >         <result property="orderId" column="OrderId"/>         <result property="customer" column="Customer"/>         <result property="orderLines" select="getOrderLinesByOrder"              column="OrderId"/>     </resultMap>          <resultMap  >         <result property="orderLineId" column="OrderLineId"/>         <result property="order" column="Order"/>         <result property="product" column="Product"/>         <result property="price" column="Price"/>     </resultMap>              <select  resultMap="result" parameter>         select * from Orders where OrderId=#value#     </select>          <select  resultMap="resultLine" parameter>         select * from OrderLines where "Order"=#value#     </select>      </sqlMap>

The elements in the sqlMap file from Listing 10-28 should be no surprise. We have two types, order and orderLine. We have also declared that to set the orderLines property, iBATIS must execute the getOrderLinesByOrder select statement and add the results of that query to the orderLines List property of the Order object. The domain objects follow the properties we declared in the sqlMap file (as shown in Listing 10-28).

Listing 10-28: Order and OrderLine Domain Objects

// Order.java package com.apress.prospring.ch12.domain;      import java.util.List;      public class Order {          private int orderId;     private int customer;     private List orderLines;          // Getters and Setters }      // OrderLine.java package com.apress.prospring.ch12.domain;      import java.math.BigDecimal;      public class OrderLine {          private int orderLineId;     private int order;     private String product;     private BigDecimal price;          // Getters and Setters }

The OrderDao interface and its implementation are also very simple (see Listing 10-29); it merely passes the required parameters to the iBATIS calls.

Listing 10-29: OrderDao Interface and Its Implementation

// OrderDao.java package com.apress.prospring.ch12.data;      import com.apress.prospring.ch12.domain.Order;           public interface OrderDao {     public Order getById(int orderId); }      // SqlMapClientOrderDao.java package com.apress.prospring.ch12.data;      import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;      import com.apress.prospring.ch12.domain.Order;      public class SqlMapClientOrderDao      extends SqlMapClientDaoSupport implements OrderDao {          public Order getById(int orderId) {         return (Order)getSqlMapClientTemplate().queryForObject(             "getOrderById", new Integer(orderId));     }      }

If we add a reference to the new sqlMap file to SqlMapConfig.xml and add a bean definition for orderDao to the applicationContext.xml file, we can use the code in the Main class (see Listing 10-30) to test the application.

Listing 10-30: Testing Code for the OderDao Implementation

OrderDao orderDao = (OrderDao)context.getBean("orderDao"); Order order = orderDao.getById(1); System.out.println(order);

The result of running this code is the following nice output that shows that Order with OrderId: 1 has two order lines:

Buildfile: d:\projects\pro-spring\ch12\build.xml init: compile:     [javac] Compiling 5 source files to D:\projects\pro-spring\ch12\build run:      [java] Order { orderId: 1, customer: 1, orderLines:          [OrderLine              { orderLineId: 2, order: 1, product: The Mangelfreuzer Switch,  price12.95 },          OrderLine              { orderLineId: 1, order: 1, product: Punch people over the internet ¿ client application, price19.95 }] }      [java] Done BUILD SUCCESSFUL Total time: 4 seconds 

Performance

In one-to-many relationships, you have no way to avoid N+1 selects. However, you can still use the basic resultMap to set the basic properties, and then you can extend this simple resultMap in a detailed resultMap that sets all properties. This makes a lot of sense: if you are displaying a list of orders, you do not really need to know the orderLines. You only need this extra information when you want to display a detailed summary of the order. This usually means selecting an order domain object by its primary key, which results in another query being executed to return all order lines.

Excellent! We already have two relationships: one-to-one and one-to-many. The last one remaining is many-to-many.

Many-to-Many Selects

Finally, a many-to-many select is a simple question of creating two one-to-many selects. For example, consider the User and Role objects. A User can appear in more than one Role and one Role can be assigned to more than one User. We need a linking table UserRole, that creates a 1:N relationship between User and UserRole and a 1:M relationship between UserRole and Role; making the whole relationship M:N.