Data Access with Spring 2
In "Struts 2 and Spring Communication" described how to make a struts application ready to use spring and how does Struts Action works in Spring. In this post, we will see how to implement spring data access.
Add: applicationContext.xml, Database-context.xml, TestDAODB2-config.xml, Manager-context.xml
applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
"http://www.springframework.org/dtd/spring-beans.dtd">
<beans default-autowire="autodetect">
<import resource="Database-context.xml"/>
<import resource= "TestDAODB2-context.xml"/>
<import resource= "Manager-context.xml"/>
</beans>
Database-context.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
"http://www.springframework.org/dtd/spring-beans.dtd">
<beans default-autowire="autodetect">
<bean id="dataSource"
class= "org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin:@localhost:1521:XE" />
<property name="username" value="hr"/>
<property name="password" value="hr"/>
</beans>
TestDAODB2-context.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
"http://www.springframework.org/dtd/spring-beans.dtd">
<beans default-autowire="autodetect">
<bean id="testDAODB2" class="example.dao.TestDAODB2">
<property name="dataSource">
<ref bean="dataSource"/>
</property>
<property name="baseFindSQL" value="SELECT EMAIL, LAST_NAME FROM EMPLOYEES" />
<property name="findByUserAndPasswordWhere"value=" WHERE ID = ? AND PASSWORD= ? "/>
</beans>
Note: In above configration, I am injecting SQL query in the DAO class because quries will also be managed from XML file.
TestManager-context.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
"http://www.springframework.org/dtd/spring-beans.dtd">
<beans default-autowire="autodetect">
<bean id="testManagerBean" class="example.manager.TestManager">
<property name="testDAO"/>
<ref bean="testDAODB2"/>
</property>
</beans>
Create Data Access Object: The database access object uses Spring's MappingSQLQuery class to query the database. This class is a reusable query in which concrete subclass must implement the abstract mapRow(..) method to convert each row of supplied Result set into an object.
Alternatively, JdbcTemplate class can be use to query the database.
Common tasks:Alternatively, JdbcTemplate class can be use to query the database.
* 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.
TestDAODB2.java
package example.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Collection;
import java.util.ArrayList;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.MappingSqlQuery;
public class TestDAODB2{
private DataSource ds;
private String baseFindSql;
private String findByUserAndPasswordWhere;
private FindByUserIDQuery findByUserIDQuery;
public Collection find(String empID, String password){
if (findByUserIDQuery == null)
findByUserIDQuery = new FindByUserIDQuery(baseFindSql +
findByUserAndPasswordWhere);
Object[] params=new Object[2];
params[0] = empID;
params[1] = password;
Collection coll = findByUserIDQuery.execute(params);
return coll;
}
protected class FindByUserIDQuery extends MappingSqlQuery{
public FindByUserIDQuery(){
super();
setDataSource(ds);
}
public FindByUserIDQuery(String sql) {
super(ds, sql);
declareParameter(new SqlParameter(Types.CHAR));
declareParameter(new SqlParameter(Types.CHAR));
compile();
}
protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
ArrayList list=new ArrayList();
list.add(rs.getString("LAST_NAME"));
list.add(rs.getString("EMAIL"));
return list;
}
}
public void setFindByUserAndPasswordWhere(String findByUserAndPasswordWhere) {
this.findByUserAndPasswordWhere = findByUserAndPasswordWhere;
}
public void setBaseFindSql(String s){
baseFindSql = s;
}
public void setDataSource(DataSource datasource){
ds = datasource;
}
}
package example.manager;
import example.dao.TestDAO;
import java.util.Collection;
import java.util.Iterator;
public class TestManager {
protected TestDAO testDAO;
public String userAuthentication(String empID, String empName)
{
Collection coll= testDAO.find(empID, empName);
Iterator iterator = coll.iterator();
if(iterator!=null){
return "Succesfull";
}else{
return "Failure";
}
}
public void setTestDAO(TestDAO testDAODB2) {
this.testDAO = testDAODB2;
}
}
This example is executed on Tomcat Server and used follwoing jar files.
JAR Files: In order for you to run this example, you must have the following jar files in your class path: commons-collections.jar || commons-lang.jar || commons-logging.jar || ojdbc14.jar || oro-2.0.8.jar || spring.jar || struts-core-1.3.5.jar || struts-taglib-1.3.5.jar
Learn more about how Spring interacts with database
JAR Files: In order for you to run this example, you must have the following jar files in your class path: commons-collections.jar || commons-lang.jar || commons-logging.jar || ojdbc14.jar || oro-2.0.8.jar || spring.jar || struts-core-1.3.5.jar || struts-taglib-1.3.5.jar
In the diagram, an initial request goes to the Servlet container (such as Jetty or Resin) which is passed through a standard filter chain. The chain includes the (optional) 