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

野性酷女 2022-06-16 09:24 279阅读 0赞

准备一个oracle 的JDBC jar 包：ojdbc14\_11g.jar

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

![Center][]

**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)
    	 */
    	
    	@Test
    	public void testProcedure(){
    		
    		String sql = "{call selectUserById(?,?,?,?)}";
    		
    		Connection conn = null;
    		CallableStatement call = null;
    		try {
    			//得到一个数据库连接
    			conn = JDBCUtils.getConnection();
    			//通过连接创建出statement
    			call = 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 
    	 */
    	@Test
    	public void testFunction(){
    		//{？= call <procedure-name>[<arg1>,<arg2>...]}
    		String sql = "{call selectAge(?)}";
    		Connection conn = null;
    		CallableStatement call = null;
    		try {
    			//得到数据库连接
    			conn = JDBCUtils.getConnection();
    			
    			//通过数据库连接创建statement
    			call = 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 as
    		procedure queryUserList(uid in number,userList out usercursor);
    		end mypackage;
    	 */
    	@Test
    	public void testCursor(){
    		String sql = "{call Mypackage.queryUserList(?,?) }";
    		
    		Connection conn = null;
    		CallableStatement call = null;
    		ResultSet rs = null;
    		try {
    			//得到数据库连接
    			conn = JDBCUtils.getConnection();
    			//通过数据库连接创建statement
    			call = conn.prepareCall(sql);
    			
    			//对于输入参数，赋值
    			call.setInt(1, 1);
    			//对于输出参数，声明
    			call.registerOutParameter(2, OracleTypes.CURSOR);
    			//执行调用
    			call.execute();
    			//将CallableStatement 强转成  OracleCallableStatement 用来获取光标类型Cursor，并得到结果ResultSet
    			rs = ((OracleCallableStatement)call).getCursor(2);
    			//遍历 ResultSet
    			while (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);
    		}
    	}
    }

[Center]: /images/20220616/cd71edfe27014a7abce09779c959535d.png