spring jdbc example query update queryForObject queryForList batchUpdate beanPropertyRownMapper

											
																	What are we going to learn?
																	
																		   We are going to see how to do database operations using Spring JDBC template. We will look at 
																
																			update

																			query

																			queryForObject

																			queryForList

																			rowMappers

																			BeanPropertyRowMapper

																	We will see the database operations in action for table 

CREATE TABLE employee (  employee_id int(11) ,  name varchar(45) ,  age int(11) ) 

We will use the following model class.
																		Employee Model Class

																			package com.javavision.SpringJDBC.model;

public class Employee {
	public String name;
	public long employeeId;
	public int age;
	
	public Employee(){
		
	}
	
	public Employee(String name, long employeeId, int age) {
		super();
		this.name = name;
		this.employeeId = employeeId;
		this.age = age;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public long getEmployeeId() {
		return employeeId;
	}
	public void setEmployeeId(long employeeId) {
		this.employeeId = employeeId;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	
}
																		
update -- Issue a single SQL update operation (such as an insert, update or delete statement).

																			Parameters : 

																					SQl statement

																					Array of arguments to bind to the query

																				Return Value :

																				     The number of rows affected.

public int insertEmployee(Employee employee) {

 	String insert_employee = "INSERT INTO employee(employee_id,name,age)VALUES(?,?,?);";
	int count = getJdbcTemplate().update( insert_employee,
	new Object[] { employee.getEmployeeId(), employee.getName(),employee.getAge() });
	return count;
}

																			public int updateEmployee(long employeeId, String newName) {
  	String update_employee = "UPDATE employee set name =? where employee_id = ?;";
	int count = getJdbcTemplate().update( update_employee,new Object[] { newName, employeeId });
	return count;
}

public int deleteEmployee(long employeeId) {
  	String delete_employee = "DELETE from employee where employee_id = ?;";
	int count = getJdbcTemplate().update( delete_employee,new Object[] { employeeId });
	return count;
}

queryForObject: 

																			Parameters : 

																					SQl statement

																					rowMapper - object that will map one object per row

																				Return Value :

																				     The single mapped object. 

																				NOTE : 

																				    IncorrectResultSizeDataAccessException exception will be thrown if the query doesn't exactly return one row.

public Employee getEmployee(long employeeId) {
Employee emp;
		try {
			emp = getJdbcTemplate().queryForObject(
					"select * from employee where employee_id = " + employeeId,
					new RowMapper<Employee>() {
						public Employee mapRow(ResultSet rs, int rownumber)
								throws SQLException {
							Employee e = new Employee();
							e.setEmployeeId(rs.getLong("employee_id"));
							e.setName(rs.getString("name"));
							e.setAge(rs.getInt("age"));
							return e;
						}
					});
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
		return emp;
	}

																			In the above example, we have written an rowmapperwhich will be called on the execution of the sql statement. We construct our return type by populate it with the values from the result set row.

																			query + rowmapper  to return list of Objects

public List<Employee> getEmployees(int age) {
	   List<Employee> empList = getJdbcTemplate().query(
			"select * from employee where age = " + age,
	                    new RowMapper<Employee>() {
				public Employee mapRow(ResultSet rs, int rownumber)
						throws SQLException {
					Employee e = new Employee();
					e.setEmployeeId(rs.getLong("employee_id"));
					e.setName(rs.getString("name"));
					e.setAge(rs.getInt("age"));
					return e;
				}
			});		
		return empList;
	}

																		queryForList

																			Parameters : 

																					SQl statement

																					The type of the element in the result.

																				Return Value :

																				     The number of rows affected.

public List<String> getEmployeeNames() {
	List<String> names = getJdbcTemplate().queryForList("select name from employee;",String.class);
	return names;
}

																		batchUpdate

																			Parameters : 

																					PreparedStatement that will be used.

																					BatchPreparedStatementSetter that is used to iterate through the ist and set the values.
																				
																				Return Value :

																				     Array of number of rows affected.

public int[] addEmployees(final List<Employee> employees) {
String insert_employee = "INSERT INTO employee(employee_id,name,age)VALUES(?,?,?);";
		int[] count = getJdbcTemplate().batchUpdate(insert_employee,
				new BatchPreparedStatementSetter() {
					public void setValues(java.sql.PreparedStatement pstm, int i)
							throws SQLException {
						Employee employee = employees.get(i);
						pstm.setLong(1, employee.getEmployeeId());
						pstm.setString(2, employee.getName());
						pstm.setInt(3, employee.getAge());
					}

					public int getBatchSize() {
						return employees.size();
					}
				});
		return count;
	}
BeanPropertyRowMapper

																		Parameters : 

																				SQl statement.

																																								BeanPropertyRowMapper with type of the class that each row should be mapped to.
																			
																			Return Value :

																			     List of Objects.

public List<Employee> getEmployeesWithBeanPropertyRowMapper(int age) {
	List<Employee> empList = getJdbcTemplate().query(
			"select * from employee where age = " + age,
			new BeanPropertyRowMapper(Employee.class));
	return empList;
}

																	Note: When using BeanPropertRowMapper with the mapping class, the mapping of names between the class and table is very important.

																	Java class field employeeId is acceptable for table field employee_id

																	If you want the names to match you can sql "as" to name the required field with Java class field.

																	Ex : select employee_id as employeeId, name, age from employee

																	Our Complete DAO Implementation class:

package com.javavision.SpringJDBC.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.javavision.SpringJDBC.model.Employee;

public class EmployeeDAOImpl extends JdbcDaoSupport implements EmployeeDAO {

	public int insertEmployee(Employee employee) {

		String insert_employee = "INSERT INTO employee(employee_id,name,age)VALUES(?,?,?);";
		int count = getJdbcTemplate().update(insert_employee,
				new Object[] { employee.getEmployeeId(), employee.getName(), employee.getAge() });
		return count;
	}

	public Employee getEmployee(long employeeId) {
		Employee emp;
		try {
			emp = getJdbcTemplate().queryForObject("select * from employee where employee_id = " + employeeId, new RowMapper<Employee>() {
				public Employee mapRow(ResultSet rs, int rownumber) throws SQLException {
					Employee e = new Employee();
					e.setEmployeeId(rs.getLong("employee_id"));
					e.setName(rs.getString("name"));
					e.setAge(rs.getInt("age"));
					return e;
				}
			});
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
		return emp;
	}

	public int updateEmployee(long employeeId, String newName) {
		String update_employee = "UPDATE employee set name =? where employee_id = ?;";
		int count = getJdbcTemplate().update(update_employee, new Object[] { newName, employeeId });
		return count;
	}

	public int deleteEmployee(long employeeId) {
		String delete_employee = "DELETE from employee where employee_id = ?;";
		int count = getJdbcTemplate().update(delete_employee, new Object[] { employeeId });
		return count;
	}

	public List<Employee> getEmployees(int age) {
		List<Employee> empList = getJdbcTemplate().query("select * from employee where age = " + age, new RowMapper<Employee>() {
			public Employee mapRow(ResultSet rs, int rownumber) throws SQLException {
				Employee e = new Employee();
				e.setEmployeeId(rs.getLong("employee_id"));
				e.setName(rs.getString("name"));
				e.setAge(rs.getInt("age"));
				return e;
			}
		});
		return empList;
	}

	public List<String> getEmployeeNames() {
		List<String> names = getJdbcTemplate().queryForList("select name from employee;", String.class);
		return names;
	}

	public int[] addEmployees(final List<Employee> employees) {

		String insert_employee = "INSERT INTO employee(employee_id,name,age)VALUES(?,?,?);";
		int[] count = getJdbcTemplate().batchUpdate(insert_employee, new BatchPreparedStatementSetter() {
			public void setValues(java.sql.PreparedStatement pstm, int i) throws SQLException {

				Employee employee = employees.get(i);

				pstm.setLong(1, employee.getEmployeeId());
				pstm.setString(2, employee.getName());
				pstm.setInt(3, employee.getAge());
			}

			public int getBatchSize() {
				return employees.size();
			}

		});
		return count;
	}

	public List<Employee> getEmployeesWithBeanPropertyRowMapper(int age) {
		List<Employee> empList = getJdbcTemplate().query("select * from employee where age = " + age,
				new BeanPropertyRowMapper(Employee.class));
		return empList;
	}
}