文档章节

oracle存储过程及Java调用

y
 yiranxijie
发布于 2015/06/01 11:23
字数 2107
阅读 39
收藏 4

初次研究出bug的地方非常多,所以注意点非常多,花了我三天时间除尽所有bug,我会把注意点都列出来,可能有落下的地方,还请多指正,相互探讨。

首先上最终测试成功版存储过程代码:(里面代码可能不尽对你都有用,借鉴参考吧,我全贴出来也是为了我以后好查)

说一下jar包用的是ojdbc14.jar,至于什么class12.jar、ojdbc6.jar啊应该都可以,只要一种就可以了。

说说我的需求,以便让大家更顺利的看懂我的代码,我的需求是:存储过程从Java端接收两个参数userid(用户)和topicid(话题),在存储过程进行循环查询当前用户对当前话题的点赞记录,如果有记录,则record为设置1,没有则为0,最后返回一个结果集,是反应 用户=>话题=>record相互对应的关系表。

好了不废话了,上代码吧,学习阶段,所以难免情绪波动和啰嗦,也是希望以最直白能懂的方式叙述出来。


------------在数据库建立一个type,对应JAVA端要传入的对象结构 :  
create or replace type tp_arr3 as Object    
(    
  userid   nvarchar2(40),    --这里从varchar2改成nvarchar2类型才能跟Java的String匹配上  
  topicid nvarchar2(40),
  record nvarchar2(4)    
)
---多次测试创建可能会出现“ORA-02303: 无法使用类型或表的相关性来删除或取代一个类型”这个错误,这时只要换一个类型名字再创建就可以了
----------
CREATE OR REPLACE TYPE tp_arr_tbl3 AS TABLE OF tp_arr3
--------------创建包 ,创建一个游标类型用来放输出参数 
create or replace package testpkg as 
 type testcur is ref cursor;
 end testpkg;
------创建存储过程 ,定义两个参数,一个入参,是一个对象类型数组(这种类型应该可以满足大部分复杂需求了),一个出参,是用游标存放查询值 
 CREATE OR REPLACE  procedure findRecord(type_obj IN tp_arr_tbl3,result out testpkg.testcur)  
  as 
  t tp_arr3; 
  sql2 varchar2(500);
  sql3 varchar2(500); 
  v_count varchar2(4);     --临时中间变量,用来存放对应的record 
 BEGIN     
    sql2 :='drop table tb_temp';
     sql3 :='CREATE TABLE tb_temp( userid varchar2(40),topicid varchar2(40) primary key, record varchar2(4))';
       execute immediate sql2;
       execute immediate sql3;
       
       FOR i IN type_obj.first()..type_obj.last() 
       LOOP
       t:= type_obj(i);
       select count(*) into v_count from scott.tb_praise_rel where userid=t.userid and topicid=t.topicid;
       dbms_output.put_line( t.userid || '=>'||t.topicid ||'=>' || v_count);
       insert into tb_temp values (t.userid,t.topicid, v_count);
      END LOOP;
      COMMIT;
      
      open result for select * from tb_temp;
 END;
--------------执行存储过程 

declare 
ta tp_arr_tbl3:=tp_arr_tbl3();      --对象的声明 
t tp_arr3:=tp_arr3('0','0','0');    --声明及赋初值(必要步骤)
begin
for i in 1..12 loop
ta.extend;
  t.userid:='1';
  t.topicid:=i;
  t.record:='0';
  ta(i):=t;
  findRecord(ta);
  end loop;
  end findRecord;

 ----------------------表查询测试部分 
 select * from tb_temp;
 
 select * from scott.tb_praise_rel;
 
 select userid from scott.tb_praise_rel where userid='1' and topicid='1';
-----------------------游标测试,后来没用,可以略过 
 cursor testcur is select userid,topicid from scott.tb_praise_rel; 
  cur testcur%rowtype;
  
   open testcur;
    loop 
     fetch testcur into cur;
     exit when testcur%notfound;
     dbms_output.put_line( 'userid:' || cur.userid || ',topicid:' || cur.topicid );
     update tb_temp set record='1' where userid=cur.userid and topicid=cur.topicid;
     end loop;
     dbms_output.put_line('----------------------');
     close testcur;
      COMMIT;

注意点:1.测试用户起初用的Scott,发现没有执行权限,对其进行赋予dba权限还是不行,遂后来用的system;2.多次测试创建可能会出现“ORA-02303: 无法使用类型或表的相关性来删除或取代一个类型”这个错误,这时只要换一个类型名字再创建就可以了;3.由于我的tb_temp表有唯一字段约束,所以存储过程每次进来先删表,再建表,再插入数据;4.记得该打分号的地方不要漏,不该打的地方不要多;5.执行存储过程的时候,要先声明并赋初值,不然也会报错;6.注意pl/sql里执行存储过程测试赋值时候ta.extend不能少;6.自定义类型要注意的地方很多,比如nvarchar2和JavaString类型的定义;7.简单说tp_arr3 类型是指一条记录,tp_arr_tbl3是指多条记录;

--点赞关系表 
create table tb_praise_rel(
       id varchar2(40) primary key,
       userid varchar2(40),             --用户id 
       topicid  varchar2(40),            --话题id 
       remarks1 varchar2(3000),        --备用字段 
       remarks2 varchar2(3000),
       remarks3 varchar2(3000)
);


再上Java调用代码:


package com.lofter.svntesr;

import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import oracle.jdbc.OracleTypes;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

import com.lofter.bean.ProcedureBean;

public class ProcedureTest3 {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		try {  
			  
            Class.forName("oracle.jdbc.driver.OracleDriver");  
  
            String url = "jdbc:oracle:thin:@localhost:1521:orcl";  
  
            //网上很多卡在获取con这个地方的,我最初也是,说是什么jar包问题,删掉class12啊,什么oracle与apache连接池冲突啊,但是我其实是存储过程没写对,最后绕了一圈回来还是用的这种方法测试通过,并没有加((org.apache.commons.dbcp.PoolableConnection) conn).getInnermostDelegate()
            Connection con = DriverManager.getConnection(url, "system", "a");  
  
      //      PreparedStatement pstmt = null;  
            CallableStatement cs = null;
            ResultSet rs=null;
            
            List<ProcedureBean> list = new ArrayList<ProcedureBean>();
    		for (int i = 1; i <= 12; i++) {
    			String r = i + "";
    			list.add(new ProcedureBean("1", r, "0"));
    		}
    	//	list.add(new ProcedureBean("1","5f60b0f0-03d9-4671-b945-936fe821fe19", "0"));
    		
    		//如果存储过程是用我这种对象数组as object类型,则java调用这一步必不可少,这是对之前在pl/sql中声明的tp_arr3 类型的映射,表示在pl/sql中去匹配你自定义的类型
    		//还有注意要大写,不然可能会报“无效名称模式”
    		StructDescriptor recDesc = StructDescriptor.createDescriptor(
					"TP_ARR3", con);

    		//这一步是将你自定义的类型转化成oracle自己的类型,即STRUCT,相当于一个Object类,因为oracle的开发人员也不知道你会定义一个什么名字的类型,反正只用提供一个规则,最后大家都照着这个规则来转化就是了
			ArrayList<STRUCT> pstruct = new ArrayList<STRUCT>();
			for (ProcedureBean pb : list) {
				System.out.println(pb);
				Object[] objs = new Object[3];
				objs[0] = pb.getUserid();
				objs[1] = pb.getTopicid();
				objs[2] = pb.getRecord();
				STRUCT item = new STRUCT(recDesc, con, objs);
				pstruct.add(item);
			}
			
			//这是第二步映射,映射我在oracle中自定义的tp_arr_tbl3类型,注意也要大写,网上也有说要加包名,不是同一个用户要加用户前缀什么的,我没有加,测试也通过,可能不是极端情况吧
			oracle.sql.ArrayDescriptor desc = oracle.sql.ArrayDescriptor.createDescriptor("TP_ARR_TBL3", con);  
			  
            oracle.sql.ARRAY array = new oracle.sql.ARRAY(desc, con, pstruct.toArray()); 
            
            //也有说调用的时候要加包名的
            cs = con.prepareCall("{call findRecord(?,?)}");
            
            //设置参数这里,1、2分别对应存储过程findRecord(?,?)中参数的位置,注意位置不要错了
            cs.setArray(1, array);
            cs.registerOutParameter(2, OracleTypes.CURSOR);
            cs.execute();
            rs=(ResultSet) cs.getObject(2);	//取数据也是根据对应参数位置来的
            
            while( rs.next() ){
            	System.out.println("result : \t" + rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3));
            }
            con.commit();
        } catch (Exception e) {  
  
            e.printStackTrace();  
  
        }  
  
    }    
	}

Java调用注意:基本上注意事项都以注释的方式写在代码里了,也有可能没想起来,很多bug信息由于测试通过心切,没能及时复制下来。注意不要导错包

2.错误信息“Message file 'oracle.jdbc.driver.Messages' is missing.”,可能是你写错了或类型与oracle中不匹配,不要去找什么jar包啊什么的,网上信息也不多,我在这绕了好久,多检查一下上面提到的加包名、大小写、转类型什么的;

还有其他没想起来或没碰到的bug只有亲们多结合错误信息猜测,多动手测测,相信就会迎刃而解了。


测试的javaBean:

package com.lofter.bean;

import java.io.Serializable;

public class ProcedureBean implements Serializable {

	private static final long serialVersionUID = 809894604693791308L;
	private String userid;
	private String topicid;
	private String record;

	public ProcedureBean() {
		super();
	}

	public ProcedureBean(String userid, String topicid, String record) {
		super();
		this.userid = userid;
		this.topicid = topicid;
		this.record = record;
	}

	public String getUserid() {
		return userid;
	}

	public void setUserid(String userid) {
		this.userid = userid;
	}

	public String getTopicid() {
		return topicid;
	}

	public void setTopicid(String topicid) {
		this.topicid = topicid;
	}

	public String getRecord() {
		return record;
	}

	public void setRecord(String record) {
		this.record = record;
	}

	@Override
	public String toString() {
		return "ProcedureBean [userid=" + userid + ", topicid=" + topicid
				+ ", record=" + record + "]";
	}

}
折腾了我好几天研究这个东西,主要是要研究对象数组类型的,从语法都不清楚,只能参照着能看懂大概的别人代码揣测着写,到最后测试通过,一把辛酸泪啊,两天研究到凌晨四点,敲了不知多少遍回车键,点了不知多少次运行(其实也没多少,可能也是对之前调试所有bug过程的一次发泄),因为复杂类型的参数网上很多没讲清楚,也有很多bug,所以没办法就用。测试期间bug不断,一直百度,也翻了下平时都没认真看过的教材,最后终于打通从PL/SQL调用到Java调用,其实bug出最多在Java调用上,各种类型不匹配,只能说搜索引擎真强大,互联网真强大,大数据真强大。最后我想说的是:“人就怕认真”。


本文转载自:http://blog.csdn.net/u013863751/article/details/46065735

y
粉丝 0
博文 26
码字总数 0
作品 0
成都
私信 提问
如何在Oracle中使用Java存储过程 (详解)

其实,这篇短文,我早就应该写了。因为,java存储过程今后在各大数据库厂商中越来越流行,功能也越来越强大。这里以Oracle为例,介绍一下java存储过程的具体用法。 任何转载,请尊重版权。(...

晨曦之光
2012/04/12
144
0
JAVA客户端传递对象数组到Oracle存储过程做大数据量插入

最近在项目中用到了JAVA客户端传递对象数组到Oracle存储过程做大数据量插入,比如10万级别. 下面做一个插入10万条记录的示例步骤,,为了容易理解,表的结果很简单. 1,假设表结构如下: 2,在数据库...

晨曦之光
2012/04/25
537
0
用java调用oracle存储过程总结

...CallableStatement cstmt = conn.prepareCall("call p_changesal(?,?)");//存储过程中类型为OUT的参数必须被注册cstmt.registerOutParameter(2,java.sql.Types.INTEGER);//类型为In的参数......

sisyphus
2012/11/14
79
1
Jboss 中添加Oracle 的XA 数据源支持

这方面的资料太少了, 最终在一个IBM的网页上找到。 首先要先让Oracle 打开XA功能,可以通过以下几步实现 : 1) 在SQLPLUS(一定要用这个工具) 中,用Oracle 的系统管理员用户 SYSOPER 或 ...

小骏骏
2015/04/01
305
0
ibatis/Mybatis 配备调用存储过程,jdbctype配置

ibatis 配置调用存储过程,jdbctype配置 数据库存储过程如下: procedure pprojectdetailsstat ( idatestart in varchar2, idateend in varchar2, irangestart in number, irangeend in numb......

0o清风徐来o0
2013/03/20
5K
0

没有更多内容

加载失败,请刷新页面

加载更多

PhotoShop 色调:理解直方图/RGB通道信息

一、直方图:图表的形式,展示图像像素分布的情况 1.平均值:表示平均亮度 2.标准偏差值:表示亮度值范围内的中间值 3.像素: 表示用于计算直方图的像素总数 4.色阶:显示指针下面的区域亮度...

东方墨天
8分钟前
2
0
wildfly(JBoss AS)应用服务器快速入门

什么是wildfly JBoss AS 从8版本起名为wildfly。Wildfly是一个开源的基于JavaEE的轻量级应用服务器。可以在任何商业应用中免费使用。 WildFly是一个灵活的、轻量的、强大管理能力的应用程序服...

程序新视界
32分钟前
2
0
Java集合类常见面试知识点总结

Java集合类学习总结 这篇总结是基于之前博客内容的一个整理和回顾。 这里先简单地总结一下,更多详细内容请参考我的专栏:深入浅出Java核心技术 https://blog.csdn.net/column/details/21930...

Java技术江湖
35分钟前
6
0
怎么用for循环打出爱心

先上效果图: 这是用*组成的爱心,下面讲讲思路: 首先这个图形可以拆分成三部分:第一部分是上面三行的两个梯形,第二部分是中间三行的长方形,第三部分是最下面的倒三角形。 其实图形拆分好...

INEVITABLE
41分钟前
4
0
用HttpUrlConnection伪造成HttpClient

https://www.jianshu.com/p/27ad06cc39d2

shzwork
47分钟前
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部