Digest for jooq-user@googlegroups.com - 8 updates in 3 topics

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Digest for jooq-user@googlegroups.com - 8 updates in 3 topics

http://groups.google.com/group/jooq-user/topics mailing list
[hidden email]: Sep 28 01:53PM -0700

I am passing my queries through proxy sql which caches the raw query
strings its forwarding to mysql.
Proxysql is seeing this as the rendered query and is then ignoring the
cache.
 
On Tuesday, September 26, 2017 at 12:53:36 AM UTC-7, Lukas Eder wrote:
[hidden email]: Sep 28 03:33PM -0700

Sorry for the double post. I've been investigating further after
understanding some of the dynamics between these two pieces of software.
The issue isn't actually with the inlined sql (I was assuming the error
was in executing that statement) but in some initial setup that is being
done by some part of the tool chain (not sure where)- specifically
 
SET character_set_results = NULL
SET autocommits=?
 
These (obviously) not possible to be cache. The rest of the query is
working as intended. I am not sure at what layer of the toolchain these
commands are added though.
Is this from jOOQ?
Thanks for taking the time to look.
 
On Thursday, September 28, 2017 at 1:53:21 PM UTC-7, [hidden email]
wrote:
Lukas Eder <[hidden email]>: Sep 29 09:12AM +0200

That's interesting - well I can guarantee to you that jOOQ keeps its
promise when you set those flags, *especially* when you set them three
times. In particular, DSL.inline() will never produce a bind variable in
jOOQ.
 
But your JDBC driver might parse the SQL string again, and generate
synthetic bind variables. Oracle database has a server-side setting called
CURSOR_SHARING = FORCE, which will parse all literals and replace them by
bind variables prior to generating an execution plan. Perhaps that's done
in your JDBC driver as well?
 
What's your JDBC connection string and JDBC driver version?
 
Lukas Eder <[hidden email]>: Sep 29 09:13AM +0200

jOOQ doesn't run any such statements for you, it doesn't even manage your
connection...
 
[hidden email]: Sep 29 12:07PM -0700

You are correct - these queries are not coming from jOOQ at all - the
queries that were causing the issues were generated by JDBC during the
setting up of the connection.
jOOQ itself behaves correctly, the logs were confusing so I appreciate you
taking a look into this.
For the record I have jdbc version
mysql-connector-java-8.0.7-dmr ( Revision:
d33a3ca56530848bebdf75cf48cf8e68e0feef98 )
There isn't a whole lot of prior work done (at least published that I could
find) on doing offline cacheing with java and proxysql unfortunately.
Thanks again.
 
 
On Friday, September 29, 2017 at 12:13:17 AM UTC-7, Lukas Eder wrote:
Lukas Eder <[hidden email]>: Sep 29 06:34AM -0700

Version 3.10.0 - September 29, 2017
================================================================================
 
Formal Java 9 Support
---------------------
 
jOOQ 3.10 is the first release that is formally integration tested with
Java 9
along with the existing integration tests for Java 6/7 and for Java 8. To
use
jOOQ with Java 9 use the Java 8 distribution which has not yet been
modularised,
but contains Automatic-Module-Name specification to be forward compatible
with
future, modularised jOOQ distributions.
 
 
Cross release for Scala 2.10, 2.11, 2.12
----------------------------------------
 
We've been offering cross releases for Java 6/7 and Java 8 in the past and
we're
now doing the same for Scala. In jOOQ 3.10, Scala 2.11+ will be supported
by the
jOOQ Open Source Edition and Scala 2.10+ by the commercial editions.
 
 
More and better stored procedure support for Oracle and SQL Server
------------------------------------------------------------------
 
We've finally supported SQL Server's table-valued parameters (TVP), which
are a
great way of passing data sets to procedures in SQL Server.
 
In Oracle, we're now supporting a variety of cool PL/SQL features that are
not
supported directly by ojdbc, among which:
 
- UPDATE RETURNING and DELETE RETURNING
- Oracle 12c implicit result sets
- PL/SQL RECORD Types passed to and returned from stored procedures
- PL/SQL %ROWTYPE references
- SYS_REFCURSOR IN parameters (we have always supported them as OUT
parameters)
- Combinations of PL/SQL BOOLEAN and PL/SQL RECORD types
 
We believe that those excellent T-SQL and PL/SQL features would be used much
more often by Java developers, if the serialisation was made easier. That's
why
jOOQ 3.10 greatly helps here.
 
 
New parser now fully supported
------------------------------
 
In jOOQ 3.9, we've introduced an interesting new feature that will allow
jOOQ to
be used in non-classic jOOQ contexts, e.g. as a JDBC proxy to translate SQL
from
dialect to dialect, or as a formatting tool.
 
Users who have been using jOOQ's plain SQL API will be happy to hear that
using
the parser, they will be able to validate their plain SQL already in the
client,
and possibly normalise / transform it using the entire jOOQ tool chain
including
the VisitListener SPI.
 
One immediate benefit of having a parser is the new DDLDatabase, which can
reverse engineer your DDL scripts to generate jOOQ code out of them,
without any
connection to a database!
 
 
Support for index types
-----------------------
 
While we've supported DDL operations to create / drop indexes for a while,
we
haven't supported indexes in our meta model. We finally do with jOOQ 3.10.
They
can now be used:
 
- In code generation output
- As plain SQL meta objects
- As InformationSchema import / exports
- And much more
 
 
Other great improvements
------------------------
 
- We're now supporting MySQL 8.0 and its WITH clause and window functions.
- Support for the SQL Server 2016 dialect
- Better support for PostgreSQL's ON CONFLICT clause
- Support for FETCH .. WITH TIES: Native and emulated using RANK() OVER
(...)
- More improvements for JSR-310 type support
- Many improvements to the org.jooq.Name API for manipulating qualified
names
- More DDL support and emulations for DB2, Oracle, and SQL Server
- Many improvements to the org.jooq.Queries API (batches of org.jooq.Query)
- A new RecordUnmapper SPI - the inverse of the existing RecordMapper
- Record unstructuring in Kotlin
- Results can now be formatted as ASCII charts
 
 
Features and Improvements
-------------------------
#1735 - Add Setting to indicate that bind values should not be cast
#2520 - Add RecordUnmapper<E, R extends Record> to allow for the inverse
operations of RecordMapper
#2830 - Add JavaDoc on Settings classes
#3062 - Add support for conversion into Collection types
#3593 - Add support for packages and routines in XMLDatabase
#4111 - Cross-release several jooq-scala deliverables
#4846 - Add <orderProvider/> SPI to allow for injecting meta data ordering
#4900 - Add support for LIMIT .. WITH TIES
#4990 - Deprecate Context.keyword() and Context.literal()
#5149 - Pull up AttachableInternal.configuration() to Attachable. Remove
AttachableInternal
#5171 - Support SQL Server table valued parameters
#5189 - Generate synthetic ArrayRecord and UDTs for SQL Server table types
#5191 - Support multi-row UPDATE and DELETE RETURNING for Oracle using
PL/SQL RETURNING .. BULK COLLECT INTO
#5231 - Add a subsection to the manual explaining each setting from the
Settings.xml
#5361 - Add ResultQuery.fetchStreamInto()
#5411 - Add support for ResultQuery.fetchSingle(), which returns exactly
one record
#5493 - [#2123] Combining INSERT..RETURNING and ON DUPLICATE KEY
#5551 - Add support for MySQL 8.0 CTE
#5552 - Add a MySQL 8 SQLDialect version
#5568 - #3062 Work on adding ability to convert between collections and
arrays.
#5619 - Add Field.containsIgnoreCase() to support Postgres' ILIKE operator
#5637 - Add support for PostgreSQL ON CONFLICT DO UPDATE WHERE
#5638 - Add org.jooq.Index
#5645 - Add DSL.localDateAdd() localDateSub(), localDateDiff()
#5666 - Add support for Oracle 12c implicit result sets returned from
procedures
#5667 - Review jOOQ's reflection usage for JDK 9 / Jigsaw compatibility
#5724 - Add support for MySQL's ALTER TABLE .. RENAME INDEX
#5732 - Emulate { ALTER | DROP } .. IF EXISTS in Oracle using a PL/SQL block
#5733 - Add a configuration option for varargs setters
#5738 - Add DSLContext.parsingConnection() to expose the new Parser API
through the JDBC API
#5742 - Stop distributing third party dependencies through zip distribution
#5745 - Make JDBC driver configuration optional in code generator
configuration
#5746 - The <database/> element in the code generator configuration should
be optional
#5747 - Mention JPADatabase in jooq-codegen.xsd
#5750 - Deprecate and undocument the <customTypes/> element
#5754 - Remove copyright (and dates) from file headers
#5762 - Add DataType.getSQLType(Configuration)
#5764 - Add support for inlining SQL Server table valued parameters in SQL
#5767 - Add a comment to the manual's section about MatcherRules regarding
unqualified / qualified identifier matching
#5770 - Add Field.notContains()
#5772 - Implement Converter.toString() of Converter.of() and ofNullable()
results
#5790 - Add Setting.returnRecordToPojo to disable copying record values
back into inserted / updated POJOs
#5791 - Implement DefaultRecordContext.toString()
#5806 - Add support for H2's TIMESTAMP WITH TIME ZONE data type
#5809 - Add code generation support for %ROWTYPE references in Oracle
#5823 - Add native support for HSQLDB UUID type
#5824 - Add Convert support for converting UUID to byte[] and vice versa
#5826 - Add Settings.delimiter to specify the standard delimiter for batches
#5828 - Add Queries.fetchMany()
#5829 - Enhance jooq-meta.xsd to include INFORMATION_SCHEMA.ROUTINES and
PARAMETERS dictionary views
#5833 - Add support for routines in XMLGenerator
#5834 - Improve formatting of multi-argument named parameter procedure calls
#5850 - Add support for Oracle SYS_REFCURSOR IN parameters
#5853 - Add XMLFormat, an XML export / import formatting configuration
#5854 - Support indented XML output in formatXML()
#5862 - Rename Generator.fluentSetters() to
Generator.generateFluentSetters() for API naming consistency
#5877 - Add <enumConverter/> flag in <forcedType/> to auto-generate
EnumConverter
#5878 - Add DelegatingConverter<T, U>
#5884 - Allow for specifying Java expressions as Converter / Binding
configurations
#5885 - Document the fact that only the first matching <forcedType/> is a
applied to a data type definition
#5894 - Add Record.formatXML(), formatJSON()
#5904 - Improve Javadoc on ExecuteContext.rows()
#5920 - Add several Binding.of() convenience constructors
#5924 - Add the Table.fullJoin() and SelectJoinStep.fullJoin() aliases
#5927 - Undeprecate Parser
#5942 - [5619] Add Field.containsIgnoreCase() to support Postgres' ILIKE
operator
#5947 - Add TypedElementDefinition.getDefinedType()
#5956 - Add DSL.{rank|denseRank|percentRank|cumeDist}(Collection<? extends
Field<?>>)
#5968 - Add JavaGenerator.printPackageComment() to allow for overriding this
#5973 - Allow for formatting / indenting JSON output through JSONFormat
#5987 - Add DSLContext.map(Schema) and map(Table) to expose the schema
mapping feature
#5993 - Add Name DSL.quotedName() and unquotedName()
#5995 - Add Context.quote()
#5996 - Add Field.as(Name) and Table.as(Name), Table.as(Name, Name...)
#5997 - Field.as(Field) and Table.as(Table) should retain quotation flag
from Name
#5998 - Add DSL.name(Name...) to construct a Name from Name.last() elements
#6000 - Add TableImpl.rename(Name)
#6005 - Add DDLDatabase to reverse engineer DDL files
#6006 - Add DSL.primaryKey(Name...), unique(Name...), foreignKey(Name...)
#6009 - Add support for CREATE INDEX ON with on(Name, Name...) arguments
#6011 - Add better documentation for the JPADatabase
#6012 - Implement specialised Name for unqualified Names
#6013 - Add Name Name.qualifier()
#6014 - Implement QualifiedTable.getSchema() and
QualifiedSchema.getCatalog()
#6021 - Add DSLContext.with(Name, Name...)
#6022 - Add Name.fields(Name, ...)
#6035 - Detect third-party JDBC URL plugins in JDBCUtils
#6044 - Add DSLContext.currval(Name) and nextval(Name)
#6052 - Create an internal Keyword cache
#6053 - KeywordImpl should cache the AS_IS, UPPER, LOWER renderings
#6056 - Upgrade optional logger dependencies
#6063 - Use eq / ne / etc in manual, rather than equal / notEqual / etc
#6068 - Add Name { Field | Table | Schema | Catalog }.getQualifiedName()
#6070 - Add Name Name.unqualifiedName()
#6072 - Add code generation flag <recordsImplementingRecordN/> to configure
whether generated Records should implement Record[N]
#6073 - Add Name[] Name.parts()
#6074 - Implement QueriesImpl.equals() and hashCode()
#6079 - Add JoinType.qualified()
#6082 - DSL.condition(Field) and field(Condition) should unwrap previously
wrapped QueryPart
#6084 - Let { Update | Delete }ReturningStep extend { Update | Delete
}FinalStep
#6086 - Add Row Parser.parseRow(String)
#6087 - Make ParserException public
#6088 - InsertSetStep.select() should return InsertOnDuplicateStep
#6093 - Add Field.pow[er](Field)
#6094 - Support Oracle's PARTITION BY prefixed OUTER JOIN
#6099 - Document Parser's BNF
#6104 - Improve VALUES constructor formatting
#6105 - Add Name { Field | Table | Schema | Catalog }.getUnqualifiedName()
#6108 - Add PostgresDSL.arrayOverlap()
#6114 - [#6063] change Field.equal() and Field.notEqual() to Field.eq() and
Field.ne() in manual pages
#6120 - Add support for DB2 RENAME INDEX statement
#6121 - Add support for DB2 RENAME TABLE statement
#6123 - Emulate CREATE TABLE AS for DB2 using CREATE TABLE and INSERT
#6125 - Support different DB2 sqlstates when emulating IF NOT EXISTS
#6128 - Add more Javadoc to JPADatabase
#6136 - [#6022] add Name.fields(Name...) method along with the generated
ones
#6139 - Put XJC generated sources under version control
#6143 - Rename Queries.stream() to Queries.queryStream()
#6144 - Let Queries extend QueryPart
#6146 - Add DSLContext.queries(Query...)
#6147 - Let Queries extend Attachable
#6148 - Add Queries.executeBatch()
#6154 - Remove test dependencies from OSS distribution
#6155 - Remove unneeded javax.validation dependency
#6156 - Add Configuration.recordUnmapperProvider()
#6162 - Deprecate plain SQL DSL.sequence(String) constructors
#6167 - GenerationTool should debug-log input Configuration as XML
#6169 - Document exception propagation in transaction API Javadoc
#6194 - Add a section to the manual about the Query By Example API
#6195 - Add DSLContext.fetchByExample(TableRecord)
#6196 - Override Iterable.forEach(Consumer) in ResultQuery to provide
Javadoc
#6212 - Generate deprecation for all objects referencing DataType<Object>
(unknown type)
#6214 - Pull up jOOQ-meta's AbstractDatabase.getProperties() to Database
#6215 - Add localDateAdd() localDateSub(), localDateDiff() support to DSL
#6217 - Add more details to QueryPart.toString() Javadoc
#6218 - Add a section to the manual about the plain SQL templating part
#6219 - Add Parser.parseResultQuery()
#6222 - Generate IDENTITY information on data type
#6239 - Add SQLServer2016 dialect
#6245 - Add Record[N].component[1-N]() methods for better Kotlin
interoperability
#6246 - Add Kotlin operators on Field
#6247 - Add more Kotlin examples
#6253 - Add a jOOQ / JPA example to GitHub
#6254 - Deprecate the ResultQuery.intern() and Result.intern() features
#6255 - Add better Enum to Enum conversion support in org.jooq.tools.Convert
#6258 - Emulate MySQL / T-SQL DROP INDEX .. ON for other databases
#6268 - Support DROP SCHEMA IF EXISTS for SQL Server
#6269 - Support CREATE SCHEMA IF NOT EXISTS in SQL Server
#6271 - Improve onKey() join error messages, when join expression is
ambiguous
#6273 - Document <configurationFile/> Maven plugin property
#6275 - Add SQL Server support for ALTER VIEW .. RENAME
#6276 - Add SQL Server support for ALTER TABLE IF EXISTS
#6280 - Deprecate DSLContext.bindContext() and renderContext()
#6281 - Add DSL.arrayAggDistinct()
#6285 - Add Javadoc to code generator Configuration classes
#6287 - Upgrade Spring Boot example to version 1.5.3
#6296 - Update Hibernate Dependency in jooq-meta-extensions and examples
#6297 - Add "development versions" link to the manual
#6299 - Remove unneeded config files in spring-boot-example
#6300 - Remove unneeded config files in spring-boot-example
#6304 - Improve internal TableAlias discovery
#6306 - Add org.jooq.True and org.jooq.False
#6307 - Add code generation support for indexes
#6312 - Document DISTINCT predicate emulation using INTERSECT
#6313 - Add List<Index> Table.getIndexes()
#6315 - Add SortField Field.sortDefault()
#6319 - Add INDEXES and INDEX_COLUMN_USAGES views to jooq-meta.xsd
#6320 - Import / Export index information with
DSLContext.meta(InformationSchema) / DSLContext.informationSchema()
#6321 - Export indexes using XMLGenerator
#6322 - Import indexes using XMLDatabase
#6323 - Generate javax.persistence.Index annotation
#6325 - Add ResultQuery.fetchGroups(Field[], Field[]) and similar overloads
#6336 - Add another Kotlin example using apply
#6352 - Add Result.formatChart() to produce ASCII text charts
#6355 - Improve Kotlin example in manual
#6362 - Auto-wrap Select as Field in overloaded API like DSL.least()
#6363 - Rename Cursor.fetchXXX() methods to fetchNextXXX()
#6368 - Port Sakila database to DB2
#6372 - Add DSLContext Configuration.dsl()
#6374 - In jOOQ-spring-boot-example, use jooq starter instead of jdbc
starter
#6381 - Add Derby support for RENAME { TABLE | COLUMN | INDEX }
#6386 - Add Scope.dsl()
#6391 - Add ResultOrRows.exception() to improve support for SQL Server's
RAISERROR
#6410 - Don't generate empty Keys.java or Indexes.java files
#6411 - Code generator should generate VARCHAR(length) and
NUMBER(precision, scale) method
#6413 - Add Setting.throwExceptions to define how SQLException should be
propagated out of jOOQ
#6416 - Add Name.unquotedName() and Name.quotedName()
#6418 - Change SQLite's VALUES constructor implementation to support
standard multi-row VALUES() clause
#6422 - Add support for MySQL 8.0 window functions
#6426 - Add support for MySQL 8.0 FOR UPDATE { NOWAIT | SKIP LOCKED }
#6427 - Deprecate partitionByOne() API
#6428 - Add missing overload WindowPartitionByStep.partitionBy(Collection<?
extends Field<?>>)
#6430 - Add support for MySQL 8.0 FOR UPDATE OF [ tables ]
#6434 - Enhance "Forcing type" INFO message with the full <forcedType/>
specification
[hidden email]: Sep 29 05:31AM -0700

Since jOOQ does not map TINYINT(1) to java.lang.Boolean by default, is it
possible to add this mapping programmatically? The solutions I found do not
fit my case, which is:
DSL.using( SQLDialect.MYSQL ).fetchOne( resultSet ).intoMap()
 
The problem is that the fields list is completely dynamic, so I can't set
proper Bindings or Converters on Field instances. Rather I need this
setting to be global, attached to some configuration or something.
Lukas Eder <[hidden email]>: Sep 29 02:56PM +0200

Hello,
 
There's a common misconception among MySQL users that TINYINT(1) is:
 
- A good hint for the type to have boolean semantics
- An actual type
 
When you specify TINYINT(1), the type will still be a TINYINT. Observe the
following:
 
CREATE TABLE t(i TINYINT(1));
 
INSERT INTO t VALUES (1);
INSERT INTO t VALUES (2);
INSERT INTO t VALUES (10);
 
SELECT * FROM t;
 
 
You'll get:
 
i
--
1
2
10
 
 
That explains why it would be unwise for jOOQ to consider that "length"
information for any type deduction.
 
The "global" type conversion mechanism that you could use would be to
create your own general-purpose Binding and use that for every dynamically
created column expression (you'd have to know these columns in advance,
though).
 
Otherwise, you could patch jOOQ's internal MetaDataFieldProvider and put
the desired logic there.
 
In any case, I think your idea is very interesting. jOOQ should have an SPI
that allows for overriding such default types at runtime. I've created a
feature request for this:
https://github.com/jOOQ/jOOQ/issues/6619
 
Cheers,
Lukas
 
You received this digest because you're subscribed to updates for this group. You can change your settings on the group membership page.
To unsubscribe from this group and stop receiving emails from it send an email to [hidden email].