Example of Executing SQL statements:-
This section provides some examples of JdbcTemplate class usage. These examples
are not an exhaustive list of all of the functionality exposed by the
JdbcTemplate.
Here Let us see how we can perform CRUD (Create, Read, Update and Delete)
operation on database tables using SQL and jdbcTemplate object.
Querying for an integer:
String SQL = "select count(*) from Employee";
int rowCount = jdbcTemplateObject.queryForInt( SQL );
Querying for a long:
String SQL = "select count(*) from Employee";
long rowCount = jdbcTemplateObject.queryForLong( SQL );
Query using a bind variable:
String SQL = "select salary from Employee where id = ?";
int salary = jdbcTemplateObject.queryForInt(SQL, new Object[]{10});
Querying for a String:
String SQL = "select name from Employee where id = ?";
String name = jdbcTemplateObject.queryForObject(SQL, new Object[]{10}, String.class);
Querying for Inserting a row into the table:
String SQL = "insert into Employee (name, salary) values (?, ?)";
jdbcTemplateObject.update( SQL, new Object[]{"Vipul", 2000} );
Querying for Updating a row into the table:
String SQL = "update Employee set name = ? where id = ?";
jdbcTemplateObject.update( SQL, new Object[]{"Aryan", 15} );
Querying for Deletng a row from the table:
String SQL = "delete Employee where id = ?";
jdbcTemplateObject.update( SQL, new Object[]{10} );
Querying and returning an object:
String SQL = "select * from Employee where id = ?";
Employee emp = jdbcTemplateObject.queryForObject(SQL, new Object[]{10}, new EmployeeMapper());
public class EmployeeMapper implements RowMapper<Employee> {
public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
Employee emp = new Employee();
emp.setID(rs.getInt("id"));
emp.setName(rs.getString("name"));
emp.setSalary(rs.getInt("salary"));
return emp; } }
Querying and returning multiple objects:
String SQL = "select * from Employee";
List<Employee> emp = jdbcTemplateObject.query(SQL, new EmployeeMapper());
public class EmployeeMapper implements RowMapper<Employee> {
public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
Employee emp = new Employee();
emp.setID(rs.getInt("id"));
emp.setName(rs.getString("name"));
emp.setSalary(rs.getInt("salary"));
return emp; } }