Thursday, September 29, 2011

Using Spring JdbcTemplate to store and retrive values into DB

In This example i will demonstrate you Spring example to insert and select values from oracle DB. The insertForum() method below shows the amount of code you need to write to insert data using JDBC.

1. Create Java POJO Class ex. Forum which contains getter and setter method for field as mentioned below.
    private int forumId;
    private String forumName;
    private String forumDesc;


1.1 create parameterized constructor as below.
  public Forum(int forumId, String forumName, String forumDesc) {
        this.forumId = forumId;
        this.forumName = forumName;
        this.forumDesc = forumDesc;
    }
 

2. Create Interface ex. ForumDAO which contains insert and select methods
package com.learning.dao;
import com.learning.core.Forum;
public interface ForumDAO {

    public void insertForum(Forum forum);
    public Forum selectForum(int forumId);
}


3. Create Imlementation file containting logic to perform insertion and retrival from DB.
package com.learning.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.learning.core.Forum;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

public class ForumDAOImpl implements ForumDAO{

    private JdbcTemplate jdbcTemplate;
    
    public void setDataSource(DataSource dataSource){
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }
    
    public void insertForum(Forum forum) {
        // TODO Auto-generated method stub
        String query = "INSERT INTO FORUMS (FORUM_ID, FORUM_NAME, FORUM_DESC) VALUES (?,?,?)";
        
        jdbcTemplate.update(query,new Object[]{Integer.valueOf(forum.getForumId()),
                forum.getForumName(), forum.getForumDesc()});
    }

    
    public Forum selectForum(int forumId) {
        // TODO Auto-generated method stub
        String query = "SELECT * FROM FORUMS WHERE FORUM_ID=?";
        
        return jdbcTemplate.queryForObject(query, new Object[]{Integer.valueOf(forumId)}, 
        new RowMapper(){            
            public Object mapRow(ResultSet resultSet, int rowNum) throws SQLException {
                // TODO Auto-generated method stub
                return new Forum(resultSet.getInt("FORUM_ID"), resultSet.getString("FORUM_NAME"), 
                        resultSet.getString("FORUM_DESC"));                
            }            
        }        
        );        
    }
}

Here you need to implement the mapRow() method of the RowMapper callback interface. In the mapRow() method, map the single row of the result set to the Forum object. The queryForObject() method takes a sql query, an array of Object that contains values to be bound to indexed parameters of the query and a RowMapper object.

4. Create beans xml e.g. spring-beans.xml in which we contains bean id of class and dataSource information.

The BasicDataSource can be easily configured and supports connection pooling.

5. create Main class to run Application :

ApplicationContext context = new ClassPathXmlApplicationContext("spring-beans.xml");
ForumDAO forumDAO = (ForumDAO) context.getBean("forumDAO");
Forum springForum = new Forum(1,"Spring Forum", "Discuss everything related to Spring");
forumDAO.insertForum(springForum);        
System.out.println(forumDAO.selectForum(1));

Result :

Thanks to Vanilla for providing good guides for forum example.