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

首先给大家推荐一下我老师大神的人工智能教学网站。教学不仅零基础,通俗易懂,而且非常风趣幽默,还时不时有内涵黄段子!点这里可以跳转到网站

准备一个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)	 */		@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);		}	}}

点这里可以跳转到人工智能网站

发表评论