CODEWITHSUNDEEP
javaoracle-databasejdbc
The Guest
The Guest

Reputation: 708

Why statement works but not prepared satement in java, jdbc, oracle?

I am trying to write a query and get results from oracle db using java and jdbc. My problem is the same query works if I try with statement, but the same query does not work if I use preparedStatement. Statement Code: (Here I get real count value)

Statement stmt = con.createStatement();
String sql = "SELECT COUNT(*) CNT FROM DB.TABLE WHERE DAY = TO_DATE('" + sqlDate + "','YYYY-MM-DD')";
rs = stmt.executeQuery(sql);

PreparedStatement Code: (Here I get count value zero)

Date sqlDate = new java.sql.Date(someJava.Util.Date.getTime());// = 2015-09-24
sqlString = "SELECT COUNT(*) CNT FROM DB.TABLE WHERE DAY = TO_DATE(?,'YYYY-MM-DD')";
pstmt = con.prepareStatement(sqlString);                                        
pstmt.setDate(1, sqlDate);          
rs = pstmt.executeQuery();

When I sysout my sqlDate prints like: 2015-09-24.

I have same problem with some other queries. Can anyone know whats wrong here?

Upvotes: 1

Views: 521

Answers (1)

M A
M A

Reputation: 72854

The TO_DATE function converts a string to a date given a certain format. So the parameter passed to the prepared statement should be the String to be converted by the Oracle function:

pstmt.setString(1, sqlDate.toString());

Or you can change the query so that the parameter is the date itself and pass the java.sql.Date object to the prepared statement:

sqlString = "SELECT COUNT(*) CNT FROM DB.TABLE WHERE DAY = ?";
pstmt.setDate(1, sqlDate());

Note that, for the normal statement query:

String sql = "SELECT COUNT(*) CNT FROM DB.TABLE WHERE DAY = TO_DATE('" + sqlDate + "','YYYY-MM-DD')";

the String concatenation will append the string representation of the object, i.e. it is equivalent to:

String sql = "SELECT COUNT(*) CNT FROM DB.TABLE WHERE DAY = TO_DATE('" + sqlDate.toString() + "','YYYY-MM-DD')";

Upvotes: 3

Related Questions