来自Oracle XE数据库的预处理语句未返回结果

Question

来自Oracle XE数据库的预处理语句未返回结果

5

我的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很想知道。谢谢。

- Jibby

@Justin Cave：staff_id 是一个 CHAR(8) 数据类型，能够工作的查询语句是 "select distinct staff_id from OE_ROLES where staff_id = 'jibbyj'"。我不知道其他三个问题的答案，但会向我的数据库管理员询问。 - Jibby

你使用的字符串真的是 jibbyj 吗？还是其他的字符串（可能包含 US-ASCII 范围之外的字符）？ - Mick Mnemonic

你的 JVM 使用的 Locale 是什么？顺便问一下。 - Mick Mnemonic

2

我会像避瘟疫一样避免使用char数据类型。如果您用空格填充字符串到8个位置（例如"jibbyj "，末尾有两个空格），您的查询可能会起作用。就我个人而言，我倾向于建议将创建char(8)列的人处以火刑，因为每个值都没有恰好8个字符的数据。其他人则建议采取更严厉的措施... - Justin Cave

1

正如Justin所指出的那样，您可以尝试使用select distinct staff_id from OE_ROLES where staff_id = rpad(?, 8)。准备好的语句的绑定变量会作为VARCHAR2传递，这就是为什么您可能需要填充的原因。 - Mick Mnemonic

显示剩余5条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Justin Cave · Accepted Answer

这是第47个原因，说明为什么char数据类型很糟糕，应该避免使用。任何创建一个将存储不总是恰好8个字符的字符串的char（8）列的人都应该被判决调试各种令人发狂的问题，直到他们看到他们的错误。

在数据库中，char（8 char）将始终占用8个字符的空间。如果您存储的数据实际上不是8个字符长，则数据库必须将其用空格填充到8个字符。因此，如果您的实际数据长度为6个字符 - "jibbyj"，则数据库必须在末尾添加两个额外的空格。几乎没有任何情况下这对您有任何好处-您正在承担额外存储两个字节数据的成本，而没有理由。如果您改用varchar2（8 char），则您的6个字符字符串将按您所期望的那样存储，不会有额外的空格。

当您查询char列中的数据时，必须非常小心，无论您是否使用char或varchar比较语义。如果您的查询包括一个硬编码的文字字面量

SELECT *
  FROM your_table
 WHERE char_column = 'jibbyj'

Oracle假定您的文本是一个char类型，并在进行比较之前填充空格。因此，它实际上会在“char_column”中搜索以两个空格结尾的值“jibbyj ”。当它找到该值时，它会返回数据，一切正常。

另一方面，如果您尝试使用varchar2（或varchar），则Oracle将使用varchar比较语义。当发生这种情况时，存储在表中的两个额外空格被视为数据的一部分，因此您要搜索的字符串必须完全匹配。

DECLARE
  l_str_wo_spaces VARCHAR2(8) := 'jibbyj';
  l_str_w_spaces  VARCHAR2(8) := 'jibbyj  ';
  l_cnt           INTEGER;
BEGIN
  -- This will find no rows
  SELECT COUNT(*)
    INTO l_cnt
    FROM your_table
   WHERE char_column = l_str_wo_spaces;
  dbms_output.put_line( l_cnt );

  -- This will find a row because it has the extra spaces
  SELECT COUNT(*)
    INTO l_cnt
    FROM your_table
   WHERE char_column = l_str_w_spaces;
  dbms_output.put_line( l_cnt );
END;

在您的PreparedStatement方法中，您将varchar比较语义强制应用于char列。您可以通过在调用setString之前将Java字符串对象填充到8个字符或修改查询以对char(8)列进行trim或rpad参数传递到8个字符来解决该问题。但是，这些选项都不太令人满意-- 您要么需要编写大量代码来确定列的长度以及需要填充字符串的长度，要么就会得到一堆存储在代码中并且如果将来有人修改数据库以增加列的长度，则可能变得过时的列长度。您最好将数据库列更改为varchar2(8 char)并清除任何对char数据类型的引用。