Spring integration with JDBC

Steps to Integrate Spring with JDBC

Step 1. Add the JDBC configuration in the applicationContext.xml (Spring Configuration file)

Spring allows you to define resources like a JDBC DataSource as beans in an application context. Set up a JDBC DataSource and integrate with JdbcTemplate.

Step 2. Create JdbcTemplate

The JdbcTemplate class is the central class in the JDBC core package.This class executes SQL queries, update statements or stored procedure calls, imitating iteration over ResultSets and extraction of returned parameter values. It also catches JDBC exceptions and translates them to the generic, more informative, exception hierarchy defined in the org.springframework.dao package.JdbcTemplate class are threadsafe once configured. This is important because it means that you can configure a single instance of a JdbcTemplate and then safely inject this shared reference into multiple DAOs .
JdbcTemplate is stateful, in that it maintains a reference to a DataSource, but this state is not conversational state
JdbcTemplate is created in the setter for the DataSource.
Below you can see how you can configure JdbcTemplate.



				public class EmpDAO {

    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

  }

Examples of JdbcTemplate



				public class EmpDAO {

    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }


				
				public int getCount() {
        return this.jdbcTemplate.queryForInt("select count(*) from EMP");
    }

				
				  public void createTable() {
        this.jdbcTemplate.execute("create table EMP (id integer, name varchar(100))");
    }


    public List getList() {
    return this.jdbcTemplate.queryForList("select * from EMP");

	//The list returned would look something like this: 

//[{name=Ram, id=1}, {name=Shyam, id=2}]

}
				
				}

Other Examples


public void setName(int id, String name) {
        this.jdbcTemplate.update("update emp set name = ? where id = ?", new Object[] {name, new Integer(id)});
    }


public Emp findEmployee(long id) {
    String sql = "select id, name from EMP where id = ?";
    
    RowMapper mapper = new RowMapper() {
    
        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            Emp emp = new Emp();
            emp.setId(rs.getInt("id"));
            emp.setName(rs.getString("name"));
            return emp;
        }
    };

public String getName(){
String name = (String) this.jdbcTemplate
    .queryForObject("select name from emp where id = ?", new Object[]{new Integer(12)}, String.class);
retrun name;
}


				
				
				


				this.jdbcTemplate.execute("create table emp (id integer, name varchar(100))");

				

				Call to stored procedure

				this.jdbcTemplate.update("call SUPPORT.REFRESH_EMP_SUMMARY(?)", new Object[]{new Integer(id)});
				

				
				this.jdbcTemplate.update("insert into emp (name) values (?)", new Object[] {"Dakue"});
				

	
this.jdbcTemplate.update("delete from emp");