1

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?

The Guest
  • 698
  • 11
  • 27
  • 3
    why are you assigning `sqlString` *after* executing your query? – sstan Sep 24 '15 at 15:51
  • I am not assigning it, I just wanted to show you what is my sqlString value. I – The Guest Sep 24 '15 at 15:53
  • @sstan edited my question, thanks – The Guest Sep 24 '15 at 15:54
  • 4
    Because you should **just use a `java.sql.Date`**! What do you think [`PreparedStatement.setDate`](https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#setDate(int,%20java.sql.Date)) is for?? – Boris the Spider Sep 24 '15 at 15:54
  • @BoristheSpider I can't understand what you are saying, can you pls explain – The Guest Sep 24 '15 at 15:56
  • Is your date a String? Then you need something like: ps.setDate(1, java.sql.Date.valueOf("2013-09-04"); Alternatively, your string looks to be in right format, so you could also use pstmt.setString(1, sqlDate); – HulkSmash Sep 24 '15 at 15:57
  • Convert the String to Date first. Something like pstmt.setDate(1, new SimpleDateFormat("yyyy-MM-dd").parse(sqlDate)); – user2953113 Sep 24 '15 at 16:01
  • Boris means you can get rid of the TO_DATE in your sql string since you're passing in the date as a Date object, you only need TO_DATE if you're passing in a string. – Nathan Hughes Sep 24 '15 at 16:01
  • @DV88 that is not string, if I try like you said, i get the error: The method setString(int, String) in the type PreparedStatement is not applicable for the arguments (int, Date) – The Guest Sep 24 '15 at 16:01
  • OK, then go with either using the sql.Date. Overview here: http://stackoverflow.com/questions/18614836/using-setdate-in-preparedstatement – HulkSmash Sep 24 '15 at 16:03
  • @NathanHughes Now I understand, Thank you so much. But I don't know why the same thing TO_DATE() works for so many queries I already wrote. Thanks to BoristheSpider – The Guest Sep 24 '15 at 16:05
  • 1
    @TheGuest because when you do a concatenated query, all variables are converted to `String`, your `TO_DATE` function then turns it back into a `DATE` on the database side. When you use a `PreparedStatement` you pass **typed** data - i.e. you pass your `sqlDate` as a `DATE` and so you no longer need to convert. Win win I say. – Boris the Spider Sep 24 '15 at 16:20

1 Answers1

3

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')";
M A
  • 71,713
  • 13
  • 134
  • 174
  • Thanks for your response. my sqlDate is not a String, it is of type java.sql.Date and DAY also date – The Guest Sep 24 '15 at 16:06
  • @TheGuest `sqlDate.toString()` will get the string representation which is the one you're getting in the sysout. See the second solution which is much better. – M A Sep 24 '15 at 16:07