Java代码调用存储过程和存储方法

来源：互联网发布：kuaidial数据库2017 编辑：程序博客网时间：2024/06/07 18:19

准备一个oracle 的JDBC jar 包：ojdbc14_11g.jar

首先找到你的 oracle 安装位置，例如：

1.创建一个JDBC数据库连接工具类：

package com.test.db;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class JDBCUtils {private static String driver = "oracle.jdbc.OracleDriver";private static String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";private static String user = "数据库连接名";private static String password = "数据库连接密码";//注册数据库驱动static{try {Class.forName(driver);} catch (Exception e) {throw new ExceptionInInitializerError(e);}}/** * 获取数据库连接 * @return */public static Connection getConnection(){try {return DriverManager.getConnection(url,user,password);} catch (SQLException e) {e.printStackTrace();}return null;}/** * 释放数据库连接资源 * @param conn * @param st * @param rs */public static void release(Connection conn,Statement st,ResultSet rs){if (rs!=null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}finally{rs = null;}}if (st!=null) {try {st.close();} catch (SQLException e) {e.printStackTrace();}finally{st = null;}}if (conn!=null) {try {conn.close();} catch (SQLException e) {e.printStackTrace();}finally{conn = null;}}}}

2.调用存储过程：

package com.test.demo;import java.sql.CallableStatement;import java.sql.Connection;import org.junit.Test;import oracle.jdbc.internal.OracleTypes;import oracle.jdbc.oracore.OracleType;import com.hwb.db.JDBCUtils;/** * 调用存储过程  一个输入参数，多个 输出参数 * @author Administrator * */public class ProcedureTest {/** * create or replace procedure selectUserById(uid in number,                                           uName out VARCHAR2,                                           uAge out number,                                           uSex out char) */@Testpublic void testProcedure(){String sql = "{call selectUserById(?,?,?,?)}";Connection conn = null;CallableStatement call = null;try {//得到一个数据库连接conn = JDBCUtils.getConnection();//通过连接创建出statementcall = conn.prepareCall(sql);//对于in参数，赋值call.setInt(1, 2);  // (第几个问号,要赋的值)//对out参数，声明call.registerOutParameter(2, OracleTypes.VARCHAR);  //(第几个问号，声明的类型)call.registerOutParameter(3, OracleTypes.NUMBER);call.registerOutParameter(4, OracleTypes.CHAR);//执行调用call.execute();//取出结果String userName = call.getString(2);int userAge = call.getInt(3);String userSex = call.getString(4);System.out.println("用户姓名："+userName+"\n\t年龄:"+userAge+"\n\t性别："+userSex);} catch (Exception e) {e.printStackTrace();}finally{                    //关闭连接，释放资源                   JDBCUtils.release(conn, call, null);}}}

3.调用存储方法：

package com.test.demo;import java.sql.CallableStatement;import java.sql.Connection;import oracle.jdbc.internal.OracleTypes;import org.junit.Test;import com.hwb.db.JDBCUtils;/** * 调用存储函数，一个输入参数，一个输出参数 * @author Administrator * */public class FunctionTest {/** * create or replace function selectAge(eno in number)return number  */@Testpublic void testFunction(){//{？= call <procedure-name>[<arg1>,<arg2>...]}String sql = "{call selectAge(?)}";Connection conn = null;CallableStatement call = null;try {//得到数据库连接conn = JDBCUtils.getConnection();//通过数据库连接创建statementcall = conn.prepareCall(sql);//对于输出参数，声明call.registerOutParameter(1, OracleTypes.NUMBER);//对于输入参数，赋值call.setInt(2, 3);//执行调用call.execute();                        //获取返回的结果int age = call.getInt(1);System.out.println("该用户年龄："+age);} catch (Exception e) {e.printStackTrace();}finally{JDBCUtils.release(conn, call, null);}}}

4.调用存储过程，一个输入参数，返回一个查询结果集合

package com.hwb.demo;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.ResultSet;import org.junit.Test;import oracle.jdbc.internal.OracleCallableStatement;import oracle.jdbc.internal.OracleTypes;import com.hwb.db.JDBCUtils;/** * 存储过程  一个输入参数，返回一个查询结果集合 * @author Administrator * */public class CursorTest {/** * create or replace package Mypackage asprocedure queryUserList(uid in number,userList out usercursor);end mypackage; */@Testpublic void testCursor(){String sql = "{call Mypackage.queryUserList(?,?) }";Connection conn = null;CallableStatement call = null;ResultSet rs = null;try {//得到数据库连接conn = JDBCUtils.getConnection();//通过数据库连接创建statementcall = conn.prepareCall(sql);//对于输入参数，赋值call.setInt(1, 1);//对于输出参数，声明call.registerOutParameter(2, OracleTypes.CURSOR);//执行调用call.execute();//将CallableStatement 强转成  OracleCallableStatement 用来获取光标类型Cursor，并得到结果ResultSetrs = ((OracleCallableStatement)call).getCursor(2);//遍历 ResultSetwhile (rs.next()) {//根据类型和列名取值int id = rs.getInt("user_id");    //括号内为 列名String user_name = rs.getString("user_name");int age = rs.getInt("user_age");String sex = rs.getString("user_sex");System.out.println("查询到的用户信息：\n\tid:"+id+"\n\t姓名："+user_name+"\n\t年龄："+age+"\n\t性别："+sex);}} catch (Exception e) {e.printStackTrace();}finally{JDBCUtils.release(conn, call, rs);}}}

0 0