java文件中写sql语句,Java - 在外部文件中存储SQL语句

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.

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

Notes:

Once retrieved, the statements will

executed using Spring’s JDBCTemplate

Hibernate or Spring’s IOC container

will not be used

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

Sample file content:

select *

from scott.emp

join scott.dept on (emp.deptno = dept.deptno)

where emp.ename =

Sample file content:

type="com.sample.contact.Contact"/">

parameterClass="int" resultClass="contact"">

select CONTACTID as contactId,

FIRSTNAME as firstName,

LASTNAME as lastName from

ADMINISTRATOR.CONTACT where CONTACTID = #id#

INSERT INTO ADMINISTRATOR.CONTACT( CONTACTID,FIRSTNAME,LASTNAME)

VALUES(#contactId#,#firstName#,#lastName#);

update ADMINISTRATOR.CONTACT SET

FIRSTNAME=#firstName# ,

LASTNAME=#lastName#

where contactid=#contactId#

DELETE FROM ADMINISTRATOR.CONTACT WHERE CONTACTID=#contactId#

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

解决方案

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

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.

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