CODEWITHSUNDEEP
javaoracle-databasejdbc
Jaydeep
Jaydeep

Reputation: 149

Query returns less rows when used in JDBC then SQL developer

I am using JDBC to connect to oracle database using prepareStatement and executeQuery. But when I use the same query on database from SQL developer, I found that rows returned through JDBC are quite less than what they actually are.

Is this possible due to connection issues or query or statement. Can anyone help me out.

Here's the query I ran in SQL developer-

select count(*) 
from xxx 
where (TIME_STAMP between 
           TO_TIMESTAMP('2015-12-22 09:45:37.0','YYYY-MM-DD HH24:MI:SS.ff') 
           and TO_TIMESTAMP('2015-12-22 09:55:37.0','YYYY-MM-DD HH24:MI:SS.ff')) 
AND (customerid not like '[null]') 
AND (applicationid not like '[null]') 
and (status not like '-');

EDIT: I used a variable counter in while(resultSet.next()) to count number of rows returned, from where I got to know that number of rows returned are different. Here is the JDBC code below-

 String query = "select * from xxx where 
 (TIME_STAMP between TO_TIMESTAMP('2015-12-22 09:45:37.0','YYYY-MM-DD HH24:MI:SS.ff') and 
 TO_TIMESTAMP('2015-12-22 09:55:37.0','YYYY-MM-DD HH24:MI:SS.ff')) 
 AND (customerid not like '[null]') 
 AND (applicationid not like '[null]') 
 and (status not like '-')";
        stmt = con.prepareStatement(query);
        rs = stmt.executeQuery(query);
        while (rs.next()) {
 counter++; }

Upvotes: 1

Views: 1835

Answers (1)

mostar
mostar

Reputation: 4831

Firstly, please make sure that you connect to the same database. (If you have possible others that you use for testing or so)

Then, looking at your query, the most suspicious point seems to be the date conversions. You can analyze the two different result sets and check to see if the date period you entered matches with the results. If one of them contains unexpected results in terms of date values, then the issue may be related to default timezone of your Java Virtual Machine or Oracle Server.

Upvotes: 1

Related Questions