Java is Throwing Syntax Error When I Try to Execute an SQL Query

Im building a java microservice that uses AWS Aurora Mysql for some operations. the code connects to the database fine and the first 3 queries I had were throwing a similar error till I realized I was missing semicolons. The final query (the update statement) however is still giving me the same syntax error even with the semi colon and I can’t seem to figure out why.

here is the code below

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) {}
        }

The error I am getting for that update query is

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

as mentioned the same error was showing for the previous queries before I add a semicolon at the end of them, now only shows for the final one.

>Solution :

Since you have created the column secretNumber as a VARCHAR(20) I would check to make sure secretNumber is at most 20 characters long before adding it.

On line 30 you have the following.

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

So, you can either use the debugger, or on line 31 you can put the following.

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

If that doesn’t work, you can try single-quotes around the values, I guess.

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

Additionally, a semi-colon at the end of a single statement is not required.