Pulling data from json inside mysql to java

So I have a claim script that I’m trying to put together, that claims the product after purchase. The program is built inside of Java. The problem I’m running into is that there is a custom field they input on their purchase, and the store itself inserts it into a JSON format. So I need to execute a query that pulls the custom field into the WHERE statement, like so :

public class StoreClaim implements Runnable {

    public static final String HOST = "104.161.43.58"; // website ip address 
    public static final String USER = "eseezjte_forum";
    public static final String PASS = "Fishsticks123";
    public static final String DATABASE = "eseezjte_forum";

    private Player player;
    private Connection conn;
    private Statement stmt;

    
    public StoreClaim(Player player) {
        this.player = player;
    }
    
    

    public void run() {
        try {
            if (!connect(HOST, DATABASE, USER, PASS)) {
                return;
            }

            
            String name = player.getUsername().replace("_", " ");
            ResultSet connect = executeQuery("SELECT ps_claimed, ps_item_id, ps_custom_fields->$.1 AS claimed FROM nexus_purchases WHERE ps_custom_fields->$.1 = '"+name+"' AND ps_claimed='0'");
            while (connect.next()) {
                player.sm("WORKING!");
                return;
                
            }
            

            destroy();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

/**
     *
     * @param host the host ip address or url
     * @param database the name of the database
     * @param user the user attached to the database
     * @param pass the users password
     * @return true if connected
     */
    public boolean connect(String host, String database, String user, String pass) {
        try {
            this.conn = DriverManager.getConnection("jdbc:mysql://"+host+":3306/"+database, user, pass);
            return true;
        } catch (SQLException e) {
            System.out.println("Failing connecting to database!");
            return false;
        }
    }

    /**
     * Disconnects from the MySQL server and destroy the connection
     * and statement instances
     */
    public void destroy() {
        try {
            conn.close();
            conn = null;
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
        } catch(Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * Executes an update query on the database
     * @param query
     * @see {@link Statement#executeUpdate}
     */
    public int executeUpdate(String query) {
        try {
            this.stmt = this.conn.createStatement(1005, 1008);
            int results = stmt.executeUpdate(query);
            return results;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    /**
     * Executres a query on the database
     * @param query
     * @see {@link Statement#executeQuery(String)}
     * @return the results, never null
     */
    public ResultSet executeQuery(String query) {
        try {
            this.stmt = this.conn.createStatement(1005, 1008);
            ResultSet results = stmt.executeQuery(query);
            return results;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

So i need to insert into this Result Set the JSON I’m trying to pull from. This is what I’m trying to pull from the database and insert as the name to verify which user bought the product to claim.

I get the error :

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>$.1 AS claimed FROM nexus_purchases WHERE ps_custom_fields->$.1 = 'quantum' ...' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1557)
    at quantum.site.StoreClaim.executeQuery(StoreClaim.java:139)
    at quantum.site.StoreClaim.run(StoreClaim.java:45)
    at java.lang.Thread.run(Thread.java:748)
java.lang.NullPointerException
    at quantum.site.StoreClaim.run(StoreClaim.java:46)
    at java.lang.Thread.run(Thread.java:748)

which points to :

ResultSet connect = executeQuery("SELECT ps_claimed, ps_item_id, ps_custom_fields->$.1 AS claimed FROM nexus_purchases WHERE ps_custom_fields->$.1 = '"+name+"' AND ps_claimed='0'");

I need the execute to check the databse, find a row in which the name == the custom field “1”: variable, and where claimed == 0, and I will need to pull the ps_item_id from that row to execute inside another file. How do i properly access the array and check if the player name is == to the “1”: variable?

Answer

After trying multiple different ways, I found through JSON object how to parse the data. I had to initiate a first ResultSet, and than from that result set, pull the custom_field and than pull that into a JSON Object, parse the “1” to a string, and than use another ResultSet with that string to get that row

String name = player.getUsername().replace("_", " ");
ResultSet connect = executeQuery("SELECT * FROM nexus_purchases WHERE ps_claimed='0'");
            
while (connect.next()) {
    String str = connect.getString("ps_custom_fields");
    JSONObject obj = new JSONObject(str);
    String n = obj.getString("1");
    
    ResultSet check = executeQuery("SELECT * FROM nexus_purchases WHERE '"+n+"' = '"+name+"' AND ps_claimed='0'");
    while (check.next()) {
        player.sm("WORKING!");
        return;
    }
}