java 通常，在应用程序中放置 SQL 查询的位置是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4265255/
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
In general, where to place SQL queries in an application?
提问by jai
what is the best place to place SQL queries in an application?
在应用程序中放置 SQL 查询的最佳位置是什么？
The queries might be big and requires formatting.
查询可能很大并且需要格式化。
Appending the query using StringBuilder looks very cluttered.
使用 StringBuilder 附加查询看起来很混乱。
Storing them in files and reading them every time when a request is made - looks like a bad idea.(but i think reading from the file can be put in a static block)
将它们存储在文件中并在每次发出请求时读取它们 - 看起来是个坏主意。（但我认为从文件中读取可以放在静态块中）
回答by Sean Patrick Floyd
Keep the SQL query in a resource file that you read to a constant at class load time:
将 SQL 查询保存在您在类加载时读取为常量的资源文件中：
private static final String PERSON_QUERY;
static{
InputStream str = null;
try{
str = ThisClass.class.getResourceAsStream("/path/to/query.sql");
PERSON_QUERY = IOUtils.toString(str);
}catch(IOException e){
throw new IllegalStateException("Failed to read SQL query", e);
}finally{
IOUtils.closeQuitely(str);
}
}
That way you can use your favorite editor to edit the SQL, but you still get the query in a constant in java.
这样你就可以使用你最喜欢的编辑器来编辑 SQL，但你仍然可以在 java 中以常量形式获得查询。
If you do this a lot, extract the code to a helper method:
如果您经常这样做，请将代码提取到辅助方法中：
public static String loadResourceToString(final String path){
final InputStream stream =
Thread
.currentThread()
.getContextClassLoader()
.getResourceAsStream(path);
try{
return IOUtils.toString(stream);
} catch(final IOException e){
throw new IllegalStateException(e);
} finally{
IOUtils.closeQuietly(stream);
}
}
and use that in your static blocks:
并在您的静态块中使用它：
private static final String PERSON_QUERY;
private static final String ADDRESS_QUERY;
private static final String AGE_QUERY;
static{
PERSON_QUERY = Helper.loadResourceToString("queries/personQuery.sql");
ADDRESS_QUERY = Helper.loadResourceToString("queries/addressQuery.sql");
AGE_QUERY = Helper.loadResourceToString("queries/ageQuery.sql");
}
In my opinion, different languages should always be separated. It's an awful practice to assemble SQL, HTML, XML, JavaScript etc. from Java code. Use plain templates or template engines like Velocity whenever possible. That gives you many benefits, one of them being that you can change the template without recompiling the java class.
在我看来，不同的语言应该始终分开。从 Java 代码组装 SQL、HTML、XML、JavaScript 等是一种糟糕的做法。尽可能使用普通模板或模板引擎，如 Velocity。这给您带来了许多好处，其中之一是您可以在不重新编译 java 类的情况下更改模板。
PS: I am using Apache Commons / IO in the above code, but it's not necessary, just easier.
PS：我在上面的代码中使用了Apache Commons / IO，但这不是必需的，只是更容易。
回答by raj
Read about PreparedStatement
In this you need not store all the variable parts of the query
like, insert into table_x values (?,?,?);
在这种情况下，您不需要存储查询的所有可变部分，例如， insert into table_x values (?,?,?);
and using statement.setString(1,"hello");, statement.setInt(2,1);, statement.setDouble (3,4.555);
并使用statement.setString(1,"hello");, statement.setInt(2,1);,statement.setDouble (3,4.555);
and finally statement.execute();you can insert the values..
最后statement.execute();你可以插入值..
PS: Storing the prepared statement strings in a properties file is recommended.
PS：建议将准备好的语句字符串存储在属性文件中。
回答by Sanjay T. Sharma
I'd personally lean towards placing those queries in an XML file; properties file are a nightmare for complex queries (not to forget the \after each line of query). And while you are at it, why not just use a simple DAO framework like iBatis (now MyBatis)which is a pleasure to use for both simple and complex projects. :-)
我个人倾向于将这些查询放在 XML 文件中；属性文件是复杂查询的噩梦（不要忘记\每行查询之后）。当你在做的时候，为什么不使用一个简单的 DAO 框架，比如iBatis（现在是 MyBatis），它很高兴用于简单和复杂的项目。:-)
回答by Adeel Ansari
Yeah, its good to go. And a property file wouldn't be a bad idea. But sometimes we need to build queries on the fly, for that StringBuilerapproach is fine.
是的，很高兴去。并且属性文件不会是一个坏主意。但有时我们需要动态构建查询，因为这种StringBuiler方法很好。
回答by darioo
You can put them in a .properties file. Using Apache Commons for configuration, you can avoid reading files every time.
您可以将它们放在 .properties 文件中。使用Apache Commons进行配置，可以避免每次都读取文件。
If you choose to go with this route, you can aid readibility by breaking up one query into more rows using backslashes:
如果您选择使用这条路线，您可以通过使用反斜杠将一个查询分解为更多行来提高可读性：
myLongQuery: select col1, col2, col3, col4 from \
table1 where \
col1 = 'something'
回答by Buhake Sindi
In my scenario, I have a Specific DAO where all my SQL queries are "registered" in a static finalblock.
在我的场景中，我有一个特定的 DAO，其中我的所有 SQL 查询都在一个static final块中“注册” 。
Example:
例子：
public class MySQLUserDAO extends UserDAO {
private static final String SQL_COUNT = "SELECT COUNT(1) AS TOTAL FROM USER";
// private static final String SQL_CREATE = "INSERT INTO USER(FIRST_NAME, MIDDLE_NAME, LAST_NAME, EMAIL_ADDRESS, DOB) VALUES (?, ?, ?, ?, ?)";
private static final String SQL_DELETE = "DELETE FROM USER WHERE USER_ID = ?";
private static final String SQL_RETRIEVE = "SELECT * FROM USER WHERE USER_ID = ?";
private static final String SQL_UPDATE = "UPDATE USER SET FIRST_NAME = ?, MIDDLE_NAME = ?, LAST_NAME = ?, GENDER = ?, EMAIL_ADDRESS = ?, DOB = ? WHERE USER_ID = ?";
private static final String SQL_FIND_EMAIL = "SELECT * FROM USER WHERE EMAIL_ADDRESS = ?";
private static final String SQL_FIND_FIRST_NAME = "SELECT * FROM USER WHERE LTRIM(RTRIM(LOWER(FIRST_NAME))) = LOWER(?)";
private static final String SQL_FIND_FIRST_NAME_LIKE = "SELECT * FROM USER WHERE LTRIM(RTRIM(LOWER(FIRST_NAME))) LIKE ?";
private static final String SQL_FIND_LAST_NAME = "SELECT * FROM USER WHERE LTRIM(RTRIM(LOWER(LAST_NAME))) = LOWER(?)";
private static final String SQL_FIND_LAST_NAME_LIKE = "SELECT * FROM USER WHERE LTRIM(RTRIM(LOWER(LAST_NAME))) LIKE ?";
private static final String SQL_FIND_BY_NAME = "SELECT * FROM USER WHERE LTRIM(RTRIM(LOWER(CONCAT_WS(' ', FIRST_NAME, LAST_NAME)))) LIKE ?";
But for queries that requires dynamic statement creation, I place it in the method it is used for.
但是对于需要创建动态语句的查询，我将它放在它所使用的方法中。
Example:
例子：
/* (non-Javadoc)
* @see net.imatri.dao.JdbcDAO#create(java.lang.Object)
*/
@Override
public boolean create(UserEntity user) throws DAOException {
// TODO Auto-generated method stub
PreparedStatement ps = null;
ResultSet generatedKeyResultSet = null;
boolean created = false;
String SQL_CREATE = "INSERT INTO USER(FIRST_NAME, MIDDLE_NAME, LAST_NAME, EMAIL_ADDRESS";
String sqlValues = "(?, ?, ?, ?";
if (user.getGender() != null) {
SQL_CREATE += ", GENDER";
sqlValues += ", ?";
}
if (user.getBirthDate() != null) {
SQL_CREATE += ", DOB";
sqlValues += ", ?";
}
SQL_CREATE += ") VALUES " + sqlValues + ")";
try {
ps = getConnection().prepareStatement(SQL_CREATE, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, user.getFirstName());
ps.setString(2, user.getMiddleName());
ps.setString(3, user.getLastName());
int pos = 4;
if (user.getGender() != null) {
ps.setString(pos++, user.getGender().toString());
}
ps.setString(pos++, user.getEmailAddress());
if (user.getBirthDate() != null)
ps.setDate(pos++, new Date(user.getBirthDate().getTime()));
ps.executeUpdate();
generatedKeyResultSet = ps.getGeneratedKeys();
if (generatedKeyResultSet != null && generatedKeyResultSet.next()) {
user.setId(generatedKeyResultSet.getLong(1));
}
created = true;
} catch (SQLException e) {
// TODO Auto-generated catch block
throw new DAOException(e);
} finally {
try {
close(generatedKeyResultSet, ps);
} catch (SQLException e) {
// TODO Auto-generated catch block
logger.error("Error closing statement or resultset.", e);
}
}
return created;
}
Your approach isn't bad. We've just used to having DAO's containing SQL in a static finalblock.
你的方法不错。我们已经习惯了在static final块中包含包含 SQL 的 DAO 。
If your SQL can grow many lines, you can use StringBuilder(with no synchronization) or StringBuffer(with synchronization) for String manipulation.
如果您的 SQL 可以增长很多行，您可以使用StringBuilder（无同步）或StringBuffer（有同步）进行字符串操作。
回答by AlexR
I used to put them into special properties file that is packed into my jar. Then I extracted it using Properties.load(getClass().getResourceAsStream("queries.properties"))and used prepared statement.
我曾经将它们放入打包到我的 jar 中的特殊属性文件中。然后我使用Properties.load(getClass().getResourceAsStream("queries.properties"))并使用了准备好的语句提取它。
But years passed since I used this technique last time and now I think that it is highly not recommended unless you have a serious reason to do this.
但是自从我上次使用这种技术以来已经过去了很多年，现在我认为强烈不推荐它，除非你有充分的理由这样做。
I think that usage of JPA is a "right" solution for big projects. If you are developing smaller project use mapping tool like iBatis that allows you to write queries as annotations.
我认为使用 JPA 是大型项目的“正确”解决方案。如果您正在开发较小的项目，请使用像 iBatis 这样的映射工具，它允许您将查询编写为注释。
回答by Richard Key
One thing you might want to look into is Stored Procedures or views. I am not sure what what type of database you are using, but in MS SQL and MySQL both of these are an option. They offer not only a place to store your long queries, but since you pass in variables rather than just executing a query, this also protects again the dreaded dun dun dunnnnnnnSQL injection. Now, I also don't know how complex your application is, but in general I tend to use a solution where my queries are stored on the database end, rather than in an application somewhere.
您可能想要研究的一件事是存储过程或视图。我不确定您使用的是什么类型的数据库，但在 MS SQL 和 MySQL 中，这两个都是一个选项。它们不仅提供了一个存储你的长查询的地方，而且由于你传递变量而不是仅仅执行查询，这也再次保护了可怕的dun dun dunnnnnSQL 注入。现在，我也不知道您的应用程序有多复杂，但总的来说，我倾向于使用一种解决方案，将我的查询存储在数据库端，而不是某个应用程序中。
A bit of reading: (wiki articles yes, but there are good references at the bottom.) http://en.wikipedia.org/wiki/Stored_procedurehttp://en.wikipedia.org/wiki/View_(database)
一点阅读：（维基文章是的，但底部有很好的参考。） http://en.wikipedia.org/wiki/Stored_procedurehttp://en.wikipedia.org/wiki/View_(database)
回答by Victor Sorokin
Static queries -- the ones which are depend only on binding parameters -- are perfectly fit in *DAOclasses, which abstract DB access away -- you only deal with DAO API like loadUser(int userId)or saveUser(User user). This way how queries are stored in the DAO isn't a big question, do as you like.
I don't use dynamic queries usually, so I can't give good advice about them.
静态查询——仅依赖于绑定参数的那些——非常适合*DAO抽象数据库访问的类——你只处理像loadUser(int userId)或这样的DAO API saveUser(User user)。这样查询如何存储在 DAO 中就不是什么大问题了，随心所欲。
我通常不使用动态查询，所以我不能就它们给出好的建议。
