java - 将数据从mysql内部的json拉到java

Question

所以我有一个我正在尝试组合的索赔脚本，它在购买后对产品进行索赔。该程序是在 Java 内部构建的。我遇到的问题是他们在购买时输入了一个自定义字段，而商店本身将其插入 JSON 格式。所以我需要执行一个查询，将自定义字段拉入 WHERE 语句，如下所示：

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;
    }

所以我需要将我试图从中提取的 JSON 插入到这个结果集中。这就是我试图从数据库中提取并作为名称插入以验证哪个用户购买了要声明的产品。

我得到错误：

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)

这指向：

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'");

我需要执行来检查数据库，找到其中名称 == 自定义字段“1”：变量的行，其中声明 == 0，我需要从该行中提取 ps_item_id 以在另一个文件中执行. 我如何正确访问数组并检查玩家名称是否 == 到“1”：变量？

标签： javamysqljson

Answer 1

在尝试了多种不同的方法后，我通过 JSON 对象找到了如何解析数据。我必须启动第一个 ResultSet，然后从该结果集中提取 custom_field，然后将其拉入 JSON 对象，将“1”解析为字符串，然后使用带有该字符串的另一个 ResultSet 来获取该行

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;
                    
                }
                
            }