使用Spring JdbcTemplate进行操作

1.spring容器的配置

<?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.1.xsd">

    <!-- 配置jdbc的数据源 -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="oracle.jdbc.OracleDriver"></property>
        <property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl"></property>
        <property name="username" value="scott"></property>
        <property name="password" value="123"></property>
    </bean>
    
    <!-- 给JdbcTemplate注入数据源 -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    
    <!-- dao层 -->
    <bean id="empDao" class="cn.jdbc.dao.impl.EmpDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>
</beans>

2.dao实现类

package cn.jdbc.dao.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import oracle.jdbc.OracleTypes;
import oracle.net.aso.r;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;

import cn.jdbc.dao.IEmpDao;
import cn.jdbc.entity.Emp;

public class EmpDaoImpl implements IEmpDao {
    private JdbcTemplate jdbcTemplate;
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    
    @Override
    public Emp findEmpByEmpno(Short empno) {
        String sql="select * from emp where empno = ?";
        Object[] paramValues={empno};
        return jdbcTemplate.queryForObject(sql, paramValues,new RowMapper<Emp>(){
            @Override
            public Emp mapRow(ResultSet resultSet, int arg1) throws SQLException {
                Emp e=new Emp();
                e.setEmpno(resultSet.getShort("empno"));
                e.setEname(resultSet.getString("ename"));
                e.setHiredate(resultSet.getDate("hiredate"));
                e.setJob(resultSet.getString("job"));
                return e;
            }
        });
    }

    @Override
    public List<Emp> findEmps() {
        String sql="select * from emp";
        /*return jdbcTemplate.query(sql, new ResultSetExtractor<List<Emp>>(){
            @Override
            public List<Emp> extractData(ResultSet resultSet) throws SQLException,
                    DataAccessException {
                List<Emp> list=new ArrayList<Emp>();
                while(resultSet.next()){  //循环ResultSet
                    Emp e=new Emp();
                    e.setEmpno(resultSet.getShort("empno"));
                    e.setEname(resultSet.getString("ename"));
                    e.setHiredate(resultSet.getDate("hiredate"));
                    e.setJob(resultSet.getString("job"));
                    list.add(e);
                }
                return list;
            }
        });*/
        
        return jdbcTemplate.queryForObject(sql, new RowMapper<List<Emp>>(){
            @Override
            public List<Emp> mapRow(ResultSet resultSet, int arg1) throws SQLException {
                List<Emp> list=new ArrayList<Emp>();
                while(resultSet.next()){  //循环ResultSet
                    Emp e=new Emp();
                    e.setEmpno(resultSet.getShort("empno"));
                    e.setEname(resultSet.getString("ename"));
                    e.setHiredate(resultSet.getDate("hiredate"));
                    e.setJob(resultSet.getString("job"));
                    list.add(e);
                }
                return list;
            }
        });
    }

    @Override
    public int findCount() {
        String sql="select count(1) from emp";//在数据库可以这样写count(1) ,hql不能这样写,要写成这样count(*)
        return jdbcTemplate.queryForInt(sql);
    }

    @Override
    public void addEmp(Emp e) {
        String sql="insert into emp values (?,?,?,?,?,?,?,?)";
        Object [] args={e.getEmpno(),e.getEname(),null,null,null,null,null,null};
        jdbcTemplate.update(sql, args);
    }

    @Override
    public int deleteEmpByEmpNo(Short empno) {
        String sql="delete emp where empno = ?";
        Object [] args={empno};
        return jdbcTemplate.update(sql, args);
    }

    /**
     * 使用jdbc调用存储过程(重点)
     */
    @Override
    public String callProcedure_selectEmpNameByEmpNo(final Short empno) {
        return jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection connection)//做调用存储过程的准备
                    throws SQLException {
                String storedProc="{call selectEmpNameByEmpNo(?,?)}";//调用存储过程的名称
                CallableStatement cs=connection.prepareCall(storedProc);
                cs.setShort(1, empno);//为第一个输入参数赋值
                cs.registerOutParameter(2, OracleTypes.VARCHAR);//给输出参数注册
                return cs;
            }
        }, new CallableStatementCallback<String>() {
            @Override
            public String doInCallableStatement(CallableStatement cs)
                    throws SQLException, DataAccessException {
                cs.execute();
                return cs.getString(2);//获取输出 参数并返回
            }
        });
    }

}

3.oracle数据库的存储过程

create or replace procedure selectEmpNameByEmpNo
(
  eno in emp.empno%type,   --输入参数
  out_empname out emp.ename%type  --输出参数
)
is
begin
  select ename into out_empname from emp where empno=eno;
end;

4.测试

package cn.jdbc.test;

import java.util.ArrayList;
import java.util.List;

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

import cn.jdbc.dao.IEmpDao;
import cn.jdbc.entity.Emp;

public class TestAll {
    private  IEmpDao empdao;
    private ApplicationContext ctx=null;
    public TestAll(){
        ctx=new ClassPathXmlApplicationContext("applicationContext.xml");
        empdao=(IEmpDao) ctx.getBean("empDao");
    }
    
    /**
     * 根据id查询
     */
    @Test
    public void findEmpByEmpno(){
        Short empno=7369;
        Emp e=empdao.findEmpByEmpno(empno);
        System.out.println(e.getEname());
    }
    
    @Test
    public void findEmps(){
        List<Emp> list=new ArrayList<Emp>();
        list=empdao.findEmps();
        System.out.println(list.size());
    }
    
    @Test
    public void findCount(){
        int count=empdao.findCount();
        System.out.println(count);
    }
    
    @Test
    public void addEmp(){
        try{
        Emp e=new Emp();
        e.setEmpno((short)252);
        e.setEname("你好");
        empdao.addEmp(e);
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    
    @Test
    public void deleteEmpByEmpNo(){
        Short empno=252;
        int flag=empdao.deleteEmpByEmpNo(empno);
        System.out.println(flag);
    }
    
    
    /**
     * 使用jdbc调用存储过程(重点)
     */
    @Test
    public void callProcedure_selectEmpNameByEmpNo(){
        Short empno=7369;
        String ename=empdao.callProcedure_selectEmpNameByEmpNo(empno);
        System.out.println(ename);
    }
    
}