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
  • 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
  • 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 43
  • 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
  • Avoid duplicate bean names in Spring annotations

    Avoid duplicate bean names in Spring annotations

    Use case

    Generated DAOs with Spring annotations should work if there are multiple tables with the same name in different schemas.

    Currently, if you have Spring annotations enabled, jOOQ puts @Repository on the DAO classes and Spring uses the unqualified class name as the default bean name. When there are two DAO classes with the same names in different packages, Spring treats the collision as an error.

    Possible solution

    Explicitly set the bean name in the @Repository annotation, and generate a name that avoids these collisions (e.g., prefixing with the catalog/schema names if the table isn't in the default schema).

    Possible workarounds

    I was already subclassing KotlinGenerator for other reasons, so I turned off the Spring annotation option in the jOOQ codegen configuration and added this to my subclass:

      override fun printClassAnnotations(
          out: JavaWriter,
          definition: Definition,
          mode: GeneratorStrategy.Mode
      ) {
        super.printClassAnnotations(out, definition, mode)
    
        if (definition is TableDefinition && mode == GeneratorStrategy.Mode.DAO) {
          // Can't call the jOOQ strategy method to generate the DAO class name because it's package-
          // private, so generate it from definition.table.name (which already includes the schema name)
          val beanName =
              definition.table.name.replace(Regex("[._](.)")) { it.groupValues[1].capitalize() } + "Dao"
          out.println("@%s(\"%s\")", out.ref("org.springframework.stereotype.Repository"), beanName)
        }
      }
    

    This isn't robust in the face of table names with special characters, and it adds the schema prefix even for the default schema, but for my code base it does the trick.

    jOOQ Version

    3.17.4

    Database product and version

    PostgreSQL 14.5 (Homebrew)

    Java Version

    openjdk version "17.0.4" 2022-07-19 LTS

    OS Version

    OS X 12.6

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

    org.postgresql:postgresql:42.5.0

    T: Enhancement C: Code Generation P: Low C: Integration: Spring E: All Editions 
    opened by sgrimm 2
  • Allow dynamic registration of `ExecuteListener`

    Allow dynamic registration of `ExecuteListener`

    Use case

    I'm hoping for a way to enable an ExecuteListener across all Configuration instances, dynamically.

    Some context:

    We create an instance of DSLContext for each one of our repositories. This is constructed in the repository constructor - the constructor only exposes a DataSource as a dependency:

    public PostgresRepository(DataSource ds) {
      this.dsl = DSL.using(ds, POSTGRES);
    }
    

    What I'd like to do is set up an ExecuteListener that identifies repeated statements. This would be similar to this blog post, but with integration into dsl.select().

    Obviously, and as per the blog, I don't want this running in production code, only in the tests. I'd also like this to be enabled without developer involvement - an author would not be able to forget to turn on the detection for their new repository/test.

    We've had a fair few instances of N+1 queries. While we try to catch these in review, a hard block or even just automated feedback would be awesome.

    Possible solution

    Perhaps the ListenerProviders could be implemented using Java's ServiceLoader.

    I took the liberty of creating an example to help illustrate the desire. It's built for DiagnosticsListener, but the idea could be applied to any Listener.

    Possible workarounds

    An obvious workaround would be to create a single jOOQ Configuration to be used by all repositories. We use a DI framework, so that's achievable. The drawback to this is that we don't use DI in our repository integration tests - we just construct the repository the old fashioned way. As said above, this could lead to test authors forgetting to set up the Configuration correctly. There are mitigations to this, of course - factory methods, etc - but it isn't fool proof.

    Also open to any alternatives I've not considered/am unaware of!

    jOOQ Version

    jOOQ Community 3.14.16

    Database product and version

    PostgreSQL 14.3

    Java Version

    openjdk 17

    OS Version

    Various Windows/Mac/Linux

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

    org.postgresql:postgresql:42.3.7

    T: Enhancement C: Functionality P: Medium E: All Editions 
    opened by scottsteen 1
  • Metadata API not returning correct type for enum columns

    Metadata API not returning correct type for enum columns

    Expected behavior

    The metadata API correctly reports the SQL type of all columns.

    Actual behavior

    For any columns that have an enum type, the metadata API reports their type as Object and their dataType as "other".

    Steps to reproduce the problem

    Have a schema defined as:

    CREATE TYPE public.my_enum AS ENUM (
        'foo',
        'bar',
        'baz'
    );
    
    CREATE TABLE public.my_table (
        id bigint NOT NULL,
        created_at timestamp with time zone DEFAULT now() NOT NULL,
        name text,
        my_enum_column public.my_enum NOT NULL,
    );
    

    The following test passes:

    // this is Kotlin, using kotest, but hopefully pretty easy to decipher
    test("something fishy going on here") {
        val jooq = DSL.using(myDataSource, SQLDialect.POSTGRES)
        val myTable = jooq.meta().tables.find { it.name == "my_table" }!!
    
        // This looks right...
        val createdAt = myTable.field("created_at")!!
        createdAt.dataType.nullability() shouldBe Nullability.NOT_NULL
        createdAt.dataType.typeName shouldBe "timestamp with time zone"
    
        // ...but none of this seems right
        val myEnumField = myTable.field("my_enum_column")!!
        myEnumField.dataType.typeName shouldBe "other" // ???
        myEnumField.dataType.castTypeName shouldBe "other" // ???
        myEnumField.type shouldBe Any::class.java // ???
    }
    

    For any enum column, the type is Object (Any in Kotlin), and the dataType.typeName is "other". For non-enum columns, dataType.typeName gives me the correct SQL for the type.

    I'm also using the jOOQ code generator, and it generates the correct types for enum columns. That is, it creates an enum class and uses that as the type for the corresponding fields. The generated code for this field looks something like (reformatted to avoid long lines):

    public final TableField<MyTableRecord, MyEnum> MY_ENUM_COLUMN =
        createField(
            DSL.name("my_enum_column"),
            SQLDataType.VARCHAR
                .nullable(false)
                .asEnumDataType(com.example.schema.enums.MyEnum.class),
            this,
            ""
        )
    

    So it appears that jOOQ's code generator has the type information.

    Note

    I'd previously tried this with org.jooq:jooq:3.14.11, and it also has problems with nullability being reported incorrectly for enum columns (despite the code generator getting it right). Upgrading to org.jooq:jooq:3.16.10 fixes the nullability problem, but not the type/dataType problem: the datatype is still "other", and the type is still Object.

    Upgrading to org.jooq:jooq:3.17.4 yields the same behavior as org.jooq:jooq:3.16.10.

    See also this Stack Overflow question.

    jOOQ Version

    org.jooq:jooq:3.14.11, org.jooq:jooq:3.16.10, org.jooq:jooq:3.17.4

    Database product and version

    postgres:11-alpine

    Java Version

    openjdk 11.0.16 2022-07-19, openjdk 17.0.4 2022-07-19

    OS Version

    Ubuntu Linux 20.04

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

    org.postgresql:postgresql:42.5.0

    T: Defect 
    opened by xenomachina 0
  • Plain SQL queries should read java.sql.Struct values into org.jooq.Record

    Plain SQL queries should read java.sql.Struct values into org.jooq.Record

    There's a test that currently fails in the Oracle test suit where we fetch a ResultSet that has been retrieved from a JDBC query and assert equality with a plain SQL query.

    Both results contain an oracle.sql.STRUCT reference, which can't be compared because it doesn't implement equals() and hashCode(), nor toString() for that matter:

    java.lang.AssertionError: expected:<+----+----------+---------+-------------+-------------+-------------------------+
    |  ID|FIRST_NAME|LAST_NAME|DATE_OF_BIRTH|YEAR_OF_BIRTH|ADDRESS                  |
    +----+----------+---------+-------------+-------------+-------------------------+
    |   1|George    |Orwell   |1903-06-25   |         1903|[email protected]|
    |   2|Paulo     |Coelho   |1947-08-24   |         1947|[email protected]|
    +----+----------+---------+-------------+-------------+-------------------------+
    > but was:<+----+----------+---------+-------------+-------------+--------------------------+
    |  ID|FIRST_NAME|LAST_NAME|DATE_OF_BIRTH|YEAR_OF_BIRTH|ADDRESS                   |
    +----+----------+---------+-------------+-------------+--------------------------+
    |   1|George    |Orwell   |1903-06-25   |         1903|[email protected]|
    |   2|Paulo     |Coelho   |1947-08-24   |         1947|[email protected]|
    +----+----------+---------+-------------+-------------+--------------------------+
    >
    	at org.junit.Assert.fail(Assert.java:89)
    	at org.junit.Assert.failNotEquals(Assert.java:835)
    	at org.junit.Assert.assertEquals(Assert.java:120)
    	at org.junit.Assert.assertEquals(Assert.java:146)
    	at org.jooq.test.util.BaseTest.assertEquals(BaseTest.java:295)
    	at org.jooq.test.all.testcases.FetchTests.testFetchResultSet(FetchTests.java:1736)
    	at org.jooq.test.jOOQAbstractTest.testFetchResultSet(jOOQAbstractTest.java:2579)
    	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.rules.TestWatcher$1.evaluate(TestWatcher.java:61)
    	at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:61)
    	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.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.ParentRunner.run(ParentRunner.java:413)
    	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:93)
    	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:40)
    	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:529)
    	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:756)
    	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:452)
    	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:210)
    

    By default, jOOQ fetches all JDBC types into an equivalent jOOQ type, without any JDBC dependency, or open Connection, etc. The STRUCT type as such isn't really very useful. It would be better to have a Record type available (or UDTRecord, but those can't be generated on the fly at runtime)

    T: Enhancement C: Functionality P: Medium T: Incompatible change E: All Editions 
    opened by lukaseder 3
  • Unstable overload index in PostgreSQL databases, when generating from different instances

    Unstable overload index in PostgreSQL databases, when generating from different instances

    https://github.com/jOOQ/jOOQ/issues/8589 was an incomplete fix. There are still unstable overload indexes being generated, depending on what database instance is being used for code generation.

    The overload index is calculated based on the SPECIFIC_NAME, which is instance dependent (e.g. two migrations on two instances can produce different SPECIFIC_NAME values). The expression is:

    when(
        count().over(partitionBy(r1.ROUTINE_SCHEMA, r1.ROUTINE_NAME)).gt(one()),
        rowNumber().over(partitionBy(r1.ROUTINE_SCHEMA, r1.ROUTINE_NAME).orderBy(
    
            // [#9754] To stabilise overload calculation, we use the type signature
            // replace(field("pg_get_function_arguments({0})", VARCHAR, PG_PROC.OID), inline('"'), inline("")),
            r1.SPECIFIC_NAME
        ))
    ).as("overload"),
    

    It seems that pg_get_function_arguments() approach was never actually implemented

    T: Defect C: Code Generation C: DB: PostgreSQL P: High T: Incompatible change E: All Editions 
    opened by lukaseder 3
  • ORA-01704 when generating large BLOB inline values in Oracle

    ORA-01704 when generating large BLOB inline values in Oracle

    Following up on https://github.com/jOOQ/jOOQ/issues/6516, which is about CLOB types only.

    The current inline serialisation of large BLOB values in Oracle doesn't consider the VARCHAR2(4000) size limit (still the default in many Oracle 12c installations). We should generate a large CLOB expression, e.g. using:

    SELECT to_clob('first-part ... after N characters, split') 
        || to_clob('... and concatenate the next part ...')
    

    We can't use exactly 4000 characters, because that limit counts the number of bytes. So let's count those in UTF-8, and add a heuristic maximum of 2000 characters?

    While serialising clobs is straightforward (see above), serialising blobs is trickier. We'll need to generate an anonymous block with a utility function:

    DECLARE
      FUNCTION clob_to_blob (c CLOB) RETURN BLOB IS
        v_result BLOB;
        o1 INTEGER := 1;
        o2 INTEGER := 1;
        c INTEGER := 0;
        w INTEGER := 0;
      BEGIN
        dbms_lob.createtemporary(v_result, true);
        dbms_lob.converttoblob(v_result, c, length(c), o1, o2, 0, c, w);
        RETURN v_result;
      END;
    BEGIN
      INSERT INTO my_table (my_clob, my_blob) 
      VALUES (to_clob('...') || to_clob('...'), clob_to_blob(to_clob('...') || to_clob('...'));
    
      ? := SQL%ROWCOUNT;
    END;
    

    Or, in the case of a ResultQuery, we'd fetch the result using DBMS_SQL.RETURN_RESULT

    Instead of just blindly generating anonymous PL/SQL blocks, we should start thinking about a common, reusable PL/SQL model, see also #6475

    T: Defect C: Functionality C: DB: Oracle P: Medium E: Professional Edition E: Enterprise Edition 
    opened by lukaseder 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
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 Sep 25, 2022
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 Oct 5, 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 20 Aug 30, 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 13 Jul 20, 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 13 Jul 20, 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 175 Sep 23, 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.2k Oct 2, 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 14k Sep 27, 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.5k Oct 5, 2022
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 6 Aug 21, 2021
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 18 Aug 9, 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 Sep 30, 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 186 Sep 20, 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.

赵永春 16 Sep 2, 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.2k Sep 30, 2022