运行时 SQL 查询生成器 [英] Runtime SQL Query Builder

问题描述

我的问题类似于

有没有好的动态SQL构建器库爪哇?

但是从上面的线程中提取的一个重要点:

However one important point taken from above thread:

Querydsl 和 jOOQ 似乎是最流行和最成熟的选择，但是有一点需要注意:两者都依赖于代码生成的概念，其中为数据库表和字段生成元类.这有助于创建一个漂亮、干净的 DSL，但它在尝试为仅在运行时已知的数据库创建查询时会遇到问题.

Querydsl and jOOQ seem to be the most popular and mature choices however there's one thing to be aware of: Both rely on the concept of code generation, where meta classes are generated for database tables and fields. This facilitates a nice, clean DSL but it faces a problem when trying to create queries for databases that are only known at runtime.

除了使用普通的 JDBC + 字符串连接之外，还有什么方法可以在运行时创建查询吗?

Is there any way to create the queries at runtime besides just using plain JDBC + String concatenation?

我正在寻找的是一个 Web 应用程序，它可用于构建表单以查询现有数据库.现在，如果类似的东西已经存在，那么也欢迎链接到这样的产品.

What I'm looking for is a web application that can be used to build forms to query existing databases. Now if something like that already exists links to such a product would be welcome too.

推荐答案

虽然为数据库元数据生成源代码确实为使用 jOOQ，它不是先决条件.许多 jOOQ 用户将 jOOQ 用于您设想的相同用例.这也反映在 jOOQ 教程，其中列出了使用 jOOQ 而不生成代码作为一个完全有效的用例.例如:

While source code generation for database meta data certainly adds much value to using jOOQ, it is not a prerequisite. Many jOOQ users use jOOQ for the same use-case that you envision. This is also reflected in the jOOQ tutorials, which list using jOOQ without code generation as a perfectly valid use-case. For example:

String sql = create.select(
                        fieldByName("BOOK","TITLE"), 
                        fieldByName("AUTHOR","FIRST_NAME"), 
                        fieldByName("AUTHOR","LAST_NAME"))
                   .from(tableByName("BOOK"))
                   .join(tableByName("AUTHOR"))
                   .on(fieldByName("BOOK", "AUTHOR_ID").eq(
                        fieldByName("AUTHOR", "ID")))
                   .where(fieldByName("BOOK", "PUBLISHED_IN").eq(1948))
                   .getSQL();

以类似的方式，可以使用 Query.getBindValues().

In a similar fashion, bind values can be extracted from any Query using Query.getBindValues().

对于动态 SQL 语句，这种方法仍然胜过普通的 JDBC + 字符串连接，因为您无需担心:

This approach will still beat plain JDBC + String concatenation for dynamic SQL statements, as you do not need to worry about:

• 语法正确性
• 跨数据库兼容性
• SQL 注入
• 绑定变量索引

(免责声明:我为 jOOQ 的供应商工作)

(Disclaimer: I work for the vendor of jOOQ)

这篇关于运行时 SQL 查询生成器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！