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....
}
}
