java 使用 spring jdbc 时外部化长（+20 行 sql）的干净方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15433032/
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
Clean way to externalize long (+20 lines sql) when using spring jdbc?
提问by Hyman Dans
I want to externalize some large queries in my application to properties\sql\xml files. However I was wondering if anyone has some recommendations as to how achieve this in a clean fashion. Most results recommend using an ORM framework but this isn't applicable due to some data constraints.
我想将应用程序中的一些大型查询外部化为 properties\sql\xml 文件。但是，我想知道是否有人对如何以干净的方式实现这一目标有一些建议。大多数结果建议使用 ORM 框架，但由于某些数据限制，这并不适用。
I took a look at: Java - Storing SQL statements in an external filebut doing this propertyname .1, .2 etc for a few queries that are each longer that 20 lines does not seem that clean.
我看了一下：Java - 将 SQL 语句存储在外部文件中，但是对于一些长度超过 20 行的查询执行此属性名称 .1、.2 等似乎并不那么干净。
回答by Pankaj
You can put your queries in a xml file
您可以将查询放在 xml 文件中
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<entry key="getPersonById">
<![CDATA[
Select Name From Person
Where Id =?
]]>
</entry>
<entry key="getPersonBySSN">
<![CDATA[
]]>
</entry>
</properties>
In Spring application Context, load this xml file
在 Spring application Context 中，加载这个 xml 文件
<bean id="queryProps" class="org.springframework.beans.factory.config.PropertiesFactoryBean">
<property name="locations" value="classpath:/queries.xml" />
</bean>
Inject this bean in your DAO class
在你的 DAO 类中注入这个 bean
<bean id="myDAO" class="com.xyz.dao.MyDAOImpl">
<property name="queryProps" ref="queryProps" />
</bean>
Define queryProps in your DAO class and don't forget to have setter method for this
在你的 DAO 类中定义 queryProps 并且不要忘记为此设置 setter 方法
private Properties queryProps;
Now you can access the query in your DAO like this -
现在您可以像这样访问 DAO 中的查询 -
String query = queryProps.getProperty("getPersonById");
Hope this helps.
希望这可以帮助。
回答by siphiuel
I faced the same issue some time ago, and came up with YAML. It supports multi-line string property values, so you can write something like this in your query files:
前段时间我遇到了同样的问题，并想出了 YAML。它支持多行字符串属性值，因此您可以在查询文件中编写如下内容：
selectSomething: >
SELECT column1, column2 FROM SOMETHING
insertSomething: >
INSERT INTO SOMETHING(column1, column2)
VALUES(1, '1')
Here, selectSomethingand insertSomethingare query names. So it's really convenient and contains very few special characters. Queries are separated by blank lines, and each query text must be indented. Note that queries can absolutely contain the indentation of their own, so that the following is perfectly valid:
在这里，selectSomething和insertSomething是查询名称。所以它真的很方便并且包含很少的特殊字符。查询由空行分隔，每个查询文本必须缩进。请注意，查询绝对可以包含自己的缩进，因此以下内容完全有效：
anotherSelect: <
SELECT column1 FROM SOMETHING
WHERE column2 IN (
SELECT * FROM SOMETHING_ELSE
)
You can then read the contents of the file into a hash-map with the help of SnakeYAML library, using the code below:
然后，您可以在 SnakeYAML 库的帮助下使用以下代码将文件的内容读入哈希映射：
import org.apache.commons.io.FilenameUtils;
import org.apache.commons.io.FileUtils;
import java.io.FileReader;
import org.yaml.snakeyaml.Yaml;
import java.io.File;
import java.io.FileNotFoundException;
public class SQLReader {
private Map<String, Map> sqlQueries = new HashMap<String, Map>();
private SQLReader() {
try {
final File sqlYmlDir = new File("dir_with_yml_files");
Collection<File> ymlFiles = FileUtils.listFiles(sqlYmlDir, new String[]{"yml"}, false);
for (File f : ymlFiles) {
final String fileName = FilenameUtils.getBaseName(f.getName());
Map ymlQueries = (Map)new Yaml().load(new FileReader(f));
sqlQueries.put(fileName, ymlQueries);
}
}
catch (FileNotFoundException ex) {
System.out.println("File not found!!!");
}
}
}
In the example above a map of maps is created, mapping each YAML file to a map containing query names/strings.
在上面的示例中，创建了一个映射映射，将每个 YAML 文件映射到包含查询名称/字符串的映射。
回答by Arun B Chandrasekaran
This is in addition to what Pankaj has answered. This one doesn't have CDATA in the Properties XML and uses autowiring. I had to add this as answer, as I cannot format code if I had to do this in comment section.
这是对Pankaj 回答的补充。这个在属性 XML 中没有 CDATA 并使用自动装配。我不得不将此添加为答案，因为如果必须在评论部分执行此操作，我将无法格式化代码。
Make sure you have following namespace in spring application context xml file.
确保在 spring 应用程序上下文 xml 文件中有以下命名空间。
xmlns:util="http://www.springframework.org/schema/util
Add the following bean to spring application context xml
将以下 bean 添加到 spring 应用程序上下文 xml
<util:properties id="sqls" location="classpath:oracle/sqls.xml" />
Contents of the file sqls.xmlis
文件内容sqls.xml是
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Employee Queries</comment>
<entry key="employee.insert">
INSERT
INTO EMPLOYEE
(
ID,
NAME,
AGE,
DEPARTMENT
)
VALUES
(
EMPLOYEE_SEQ.NEXTVAL,
?,
?,
?
)
</entry>
</properties>
Autowired the properties as below
自动装配属性如下
@Autowired
@Qualifier("sqls")
private Properties sqls;
Code to get the sql query from Properties
从属性获取 sql 查询的代码
String sql = sqls.getProperty("employee.insert");
回答by Adam
You can do multiline queries in a properties file by putting a \ at the end of the line. For example
您可以通过在行的末尾放置 \ 来在属性文件中进行多行查询。例如
queries.myquery = select \
foo, bar \
from mytable \
where baz > 10
回答by LoganMzz
Take a loot at JdbcTestUtilsand the methods "executeSqlScript" and "readScript".
看看JdbcTestUtils和方法“executeSqlScript”和“readScript”。
