读书人
栏目▼

Java使用JDBC操作Oracle数据库遇到的两

发布时间： 2013-08-01 15:23:18 作者： rapoo

Java使用JDBC操作Oracle数据库遇到的两个问题，求解
本帖最后由 Rabsia 于 2013-07-24 18:28:22 编辑 第一个问题是这样的：

Linux系统，JDK1.4编译的Jar包，在1.5的环境中运行，oracle数据库版本是10g，jdbc驱动版本是ojdbc14

代码中只要使用PrepareStatement的绑定变量功能，就会出错，类似这种代码：

String umid = "0-1";
String s = "DELETE FROM TZ_EXPORT_SQL WHERE ROW_ID = ?";
PreparedStatement stmt = conn.prepareStatement(s);
stmt.setString(1, umid);
stmt.executeUpdate();

而把绑定变量换成直接拼字符串的形式就运行正常

报错抓到的错误日志如下：
#
# An unexpected error has been detected by HotSpot Virtual Machine:
#
#  SIGSEGV (0xb) at pc=0xfe992c24, pid=22771, tid=1
#
# Java VM: Java HotSpot(TM) Server VM (1.5.0_18-b02 mixed mode)
# Problematic frame:
# V  [libjvm.so+0x192c24]
#

---------------  T H R E A D  ---------------

Current thread (0x000367b8):  JavaThread "main" [_thread_in_vm, id=1]

siginfo:si_signo=11, si_errno=0, si_code=1, si_addr=0x00000000

Registers:
 O0=0x00007794 O1=0x00007400 O2=0x80000000 O3=0x00000006
 O4=0xff032f1c O5=0x00008000 O6=0xffbfe5e8 O7=0x00009628
 G1=0x0000941c G2=0x00009400 G3=0x00009400 G4=0x00000000
 G5=0xff036fb8 G6=0x5387b38f G7=0xff362a00 Y=0x00000000
 PC=0xfe992c24 nPC=0xfe992c28


Top of Stack: (sp=0xffbfe5e8)
0xffbfe5e8:   ff032f18 00000000 fefde000 725f725f
0xffbfe5f8:   00001ffc 00000001 ff032f20 0000835c
0xffbfe608:   ff027754 00000000 00000000 725f725f
0xffbfe618:   005e48c8 000367b8 ffbfe648 fadd7e0c
0xffbfe628:   1b22638c 0048bc78 00000004 00000004
0xffbfe638:   1b226390 1b22639c ffbfe648 fadd7dd0
0xffbfe648:   0000fc00 00000000 00000005 a864a864
0xffbfe658:   00000000 00000014 fe992ad4 00000004 

Instructions: (pc=0xfe992c24)
0xfe992c14:   06 40 00 4c b6 90 00 1b 06 40 00 4a a6 06 80 1b
0xfe992c24:   e0 06 60 00 e8 04 20 08 80 a4 c0 14 18 40 00 45 

Stack: [0xffb80000,0xffc00000),  sp=0xffbfe5e8,  free space=505k
Native frames: (J=compiled Java code, j=interpreted, Vv=VM code, C=native code) 
 
V  [libjvm.so+0x192c24]
C  [libocijdbc10.so+0x7e14]
C  [libocijdbc10.so+0xa888]  Java_oracle_jdbc_driver_T2CStatement_t2cParseExecuteDescribe+0x1b4
j  oracle.jdbc.driver.T2CStatement.t2cParseExecuteDescribe(Loracle/jdbc/driver/OracleStatement;JIIIZZZZ[BIBII[SI[B[CII[SII[B[CII[I[S[BIIIIZZ[Loracle/jdbc/driver/Accessor;[[[B[J[BI[CI[SIZ)I+37916
j  oracle.jdbc.driver.T2CStatement.t2cParseExecuteDescribe(Loracle/jdbc/driver/OracleStatement;JIIIZZZZ[BIBII[SI[B[CII[SII[B[CII[I[S[BIIIIZZ[Loracle/jdbc/driver/Accessor;[[[B[J[BI[CI[SIZ)I+0
j  oracle.jdbc.driver.T2CPreparedStatement.executeForDescribe()V+260
j  oracle.jdbc.driver.T2CPreparedStatement.executeForRows(Z)V+41
j  oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout()V+271
j  oracle.jdbc.driver.OraclePreparedStatement.executeInternal()I+94
j  oracle.jdbc.driver.OraclePreparedStatement.executeUpdate()I+17
j  tz.export.ExportService.doInvokeMethod(Ljava/lang/String;Lcom/siebel/data/SiebelPropertySet;Lcom/siebel/data/SiebelPropertySet;)V+433
j  tz.export.ExportService.main([Ljava/lang/String;)V+56
v  ~StubRoutines::call_stub
V  [libjvm.so+0x19b360]
V  [libjvm.so+0x2dbe60]
C  [java+0x222c]  main+0x1164

Java frames: (J=compiled Java code, j=interpreted, Vv=VM code)
j  oracle.jdbc.driver.T2CStatement.t2cParseExecuteDescribe(Loracle/jdbc/driver/OracleStatement;JIIIZZZZ[BIBII[SI[B[CII[SII[B[CII[I[S[BIIIIZZ[Loracle/jdbc/driver/Accessor;[[[B[J[BI[CI[SIZ)I+0
j  oracle.jdbc.driver.T2CPreparedStatement.executeForDescribe()V+260
j  oracle.jdbc.driver.T2CPreparedStatement.executeForRows(Z)V+41
j  oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout()V+271
j  oracle.jdbc.driver.OraclePreparedStatement.executeInternal()I+94
j  oracle.jdbc.driver.OraclePreparedStatement.executeUpdate()I+17
j  tz.export.ExportService.doInvokeMethod(Ljava/lang/String;Lcom/siebel/data/SiebelPropertySet;Lcom/siebel/data/SiebelPropertySet;)V+433
j  tz.export.ExportService.main([Ljava/lang/String;)V+56
v  ~StubRoutines::call_stub

---------------  P R O C E S S  ---------------

Java Threads: ( => current thread )
  0x002524d8 JavaThread "Low Memory Detector" daemon [_thread_blocked, id=41]
  0x00250c70 JavaThread "CompilerThread1" daemon [_thread_blocked, id=40]
  0x0024fdb0 JavaThread "CompilerThread0" daemon [_thread_blocked, id=39]
  0x0024f030 JavaThread "AdapterThread" daemon [_thread_blocked, id=38] 
 
  0x0024e2a8 JavaThread "Signal Dispatcher" daemon [_thread_blocked, id=37]
  0x002418f0 JavaThread "Finalizer" daemon [_thread_blocked, id=36]
  0x00241410 JavaThread "Reference Handler" daemon [_thread_blocked, id=35]
=>0x000367b8 JavaThread "main" [_thread_in_vm, id=1]

Other Threads:
  0x0023f330 VMThread [id=34]
  0x000b05f0 WatcherThread [id=42]

VM state:synchronizing (normal execution)

VM Mutex/Monitor currently owned by a thread:  ([mutex/lock_event])
[0x00035728/0x00035758] Threads_lock - owner thread: 0x0023f330

Heap
 PSYoungGen      total 154112K, used 5318K [0xe3000000, 0xedc00000, 0xf8800000)
  eden space 132096K, 4% used [0xe3000000,0xe35318d0,0xeb100000)
  from space 22016K, 0% used [0xec680000,0xec680000,0xedc00000)
  to   space 22016K, 0% used [0xeb100000,0xeb100000,0xec680000)
 PSOldGen        total 352256K, used 0K [0xb8400000, 0xcdc00000, 0xe3000000)
  object space 352256K, 0% used [0xb8400000,0xb8400000,0xcdc00000)
 PSPermGen       total 16384K, used 5157K [0xb4400000, 0xb5400000, 0xb8400000)
  object space 16384K, 31% used [0xb4400000,0xb4909630,0xb5400000)

Dynamic libraries:
0x00010000 /usr/jdk/instances/jdk1.5.0/bin/java
0xff3a0000 /usr/lib/libthread.so.1
0xff370000 /usr/lib/libdl.so.1
0xff200000 /usr/lib/libc.so.1
0xff390000 /platform/SUNW,SPARC-Enterprise/lib/libc_psr.so.1
0xfe800000 /usr/jdk/instances/jdk1.5.0/jre/lib/sparc/server/libjvm.so
0xff1e0000 /usr/lib/libsocket.so.1
0xff350000 /usr/lib/libsched.so.1
0xff1b0000 /usr/lib/libCrun.so.1
0xff190000 /usr/lib/libm.so.1
0xff080000 /usr/lib/libnsl.so.1
0xfe700000 /usr/lib/libm.so.2
0xff160000 /usr/lib/libscf.so.1
0xff140000 /usr/lib/libdoor.so.1
0xff060000 /usr/lib/libuutil.so.1
0xfe7e0000 /usr/lib/libgen.so.1
0xfe6d0000 /usr/lib/libmd.so.1
0xfe6b0000 /usr/lib/libmp.so.2
0xfe690000 /usr/jdk/instances/jdk1.5.0/jre/lib/sparc/native_threads/libhpi.so
0xfe630000 /usr/jdk/instances/jdk1.5.0/jre/lib/sparc/libverify.so
0xfe5f0000 /usr/jdk/instances/jdk1.5.0/jre/lib/sparc/libjava.so
0xfe5d0000 /usr/jdk/instances/jdk1.5.0/jre/lib/sparc/libzip.so 
 
0xfadd0000 /oraapp/db_1/lib32/libocijdbc10.so
0xb0400000 /oraapp/db_1/lib32/libclntsh.so.10.1
0xb1500000 /oraapp/db_1/lib32/libnnz10.so
0xfb860000 /usr/lib/libkstat.so.1
0xfadb0000 /usr/lib/libaio.so.1
0xfad90000 /usr/lib/librt.so.1

VM Arguments:
java_command: /siebel/exportLog/exportSupport.jar
Launcher Type: SUN_STANDARD

Environment Variables:
PATH=/oraapp/db_1/bin:/bin:/sbin:/usr/local/bin
LD_LIBRARY_PATH=/usr/jdk/instances/jdk1.5.0/jre/lib/sparc/server:/usr/jdk/instances/jdk1.5.0/jre/lib/sparc:/usr/jdk/instances/jdk1.5.0/jre/../lib/sparc:/oraapp/db_1/lib32:/oraapp/db_1/lib:/oraapp/db_1/network/lib:/usr/local/lib:/usr/lib
SHELL=/bin/sh
DISPLAY=localhost:10.0

Signal Handlers:
SIGSEGV: [libjvm.so+0x70d970], sa_mask[0]=0xffbffeff, sa_flags=0x00000004
SIGBUS: [libjvm.so+0x70d970], sa_mask[0]=0xffbffeff, sa_flags=0x00000004
SIGFPE: [libjvm.so+0x2731e4], sa_mask[0]=0xffbffeff, sa_flags=0x0000000c
SIGPIPE: SIG_IGN, sa_mask[0]=0x00000000, sa_flags=0x00000000
SIGILL: [libjvm.so+0x2731e4], sa_mask[0]=0xffbffeff, sa_flags=0x0000000c
SIGUSR1: SIG_DFL, sa_mask[0]=0x00000000, sa_flags=0x00000000
SIGUSR2: SIG_DFL, sa_mask[0]=0x00000000, sa_flags=0x00000000
SIGHUP: [libjvm.so+0x67ef14], sa_mask[0]=0xffbffeff, sa_flags=0x00000004
SIGINT: [libjvm.so+0x67ef14], sa_mask[0]=0xffbffeff, sa_flags=0x00000004
SIGQUIT: [libjvm.so+0x67ef14], sa_mask[0]=0xffbffeff, sa_flags=0x00000004
SIGTERM: [libjvm.so+0x67ef14], sa_mask[0]=0xffbffeff, sa_flags=0x00000004


---------------  S Y S T E M  ---------------

OS:                       Solaris 10 5/09 s10s_u7wos_08 SPARC
           Copyright 2009 Sun Microsystems, Inc.  All Rights Reserved.
                        Use is subject to license terms.
                             Assembled 30 March 2009

uname:SunOS 5.10 Generic_139555-08 sun4u  (T2 libthread)
rlimit: STACK 8192k, CORE infinity, NOFILE 65536, AS infinity
load average:0.32 0.34 0.36

CPU:total 32 has_v8, has_v9, has_vis1, has_vis2, is_ultra3 
 

Memory: 8k page, physical 33554432k(20497000k free)

vm_info: Java HotSpot(TM) Server VM (1.5.0_18-b02) for solaris-sparc, built on Feb 25 2009 02:31:38 by unknown with unknown Workshop:0x550



纠结两天了，现在是一头雾水，求解答；

================================================================================

第二个问题：

jdbc驱动依然是ojdbc14

功能是这样：jdbc对数据库做查询并且遍历结果集，将取出的结果集写入一个文件里

现在的问题是，遍历结果集的时候速度不稳定，有时候几十万条也只要不到1分钟，有时候几十条数据可能就需要五六分钟

经过多次测试，发现取结果集的操作容易卡在某几条记录上，也就是说大部分过程都很流畅，但在某几个点会卡很久，至少也要四五秒，这样累计下来就大大影响了效率

而且这种卡住跟数据是无关的，仅与结果集的第X条有关

举个例子，一条SQL查询出的记录总数是1291条，在取结果集的时候很明显卡在了226条和1270条这两个地方
总共花费约10秒，这两个地方分别就花费了4-5秒
在删除卡住位置的记录之后，重试，仍然会卡在这两个地方

这个让我非常不理解，也找不到相关资料，请问有没有办法解决，或者解释下问题出现的原因？

================================================================================

求大神。。。。。。
[解决办法]
问题一：
很大概率是 OracleJDBC 驱动问题，也许要去官网上重新找个 Linux 版本的，另外要注意下JVM是32bit还是64bit。
其实最好编译跟运行是完全相同的JDK，甚至运行在Linux的话，你就在Linux上进行编译是最安全的。

问题二：
不清楚，用别的语言，或者直接用SQLPlus试试看什么效果？
[解决办法]
String umid = "0-1";
String s = "DELETE FROM TZ_EXPORT_SQL WHERE ROW_ID = ?";
PreparedStatement stmt = conn.prepareStatement(s);
stmt.setString(1, umid);

------->
String umid = "0-1";
String s = "DELETE FROM TZ_EXPORT_SQL WHERE ROW_ID =:umid";
NamedParameterStatement p = new NamedParameterStatement(conn, s);
p.setString("umid", umid);
p.executeUpdate();


第二个可能是有些字段长度、类型什么的有问题
[解决办法]
引用:
Quote: 引用:

For 1.5 的，可以到这里下载：
http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html

兼容性问题，很不好说。有时候换个运行环境的JDK版本，所有问题就消失了，真的碰到过这种情况。



如果SQLPlus也是这种情况，说明是数据库层面的问题，跟程序无关了。


这个很难猜测，也许跟数据存储块划分有关，建议请教Oracle数据库板块的同学。
顺便问一句，查询条件所在字段建了索引么？



非常感谢, 由于运行环境可能同时在跑其他的服务, 所以换JDK不是太现实, 明天先换jdbc驱动试试. 不过我打算额外装一个jdk, 先看看是否这就是问题所在, 请问libjvm.so这个文件是做什么用的? 我还没在linux装过jdk


查询字段是建了索引的.


另外刚才看了下, 还得多问一句, NamedParameterStatement使用这个需要什么包么?

关于JDK，Linux下可以装多个的，然后Oracle用户下配置.cshrc环境变量，使用新的JDK。

读书人网 >J2EE开发

热点推荐

触屏版 | 电脑版

读书人网 m.reader8.net