JDBC is removing part of my query when I run it in Java but it works in my SQL editor

Question

This is my fairly simple SQL:

SELECT
    CONCAT(
    '{ ',
        '"test": ',
        '{',
                '"a": true,',   /* don't set to false  */
                '"b": { "t": true },',
                '"c": 2',
        '},',
        '"b": { "t": true } ',
    '}'
    ) AS test

I'm using mysql-connector-java-5.1.7-bin.jar in Java to connect to a MariaDB database.

In my SQL editor if I run that sql this is what gets returned with no errors:

{ "test": {"a": true,"b": { "t": true },"c": 2},"b": { "t": true } }

However, for some reason Java returns:

{ "test": {"a": true,"b": ,"c": 2},"b": { "t": true } }

Notice that the first "b" has its value { "t": true } completely removed for some reason, but only the first instance of "b", the second one is fine.

This is what I'm doing in Java:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class testSQL {
    
    private static final String DB_URL = "jdbc:mysql://localhost:3306/database?characterEncoding=utf-8";
    private static final String DB_USER = "your_username";
    private static final String DB_PASSWORD = "your_password";

    public static void main(String[] args) {
        String sql = "SELECT\n\tCONCAT(\n\t'{ ',\n\t\t'\"test\": ',\n\t\t'{',\n\t\t\t\t'\"a\": true,',\t/* don't set to false  */\n\t\t\t\t'\"b\": { \"t\": true },',\n\t\t\t\t'\"c\": 2',\n\t\t'},',\n\t\t'\"b\": { \"t\": true } ',\n\t'}'\n\t) AS test";

        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
                PreparedStatement stmt = conn.prepareStatement(sql);
                ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                System.out.println(rs.getObject(1));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

What could be causing this?

We can not know what your code actually executes if you don't share a minimal reproducible example. — aled
– aled, Commented May 6 at 1:26
I included everything you needed to know in order to reproduce the issue in my original post. But if it helps, I have now gone in and fully built out an example Java class if you needed it. — Curtis
– Curtis, Commented May 6 at 3:06
Do you really expect a serious answer when using a database driver which reached end of life and was released 17 years ago? — Georg Richter
– Georg Richter, Commented May 6 at 13:00

Mark Rotteveel · Accepted Answer · 2025-05-26 11:22:28Z

3

I would say that you hit some kind of escaping mode, like ODBC escape syntax as seen in this or this SO answer, posing problems with other RDBMS, probably inherited from Oracle; note that MySQL-based RDBMS would ignore the keyword after the { (thus eating this first word, whatever it is) while other RDBMS only replace well-defined ones;
or it could be interpreted as some comment mode à la DB2.

Someone got luck embedding it in " SQL strings instead of ' strings,
but frankly I don't see what would trigger it in your string.
To circumvent it I would try separating the { from everything else, with something like CONCAT(…, '{', '"t": ', …).

edited May 26 at 11:22

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

answered May 6 at 5:58

Guillaume Outters

7,5041 gold badge22 silver badges28 bronze badges

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

2 Comments

Curtis May 6 at 18:12

After playing around with it, I figured out it was an issue with the apostrophe in the comment of my sql /* don't set to false */. Interestingly, if I have a comment with an opening and closing apostrophe eg: /* use 'do not' to make it work */ it works fine. But the open ended apostrophe from don't for some reason had an issue. My SQL editor handled it fine, maybe its just an issue with my old version of the JDBC driver though.

Guillaume Outters May 6 at 19:12

You could post it as an auto-answer. This kind of parsing bugs appearing on corner cases by drivers that sincerily try to be smart are quite common (I being a PHP guy have encountered some in PHP's PostgreSQL client layer), having it clearly referenced would be a plus.

Reet · Accepted Answer · 2025-05-07 03:19:51Z

1

Issue is with EscapeTokenizer.java in mysql-connector-java-5.1.7. Apostrophe in comment is not properly interpreted.

This works fine with mysql-connector-java-5.1.49. If upgrade is not an option, you will have to remove or escape the apostrophe in comment.

answered May 7 at 3:19

Reet

791 silver badge5 bronze badges

Comments

jialin.zhou · Accepted Answer · 2025-05-06 02:00:27Z

1

1.Do not use `getObject(0)`

In JDBC, column indexes start at 1, not 0. Using index 0 can lead to undefined behavior, often resulting in partial or incorrect data being returned.

When you access a column via rs.getObject(0), it might partially return data or behave inconsistently, exactly as you're experiencing.

Maybe you can use :

System.out.println(rs.getString(1)); // or
System.out.println(rs.getString("test"));

2. you can print raw string and length to debug potential truncation

String json = rs.getString("test");
System.out.println("Length: " + json.length());
System.out.println("Raw JSON: " + json);

3.If you must use the old driver, avoid JSON concatenation – encode it

String base64 = rs.getString("encoded_json");
byte[] decodedBytes = Base64.getDecoder().decode(base64);
String json = new String(decodedBytes, StandardCharsets.UTF_8);
System.out.println(json);

To be honest, i cannot make sure above content is correct. You can try and test it.

answered May 6 at 2:00

jialin.zhou

1045 bronze badges

2 Comments

Curtis May 6 at 2:39

thanks for the reply. The index of 0 was actually a error in copying the code over. I am using index 1 in my actual code. I just didnt want to copy the entire section of code that handles the result. I also tried creating it as a Prepared Statement and executing it that way and got the same issue.

Curtis May 6 at 3:19

Also, smart plan to encode the string. My version of mysql doesnt have a base64 function. I used Hex() around the CONCAT. But after converting back from hex, the same section is still missing. And again, it works fine in my SQL editor (even using the hex method) but just not when run through Java.

Curtis · Accepted Answer · 2025-05-06 21:33:09Z

The Issue

After some experimentation, I discovered that the issue was caused by the comment line in the SQL query that I was running:

/* don't set to false */

Apparently, the lone apostrophe (') in the word "don't" confused the JDBC driver, even though it's inside of a comment and should have been ignored. My SQL editor handled it just fine, so the issue seems specific to JDBC. I'm not certain whether this is a bug in the particular version I'm using (mysql-connector-java-5.1.7) or if it persists in newer versions as well.

I initially overlooked the problem because I had used apostrophes in other comments without issue. But in hindsight, every other case involved a paired apostrophe, which didn’t trigger the problem.

For example:

/* Set the setting to 'yes' or 'no' */

...works just fine.

The Solution

Here are a few workarounds that resolved the issue for me:

Remove any unpaired apostrophe — e.g. write /* dont */ or /* do not */ (the simplest fix).
Use “curly” apostrophes — e.g. /* don‘t */ or /* don’t */ (the typographic versions).
Escape the apostrophe — e.g. /* don\'t */ or /* don''t */ both ways of escaping the apostrophe worked.

Keep in mind if you are writing the query as a String in Java and chose to escape the apostrophe with a backslash you will also have to escape the backslash — e.g. "/* don\\'t */"

Collectives™ on Stack Overflow

JDBC is removing part of my query when I run it in Java but it works in my SQL editor

4 Answers 4

2 Comments

Comments

1.Do not use `getObject(0)`

2. you can print raw string and length to debug potential truncation

3.If you must use the old driver, avoid JSON concatenation – encode it

2 Comments

The Issue

The Solution

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1.Do not use getObject(0)

2. you can print raw string and length to debug potential truncation

3.If you must use the old driver, avoid JSON concatenation – encode it

2 Comments

The Issue

The Solution

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

1.Do not use `getObject(0)`