在 SQL 中使用 Java 插入 Java 变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2256504/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert Java variable using Java in SQL
提问by Mohit BAnsal
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
请帮忙
采纳答案by BalusC
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.
这并不是您真正应该使用变量构造和执行 SQL INSERT 查询的方式。这不仅容易受到SQL 注入攻击，而且还相当……麻烦；) 可能一个值包含单引号并导致您的查询在语法上无效。
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 Dateand 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).
只是不要将变量字符串连接到 SQL 字符串中。相反，将PreparedStatement（此处的教程）与?作为 SQL 字符串中变量的占位符结合使用。通过这种方式，您可以通过值索引将完整的 Java 对象（包括Date和InputStream！）很好地放在SQL 语句中，而不必担心字符串中的字符可能会在语法上破坏 SQL 查询（因此也会导致 SQL 注入风险）。
Here's a kickoff example based on your original SQL query:
以下是基于原始 SQL 查询的启动示例：
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 JDBCthe proper way, you may find this articleuseful.
要了解有关以正确方式使用基本 JDBC的更多信息，您可能会发现本文很有用。
Hope this helps.
希望这可以帮助。
回答by Chris Nava
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.
您应该考虑使用 PrepairedStatements 而不是构建字符串。它们更快，并处理与引用和转义值相关的许多陷阱。
回答by Doug Porter
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.
就像其他人所说的那样，出于多种原因，您确实应该将其转换为使用 PreparedStatements。您很可能会收到错误（您没有发布确切的 ORA 错误），因为您传入了 String 类型的值，但您没有在硬编码查询中将它们用单引号括起来。
If textual_description and for_description where the only String type columns in your query, then your query would need to look like this:
如果 textual_description 和 for_description 是您查询中唯一的 String 类型列，那么您的查询将需要如下所示：
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.
现在注意围绕这些值的单引号。
