Java在尝试执行SQL查询时抛出语法错误

nhn9ugyo  于 2023-05-21  发布在  Java
关注(0)|答案(2)|浏览(195)

我正在构建一个Java微服务,它使用AWS Aurora Mysql进行一些操作。代码连接到数据库罚款和前3个查询我有抛出类似的错误,直到我意识到我是失踪的分号。然而,最后一个查询(update语句)仍然给我同样的语法错误,即使使用了分号,我也不知道为什么。
下面是代码

try {
            conn = DriverManager.getConnection(jdbcUrl);

            setupStatement = conn.createStatement();
            idInsertStatement = conn.createStatement();
            secretNumberUpdateStatement = conn.createStatement();

            String createTableIfNotExists = "CREATE TABLE IF NOT EXISTS secret_number_generator(id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, secret_number VARCHAR(20);";

            String generateSecretId = "INSERT INTO secret_number_generator VALUES(null,null);";
            
            String getLastRecordId = "SELECT id FROM secret_number_generator ORDER BY id DESC LIMIT 1;";
            
            setupStatement.addBatch(createTableIfNotExists);
            idInsertStatement.addBatch(generateSecretId);
            setupStatement.executeBatch();
            idInsertStatement.executeBatch();
            readStatement = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            resultSet = readStatement.executeQuery(getLastRecordId);
         
            while(resultSet.next()){
                id = String.valueOf(resultSet.getLong("id"));
            }

            /*
              Logic for setting up secretNumber
            */

            }
            secretNumber = "R" + env + id + randomDigit;
            
            String updateSecretNumber = "UPDATE secret_number_generator SET secret_number = " + secretNumber + " WHERE id = " + id + ";";
            
            secretNumberUpdateStatement.addBatch(updateSecretNumber);
            secretNumberUpdateStatement.executeBatch();

            resultSet.close();
            setupStatement.close();
            idInsertStatement.close();
            secretNumberUpdateStatement.close();
            readStatement.close();
            conn.close();

        } catch (SQLException ex) {
            // Handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        } finally {
            System.out.println("Closing the connection.");
            if (conn != null) try { conn.close(); } catch (SQLException ignore) {}
        }

我得到的更新查询的错误是

SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

如前所述,在我在前面的查询末尾添加分号之前,前面的查询显示了相同的错误,现在只显示最后一个查询。

qyzbxkaa

qyzbxkaa1#

由于您已经将列secretNumber创建为VARCHAR(20),因此在添加它之前,我将检查以确保secretNumber的长度最多为20个字符。
第30章你有下面的

secretNumber = "R" + env + id + randomDigit;

因此,您可以使用调试器,或者在第31行放置以下内容。

if (secretNumber.length() > 20) throw new Exception();

如果这不起作用,我想你可以尝试在值周围加上单引号。

String updateSecretNumber = 
   "UPDATE secret_number_generator " +
   "SET secret_number = '" + secretNumber + "' " +
   "WHERE id = '" + id + "'";

此外,不需要在单个语句的末尾使用分号。

hmae6n7t

hmae6n7t2#

虽然前面的示例为您提供了所需的结果,但它很容易在恶意变量值上中断。此版本针对SQL注入进行了强化:
像这样使用PreparedStatement:

PreparedStatement ps = connection.prepareStatement(
    "UPDATE secret_number_generator " +
    "SET secret_number = ? " +
    "WHERE id = ?";
ps.setString(1, secretNumber);
ps.setString(2, id);
int count = ps.executeUpdate();
System.out.println("updated %d rows", count);

相关问题