INSERT INTO whit more variable-CodePudding

I want to add a new query to my database table, but with the difference that I used more variables in it, like the code below, but it gives me a syntax error.

public String insertData(Charger charger) {
    try {
    String sql = " INSERT INTO  `online-shop`.charger"
              " VALUES(?,?,?,?)"
              "id="  charger.getId()
              "color="   charger.getColor()
              "brand="   charger.getBrand()
              "productYear="   charger.getProductYear();
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setString(1, "id");
    preparedStatement.setString(2, "color");
    preparedStatement.setString(3, "brand");
    preparedStatement.setString(4, "productYear");
    boolean execute = preparedStatement.execute();
    if (execute) {
        System.out.println("insert complicated");
    } else {
        System.out.println("insert have error");
    }
}
    catch (SQLException e){
        System.out.println(e.getMessage());
    }
    return null;

And the error it gives me is this

java.sql.SQLSyntaxErrorException: 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 'id=3color=yellowbrand=lenovoproductYear=1999' at line 1

CodePudding user response：

Try the following syntax, i believe the error occured from line 6 onwards: {

public String insertData(Charger charger) {
    try {
        String sql = "INSERT INTO `online-shop`.charger (id, color, brand, productYear) VALUES (?, ?, ?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, charger.getId());
        preparedStatement.setString(2, charger.getColor());
        preparedStatement.setString(3, charger.getBrand());
        preparedStatement.setInt(4, charger.getProductYear());
        boolean execute = preparedStatement.execute();
        if (execute) {
            System.out.println("insert complicated");
        } else {
            System.out.println("insert have error");
        }
    }
    catch (SQLException e){
        System.out.println(e.getMessage());
    }
    return null;
}

}

CodePudding user response：

Your current syntax is off, both on the SQL and Java side. Here is the corrected version:

String sql = "INSERT INTO `online-shop`.charger (id, color, brand, year) ";
sql  = "VALUES (?, ?, ?, ?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, charger.getId());
preparedStatement.setString(2, charger.getColor());
preparedStatement.setString(3, charger.getBrand());
preparedStatement.setString(4, charger.getProductYear());
boolean execute = preparedStatement.execute();

The values are bound to the ? placeholders by calling setters on the PreparedStatement.