spring 对jdbc的简化

spring.xml

    
    <!-- 加载属性配置文件 -->
    <util:properties id="db" location="classpath:db.properties" />
    
    <!-- 定义数据源 -->
    <bean id="ds" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="#{db.driver}"></property>
        <property name="url" value="#{db.url}"></property>
        <property name="username" value="#{db.user}"></property>
        <property name="password" value="#{db.pwd}"></property>    
    </bean>
    
    <!-- 定义jdbcTemplate -->
    <bean class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="ds"></property>
    </bean>
    
    <!-- 开启注解扫描 -->
    <context:component-scan base-package="com.xms"></context:component-scan>
    
    

empDao.java

package com.xms.dao;

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

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.xms.entity.Emp;

@Repository
public class EmpDao {
    @Resource
    private JdbcTemplate template;
    
    //查询全部员工
    public List<Emp> findAll(){
        String sql="select*from emp";
        
        return template.query(sql, new EmpRowMapper());
    }
    
    
    class EmpRowMapper implements RowMapper<Emp>{
        //将结果集中的记录映射成实体对象
        public Emp mapRow(ResultSet rs, int index) throws SQLException {
            //index代表结果集中记录的下标，从0开始
            System.out.println(index);
            
            Emp emp=new Emp();
            emp.setEmpno(rs.getInt("empno"));
            emp.setEname(rs.getString("ename"));
            emp.setSalary(rs.getDouble("salary"));
            emp.setBonus(rs.getDouble("bonus"));
            emp.setHiredate(rs.getDate("hiredate"));
            emp.setDeptno(rs.getInt("deptno"));
            
            
            return emp;
        }
        
    }
    
    //根据员工号查询员工
    public Emp findByEmpno(int empno){
        String sql="select*from emp where empno=?";
        return template.queryForObject(sql, new Object[]{empno}, new EmpRowMapper());
    }
    
    
    //添加员工
    public void save(Emp emp){
        String sql="insert into emp values(?,?,?,?,?,?)";
        Object[] parameters={
                emp.getEmpno(),emp.getEname(),emp.getSalary(),emp.getBonus(),emp.getHiredate(),emp.getDeptno()
        };
         template.update(sql, parameters);
    }
    
    //修改员工
    public void update(Emp emp){
        String sql="update emp set ename=?,salary=?,bonus=?,hiredate=?,deptno=? where empno=?";
        Object[] parameters={
                emp.getEname(),emp.getSalary(),emp.getBonus(),emp.getHiredate(),emp.getDeptno(),emp.getEmpno()
        };
        template.update(sql,parameters);
        
    }
    
    //删除员工
    public void delete(int empno){
        String sql="delete from emp where empno=?";
        template.update(sql,empno);
    }        
}

TestCase.java

package com.xms.test;

import java.sql.Date;
import java.util.List;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.xms.dao.EmpDao;
import com.xms.entity.Emp;

public class TestCase {

    ApplicationContext ac=new ClassPathXmlApplicationContext("spring.xml");
    EmpDao empDao=ac.getBean("empDao",EmpDao.class);
    
    @Test
    public void testOne(){
        List<Emp> emps=empDao.findAll();
        for(Emp emp:emps){
            System.out.println(emp.getEname()+":" + emp.getSalary());
        }
    }
    
    @Test
    public void testTwo(){
        Emp emp=empDao.findByEmpno(1002);
        System.out.println(emp.getEname()+":"+emp.getSalary());
    }
    
    
    @Test
    public void testThree(){
        Emp emp=new Emp();
        emp.setEname("张三丰");
        emp.setSalary(99999.99);
        emp.setBonus(999.99);
        emp.setHiredate(new Date(System.currentTimeMillis()));
        emp.setDeptno(1);
        empDao.save(emp);
    }
    
    @Test
    public void testFour(){
        Emp emp=empDao.findByEmpno(1005);
        emp.setEname("孙悟空");
        emp.setSalary(88888.88);
        emp.setBonus(888.88);

        empDao.update(emp);
    }
    
    @Test
    public void testFive(){
        empDao.delete(1006);
    }    
}

System.currentTimeMillis()获取的是当前的系统时间，不是网络时间。

Emp.java实体类

package com.xms.entity;

import java.sql.Date;

public class Emp {

    private Integer empno;
    private String ename;
    private Double salary;
    private Double bonus;
    private Date hiredate;
    private Integer deptno;
    public Integer getEmpno() {
        return empno;
    }
    public void setEmpno(Integer empno) {
        this.empno = empno;
    }
    public String getEname() {
        return ename;
    }
    public void setEname(String ename) {
        this.ename = ename;
    }
    public Double getSalary() {
        return salary;
    }
    public void setSalary(Double salary) {
        this.salary = salary;
    }
    public Double getBonus() {
        return bonus;
    }
    public void setBonus(Double bonus) {
        this.bonus = bonus;
    }
    public Date getHiredate() {
        return hiredate;
    }
    public void setHiredate(Date hiredate) {
        this.hiredate = hiredate;
    }
    public Integer getDeptno() {
        return deptno;
    }
    public void setDeptno(Integer deptno) {
        this.deptno = deptno;
    }
    
}

出现syntax异常，说明，SQL语法写错了，或者JdbcTemplate对象参数没写对