在Java中构建SQL字符串的最简洁方法 [英] Cleanest way to build an SQL string in Java
问题描述
我想构建一个SQL字符串来进行数据库操作(更新,删除,插入,选择,那种事情) - 而不是使用数以百万计的+和引号的糟糕的字符串连接方法,这是不可读的最好的 - 必须有更好的方法。
I want to build an SQL string to do database manipulation (updates, deletes, inserts, selects, that sort of thing) - instead of the awful string concat method using millions of "+"'s and quotes which is unreadable at best - there must be a better way.
我确实想过使用MessageFormat - 但它应该用于用户消息,虽然我认为它会做一个合理的工作 - 但我想应该有更多的东西与java sql库中的SQL类型操作对齐。
I did think of using MessageFormat - but its supposed to be used for user messages, although I think it would do a reasonable job - but I guess there should be something more aligned to SQL type operations in the java sql libraries.
Groovy会不会有任何好处?
Would Groovy be any good?
推荐答案
首先考虑在预准备语句中使用查询参数:
First of all consider using query parameters in prepared statements:
PreparedStatement stm = c.prepareStatement("UPDATE user_table SET name=? WHERE id=?");
stm.setString(1, "the name");
stm.setInt(2, 345);
stm.executeUpdate();
可以做的另一件事是将所有查询保存在属性文件中。例如,queries.properties文件中的
可以放置上述查询:
The other thing that can be done is to keep all queries in properties file. For example in a queries.properties file can place the above query:
update_query=UPDATE user_table SET name=? WHERE id=?
然后借助简单的实用程序类:
Then with the help of a simple utility class:
public class Queries {
private static final String propFileName = "queries.properties";
private static Properties props;
public static Properties getQueries() throws SQLException {
InputStream is =
Queries.class.getResourceAsStream("/" + propFileName);
if (is == null){
throw new SQLException("Unable to load property file: " + propFileName);
}
//singleton
if(props == null){
props = new Properties();
try {
props.load(is);
} catch (IOException e) {
throw new SQLException("Unable to load property file: " + propFileName + "\n" + e.getMessage());
}
}
return props;
}
public static String getQuery(String query) throws SQLException{
return getQueries().getProperty(query);
}
}
您可以按如下方式使用您的查询:
you might use your queries as follows:
PreparedStatement stm = c.prepareStatement(Queries.getQuery("update_query"));
这是一个相当简单的解决方案,但效果很好。
This is a rather simple solution, but works well.
这篇关于在Java中构建SQL字符串的最简洁方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!