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
– JB Nizet, Commented Aug 25, 2014 at 7:00
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
– Jens, Commented Aug 25, 2014 at 7:02
remove r.report_comment LIKE '%事務%' execute is working well. I don't know why. — user2024503
– user2024503, Commented Aug 25, 2014 at 7:31

icza · Accepted Answer · 2014-08-25 07:07:05Z

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, 2014 at 7:07

icza

423k78 gold badges1k silver badges905 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2024503 Over a year ago

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 ??

icza Over a year ago

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: stackoverflow.com/questions/3040597/jdbc-character-encoding

BruceRudd Over a year ago

Agree with others that looking at the encoding of Japanese characters is the right direction to go.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related