We are using JOOQ 3.11.1
Our java DB stack was implemented using hibernate, but now we're trying to
use JOOQ. We are trying to flip to our JOOQ production code for a small set
of entities at a time. We have tests that run on local MYSQL DB instances
and work successfully. Our code works in our production MYSQL environments
as well. We have integration tests on HSQLDB. These tests run using the
hibernate production stack (which use sessions and entity objects for CRUD
operations). We run into some issues when using JOOQ for these integration
tests. I'm currently just trying to use JOOQ for a couple of entities to
insert, select, delete, and list while the other entities still interact
using hibernate.
*I have run into three types issues so far*:
1. "user lacks privilege or object not found: n_p_c". n_p_c is a table in
our DB
2. "integrity constraint violation: NOT NULL check constraint; table:
n_p_c column: c_s_date"
3. "user lacks privilege or object not found: DUAL"
*Problem 1*
- Problem 1. hides problem 2. When problem 1. is fixed, problem 2. appears.
I was able to fix problem 1. by adding the RenderNameStyle to AS_IS in our
integration test. NOTE: This render style is not in production because we
have not run into issues in production.
org.jooq.exception.DataAccessException: SQL [insert into `n_p_c`
(`f_number`, `t_number`) values (?, ?)]; user lacks privilege or object not
found: n_p_c
at org.jooq_3.11.1.MYSQL.debug(Unknown Source)
at org.jooq.impl.Tools.translate(Tools.java:2380)
at
org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:802)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:364)
at
org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:127)
Integration testing settings:
new Settings().withRenderMapping(renderMapping) // renderMapping is just
new RenderMapping();
.withExecuteLogging(true)
.withRenderFormatted(true)
.withFetchWarnings(true)
.withRenderSchema(false);
.withRenderNameStyle(RenderNameStyle.AS_IS); // Adding this
SPECIFICALLY for integration testing.
// This is not needed in production. I found solution to this
here:
https://groups.google.com/forum/#!searchin/jooq-user/user$20lacks$20privilege$20or$20object$20not$20found%7Csort:date/jooq-user/qDKCmMI9-14/9AXYs-wuHnUJ
*Problem 2*
- Problem 2 fails to insert a row because my JOOQ query does not explicitly
set the c_s_date. c_s_date is a Timestamp that the database sets on
creation.
This is the JOOQ generated field for c_s_date:
public final TableField<NPCRecordRecord, Timestamp> C_S_DATE =
createField("C_S_DATE",
org.jooq.impl.SQLDataType.TIMESTAMP.nullable(false).defaultValue(org.jooq.impl.DSL.field("CURRENT_TIMESTAMP",
org.jooq.impl.SQLDataType.TIMESTAMP)), this, "");
org.jooq.exception.DataAccessException: SQL [insert into n_p_c
(f_number, t_number) values (?, ?)]; integrity constraint violation: NOT
NULL check constraint; table: n_p_c column: c_s_date
at org.jooq_3.11.1.MYSQL.debug(Unknown Source)
at org.jooq.impl.Tools.translate(Tools.java:2380)
at
org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:802)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:364)
at
org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:127)
Caused by: java.sql.SQLIntegrityConstraintViolationException: integrity
constraint violation: NOT NULL check constraint; table: n_p_c column:
c_s_date
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.execute(Unknown Source)
at
com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at
com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at
org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:209)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:432)
at org.jooq.impl.AbstractDMLQuery.execute(AbstractDMLQuery.java:612)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:350)
... 64 more
Caused by: org.hsqldb.HsqlException: integrity constraint violation: NOT
NULL check constraint; table: n_p_c column: c_s_date
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.Table.enforceRowConstraints(Unknown Source)
at org.hsqldb.Table.insertSingleRow(Unknown Source)
at org.hsqldb.StatementDML.insertSingleRow(Unknown Source)
at org.hsqldb.StatementInsert.getResult(Unknown Source)
at org.hsqldb.StatementDMQL.execute(Unknown Source)
at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 72 more
*Problem 3*
- Problem 3 stems from trying to query this "DUAL" table. I am not too
familiar with this.
org.jooq.exception.DataAccessException: SQL [select 1 as one from dual
where exists (select n_p_c.id, n_p_c.f_number, n_p_c.t_number,
n_p_c.c_s_date from n_p_c where (n_p_c.t_number = ? and n_p_c.f_number =
?))]; user lacks privilege or object not found: DUAL
at org.jooq_3.11.1.MYSQL.debug(Unknown Source)
at org.jooq.impl.Tools.translate(Tools.java:2380)
at
org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:802)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:364)
at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:393)
at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:380)
at org.jooq.impl.AbstractResultQuery.fetchOne(AbstractResultQuery.java:545)
at org.jooq.impl.SelectImpl.fetchOne(SelectImpl.java:2879)
at org.jooq.impl.DefaultDSLContext.fetchExists(DefaultDSLContext.java:4345)
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object
not found: DUAL
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
at
com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:311)
at
com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
at
org.jooq.impl.ProviderEnabledConnection.prepareStatement(ProviderEnabledConnection.java:109)
at
org.jooq.impl.SettingsEnabledConnection.prepareStatement(SettingsEnabledConnection.java:73)
at org.jooq.impl.AbstractResultQuery.prepare(AbstractResultQuery.java:239)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:322)
... 103 more
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not
found: DUAL
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.SchemaManager.getTable(Unknown Source)
at org.hsqldb.ParserDQL.readTableName(Unknown Source)
at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source)
at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source)
at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source)
at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
at org.hsqldb.Session.compileStatement(Unknown Source)
at org.hsqldb.StatementManager.compile(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 111 more
Any advice/pointer is much appreciated.
Thanks
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
