Insert Java variable using Java in SQL

Question

I was trying to do:

 String sql = "INSERT INTO CURRENT_WEATHER_US VALUES("+city_code+",   
"+object.city+","+object.region+","+object.country+","+object.wind_chill+",  
"+object.wind_direction+", "+object.wind_speed+","+object.humidity+","+object.visibility+", 
"+object.pressure+","+object.rising+",  
"+object.sunrise+","+object.sunset+","+object.textual_description+",  
"+object.condition_code+","+object.temp+","+object.for_temp_high+",  
"+object.for_temp_low+","+object.for_description+","+object.forecast_code+")";   

  stmt.execute(sql);  

Error is missing comma

Please Help

Answer 1

This is not really the way you're supposed to construct and execute a SQL INSERT query with variables. This is not only prone to SQL injection attacks, but it is also pretty .. cumbersome ;) Possibly a value contained a singlequote and caused your query to be syntactically invalid.

Just do not string-concatenate variables into a SQL string. Instead, use PreparedStatement (tutorial here) in combination with ? as placeholder for the variable in the SQL string. This way you can nicely put fullworthy Java objects (including Date and InputStream!) in a SQL statement by value index without worrying about characters in strings which may syntactically break the SQL query (and thus also induce SQL injection risks).

Here's a kickoff example based on your original SQL query:

private static final String SQL_INSERT = "INSERT INTO CURRENT_WEATHER_US"
    + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

public void create(String cityCode, Weather weather) throws SQLException {
    try (
        Connection connection = database.getConnection();
        PreparedStatement statement = connection.prepareStatement(SQL_INSERT);
    ) {
        statement.setString(1, cityCode);
        statement.setString(2, weather.getCity());
        statement.setString(3, weather.getRegion());
        // ...
        statement.setString(20, weather.getForecastCode());
        statement.executeUpdate();
    }
}

To learn more about using basic JDBC the proper way, you may find this article useful.

Hope this helps.

Answer 2

You should look into using PrepairedStatements instead of building Strings. They are quicker and take care of many pitfalls related to quoting and escaping values.