查询适当的数据库模式 [英] Querying the appropriate database schema
问题描述
这是我的更早的问题的后续问题，有关在Java中指定多个架构使用jooq与H2进行交互.
This is a follow-on question to my earlier question about specifying multiple schemata in java using jooq to interact with H2.
我的测试H2 DB当前具有2个模式，即PUBLIC和INFORMATION_SCHEMA. H2将PUBLIC指定为默认架构.当运行查询时，应从例如INFORMATION_SCHEMA.TABLES中提取信息，查询将失败，并显示表未知" SQL错误.我只能通过执行factory.use(INFORMATION_SCHEMA)来执行此类查询.没有构建错误等，并且Eclipse可以正确自动完成，例如TABLES.TABLE_NAME.
My test H2 DB currently has 2 schemata, PUBLIC and INFORMATION_SCHEMA. PUBLIC is specified as the default schema by H2. When running a query that should extract information from eg INFORMATION_SCHEMA.TABLES the query fails with a "table unknown" SQL error. I am only able to execute such queries by executing a factory.use(INFORMATION_SCHEMA). There are no build errors etc and eclipse properly autocompletes eg TABLES.TABLE_NAME.
如果我不这样做，即使我为模式创建了正确的Factory对象，例如jop，jooq似乎也没有在适当的模式之前添加
If I dont do this, jooq doesnt seem to prepend the appropriate schema even though I create the correct Factory object for the schema eg
InformationSchemaFactory info = new InformationSchemaFactory(conn);
我了解了映射，但我对于我将哪种模式用作输入/输出有些困惑.
I read about mapping but am a bit confused as to which schema I would use as the input/output.
推荐答案
默认情况下，InformationSchemaFactory假定提供的连接实际上已连接到INFORMATION_SCHEMA.这就是为什么架构名称未在SQL中呈现的原因.示例:
By default, the InformationSchemaFactory assumes that the supplied connection is actually connected to the INFORMATION_SCHEMA. That's why schema names are not rendered in SQL. Example:
// This query...
new InformationSchemaFactory(conn).selectFrom(INFORMATION_SCHEMA.TABLES).fetch();
// ... renders this SQL (with the asterisk expanded):
SELECT * FROM "TABLES";
以上行为应记录在您生成的InformationSchemaFactory Javadoc中.为了在"TABLES"之前加上"INFORMATION_SCHEMA"，您有几种选择.
The above behaviour should be documented in your generated InformationSchemaFactory Javadoc. In order to prepend "TABLES" with "INFORMATION_SCHEMA", you have several options.
-
改为使用不与任何模式绑定的常规工厂:
Use a regular factory instead, which is not tied to any schema:
// This query...
new Factory(H2, conn).selectFrom(INFORMATION_SCHEMA.TABLES).fetch();
// ... renders this SQL:
SELECT * FROM "INFORMATION_SCHEMA"."TABLES";
使用另一个架构的工厂，例如生成的PublicFactory:
// This query...
new PublicFactory(conn).selectFrom(INFORMATION_SCHEMA.TABLES).fetch();
// ... renders this SQL:
SELECT * FROM "INFORMATION_SCHEMA"."TABLES";
使用设置和适当的架构映射来强制执行要呈现的架构名称.
Use Settings and an appropriate schema mapping to force the schema name to be rendered.
第一个选项可能是最简单的一个.
The first option is probably the easiest one.
此博客文章将为您提供一些有关如何将执行的查询记录到首选记录器输出的见解:
This blog post here will give you some insight about how to log executed queries to your preferred logger output: http://blog.jooq.org/2011/10/20/debug-logging-sql-with-jooq/
这篇关于查询适当的数据库模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
