在 Java 中构建 SQL 字符串的最简洁方法 [英] Cleanest way to build an SQL string in Java

问题描述

我想构建一个 SQL 字符串来进行数据库操作(更新、删除、插入、选择，诸如此类)——而不是使用数百万个+"和引号的可怕的字符串 concat 方法，这是不可读的最好 - 必须有更好的方法.

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

可以做的另一件事是将所有查询保存在属性文件中.例如在query.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 + "
" + 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屋！