JDBC系列之存储过程

不念不忘少年蓝@ 2021-06-24 15:59 399阅读 0赞

本文主要通过 **使用JDBC创建存储过程** 和**使用JDBC调用存储过程**两部分 阐述JDBC 对存储过程的支持。本文将在Oracle数据库下创建一个可以表示岗位信息的基本表Jobs为例, 然后通过存储过程对这个Jobs表进行各种操作。表JOBS的建表语句如下：

-- 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);

### JDBC创建存储过程 ###

> 使用数据库操作数据库需要三个步骤： 执行 **创建存储过程语句** --> **编译存储过程**\---> **调用存储过程**。
> 
> 比如我们创建一个向表Jobs添加记录的存储过程，并且调用它，在数据库上要执行下列代码：
> 
>     --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();
>     				}
>     		}
>     	}
> 
> ### JDBC调用存储过程 ###
> 
> > 使用JDBC调用存储过程的基本格式为：
> 
> > > ***CALL PROCEDURE\_NAME(parameter1,parameter2,paramter3.....)***
> > 
> > 这里参数有三种不同的形式 ：in 类型、out类型还有 in 和out的混合类型：
> > 
> > > IN 类型：此类型是用于参数从外部传递给存储过程使用；
> > 
> > > OUT类型：此类型是存储过程执行过程中的返回值；
> > 
> > > IN、OUT混合类型：此类型是参数传入，然后返回。
> > 
> > 以下分四种参数类型创建不同的存储过程，然后通过JDBC调用：
> > 
> > 只有输入IN参数，没有输出OUT参数
> > 
> > >  上面演示的存储过程 ** insert\_jobs\_proc** 就是只有IN 参数传入的例子，请读者看上述的 例子。
> > 
> > 既有输入IN参数，也有输出OUT参数，输出是简单值（非列表）
> > 
> > 创建一个存储过程  **get\_job\_min\_salary\_proc**，传入特定岗位的job\_id,返回输出此岗位的最小薪酬min\_salary，对应的SQL语句如下：
> > 
> >     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();
> >     				}
> >     		}
> >     	}
> > 
> > 既有输入IN参数，也有输出OUT参数，输出是列表
> > 
> > 创建一个存储过程** get\_min\_greater\_proc**，输入参数 最小薪酬，返回jobs表里最小薪酬不小于此参数的岗位集合。
> > 
> > 对应的SQL语句如下：
> > 
> >     --创建一个包，自定义一个数据类型 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();
> >     					}
> >     			}
> >     		}
> > 
> > 输入输出参数是同一个（IN OUT）
> > 
> > 创建一个存储过程** get\_job\_info** ,传入job\_id 返回 job\_id ,返回的job\_id 是输入的job\_id 和对应的job\_title 拼接而成。
> > 
> >     --创建存储过程 传入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);
> > 
> >