Data Access with Spring and Struts: Part 2

Part 1 of "Data Access with Spring and struts" described how to make a struts application ready to use spring. In this post, we will see how to implement spring data access.

1. Create a RowMapper: A row mapper is used to map a single row in the ResultSet to any object. The iteration through the result set is taken care of by the JdbcTemplate class.

   public class EmpMapper implements RowMapper {
   public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
    int empNo = rs.getInt(1);
    String empName = rs.getString(2);
    String empJob = rs.getString(3);
    long salary = rs.getLong(4);
    Employee emp = new Employee();
    emp.setEmpId(empNo);
    emp.setEmpJob(empJob);
    emp.setEmpSal(salary);
    emp.setEmpName(empName);
    return emp;
   }
   }

   EmpMapper.java
2. Create the Data Access Object: The Data access object uses spring's JdbcTemplate class to query the database. The JdbcTemplate class helps separate the static parts of JDBC DAO code by performing
   the common boilerplate tasks:
   • Retrieves connections from the datasource.
   • Prepares statement object.
   • Executes SQL CRUD operations.
   • Iterates over result sets and populates the results in standard collection objects.
   • Handles SQLException and translates it into a more explicit exception in the spring exception hierarchy.

   public class DAO extends JdbcDaoSupport {
   public long empId;
   public String empName;
   public String empJob;
   public long empSal;
   public String SQL = "SELECT EMPNO, ENAME, JOB, SAL " + "FROM EMP WHERE SAL >= ?";
   public List getData(long minSal) {
    Long params[] = { minSal };
    JdbcTemplate daoTmplt = getJdbcTemplate();
    return daoTmplt.query(SQL, params, new EmpMapper());
   }
   }

   DAO.java
   Note that the DAO class has to extend JdbcDaoSupport, which defines the getJdbcTemplate() method.
3. Create the Business Interface: The business interface class acts as a simple facade to the DAO layer.

   public class BusinessInterface {
    DAO empDAO;
    public List getData(long minSal){
      List empList = empDAO.getData(minSal);
      return empList;
    }
    public void setDao(DAO empDAO){
      this.empDAO = empDAO;
    }
   }

   BusinessInterface.java
4. Add the Action class, Business Interface and DAO to Spring

   <?xml version="1.0" encoding="UTF-8"?>
   <!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
   "http://www.springframework.org/dtd/spring-beans.dtd">
   <beans>
   <bean id="dataSource"
    class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <property name="driverClassName">
      <value>oracle.jdbc.driver.OracleDriver</value>
    </property>
    <property name="url">
      <value>jdbc:oracle:thin:@localhost:1521:orcl</value>
    </property>
    <property name="username">
      <value>scott</value>
    </property>
    <property name="password">
      <value>tiger</value>
    </property>
   </bean>
   
   <!-- Configure DAO -->
   <bean id="empDao" class="data.DAO">
    <property name="dataSource">
      <ref bean="dataSource"></ref>
    </property>
   </bean>
   <!-- Configure Business Service -->
   <bean id="businessInterface" class="business.BusinessInterface">
    <property name="dao">
      <ref bean="empDao"></ref>
    </property>
   </bean>
   <bean name="/search" class="actions.SearchAction">
    <property name="businessInterface">
      <ref bean="businessInterface" />
    </property>
   </bean>
   </beans>

   WEB-INF/applicationContext.xml
   Note that SearchAction is defined here, instead of in the struts-config.xml. The bean name "/search" is used to as a link between this file and struts-config.xml.


5. Create the Sorter class: This is used in the JSP for sorting the result list.

   public class Sorter {
   public static List sort(List list, String sortBy) {
    Comparator comp = getComparator(sortBy);
    Collections.sort(list, comp);
    return list;
   }
   
   private static Comparator getComparator(String sortBy) {
    System.out.println("Sort by : " + sortBy);
    if (sortBy == null) {
      return new NameComparator();
    }
    if (sortBy.equals("empName"))
      return new NameComparator();
    if (sortBy.equals("empId"))
      return new IdComparator();
    if (sortBy.equals("empSal"))
      return new SalComparator();
    if (sortBy.equals("empJob"))
      return new JobComparator();
   
    return null;
   
   }
   
   private static class NameComparator implements Comparator {
    public int compare(Object emp1, Object emp2) {
      Employee employee1 = (Employee) emp1;
      Employee employee2 = (Employee) emp2;
      return employee1.getEmpName().compareTo(employee2.getEmpName());
    }
   }
   
   private static class IdComparator implements Comparator {
    public int compare(Object emp1, Object emp2) {
      Employee employee1 = (Employee) emp1;
      Employee employee2 = (Employee) emp2;
      return new Long(employee1.getEmpId()).compareTo(new Long(employee2.getEmpId()));
    }
   }
   
   private static class SalComparator implements Comparator {
    public int compare(Object emp1, Object emp2) {
      Employee employee1 = (Employee) emp1;
      Employee employee2 = (Employee) emp2;
      return new Long(employee1.getEmpSal()).compareTo(new Long(employee2.getEmpSal()));
    }
   }
   
   private static class JobComparator implements Comparator {
    public int compare(Object emp1, Object emp2) {
      Employee employee1 = (Employee) emp1;
      Employee employee2 = (Employee) emp2;
      return employee1.getEmpJob().compareTo(employee2.getEmpJob());
    }
   }
   }

   Sorter.java
6. Create the Value object: The Employee.java bean used in the row mapper is shown below.

   public class Employee {
   public long empId;
   public String empName;
   public String empJob;
   public long empSal;
   public long getEmpId() {
    return empId;
   }
   public void setEmpId(long empId) {
    this.empId = empId;
   }
   public String getEmpJob() {
    return empJob;
   }
   public void setEmpJob(String empJob) {
    this.empJob = empJob;
   }
   public String getEmpName() {
    return empName;
   }
   public void setEmpName(String empName) {
    this.empName = empName;
   }
   public long getEmpSal() {
    return empSal;
   }
   public void setEmpSal(long empSal) {
    this.empSal = empSal;
   }
   }

   Employee.java

REQUIREMENTS

1. JAR Files: In order for you to run this example, you must have the following jar files in your class path: displaytag-1.1.jar || commons-chain-1.1.jar || commons-collections.jar || commons-dbcp.jar || commons-digester-1.6.jar || commons-lang.jar || commons-logging.jar || commons-pool-1.3.jar || commons-validator-1.3.0.jar || commons-beanutils.jar || displaytag-export-poi-1.1.jar || jta.jar || ojdbc14.jar || oro-2.0.8.jar || spring.jar || struts-core-1.3.5.jar || struts-taglib-1.3.5.jar || struts-tiles-1.3.5.jar || xml-apis.jar
2. Tomcat Server
3. Java 5.0
4. A log4j configuration file.