JdbcTemplate

JdbcTemplate is used to access database using JDBC

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.
The PreparedStatementCreator callback interface creates a prepared statement given a Connection provided by this class.
CallableStatementCreator interface which creates callable statement.
The RowCallbackHandler interface extracts values from each row of a ResultSet

Below is the steps for JdbcTemplate configuration and Excecute Queries

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");

PreparedStatementCreator Example

The method takes a PreparedStatementCreator as its first argument, and this is the way the required insert statement is specified. The other argument is a KeyHolder, which will contain the generated key on successful return from the update.



					final String INSERT_SQL = "insert into Emp (name) values(?)";


KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
    new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps =
                connection.prepareStatement(INSERT_SQL);
            ps.setString(1, "Ram");
            return ps;
        }
    },
    keyHolder);