老调重弹：JDBC系列之存储过程 CallableStatement(创建和使用)

时间：2022-03-10 17:39

-- Create table create table JOBS ( job_id VARCHAR2(10) not null, job_title VARCHAR2(35), min_salary NUMBER(6), max_salary NUMBER(6) ); -- Add comments to the table comment on table JOBS is '岗位信息表'; -- Add comments to the columns comment on column JOBS.job_id is 'Job Id'; comment on column JOBS.job_title is '岗位名称'; comment on column JOBS.min_salary is '最小薪酬'; comment on column JOBS.max_salary is '最大薪酬'; -- Create/Recreate primary, unique and foreign key constraints alter table JOBS add constraint PK_JOB_ID primary key (JOB_ID);

--1.创建存储过程 CREATE OR REPLACE PROCEDURE insert_jobs_proc( input_job_id IN VARCHAR2, input_job_title IN VARCHAR2, input_min_salary IN NUMBER, input_max_salary IN NUMBER) AS BEGIN INSERT INTO jobs(job_id,job_title,Min_Salary,max_salary)VALUES(input_job_id,input_job_title,input_min_salary,input_max_salary); END insert_jobs_proc; --2.编译存储过程 COMPILE; --3.使用存储过程 CALL insert_jobs_proc('AD_preS','President',20080,40000);

由于上述的代码本质上来说就是SQL代码，可以使用JDBC逐步执行上述的SQL代码即可(不过使用JDBC创建不需要调用compile进行编译，JDBC会自动让数据库编译)：

	public static void inTest(){
		
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		
		try {
			
			Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
			
			Driver driver = DriverManager.getDriver(URL);
			Properties props = new Properties();
			props.put("user", USER_NAME);
			props.put("password", PASSWORD);
			
			connection = driver.connect(URL, props);
			
			//获得Statement对象,这里使用了事务机制，如果创建存储过程语句失败或者是执行compile失败，回退
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			String procedureString = "CREATE OR REPLACE PROCEDURE insert_jobs_proc("
                                      +"input_job_id IN VARCHAR2,"
		                              +"input_job_title IN VARCHAR2,"
		                              +"input_min_salary IN NUMBER,"
		                              +"input_max_salary IN NUMBER) AS "
                                      +"BEGIN "
	                                  +"INSERT INTO jobs(job_id,job_title,Min_Salary,max_salary)VALUES(input_job_id,input_job_title,input_min_salary,input_max_salary); "
                                      +"END insert_jobs_proc;";
			//1 创建存储过程,JDBC 数据库会编译存储过程
			statement.execute(procedureString);
			//成功则提交
			connection.commit();
			//2.调用
		    CallableStatement callableStatement = connection.prepareCall("CALL insert_jobs_proc(?,?,?,?)");
		    //设置IN参数
		    callableStatement.setString(1, "AD_preSS");
		    callableStatement.setString(2, "President");
		    callableStatement.setBigDecimal(3, new BigDecimal(20080));
		    callableStatement.setBigDecimal(4, new BigDecimal(40000));
		    
		    callableStatement.execute();
		    connection.commit();
			
		} catch (ClassNotFoundException e) {
			System.out.println("加载Oracle类失败！");
			e.printStackTrace();
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}finally{
			    //使用完成后管理链接，释放资源，释放顺序应该是： ResultSet ->Statement ->Connection
				
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}

CREATE OR REPLACE PROCEDURE get_job_min_salary_proc( input_job_id IN VARCHAR2, output_salary OUT number) AS BEGIN SELECT min_salary INTO output_salary FROM jobs WHERE job_id = input_job_id; END get_job_min_salary_proc;

在JDBC中调用如下：

/*
 * 有IN 类型的参数输入 和Out类型的参数输出	
 */
	public static void inOutTest(){
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			
			Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
			
			Driver driver = DriverManager.getDriver(URL);
			Properties props = new Properties();
			props.put("user", USER_NAME);
			props.put("password", PASSWORD);
			
			connection = driver.connect(URL, props);
			
			//获得Statement对象,这里使用了事务机制，如果创建存储过程语句失败或者是执行compile失败，回退
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			String procedureString = "CREATE OR REPLACE PROCEDURE get_job_min_salary_proc("
                                      +"input_job_id IN VARCHAR2,"
		                              +"output_salary OUT number) AS "
                                      +"BEGIN "
	                                  +"SELECT min_salary INTO output_salary FROM jobs WHERE job_id = input_job_id; "
                                      +"END   get_job_min_salary_proc;";
			//1 创建存储过程,JDBC 数据库会编译存储过程
			statement.execute(procedureString);
			//成功则提交
			connection.commit();
			//2.创建callableStatement
		    CallableStatement callableStatement = connection.prepareCall("CALL get_job_min_salary_proc(?,?)");
		    //3，设置in参数
		    callableStatement.setString(1, "AD_preS");
		    //4.注册输出参数
		    callableStatement.registerOutParameter(2, Types.NUMERIC);
		    //5.执行语句
		    callableStatement.execute();
		    
		    BigDecimal salary = callableStatement.getBigDecimal(2);
		    System.out.println(salary);
			
		} catch (ClassNotFoundException e) {
			System.out.println("加载Oracle类失败！");
			e.printStackTrace();
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}finally{
			    //使用完成后管理链接，释放资源，释放顺序应该是： ResultSet ->Statement ->Connection
				
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}

--创建一个包，自定义一个数据类型 my_cursor CREATE OR REPLACE PACKAGE my_package_cursor IS TYPE my_cursor IS REF CURSOR; END my_package_cursor; --创建存储过程，通过传入最小薪酬，返回JOBs表内不小于最小薪酬的岗位集合 CREATE OR REPLACE PROCEDURE get_min_greater_proc( input_min_salary IN NUMBER, setResult OUT my_package_cursor.my_cursor) AS BEGIN OPEN setResult FOR SELECT * FROM jobs WHERE min_salary >= input_min_salary; END get_min_greater_proc;

JDBC调用代码如下：

	
	/*
	 * 有IN 类型的参数输入 和Out类型的集合输出
	 */
		public static void inOutResultSetTest(){
			Connection connection = null;
			Statement statement = null;
			ResultSet resultSet = null;
			try {
				
				Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
				
				Driver driver = DriverManager.getDriver(URL);
				Properties props = new Properties();
				props.put("user", USER_NAME);
				props.put("password", PASSWORD);
				connection = driver.connect(URL, props);
				//1.创建callableStatement
			    CallableStatement callableStatement = connection.prepareCall("CALL get_min_greater_proc(?,?)");
			    //2，设置in参数
			    callableStatement.setBigDecimal(1, new BigDecimal(20000));
			    //3.注册输出参数
			    callableStatement.registerOutParameter(2, OracleTypes.CURSOR);
			    //4.执行语句
			    callableStatement.execute();
			    //返回的是结果集
			    resultSet = (ResultSet)callableStatement.getObject(2);
				
			} catch (ClassNotFoundException e) {
				System.out.println("加载Oracle类失败！");
				e.printStackTrace();
			} catch (SQLException e) {
				try {
					connection.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
				e.printStackTrace();
			} catch (InstantiationException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}finally{
				    //使用完成后管理链接，释放资源，释放顺序应该是： ResultSet ->Statement ->Connection
					
					try {
						statement.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
					
					try {
						connection.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}

--创建存储过程传入job_id 返回job_id CREATE OR REPLACE PROCEDURE get_job_info( io_job_id IN OUT VARCHAR2) AS BEGIN SELECT job_id ||job_title INTO io_job_id FROM jobs WHERE job_id =io_job_id ; END get_job_info;

对应的JDBC代码如下：

				//1.创建callableStatement
			    CallableStatement callableStatement = connection.prepareCall("CALL get_job_info(?)");
			    //2，设置in参数
			    callableStatement.setString(1, "AD_preS");
			    //3.注册输出参数
			    callableStatement.registerOutParameter(1, Types.VARCHAR);
			    //4.执行语句
			    callableStatement.execute();
			    //返回结果
			    String jobId = callableStatement.getString(1);
			    System.out.println(jobId);

----------------------------------------------------------------------------------------------------------------------------------------

注：本篇文章很大程度上借鉴了这篇博文。

欢迎读者关注我的 老调重弹：JDBC系列 的其他文章～～～

老调重弹：JDBC系列之存储过程 CallableStatement(创建和使用),布布扣,bubuko.com