使用Spring JdbcTemplate进行操作
1.spring容器的配置
1 <?xml version="1.0" encoding="UTF-8"?> 2 <beans 3 xmlns="http://www.springframework.org/schema/beans" 4 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 5 xmlns:p="http://www.springframework.org/schema/p" 6 xsi:schemaLocation="http://www.springframework.org/schema/beans 7 http://www.springframework.org/schema/beans/spring-beans-3.1.xsd"> 8 9 <!-- 配置jdbc的数据源 --> 10 <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> 11 <property name="driverClassName" value="oracle.jdbc.OracleDriver"></property> 12 <property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl"></property> 13 <property name="username" value="scott"></property> 14 <property name="password" value="123"></property> 15 </bean> 16 17 <!-- 给JdbcTemplate注入数据源 --> 18 <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> 19 <property name="dataSource" ref="dataSource"></property> 20 </bean> 21 22 <!-- dao层 --> 23 <bean id="empDao" class="cn.jdbc.dao.impl.EmpDaoImpl"> 24 <property name="jdbcTemplate" ref="jdbcTemplate"></property> 25 </bean> 26 </beans>
2.dao实现类
1 package cn.jdbc.dao.impl; 2 3 import java.sql.CallableStatement; 4 import java.sql.Connection; 5 import java.sql.ResultSet; 6 import java.sql.SQLException; 7 import java.util.ArrayList; 8 import java.util.List; 9 10 import oracle.jdbc.OracleTypes; 11 import oracle.net.aso.r; 12 13 import org.springframework.dao.DataAccessException; 14 import org.springframework.jdbc.core.CallableStatementCallback; 15 import org.springframework.jdbc.core.CallableStatementCreator; 16 import org.springframework.jdbc.core.JdbcTemplate; 17 import org.springframework.jdbc.core.ResultSetExtractor; 18 import org.springframework.jdbc.core.RowMapper; 19 20 import cn.jdbc.dao.IEmpDao; 21 import cn.jdbc.entity.Emp; 22 23 public class EmpDaoImpl implements IEmpDao { 24 private JdbcTemplate jdbcTemplate; 25 public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { 26 this.jdbcTemplate = jdbcTemplate; 27 } 28 29 @Override 30 public Emp findEmpByEmpno(Short empno) { 31 String sql="select * from emp where empno = ?"; 32 Object[] paramValues={empno}; 33 return jdbcTemplate.queryForObject(sql, paramValues,new RowMapper<Emp>(){ 34 @Override 35 public Emp mapRow(ResultSet resultSet, int arg1) throws SQLException { 36 Emp e=new Emp(); 37 e.setEmpno(resultSet.getShort("empno")); 38 e.setEname(resultSet.getString("ename")); 39 e.setHiredate(resultSet.getDate("hiredate")); 40 e.setJob(resultSet.getString("job")); 41 return e; 42 } 43 }); 44 } 45 46 @Override 47 public List<Emp> findEmps() { 48 String sql="select * from emp"; 49 /*return jdbcTemplate.query(sql, new ResultSetExtractor<List<Emp>>(){ 50 @Override 51 public List<Emp> extractData(ResultSet resultSet) throws SQLException, 52 DataAccessException { 53 List<Emp> list=new ArrayList<Emp>(); 54 while(resultSet.next()){ //循环ResultSet 55 Emp e=new Emp(); 56 e.setEmpno(resultSet.getShort("empno")); 57 e.setEname(resultSet.getString("ename")); 58 e.setHiredate(resultSet.getDate("hiredate")); 59 e.setJob(resultSet.getString("job")); 60 list.add(e); 61 } 62 return list; 63 } 64 });*/ 65 66 return jdbcTemplate.queryForObject(sql, new RowMapper<List<Emp>>(){ 67 @Override 68 public List<Emp> mapRow(ResultSet resultSet, int arg1) throws SQLException { 69 List<Emp> list=new ArrayList<Emp>(); 70 while(resultSet.next()){ //循环ResultSet 71 Emp e=new Emp(); 72 e.setEmpno(resultSet.getShort("empno")); 73 e.setEname(resultSet.getString("ename")); 74 e.setHiredate(resultSet.getDate("hiredate")); 75 e.setJob(resultSet.getString("job")); 76 list.add(e); 77 } 78 return list; 79 } 80 }); 81 } 82 83 @Override 84 public int findCount() { 85 String sql="select count(1) from emp";//在数据库可以这样写count(1) ,hql不能这样写,要写成这样count(*) 86 return jdbcTemplate.queryForInt(sql); 87 } 88 89 @Override 90 public void addEmp(Emp e) { 91 String sql="insert into emp values (?,?,?,?,?,?,?,?)"; 92 Object [] args={e.getEmpno(),e.getEname(),null,null,null,null,null,null}; 93 jdbcTemplate.update(sql, args); 94 } 95 96 @Override 97 public int deleteEmpByEmpNo(Short empno) { 98 String sql="delete emp where empno = ?"; 99 Object [] args={empno}; 100 return jdbcTemplate.update(sql, args); 101 } 102 103 /** 104 * 使用jdbc调用存储过程(重点) 105 */ 106 @Override 107 public String callProcedure_selectEmpNameByEmpNo(final Short empno) { 108 return jdbcTemplate.execute(new CallableStatementCreator() { 109 @Override 110 public CallableStatement createCallableStatement(Connection connection)//做调用存储过程的准备 111 throws SQLException { 112 String storedProc="{call selectEmpNameByEmpNo(?,?)}";//调用存储过程的名称 113 CallableStatement cs=connection.prepareCall(storedProc); 114 cs.setShort(1, empno);//为第一个输入参数赋值 115 cs.registerOutParameter(2, OracleTypes.VARCHAR);//给输出参数注册 116 return cs; 117 } 118 }, new CallableStatementCallback<String>() { 119 @Override 120 public String doInCallableStatement(CallableStatement cs) 121 throws SQLException, DataAccessException { 122 cs.execute(); 123 return cs.getString(2);//获取输出 参数并返回 124 } 125 }); 126 } 127 128 }
3.oracle数据库的存储过程
1 create or replace procedure selectEmpNameByEmpNo 2 ( 3 eno in emp.empno%type, --输入参数 4 out_empname out emp.ename%type --输出参数 5 ) 6 is 7 begin 8 select ename into out_empname from emp where empno=eno; 9 end;
4.测试
1 package cn.jdbc.test; 2 3 import java.util.ArrayList; 4 import java.util.List; 5 6 import org.junit.Test; 7 import org.springframework.context.ApplicationContext; 8 import org.springframework.context.support.ClassPathXmlApplicationContext; 9 10 import cn.jdbc.dao.IEmpDao; 11 import cn.jdbc.entity.Emp; 12 13 public class TestAll { 14 private IEmpDao empdao; 15 private ApplicationContext ctx=null; 16 public TestAll(){ 17 ctx=new ClassPathXmlApplicationContext("applicationContext.xml"); 18 empdao=(IEmpDao) ctx.getBean("empDao"); 19 } 20 21 /** 22 * 根据id查询 23 */ 24 @Test 25 public void findEmpByEmpno(){ 26 Short empno=7369; 27 Emp e=empdao.findEmpByEmpno(empno); 28 System.out.println(e.getEname()); 29 } 30 31 @Test 32 public void findEmps(){ 33 List<Emp> list=new ArrayList<Emp>(); 34 list=empdao.findEmps(); 35 System.out.println(list.size()); 36 } 37 38 @Test 39 public void findCount(){ 40 int count=empdao.findCount(); 41 System.out.println(count); 42 } 43 44 @Test 45 public void addEmp(){ 46 try{ 47 Emp e=new Emp(); 48 e.setEmpno((short)252); 49 e.setEname("你好"); 50 empdao.addEmp(e); 51 }catch(Exception e){ 52 e.printStackTrace(); 53 } 54 } 55 56 @Test 57 public void deleteEmpByEmpNo(){ 58 Short empno=252; 59 int flag=empdao.deleteEmpByEmpNo(empno); 60 System.out.println(flag); 61 } 62 63 64 /** 65 * 使用jdbc调用存储过程(重点) 66 */ 67 @Test 68 public void callProcedure_selectEmpNameByEmpNo(){ 69 Short empno=7369; 70 String ename=empdao.callProcedure_selectEmpNameByEmpNo(empno); 71 System.out.println(ename); 72 } 73 74 }