jooq映射原理_JOOQ事实:从JPA批注到JOOQ表映射
原文翻译
JOOQ是一个简洁的框架,它解决了我在使用高级动态过滤查询时遇到的一个长期问题。虽然Hibernate和JPA附带了一个有用的Criteria API(我已经使用了很长一段时间),但是您可以使用这些API进行操作有一些可以理解的限制。例如,您不能超出简单的SQL操作(例如,JOINS,NESTED SLECTS,AGGREGATION),并且要做类似的事情:窗口函数,用户定义的函数或简单的排序等。
JOOQ不想和Hibernate竞争,但是我觉得它可以完成它。我一直在将Hibernate用于数据层的WRITE部分,因此使用它的名称或JPA中的“ Persisting”部分。对于简单到中等复杂的查询,Hibernate会尽力而为,但是我不必全部依靠它进行所有查询,是吗?查询属性还有一个缺点,这是因为有时为了仅针对少数用例进行查询,有时必须向域模型添加关联。
因此,由于我不怕编写本机查询,因此可以以DSL方式和独立于供应商的方式来执行此操作。
尽管可以使用基于字符串的列命名,但JOOQ通过使用类型安全的元数据提供了一种更好的方法,因此,我们要做的第一件事是为数据库架构生成表映射。
由于我已经有了JPA模型,因此可以从中生成数据库模式DDL,为此,我们可以使用hibernatetool ant任务。
<plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-antrun-plugin</artifactId> <executions> <execution> <id>generate-test-sql-scripts</id> <phase>generate-test-resources</phase> <goals> <goal>run</goal> </goals> <configuration> <tasks> <property name="maven_test_classpath" refid="maven.test.classpath"/> <path id="hibernate_tools_path"> <pathelement path="${maven_test_classpath}"/> </path> <property name="hibernate_tools_classpath" refid="hibernate_tools_path"/> <taskdef name="hibernatetool" classname="org.hibernate.tool.ant.HibernateToolTask"/> <mkdir dir="${project.build.directory}/test-classes/hsqldb"/> <hibernatetool destdir="${project.build.directory}/test-classes/hsqldb"> <classpath refid="hibernate_tools_path"/> <jpaconfiguration persistenceunit="testPersistenceUnit" propertyfile="src/test/resources/META-INF/spring/jdbc.properties"/> <hbm2ddl drop="false" create="true" export="false" outputfilename="create_db.sql" delimiter=";" format="true"/> <hbm2ddl drop="true" create="false" export="false" outputfilename="drop_db.sql" delimiter=";" format="true"/> </hibernatetool> </tasks> </configuration> </execution> </executions> <dependencies> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-entitymanager</artifactId> <version>${hibernate.version}</version> <exclusions> <exclusion> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-tools</artifactId> <version>${hibernate.tools.version}</version> <exclusions> <exclusion> <groupId>org.hibernate</groupId> <artifactId>hibernate-commons-annotations</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> <version>${slf4j.version}</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-simple</artifactId> <version>${slf4j.version}</version> </dependency> </dependencies> </plugin>
这将生成一个“ create_db.sql”数据库DDL脚本,我们将使用“ maven.sql.plugin”将其用于填充基于文件的临时HSQLDB。我本来希望使用内存中的HSQLDB,但不幸的是它没有保存插件执行之间的状态。
<plugin> <groupId>org.codehaus.mojo</groupId> <artifactId>sql-maven-plugin</artifactId> <dependencies> <dependency> <groupId>org.hsqldb</groupId> <artifactId>hsqldb</artifactId> <version>${hsqldb.version}</version> </dependency> </dependencies> <configuration> <driver>org.hsqldb.jdbc.JDBCDriver</driver> <url>jdbc:hsqldb:file:${project.build.directory}/hsqldb/db;shutdown=true</url> <username>sa</username> <password></password> <autocommit>true</autocommit> <settingsKey>hsql-db-test</settingsKey> </configuration> <executions> <execution> <id>create-test-compile-data</id> <phase>process-test-resources</phase> <inherited>true</inherited> <goals> <goal>execute</goal> </goals> <configuration> <orderFile>ascending</orderFile> <fileset> <basedir>${project.build.directory}/test-classes/hsqldb/</basedir> <includes> <include>create_db.sql</include> </includes> </fileset> <autocommit>true</autocommit> </configuration> </execution> </executions> </plugin>
因此,HSQLDB现在已由我们的JPA生成的架构填充,并且我们终于可以调用JOOQ代码生成来构建表映射。
<plugin> <groupId>org.codehaus.mojo</groupId> <artifactId>sql-maven-plugin</artifactId> <dependencies> <dependency> <groupId>org.hsqldb</groupId> <artifactId>hsqldb</artifactId> <version>${hsqldb.version}</version> </dependency> </dependencies> <configuration> <driver>org.hsqldb.jdbc.JDBCDriver</driver> <url>jdbc:hsqldb:file:${project.build.directory}/hsqldb/db;shutdown=true</url> <username>sa</username> <password></password> <autocommit>true</autocommit> <settingsKey>hsql-db-test</settingsKey> </configuration> <executions> <execution> <id>create-test-compile-data</id> <phase>process-test-resources</phase> <inherited>true</inherited> <goals> <goal>execute</goal> </goals> <configuration> <orderFile>ascending</orderFile> <fileset> <basedir>${project.build.directory}/test-classes/hsqldb/</basedir> <includes> <include>create_db.sql</include> </includes> </fileset> <autocommit>true</autocommit> </configuration> </execution> </executions> </plugin>
通过maven运行,我们生成了表映射,因此让我们将Image类的JPA元模型与关联的JOOQ表映射进行比较:
JPA元模型如下所示:
@StaticMetamodel(Image.class) public abstract class Image_ { public static volatile SingularAttribute<Image, Product> product; public static volatile SingularAttribute<Image, Long> id; public static volatile SetAttribute<Image, Version> versions; public static volatile SingularAttribute<Image, Integer> index; public static volatile SingularAttribute<Image, String> name; }
和JOOQ表映射
@javax.annotation.Generated(value = { "http://www.jooq.org", "3.2.0" }, comments = "This class is generated by jOOQ") @java.lang.SuppressWarnings({ "all", "unchecked", "rawtypes" }) public class Image extends org.jooq.impl.TableImpl<vladmihalcea.jooq.schema.tables.records.ImageRecord> { private static final long serialVersionUID = 1596930978; /** * The singleton instance of <code>PUBLIC.IMAGE</code> */ public static final vladmihalcea.jooq.schema.tables.Image IMAGE = new vladmihalcea.jooq.schema.tables.Image(); /** * The class holding records for this type */ @Override public java.lang.Class<vladmihalcea.jooq.schema.tables.records.ImageRecord> getRecordType() { return vladmihalcea.jooq.schema.tables.records.ImageRecord.class; } /** * The column <code>PUBLIC.IMAGE.ID</code>. */ public final org.jooq.TableField<vladmihalcea.jooq.schema.tables.records.ImageRecord, java.lang.Long> ID = createField("ID", org.jooq.impl.SQLDataType.BIGINT.nullable(false), this); /** * The column <code>PUBLIC.IMAGE.INDEX</code>. */ public final org.jooq.TableField<vladmihalcea.jooq.schema.tables.records.ImageRecord, java.lang.Integer> INDEX = createField("INDEX", org.jooq.impl.SQLDataType.INTEGER, this); /** * The column <code>PUBLIC.IMAGE.NAME</code>. */ public final org.jooq.TableField<vladmihalcea.jooq.schema.tables.records.ImageRecord, java.lang.String> NAME = createField("NAME", org.jooq.impl.SQLDataType.VARCHAR.length(255), this); /** * The column <code>PUBLIC.IMAGE.PRODUCT_ID</code>. */ public final org.jooq.TableField<vladmihalcea.jooq.schema.tables.records.ImageRecord, java.lang.Long> PRODUCT_ID = createField("PRODUCT_ID", org.jooq.impl.SQLDataType.BIGINT, this); /** * Create a <code>PUBLIC.IMAGE</code> table reference */ public Image() { super("IMAGE", vladmihalcea.jooq.schema.Public.PUBLIC); } /** * Create an aliased <code>PUBLIC.IMAGE</code> table reference */ public Image(java.lang.String alias) { super(alias, vladmihalcea.jooq.schema.Public.PUBLIC, vladmihalcea.jooq.schema.tables.Image.IMAGE); } /** * {@inheritDoc} */ @Override public org.jooq.Identity<vladmihalcea.jooq.schema.tables.records.ImageRecord, java.lang.Long> getIdentity() { return vladmihalcea.jooq.schema.Keys.IDENTITY_IMAGE; } /** * {@inheritDoc} */ @Override public org.jooq.UniqueKey<vladmihalcea.jooq.schema.tables.records.ImageRecord> getPrimaryKey() { return vladmihalcea.jooq.schema.Keys.SYS_PK_10059; } /** * {@inheritDoc} */ @Override public java.util.List<org.jooq.UniqueKey<vladmihalcea.jooq.schema.tables.records.ImageRecord>> getKeys() { return java.util.Arrays.<org.jooq.UniqueKey<vladmihalcea.jooq.schema.tables.records.ImageRecord>>asList(vladmihalcea.jooq.schema.Keys.SYS_PK_10059, vladmihalcea.jooq.schema.Keys.UK_OQBG3YIU5I1E17SL0FEAWT8PE); } /** * {@inheritDoc} */ @Override public java.util.List<org.jooq.ForeignKey<vladmihalcea.jooq.schema.tables.records.ImageRecord, ?>> getReferences() { return java.util.Arrays.<org.jooq.ForeignKey<vladmihalcea.jooq.schema.tables.records.ImageRecord, ?>>asList(vladmihalcea.jooq.schema.Keys.FK_9W522RC4D0KFDKQ390IHV92GB); } /** * {@inheritDoc} */ @Override public vladmihalcea.jooq.schema.tables.Image as(java.lang.String alias) { return new vladmihalcea.jooq.schema.tables.Image(alias); } }
现在,我们还需要使Maven意识到我们新生成的JOOQ元数据类,以便它可以在下一个测试编译阶段对其进行编译。
<plugin> <groupId>org.codehaus.mojo</groupId> <artifactId>build-helper-maven-plugin</artifactId> <executions> <execution> <id>add-source</id> <phase>process-test-sources</phase> <goals> <goal>add-test-source</goal> </goals> <configuration> <sources> <source>${project.build.directory}/generated-sources/java</source> </sources> </configuration> </execution> </executions> </plugin>
现在,我可以开始玩JOOQ了。让我们将DSLContext添加到我们的Spring应用程序上下文中:
<bean id="jooqContext" class="org.jooq.impl.DSL" factory-method="using">
<constructor-arg ref="dataSource"/>
<constructor-arg value="#{T(org.jooq.SQLDialect).HSQLDB}"/>
</bean
我们将编写一个测试来检查一切是否正常:
private List<ImageProductDTO> getImageProductDTOs_JOOQ() { return transactionTemplate.execute(new TransactionCallback<List<ImageProductDTO>>() { @Override public List<ImageProductDTO> doInTransaction(TransactionStatus transactionStatus) { return jooqContext .select(IMAGE.NAME, PRODUCT.NAME) .from(IMAGE) .join(PRODUCT).on(IMAGE.PRODUCT_ID.equal(PRODUCT.ID)) .where(PRODUCT.NAME.likeIgnoreCase("%tv%")) .and(IMAGE.INDEX.greaterThan(0)) .orderBy(IMAGE.NAME.asc()) .fetch().into(ImageProductDTO.class); } }); }
生成以下SQL
SELECT "PUBLIC"."image"."name", "PUBLIC"."product"."name" FROM "PUBLIC"."image" JOIN "PUBLIC"."product" ON "PUBLIC"."image"."product_id" = "PUBLIC"."product"."id" WHERE ( Lower("PUBLIC"."product"."name") LIKE Lower('%tv%') AND "PUBLIC"."image"."index" > 0 ) ORDER BY "PUBLIC"."image"."name" ASC
这是我第一次使用JOOQ,花了我太多时间浏览文档并在Hibernate Facts编码示例中进行了所有设置。JOOQ查询的构建感觉很自然,就像编写本机SQL代码一样,因此我不必真正学习API就能知道如何使用它。我将很自豪地将其添加到我的Java Data Toolbox中。
这个编码示例将JOOQ映射生成到test-classes文件夹中,因此您不能在main / java源文件中使用它们。这可以解决,但是需要通过将模型类移动到单独的Maven模块中来重构现有解决方案。您可以在此单独的模块中生成JOOQ模式,在打包之前,您可以在其中将模式类从测试类移至classes文件夹。然后,您将必须包括这个新模块,通常在其中使用JOOQ模式。