准备的语句不从oracle XE数据库返回结果

Question

我的JDBCPreparedStatement不工作。我使用的是Oracle 11g Express Edition、Tomcat7、Java7、OJDBC7。jar在$CATALINA_HOME/lib中。我正在开发的应用程序使用spring框架。但那是无关紧要的，因为我构建了一个简单的Java类来测试相同的PreparedStatement，但仍然没有得到任何结果。

如果我在sqlplus中运行查询，就会得到预期的结果。如果我在常规的语句中使用相同的查询，就会得到预期的结果。如果我在Spring中修改jdbctemplate来使用我的硬编码值，我会得到结果。只是不是在那个该死的preparedstatement中。

正如您将从下面的日志中看到的，我的参数被插入到JDBC中的PreparedStatement中。跟踪文件显示该值在数据库中绑定，查询正在运行，但fetch没有带回任何东西。

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! Execute! Work! jibbyj No!

Answer 1

这是第47条理由，说明char数据类型是有害的，应该予以放逐。任何创建char(8)列的人，如果该列存储的字符串不一定都是8个字符，就必须调试由此产生的各种令人抓狂的问题，直到他们发现自己的方法有错误为止。

在数据库中，char(8 char)将始终占用8个字符的空间。如果您存储的数据实际上不是8个字符长，数据库必须用空格填充到8个字符。因此，如果您的实际数据是6个字符长--“jibbyj”--数据库必须在末尾添加两个额外的空格。这对您有任何好处的情况大概为0个--您无缘无故地增加了存储两个额外字节数据的额外成本。如果您使用了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方法中，您将对Char列强制使用VARCHAR比较语义。您可以通过在调用setstring之前将Java String对象填充到8个字符，或者通过将查询修改为trimchar(8)列，或者修改为rpad将传入的参数填充到8个字符，来解决这个问题。但是这些选项都不是很令人满意的--您要么需要编写一堆代码来计算列的长度，从而计算出需要填充多少字符串，要么代码中存储了一堆列长度，如果将来有人修改数据库来增加列的长度，这些长度就会过时。您最好将数据库列更改为varchar2(8 char)并删除对char数据类型的任何引用。