execute query in java not return results but in mysql workbench still return results

Question

This query:

    SELECT 
    r.report_id,
    r.user_id,
    u.user_name,
    u.user_mail,
    d.department_name,
    r.report_comment,
    r.report_target_date,
    r.report_create_date,
    r.report_revised_date,
    r.report_root_id,
    report_revised_id
FROM
    report r
        JOIN
    user u ON u.user_id = r.user_id
        JOIN
    department d ON u.department_id = d.department_id
        JOIN
    authority a ON r.user_id = a.user_src_id
        AND a.user_dest_id = 131
WHERE
    r.report_target_date BETWEEN '2014-07-23 23:59:00' AND '2014-08-22 00:00:00'
    AND r.report_comment LIKE '%事務%'

In mysql workbench this query has return value but when using it in java it's not returning anything:

Statement stmt = connection.createStatement(); 
ResultSet rs = null;
rs = stmt.executeQuery(query);

In mysql workbench this query has return value but rs return is empty.

No way. executeQuery never returns null. It might return an empty result set, but never null. Clarify your question, show us the code, and tell us exactly what happens. — JB Nizet, Aug 25 '14 at 07:00
use [`PreparedStatement`](http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html) to set the values in query. — Braj, Aug 25 '14 at 07:01
Maybe that is an encoding problem with you special characters. can you try to remove the part `AND r.report_comment LIKE ..` and try if you get a result? — Jens, Aug 25 '14 at 07:02
remove r.report_comment LIKE '%事務%' execute is working well. I don't know why. — user2024503, Aug 25 '14 at 07:31
first remove last where clause and try if it returns valid result. If so then there might be a issue using Unicode characters — Thusitha Thilina Dayaratne, Aug 25 '14 at 07:41

score 2 · Answer 1 · answered Aug 25 '14 at 07:07

2

You pass Japanese characters into the query. This could easily be a character encoding issue.

Use a PreparedStatement and insert the value through a setString() call which will properly take care of the encoding.

Modify your query to have a parameter (marked by a question mark):

...
WHERE
    r.report_target_date BETWEEN '2014-07-23 23:59:00' AND '2014-08-22 00:00:00'
    AND r.report_comment LIKE ?

And the Java code:

PreparedStatement ps = connection.prepareStatement(query);
ps.setString(1, "%事務%"); // Parameter index is 1-based
ResultSet rs = ps.executeQuery();

answered Aug 25 '14 at 07:07

icza

389,944
63
907
827

In the past i already using PreparedStatement and set value but not work. After that i try execute directly like that but still not work. What wrong in this step ?? – user2024503 Aug 25 '14 at 07:33
1

Make sure your Java source files are using UTF-8, and also the Java compiler (javac) interprets them as UTF-8. Also make sure your JDBC also uses UTF-8, here is how to do it: http://stackoverflow.com/questions/3040597/jdbc-character-encoding – icza Aug 25 '14 at 07:40
Agree with others that looking at the encoding of Japanese characters is the right direction to go. – BruceRudd Aug 25 '14 at 14:04

execute query in java not return results but in mysql workbench still return results

1 Answers1