javasqljdbcmariadbmysql-connector

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


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?


Solution

  • 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.