My JDBC PreparedStatement
is not working. I'm using Oracle 11g Express Edition, Tomcat 7, Java 7, ojdbc7.jar is in $CATALINA_HOME/lib. The application I'm developing uses the spring framework. But that's irrelevant because I built a simple Java class to test the same PreparedStatement
, and still got no results.
If I run the query in sqlplus, I get the expected result. If I use the same query in a regular Statement
, I get the expected result. If I hack JdbcTemplate
in Spring to use my hard coded value, I get results. Just not in that darned PreparedStatement
.
As you'll see from the logs below, my parameter is getting inserted into the PreparedStatement
in JDBC. The trace file shows that the value is binding in the database, the query is running, but fetch brings back nothing.
The log4jdbc log shows me this:
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
This is from the trace file:
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
This is from the output after running the trace file through 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
I tried with a POJO, still no results
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!");
}
And the output is: Go! Execute! No!
, and the trace file again shows that the query ran, but returned no results. Regular Statement returns
Go! Execute! Work! jibbyj No!
Which is right.
If anyone knows why JDBC PreparedStatement does not work on our oracle database, me and my dba would love to know. Thanks.
This is reason #47 that
char
data types are evil and should be banished. Anyone creating achar(8)
column that will store strings that aren't always exactly 8 characters should be sentenced to debug the various maddening issues that creates until they see the error of their ways.In the database, a
char(8 char)
will always consume 8 characters worth of space. If the data you're storing isn't actually 8 characters long, the database has to pad it with spaces out to 8 characters. So if your actual data is 6 characters long-- "jibbyj"-- the database has to add two additional spaces to the end. There are roughly 0 instances where this is of any benefit to you-- you're incurring the extra cost of storing two extra bytes of data for no reason. If you had used avarchar2(8 char)
instead, your 6 character string would actually be stored as you'd expect without the extra spaces.When you go to query the data in a
char
column, you have to be very cautious about whether you are using char or varchar comparison semantics. If your query includes a hard-coded literalOracle assumes that your literal is a
char
and space-pads it before doing the comparison. So it actually searcheschar_column
for the value "jibbyj " with two spaces at the end. When it finds that value, it returns the data and everything works.If, on the other hand, you try to use a
varchar2
(orvarchar
), then Oracle usesvarchar
comparison semantics. When that happens, the two extra spaces that are stored in your table are considered part of the data and the string you're searching for has to match exactly.In your
PreparedStatement
approach, you're forcingvarchar
comparison semantics on achar
column. You can work around the problem by padding the Java String object out to 8 characters before callingsetString
or by modifying the query to eithertrim
thechar(8)
column or torpad
the parameter being passed in to 8 characters. But none of those options are very satisfying-- you'd either need to write a bunch of code to figure out how long the column is and, thus, how much to pad your strings or you'd end up with a bunch of column lengths stored in your code that can get out of date if someone modifies the database in the future to increase the length of the column. You would be much, much better off changing the database column tovarchar2(8 char)
and expunging any references tochar
data types.