java调用存储过程 分类： java 20091112 08:46 479人阅读 评论(1) 收藏

 package com.conn; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import java.sql.Types; public class TestPureJavaConnection { /** * @param args */ public static void main(String[] args) throws Exception{ // TODO Auto-generated method stub String url="jdbc:oracle:thin:@localhost:1521:orcl"; String username="scott"; String password = "abc"; Class.forName("oracle.jdbc.driver.OracleDriver"); Connection conn = DriverManager.getConnection(url,username,password); System.out.println("conn="+conn); //创建sql语句 String sql = "create table ctest1(id number)"; sql = "update emp set ename=upper(ename) where ename='smith'"; //创建Statement对象 Statement stmt = conn.createStatement(); int rowcount = stmt.executeUpdate(sql); System.out.println("rowcount="+rowcount); //关闭结果集和相关的资源 /* sql = "select * from emp where ename=?"; PreparedStatement pstmt = conn.prepareStatement(sql); String[] names = {"SMITH","ALLEN","KING"}; for(int i=0;i<names.length;i++){ pstmt.setString(1, names[i]); ResultSet rs = pstmt.executeQuery(); rs.next(); System.out.println(rs.getString("ename")); rs.close(); } */ //调用存储过程 CallableStatement cstmt = conn.prepareCall("{call proc_gettime(?)}"); cstmt.registerOutParameter(1, Types.VARCHAR); //执行 cstmt.execute(); //取得刚才注册的值 String result = cstmt.getString(1); System.out.println("proc result="+result); cstmt.close(); //调用函数 cstmt = conn.prepareCall("{?=call func_gettime}"); cstmt.registerOutParameter(1, Types.VARCHAR); cstmt.execute(); result = cstmt.getString(1); System.out.println("func result="+result); //调用复杂函数 int empno=7369; double newsal = 500; cstmt = conn.prepareCall("{?=call func_raise_sal(?,?)}"); cstmt.registerOutParameter(1, Types.VARCHAR); cstmt.setInt(2, empno); cstmt.setDouble(3, newsal); cstmt.execute(); result = cstmt.getString(1); System.out.println("修改结果="+result); //statement的批处理 conn.setAutoCommit(false); String[] sqls = {"update emp set ename=ename where empno=7369", "delete from emp where empno=7369", "insert into emp(empno,ename) values(7369,'*Smith*')"}; stmt = conn.createStatement(); for(int i=0;i<sqls.length;i++){ stmt.addBatch(sqls[i]); } //批处理执行（一次发送） boolean succ = true; int[] rows = stmt.executeBatch(); for(int i=0;i<rows.length;i++){ if(rows[i]!=1){ succ = false; break; } } if(succ){ System.out.println("批处理成功"); conn.commit(); } else{ System.out.println("批处理不成功"); conn.rollback(); } conn.setAutoCommit(true); stmt.close(); conn.close(); } }

版权声明：本文为博主原创文章，未经博主允许不得转载。