我的JDBC PreparedStatement
不能正常工作。我使用的是Oracle 11g Express Edition,Tomcat 7,Java 7,ojdbc7.jar在$CATALINA_HOME/lib中。我正在开发的应用程序使用了Spring框架。但这并不重要,因为我构建了一个简单的Java类来测试相同的PreparedStatement
,并且仍然没有结果。
如果我在sqlplus中运行查询,我会得到预期的结果。如果我在常规Statement
中使用相同的查询,我会得到预期的结果。如果我在Spring中黑客JdbcTemplate
以使用我的硬编码值,则会获得结果。只是在那该死的PreparedStatement
中没有。
从下面的日志中可以看出,我的参数正在JDBC中插入PreparedStatement
。跟踪文件显示该值在数据库中绑定,查询正在运行,但获取的结果为空。
log4jdbc日志向我显示:
Jun 09, 2015 1:05:35 PM net.sf.log4jdbc.Slf4jSpyLogDelegator methodReturned
INFO: 3. Connection.prepareStatement(select distinct staff_id from OE_ROLES where staff_id = ?) returned net.sf.log4jdbc.PreparedStatementSpy@71449b35
Jun 09, 2015 1:05:35 PM org.springframework.jdbc.core.StatementCreatorUtils setParameterValueInternal
FINEST: Setting SQL statement parameter value: column index 1, parameter value [jibbyj], value class [java.lang.String], SQL type unknown
Jun 09, 2015 1:05:35 PM net.sf.log4jdbc.Slf4jSpyLogDelegator methodReturned
INFO: 3. PreparedStatement.setString(1, "jibbyj") returned
Jun 09, 2015 1:05:35 PM net.sf.log4jdbc.Slf4jSpyLogDelegator sqlOccured
INFO: select distinct staff_id from OE_ROLES where staff_id = 'jibbyj'
Jun 09, 2015 1:05:35 PM net.sf.log4jdbc.Slf4jSpyLogDelegator sqlTimingOccured
INFO: select distinct staff_id from OE_ROLES where staff_id = 'jibbyj'
{executed in 2 msec}
Jun 09, 2015 1:05:35 PM net.sf.log4jdbc.Slf4jSpyLogDelegator methodReturned
INFO: 3. ResultSet.new ResultSet returned
Jun 09, 2015 1:05:35 PM net.sf.log4jdbc.Slf4jSpyLogDelegator methodReturned
INFO: 3. PreparedStatement.executeQuery() returned net.sf.log4jdbc.ResultSetSpy@34460b79
Jun 09, 2015 1:05:35 PM net.sf.log4jdbc.Slf4jSpyLogDelegator methodReturned
INFO: 3. ResultSet.next() returned false
这是来自跟踪文件的内容:
PARSING IN CURSOR #140603768927480 len=59 dep=0 uid=52 oct=3 lid=52 tim=1433880335336621 hv=1464048059 ad='87cfc090' sqlid='6hbrj2tbn76dv'
select distinct staff_id from OE_ROLES where staff_id = :1
END OF STMT
PARSE #140603768927480:c=0,e=124,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4279656581,tim=1433880335336604
BINDS #140603768927480:
Bind#0
oacdty=01 mxl=32(24) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=873 siz=32 off=0
kxsbbbfp=7fe0ddb35b88 bln=32 avl=06 flg=05
value="jibbyj"
EXEC #140603768927480:c=0,e=87,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4279656581,tim=1433880335336761
WAIT #140603768927480: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1433880335336794
FETCH #140603768927480:c=0,e=37,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=4279656581,tim=1433880335336853
STAT #140603768927480 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT UNIQUE NOSORT (cr=1 pr=0 pw=0 time=46 us cost=2 size=9 card=1)'
STAT #140603768927480 id=2 cnt=0 pid=1 pos=1 obj=24702 op='INDEX RANGE SCAN AI_OE_ROLES_3 (cr=1 pr=0 pw=0 time=34 us cost=1 size=36 card=4)'
WAIT #140603768927480: nam='SQL*Net message from client' ela= 16956 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1433880335353990
CLOSE #140603768927480:c=0,e=22,dep=0,type=0,tim=1433880335354080
XCTEND rlbk=0, rd_only=1, tim=1433880335354131
这是运行跟踪文件通过TKPROF后的输出结果:
SQL ID: 6hbrj2tbn76dv Plan Hash: 4279656581
select distinct staff_id
from
OE_ROLES where staff_id = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 1 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 1 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 52
Number of plan statistics captured: 1
我尝试使用一个POJO,但仍然没有结果。
public static void main(String[] args) {
String url = "jdbc:oracle:thin:@oracle-test.company.com:1521:XE";
String user = "schema-owner";
String passwd = "password";
System.out.println("Go!");
try(Connection conn = DriverManager.getConnection(url, user, passwd)){
String pQuery = "select distinct staff_id from OE_ROLES where staff_id = ?";
PreparedStatement pstmt = conn.prepareStatement(pQuery);
pstmt.setString(1, "jibbyj");
ResultSet rs = pstmt.executeQuery();
System.out.println("Execute!");
while (rs.next()){
System.out.println("Work!");
System.out.println(rs.getString(1));
}
} catch (Exception E) {System.out.println(E.getMessage());}
System.out.println("No!");
}
输出结果为:Go!执行!不行!
,跟踪文件再次显示查询已运行,但未返回任何结果。常规语句返回
Go! Execute! Work! jibbyj No!
哪一个是对的。
如果有人知道为什么JDBC PreparedStatement在我们的Oracle数据库上无法工作,我和我的DBA很想知道。谢谢。
jibbyj
吗?还是其他的字符串(可能包含 US-ASCII 范围之外的字符)? - Mick Mnemonicchar
数据类型。如果您用空格填充字符串到8个位置(例如"jibbyj ",末尾有两个空格),您的查询可能会起作用。就我个人而言,我倾向于建议将创建char(8)
列的人处以火刑,因为每个值都没有恰好8个字符的数据。其他人则建议采取更严厉的措施... - Justin Caveselect distinct staff_id from OE_ROLES where staff_id = rpad(?, 8)
。准备好的语句的绑定变量会作为VARCHAR2
传递,这就是为什么您可能需要填充的原因。 - Mick Mnemonic