JdbcTemplate的完整解决方案，NamedParameterJdbcTemplate，带或不带RowMapper Example。
创建员工表
create table employee(
id number(10),
name varchar2(100),
salary number(10)
);
==========================================================================================================================.java================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================
public class Employee {
private int id;
private String name;
private float salary;
//no-arg and parameterized constructors
public Employee(){};
public Employee(int id, String name, float salary){
this.id=id;
this.name=name;
this.salary=salary;
}
//getters and setters
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public float getSalary() {
return salary;
}
public void setSalary(float salary) {
this.salary = salary;
}
public String toString(){
return id+" "+name+" "+salary;
}
}
============================================================================.java================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
public class EmployeeDao {
private JdbcTemplate jdbcTemplate;
private NamedParameterJdbcTemplate nameTemplate;
public void setnameTemplate(NamedParameterJdbcTemplate template) {
this.nameTemplate = template;
}
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
// BY using JdbcTemplate
public int saveEmployee(Employee e){
int id = e.getId();
String name = e.getName();
float salary = e.getSalary();
Object p[] = {id, name, salary};
String query="insert into employee values(?,?,?)";
return jdbcTemplate.update(query, p);
/*String query="insert into employee values('"+e.getId()+"','"+e.getName()+"','"+e.getSalary()+"')";
return jdbcTemplate.update(query);
*/
}
//By using NameParameterTemplate
public void insertEmploye(Employee e) {
String query="insert into employee values (:id,:name,:salary)";
Map<String,Object> map=new HashMap<String,Object>();
map.put("id",e.getId());
map.put("name",e.getName());
map.put("salary",e.getSalary());
nameTemplate.execute(query,map,new MyPreparedStatement());
}
// Updating Employee
public int updateEmployee(Employee e){
String query="update employee set name='"+e.getName()+"',salary='"+e.getSalary()+"' where id='"+e.getId()+"' ";
return jdbcTemplate.update(query);
}
// Deleting a Employee row
public int deleteEmployee(Employee e){
String query="delete from employee where id='"+e.getId()+"' ";
return jdbcTemplate.update(query);
}
//Selecting Single row with condition and also all rows
public int selectEmployee(Employee e){
//String query="select * from employee where id='"+e.getId()+"' ";
String query="select * from employee";
List<Map<String, Object>> rows = jdbcTemplate.queryForList(query);
for(Map<String, Object> row : rows){
String id = row.get("id").toString();
String name = (String)row.get("name");
String salary = row.get("salary").toString();
System.out.println(id + " " + name + " " + salary );
}
return 1;
}
// Can use MyrowMapper class an implementation class for RowMapper interface
public void getAllEmployee()
{
String query="select * from employee";
List<Employee> l = jdbcTemplate.query(query, new MyrowMapper());
Iterator it=l.iterator();
while(it.hasNext())
{
Employee e=(Employee)it.next();
System.out.println(e.getId()+" "+e.getName()+" "+e.getSalary());
}
}
//Can use directly a RowMapper implementation class without an object creation
public List<Employee> getAllEmployee1(){
return jdbcTemplate.query("select * from employee",new RowMapper<Employee>(){
@Override
public Employee mapRow(ResultSet rs, int rownumber) throws SQLException {
Employee e=new Employee();
e.setId(rs.getInt(1));
e.setName(rs.getString(2));
e.setSalary(rs.getFloat(3));
return e;
}
});
}
// End of all the function
}
================================================================================================================================================================.java================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
public class MyrowMapper implements RowMapper<Employee> {
@Override
public Employee mapRow(ResultSet rs, int rownumber) throws SQLException
{
System.out.println("mapRow()====:"+rownumber);
Employee e=new Employee();
e.setId(rs.getInt("id"));
e.setName(rs.getString("name"));
e.setSalary(rs.getFloat("salary"));
return e;
}
}
==========================================================================================================================.java================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.PreparedStatementCallback;
public class MyPreparedStatement implements PreparedStatementCallback<Object> {
@Override
public Object doInPreparedStatement(PreparedStatement ps)
throws SQLException, DataAccessException {
return ps.executeUpdate();
}
}
=========================================================================================================.java================================================================================================================================================================================================================================================================================================================================================================================================================================================================================
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class Test {
public static void main(String[] args) {
ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext.xml");
EmployeeDao dao=(EmployeeDao)ctx.getBean("edao");
// By calling constructor for insert
/*
int status=dao.saveEmployee(new Employee(103,"Ajay",35000));
System.out.println(status);
*/
// By calling PreparedStatement
dao.insertEmploye(new Employee(103,"Roh",25000));
// By calling setter-getter for update
/*
Employee e=new Employee();
e.setId(102);
e.setName("Rohit");
e.setSalary(8000000);
int status=dao.updateEmployee(e);
*/
// By calling constructor for update
/*
int status=dao.updateEmployee(new Employee(102,"Sadhan",15000));
System.out.println(status);
*/
// Deleting a record
/*
Employee e=new Employee();
e.setId(102);
int status=dao.deleteEmployee(e);
System.out.println(status);
*/
// Selecting single or all rows
/*
Employee e=new Employee();
e.setId(102);
int status=dao.selectEmployee(e);
System.out.println(status);
*/
// Can use MyrowMapper class an implementation class for RowMapper interface
dao.getAllEmployee();
// Can use directly a RowMapper implementation class without an object creation
/*
List<Employee> list=dao.getAllEmployee1();
for(Employee e1:list)
System.out.println(e1);
*/
}
}
================================================================================================================================================.xml================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================
<?xml version="1.0" encoding="UTF-8"?>
<beans
xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:p="http://www.springframework.org/schema/p"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">
<bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@localhost:1521:xe" />
<property name="username" value="hr" />
<property name="password" value="hr" />
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="ds"></property>
</bean>
<bean id="nameTemplate"
class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
<constructor-arg ref="ds"></constructor-arg>
</bean>
<bean id="edao" class="EmployeeDao">
<!-- Can use both -->
<property name="nameTemplate" ref="nameTemplate"></property>
<property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>
===================================================================
