JDBC系列 之 存储过程 不念不忘少年蓝@ 2021-06-24 15:59 333阅读 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); > > > >
还没有评论，来说两句吧...