Java - 在外部文件中存储SQL语句 [英] Java - Storing SQL statements in an external file

问题描述

我正在寻找一种在外部文件中存储SQL语句的Java库/框架/技术。支持团队（包括DBA）应该能够（略微）更改语句，以便在数据库架构更改或调整目的时保持同步。

I am looking for a Java library/framework/technique of storing SQL statements in an external file. The support team (including DBAs) should be able to alter (slightly) the statement to keep them in sync in case database schema changes or for tuning purposes.

以下是要求：

• 该文件必须可从Java
  应用程序读取，但必须由支持团队编辑为
  不需要
  的花哨编辑


• 理想情况下，文件应该是普通的
  文本格式，但XML也可以


• 允许存储/检索DML以及DDL语句
  


• 可以在以后添加新语句（应用程序足够灵活，可以选择并执行它们） ）


• 语句可以分组（并由应用程序作为一个组执行）


• 语句应该允许参数

• The file must be readable from a Java application but also must be editable by the support team without the need of fancy editors
• Ideally, the file should be in plain text format but XML is OK too
• Allow DML as well as DDL statements to be stored / retrieved
• New statements can be added at a later stage (the application is flexible enough to pick them up and execute them)
• Statements can be grouped (and executed as a group by the application)
• Statements should allow parameters

注意：

• 一旦检索到，语句将使用
  执行Spring的JDBCTemplate


• Hibernate或Spring的IOC续ainer
  将不会被使用

到目前为止，我设法找到以下Java库，它们使用外部文件进行存储SQL语句。但是，我主要对存储而不是隐藏所有JDBC复杂性的库感兴趣。

So far, I managed to find the following Java libraries, which use external files for storing SQL statements. However, I am mainly interested in the storage rather than a library that hides all JDBC "complexities".

• Axamol SQL库

示例文件内容：

<s:query name="get_emp">
  <s:param name="name" type="string"/>
  <s:sql databases="oracle">
    select    *
    from      scott.emp
              join scott.dept on (emp.deptno = dept.deptno)
    where     emp.ename = <s:bind param="name"/>
  </s:sql>
</s:query>

iBATIS

示例文件内容：

<sqlMap namespace="Contact"">
    <typeAlias alias="contact"
        type="com.sample.contact.Contact"/">
    <select id="getContact"
        parameterClass="int" resultClass="contact"">
            select CONTACTID as contactId,
                   FIRSTNAME as firstName,
                   LASTNAME as lastName from
                   ADMINISTRATOR.CONTACT where CONTACTID = #id#
    </select>
</sqlMap>
<insert id="insertContact" parameterClass="contact">
INSERT INTO ADMINISTRATOR.CONTACT( CONTACTID,FIRSTNAME,LASTNAME)
        VALUES(#contactId#,#firstName#,#lastName#);
 </insert>
<update id="updateContact" parameterClass="contact">
update ADMINISTRATOR.CONTACT SET
FIRSTNAME=#firstName# ,
LASTNAME=#lastName#
where contactid=#contactId#
</update>
<delete id="deleteContact" parameterClass="int">
DELETE FROM ADMINISTRATOR.CONTACT WHERE CONTACTID=#contactId#
</delete>

WEB4J

-- This is a comment 
 ADD_MESSAGE   {
 INSERT INTO MyMessage -- another comment
  (LoginName, Body, CreationDate)
  -- another comment
  VALUES (?,?,?)
 }

-- Example of referring to a constant defined above.
FETCH_RECENT_MESSAGES {
 SELECT 
 LoginName, Body, CreationDate 
 FROM MyMessage 
 ORDER BY Id DESC LIMIT ${num_messages_to_view}
}

有人可以推荐经过试用和测试的解决方案吗？

Can anyone recommend a solution that is tried and tested?

推荐答案

只需创建一个简单的Java Properties文件，其键值对就像这样：

Just create a simple Java Properties file with key-value pairs like this one:

users.select.all = select * from user

在DAO类中声明一个Properties类型的私有字段，并使用Spring配置将其注入，该配置将读取文件中的值。

Declare a private field of type Properties in your DAO class and inject it using Spring configuration which will read the values from the file.

更新：如果你想支持多行的SQL语句，请使用以下表示法：

UPDATE: if you want to support SQL statements in multiple lines use this notation:

users.select.all.0 = select *
users.select.all.1 = from   user

这篇关于Java - 在外部文件中存储SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！