jOOQ is the best way to write SQL in Java

Overview

jOOQ's reason for being - compared to JPA

Java and SQL have come a long way. SQL is an "ancient", yet established and well-understood technology. Java is a legacy too, although its platform JVM allows for many new and contemporary languages built on top of it. Yet, after all these years, libraries dealing with the interface between SQL and Java have come and gone, leaving JPA to be a standard that is accepted only with doubts, short of any surviving options.

So far, there had been only few database abstraction frameworks or libraries, that truly respected SQL as a first class citizen among languages. Most frameworks, including the industry standards JPA, EJB, Hibernate, JDO, Criteria Query, and many others try to hide SQL itself, minimising its scope to things called JPQL, HQL, JDOQL and various other inferior query languages

jOOQ has come to fill this gap.

jOOQ's reason of being - compared to LINQ

Other platforms incorporate ideas such as LINQ (with LINQ-to-SQL), or Scala's SLICK, or also Java's QueryDSL to better integrate querying as a concept into their respective language. By querying, they understand querying of arbitrary targets, such as SQL, XML, Collections and other heterogeneous data stores. jOOQ claims that this is going the wrong way too.

In more advanced querying use-cases (more than simple CRUD and the occasional JOIN), people will want to profit from the expressivity of SQL. Due to the relational nature of SQL, this is quite different from what object-oriented and partially functional languages such as C#, Scala, or Java can offer.

It is very hard to formally express and validate joins and the ad-hoc table expression types they create. It gets even harder when you want support for more advanced table expressions, such as pivot tables, unnested cursors, or just arbitrary projections from derived tables. With a very strong object-oriented typing model, these features will probably stay out of scope.

In essence, the decision of creating an API that looks like SQL or one that looks like C#, Scala, Java is a definite decision in favour of one or the other platform. While it will be easier to evolve SLICK in similar ways as LINQ (or QueryDSL in the Java world), SQL feature scope that clearly communicates its underlying intent will be very hard to add, later on (e.g. how would you model Oracle's partitioned outer join syntax? How would you model ANSI/ISO SQL:1999 grouping sets? How can you support scalar subquery caching? etc...).

jOOQ has come to fill this gap.

jOOQ is different

SQL was never meant to be abstracted. To be confined in the narrow boundaries of heavy mappers, hiding the beauty and simplicity of relational data. SQL was never meant to be object-oriented. SQL was never meant to be anything other than... SQL!

For more details please visit jooq.org.

Follow jOOQ on Twitter and the jOOQ blog.

Comments
  • Add support for Oracle associative array of object types as IN parameter

    Add support for Oracle associative array of object types as IN parameter

    Expected behavior

    No response

    Actual behavior

    Getting record type not supported in Oracle Dialect:

    Procedure ABC
    (
          x_rec                            IN  T.x_rec
    ,     a_tbl            				   IN  T.a_tbl
    ,     b_tbl           				   IN  T.b_tbl
    ,     c_tbl            				   IN  T.c_tbl
    ,     x_chrid                          OUT NOCOPY Number
    ,     x_return_status                  OUT NOCOPY VARCHAR2
    ,     x_msg_count                      OUT NOCOPY Number
    ,     x_msg_data                       OUT NOCOPY VARCHAR2
    )
    
    Type a_tbl is TABLE of CC_type index by binary_integer; 
    
         public class ATblRecord extends AssociativeArrayRecordImpl<Integer, CCTypeRecord> {
    
    
         		ATblRecord(){
         		 super(Apps.APPS, XX.AA, "CC_TYPE",
                com.example.springbootdemo.xxx.udt.ContactType.CC_TYPE.getDataType(),
                SQLDataType.INTEGER);
         		}
         }
    

    Calling method: ++++++++++++++++++++

     TTT.create(configuration, xrec, new ATblRecord(), new BTblRecord(),
            new CTblRecord());
    
    image image

    Ref: https://stackoverflow.com/questions/73574574/getting-sqldialectnotsupportedexception-not-supported-by-dialect-type-class

    Error:

    2022-09-02 12:18:21.960  WARN 27112 --- [nio-9193-exec-4] .S.o.j.e.SQLDialectNotSupportedException : Not supported by dialect : Type class com.example.springbootjooqdemo.jooq.sample.model.apps.packages.oks_contracts_pub.udt.records.ContactTblRecord is not supported in dialect ORACLE21C
    2022-09-02 12:18:21.963 ERROR 27112 --- [nio-9193-exec-4] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.jooq.exception.DataAccessException: SQL [null]; Error while writing value at JDBC bind index: 68] with root cause
    
    org.jooq.exception.SQLDialectNotSupportedException: Type class com.example.springbootjooqdemo.jooq.sample.model.apps.packages.TTT.T.records.TblRecord is not supported in dialect ORACLE21C
    	at org.jooq.impl.DefaultDataType.getDataType(DefaultDataType.java:835) ~[jooq-3.17.3.jar:na]
    	at org.jooq.impl.DefaultDataType.getDataType(DefaultDataType.java:779) ~[jooq-3.17.3.jar:na]
    	at org.jooq.impl.DefaultBinding$DefaultOtherBinding.set0(DefaultBinding.java:3561) ~[jooq-3.17.3.jar:na]
    	at org.jooq.impl.DefaultBinding$InternalBinding.set(DefaultBinding.java:1010) ~[jooq-3.17.3.jar:na]
    	at org.jooq.impl.DefaultBindContext.bindValue0(DefaultBindContext.java:63) ~[jooq-3.17.3.jar:na]
    	at org.jooq.impl.AbstractBindContext.bindValue(AbstractBindContext.java:74) ~[jooq-3.17.3.jar:na]
    	at org.jooq.impl.AssociativeArrayConstant.bind0(AssociativeArrayConstant.java:120) ~[jooq-3.17.3.jar:na]
    	at org.jooq.impl.AssociativeArrayConstant.accept(AssociativeArrayConstant.java:84) ~[jooq-3.17.3.jar:na]
    	at org.jooq.impl.AbstractBindContext.bindInternal(AbstractBindContext.java:258) ~[jooq-3.17.3.jar:na]
    	at org.jooq.impl.AbstractBindContext.visit0(AbstractBindContext.java:68) ~[jooq-3.17.3.jar:na]
    	at org.jooq.impl.AbstractContext.visit(AbstractContext.java:340) ~[jooq-3.17.3.jar:na]
    	at org.jooq.impl.AbstractContext.visit(AbstractContext.java:254) ~[jooq-3.17.3.jar:na]
    	at org.jooq.impl.AbstractRoutine.bind1(AbstractRoutine.java:717) ~[jooq-3.17.3.jar:na]
    	at org.jooq.impl.AbstractRoutine.bind0(AbstractRoutine.java:662) ~[jooq-3.17.3.jar:na]
    	at org.jooq.impl.AbstractRoutine.accept(AbstractRoutine.java:612) ~[jooq-3.17.3.jar:na]
    	at org.jooq.impl.AbstractBindContext.bindInternal(AbstractBindContext.java:258) ~[jooq-3.17.3.jar:na]
    	at org.jooq.impl.AbstractBindContext.visit0(AbstractBindContext.java:68) ~[jooq-3.17.3.jar:na]
    	at org.jooq.impl.AbstractContext.visit(AbstractContext.java:340) ~[jooq-3.17.3.jar:na]
    	at org.jooq.impl.AbstractRoutine.executeCallableStatement(AbstractRoutine.java:549) ~[jooq-3.17.3.jar:na]
    	at org.jooq.impl.AbstractRoutine.execute(AbstractRoutine.java:386) ~[jooq-3.17.3.jar:na]
    	at org.jooq.impl.Tools.attach(Tools.java:1518) ~[jooq-3.17.3.jar:na]
    	at org.jooq.impl.AbstractRoutine.execute(AbstractRoutine.java:366) ~[jooq-3.17.3.jar:na]
    	at com.example.springbootjooqdemo.jooq.sample.model.apps.packages.OksContractsPub.createContractHeader(OksContractsPub.java:156) ~[classes/:na]
    	at com.example.springbootjooqdemo.SpringBootJooqDemoApplication.createContractHeader(SpringBootJooqDemoApplication.java:99) ~[classes/:na]
    	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
    	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    	at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
    	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) ~[spring-web-5.3.22.jar:5.3.22]
    	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150) ~[spring-web-5.3.22.jar:5.3.22]
    	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117) ~[spring-webmvc-5.3.22.jar:5.3.22]
    	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895) ~[spring-webmvc-5.3.22.jar:5.3.22]
    	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808) ~[spring-webmvc-5.3.22.jar:5.3.22]
    	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.3.22.jar:5.3.22]
    	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1070) ~[spring-webmvc-5.3.22.jar:5.3.22]
    	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963) ~[spring-webmvc-5.3.22.jar:5.3.22]
    	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.3.22.jar:5.3.22]
    	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909) ~[spring-webmvc-5.3.22.jar:5.3.22]
    	at javax.servlet.http.HttpServlet.service(HttpServlet.java:681) ~[tomcat-embed-core-9.0.65.jar:4.0.FR]
    	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.3.22.jar:5.3.22]
    	at javax.servlet.http.HttpServlet.service(HttpServlet.java:764) ~[tomcat-embed-core-9.0.65.jar:4.0.FR]
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.65.jar:9.0.65]
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.3.22.jar:5.3.22]
    	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.22.jar:5.3.22]
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.3.22.jar:5.3.22]
    	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.22.jar:5.3.22]
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.3.22.jar:5.3.22]
    	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.22.jar:5.3.22]
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:360) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:890) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1789) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
    	at java.base/java.lang.Thread.run(Thread.java:829) ~[na:na]
    

    Steps to reproduce the problem

    Please follow through the sample PL/SQL to reproduce the error.

    jOOQ Version

    3.17.3

    Database product and version

    Oracle19C Enterprise Edition

    Java Version

    JDK 11

    OS Version

    No response

    JDBC driver name and version (include name if unofficial driver)

    ojdbc8

    T: Enhancement C: Functionality C: DB: Oracle P: High R: Fixed E: Professional Edition E: Enterprise Edition 
    opened by satri14 49
  • Allow for DDLDatabase to connect to a testcontainers managed database

    Allow for DDLDatabase to connect to a testcontainers managed database

    Expected behavior and actual behavior:

    Currently one must keep a DB around for generating the jOOQ classes at build time. It would be great if jOOQ could dynamically fire up a container with the correct database (Oracle, PostgreSQL, MySQL, ...), migrate the database using Flyway (of course!) and use that the generate the classes, before finally discarding the DB once again.

    Always a clean DB, works in any environment where Docker is present without any need to preprovision anything.

    Note that it may be possible to leverage some of the work done by the testcontainers project for this.

    Versions:

    • jOOQ: 3.10 ;-)
    T: Enhancement C: Code Generation P: Medium R: Wontfix E: All Editions 
    opened by axelfontaine 45
  • jOOQ parser could be more flexible

    jOOQ parser could be more flexible

    Use case:

    I'm use the jOOQ parser as a general SQL parser, both for schema parsing and query parsing. I find the parser unsuitable for this use-case, or perhaps I'm missing something. For instance, parsing schema via Parser#parse(schema) return a Queries. Internally I see all the information I need e.g., a list of QueryTableImpl with field columnFields, however access to this information is private. I'd rather not resort to reflection. I can see the accept(ctx) method, where I could in theory visit each column field, but that is abusing the API, not to mention the inherent inefficiency.

    Also missing from the API is token information for declared elements. Many tools, for example, require the location of the declared table name as well as the column names. For instance, the name offsets enable my tool to navigate to/from SQL table & column names corresponding with Java class & property declarations within an IDE.

    Note the same requirements apply to Select queries e.g., need name offsets to each field in the select list.

    Possible solution you'd like to see:

    Provide methods for immutable access to the column fields. And retain token offsets or line/column information for table and column API classes.

    Please describe a possible solution you'd like to see in jOOQ, ideally with example code. Add the Interface:

    interface CreateTable {
      Iterable<TableField> getColumnFields();
      String getName();
    }
    

    Also add the method to QueryPart:.

    int getNameOffset();
    

    and/or:

    Iterable<Token> getTokens();
    

    Possible workarounds:

    Use reflection to get columnFields. No workaround for name offset.

    Versions:

    • jOOQ: 3.11.11
    T: Enhancement P: Medium R: Duplicate E: All Editions C: Parser 
    opened by rsmckinney 38
  • Add support for type DOMAINs

    Add support for type DOMAINs

    As documented here:

    http://www.postgresql.org/docs/current/static/sql-createdomain.html

    This is also part of the SQL 92 standard (chapter 11.21):

    http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

    Interesting features to be taken into consideration for domains:

    • Domains can restrict other domains (we'll need recursive querying to find the top-most non-domain base type!)
    • Domains can restrict (some) user-defined types, but apparently not recursively.

    Note, SQL Server 2016 calls these TYPE ALIAS: https://msdn.microsoft.com/en-us/library/ms190232.aspx

    Tasks

    • [x] Add org.jooq.Domain type supporting:
      • [x] TYPE
      • [x] DEFAULT
      • [x] CHECK constraints
      • [x] ~NOT NULL constraints~ (implemented separately: #10239)
      • [x] ~COLLATION~ (implemented separately: #10240)
    • [x] Add an improved DataType model
      • [x] ~Supporting a better data type registry (see also #650)~ (We don't seem to need this yet)
      • [x] Allowing for org.jooq.Name as identifiers (see also #10230)
    • [x] Add DSL API to construct domain references
    • [x] Add meta model for domains
    • [x] Support exporting DDL from Meta.ddl()
      • [x] For CatalogMetaImpl and SchemaMetaImpl
      • [x] For InformationSchemaMetaImpl (add to XSD)
      • [x] For Interpreter
      • [x] ~For MetaImpl~ (doesn't work through JDBC DatabaseMetaData API)
    • [x] Add code generator support for domains
      • [x] H2
      • [x] HSQLDB
      • [x] Firebird
      • [x] PostgreSQL
      • [x] SQL Server
      • [x] ~Support in arrays, udts, domains, procedures~: #10475
      • [x] Generate also Schema.getDomains()
    • [x] Add DDL support for domains
      • [x] CREATE
      • [x] ALTER
      • [x] DROP
    • [x] Add parser support
    • [x] Add interpreter support
      • [x] CREATE
      • [x] ALTER
      • [x] DROP
    • [x] Add diff support
    • [x] Add dialect support
      • [x] H2
      • [x] HSQLDB
      • [x] Firebird
      • [x] PostgreSQL
      • [x] SQL Server
    • [x] Documentation

    Known limitations (#10615):

    • Support generating domain references in:
      • Casts
      • DDL

    See also

    • #3382
    • #3486 (domains need to be considered in the code generator configuration)
    • https://msdn.microsoft.com/en-us/library/ms190232.aspx
    T: Enhancement C: Functionality P: Medium R: Fixed E: All Editions 
    opened by lukaseder 37
  • INSERT ... SET ... RETURNING returns null

    INSERT ... SET ... RETURNING returns null

    It appears that this doesn't work:

    TodosRecord persisted = jooq.insertInto(TODOS)
                    .set(TODOS.CREATION_TIME, currentTime)
                    .set(TODOS.DESCRIPTION, todo.getDescription())
                    .set(TODOS.ID, id)
                    .set(TODOS.MODIFICATION_TIME, currentTime)
                    .set(TODOS.TITLE, todo.getTitle())
                    .returning()
                    .fetchOne();
    
            //persisted is always null
    

    ... whereas this does:

    TodosRecord persisted = jooq.insertInto(TODOS)
                    .set(createRecord(todo))
                    .returning()
                    .fetchOne();
    
            //Persisted is found
    

    See also: https://groups.google.com/forum/#!topic/jooq-user/fOsaaZq44Ac

    T: Defect C: Functionality P: High R: Worksforme 
    opened by lukaseder 35
  • Generate convenience methods for common MULTISET and ROW nestings

    Generate convenience methods for common MULTISET and ROW nestings

    As discussed in https://github.com/jOOQ/jOOQ/issues/13063, jOOQ 3.17 will have additional convenience to build common MULTISET and ROW expressions based on path navigation, similar to the existing implicit join feature. #13063 remains a discussion issue, this issue here is a specific task list for a prototype, and then the final implementation.

    The current prototype will add:

    • [x] <O extends Record> CHILD.parentRow(Function<? super Parent, ? extends TableLike<O>>) produces a Row[N] which extends SelectField<R>
    • [x] <O extends Record> PARENT.childMultiset(Function<? super Parent, ? extends TableLike<O>>) produces a Field<Result<R>> (we might introduce a Multiset<R> subtype of Field<Result<R>> in the future, see https://github.com/jOOQ/jOOQ/issues/12031). This works for:
      • [x] One-to-many relationships (trivial)
      • [x] Many-to-many relationships. A heuristic is implemented where for a table T, all child tables' C's foreign keys are resolved to point to U, meaning T <-> U are likely to be in a many-to-many relationship

    Code generation

    3 new <generate/> flags are added to allow for opting out of the feature, to prevent unnecessary noise if the feature is not desired, as well as to work around code generation bugs. All flags default to true:

    • [ ] Generate code only if MULTISET emulations are available
    • [x] <existsConvenience/>? Could be useful as well, to be defined
    • [x] <rowConvenienceToOne/>
      • [x] A prerequisite is to fix this bug: https://github.com/jOOQ/jOOQ/issues/12930
    • [x] <multisetConvenienceOneToMany/>
    • [x] <multisetConvenienceManyToMany/>
      • [ ] Special case: There are UNIQUE constraints on the relationship table's FKs: https://github.com/jOOQ/jOOQ/issues/13074
      • [ ] Special case: There are multiple paths between two many-to-many connected tables
      • [ ] Exclude case: There are more than 2 FKs on the relationship table
      • [x] Exclude case: There isn't any key on the 2 FKs
      • [x] Exclude case: A FK column is nullable
      • [ ] An option could be added to exclude the relationship table from the one-to-many convenience

    All of these features must be made available to:

    • [x] JavaGenerator
    • [x] KotlinGenerator
    • [x] ScalaGenerator

    Generator Strategy

    Just like for implicit join paths, the naming depends on the foreign key name, but this time in the inverse direction:

    • [ ] We must be able to define names based on inverse keys (i.e. using the referencing table, not the referenced table)
      • [ ] In case of disambiguation, the foreign key name is still used
      • [ ] Users should be able to implement a custom naming strategy as well
    • [x] The general naming needs to be specified. The suggested default is to suffix the key name (or table name, if non-ambiguous) with Multiset or Row, but that might change.

    Prerequisites

    • [ ] https://github.com/jOOQ/jOOQ/issues/8012. This would lead to much more convenience in the lambda syntax
    • [ ] https://github.com/jOOQ/jOOQ/issues/12031. (The Multiset<R> type seems desirable)
    • [ ] https://github.com/jOOQ/jOOQ/issues/13065.
    • [ ] https://github.com/jOOQ/jOOQ/issues/12045. (Would be great!)
    • [ ] https://github.com/jOOQ/jOOQ/issues/12772. (We need QueryPart::$replace to traverse join trees to get this right)

    Related

    • https://github.com/jOOQ/jOOQ/issues/4727
    • https://github.com/jOOQ/jOOQ/issues/13066
    • https://github.com/jOOQ/jOOQ/issues/13067

    Tests and feature interactions

    • [x] Aliasing of tables
    • [x] Aliasing of the multiset expression
    • [x] Implicit joins from within the MULTISET subquery
    • [ ] Self joins for tree structures
    • [ ] Converters and bindings
    • [ ] Composite keys
    • [ ] Embeddable keys
    T: Enhancement C: Functionality C: Code Generation P: High R: Wontfix E: Professional Edition E: Enterprise Edition 
    opened by lukaseder 34
  • Add support for VIRTUAL and STORED client side computed columns

    Add support for VIRTUAL and STORED client side computed columns

    Purpose

    Many dialects have native support for server side computed columns using syntaxes such as GENERATED ALWAYS AS. These are useful for:

    • Replacing triggers to produce identity values / sequence generated values or UUID primary key values
    • Calculate some expression that can be used in an index. It's generally a cleaner approach than function based indexes, as the use site does not have to remember and repeat the expression in every query in order for the computed column's index to apply.

    If a dialect doesn't support these features, or in some cases where the feature is not good enough for various reasons (e.g. Oracle doesn't support materialised computed columns, which would be useful if the computation is CPU intensive), it would be nice to have this feature in jOOQ, client side.

    Comparison with RecordListener

    Users can already implement it using RecordListener, but this feature would affect also any explicitly constructed INSERT, UPDATE or MERGE statement, including parsed ones, if meta data lookup is applied.

    Unlike RecordListener, which has access to the entire Record being inserted, the computation expression cannot access any values from other columns, although accessing expressions based on other columns is possible.

    Tasks

    The following changes are needed:

    • [x] Additional meta information on the DataType meta model
      • [x] A new GenerationLocation enum with values CLIENT and SERVER (default)
      • [x] A new Generator<T> extends Function<GeneratorContext, Field<T>>, Serializable type
        • [x] A GeneratorContext <: Scope argument object
          • [x] Providing access to a Configuration
          • [x] Providing access to statement information (INSERT, UPDATE, SELECT)
          • [x] Providing access to the target Field<T> expression that is being generated
        • [x] Or better: Generator<R extends Record, T extends Table<R>, U> extends Function<T, Field<U>>, Serializable, such that lambdas can reference aliased tables?
          • [x] It doesn't seem to be necessary as a Generator can always access the aliased table via this (lambda) or MyTable.this (anonymous class). On the other hand, that would require a Generator to capture the instance, which can cause other issues.
      • [x] A DataType.generatedAlwaysAs(Generator<T>) overload, which works only with GenerationLocation.CLIENT
      • [x] ~A DataType.default(Generator<T>) overload, which works only with GenerationLocation.CLIENT~: Postponed: https://github.com/jOOQ/jOOQ/issues/13443
    • [x] Code generation support to produce synthetic GENERATED expressions
      • [x] A forcedType configuration element generator, which can reference a class or contain inline code
      • [x] It should be possible to have a forced type only with a generator. No name, no userType
      • [x] generator applies to
        • [x] TableField (first draft)
        • [x] ~IN parameters (maybe in a later release)~: Postponed https://github.com/jOOQ/jOOQ/issues/13444
        • [x] ~UDT attributes (maybe in a later release)~: Postponed https://github.com/jOOQ/jOOQ/issues/13444
      • [x] Tests in Java, Scala, Kotlin
    • [x] ~Settings to enable / disable the feature at runtime~ (this is quite some work, let's wait for a customer requesting this. The workarounds are simple. Just create a plain SQL column expression)
    • [x] Implementation logic
    • [x] Tests for VIRTUAL semantics:
      • [x] Simple query
        • [x] Projecting virtual columns
        • [x] Projecting expressions based on virtual columns
          • [x] Function calls
          • [x] Binary expressions
          • [x] Aggregate functions
          • [x] Window functions
        • [x] Projecting aliased virtual columns
        • [x] Projecting transitive computations (computed column depends on another computed column)
      • [x] Implicit join
        • [x] Computed implicit join referencing another computed column
      • [x] Multisets
        • [x] Uncorrelated
        • [x] Correlated
        • [x] Nested
        • [x] ~MultisetAgg~ (this doesn't really make much sense as a client side computed virtual column)
      • [x] Table aliases
        • [x] Especially when used with implicit joins
        • [x] These seem to work implicitly if the Generator captures a this reference of the table, which may be aliased already!
      • [x] RETURNING clauses
    • [x] Tests for STORED semantics:
      • [x] INSERT
        • [x] INSERT .. VALUES
          • [x] With alternative column order
        • [x] INSERT .. SELECT
        • [x] Multi row INSERT
        • [x] INSERT .. ON DUPLICATE KEY ..
          • [x] IGNORE
            • [x] Native implementation
            • [x] Merge emulation
          • [x] UPDATE
            • [x] Native implementation (we might be able to leverage a peculiar MySQL "feature" where SET clauses aren't evaluated atomically, see https://twitter.com/lukaseder/status/1507019871569125381)
              • [x] Reordering columns to 1) user SET clauses, 2) computed columns
            • [x] Merge emulation with SET ROW = ..
            • [x] Merge emulation
        • [x] INSERT .. ON CONFLICT DO ..
          • [x] NOTHING
            • [x] Native implementation
            • [x] Merge emulation
          • [x] UPDATE
            • [x] Native implementation (probably depends on https://github.com/jOOQ/jOOQ/issues/6769)
              • [x] SET ..
              • [x] SET ROW = ROW
              • [x] SET ROW = (SELECT)
            • [x] Merge emulation with SET ROW = ..
              • [x] SET ..
              • [x] SET ROW = ROW
              • [x] SET ROW = (SELECT)
            • [x] Merge emulation
        • [x] INSERT .. RETURNING
      • [x] UPDATE
        • [x] UPDATE .. SET ..
        • [x] UPDATE .. SET ROW = ROW
        • [x] UPDATE .. SET ROW = SELECT
        • [x] Computation column inclusion
          • [x] Trivial solution would re-compute all columns
        • [x] UPDATE .. RETURNING
      • [x] UpdatableRecord operations?
      • [x] Combine with converters / bindings
      • [x] The SQL transformation should have a simple and sophisticated version
        • [x] Sophisticated is everything we've discussed so far, and it can handle arbitrary expressions from the Generator
    • [x] ~Synthetic CREATE TABLE and ALTER TABLE DDL support for the interpreter~ (maybe later)

    Related work / follow up work:

    • https://github.com/jOOQ/jOOQ/issues/13411
    • https://github.com/jOOQ/jOOQ/issues/13418

    Caveats:

    • [x] MySQL UPDATE doesn't run the SET clause atomically, see https://twitter.com/lukaseder/status/1507019364800688134, https://stackoverflow.com/q/37649/521799. This has 2 implications:

      • [x] Computed columns must be calculated lexically after all user-provided SET clauses
    • [x] It would be very helpful if we could guarantee that each Generator is invoked exacly once per query rendering, in order to produce more predictable results, e.g. when a Generator produces a timestamp. More caveats and known issues here:

    • https://github.com/jOOQ/jOOQ/issues/13685

    Examples:

    Independent computed expressions

    With a hypothetical synthetic table definition like this:

    CREATE TABLE t (
      i INT,
      j INT CLIENT GENERATED ALWAYS AS 1,
      k INT CLIENT GENERATED BY DEFAULT AS 2
    );
    

    The following statements...

    INSERT INTO t (i, j) VALUES (0, 0);
    INSERT INTO t (i, j, k) VALUES (0, 0, 0);
    INSERT INTO t (i, j, k) SELECT 0, 0, 0;
    UPDATE t SET i = 0, j = 0, k = 0;
    

    ... would be transformed to these ones

    INSERT INTO t (i, j, k) VALUES (0, 1, 2);
    INSERT INTO t (i, j, k) VALUES (0, 1, 0);
    INSERT INTO t (i, j, k) SELECT i, 1, k FROM (SELECT 0, 0, 0) t (i, j, k);
    UPDATE t SET i = 0, j = 1, k = 0;
    

    Dependent computed expressions

    Just like server side computed columns, it should be possible to form expressions based on other columns, e.g. in the aforementioned hypothetical syntax:

    CREATE TABLE t (
      i INT,
      j INT CLIENT GENERATED ALWAYS AS i + 1,
      k INT CLIENT GENERATED BY DEFAULT AS i + 2
    );
    

    The following statements...

    INSERT INTO t (i, j) VALUES (0, 0);
    INSERT INTO t (i, j, k) VALUES (0, 0, 0);
    INSERT INTO t (i, j, k) SELECT 0, 0, 0;
    UPDATE t SET i = 0, j = 0, k = 0;
    

    ... would be transformed to these ones

    INSERT INTO t (i, j, k) SELECT i, i + 1, i + 2 FROM (VALUES (0)) t (i);
    INSERT INTO t (i, j, k) SELECT i, i + 1, k FROM (VALUES (0, 0)) t (i, k);
    INSERT INTO t (i, j, k) SELECT i, i + 1, k FROM (SELECT 0, 0, 0) t (i, j, k);
    UPDATE t 
    SET (i, j, k) = (SELECT i, i + 1, k FROM (SELECT 0, 0, 0) t (i, j, k));
    

    The UPDATE .. SET ROW = (SELECT ..) emulation will be needed for this task, to be investigated: https://github.com/jOOQ/jOOQ/issues/10523

    More tricky cases

    The UPDATE .. SET ROW = (SELECT ..) clause is being used to ensure atomic computation of all client side computed expressions. This means that existing SET ROW = (SELECT ..) clauses might be a bit more tricky to handle:

    Other

    There will obviously be edge cases which are not properly handled in the above examples yet.

    The following statements...

    UPDATE t SET (i, j, k) = (0, 0, 0);
    UPDATE t SET (i, j, k) = (SELECT 0, 0, 0);
    UPDATE t SET 
      (i, j) = (SELECT 0, 0),
      k = 0;
    

    ... would be transformed to these ones

    -- SET ROW = ROW will just first flatten the ROW and then perform the usual emulation
    UPDATE t SET (i, j, k) = (SELECT i, i + 1, k FROM (SELECT 0, 0, 0) t (i, j, k));
    
    -- SET ROW = SELECT looks the same as the previous one, if there's only a single assignment
    UPDATE t SET (i, j, k) = (SELECT i, i + 1, k FROM (SELECT 0, 0, 0) t (i, j, k));
    
    -- But once we support multiple ROW assignments, some additional trickery will be needed
    UPDATE t SET (i, j, k) = (
      SELECT i, i + 1, k 
      FROM 
        (SELECT 0, 0) t (i, j), -- ROW assignment
        (SELECT 0) u (k)        -- k assignment
    );
    

    See also: https://github.com/jOOQ/jOOQ/issues/13330

    GenerationOption

    By default, the GenerationOption.STORED flag is applied, meaning the value is generated upon INSERT, UPDATE, MERGE directly in relevant SQL statements. However, GenerationOption.VIRTUAL is also possible, in case of which the computation takes place in every SELECT statement, as soon as the column is included.

    • [x] Support STORED (when the forced type matches an actual column)
    • [x] Support VIRTUAL (when the forced type matches a synthetic column). Prerequisites:
      • [x] https://github.com/jOOQ/jOOQ/issues/13434
    T: Enhancement C: Functionality P: Medium R: Fixed E: Professional Edition E: Enterprise Edition 
    opened by lukaseder 34
  • Generate convenience methods for common MULTISET and ROW nestings - Discussion

    Generate convenience methods for common MULTISET and ROW nestings - Discussion

    Use case:

    Currently it’s possible to use fields from related tables using implicit joins in the many to one-direction. Thus the joined table has zero or one rows for each row in the referring table.

    With the new multiset-functionality it should be possible to give similar functionality in the one to many-direction.

    Possible solution you'd like to see:

    dsl.select(
        ALBUM.TITLE,
        ALBUM.tracks(
            TRACK.NUMBER,
            TRACK.TITLE,
            TRACK.LENGTH
        )
    ).from(ALBUM)
    
    T: Enhancement C: Functionality P: Medium R: Duplicate E: All Editions 
    opened by alf 33
  • Modularise jOOQ

    Modularise jOOQ

    We're already cross-releasing JDK 6/7 (commercial only) and JDK 8 (commercial and OSS) builds. In order to modularise jOOQ, we need to also add a new build for JDK 9.

    Quite possibly, this will lead to some incompatible package renames, as we've re-used names like org.jooq.util in several modules in the past. These are:

    • #7419

    Deployments

    • The modular jOOQ will support JDK 11 (commercial, see #7789)
    • The non-modular jOOQ will support JDK 6/7 (commerical only), 8, 11 (commercial and OSS)

    Regressions detected so far:

    • [x] #10143 java.nio.file.InvalidPathException: Illegal char <*> when using FilePattern on module path
    • [x] #10144 Runtime compilation error when compiling enum types on module path
    • [x] #10145: NoClassDefFoundError when trying to load JAXB from a modularised jOOQ

    Here's an alternative solution that might help avoiding cross releasing: https://maven.apache.org/plugins/maven-compiler-plugin/examples/module-info.html

    T: Enhancement C: Build P: Medium R: Fixed T: Incompatible change E: Professional Edition E: Enterprise Edition 
    opened by lukaseder 33
  • ORA-04043 on INSERT when using qualified, quoted table name and fetching generated keys

    ORA-04043 on INSERT when using qualified, quoted table name and fetching generated keys

    Expected behavior and actual behavior:

    We are planning to migrate our Oracle 11.2 database to Oracle 12.2. We are using jooq 3.10.4, ojdbc6 - 11.2.0.4.0, java 1.8.0_144. First step we want to do is to switch to ojdbc8 for 12.02 Oracle db. We are having problems with INSERT query. UPDATE and SELECT are working fine.

     `Caused by: org.jooq.exception.DataAccessException: SQL [insert into "A"."AUDIT_TEAM" ("AUDIT_KOPF_LZN", "MA_LZN", "ZH_LZN", "TEXT", "AUDIT_ROLE_LZN", "U_MA_LZN", "U_DAT", "INFO") values (?, ?, ?, ?, ?, ?, cast(? as date), ?)]; ORA-04043: Objekt "A" ist nicht vorhanden.`
    

    Java code : AuditKopfRecord record = this.dsl.newRecord(AUDIT_KOPF); record.from(item); int rowsAffected= record.insert();

    We have 50+ schemas in database, so we can't use settings.renderSchema = false .

    Versions:

    • jOOQ: 3.10.4, also tried with newest 3.11.5
    • Java: 1.8.0_144
    • Database (include vendor): Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
    • OS: Unix, Windows
    • JDBC Driver (include name if inofficial driver): ojdbc8 - 12.2.0.1
    T: Defect C: Functionality C: DB: Oracle P: High R: Fixed E: Professional Edition E: Enterprise Edition 
    opened by skrgahr 32
  • UDT data types are unqualified in PostgreSQL, when there is more than one UDT data type per schema

    UDT data types are unqualified in PostgreSQL, when there is more than one UDT data type per schema

    Expected behavior and actual behavior:

    When inserting an array of UDT in postgres, the generated query adds a cast that does not use the full name of the type, which results in failure when they type does not belong to the default schema.

    Steps to reproduce the problem:

    Create a separate schema

    define a type there

    use your new type in an array column.

    generate an insert to that column.

    Versions:

    • jOOQ: 3.10.6
    • Java: 8 / Kotlin 1.2.41
    • Database (include vendor):PostgreSQL
    • OS: GNU/ Linux 4.15.18
    • JDBC Driver (include name if inofficial driver): official pgjdbc
    T: Defect C: Functionality C: DB: PostgreSQL P: Medium R: Fixed E: All Editions 
    opened by desiderantes 32
  • Add support for ALTER PROCEDURE, ALTER FUNCTION, ALTER TRIGGER

    Add support for ALTER PROCEDURE, ALTER FUNCTION, ALTER TRIGGER

    SQL Server supports ALTER statements for procedural code:

    This can be emulated using:

    -- SQL Server
    ALTER <object> ...
    
    -- Other dialects
    DROP <object> ...
    CREATE <object> ...
    

    Or alternatively:

    IF not_exists(<object>) THEN
      RAISE ...
    END IF;
    
    CREATE OR REPLACE <object> ...
    
    T: Enhancement C: Functionality P: Medium E: Professional Edition E: Enterprise Edition 
    opened by lukaseder 0
  • Better parser support for MySQL boolean type coercion

    Better parser support for MySQL boolean type coercion

    MySQL doesn't really have a boolean type. It's just a synonym for INTEGER. It implements C style boolean logic, such as:

    select 1 and 0
    

    This is translated by jOOQ to:

    select (1 <> 0 and 0 <> 0)
    

    It would be better to recognise when a numeric value is really a BOOLEAN and translate it as such, at least in cases where this is non-ambiguous, i.e. the above should translate to:

    select (true and false)
    

    This translation isn't always feasible, e.g. this valid MySQL query:

    select a and b
    from (select 1 a, 0 b) as t
    

    Is currently being translated to this invalid query in jOOQ, if we don't activate meta lookups:

    select (a and b)
    from (
      select 1 a, 0 b
    ) t
    

    If we do activate meta lookups, then an error is raised:

    Boolean field expected: [1:14] select a and [*]b
    from (select 1 a, 0 b) as t
    
    T: Enhancement P: Medium E: All Editions C: Parser 
    opened by lukaseder 0
  • Translator should add some hints to

    Translator should add some hints to "unknown function" error message

    The translator (https://www.jooq.org/translate) should add some hints whenever the "unknown function" error message appears:

    • There's a feature toggle to turn off this error and ignore unknown functions
    • The programmatic parser API allows for manual translation of such functions via parser listener: https://www.jooq.org/doc/latest/manual/sql-building/sql-parser/sql-parser-listener/
    T: Enhancement P: Medium E: All Editions C: Translator 
    opened by lukaseder 0
  • Support parsing MySQL backslash escaped string literals

    Support parsing MySQL backslash escaped string literals

    In MySQL, depending on the value of NO_BACKSLASH_ESCAPES, this is valid SQL:

    select 'a\'b'
    

    Producing:

    |a'b|
    |---|
    |a'b|
    

    The SQL generation already has a Settings.backslashEscaping flag, which applies to plain SQL templating and inline string literal generation. Now, we need to apply this flag also to the parser.

    T: Enhancement P: Medium E: All Editions C: Parser C: Translator 
    opened by lukaseder 0
  • UnsupportedOperationException when selecting row with YearToSecond

    UnsupportedOperationException when selecting row with YearToSecond

    Expected behavior

    Given the generated table class

    open class TestChild {
      ///// snip
        val TIME: TableField<TestChildRecord, YearToSecond?> = createField(DSL.name("time"), SQLDataType.INTERVAL, this, "")
     //// snip
    }
    

    executing the query

    ctx().select(row(TEST_CHILD.TIME))
                .from(TEST_CHILD)
                .fetch()
    

    I would expect to get a result Result<Record1<Record1<YearToSecond?>!>!>

    Actual behavior

    jOOQ throws the following exception, similar to #13611

    org.jooq.exception.DataAccessException: SQL [select row ("mcve"."test_child"."time") as "nested" from "mcve"."test_child"]; Error while reading field: row ("mcve"."test_child"."time"), at JDBC index: 1
    	at org.jooq_3.17.6.POSTGRES.debug(Unknown Source)
    	at org.jooq_3.17.6.POSTGRES.debug(Unknown Source)
    	at org.jooq.impl.Tools.translate(Tools.java:3313)
    	at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:678)
    	at org.jooq.impl.CursorImpl$CursorIterator.fetchNext(CursorImpl.java:1464)
    	at org.jooq.impl.CursorImpl$CursorIterator.hasNext(CursorImpl.java:1425)
    	at org.jooq.impl.CursorImpl.fetchNext(CursorImpl.java:238)
    	at org.jooq.impl.AbstractCursor.fetch(AbstractCursor.java:177)
    	at org.jooq.impl.AbstractCursor.fetch(AbstractCursor.java:88)
    	at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:265)
    	at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:341)
    	at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:290)
    	at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2837)
    	at org.jooq.mcve.test.kotlin.KotlinTest.mcveTestMinimal(KotlinTest.kt:55)
    	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
    	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
    	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    	at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
    	at org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100)
    	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
    	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103)
    	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63)
    	at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
    	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
    	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
    	at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
    	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
    	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
    	at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
    	at org.apache.maven.surefire.junit4.JUnit4Provider.execute(JUnit4Provider.java:252)
    	at org.apache.maven.surefire.junit4.JUnit4Provider.executeTestSet(JUnit4Provider.java:141)
    	at org.apache.maven.surefire.junit4.JUnit4Provider.invoke(JUnit4Provider.java:112)
    	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    	at org.apache.maven.surefire.util.ReflectionUtils.invokeMethodWithArray(ReflectionUtils.java:189)
    	at org.apache.maven.surefire.booter.ProviderFactory$ProviderProxy.invoke(ProviderFactory.java:165)
    	at org.apache.maven.surefire.booter.ProviderFactory.invokeProvider(ProviderFactory.java:85)
    	at org.apache.maven.surefire.booter.ForkedBooter.runSuitesInProcess(ForkedBooter.java:115)
    	at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:75)
    Caused by: java.sql.SQLException: Error while reading field: row ("mcve"."test_child"."time"), at JDBC index: 1
    	at org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.setValue(CursorImpl.java:1590)
    	at org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.apply(CursorImpl.java:1525)
    	at org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.apply(CursorImpl.java:1484)
    	at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:144)
    	at org.jooq.impl.CursorImpl$CursorIterator.fetchNext(CursorImpl.java:1449)
    	... 43 more
    Caused by: java.lang.UnsupportedOperationException: Class class org.jooq.types.YearToSecond is not supported
    	at org.jooq.impl.DefaultBinding$DefaultRecordBinding.pgFromString(DefaultBinding.java:3955)
    	at org.jooq.impl.DefaultBinding$DefaultRecordBinding.pgSetValue(DefaultBinding.java:4002)
    	at org.jooq.impl.DefaultBinding$DefaultRecordBinding.lambda$pgNewRecord$13(DefaultBinding.java:3994)
    	at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:144)
    	at org.jooq.impl.DefaultBinding$DefaultRecordBinding.pgNewRecord(DefaultBinding.java:3990)
    	at org.jooq.impl.DefaultBinding$DefaultRecordBinding.get0(DefaultBinding.java:3780)
    	at org.jooq.impl.DefaultBinding$DefaultRecordBinding.get0(DefaultBinding.java:3700)
    	at org.jooq.impl.DefaultBinding$InternalBinding.get(DefaultBinding.java:1024)
    	at org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.setValue(CursorImpl.java:1580)
    	... 47 more
    
    

    Steps to reproduce the problem

    https://github.com/bertwin/jOOQ-mcve

    jOOQ Version

    3.17.6

    Database product and version

    PostgreSQL 14.5 (Debian 14.5-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

    Java Version

    openjdk version "17.0.5" 2022-10-18

    OS Version

    Linux 6.0.15-300.fc37.x86_64 x86_64

    JDBC driver name and version (include name if unofficial driver)

    org.postgresql:postgresql:42.3.2

    T: Defect 
    opened by bertwin 0
Owner
jOOQ Object Oriented Querying
jOOQ Object Oriented Querying
🚀flink-sql-submit is a custom SQL submission client

??flink-sql-submit is a custom SQL submission client This is a customizable extension of the client, unlike flink's official default client.

ccinn 3 Mar 28, 2022
SceneView is a 3D/AR Android View with ARCore and Google Filament. This is the newest way to make your Android 3D/AR app.

SceneView is a 3D/AR Android View with ARCore and Google Filament This is Sceneform replacement Features Use SceneView for 3D only or ArSceneView for

SceneView Open Community 235 Jan 4, 2023
requery - modern SQL based query & persistence for Java / Kotlin / Android

A light but powerful object mapping and SQL generator for Java/Kotlin/Android with RxJava and Java 8 support. Easily map to or create databases, perfo

requery 3.1k Jan 5, 2023
jdbi is designed to provide convenient tabular data access in Java; including templated SQL, parameterized and strongly typed queries, and Streams integration

The Jdbi library provides convenient, idiomatic access to relational databases in Java. Jdbi is built on top of JDBC. If your database has a JDBC driv

null 1.7k Dec 27, 2022
Java code generator for calling PL/SQL.

OBridge OBridge provides a simple Java source code generator for calling Oracle PL/SQL package procedures. Supported input, output parameters and retu

Ferenc Karsany 21 Oct 7, 2022
A Java library to query pictures with SQL-like language

PicSQL A Java library to query pictures with SQL-like language. Features : Select and manipulate pixels of pictures in your disk with SQL-like dialect

Olivier Cavadenti 16 Dec 25, 2022
A Java library to query pictures with SQL-like language.

PicSQL A Java library to query pictures with SQL-like language. Features : Select and manipulate pixels of pictures in your disk with SQL-like dialect

null 16 Dec 25, 2022
Official repository of Trino, the distributed SQL query engine for big data, formerly known as PrestoSQL (https://trino.io)

Trino is a fast distributed SQL query engine for big data analytics. See the User Manual for deployment instructions and end user documentation. Devel

Trino 6.9k Dec 31, 2022
The official home of the Presto distributed SQL query engine for big data

Presto Presto is a distributed SQL query engine for big data. See the User Manual for deployment instructions and end user documentation. Requirements

Presto 14.3k Dec 30, 2022
CrateDB is a distributed SQL database that makes it simple to store and analyze massive amounts of machine data in real-time.

About CrateDB is a distributed SQL database that makes it simple to store and analyze massive amounts of machine data in real-time. CrateDB offers the

Crate.io 3.6k Jan 2, 2023
Persistent priority queue over sql

queue-over-sql This projects implement a persistent priority queue (or a worker queue) (like SQS, RabbitMQ and others) over sql. Why? There are some c

Shimon Magal 13 Aug 15, 2022
SQL tasarım komutları ve Backend yazıldı. Projeye yıldız Vermeyi Unutmayın 🚀 Teşekkürler! ❤️

HumanResourcesManagementSystem-HRMS SQL tasarım komutları ve Backend yazıldı. Projeye yıldız Vermeyi Unutmayın ?? Teşekkürler! ❤️ insan kaynakları yön

samet akca 7 Nov 6, 2022
The public release repository for SUSTech SQL (CS307) course project 2.

CS307 Spring 2021 Database Project 2 1. Source code Download link: For java: https://github.com/NewbieOrange/SUSTech-SQL-Project2-Public For python: h

null 16 Dec 26, 2022
SQL made uagliò.

GomorraSQL is an easy and straightforward interpreted SQL dialect that allows you to write simpler and more understandable queries in Neapolitan Langu

Donato Rimenti 1.1k Dec 22, 2022
Multi-DBMS SQL Benchmarking Framework via JDBC

BenchBase BenchBase (formerly OLTPBench) is a Multi-DBMS SQL Benchmarking Framework via JDBC. Table of Contents Quickstart Description Usage Guide Con

CMU Database Group 213 Dec 29, 2022
HasorDB is a Full-featured database access tool, Providing object mapping,Richer type handling than Mybatis, Dynamic SQL

HasorDB is a Full-featured database access tool, Providing object mapping,Richer type handling than Mybatis, Dynamic SQL, stored procedures, more dialect 20+, nested transactions, multiple data sources, conditional constructors, INSERT strategies, multiple statements/multiple results. And compatible with Spring and MyBatis usage.

赵永春 17 Oct 27, 2022
An open source SQL database designed to process time series data, faster

English | 简体中文 | العربية QuestDB QuestDB is a high-performance, open-source SQL database for applications in financial services, IoT, machine learning

QuestDB 9.9k Jan 1, 2023