Wednesday, November 24, 2010

Spring JDBC/DAO Example

Program to retrieve customer details using Spring JDBC  
Database details :  
·         Driver ="com.mysql.jdbc.Driver“            
·         url="jdbc:mysql://localhost:3306/mydatabase“    
·         Username ="root”
·         Password ="password”

Step 1 : Create a table in the database and insert some values
create table customer (
  id varchar(36) not null,
  name varchar(32) not null 
);
insert into customer (id, name) values ('vels','Java vel);
insert into customer (id, name) values ('jjohn','Java John');


Step 2:  Configure the DataSource information into the SpringContext.xml
          This datasource details will be used in Spring JDBC/DAO class
          ....
<bean id="dataSource” class="org.springframework.jdbc.datasource.DriverManagerDataSource">        
                <property name="driverClassName" value="com.mysql.jdbc.Driver" />               
                <property name="url” value="jdbc:mysql://localhost:3306/mydatabase" />      
                <property name="username" value="root" />
                <property name="password" value="password" />
</bean>
                <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" >         
                                <property name="dataSource" ref="dataSource" />
                </bean>

Step 3:  Create a Data Model  for “Customer”
package com.vels.spring.jdbc;
public class Customer {
private String id;
private String name;
public Customer() {
super();
}
public Customer(String id, String name) {
super();
this.id = id;
this.name = name;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String toString(){
    return String.format("%s id= %s, name= %s", getClass().getSimpleName(), this.id, this.name);
}
}

Step 4:  Create a Spring DAO Class
package com.vels.spring.jdbc;
public interface CustomerDAO {
public List<Customer> getCustomers();
}
Step 4: Create Spring DAO Impl class CustomerDAOImpl
package com.vels.spring.jdbc;
public class CustomerDAOImpl implements CustomerDAO{
                 private final JdbcTemplate jdbc;
public CustomerDAOImpl(JdbcTemplate jdbc) {
super();
this.jdbc = jdbc;
}
          }
Step 5:   Add the implementation to the getCusomters() method in the SpringDAO Impl class
          public List<Customer> getCustomers() {
                       List<Customer> result= (List<Customer>) jdbc.query("select id, name from customer”,  
                       new ResultSetExtractorImpl());
                return result;
         }
Step 6: Create a inner class ResultSetExtractorIml into the  CustomerDAOImpl.java :
private class ResultSetExtractorImpl implements ResultSetExtractor{
                                public Object extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                                                List<Customer> list = new ArrayList<Customer>();
                                                while(resultSet.next()){
                                                                Customer customer = new Customer(
                                                                                                resultSet.getString("id"),
                                                                                                resultSet.getString("name"));
                                                                list.add(customer);
                                                }
                                                return list;
                                }
                               
                }

Step 7: add the Spring DAO Details into the SpringContext.xml file
<bean name="customerDAO" class="com.vels.spring.jdbc.CustomerDAOImpl">
                <constructor-arg ref="jdbcTemplate" />
</bean>


The Final DAOImpl calss look like

package com.vels.spring.jdbc;
public class CustomerDAOImpl implements CustomerDAO{
                 private final JdbcTemplate jdbc;
public CustomerDAOImpl(JdbcTemplate jdbc) {
super();
this.jdbc = jdbc;
}
public List<Customer> getCustomers() {
                List<Customer> result= (List<Customer>) jdbc.query("select id, name from customer”, new ResultSetExtractorImpl());
                return result;
}
private class ResultSetExtractorImpl implements ResultSetExtractor{
                                public Object extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                                                List<Customer> list = new ArrayList<Customer>();
                                                while(resultSet.next()){
                                                                Customer customer = new Customer(
                                                                                                resultSet.getString("id"),
                                                                                                resultSet.getString("name"));
                                                                list.add(customer);
                                                }
                                                return list;
                                }             
                }
          }
Step 8 : Write a util class to load the beans definition
package com.vels.spring.util;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class SpringUtil {
                public static final ApplicationContext SPRING_CONTEXT = new ClassPathXmlApplicationContext(new String[]{"config/SpringContext.xml"});
}
Step 9: Write a client class CustomerDAOClient
Public class CustomerDAOClient {
                Public static void main(String [] a){
                                CustomerDAO cust = SpringUtil.SPRING_CONTEXT.getBean("customerDAO ");
                                List<Customer> list = cust.getCustomers();
// iterate the list....
}
}

No comments: