英文:
Can jOOQ be used to check native SQL string
问题
我想在一个使用原生 SQL 和 org.springframework.jdbc.core.JdbcTemplate 的项目中集成 jOOQ。数据库架构经常发生变化，开发人员必须搜索代码，查找已删除的列并更新查询。Hibernate 或其他 ORM 解决方案不是一个选项，因为查询有时非常大，开发人员只需将其插入应用程序代码中。这样非常容易出错。因此，我打算逐步集成 jOOQ。
jOOQ 能否在无法执行原生 SQL 时抛出编译时错误？
我尝试了以下方法：
// 现有代码
JdbcTemplate jdbcTemplate = ...
String sql = "select ...";
// 检查代码
try {
DSLContext dslContext = DSL.using(jdbcTemplate.getDataSource().getConnection());
Query query = dslContext.parser().parseQuery(sql + " order by NON_EXISTING_COLUMN");
} catch (SQLException e) {
...
}
// 现有代码
return jdbcTemplate.query(sql, ...)
但是尽管 NON_EXISTING_COLUMN 实际上不存在，它编译通过了。也许我对 jOOQ API 还不够了解。但我认为它应该有能力做到这一点。表类是通过生成的，因此它可以检查从原生 SQL 字符串构建的查询是否可以在不执行它的情况下执行。是这样吗？
英文:
I want to integrate jOOQ in a project which uses native SQL and org.springframework.jdbc.core.JdbcTemplate. The database schema changes often and developers have to search the code for a column that has been removed and update the queries. Hibernate or another ORM solution isn't an option as queries are sometimes very large and developers get them and just have to insert into the application code. This is very error-prune. So I thought to integrate jOOQ but gradually.
Can jOOQ throw compile time errors if a native SQL cannot be executed?
I've tried the following:
// Existing code
JdbcTemplate jdbcTemplate = ...
String sql = "select ...";
// Check code
try {
DSLContext dslContext = DSL.using(jdbcTemplate.getDataSource().getConnection());
Query query = dslContext.parser().parseQuery(sql + " order by NON_EXISTING_COLUMN");
} catch (SQLException e) {
...
}
// Existing code
return jdbcTemplate.query(sql, ...)
But it compiles well although NON_EXISTING_COLUMN doesn't really exist. Maybe I know the jOOQ API not very well yet. But I thought that it should be capable of doing it. The table classes are generated, so it can check whether the query which is build from a native SQL string is executable without executing it. Isn't it?
答案1
得分: 0
为什么不使用生成的类文件，而不是编写本机SQL？
dslContext.selectFrom(GENERATED_TABLE)...
附注：如果您使用JOOQ DSL（使用生成的类）生成SQL，它可能会工作，但在某些情况下可能不完全有效，即使您在生成的类上出现错误（关系或某些聚合函数），它也不会对此提出投诉，并且将成功编译。
英文:
why don't use generated class files rather than typing native SQL?
dslContext.selectFrom(GENERATED_TABLE)...
PS: If you use JOOQ DSL(with generated classes) for generating SQL it would be work but not completely in some cases if you do mistakes even on generated classes (relations or some aggregate functions) it will not complain about it and will compile successfully.
答案2
得分: 0
jOOQ的旧版本在解析器中未实现元数据查找，或者仅在某种程度上实现了该功能，并且接受了所有有效的标识符。从jOOQ 3.14开始，将会有一个改进的实现，具体请参阅：
https://github.com/jOOQ/jOOQ/issues/9061
您需要为jOOQ提供一个 Configuration.metaProvider()（默认为基于JDBC的DatabaseMetaData实现。您可能更喜欢使用生成的代码），然后jOOQ将尝试解析所有标识符。
在jOOQ 3.14之前，您可以实现一个VisitListener，遍历解析的SQL查询，并自行验证所有标识符。
英文:
Older versions of jOOQ did not implement meta data lookups in the parser, or only to some limited extent, and just accepted all valid identifiers. Starting with jOOQ 3.14, there will be an improved implementation as of:
https://github.com/jOOQ/jOOQ/issues/9061
You will need to provide jOOQ with a Configuration.metaProvider() (it defaults to the JDBC DatabaseMetaData backed implementation. You might prefer using generated code), and then jOOQ will try to resolve all identifiers.
Prior to jOOQ 3.14, you could implement a VisitListener which traverses the parsed SQL query, and validate all the identifiers yourself.
专注分享java语言的经验与见解，让所有开发者获益！
评论