SQL query in Java Code does not work properly

Question

I have problem with SQL query in JAVA.

JAVA code:

public boolean zeKontrolaExistujiciZalohyTest(String datum) {
        try {
            connected();
            boolean existujeZaloha = false;
            int pocet;
            ResultSet rs = statement.executeQuery("SELECT count(id) FROM "+table_ze+"\n" +
                            "WHERE TO_CHAR(TO_DATE(datum, 'dd.mm.yyyy'), 'mm.yyyy') = TO_CHAR(TO_DATE('"+datum+"', 'dd.mm.yyyy'), 'mm.yyyy')");            

            rs.next();
            pocet = rs.getInt(1);
            rs.close();
            closed();
            if (pocet >= 0) {
                existujeZaloha = true;
            } else {
                existujeZaloha = false;
            }
            return existujeZaloha;
        } catch (Exception e) {
            e.printStackTrace();
            Dialogs.create()
                .title("Exception Dialog")
                .showException(e);
            return true;
        }
    }

SQL query in SQL Developer:

SELECT count(id) FROM pbtest.u_zalohy_energie
WHERE TO_CHAR(TO_DATE(datum, 'dd.mm.yyyy'), 'mm.yyyy') = TO_CHAR(TO_DATE('15.09.2014', 'dd.mm.yyyy'), 'mm.yyyy');

When I run JAVA code, so result a variable is "pocet = 0". But, when I run SQL query in any the SQL Developer, so result column COUNT(id) is "1".

When I do change the SQL query, let me run JAVA code retuns a variable "pocet = 1".

Change sql code:

ResultSet rs = statement.executeQuery("SELECT count(id) FROM "+table_ze+"\n" +
            "WHERE datum = TO_DATE('"+datum+"', 'dd.mm.yyyy')");

Does anyone know where is the problem?

For information: I use an Oracle database.

Thank you.

If you have a date column, compare dates, not character strings. — jarlh, Jan 21 '16 at 09:52
Can you print the query in Java and see if its printing the required SQL statement? — Nitish, Jan 21 '16 at 10:03
Given that datum can be compared successfully to the result of TO_DATE I would say it's a date. The question appears to be why does it work comparing two dates but not if you format both those dates as strings with the same format. — Ben Thurley, Jan 21 '16 at 10:21
@BenThurley As I mentioned in my answer, it could be the time element in the date column causing the date comparison to wrong. — Maheswaran Ravisankar, Jan 21 '16 at 10:26
I would say @jarlh is right that you should use the simpler query that works. There's no need to cast both sides to a string. However, I suspect the question is more to gain understanding of why it doesn't work. If it was me i would look at logging out the exact query string so I can see exactly what runs. Another thing to try is to put both sides of the where clause as columns in a select statement to check they are both the same. It's more than likely a small typo giving a syntax error. — Ben Thurley, Jan 21 '16 at 10:26
@MaheswaranRavisankar he says the same query works when he runs it himself though. It's just when it's run via java that it doesn't work. I would suspect a typo in that case. Also, the simpler date comparison isn't wrong, he says that one works. I think the question is a bit misleading though. — Ben Thurley, Jan 21 '16 at 10:32
He runs the query(manually) by converting the datetime to a pure date only string. Hence it works. — Maheswaran Ravisankar, Jan 21 '16 at 10:34
Using `to_date()` on a `DATE` column to convert it into a date is useless and wrong — , Jan 25 '16 at 08:59

score 1 · Answer 1 · edited May 23 '17 at 12:23

datum is string

SELECT count(id)
FROM pbtest.u_zalohy_energie
WHERE TO_DATE(datum, 'dd.mm.yyyy') = TO_DATE('15.09.2014', 'dd.mm.yyyy');

datum is date

SELECT count(id)
FROM pbtest.u_zalohy_energie
WHERE TRUNC(datum) = TO_DATE('15.09.2014', 'dd.mm.yyyy');

If datum is date, it might contain time component too. So remove it. using TRUNC()

TRUNC(datum) = TO_DATE('15.09.2014', 'dd.mm.yyyy');

Java code:

ResultSet rs = statement.executeQuery("SELECT count(id) FROM "+table_ze+"\n" +
            "WHERE TRUNC(datum) = TO_DATE('"+datum+"', 'dd.mm.yyyy')");

As a side note, use PreparedStatement and bind variables to avoid SQL*Injection

score 0 · Answer 2 · answered Jan 21 '16 at 10:31

Your statement has a syntax error

ResultSet rs = statement.executeQuery("SELECT count(id) FROM "+table_ze+"\n" +
"WHERE TO_CHAR(TO_DATE(datum, 'dd.mm.yyyy'), 'mm.yyyy') = TO_CHAR(TO_DATE('"+datum+"', 'dd.mm.yyyy'), 'mm.yyyy')");

the executed query would be

SELECT count(id) FROM pbtest.u_zalohy_energie\nWHERE TO_CHAR(TO_DATE(datum, 'dd.mm.yyyy'), 'mm.yyyy') = TO_CHAR(TO_DATE('15.09.2014'', 'dd.mm.yyyy'), 'mm.yyyy')")

You should remove the "\n" as this will not lead in a line break.

Try it as

ResultSet rs = statement.executeQuery("SELECT count(id) FROM " + table_ze
+ " WHERE TO_CHAR(TO_DATE(datum, 'dd.mm.yyyy'), 'mm.yyyy') = TO_CHAR(TO_DATE('"+datum+"', 'dd.mm.yyyy'), 'mm.yyyy')");

Take also into consideration the comment from Maheswaran Ravisankar about: "... PreparedStatement and bind variables to avoid SQL*Injection"

score 0 · Answer 3 · answered Jan 25 '16 at 08:51

Thank you for your advice, I solved the problem as follows:

public boolean zeKontrolaExistujiciZalohy(String datum, String typZalohy, String zalohaNaMesic) {
        connected();
        boolean existujeZaloha = false;
        int pocet = 0;
        ResultSet rs;
        PreparedStatement pstmt = null;
        try{
            statement = connection.createStatement();
            String SQL = "SELECT count(id) AS pocet FROM " + table_ze + " WHERE (EXTRACT(MONTH FROM datum)) = (EXTRACT(MONTH FROM to_date(?, 'dd.mm.yyyy'))) "
                    + "AND (EXTRACT(YEAR FROM datum)) = (EXTRACT(YEAR FROM to_date(?, 'dd.mm.yyyy')))"
                    + "AND typ_zalohy = ? "
                    + "AND zaloha_na_mesic = ? ";
            pstmt = connection.prepareStatement(SQL);
            pstmt.setString(1, datum);
            pstmt.setString(2, datum);
            pstmt.setString(3, typZalohy);
            pstmt.setString(4, zalohaNaMesic);
            rs = pstmt.executeQuery();
            while(rs.next()){
                pocet = rs.getInt("pocet");
            }
            rs.close();
            if (pocet > 0) {
                existujeZaloha = true;
            } else {
                existujeZaloha = false;
            }
            return existujeZaloha;            
        }
        catch(SQLException ex){
            Dialogs.create()
                .title("Exception Dialog")
                .showException(ex);
            return true;
        }
    }

SQL query in Java Code does not work properly

3 Answers3