在 Java 中构建 SQL 字符串的最简洁方法

Cleanest way to build an SQL string in Java

提问by Vidar

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.

我想构建一个 SQL 字符串来进行数据库操作（更新、删除、插入、选择等） - 而不是使用数百万个“+”和引号的可怕的字符串 concat 方法，这充其量是不可读的 - 那里必须是更好的方法。

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.

我确实考虑过使用 MessageFormat - 但它应该用于用户消息，虽然我认为它会做一个合理的工作 - 但我想应该有一些更符合 java sql 库中的 SQL 类型操作的东西。

Would Groovy be any good?

Groovy 会有什么好处吗？

采纳答案by Piotr Kochański

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

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:

可以做的另一件事是将所有查询保留在属性文件中。例如在一个 queries.properties 文件中可以放置上面的查询：

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.

这是一个相当简单的解决方案，但效果很好。

回答by GaryF

I tend to use Spring's Named JDBC Parameters so I can write a standard string like "select * from blah where colX=':someValue'"; I think that's pretty readable.

我倾向于使用 Spring 的命名 JDBC 参数，因此我可以编写一个标准字符串，例如“select * from blah where colX=':someValue'”; 我认为这很好读。

An alternative would be to supply the string in a separate .sql file and read the contents in using a utility method.

另一种方法是在单独的 .sql 文件中提供字符串并使用实用程序方法读取内容。

Oh, also worth having a look at Squill: https://squill.dev.java.net/docs/tutorial.html

哦，也值得一看 Squill：https://squill.dev.java.net/docs/tutorial.html

回答by Rowan

If you put the SQL strings in a properties file and then read that in you can keep the SQL strings in a plain text file.

如果将 SQL 字符串放在属性文件中，然后将其读取，则可以将 SQL 字符串保存在纯文本文件中。

That doesn't solve the SQL type issues, but at least it makes copying&pasting from TOAD or sqlplus much easier.

这并不能解决 SQL 类型问题，但至少它使从 TOAD 或 sqlplus 复制和粘贴变得更加容易。

回答by Jared

Why do you want to generate all the sql by hand? Have you looked at an ORM like Hibernate Depending on your project it will probably do at least 95% of what you need, do it in a cleaner way then raw SQL, and if you need to get the last bit of performance you can create the SQL queries that need to be hand tuned.

为什么要手工生成所有的sql？您是否看过像 Hibernate 这样的 ORM 根据您的项目，它可能会完成您需要的至少 95% 的工作，以更简洁的方式完成，然后是原始 SQL，如果您需要获得最后一点性能，您可以创建需要手动调整的 SQL 查询。

回答by tcurdt

I am wondering if you are after something like Squiggle. Also something very useful is jDBI. It won't help you with the queries though.

我想知道您是否在追求Squiggle 之类的东西。还有一些非常有用的东西是jDBI。但它不会帮助您进行查询。

回答by Bent André Solheim

I would have a look at Spring JDBC. I use it whenever I need to execute SQLs programatically. Example:

我会看看Spring JDBC。每当我需要以编程方式执行 SQL 时，我都会使用它。例子：

int countOfActorsNamedJoe
    = jdbcTemplate.queryForInt("select count(0) from t_actors where first_name = ?", new Object[]{"Joe"});

It's really great for any kind of sql execution, especially querying; it will help you map resultsets to objects, without adding the complexity of a complete ORM.

它对于任何类型的 sql 执行都非常有用，尤其是查询；它将帮助您将结果集映射到对象，而不会增加完整 ORM 的复杂性。

回答by Ashley Mercer

One technology you should consider is SQLJ- a way to embed SQL statements directly in Java. As a simple example, you might have the following in a file called TestQueries.sqlj:

您应该考虑的一项技术是SQLJ——一种直接在 Java 中嵌入 SQL 语句的方法。作为一个简单的示例，您可能在名为 TestQueries.sqlj 的文件中包含以下内容：

public class TestQueries
{
    public String getUsername(int id)
    {
        String username;
        #sql
        {
            select username into :username
            from users
            where pkey = :id
        };
        return username;
    }
}

There is an additional precompile step which takes your .sqlj files and translates them into pure Java - in short, it looks for the special blocks delimited with

还有一个额外的预编译步骤，它将您的 .sqlj 文件转换为纯 Java - 简而言之，它查找以

#sql
{
    ...
}

and turns them into JDBC calls. There are several key benefits to using SQLJ:

并将它们转换为 JDBC 调用。使用 SQLJ 有几个主要好处：

• completely abstracts away the JDBC layer - programmers only need to think about Java and SQL
• the translator can be made to check your queries for syntax etc. against the database at compile time
• ability to directly bind Java variables in queries using the ":" prefix

• 完全抽象出JDBC层——程序员只需要考虑Java和SQL
• 可以让翻译器在编译时根据数据库检查语法等查询
• 能够使用“:”前缀在查询中直接绑定 Java 变量

There are implementations of the translator around for most of the major database vendors, so you should be able to find everything you need easily.

大多数主要数据库供应商都有翻译器的实现，因此您应该能够轻松找到所需的一切。

回答by JeeBee

How do you get string concatenation, aside from long SQL strings in PreparedStatements (that you could easily provide in a text file and load as a resource anyway) that you break over several lines?

除了 PreparedStatements 中的长 SQL 字符串（您可以轻松地在文本文件中提供并作为资源加载）之外，您如何获得字符串连接？

You aren't creating SQL strings directly are you? That's the biggest no-no in programming. Please use PreparedStatements, and supply the data as parameters. It reduces the chance of SQL Injection vastly.

您不是直接创建 SQL 字符串吗？这是编程中最大的禁忌。请使用 PreparedStatements，并提供数据作为参数。它大大减少了 SQL 注入的机会。

回答by james

I second the recommendations for using an ORM like Hibernate. However, there are certainly situations where that doesn't work, so I'll take this opportunity to tout some stuff that i've helped to write: SqlBuilderis a java library for dynamically building sql statements using the "builder" style. it's fairly powerful and fairly flexible.

我支持使用像 Hibernate 这样的 ORM 的建议。但是，肯定有一些情况是行不通的，所以我将借此机会吹捧一些我帮助编写的内容：SqlBuilder是一个 Java 库，用于使用“构建器”样式动态构建 sql 语句。它相当强大且相当灵活。

回答by Natalia

I have been working on a Java servlet application that needs to construct very dynamic SQL statements for adhoc reporting purposes. The basic function of the app is to feed a bunch of named HTTP request parameters into a pre-coded query, and generate a nicely formatted table of output. I used Spring MVC and the dependency injection framework to store all of my SQL queries in XML files and load them into the reporting application, along with the table formatting information. Eventually, the reporting requirements became more complicated than the capabilities of the existing parameter mapping frameworks and I had to write my own. It was an interesting exercise in development and produced a framework for parameter mapping much more robust than anything else I could find.

我一直在开发一个 Java servlet 应用程序，它需要为临时报告目的构造非常动态的 SQL 语句。该应用程序的基本功能是将一堆命名的 HTTP 请求参数提供给一个预编码的查询，并生成一个格式良好的输出表。我使用 Spring MVC 和依赖注入框架将所有 SQL 查询存储在 XML 文件中，并将它们与表格式信息一起加载到报告应用程序中。最终，报告要求变得比现有参数映射框架的功能更复杂，我不得不自己编写。这是一个有趣的开发练习，并产生了一个比我能找到的任何其他东西都更强大的参数映射框架。

The new parameter mappings looked as such:

新的参数映射如下所示：

select app.name as "App", 
       ${optional(" app.owner as "Owner", "):showOwner}
       sv.name as "Server", sum(act.trans_ct) as "Trans"
  from activity_records act, servers sv, applications app
 where act.server_id = sv.id
   and act.app_id = app.id
   and sv.id = ${integer(0,50):serverId}
   and app.id in ${integerList(50):appId}
 group by app.name, ${optional(" app.owner, "):showOwner} sv.name
 order by app.name, sv.name

The beauty of the resulting framework was that it could process HTTP request parameters directly into the query with proper type checking and limit checking. No extra mappings required for input validation. In the example query above, the parameter named serverIdwould be checked to make sure it could cast to an integer and was in the range of 0-50. The parameter appIdwould be processed as an array of integers, with a length limit of 50. If the field showOwneris present and set to "true", the bits of SQL in the quotes will be added to the generated query for the optional field mappings. field Several more parameter type mappings are available including optional segments of SQL with further parameter mappings. It allows for as complex of a query mapping as the developer can come up with. It even has controls in the report configuration to determine whether a given query will have the final mappings via a PreparedStatement or simply ran as a pre-built query.

结果框架的美妙之处在于它可以通过适当的类型检查和限制检查将 HTTP 请求参数直接处理到查询中。输入验证不需要额外的映射。在上面的示例查询中， 将检查名为serverId的参数以确保它可以转换为整数并且在 0-50 的范围内。参数appId将作为整数数组处理，长度限制为 50。如果字段showOwner存在并设置为“true”，引号中的 SQL 位将添加到生成的可选字段映射查询中。字段 提供更多参数类型映射，包括带有进一步参数映射的可选 SQL 段。它允许开发人员能够想出尽可能复杂的查询映射。它甚至在报告配置中有控件来确定给定的查询是通过 PreparedStatement 具有最终映射还是简单地作为预先构建的查询运行。

For the sample Http request values:

对于示例 Http 请求值：

showOwner: true
serverId: 20
appId: 1,2,3,5,7,11,13

It would produce the following SQL:

它将产生以下 SQL：

select app.name as "App", 
       app.owner as "Owner", 
       sv.name as "Server", sum(act.trans_ct) as "Trans"
  from activity_records act, servers sv, applications app
 where act.server_id = sv.id
   and act.app_id = app.id
   and sv.id = 20
   and app.id in (1,2,3,5,7,11,13)
 group by app.name,  app.owner,  sv.name
 order by app.name, sv.name

I really think that Spring or Hibernate or one of those frameworks should offer a more robust mapping mechanism that verifies types, allows for complex data types like arrays and other such features. I wrote my engine for only my purposes, it isn't quite read for general release. It only works with Oracle queries at the moment and all of the code belongs to a big corporation. Someday I may take my ideas and build a new open source framework, but I'm hoping one of the existing big players will take up the challenge.

我真的认为 Spring 或 Hibernate 或其中一个框架应该提供更强大的映射机制来验证类型，允许复杂的数据类型，如数组和其他此类功能。我写我的引擎只是为了我的目的，它不是为了一般发布而完全阅读。目前它只适用于 Oracle 查询，所有代码都属于一家大公司。有一天我可能会采纳我的想法并构建一个新的开源框架，但我希望现有的大玩家之一能够接受挑战。