Jars:-
spring-2.0.6.jar, commons-logging-1.1.jar, jstl.jar, standard.jar,
commons-dbcp-1.2.2.jar, commons-pool-1.2.jar (for dbcp BasicDataSource),
mysql-connector-java-5.0.5-bin.jar, commons-collections-3.1.jar (for MySQL)
To start with JDBC integration in Spring, first you need to configure data source. There are three ways to configure data source.
- Using JNDI
- Using JDBC driver
- Using pooled connection
I will describe only pooled data source here. Example described here is with reference to Spring MVC Tutorial. Configure data source to dispatcher-servlet.xml configuration file.
The driverClassName property specifies the fully qualified name of the JDBC driver class. I am using MySQL database here. The url property specifies the complete JDBC URL for the database. The username and password properties are used to authenticate connection to the database. These four are basic properties for configuring a BasicDataSource. There are many other properties as well, described below.
| Property | What it specifies |
| initialSize | The number of connections created when the pool is started. |
| maxActive | The maximum number of connections that can be allocated from the pool at the same time. If zero, there is no limit. |
| maxIdle | The maximum number of connections that can be idle in the pool without extras being released. If zero, there is no limit. |
| maxOpenPreparedStatements | The maximum number of prepared statements that can be allocated from the statement pool at the same time. If zero, there is no limit. |
| maxWait | How long the pool will wait for a connection to be returned to the pool (when there are no available connections) before an exception is thrown. If –1, wait indefinitely. |
| minEvictableIdleTimeMillis | How long a connection can remain idle in the pool before it is eligible for eviction. |
| minIdle | The minimum number of connections that can remain idle in the pool without new connections being created. |
| poolPreparedStatements | Whether or not to pool prepared statements (boolean). |
Now, inject data source to the DAO class in configuration file which will have code related to database operation. Here, LoginDao is the DAO class and that class we will use in LoginController.
I have created one table ‘emp_login_dtls’ in MySQL database, having columns username and password with few records. Now, you have to write LoginDao class performing some database operation. Here, SELECT statement from above table which will retrieve all username and password to check against username and password from user for login verification.
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class LoginDao{
private DataSource dataSource;
public LoginDao(){
}
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
public String verifyLoginDtls(String username, String password){
Connection cn = null;
Statement st = null;
ResultSet rs = null;
try{
cn = dataSource.getConnection();
st = cn.createStatement();
rs = st.executeQuery(“select username, password from emp_login_dtls;”);
while(rs.next()){
String uname = rs.getString(1);
if(uname.equals(username)){
String passwrd = rs.getString(2);
if(passwrd.equals(password)){
return “Login Successful”;
}
}
}
}catch(SQLException e){
e.printStackTrace();
}finally{
try{
if(rs!=null)
rs.close();
if(st!=null)
st.close();
if(cn!=null)
cn.close();
}catch(SQLException e){
e.printStackTrace();
}
}
return “Login Unsuccessful”;
}
}
LoginController should be like this, which will make use of LoginDao class to return verification message.
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.AbstractController;
public class LoginController extends AbstractController {
private LoginDao loginDao;
public LoginController(){
}
public void setLoginDao(LoginDao loginDao){
this.loginDao = loginDao;
}
protected ModelAndView handleRequestInternal(HttpServletRequest request, HttpServletResponse response) throws Exception {
ModelAndView mv = new ModelAndView();
String verificationMsg = loginDao.verifyLoginDtls(
request.getParameter(“username”),
request.getParameter(“password”));
mv.addObject(“resultMsg”, verificationMsg);
mv.setViewName(“resultPage”);
return mv;
}
}
This finishes JDBC integration, but Spring’s JDBC framework provides Templates which will remove burden of resource management and exception handling. It leaves you write code only related to inserting and fetching data from database. You don’t need to handle any database-related exceptions explicitly because Spring JDBC Framework will handle it for you. All the exceptions thrown by the Spring JDBC Framework are subclasses of DataAccessException. TheDataAccessException is a type of RuntimeException, so you are not forced to handle it. The SQLException is a checked exception, when you throw the SQLException here the Spring JDBC Framework will wrap checked exception,SQLException, inside one of the subclasses of DataAccessException and rethrow it. This eliminates the need to explicitly handle them. There are three types of template classes (given for knowledge from the Book ‘Spring In Action2’):
- JdbcTemplate: The most basic of Spring’s JDBC templates, this class provides simple access to a database through JDBC and simple indexed-parameter queries.
- NamedParameterJdbcTemplate: This JDBC template class enables you to perform queries where values are bound to named parameters in SQL, rather than indexed parameters.
- SimpleJdbcTemplate: This version of the JDBC template takes advantage of Java 5 features such as autoboxing, generics, and variable parameter lists to simplify how a JDBC template is used.
Here, I will discuss JdbcTemplate only. For that you can add following code to dispatcher-servlet.xml configuration file, where dataSource we already created above.
Now, in LoginDao class, only data retrieval code will remain, connection and exception related code will be removed.
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.JdbcTemplate;
public class LoginDao{
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public LoginDao(){
}
public String verifyLoginDtls(String username, String password){
List
for(Map
String uname = row.get(“username”);
if(uname.equals(username)){
String passwrd = row.get(“password”);
if(passwrd.equals(password)){
return “Login Successful”;
}
}
}
return “Login Unsuccessful”;
}
}
You can realize now how efficient JdbcTemplate is as it creates connection and statement and execute query for you. For exception, JdbcTemplate will catch any SQLExceptions that are thrown and then translate the generic SQLException into one of the more specific data access exceptions and rethrow it. Because Spring’s data access exceptions are all runtime exceptions, we didn’t have to catch it in the verifyLoginDtls() method.
There are many methods of JdbcTemplate for various use, you need to study them. Here, I have usedqueryForList(String sql) method which executes a query and returns a result list. For insertion of data to the database for the same table, you can use update(String sql) method like below.
int result = jdbcTemplate .update(“insert into emp_login_dtls(username, password) values (‘”+username+”‘,’”+password+”‘);”);
For using JdbcTemplate like this, you need to be sure that you write JdbcTemplate bean, JdbcTemplate property and setter method for each DAO class in your application. To solve problem of this tedious job, Spring providesJdbcDaoSupport class which gives JdbcTemplate by use of getJdbcTemplate() method. Now, you don’t need to add setter method and only need to extend JdbcDaoSupport.
public class LoginDao extends JdbcDaoSupport {
public LoginDao(){
}
public String verifyLoginDtls(String username, String password){
List
… (same as above)
}
}
In configuration file, you can directly add dataSource property to loginDao. It will internally create a JdbcTemplate instance. This eliminates the need to explicitly declare a JdbcTemplate bean in Spring.
Source Code Download.
Jars used in this project Download, excluding jars already provided in Spring MVC Tutorial.
No comments:
Post a Comment