在Java中处理丑陋的SQL [英] Dealing with ugly SQL in Java

问题描述

这里是一个SQL-Java编码风格问题...

Here goes a SQL-Java coding style question...

其他人如何处理在Java中创建复杂的自定义查询 ？

How do others here deal with creating complex custom queries in Java cleanly?

我说的是一个看似简单的任务，准备一个字符串，它是要执行的SQL语句。

I am speaking of the seemingly simple task of preparing a string which is the SQL statement to be executed.

我知道HQL，还有存储过程，但老实说，我真的不喜欢这些解决方案。也许我可以说服不同。存储过程恼人的部署/维护，解析性能不是这样一个巨大的问题在我的情况下 - 灵活性优先。 HQL似乎是一个大的飞跃，对我的复杂查询有一些限制。

I am aware of HQL, and also of stored procedures, but honestly I don't really love these solutions. Perhaps I can be convinced differently. Stored procedures are annoying to deploy/maintain, and parsing performance is not such a huge issue in my case -- flexibility takes precedence. HQL seems like a big leap, and has some limitations for my complex queries.

要说清楚，我说的超丑的代码，如下：

TO be clear, I am talking of super-ugly looking code like this:

    return 
        "(" + topTwenty + ")" +
        "UNION " +
        "(" + twentyBeforeMe + ")" +
        "UNION " +
        "(" + meDummyQuery + ")" +
        "UNION " +
        "(" + twentyAfterMe + ")";

其中变量topTwenty也是类似创建的子查询。

Where the variables topTwenty are for example also subqueries created similarly.

我从来没有想过我会这么说，但在PHP中更清晰，它有多行字符串和$变量嵌入字符串。

I never thought I'd say this, but it was cleaner in PHP which had the multi-line string and $variable embedding in the strings.

人们有没有使用一个平凡的模板库？你如何整洁地保持字符串在程序中？

Do people ever use a trivial templating library? How do you neatly keep the strings in the program? Or do you put them in a separate file (also seems annoying to me somehow).

推荐答案

我要做的就是使用String .format表名和不能被参数化的东西，此类（NamedPreparedStament），它允许为绑定变量而不是问号使用好的名称。

What I do is to use String.format for table names and things that can't be parametrized, and this class(NamedPreparedStament), which allows to use good names for bind variables instead of question marks.

String sql = "SELECT id FROM %s WHERE id > :lastInsertedYesterday ";
NamedParameterStatement p = new NamedParameterStatement(con, 
                                    String.format(sql, "table1"));
p.setInt("lastInsertedYesterday", lastOne);

这篇关于在Java中处理丑陋的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！