Java - 避免在代码中使用长 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6815115/
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
Java - Avoiding long SQL query in code
提问by Stephan
In my Java code, I have something like this :
在我的 Java 代码中，我有这样的事情：
ResultSet rs = statement.executeQuery(
"SELECT a,b,c FROM foo -- here starts the long query"+
" -- that is not yet finished " +
" -- that still has something to say... "+
" -- now the end !"
);
I would like to clean up my code like this :
我想像这样清理我的代码：
ResultSet rs = statement.executeQuery(all_queries.getQuery("The very long one"));
I have read that ResourceBundleis for localization. So I don't think it matches in my case.
我读过ResourceBundle是为了本地化。所以我认为它不符合我的情况。
What should all_queriesbe ?
应该all_queries是什么？
EDIT :The most important thing for me is to clean up the code.
编辑：对我来说最重要的是清理代码。
回答by Konrad Garus
I would put it in a file with an sql extension and implement Querieslike:
我会把它放在一个带有 sql 扩展名的文件中，并实现Queries如下：
Queries {
static public String getQuery(String name) {
return loadResource("/com/example/queries/" + name + ".sql");
}
}
User:
用户：
conn.prepareStatement(Queries.getQuery("my_query"));
Of course that's only one way to do it. You can make Queriesreturn Statement, or even use a dynamic proxy to mask it behind a simple Java interface (where proxy handler could create statement, set parameters and run query). Your mileage may vary.
当然，这只是一种方法。您可以使用Queriesreturn Statement，甚至使用动态代理将其屏蔽在一个简单的 Java 接口后面（代理处理程序可以在其中创建语句、设置参数和运行查询）。你的旅费可能会改变。
Added benefit: sql files have syntax coloring and are wayeasier to maintain than Strings in Java.
额外的好处：SQL文件的语法着色，且方式更容易维护不是字符串在Java中。
回答by Adam Matan
Datastructure perspective
数据结构视角
Since you need a mapping from a key (name) to value (long query), which is achieved using a dictionary (aka map, associative array)datastructure.
由于您需要从键（名称）到值（长查询）的映射，这是使用字典（又名映射、关联数组）数据结构实现的。
Keep your configuration away from your code
让你的配置远离你的代码
You should store your configuration in a file, separate from your code. I recommend the .iniconfiguration format, which is very readable, can be divided into sections, and has good parser for almost any computer language.
您应该将您的配置存储在一个文件中，与您的代码分开。我推荐.ini配置格式，它非常易读，可以分成几个部分，并且对几乎任何计算机语言都有很好的解析器。
Your configuration file will look like:
您的配置文件将如下所示：
[users_queries]
find_max_user_id = SELECT max(id)
FROM users
WHERE ...
name = query
...
...
Using the ini4jmodule, getting your queries would be as easy as:
使用该ini4j模块，获取查询将非常简单：
Ini.Section section = ini.get("users_queries");
String query = section.get("find_max_user_id");
回答by djna
I would just make them
我只会让他们
static final String someMeaningfulName = " ... ";
Externalising to a text file such as a resource bundle would work, but I'm not convinced that it is necessary, or even a good idea as it might lead to a way of thinking that these are not really "code" and hence changes don't really need testing.
外部化到诸如资源包之类的文本文件是可行的，但我不相信这是必要的，甚至是一个好主意，因为它可能会导致人们认为这些不是真正的“代码”，因此更改不会真的不需要测试。
回答by Manny
A simple solution would be to use the normal properties file, answer is from Cleanest way to build an SQL string in Java
一个简单的解决方案是使用普通的属性文件，答案来自Cleanest way to build an SQL string in Java
Only problem is that new line needs to be separated by "\" e.g.
唯一的问题是新行需要用“\”分隔，例如
CURRENT_DATE=select sysdate \
from dual
then you can use
然后你可以使用
Queries.getQuery("CURRENT_DATE");
Yes, "\" is still ugly but it's cleaner and easier to format compared to using Java's String / StringBuilder concatenation, imo.
是的，"\" 仍然很丑，但与使用 Java 的 String / StringBuilder 串联 imo 相比，它更清晰、更容易格式化。
If you want to support a cleaner format, maybe you can create your own parser or use XML format. But I think it's an overkill.
如果您想支持更清晰的格式，也许您可以创建自己的解析器或使用 XML 格式。但我认为这是一种矫枉过正。
Off topic: Gotta love Groovy's multiline String (shameless):
题外话：必须喜欢Groovy的多行字符串（无耻）：
public static final String MY_QUERY = """\
select col1, col2
from table1
where col1=:param1
""";
回答by user2001125
If we write multiple queries in text file (not in properties file), we can retrieve or fetch a single query out of all.
如果我们在文本文件（而不是属性文件）中编写多个查询，我们可以从所有查询中检索或获取单个查询。
回答by Alexey Grigorev
Maybe the problem is in the structure of your application. Do you separate your java classes into "dao", "service", etc packages?
也许问题出在您的应用程序的结构中。您是否将 Java 类分为“dao”、“service”等包？
If you organize your project, you won't need to call ResultSet rs = statement.executeQuery( all_queries.getQuery("The very long one") ), but instead to call Result res = dao.getSomethingYouNeed(param1, param2, ...);
如果您组织项目，则不需要调用ResultSet rs = statement.executeQuery( all_queries.getQuery("The very long one") )，而是调用Result res = dao.getSomethingYouNeed(param1, param2, ...);
回答by Charles Goodwin
A HashMap would be simple, since you want to map from a query name/key to a query/value. Any Map would do really.
HashMap 很简单，因为您希望从查询名称/键映射到查询/值。任何地图都可以。
public class Queries extends HashMap {
public Queries() {
add("My long query",
"Super long..."+
"...long long..."+
"...long query.");
// add others
}
}
You could use a singleton if you wanted to keep it static.
如果您想保持静态，您可以使用单例。
public class Queries {
private static HashMap store = new HashMap();
{
// constructor
add("My long query",
"Super long..."+
"...long long..."+
"...long query.");
// add others
}
public String getQuery(String queryName) { return store.get(queryName); }
Or you could just use static Strings as suggested by djna:
或者您可以按照 djna 的建议使用静态字符串：
public class Queries {
final public static myQuery = "My long query";
}
public class MyProgram extends Queries {
...
public void someMethod() {
...
doQuery(myQuery);
...
}
}
回答by Nestor Hernandez Loli
MyBatis does this out of the box and works like champ!
MyBatis 开箱即用，并且像冠军一样工作！
