Spring JDBCTemplate code example – view/download (Github)

In the below code example shows how the Spring JDBCTemplate simplifies the code to perform the database-related operations.

The complete examples running code is available to download from Github

DAO Interface:

package com.spring.jdbc;

import java.util.List;

/**
 *
 * @author codedairy
 */
public interface CustomerDAO {

    public Customer getCustomer(Integer customerId);
    public int createCustomer(Customer newCustomer);
    public List retrieveAllCustomers();

}

Implementation of our DAO class

package com.spring.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

/**
 *
 * @author codedairy
 */
public class CustomerDAOImpl implements CustomerDAO {

    private JdbcTemplate jdbcTemplate;

    private final String select = "SELECT * FROM CUSTOMER WHERE ID= ?";
    private final String insert = "INSERT INTO CUSTOMER( NAME, AGE) VALUES(?,?)";
    private final String retrieveall = "SELECT * FROM CUSTOMER";

    @Override
    public Customer getCustomer(Integer customerId) {

        Customer c = (Customer)getJdbcTemplate().queryForObject(select, new Object[]{customerId}, new RowMapper() {

            @Override
            public Customer mapRow(ResultSet rs, int i) throws SQLException {

                Customer cust = new Customer();
                cust.setId(new Integer(rs.getString("ID")));
                cust.setName(rs.getString("NAME"));
                cust.setAge(new Integer(rs.getString("AGE")));
                return cust;
            }
        });
        return c;
    }

    @Override
    public int createCustomer(Customer newCustomer) {
       return getJdbcTemplate().update(insert, new Object[]{newCustomer.getName(),newCustomer.getAge()});
    }

    @Override
    public List retrieveAllCustomers() {
        List<Map> list = getJdbcTemplate().queryForList(retrieveall);

        List custList = new ArrayList();
        for(Iterator itr = list.iterator(); itr.hasNext();){
            Map map = (Map) itr.next();

            Integer id = (Integer) map.get("ID");
            String name = (String)map.get("NAME");
            Integer age = (Integer)map.get("AGE");

            Customer cust = new Customer(id,name,age);

            custList.add(cust);

        }

        return custList;
    }

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

}

Our Main executable class is:


package com.spring.jdbc.test;

import com.spring.jdbc.Customer;
import com.spring.jdbc.CustomerDAO;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

/**
 *
 * @author codedairy 
 */
public class CustomerExec {
    
    public static void main(String args[]){
        
        
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("customer_jdbc.xml");
        CustomerDAO customerDAO =  (CustomerDAO) applicationContext.getBean("customerdao");
        
        
        Customer customer = new Customer();    
        customer.setAge(20);
        customer.setName("Roger Chick");
        
        Customer customer1 = new Customer();    
        customer1.setAge(30);
        customer1.setName("Stephen Hardy");
        
         Customer customer2 = new Customer();    
        customer2.setAge(30);
        customer2.setName("Kuldip Bajwa");
        
        //CREATE CUSTOMER RECORD EXAMPLE
        System.out.println("Rows affected: " + customerDAO.createCustomer(customer));
        System.out.println("Rows affected: " + customerDAO.createCustomer(customer1));
        
        //RETRIEVE CUSTOMER RECORDS EXAMPLE
        System.out.println("CUSTOMER TABLE RECORDS: "+customerDAO.retrieveAllCustomers().toString());
       
        //QUERYING A PARTICULAR RECORD FROM CUSTOMER TABLE - For e.g., by Customer ID
        System.out.println("Record Found: \n" + customerDAO.getCustomer(3).toString());
        
        
        
    }
}



Leave a Comment