Java - 在外部文件中存储 SQL 语句

Java - Storing SQL statements in an external file

提问by Adrian

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.

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

Here are the requirements:

以下是要求：

• 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

• 该文件必须可从 Java 应用程序中读取，但也必须可由支持团队编辑，无需花哨的编辑器
• 理想情况下，文件应该是纯文本格式，但 XML 也可以
• 允许存储/检索 DML 和 DDL 语句
• 可以在稍后阶段添加新语句（应用程序足够灵活，可以选择并执行它们）
• 语句可以分组（并由应用程序作为一个组执行）
• 语句应该允许参数

Notes:

笔记：

• Once retrieved, the statements will executed using Spring's JDBCTemplate
• Hibernate or Spring's IOC container will not be used

• 一旦检索到，语句将使用 Spring 的 JDBCTemplate 执行
• 不会使用 Hibernate 或 Spring 的 IOC 容器

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”.

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

• Axamol SQL Library

  Sample file content:

  <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

  Sample file content:

  <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}
  }
  

• 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>
  

• 伊巴蒂斯

  示例文件内容：

  <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?

谁能推荐一个经过试验和测试的解决方案？

采纳答案by Boris Pavlovi?

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

只需使用键值对创建一个简单的 Java 属性文件，如下所示：

users.select.all = select * from user

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.

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

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

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

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

回答by Dave

I would strongly encourage you to use Stored Procedures. This kind of thing is exactly what they're for.

我强烈建议您使用存储过程。这种事情正是他们的目的。

回答by John Stauffer

If you mustdo this, you should look at the MyBatisproject. I haven't used it, but have heard it recommended a number of times.

如果你一定要这样做，你应该看看MyBatis项目。我没用过，但听过多次推荐。

Separating SQL and Java isn't my favorite approach, since SQL is actually code, and is tightly coupled to the Java code that calls it. Maintaining and debugging the separated code can be challenging.

分离 SQL 和 Java 不是我最喜欢的方法，因为 SQL 实际上是代码，并且与调用它的 Java 代码紧密耦合。维护和调试分离的代码可能具有挑战性。

Absolutely don't used stored procs for this. They should only be used to improve performance by reducing traffic between the DB and the application.

绝对不要为此使用存储过程。它们应该仅用于通过减少数据库和应用程序之间的流量来提高性能。

回答by Rich Kroll

A simple solution we have implemented when faced with this was to externalize the SQL/DML into a file (mySql.properties), then use MessageFormat.format(String[] args) to inject dynamic properties into the SQL.

遇到这种情况时，我们实施的一个简单解决方案是将 SQL/DML 外部化到一个文件 (mySql.properties) 中，然后使用 MessageFormat.format(String[] args) 将动态属性注入 SQL。

For example: mySql.properties:

例如：mySql.properties：

select    *
    from      scott.emp
              join scott.dept on (emp.deptno = dept.deptno)
    where     emp.ename = {0}

Utility methods:

实用方法：

public static String format(String template, Object[] args) {
    String cleanedTemplate = replaceSingleQuotes(template);
    MessageFormat mf = new MessageFormat(cleanedTemplate);
    String output = mf.format(args);
    return output;
}
private static String replaceSingleQuotes(String template) {
    String cleaned = template.replace("'", "''");
    return cleaned;
}

Then use it like so:

然后像这样使用它：

String sqlString = youStringReaderImpl("/path/to/file");
String parsedSql = format(sqlString, new String[] {"bob"});

回答by Thorbj?rn Ravn Andersen

You can use the localization facilities to do this. You then use the name of the database as the locale to get the "oraclish" version of "insert-foo-in-bar" instead of the English or French version.

您可以使用本地化工具来执行此操作。然后，您使用数据库名称作为语言环境来获取“insert-foo-in-bar”的“oraclish”版本，而不是英语或法语版本。

The translations are usually stored in property files, and there are good tools for localizing applications by allowing editing these property files.

翻译通常存储在属性文件中，并且通过允许编辑这些属性文件来本地化应用程序有很好的工具。

回答by Ken Liu

You can also use the QueryLoaderclass in Apache Commons DbUtils, which will read the sql from a properties file. However, you will have to use DbUtils which sort of serves the same purpose as the JDBCTemplate.

您还可以使用Apache Commons DbUtils 中的QueryLoader类，它将从属性文件中读取 sql。但是，您必须使用与 JDBCTemplate 具有相同目的的 DbUtils。

回答by Patrick Cornelissen

You can use velocity to have "scriptable" sql templates that you can use to work with the files in a flexible way. You have primitive statements like conditionals and loops to build your sql commands.

您可以使用velocity 来拥有“可编写脚本的”sql 模板，您可以使用这些模板以灵活的方式处理文件。你有像条件和循环这样的原始语句来构建你的 sql 命令。

But I strongly suggest to use prepared statements and/or stored procedures. Building your SQL the way you're planning will make you vulnerable to SQL injection, the DB server will not be able to cache the sql queries (which will lead to bad performance).

但我强烈建议使用准备好的语句和/或存储过程。以您计划的方式构建 SQL 会使您容易受到 SQL 注入的影响，数据库服务器将无法缓存 sql 查询（这将导致性能不佳）。

BTW: You can store the definition of the prepared statements in files too. This is not the best solution but pretty close to it and you get the benefit of SQL-injection protection and performance.

顺便说一句：您也可以将准备好的语句的定义存储在文件中。这不是最好的解决方案，但非常接近它，您可以获得 SQL 注入保护和性能的好处。

When your SQL schema is not build to work with prepared statements or stored procedures you might want to rethink your schema. Maybe it needs to be refactored.

当您的 SQL 模式不是为使用准备好的语句或存储过程而构建时，您可能需要重新考虑您的模式。也许它需要重构。

回答by Patrick Cornelissen

You can use Spring and have your sql statements stored in your beans file that are injected when you get the class from your bean factory. That class can also to use an instance of SimpleJDBCTemplate that can be configured via the bean file to help simplify your code.

您可以使用 Spring 并将您的 sql 语句存储在您的 bean 文件中，当您从 bean 工厂获取类时，这些语句将被注入。该类还可以使用 SimpleJDBCTemplate 的实例，该实例可以通过 bean 文件进行配置，以帮助简化您的代码。

回答by marcus none

It's simple and reliable to do using classes from Spring. Take your SQL files and save them in some location on your classpath. This can be in a JAR file that only contains SQL if you want. Then use Spring's ClassPathResourceto load the file into a stream and use Apache IOUtilsto convert it into a String. You can then execute the SQL using SimpleJdbcTemplate, or DB code of your choice.

使用 Spring 中的类既简单又可靠。获取您的 SQL 文件并将它们保存在类路径上的某个位置。如果需要，这可以位于仅包含 SQL 的 JAR 文件中。然后使用 Spring 的ClassPathResource将文件加载到流中，并使用 Apache IOUtils将其转换为 String。然后，您可以使用SimpleJdbcTemplate或您选择的 DB 代码执行 SQL 。

I suggest you create a utility class that takes a simple Java class with public String fields that correspond to the SQL file names following a convention of your choosing. Then use reflection in conjunction with the ClassPathResourceclass to go find the SQL files conforming to your naming convention and assign them to the String fields. After that just refer to the class fields when you need the SQL. It's simple, works great, and achieves the goal you want. It also uses well worn classes and techniques. Nothing fancy. I did it couple years back. Works great. Too lazy to go get the code. You'll have no time figuring it out yourself.

我建议您创建一个实用程序类，它采用一个带有公共字符串字段的简单 Java 类，这些字段对应于您选择的约定的 SQL 文件名。然后将反射与ClassPathResource类结合使用以查找符合命名约定的 SQL 文件并将它们分配给 String 字段。之后只需在需要 SQL 时引用类字段。它很简单，效果很好，并且可以实现您想要的目标。它还使用陈旧的课程和技术。没有什么花哨。几年前我做过。效果很好。懒得去拿代码了。你将没有时间自己弄清楚。

回答by JodaStephen

The ElSqllibrary provides this functionality.

该ElSql库提供此功能。

ElSql consists of a small jar file (six public classes) that allows an external SQL file (elsql) to be loaded. The file uses a simple format to optionally provide slightly more behaviour than simply loading a file:

ElSql 由一个允许加载外部 SQL 文件 (elsql) 的小 jar 文件（六个公共类）组成。该文件使用一种简单的格式来选择性地提供比简单地加载文件更多的行为：

-- an example comment
@NAME(SelectBlogs)
  @PAGING(:paging_offset,:paging_fetch)
    SELECT @INCLUDE(CommonFields)
    FROM blogs
    WHERE id = :id
      @AND(:date)
        date > :date
      @AND(:active)
        active = :active
    ORDER BY title, author
@NAME(CommonFields)
  title, author, content

// Java code:
bundle.getSql("SelectBlogs", searchArgs);

The file is broken up into @NAMEblocks which can be referred to from code. Each block is defined by significant whitespace indentation. @PAGINGwill insert the necessary code for paging such as FETCH/OFFSET. @ANDwill only be output if the specified variable exists (helping build dynamic searches). The DSL also handles LIKEvs =for wildcards in searches. The goal of the optional DSL tags is to provide the common basics that often hit when trying to build dynamic SQL in a database-neutral way.

该文件被分解为@NAME可以从代码中引用的块。每个块都由重要的空白缩进定义。@PAGING将插入必要的分页代码，例如 FETCH/OFFSET。@AND仅当指定的变量存在时才会输出（帮助构建动态搜索）。DSL 还处理搜索中的通配符LIKEvs。=可选 DSL 标记的目标是提供在尝试以数据库中立的方式构建动态 SQL 时经常遇到的常见基础知识。

More info on the blogor user guide.

有关博客或用户指南的更多信息。