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 an INSERT. This is not only prone to SQL Injections, but it is also pretty .. cumbersome ;) Possibly a value contained a singlequote and caused your query to be syntactically invalid.

I recommend you to replace Statement by PreparedStatement (tutorial here). This way you can nicely put fullworthy Java objects in a SQL statement by value index without worrying about strings which may syntactically break the SQL query (and thus also SQL injection risks). Here's a kickoff example:

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

public void create(String cityCode, Weather weather) throws SQLException {
    Connection connection = null;
    PreparedStatement statement = null;

    try {
        connection = database.getConnection();
        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();
    } finally {
        if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
        if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
    }
}

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.

Answer 3

Like all the others say, you really should convert it to use PreparedStatements for a number of reasons. You are most likely getting the error (you didn't post the exact ORA error) because you passing in String type values, but you didn't wrap them in single quotes in your hard coded query.

If textual_description and for_description where the only String type columns in your query, then your query would need to look like this:

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

Notice the single quotes surrounding those values now.