{"id":8722,"date":"2018-08-05T22:34:07","date_gmt":"2018-08-05T20:34:07","guid":{"rendered":"https:\/\/www.salvis.com\/blog\/?p=8722"},"modified":"2023-11-08T03:11:28","modified_gmt":"2023-11-08T02:11:28","slug":"view-api-jooq-application","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2018\/08\/05\/view-api-jooq-application\/","title":{"rendered":"View-API for JOOQ Application"},"content":{"rendered":"\n

In this blog post, I show how to build a read-only view-API for Oracle’s\u00a0HR sample schema<\/a>. And I will use this view-API in a JOOQ<\/a> application.\u00a0This application will fully comply with the\u00a0Pink Database Paradigm (PinkDB)<\/a>. This means the application executes set-based SQL and retrieves data with as few network roundtrips as possible.<\/p>\n\n\n\n

The following topics are covered:<\/p>\n\n\n\n

    \n
  1. Install Sample Schemas<\/a><\/li>\n\n\n\n
  2. Build the View-API<\/a><\/li>\n\n\n\n
  3. Create the View-API Database Role<\/a><\/li>\n\n\n\n
  4. Create the Connect User<\/a><\/li>\n\n\n\n
  5. Install JOOQ<\/a><\/li>\n\n\n\n
  6. Teach JOOQ the View-API<\/a><\/li>\n\n\n\n
  7. Run a Simple Query<\/a><\/li>\n\n\n\n
  8. Using Joins & Aggregations<\/a><\/li>\n\n\n\n
  9. Using Bind Variables<\/a><\/li>\n\n\n\n
  10. Run a Top N Query<\/a><\/li>\n\n\n\n
  11. Using Row Pattern Matching<\/a><\/li>\n\n\n\n
  12. Conclusion<\/a><\/li>\n<\/ol>\n\n\n\n

    This is not meant to be a tutorial. However, you should be able to build the application based on the information in this blog post and the referenced links.<\/p>\n\n\n\n

    It’s the first time I’ve done anything with JOOQ. I read about it and understood the high-level concepts, but I have never used JOOQ before. This is one reason why this blog post became a bit more verbose. I hope it is helpful.<\/p>\n\n\n\n

    <\/a>1. Install Sample Schemas<\/h2>\n\n\n\n

    See the Oracle documentation<\/a>.<\/p>\n\n\n\n

    \n

    Starting with Oracle Database 12c Release 2, the latest version of the sample schema scripts are available on GitHub at https:\/\/github.com\/oracle\/db-sample-schemas\/releases\/latest<\/a>. (…)<\/p>\n<\/blockquote>\n\n\n\n

    <\/a>2. Build the View-API<\/h2>\n\n\n\n

    1:1 Views<\/h3>\n\n\n\n

    When building a view API I start with a 1:1 mapping to the table. There are usually some discussions on different topics.<\/p>\n\n\n\n

    Expose Surrogate Keys<\/h3>\n\n\n\n

    One topic is\u00a0to include surrogate keys or just business keys in the view API. Nowadays, I tend to expose surrogate keys. The main reason is to avoid joins when business key columns are stored in related (parent) tables. Forcing the application to join views via such business key columns is a bad idea from a performance point of view.<\/p>\n\n\n\n

    Convenience Views – Not a Replacement for 1:1 Views<\/h3>\n\n\n\n

    Another topic is the simplification of the model. For example, provide data from various tables to simplify the usage. Simplification means here sparing the consuming application to use joins. This is sometimes a good idea, but it does not replace the 1:1 views, which are the key to the optimal path to the data. I know about join elimination, but in reality, they often do not work. One reason is that unnecessary columns are queried as well. Another problem is, to force the view to apply selective predicates as early as possible. Sometimes it is necessary to introduce an application context, just for that purpose, making the usage of the view not that simple anymore.<\/p>\n\n\n\n

    Value of a 1:1 View-API<\/h3>\n\n\n\n

    The logical next question is this: Why do we need a view layer when it just provides data the same way as the underlying tables? That’s an excellent question. I strongly recommend introducing a layer only, when it provides more value than the additional effort. So, what is the value of a 1:1 view layer? Most products evolve during their life cycle. Hence, the data model will most probably change as well. When we have a view layer, we have the option to change the physical data model and keep the existing view layer the same for the consuming applications. From that point on, the views are not a 1:1 representation of tables, at least not in every case.<\/p>\n\n\n\n

    As long as we keep the interface to the application unchanged, we do not have to coordinate changes with the consuming applications. This gives us effective room for refactoring and simplifies going-live scenarios of new releases. Providing additional data and functionality is usually not a problem. However, adapting applications to new interfaces needs time. A view-API, especially if it implements some versioning concept, provides an excellent value in this area.<\/p>\n\n\n\n

    View-API of HR Schema<\/h3>\n\n\n\n

    I’ve generated the initial version of the 1:1 view layer with oddgen<\/a>‘s 1:1 View generator.<\/p>\n\n\n\n

    View-API<\/span><\/path><\/path><\/svg><\/span>
    ALTER<\/span> <\/span>SESSION<\/span> <\/span>SET<\/span> current_schema=hr;<\/span><\/span>\n<\/span>\nCREATE OR REPLACE<\/span> <\/span>VIEW<\/span> <\/span>COUNTRIES_V<\/span> <\/span>AS<\/span><\/span>\n   <\/span>SELECT<\/span> COUNTRY_ID,<\/span><\/span>\n          COUNTRY_NAME,<\/span><\/span>\n          REGION_ID<\/span><\/span>\n     <\/span>FROM<\/span> COUNTRIES;<\/span><\/span>\n<\/span>\nCREATE OR REPLACE<\/span> <\/span>VIEW<\/span> <\/span>DEPARTMENTS_V<\/span> <\/span>AS<\/span><\/span>\n   <\/span>SELECT<\/span> DEPARTMENT_ID,<\/span><\/span>\n          DEPARTMENT_NAME,<\/span><\/span>\n          MANAGER_ID,<\/span><\/span>\n          LOCATION_ID<\/span><\/span>\n     <\/span>FROM<\/span> DEPARTMENTS;<\/span><\/span>\n<\/span>\nCREATE OR REPLACE<\/span> <\/span>VIEW<\/span> <\/span>EMPLOYEES_V<\/span> <\/span>AS<\/span><\/span>\n   <\/span>SELECT<\/span> EMPLOYEE_ID,<\/span><\/span>\n          FIRST_NAME,<\/span><\/span>\n          LAST_NAME,<\/span><\/span>\n          EMAIL,<\/span><\/span>\n          PHONE_NUMBER,<\/span><\/span>\n          HIRE_DATE,<\/span><\/span>\n          JOB_ID,<\/span><\/span>\n          SALARY,<\/span><\/span>\n          COMMISSION_PCT,<\/span><\/span>\n          MANAGER_ID,<\/span><\/span>\n          DEPARTMENT_ID<\/span><\/span>\n     <\/span>FROM<\/span> EMPLOYEES;<\/span><\/span>\n<\/span>\nCREATE OR REPLACE<\/span> <\/span>VIEW<\/span> <\/span>JOB_HISTORY_V<\/span> <\/span>AS<\/span><\/span>\n   <\/span>SELECT<\/span> EMPLOYEE_ID,<\/span><\/span>\n          <\/span>START_DATE<\/span>,<\/span><\/span>\n          END_DATE,<\/span><\/span>\n          JOB_ID,<\/span><\/span>\n          DEPARTMENT_ID<\/span><\/span>\n     <\/span>FROM<\/span> JOB_HISTORY;<\/span><\/span>\n<\/span>\nCREATE OR REPLACE<\/span> <\/span>VIEW<\/span> <\/span>JOBS_V<\/span> <\/span>AS<\/span><\/span>\n   <\/span>SELECT<\/span> JOB_ID,<\/span><\/span>\n          JOB_TITLE,<\/span><\/span>\n          MIN_SALARY,<\/span><\/span>\n          MAX_SALARY<\/span><\/span>\n     <\/span>FROM<\/span> JOBS;<\/span><\/span>\n<\/span>\nCREATE OR REPLACE<\/span> <\/span>VIEW<\/span> <\/span>LOCATIONS_V<\/span> <\/span>AS<\/span><\/span>\n   <\/span>SELECT<\/span> LOCATION_ID,<\/span><\/span>\n          STREET_ADDRESS,<\/span><\/span>\n          POSTAL_CODE,<\/span><\/span>\n          CITY,<\/span><\/span>\n          STATE_PROVINCE,<\/span><\/span>\n          COUNTRY_ID<\/span><\/span>\n     <\/span>FROM<\/span> LOCATIONS;<\/span><\/span>\n<\/span>\nCREATE OR REPLACE<\/span> <\/span>VIEW<\/span> <\/span>REGIONS_V<\/span> <\/span>AS<\/span><\/span>\n   <\/span>SELECT<\/span> REGION_ID,<\/span><\/span>\n          REGION_NAME<\/span><\/span>\n     <\/span>FROM<\/span> REGIONS;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    View Constraints<\/h3>\n\n\n\n
    When building a view-API it is a good idea to also define primary key, foreign key and unique constraints for these views. They will not be enforced by the Oracle database, but they are an excellent documentation. Furthermore, they will be used by JOOQ as we see later.<\/p>\n\n\n\n
    View Constraints<\/span><\/path><\/path><\/svg><\/span>
    ALTER<\/span> <\/span>SESSION<\/span> <\/span>SET<\/span> current_schema=hr;<\/span><\/span>\n<\/span>\n-- primary key and unique constraints<\/span><\/span>\nALTER<\/span> <\/span>VIEW<\/span> countries_v   <\/span>ADD<\/span> <\/span>PRIMARY KEY<\/span> (country_id)              <\/span>DISABLE<\/span> NOVALIDATE;<\/span><\/span>\nALTER<\/span> <\/span>VIEW<\/span> departments_v <\/span>ADD<\/span> <\/span>PRIMARY KEY<\/span> (department_id)           <\/span>DISABLE<\/span> NOVALIDATE;<\/span><\/span>\nALTER<\/span> <\/span>VIEW<\/span> employees_v   <\/span>ADD<\/span> <\/span>PRIMARY KEY<\/span> (employee_id)             <\/span>DISABLE<\/span> NOVALIDATE;<\/span><\/span>\nALTER<\/span> <\/span>VIEW<\/span> employees_v   <\/span>ADD<\/span> <\/span>UNIQUE<\/span>      (email)                   <\/span>DISABLE<\/span> NOVALIDATE;<\/span><\/span>\nALTER<\/span> <\/span>VIEW<\/span> job_history_v <\/span>ADD<\/span> <\/span>PRIMARY KEY<\/span> (employee_id, <\/span>start_date<\/span>) <\/span>DISABLE<\/span> NOVALIDATE;<\/span><\/span>\nALTER<\/span> <\/span>VIEW<\/span> jobs_v        <\/span>ADD<\/span> <\/span>PRIMARY KEY<\/span> (job_id)                  <\/span>DISABLE<\/span> NOVALIDATE;<\/span><\/span>\nALTER<\/span> <\/span>VIEW<\/span> locations_v   <\/span>ADD<\/span> <\/span>PRIMARY KEY<\/span> (location_id)             <\/span>DISABLE<\/span> NOVALIDATE;<\/span><\/span>\nALTER<\/span> <\/span>VIEW<\/span> regions_v     <\/span>ADD<\/span> <\/span>PRIMARY KEY<\/span> (region_id)               <\/span>DISABLE<\/span> NOVALIDATE;<\/span><\/span>\n<\/span>\n-- foreign key constraints<\/span><\/span>\nALTER<\/span> <\/span>VIEW<\/span> countries_v   <\/span>ADD<\/span> <\/span>FOREIGN KEY<\/span> (region_id)     <\/span>REFERENCES<\/span> hr.regions_v     <\/span>DISABLE<\/span> NOVALIDATE;<\/span><\/span>\nALTER<\/span> <\/span>VIEW<\/span> departments_v <\/span>ADD<\/span> <\/span>FOREIGN KEY<\/span> (location_id)   <\/span>REFERENCES<\/span> hr.locations_v   <\/span>DISABLE<\/span> NOVALIDATE;<\/span><\/span>\nALTER<\/span> <\/span>VIEW<\/span> departments_v <\/span>ADD<\/span> <\/span>FOREIGN KEY<\/span> (manager_id)    <\/span>REFERENCES<\/span> hr.employees_v   <\/span>DISABLE<\/span> NOVALIDATE;<\/span><\/span>\nALTER<\/span> <\/span>VIEW<\/span> employees_v   <\/span>ADD<\/span> <\/span>FOREIGN KEY<\/span> (department_id) <\/span>REFERENCES<\/span> hr.departments_v <\/span>DISABLE<\/span> NOVALIDATE;<\/span><\/span>\nALTER<\/span> <\/span>VIEW<\/span> employees_v   <\/span>ADD<\/span> <\/span>FOREIGN KEY<\/span> (job_id)        <\/span>REFERENCES<\/span> hr.jobs_v        <\/span>DISABLE<\/span> NOVALIDATE;<\/span><\/span>\nALTER<\/span> <\/span>VIEW<\/span> employees_v   <\/span>ADD<\/span> <\/span>FOREIGN KEY<\/span> (manager_id)    <\/span>REFERENCES<\/span> hr.employees_v   <\/span>DISABLE<\/span> NOVALIDATE;<\/span><\/span>\nALTER<\/span> <\/span>VIEW<\/span> job_history_v <\/span>ADD<\/span> <\/span>FOREIGN KEY<\/span> (department_id) <\/span>REFERENCES<\/span> hr.departments_v <\/span>DISABLE<\/span> NOVALIDATE;<\/span><\/span>\nALTER<\/span> <\/span>VIEW<\/span> job_history_v <\/span>ADD<\/span> <\/span>FOREIGN KEY<\/span> (employee_id)   <\/span>REFERENCES<\/span> hr.employees_v   <\/span>DISABLE<\/span> NOVALIDATE;<\/span><\/span>\nALTER<\/span> <\/span>VIEW<\/span> job_history_v <\/span>ADD<\/span> <\/span>FOREIGN KEY<\/span> (job_id)        <\/span>REFERENCES<\/span> hr.jobs_v        <\/span>DISABLE<\/span> NOVALIDATE;<\/span><\/span>\nALTER<\/span> <\/span>VIEW<\/span> locations_v   <\/span>ADD<\/span> <\/span>FOREIGN KEY<\/span> (country_id)    <\/span>REFERENCES<\/span> hr.countries_v   <\/span>DISABLE<\/span> NOVALIDATE;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    View-API Model<\/h3>\n\n\n\n
    I created the following relational model with Oracle’s SQL Developer. SQL Developer automatically took into account all constraints defined on these 7 views. Most of the work was to rearrange the view boxes on the diagram, but with only 7 views it was no big deal.<\/p>\n\n\n\n
    \"HR<\/a><\/figure>\n\n\n\n
    <\/a>3. Create the View-API Database Role<\/h2>\n\n\n\n
    We grant read access to all views as part of the API to the database role HR_API_ROLE<\/code>. This is easier to maintain, especially when access to more than one connect user is granted.<\/p>\n\n\n\n
    Please note that just READ<\/code> access is granted on the views. We do not grant SELECT<\/code> access to ensure that data can’t be locked by the connect users via SELECT FOR UPDATE<\/code>.  The READ<\/code> privilege was introduced in Oracle Database 12c.<\/p>\n\n\n\n
    HR_API_ROLE<\/span><\/path><\/path><\/svg><\/span>
    CREATE<\/span> <\/span>ROLE<\/span> hr_api_role;<\/span><\/span>\n<\/span>\nGRANT<\/span> <\/span>READ<\/span> <\/span>ON<\/span> hr.countries_v   <\/span>TO<\/span> hr_api_role;<\/span><\/span>\nGRANT<\/span> <\/span>READ<\/span> <\/span>ON<\/span> hr.departments_v <\/span>TO<\/span> hr_api_role;<\/span><\/span>\nGRANT<\/span> <\/span>READ<\/span> <\/span>ON<\/span> hr.employees_v   <\/span>TO<\/span> hr_api_role;<\/span><\/span>\nGRANT<\/span> <\/span>READ<\/span> <\/span>ON<\/span> hr.job_history_v <\/span>TO<\/span> hr_api_role;<\/span><\/span>\nGRANT<\/span> <\/span>READ<\/span> <\/span>ON<\/span> hr.jobs_v        <\/span>TO<\/span> hr_api_role;<\/span><\/span>\nGRANT<\/span> <\/span>READ<\/span> <\/span>ON<\/span> hr.locations_v   <\/span>TO<\/span> hr_api_role;<\/span><\/span>\nGRANT<\/span> <\/span>READ<\/span> <\/span>ON<\/span> hr.regions_v     <\/span>TO<\/span> hr_api_role;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    In this case, it does not make sense to define more than one role. But for larger projects, it might be helpful to create more roles. For example to distinguish between read and write roles or to manage access to sensitive data explicitly.<\/p>\n\n\n\n
    <\/a>4. Create the Connect User<\/h2>\n\n\n\n
    Now we create a connect user named JOOQ<\/code>. It will have the right to connect, access the view-API and execute the procedures in the SYS.DBMS_MONITOR<\/code> package. The access to DBMS_MONITOR<\/code> is given only to enable SQL Trace for some analysis on the database server.<\/p>\n\n\n\n
    Create Connect User JOOQ<\/span><\/path><\/path><\/svg><\/span>
    CREATE<\/span> <\/span>USER<\/span> <\/span>jooq<\/span> IDENTIFIED <\/span>BY<\/span> jooq;<\/span><\/span>\nGRANT<\/span> <\/span>connect<\/span>, hr_api_role <\/span>TO<\/span> jooq;<\/span><\/span>\nGRANT<\/span> <\/span>EXECUTE<\/span> <\/span>ON<\/span> sys.dbms_monitor <\/span>TO<\/span> jooq;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    <\/a>5. Install JOOQ<\/h2>\n\n\n\n
    Download<\/a> the trial version of JOOQ Professional for free. The trial period is 30 days. Registration is not required.<\/p>\n\n\n\n
    JOOQ comes with an excellent documentation. In fact, I kept the PDF version open during all my tests and found everything I needed using the search function. After extracting the downloaded ZIP file, I run the following commands on my macOS system:<\/p>\n\n\n\n
    Install JOOQ in Local Maven Repository<\/span><\/path><\/path><\/svg><\/span>
    chmod<\/span> <\/span>+x<\/span> <\/span>maven-deploy.sh<\/span><\/span>\n.\/maven-deploy.sh<\/span> <\/span>-u<\/span> <\/span>file:\/\/\/Users\/phs\/.m2\/repository<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    This deployed JOOQ in my local Maven repository. Run .\/maven-deploy.sh -h<\/code> for command line options, e.g. when you want to deploy it on your Sonatype Nexus repository.<\/p>\n\n\n\n
    <\/a>6. Teach JOOQ the View-API<\/h2>\n\n\n\n
    JOOQ allows to build SQL and execute SQL statements in a typesafe manner. It’s similar to SQL within PL\/SQL. You avoid to build SQL using string concatenation. Instead, you use the JOOQ domain-specific language (DSL) that knows about SQL and can learn about your database model. For example, JOOQ must learn that\u00a0JOBS_V<\/code> is a view and JOB_ID<\/code>\u00a0is its primary key column.<\/p>\n\n\n\n
    Technically JOOQ reads the data dictionary and generates a set of Java classes for you. You may call the generator via command line using a XML configuration file or use the JOOQ Maven plugin to configure the generator directly in your pom.xml<\/code>. You may also generate these classes via Ant or Gradle. I’ve used the Maven plugin to include the code generation process in my project build.<\/p>\n\n\n\n
    On lines 36-38, we define the JDBC driver.<\/p>\n\n\n\n
    On lines 43-46, we configure the JDBC URL and the credentials of the connect user.<\/p>\n\n\n\n
    Line 50 tells JOOQ to use the Oracle meta-model and line 51 is a regular expression to define the generation scope. In our case the visible objects in the HR<\/code> schema and the PL\/SQL package SYS.DBMS_MONITOR<\/code>. Using the default would generate Java code for all public objects.<\/p>\n\n\n\n
    Finally, on lines 54-55, we define the target directory for the\u00a0generated Java classes and their Java package name.<\/p>\n\n\n\n
    XML<\/span><\/path><\/path><\/svg><\/span>
    <<\/span>project<\/span> <\/span>xmlns<\/span>=<\/span>"http:\/\/maven.apache.org\/POM\/4.0.0"<\/span> <\/span>xmlns:xsi<\/span>=<\/span>"http:\/\/www.w3.org\/2001\/XMLSchema-instance"<\/span><\/span>\n   <\/span>xsi:schemaLocation<\/span>=<\/span>"http:\/\/maven.apache.org\/POM\/4.0.0 http:\/\/maven.apache.org\/xsd\/maven-4.0.0.xsd"<\/span>><\/span><\/span>\n   <\/span><<\/span>modelVersion<\/span>><\/span>4.0.0<\/span><\/<\/span>modelVersion<\/span>><\/span><\/span>\n   <\/span><<\/span>groupId<\/span>><\/span>com.trivadis.pinkdb.jooq<\/span><\/<\/span>groupId<\/span>><\/span><\/span>\n   <\/span><<\/span>artifactId<\/span>><\/span>jooq-pinkdb<\/span><\/<\/span>artifactId<\/span>><\/span><\/span>\n   <\/span><<\/span>version<\/span>><\/span>0.0.1-SNAPSHOT<\/span><\/<\/span>version<\/span>><\/span><\/span>\n   <\/span><<\/span>properties<\/span>><\/span><\/span>\n      <\/span><<\/span>project.build.sourceEncoding<\/span>><\/span>UTF-8<\/span><\/<\/span>project.build.sourceEncoding<\/span>><\/span><\/span>\n      <\/span><<\/span>jdk.version<\/span>><\/span>1.8<\/span><\/<\/span>jdk.version<\/span>><\/span><\/span>\n      <\/span><<\/span>xtend.version<\/span>><\/span>2.12.0<\/span><\/<\/span>xtend.version<\/span>><\/span><\/span>\n   <\/span><\/<\/span>properties<\/span>><\/span><\/span>\n   <\/span><<\/span>build<\/span>><\/span><\/span>\n      <\/span><<\/span>plugins<\/span>><\/span><\/span>\n         <\/span><<\/span>plugin<\/span>><\/span><\/span>\n            <\/span><<\/span>groupId<\/span>><\/span>org.apache.maven.plugins<\/span><\/<\/span>groupId<\/span>><\/span><\/span>\n            <\/span><<\/span>version<\/span>><\/span>3.8.0<\/span><\/<\/span>version<\/span>><\/span><\/span>\n            <\/span><<\/span>artifactId<\/span>><\/span>maven-compiler-plugin<\/span><\/<\/span>artifactId<\/span>><\/span><\/span>\n            <\/span><<\/span>configuration<\/span>><\/span><\/span>\n               <\/span><<\/span>source<\/span>><\/span>${jdk.version}<\/span><\/<\/span>source<\/span>><\/span><\/span>\n               <\/span><<\/span>target<\/span>><\/span>${jdk.version}<\/span><\/<\/span>target<\/span>><\/span><\/span>\n            <\/span><\/<\/span>configuration<\/span>><\/span><\/span>\n         <\/span><\/<\/span>plugin<\/span>><\/span><\/span>\n         <\/span><<\/span>plugin<\/span>><\/span><\/span>\n            <\/span><<\/span>groupId<\/span>><\/span>org.jooq.trial<\/span><\/<\/span>groupId<\/span>><\/span><\/span>\n            <\/span><<\/span>artifactId<\/span>><\/span>jooq-codegen-maven<\/span><\/<\/span>artifactId<\/span>><\/span><\/span>\n            <\/span><<\/span>version<\/span>><\/span>3.11.3<\/span><\/<\/span>version<\/span>><\/span><\/span>\n            <\/span><<\/span>executions<\/span>><\/span><\/span>\n               <\/span><<\/span>execution<\/span>><\/span><\/span>\n                  <\/span><<\/span>goals<\/span>><\/span><\/span>\n                     <\/span><<\/span>goal<\/span>><\/span>generate<\/span><\/<\/span>goal<\/span>><\/span><\/span>\n                  <\/span><\/<\/span>goals<\/span>><\/span><\/span>\n               <\/span><\/<\/span>execution<\/span>><\/span><\/span>\n            <\/span><\/<\/span>executions<\/span>><\/span><\/span>\n            <\/span><<\/span>dependencies<\/span>><\/span><\/span>\n               <\/span><<\/span>dependency<\/span>><\/span><\/span>\n                  <\/span><<\/span>groupId<\/span>><\/span>oracle<\/span><\/<\/span>groupId<\/span>><\/span><\/span>\n                  <\/span><<\/span>artifactId<\/span>><\/span>ojdbc8<\/span><\/<\/span>artifactId<\/span>><\/span><\/span>\n                  <\/span><<\/span>version<\/span>><\/span>12.2.0.1.0<\/span><\/<\/span>version<\/span>><\/span><\/span>\n               <\/span><\/<\/span>dependency<\/span>><\/span><\/span>\n            <\/span><\/<\/span>dependencies<\/span>><\/span><\/span>\n            <\/span><<\/span>configuration<\/span>><\/span><\/span>\n               <\/span><<\/span>jdbc<\/span>><\/span><\/span>\n                  <\/span><<\/span>driver<\/span>><\/span>oracle.jdbc.OracleDriver<\/span><\/<\/span>driver<\/span>><\/span><\/span>\n                  <\/span><<\/span>url<\/span>><\/span>jdbc:oracle:thin:@localhost:1521\/odb.docker<\/span><\/<\/span>url<\/span>><\/span><\/span>\n                  <\/span><<\/span>user<\/span>><\/span>jooq<\/span><\/<\/span>user<\/span>><\/span><\/span>\n                  <\/span><<\/span>password<\/span>><\/span>jooq<\/span><\/<\/span>password<\/span>><\/span><\/span>\n               <\/span><\/<\/span>jdbc<\/span>><\/span><\/span>\n               <\/span><<\/span>generator<\/span>><\/span><\/span>\n                  <\/span><<\/span>database<\/span>><\/span><\/span>\n                     <\/span><<\/span>name<\/span>><\/span>org.jooq.meta.oracle.OracleDatabase<\/span><\/<\/span>name<\/span>><\/span><\/span>\n                     <\/span><<\/span>includes<\/span>><\/span>HR\\..*|SYS\\.DBMS_MONITOR<\/span><\/<\/span>includes<\/span>><\/span><\/span>\n                  <\/span><\/<\/span>database<\/span>><\/span><\/span>\n                  <\/span><<\/span>target<\/span>><\/span><\/span>\n                     <\/span><<\/span>packageName<\/span>><\/span>com.trivadis.jooq.pinkdb.model.generated<\/span><\/<\/span>packageName<\/span>><\/span><\/span>\n                     <\/span><<\/span>directory<\/span>><\/span>src\/main\/java<\/span><\/<\/span>directory<\/span>><\/span><\/span>\n                  <\/span><\/<\/span>target<\/span>><\/span><\/span>\n               <\/span><\/<\/span>generator<\/span>><\/span><\/span>\n            <\/span><\/<\/span>configuration<\/span>><\/span><\/span>\n         <\/span><\/<\/span>plugin<\/span>><\/span><\/span>\n      <\/span><\/<\/span>plugins<\/span>><\/span><\/span>\n   <\/span><\/<\/span>build<\/span>><\/span><\/span>\n   <\/span><<\/span>dependencies<\/span>><\/span><\/span>\n      <\/span><<\/span>dependency<\/span>><\/span><\/span>\n         <\/span><<\/span>groupId<\/span>><\/span>org.jooq.trial<\/span><\/<\/span>groupId<\/span>><\/span><\/span>\n         <\/span><<\/span>artifactId<\/span>><\/span>jooq<\/span><\/<\/span>artifactId<\/span>><\/span><\/span>\n         <\/span><<\/span>version<\/span>><\/span>3.11.3<\/span><\/<\/span>version<\/span>><\/span><\/span>\n      <\/span><\/<\/span>dependency<\/span>><\/span><\/span>\n      <\/span><<\/span>dependency<\/span>><\/span><\/span>\n         <\/span><<\/span>groupId<\/span>><\/span>oracle<\/span><\/<\/span>groupId<\/span>><\/span><\/span>\n         <\/span><<\/span>artifactId<\/span>><\/span>ojdbc8<\/span><\/<\/span>artifactId<\/span>><\/span><\/span>\n         <\/span><<\/span>version<\/span>><\/span>12.2.0.1.0<\/span><\/<\/span>version<\/span>><\/span><\/span>\n      <\/span><\/<\/span>dependency<\/span>><\/span><\/span>\n   <\/span><\/<\/span>dependencies<\/span>><\/span><\/span>\n<\/<\/span>project<\/span>><\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    Here’s the excerpt of the console output (of mvn package<\/code>) regarding the generation run:<\/p>\n\n\n

    \n
    <\/i><\/i>JOOQ generator output<\/div>
    <\/p>\n\n\n\n
    JOOQ Generator Output<\/span><\/path><\/path><\/svg><\/span>
    [INFO] --- jooq-codegen-maven:3.11.3:generate (default) @ jooq-pinkdb ---<\/span><\/span>\n[INFO] No <inputCatalog\/> was provided. Generating ALL available catalogs instead.<\/span><\/span>\n[INFO] No <inputSchema\/> was provided. Generating ALL available schemata instead.<\/span><\/span>\n[INFO] License parameters       <\/span><\/span>\n[INFO] ----------------------------------------------------------<\/span><\/span>\n[INFO]   Thank you for using jOOQ and jOOQ's code generator<\/span><\/span>\n[INFO]                          <\/span><\/span>\n[INFO] Database parameters      <\/span><\/span>\n[INFO] ----------------------------------------------------------<\/span><\/span>\n[INFO]   dialect                : ORACLE<\/span><\/span>\n[INFO]   URL                    : jdbc:oracle:thin:@localhost:1521\/odb.docker<\/span><\/span>\n[INFO]   target dir             : \/Users\/phs\/git\/JooqPinkDB\/src\/main\/java<\/span><\/span>\n[INFO]   target package         : com.trivadis.jooq.pinkdb.model.generated<\/span><\/span>\n[INFO]   includes               : [HR\\..*|SYS\\.DBMS_MONITOR]<\/span><\/span>\n[INFO]   excludes               : []<\/span><\/span>\n[INFO]   includeExcludeColumns  : false<\/span><\/span>\n[INFO] ----------------------------------------------------------<\/span><\/span>\n[INFO]                          <\/span><\/span>\n[INFO] JavaGenerator parameters <\/span><\/span>\n[INFO] ----------------------------------------------------------<\/span><\/span>\n[INFO]   annotations (generated): true<\/span><\/span>\n[INFO]   annotations (JPA: any) : false<\/span><\/span>\n[INFO]   annotations (JPA: version): <\/span><\/span>\n[INFO]   annotations (validation): false<\/span><\/span>\n[INFO]   comments               : true<\/span><\/span>\n[INFO]   comments on attributes : true<\/span><\/span>\n[INFO]   comments on catalogs   : true<\/span><\/span>\n[INFO]   comments on columns    : true<\/span><\/span>\n[INFO]   comments on keys       : true<\/span><\/span>\n[INFO]   comments on links      : true<\/span><\/span>\n[INFO]   comments on packages   : true<\/span><\/span>\n[INFO]   comments on parameters : true<\/span><\/span>\n[INFO]   comments on queues     : true<\/span><\/span>\n[INFO]   comments on routines   : true<\/span><\/span>\n[INFO]   comments on schemas    : true<\/span><\/span>\n[INFO]   comments on sequences  : true<\/span><\/span>\n[INFO]   comments on tables     : true<\/span><\/span>\n[INFO]   comments on udts       : true<\/span><\/span>\n[INFO]   daos                   : false<\/span><\/span>\n[INFO]   deprecated code        : true<\/span><\/span>\n[INFO]   global references (any): true<\/span><\/span>\n[INFO]   global references (catalogs): true<\/span><\/span>\n[INFO]   global references (keys): true<\/span><\/span>\n[INFO]   global references (links): true<\/span><\/span>\n[INFO]   global references (queues): true<\/span><\/span>\n[INFO]   global references (routines): true<\/span><\/span>\n[INFO]   global references (schemas): true<\/span><\/span>\n[INFO]   global references (sequences): true<\/span><\/span>\n[INFO]   global references (tables): true<\/span><\/span>\n[INFO]   global references (udts): true<\/span><\/span>\n[INFO]   indexes                : true<\/span><\/span>\n[INFO]   instance fields        : true<\/span><\/span>\n[INFO]   interfaces             : false<\/span><\/span>\n[INFO]   interfaces (immutable) : false<\/span><\/span>\n[INFO]   javadoc                : true<\/span><\/span>\n[INFO]   keys                   : true<\/span><\/span>\n[INFO]   links                  : true<\/span><\/span>\n[INFO]   pojos                  : false<\/span><\/span>\n[INFO]   pojos (immutable)      : false<\/span><\/span>\n[INFO]   queues                 : true<\/span><\/span>\n[INFO]   records                : true<\/span><\/span>\n[INFO]   routines               : true<\/span><\/span>\n[INFO]   sequences              : true<\/span><\/span>\n[INFO]   table-valued functions : false<\/span><\/span>\n[INFO]   tables                 : true<\/span><\/span>\n[INFO]   udts                   : true<\/span><\/span>\n[INFO]   relations              : true<\/span><\/span>\n[INFO] ----------------------------------------------------------<\/span><\/span>\n[INFO]                          <\/span><\/span>\n[INFO] Generation remarks       <\/span><\/span>\n[INFO] ----------------------------------------------------------<\/span><\/span>\n[INFO]                          <\/span><\/span>\n[INFO] ----------------------------------------------------------<\/span><\/span>\n[INFO] Generating catalogs      : Total: 1<\/span><\/span>\n[INFO] <\/span><\/span>\n                                      <\/span><\/span>\n@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@<\/span><\/span>\n@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@<\/span><\/span>\n@@@@@@@@@@@@@@@@  @@        @@@@@@@@@@<\/span><\/span>\n@@@@@@@@@@@@@@@@@@@@        @@@@@@@@@@<\/span><\/span>\n@@@@@@@@@@@@@@@@  @@  @@    @@@@@@@@@@<\/span><\/span>\n@@@@@@@@@@  @@@@  @@  @@    @@@@@@@@@@<\/span><\/span>\n@@@@@@@@@@        @@        @@@@@@@@@@<\/span><\/span>\n@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@<\/span><\/span>\n@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@<\/span><\/span>\n@@@@@@@@@@        @@        @@@@@@@@@@<\/span><\/span>\n@@@@@@@@@@    @@  @@  @@@@  @@@@@@@@@@<\/span><\/span>\n@@@@@@@@@@    @@  @@  @@@@  @@@@@@@@@@<\/span><\/span>\n@@@@@@@@@@        @@  @  @  @@@@@@@@@@<\/span><\/span>\n@@@@@@@@@@        @@        @@@@@@@@@@<\/span><\/span>\n@@@@@@@@@@@@@@@@@@@@@@@  @@@@@@@@@@@@@<\/span><\/span>\n@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@<\/span><\/span>\n@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  Thank you for using the 30 day free jOOQ 3.11.3 trial edition<\/span><\/span>\n                                      <\/span><\/span>\n[INFO] Packages fetched         : 517 (1 included, 516 excluded)<\/span><\/span>\n[INFO] Applying synonym         : "PUBLIC"."KU$_LOGENTRY" is synonym for "SYS"."KU$_LOGENTRY1010"<\/span><\/span>\n[INFO] Applying synonym         : "PUBLIC"."XMLTYPE" is synonym for "SYS"."XMLTYPE"<\/span><\/span>\n[INFO] ARRAYs fetched           : 641 (0 included, 641 excluded)<\/span><\/span>\n[INFO] Enums fetched            : 0 (0 included, 0 excluded)<\/span><\/span>\n[INFO] No schema version is applied for catalog . Regenerating.<\/span><\/span>\n[INFO]                          <\/span><\/span>\n[INFO] Generating catalog       : DefaultCatalog.java<\/span><\/span>\n[INFO] ==========================================================<\/span><\/span>\n[INFO] Routines fetched         : 312 (0 included, 312 excluded)<\/span><\/span>\n[INFO] Tables fetched           : 2347 (7 included, 2340 excluded)<\/span><\/span>\n[INFO] UDTs fetched             : 1287 (0 included, 1287 excluded)<\/span><\/span>\n[INFO] Generating schemata      : Total: 63<\/span><\/span>\n[INFO] No schema version is applied for schema SYS. Regenerating.<\/span><\/span>\n[INFO] Generating schema        : Sys.java<\/span><\/span>\n[INFO] ----------------------------------------------------------<\/span><\/span>\n[INFO] Sequences fetched        : 12 (0 included, 12 excluded)<\/span><\/span>\n[INFO] Domains fetched          : 0 (0 included, 0 excluded)<\/span><\/span>\n[INFO] Generating packages      <\/span><\/span>\n[INFO] Generating package       : SYS.DBMS_MONITOR<\/span><\/span>\n[INFO] Generating routine       : ClientIdStatDisable.java<\/span><\/span>\n[INFO] Generating routine       : ClientIdStatEnable.java<\/span><\/span>\n[INFO] Generating routine       : ClientIdTraceDisable.java<\/span><\/span>\n[INFO] Generating routine       : ClientIdTraceEnable.java<\/span><\/span>\n[INFO] Generating routine       : DatabaseTraceDisable.java<\/span><\/span>\n[INFO] Generating routine       : DatabaseTraceEnable.java<\/span><\/span>\n[INFO] Generating routine       : ServModActStatDisable.java<\/span><\/span>\n[INFO] Generating routine       : ServModActStatEnable.java<\/span><\/span>\n[INFO] Generating routine       : ServModActTraceDisable.java<\/span><\/span>\n[INFO] Generating routine       : ServModActTraceEnable.java<\/span><\/span>\n[INFO] Generating routine       : SessionTraceDisable.java<\/span><\/span>\n[INFO] Generating routine       : SessionTraceEnable.java<\/span><\/span>\n[INFO] Packages generated       : Total: 7.009s<\/span><\/span>\n[INFO] UDT not supported or not in input schemata: SYS.SCHEDULER$_EVENT_INFO<\/span><\/span>\n[INFO] UDT not supported or not in input schemata: SYS.SCHEDULER_FILEWATCHER_RESULT<\/span><\/span>\n[INFO] Queues fetched           : 0 (0 included, 0 excluded)<\/span><\/span>\n[INFO] Links fetched            : 0 (0 included, 0 excluded)<\/span><\/span>\n[INFO] Generation finished: SYS : Total: 7.482s, +473.374ms<\/span><\/span>\n[INFO]                          <\/span><\/span>\n[INFO] Excluding empty schema   : AUDSYS<\/span><\/span>\n[INFO] Excluding empty schema   : SYSTEM<\/span><\/span>\n[INFO] Excluding empty schema   : SYSBACKUP<\/span><\/span>\n[INFO] Excluding empty schema   : SYSDG<\/span><\/span>\n[INFO] Excluding empty schema   : SYSKM<\/span><\/span>\n[INFO] Excluding empty schema   : SYSRAC<\/span><\/span>\n[INFO] Excluding empty schema   : OUTLN<\/span><\/span>\n[INFO] Excluding empty schema   : XS$NULL<\/span><\/span>\n[INFO] Excluding empty schema   : GSMADMIN_INTERNAL<\/span><\/span>\n[INFO] Excluding empty schema   : GSMUSER<\/span><\/span>\n[INFO] Excluding empty schema   : DIP<\/span><\/span>\n[INFO] Excluding empty schema   : REMOTE_SCHEDULER_AGENT<\/span><\/span>\n[INFO] Excluding empty schema   : DBSFWUSER<\/span><\/span>\n[INFO] Excluding empty schema   : ORACLE_OCM<\/span><\/span>\n[INFO] Excluding empty schema   : SYS$UMF<\/span><\/span>\n[INFO] Excluding empty schema   : DBSNMP<\/span><\/span>\n[INFO] Excluding empty schema   : APPQOSSYS<\/span><\/span>\n[INFO] Excluding empty schema   : GSMCATUSER<\/span><\/span>\n[INFO] Excluding empty schema   : GGSYS<\/span><\/span>\n[INFO] Excluding empty schema   : XDB<\/span><\/span>\n[INFO] Excluding empty schema   : ANONYMOUS<\/span><\/span>\n[INFO] Excluding empty schema   : WMSYS<\/span><\/span>\n[INFO] Excluding empty schema   : DVF<\/span><\/span>\n[INFO] Excluding empty schema   : OJVMSYS<\/span><\/span>\n[INFO] Excluding empty schema   : CTXSYS<\/span><\/span>\n[INFO] Excluding empty schema   : ORDSYS<\/span><\/span>\n[INFO] Excluding empty schema   : ORDDATA<\/span><\/span>\n[INFO] Excluding empty schema   : ORDPLUGINS<\/span><\/span>\n[INFO] Excluding empty schema   : SI_INFORMTN_SCHEMA<\/span><\/span>\n[INFO] Excluding empty schema   : MDSYS<\/span><\/span>\n[INFO] Excluding empty schema   : OLAPSYS<\/span><\/span>\n[INFO] Excluding empty schema   : MDDATA<\/span><\/span>\n[INFO] Excluding empty schema   : LBACSYS<\/span><\/span>\n[INFO] Excluding empty schema   : DVSYS<\/span><\/span>\n[INFO] Excluding empty schema   : FLOWS_FILES<\/span><\/span>\n[INFO] Excluding empty schema   : APEX_PUBLIC_USER<\/span><\/span>\n[INFO] Excluding empty schema   : APEX_180100<\/span><\/span>\n[INFO] Excluding empty schema   : APEX_INSTANCE_ADMIN_USER<\/span><\/span>\n[INFO] Excluding empty schema   : APEX_LISTENER<\/span><\/span>\n[INFO] Excluding empty schema   : APEX_REST_PUBLIC_USER<\/span><\/span>\n[INFO] Excluding empty schema   : ORDS_METADATA<\/span><\/span>\n[INFO] Excluding empty schema   : ORDS_PUBLIC_USER<\/span><\/span>\n[INFO] Excluding empty schema   : SCOTT<\/span><\/span>\n[INFO] No schema version is applied for schema HR. Regenerating.<\/span><\/span>\n[INFO] Generating schema        : Hr.java<\/span><\/span>\n[INFO] ----------------------------------------------------------<\/span><\/span>\n[INFO] Generating tables        <\/span><\/span>\n[INFO] Adding foreign key       : SYS_C0012240 (HR.COUNTRIES_V.REGION_ID) referencing SYS_C0012239<\/span><\/span>\n[INFO] Adding foreign key       : SYS_C0012241 (HR.DEPARTMENTS_V.LOCATION_ID) referencing SYS_C0012238<\/span><\/span>\n[INFO] Adding foreign key       : SYS_C0012242 (HR.DEPARTMENTS_V.MANAGER_ID) referencing SYS_C0012235<\/span><\/span>\n[INFO] Adding foreign key       : SYS_C0012244 (HR.EMPLOYEES_V.DEPARTMENT_ID) referencing SYS_C0012234<\/span><\/span>\n[INFO] Adding foreign key       : SYS_C0012245 (HR.EMPLOYEES_V.JOB_ID) referencing SYS_C0012237<\/span><\/span>\n[INFO] Adding foreign key       : SYS_C0012246 (HR.EMPLOYEES_V.MANAGER_ID) referencing SYS_C0012235<\/span><\/span>\n[INFO] Adding foreign key       : SYS_C0012247 (HR.JOB_HISTORY_V.DEPARTMENT_ID) referencing SYS_C0012234<\/span><\/span>\n[INFO] Adding foreign key       : SYS_C0012248 (HR.JOB_HISTORY_V.EMPLOYEE_ID) referencing SYS_C0012235<\/span><\/span>\n[INFO] Adding foreign key       : SYS_C0012249 (HR.JOB_HISTORY_V.JOB_ID) referencing SYS_C0012237<\/span><\/span>\n[INFO] Adding foreign key       : SYS_C0012250 (HR.LOCATIONS_V.COUNTRY_ID) referencing SYS_C0012233<\/span><\/span>\n[INFO] Synthetic primary keys   : 0 (0 included, 0 excluded)<\/span><\/span>\n[INFO] Overriding primary keys  : 8 (0 included, 8 excluded)<\/span><\/span>\n[INFO] Generating table         : CountriesV.java [input=COUNTRIES_V, output=COUNTRIES_V, pk=SYS_C0012233]<\/span><\/span>\n[INFO] Indexes fetched          : 0 (0 included, 0 excluded)<\/span><\/span>\n[INFO] Generating table         : DepartmentsV.java [input=DEPARTMENTS_V, output=DEPARTMENTS_V, pk=SYS_C0012234]<\/span><\/span>\n[INFO] Generating table         : EmployeesV.java [input=EMPLOYEES_V, output=EMPLOYEES_V, pk=SYS_C0012235]<\/span><\/span>\n[INFO] Generating table         : JobsV.java [input=JOBS_V, output=JOBS_V, pk=SYS_C0012237]<\/span><\/span>\n[INFO] Generating table         : JobHistoryV.java [input=JOB_HISTORY_V, output=JOB_HISTORY_V, pk=SYS_C0012236]<\/span><\/span>\n[INFO] Generating table         : LocationsV.java [input=LOCATIONS_V, output=LOCATIONS_V, pk=SYS_C0012238]<\/span><\/span>\n[INFO] Generating table         : RegionsV.java [input=REGIONS_V, output=REGIONS_V, pk=SYS_C0012239]<\/span><\/span>\n[INFO] Tables generated         : Total: 13.888s, +6.405s<\/span><\/span>\n[INFO] Generating table references<\/span><\/span>\n[INFO] Table refs generated     : Total: 13.889s, +1.9ms<\/span><\/span>\n[INFO] Generating Keys          <\/span><\/span>\n[INFO] Keys generated           : Total: 13.9s, +10.228ms<\/span><\/span>\n[INFO] Generating Indexes       <\/span><\/span>\n[INFO] Skipping empty indexes   <\/span><\/span>\n[INFO] Generating table records <\/span><\/span>\n[INFO] Generating record        : CountriesVRecord.java<\/span><\/span>\n[INFO] Generating record        : DepartmentsVRecord.java<\/span><\/span>\n[INFO] Generating record        : EmployeesVRecord.java<\/span><\/span>\n[INFO] Generating record        : JobsVRecord.java<\/span><\/span>\n[INFO] Generating record        : JobHistoryVRecord.java<\/span><\/span>\n[INFO] Generating record        : LocationsVRecord.java<\/span><\/span>\n[INFO] Generating record        : RegionsVRecord.java<\/span><\/span>\n[INFO] Table records generated  : Total: 13.939s, +39.368ms<\/span><\/span>\n[INFO] Generation finished: HR  : Total: 13.939s, +0.14ms<\/span><\/span>\n[INFO]                          <\/span><\/span>\n[INFO] Excluding empty schema   : OE<\/span><\/span>\n[INFO] Excluding empty schema   : PM<\/span><\/span>\n[INFO] Excluding empty schema   : IX<\/span><\/span>\n[INFO] Excluding empty schema   : SH<\/span><\/span>\n[INFO] Excluding empty schema   : BI<\/span><\/span>\n[INFO] Excluding empty schema   : FTLDB<\/span><\/span>\n[INFO] Excluding empty schema   : TEPLSQL<\/span><\/span>\n[INFO] Excluding empty schema   : ODDGEN<\/span><\/span>\n[INFO] Excluding empty schema   : OGDEMO<\/span><\/span>\n[INFO] Excluding empty schema   : AQDEMO<\/span><\/span>\n[INFO] Excluding empty schema   : AX<\/span><\/span>\n[INFO] Excluding empty schema   : EMPTRACKER<\/span><\/span>\n[INFO] Excluding empty schema   : UT3<\/span><\/span>\n[INFO] Excluding empty schema   : PLSCOPE<\/span><\/span>\n[INFO] Excluding empty schema   : SONAR<\/span><\/span>\n[INFO] Excluding empty schema   : TVDCA<\/span><\/span>\n[INFO] Excluding empty schema   : DEMO<\/span><\/span>\n[INFO] Excluding empty schema   : JOOQ<\/span><\/span>\n[INFO] Removing excess files<\/span><\/span><\/code><\/pre><\/div>\n\n\n
    <\/div><\/div>\n
    \n<\/div><\/div>\n\n\n\n\n
    <\/a>7. Run a Simple Query<\/h2>\n\n\n\n
    The following code is a complete Java program. It contains a bit more than the simple query because I will need these parts in the next chapters and would like to focus on the JOOQ query only.<\/p>\n\n\n\n
    Java Program (JOOQ Query)<\/h3>\n\n\n\n
    On line 3 the view-API is imported statically. This allows to reference the view JOBS_V<\/code> instead of  JobsV.JOBS_V<\/code> or Hr.JOBS_V<\/code>. It’s just convenience, but it makes the code much more readable.<\/p>\n\n\n\n
    The next static import on line 4 allows for example to use SQL functions such as count()<\/code>, sum()<\/code>, etc. without the DSL.<\/code> prefix. Again, it’s just convenience, but makes to code shorter and improves readability.<\/p>\n\n\n\n
    On lines 27-28, the DSL context is initialized.\u00a0 The context holds the connection to the database and some configuration. In this case I’ve configured JOOQ to produce formatted SQL statements and fetch data with an array size of 30 from the database. The JDBC default is 10, which is not bad, but 30 is in our case better, since it reduces the network roundtrips to 1 for all SQL query results.<\/p>\n\n\n\n
    On lines 64-67, we build the SQL statement using the JOOQ DSL. The statement is equivalent to SELECT * FROM hr.jobs_v ORDER BY job_id<\/code>.<\/p>\n\n\n\n
    Finally, on line 68 the function fetchAndPrint<\/code> is called. This function prints the query produced by JOOQ, all used bind variables and the query result. See lines 51, 59 and 60.<\/p>\n\n\n\n
    Simple Query – Java Program<\/span><\/path><\/path><\/svg><\/span>
    package<\/span> com.trivadis.jooq.pinkdb;<\/span><\/span>\n<\/span>\nimport<\/span> <\/span>static<\/span> com.trivadis.jooq.pinkdb.model.generated.hr.Tables.*;<\/span><\/span>\nimport<\/span> <\/span>static<\/span> org.jooq.impl.DSL.*;<\/span><\/span>\n<\/span>\nimport<\/span> com.trivadis.jooq.pinkdb.model.generated.sys.packages.DbmsMonitor;<\/span><\/span>\n<\/span>\nimport<\/span> java.sql.Connection;<\/span><\/span>\nimport<\/span> java.sql.DriverManager;<\/span><\/span>\nimport<\/span> java.sql.SQLException;<\/span><\/span>\nimport<\/span> java.util.List;<\/span><\/span>\n<\/span>\nimport<\/span> org.jooq.DSLContext;<\/span><\/span>\nimport<\/span> org.jooq.Result;<\/span><\/span>\nimport<\/span> org.jooq.ResultQuery;<\/span><\/span>\nimport<\/span> org.jooq.SQLDialect;<\/span><\/span>\nimport<\/span> org.jooq.conf.Settings;<\/span><\/span>\nimport<\/span> org.jooq.impl.DSL;<\/span><\/span>\n<\/span>\npublic<\/span> <\/span>class<\/span> <\/span>Main<\/span> {<\/span><\/span>\n   <\/span>private<\/span> <\/span>static<\/span> <\/span>DSLContext<\/span> <\/span>ctx<\/span>;<\/span><\/span>\n   <\/span>private<\/span> <\/span>static<\/span> <\/span>boolean<\/span> <\/span>sqlTrace<\/span> = <\/span>false<\/span>;<\/span><\/span>\n   <\/span><\/span>\n   <\/span>private<\/span> <\/span>static<\/span> <\/span>void<\/span> <\/span>initCtx<\/span>(<\/span>boolean<\/span> <\/span>withSqlTrace<\/span>) <\/span>throws<\/span> <\/span>SQLException<\/span> {<\/span><\/span>\n      <\/span>final<\/span> <\/span>Connection<\/span> <\/span>conn<\/span> = <\/span>DriverManager<\/span>.<\/span>getConnection<\/span>(<\/span><\/span>\n            <\/span>"jdbc:oracle:thin:@localhost:1521\/odb.docker"<\/span>, <\/span>"jooq"<\/span>, <\/span>"jooq"<\/span>);<\/span><\/span>\n      ctx = <\/span>DSL<\/span>.<\/span>using<\/span>(conn, <\/span>SQLDialect<\/span>.<\/span>ORACLE12C<\/span>, <\/span><\/span>\n            <\/span>new<\/span> <\/span>Settings<\/span>().<\/span>withRenderFormatted<\/span>(<\/span>true<\/span>).<\/span>withFetchSize<\/span>(<\/span>30<\/span>));<\/span><\/span>\n      sqlTrace = withSqlTrace;<\/span><\/span>\n      <\/span>enableSqlTrace<\/span>();<\/span><\/span>\n   }<\/span><\/span>\n   <\/span><\/span>\n   <\/span>private<\/span> <\/span>static<\/span> <\/span>void<\/span> <\/span>closeCtx<\/span>() <\/span>throws<\/span> <\/span>SQLException<\/span> {<\/span><\/span>\n      <\/span>disableSqlTrace<\/span>();<\/span><\/span>\n      <\/span>ctx<\/span>.<\/span>close<\/span>();<\/span><\/span>\n   }<\/span><\/span>\n   <\/span><\/span>\n   <\/span>private<\/span> <\/span>static<\/span> <\/span>void<\/span> <\/span>enableSqlTrace<\/span>() <\/span>throws<\/span> <\/span>SQLException<\/span> {<\/span><\/span>\n      <\/span>if<\/span> (sqlTrace) {<\/span><\/span>\n         <\/span>DbmsMonitor<\/span>.<\/span>sessionTraceEnable<\/span>(<\/span>ctx<\/span>.<\/span>configuration<\/span>(), <\/span>null<\/span>, <\/span>null<\/span>, <\/span>true<\/span>, <\/span>true<\/span>, <\/span>"all_executions"<\/span>);<\/span><\/span>\n      }<\/span><\/span>\n   }<\/span><\/span>\n<\/span>\n   <\/span>private<\/span> <\/span>static<\/span> <\/span>void<\/span> <\/span>disableSqlTrace<\/span>() <\/span>throws<\/span> <\/span>SQLException<\/span> {<\/span><\/span>\n      <\/span>if<\/span> (sqlTrace) {<\/span><\/span>\n         <\/span>DbmsMonitor<\/span>.<\/span>sessionTraceDisable<\/span>(<\/span>ctx<\/span>.<\/span>configuration<\/span>(), <\/span>null<\/span>, <\/span>null<\/span>);<\/span><\/span>\n      }<\/span><\/span>\n   }<\/span><\/span>\n   <\/span><\/span>\n   <\/span>private<\/span> <\/span>static<\/span> <\/span>void<\/span> <\/span>fetchAndPrint<\/span>(<\/span>String<\/span> <\/span>name<\/span>, <\/span>ResultQuery<\/span><<\/span>?<\/span>> <\/span>query<\/span>) {<\/span><\/span>\n      <\/span>System<\/span>.<\/span>out<\/span>.<\/span>println<\/span>(name + <\/span>": <\/span>\\n\\n<\/span>"<\/span> + <\/span>query<\/span>.<\/span>getSQL<\/span>());<\/span><\/span>\n      <\/span>final<\/span> <\/span>List<\/span><<\/span>Object<\/span>> <\/span>binds<\/span> = <\/span>query<\/span>.<\/span>getBindValues<\/span>();<\/span><\/span>\n      <\/span>if<\/span> (<\/span>binds<\/span>.<\/span>size<\/span>() > <\/span>0<\/span>) {<\/span><\/span>\n         <\/span>System<\/span>.<\/span>out<\/span>.<\/span>println<\/span>(<\/span>"<\/span>\\n<\/span>"<\/span> + name + <\/span>" binds:"<\/span>);<\/span><\/span>\n         <\/span>for<\/span> (<\/span>int<\/span> <\/span>i<\/span>=<\/span>0<\/span>; i<<\/span>binds<\/span>.<\/span>size<\/span>(); i++) {<\/span><\/span>\n            <\/span>System<\/span>.<\/span>out<\/span>.<\/span>println<\/span>(<\/span>"   "<\/span> + <\/span>":"<\/span> + (i+<\/span>1<\/span>) + <\/span>" = "<\/span> + <\/span>binds<\/span>.<\/span>get<\/span>(i));<\/span><\/span>\n         }<\/span><\/span>\n      }<\/span><\/span>\n      <\/span>final<\/span> <\/span>Result<\/span><<\/span>?<\/span>> <\/span>result<\/span> = <\/span>query<\/span>.<\/span>fetch<\/span>();<\/span><\/span>\n      <\/span>System<\/span>.<\/span>out<\/span>.<\/span>println<\/span>(<\/span>"<\/span>\\n<\/span>"<\/span> + name + <\/span>" result ("<\/span> + <\/span>result<\/span>.<\/span>size<\/span>() + <\/span>" rows): <\/span>\\n\\n<\/span>"<\/span> + <\/span>result<\/span>.<\/span>format<\/span>());            <\/span><\/span>\n   }<\/span><\/span>\n   <\/span><\/span>\n   <\/span>private<\/span> <\/span>static<\/span> <\/span>void<\/span> <\/span>queryJobs<\/span>() {<\/span><\/span>\n      <\/span>final<\/span> <\/span>ResultQuery<\/span><<\/span>?<\/span>> <\/span>query<\/span> = ctx<\/span><\/span>\n            .<\/span>select<\/span>()<\/span><\/span>\n            .<\/span>from<\/span>(JOBS_V)<\/span><\/span>\n            .<\/span>orderBy<\/span>(<\/span>JOBS_V<\/span>.<\/span>JOB_ID<\/span>);<\/span><\/span>\n      <\/span>fetchAndPrint<\/span>(<\/span>"Jobs"<\/span>, query);<\/span><\/span>\n   }<\/span><\/span>\n   <\/span><\/span>\n   <\/span>public<\/span> <\/span>static<\/span> <\/span>void<\/span> <\/span>main<\/span>(<\/span>String<\/span>[] <\/span>args<\/span>) <\/span>throws<\/span> <\/span>SQLException<\/span> {<\/span><\/span>\n      <\/span>initCtx<\/span>(<\/span>true<\/span>);<\/span><\/span>\n      <\/span>queryJobs<\/span>();<\/span><\/span>\n      <\/span>closeCtx<\/span>();<\/span><\/span>\n   }<\/span><\/span>\n}<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    Java Program Output (SQL Query & Result)<\/h3>\n\n\n\n
    The program produces this output:<\/p>\n\n\n\n
    Simple Query – SQL Query & Result<\/span><\/path><\/path><\/svg><\/span>
    Jobs: <\/span><\/span>\n<\/span><\/span>\nselect <\/span><\/span>\n  "HR"."JOBS_V"."JOB_ID", <\/span><\/span>\n  "HR"."JOBS_V"."JOB_TITLE", <\/span><\/span>\n  "HR"."JOBS_V"."MIN_SALARY", <\/span><\/span>\n  "HR"."JOBS_V"."MAX_SALARY"<\/span><\/span>\nfrom "HR"."JOBS_V"<\/span><\/span>\norder by "HR"."JOBS_V"."JOB_ID"<\/span><\/span>\n<\/span><\/span>\nJobs result (19 rows): <\/span><\/span>\n<\/span><\/span>\n+----------+-------------------------------+----------+----------+<\/span><\/span>\n|JOB_ID    |JOB_TITLE                      |MIN_SALARY|MAX_SALARY|<\/span><\/span>\n+----------+-------------------------------+----------+----------+<\/span><\/span>\n|AC_ACCOUNT|Public Accountant              |      4200|      9000|<\/span><\/span>\n|AC_MGR    |Accounting Manager             |      8200|     16000|<\/span><\/span>\n|AD_ASST   |Administration Assistant       |      3000|      6000|<\/span><\/span>\n|AD_PRES   |President                      |     20080|     40000|<\/span><\/span>\n|AD_VP     |Administration Vice President  |     15000|     30000|<\/span><\/span>\n|FI_ACCOUNT|Accountant                     |      4200|      9000|<\/span><\/span>\n|FI_MGR    |Finance Manager                |      8200|     16000|<\/span><\/span>\n|HR_REP    |Human Resources Representative |      4000|      9000|<\/span><\/span>\n|IT_PROG   |Programmer                     |      4000|     10000|<\/span><\/span>\n|MK_MAN    |Marketing Manager              |      9000|     15000|<\/span><\/span>\n|MK_REP    |Marketing Representative       |      4000|      9000|<\/span><\/span>\n|PR_REP    |Public Relations Representative|      4500|     10500|<\/span><\/span>\n|PU_CLERK  |Purchasing Clerk               |      2500|      5500|<\/span><\/span>\n|PU_MAN    |Purchasing Manager             |      8000|     15000|<\/span><\/span>\n|SA_MAN    |Sales Manager                  |     10000|     20080|<\/span><\/span>\n|SA_REP    |Sales Representative           |      6000|     12008|<\/span><\/span>\n|SH_CLERK  |Shipping Clerk                 |      2500|      5500|<\/span><\/span>\n|ST_CLERK  |Stock Clerk                    |      2008|      5000|<\/span><\/span>\n|ST_MAN    |Stock Manager                  |      5500|      8500|<\/span><\/span>\n+----------+-------------------------------+----------+----------+<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    Two things are interesting.<\/p>\n\n\n\n
    First, the generated SELECT<\/code> statement lists all columns, even if no column was defined in the query on program line 65.<\/p>\n\n\n\n
    Second, the output is formatted nicely. Where did this happen? On line 59 the query is executed and the result is saved in the local variable named\u00a0result<\/code>. This is a result set and contains all rows. For example, you may get the number of rows via result.size()<\/code>, as on line 60. I may also loop through the result set and do whatever I want. In this case, I just used a convenience function format<\/code> to format the result set as text. There are other convenience functions such as formatCSV<\/code>, formatHTML<\/code>, formatJSON<\/code> or formatXML<\/code>.\u00a0 You may guess by the name what they are doing. Nice!<\/p>\n\n\n\n
    SQL Trace Output<\/h3>\n\n\n\n
    The Java program produces a SQL Trace file by default. See program line 40. Here’s the relevant tkprof<\/code> excerpt:<\/p>\n\n\n\n
    Simple Query – TKPROF excerpt<\/span><\/path><\/path><\/svg><\/span>
    call     count       cpu    elapsed       disk      query    current        rows<\/span><\/span>\n------- ------  -------- ---------- ---------- ---------- ----------  ----------<\/span><\/span>\nParse        1      0.03       0.18          0          0          0           0<\/span><\/span>\nExecute      1      0.00       0.00          0          0          0           0<\/span><\/span>\nFetch        1      0.00       0.00          0          2          0          19<\/span><\/span>\n------- ------  -------- ---------- ---------- ---------- ----------  ----------<\/span><\/span>\ntotal        3      0.03       0.18          0          2          0          19<\/span><\/span>\n<\/span><\/span>\nMisses in library cache during parse: 1<\/span><\/span>\nOptimizer mode: ALL_ROWS<\/span><\/span>\nParsing user id: 133  <\/span><\/span>\nNumber of plan statistics captured: 1<\/span><\/span>\n<\/span><\/span>\nRows (1st) Rows (avg) Rows (max)  Row Source Operation<\/span><\/span>\n---------- ---------- ----------  ---------------------------------------------------<\/span><\/span>\n        19         19         19  TABLE ACCESS BY INDEX ROWID JOBS (cr=2 pr=0 pw=0 time=45 us starts=1 cost=2 size=627 card=19)<\/span><\/span>\n        19         19         19   INDEX FULL SCAN JOB_ID_PK (cr=1 pr=0 pw=0 time=18 us starts=1 cost=1 size=0 card=19)(object id 78619)<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\nElapsed times include waiting on following events:<\/span><\/span>\n  Event waited on                             Times   Max. Wait  Total Waited<\/span><\/span>\n  ----------------------------------------   Waited  ----------  ------------<\/span><\/span>\n  SQL*Net message to client                       1        0.00          0.00<\/span><\/span>\n  SQL*Net message from client                     1        0.10          0.10<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    Interesting is line 5 of the output. There was just one fetch to retrieve 19 rows. A default configuration of the JDBC driver would have caused two fetches. Our configuration on program line 28 worked. All data fetched in a single network roundtrip.<\/p>\n\n\n\n
    <\/a>8. Using Joins & Aggregations<\/h2>\n\n\n\n
    In the next example we query the salaries per location. The idea is to look at a JOOQ query with joins and aggregations.<\/p>\n\n\n\n
    JOOQ Query<\/h3>\n\n\n\n
    On lines 6-8, you see how aggregate functions are used in JOOQ and how to define an alias for the resulting column.<\/p>\n\n\n\n
    Line 10 is interesting. It defines the join using the onKey<\/code> function. This function requires a parameter when multiple join paths are possible. In this case EMPLOYEE_V<\/code> and DEPARTMENTS_V<\/code> may be joined either via DEPARTMENTS_V.MANAGER_ID<\/code> or via EMPLOYEES_V.DEPARTMENT_ID<\/code>. In this case we defined the latter.<\/p>\n\n\n\n
    On line 11 the onKey<\/code> function has no parameters, since only one join path exists to LOCATIONS<\/code>. This clearly shows how JOOQ uses our referential integrity constraints on the view-API to build the query. The idea is similar to\u00a0a NATURAL JOIN<\/code>, but the implementation is better since it relies on constraints and not naming conventions. I already miss this feature in SQL.<\/p>\n\n\n\n
    The use of the functions groupBy<\/code> and orderBy<\/code> on line 14 and 15 should be self-explanatory.<\/p>\n\n\n\n
    Joins & Aggregations – JOOQ Query<\/span><\/path><\/path><\/svg><\/span>
       <\/span>private<\/span> <\/span>static<\/span> <\/span>void<\/span> <\/span>querySalariesPerLocation<\/span>() {<\/span><\/span>\n      <\/span>final<\/span> <\/span>ResultQuery<\/span><<\/span>?<\/span>> <\/span>query<\/span> = ctx<\/span><\/span>\n            .<\/span>select<\/span>(<\/span>REGIONS_V<\/span>.<\/span>REGION_NAME<\/span>,<\/span><\/span>\n                  <\/span>COUNTRIES_V<\/span>.<\/span>COUNTRY_NAME<\/span>,<\/span><\/span>\n                  <\/span>LOCATIONS_V<\/span>.<\/span>CITY<\/span>,<\/span><\/span>\n                  <\/span>count<\/span>().<\/span>as<\/span>(<\/span>"employees"<\/span>),<\/span><\/span>\n                  <\/span>sum<\/span>(<\/span>EMPLOYEES_V<\/span>.<\/span>SALARY<\/span>).<\/span>as<\/span>(<\/span>"sum_salaray"<\/span>),<\/span><\/span>\n                  <\/span>max<\/span>(<\/span>EMPLOYEES_V<\/span>.<\/span>SALARY<\/span>).<\/span>as<\/span>(<\/span>"max_salary"<\/span>))<\/span><\/span>\n            .<\/span>from<\/span>(EMPLOYEES_V)<\/span><\/span>\n            .<\/span>join<\/span>(DEPARTMENTS_V).<\/span>onKey<\/span>(<\/span>EMPLOYEES_V<\/span>.<\/span>DEPARTMENT_ID<\/span>)<\/span><\/span>\n            .<\/span>join<\/span>(LOCATIONS_V).<\/span>onKey<\/span>()<\/span><\/span>\n            .<\/span>join<\/span>(COUNTRIES_V).<\/span>onKey<\/span>()<\/span><\/span>\n            .<\/span>join<\/span>(REGIONS_V).<\/span>onKey<\/span>()<\/span><\/span>\n            .<\/span>groupBy<\/span>(<\/span>REGIONS_V<\/span>.<\/span>REGION_NAME<\/span>, <\/span>COUNTRIES_V<\/span>.<\/span>COUNTRY_NAME<\/span>, <\/span>LOCATIONS_V<\/span>.<\/span>CITY<\/span>)<\/span><\/span>\n            .<\/span>orderBy<\/span>(<\/span>REGIONS_V<\/span>.<\/span>REGION_NAME<\/span>, <\/span>COUNTRIES_V<\/span>.<\/span>COUNTRY_NAME<\/span>, <\/span>LOCATIONS_V<\/span>.<\/span>CITY<\/span>);<\/span><\/span>\n      <\/span>fetchAndPrint<\/span>(<\/span>"Salaries Per Location"<\/span>, query);<\/span><\/span>\n   }<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    SQL Query & Result<\/h3>\n\n\n\n
    No surprises. The query looks as expected.<\/p>\n\n\n\n
    Joins & Aggregations – SQL Query & Result<\/span><\/path><\/path><\/svg><\/span>
    Salaries Per Location: <\/span><\/span>\n<\/span><\/span>\nselect <\/span><\/span>\n  "HR"."REGIONS_V"."REGION_NAME", <\/span><\/span>\n  "HR"."COUNTRIES_V"."COUNTRY_NAME", <\/span><\/span>\n  "HR"."LOCATIONS_V"."CITY", <\/span><\/span>\n  count(*) "employees", <\/span><\/span>\n  sum("HR"."EMPLOYEES_V"."SALARY") "sum_salaray", <\/span><\/span>\n  max("HR"."EMPLOYEES_V"."SALARY") "max_salary"<\/span><\/span>\nfrom "HR"."EMPLOYEES_V"<\/span><\/span>\n  join "HR"."DEPARTMENTS_V"<\/span><\/span>\n  on "HR"."EMPLOYEES_V"."DEPARTMENT_ID" = "HR"."DEPARTMENTS_V"."DEPARTMENT_ID"<\/span><\/span>\n  join "HR"."LOCATIONS_V"<\/span><\/span>\n  on "HR"."DEPARTMENTS_V"."LOCATION_ID" = "HR"."LOCATIONS_V"."LOCATION_ID"<\/span><\/span>\n  join "HR"."COUNTRIES_V"<\/span><\/span>\n  on "HR"."LOCATIONS_V"."COUNTRY_ID" = "HR"."COUNTRIES_V"."COUNTRY_ID"<\/span><\/span>\n  join "HR"."REGIONS_V"<\/span><\/span>\n  on "HR"."COUNTRIES_V"."REGION_ID" = "HR"."REGIONS_V"."REGION_ID"<\/span><\/span>\ngroup by <\/span><\/span>\n  "HR"."REGIONS_V"."REGION_NAME", <\/span><\/span>\n  "HR"."COUNTRIES_V"."COUNTRY_NAME", <\/span><\/span>\n  "HR"."LOCATIONS_V"."CITY"<\/span><\/span>\norder by <\/span><\/span>\n  "HR"."REGIONS_V"."REGION_NAME", <\/span><\/span>\n  "HR"."COUNTRIES_V"."COUNTRY_NAME", <\/span><\/span>\n  "HR"."LOCATIONS_V"."CITY"<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\nSalaries Per Location result (7 rows): <\/span><\/span>\n<\/span><\/span>\n+-----------+------------------------+-------------------+---------+-----------+----------+<\/span><\/span>\n|REGION_NAME|COUNTRY_NAME            |CITY               |employees|sum_salaray|max_salary|<\/span><\/span>\n+-----------+------------------------+-------------------+---------+-----------+----------+<\/span><\/span>\n|Americas   |Canada                  |Toronto            |        2|      19000|     13000|<\/span><\/span>\n|Americas   |United States of America|Seattle            |       18|     159216|     24000|<\/span><\/span>\n|Americas   |United States of America|South San Francisco|       45|     156400|      8200|<\/span><\/span>\n|Americas   |United States of America|Southlake          |        5|      28800|      9000|<\/span><\/span>\n|Europe     |Germany                 |Munich             |        1|      10000|     10000|<\/span><\/span>\n|Europe     |United Kingdom          |London             |        1|       6500|      6500|<\/span><\/span>\n|Europe     |United Kingdom          |Oxford             |       34|     304500|     14000|<\/span><\/span>\n+-----------+------------------------+-------------------+---------+-----------+----------+<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    SQL Trace Output<\/h3>\n\n\n\n
    In the tkprof<\/code> output we see that parsing took more than a second. This is a bit too long for this kind of query. However, that is not a problem of JOOQ. Beside that, the output looks good. Single network roundtrip as expected.<\/p>\n\n\n\n
    Joins & Aggregations – TKPROF excerpt<\/span><\/path><\/path><\/svg><\/span>
    call     count       cpu    elapsed       disk      query    current        rows<\/span><\/span>\n------- ------  -------- ---------- ---------- ---------- ----------  ----------<\/span><\/span>\nParse        1      0.24       1.09          0          0          0           0<\/span><\/span>\nExecute      1      0.00       0.00          0          0          0           0<\/span><\/span>\nFetch        1      0.00       0.00          0         22          0           7<\/span><\/span>\n------- ------  -------- ---------- ---------- ---------- ----------  ----------<\/span><\/span>\ntotal        3      0.25       1.09          0         22          0           7<\/span><\/span>\n<\/span><\/span>\nMisses in library cache during parse: 1<\/span><\/span>\nOptimizer mode: ALL_ROWS<\/span><\/span>\nParsing user id: 133  <\/span><\/span>\nNumber of plan statistics captured: 1<\/span><\/span>\n<\/span><\/span>\nRows (1st) Rows (avg) Rows (max)  Row Source Operation<\/span><\/span>\n---------- ---------- ----------  ---------------------------------------------------<\/span><\/span>\n         7          7          7  SORT GROUP BY (cr=24 pr=0 pw=0 time=8817 us starts=1 cost=12 size=5512 card=106)<\/span><\/span>\n       106        106        106   HASH JOIN  (cr=24 pr=0 pw=0 time=8745 us starts=1 cost=11 size=5512 card=106)<\/span><\/span>\n        27         27         27    MERGE JOIN  (cr=16 pr=0 pw=0 time=294 us starts=1 cost=8 size=1215 card=27)<\/span><\/span>\n         3          3          3     TABLE ACCESS BY INDEX ROWID REGIONS (cr=2 pr=0 pw=0 time=43 us starts=1 cost=2 size=56 card=4)<\/span><\/span>\n         3          3          3      INDEX FULL SCAN REG_ID_PK (cr=1 pr=0 pw=0 time=11 us starts=1 cost=1 size=0 card=4)(object id 78609)<\/span><\/span>\n        27         27         27     SORT JOIN (cr=14 pr=0 pw=0 time=251 us starts=3 cost=6 size=837 card=27)<\/span><\/span>\n        27         27         27      VIEW  VW_GBF_23 (cr=14 pr=0 pw=0 time=328 us starts=1 cost=5 size=837 card=27)<\/span><\/span>\n        27         27         27       NESTED LOOPS  (cr=14 pr=0 pw=0 time=325 us starts=1 cost=5 size=999 card=27)<\/span><\/span>\n        27         27         27        MERGE JOIN  (cr=10 pr=0 pw=0 time=238 us starts=1 cost=5 size=594 card=27)<\/span><\/span>\n        19         19         19         TABLE ACCESS BY INDEX ROWID LOCATIONS (cr=2 pr=0 pw=0 time=32 us starts=1 cost=2 size=345 card=23)<\/span><\/span>\n        19         19         19          INDEX FULL SCAN LOC_ID_PK (cr=1 pr=0 pw=0 time=26 us starts=1 cost=1 size=0 card=23)(object id 78613)<\/span><\/span>\n        27         27         27         SORT JOIN (cr=8 pr=0 pw=0 time=189 us starts=19 cost=3 size=189 card=27)<\/span><\/span>\n        27         27         27          VIEW  index$_join$_013 (cr=8 pr=0 pw=0 time=163 us starts=1 cost=2 size=189 card=27)<\/span><\/span>\n        27         27         27           HASH JOIN  (cr=8 pr=0 pw=0 time=162 us starts=1)<\/span><\/span>\n        27         27         27            INDEX FAST FULL SCAN DEPT_ID_PK (cr=4 pr=0 pw=0 time=35 us starts=1 cost=1 size=189 card=27)(object id 78616)<\/span><\/span>\n        27         27         27            INDEX FAST FULL SCAN DEPT_LOCATION_IX (cr=4 pr=0 pw=0 time=15 us starts=1 cost=1 size=189 card=27)(object id 78631)<\/span><\/span>\n        27         27         27        INDEX UNIQUE SCAN COUNTRY_C_ID_PK (cr=4 pr=0 pw=0 time=20 us starts=27 cost=0 size=15 card=1)(object id 78611)<\/span><\/span>\n       107        107        107    TABLE ACCESS FULL EMPLOYEES (cr=6 pr=0 pw=0 time=35 us starts=1 cost=3 size=749 card=107)<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\nElapsed times include waiting on following events:<\/span><\/span>\n  Event waited on                             Times   Max. Wait  Total Waited<\/span><\/span>\n  ----------------------------------------   Waited  ----------  ------------<\/span><\/span>\n  SQL*Net message to client                       1        0.00          0.00<\/span><\/span>\n  PGA memory operation                            1        0.00          0.00<\/span><\/span>\n  SQL*Net message from client                     1        0.01          0.01<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    <\/a>9. Using Bind Variables<\/h2>\n\n\n\n
    Parsing can be costly, as we have seen in the previous example. Using bind variables can reduce parsing. In this case I’d like to see how I can avoid hard- and soft-parsing. See Oracle FAQs<\/a> for good parsing definitions.<\/p>\n\n\n\n
    JOOQ Query<\/h3>\n\n\n\n
    JOOQ automatically creates bind variables for the usages of\u00a0fromSalary<\/code> and toSalary<\/code>\u00a0on line 23. This means that JOOQ eliminates unnecessary hard parses by design. To eliminate unnecessary soft-parses we have to ensure that the Java statement behind the scenes is not closed. JOOQ provides the keepStatement<\/code> function for that purpose as used on line 25. When using this function we are responsible to close the statement. We do so on line 7.<\/p>\n\n\n\n
    We call the query twice. See lines 36 and 37. On the first call, the bind variables are set automatically by JOOQ when building the preparedQuery<\/code>. On subsequent calls the preparedQuery<\/code>\u00a0is reused and its bind variable values are changed. See line 27 and 28. Fetching and printing works as for the other JOOQ queries. The only difference is, that the statement is not closed after the last row is fetched.<\/p>\n\n\n\n
    Bind Variables – JOOQ Query<\/span><\/path><\/path><\/svg><\/span>
    public<\/span> <\/span>class<\/span> <\/span>Main<\/span> {<\/span><\/span>\n   <\/span>private<\/span> <\/span>static<\/span> <\/span>ResultQuery<\/span><<\/span>?<\/span>> <\/span>preparedQuery<\/span>;<\/span><\/span>\n   (...)<\/span><\/span>\n   <\/span>private<\/span> <\/span>static<\/span> <\/span>void<\/span> <\/span>closeCtx<\/span>() <\/span>throws<\/span> <\/span>SQLException<\/span> {<\/span><\/span>\n      <\/span>disableSqlTrace<\/span>();<\/span><\/span>\n      <\/span>if<\/span> (preparedQuery != <\/span>null<\/span>) {<\/span><\/span>\n         <\/span>preparedQuery<\/span>.<\/span>close<\/span>();<\/span><\/span>\n      }<\/span><\/span>\n      <\/span>ctx<\/span>.<\/span>close<\/span>();<\/span><\/span>\n   }<\/span><\/span>\n   (...)<\/span><\/span>\n   <\/span>private<\/span> <\/span>static<\/span> <\/span>void<\/span> <\/span>queryEmployeesInSalaryRange<\/span>(<\/span>BigDecimal<\/span> <\/span>fromSalary<\/span>, <\/span>BigDecimal<\/span> <\/span>toSalary<\/span>) {<\/span><\/span>\n      <\/span>if<\/span> (preparedQuery == <\/span>null<\/span>) {<\/span><\/span>\n         preparedQuery = ctx<\/span><\/span>\n               .<\/span>select<\/span>(<\/span>JOBS_V<\/span>.<\/span>JOB_TITLE<\/span>,<\/span><\/span>\n                     <\/span>EMPLOYEES_V<\/span>.<\/span>LAST_NAME<\/span>,<\/span><\/span>\n                     <\/span>EMPLOYEES_V<\/span>.<\/span>FIRST_NAME<\/span>,<\/span><\/span>\n                     <\/span>EMPLOYEES_V<\/span>.<\/span>SALARY<\/span>,<\/span><\/span>\n                     <\/span>JOBS_V<\/span>.<\/span>MIN_SALARY<\/span>,<\/span><\/span>\n                     <\/span>JOBS_V<\/span>.<\/span>MAX_SALARY<\/span>)<\/span><\/span>\n               .<\/span>from<\/span>(EMPLOYEES_V)<\/span><\/span>\n               .<\/span>join<\/span>(JOBS_V).<\/span>onKey<\/span>()<\/span><\/span>\n               .<\/span>where<\/span>(<\/span>EMPLOYEES_V<\/span>.<\/span>SALARY<\/span>.<\/span>between<\/span>(fromSalary, toSalary))<\/span><\/span>\n               .<\/span>orderBy<\/span>(<\/span>EMPLOYEES_V<\/span>.<\/span>SALARY<\/span>.<\/span>desc<\/span>())<\/span><\/span>\n               .<\/span>keepStatement<\/span>(<\/span>true<\/span>);<\/span><\/span>\n      } <\/span>else<\/span> {<\/span><\/span>\n         <\/span>preparedQuery<\/span>.<\/span>bind<\/span>(<\/span>1<\/span>, fromSalary);<\/span><\/span>\n         <\/span>preparedQuery<\/span>.<\/span>bind<\/span>(<\/span>2<\/span>, toSalary);<\/span><\/span>\n      }<\/span><\/span>\n      <\/span>fetchAndPrint<\/span>(<\/span>"Employees in Salary Range"<\/span>, preparedQuery);<\/span><\/span>\n   }<\/span><\/span>\n   (...)<\/span><\/span>\n   <\/span>public<\/span> <\/span>static<\/span> <\/span>void<\/span> <\/span>main<\/span>(<\/span>String<\/span>[] <\/span>args<\/span>) <\/span>throws<\/span> <\/span>SQLException<\/span> {<\/span><\/span>\n      <\/span>initCtx<\/span>(<\/span>true<\/span>);<\/span><\/span>\n      (...)<\/span><\/span>\n      <\/span>queryEmployeesInSalaryRange<\/span>(<\/span>BigDecimal<\/span>.<\/span>valueOf<\/span>(<\/span>13000<\/span>), <\/span>BigDecimal<\/span>.<\/span>valueOf<\/span>(<\/span>100000<\/span>));<\/span><\/span>\n      <\/span>queryEmployeesInSalaryRange<\/span>(<\/span>BigDecimal<\/span>.<\/span>valueOf<\/span>(<\/span>10000<\/span>), <\/span>BigDecimal<\/span>.<\/span>valueOf<\/span>(<\/span>13000<\/span>));<\/span><\/span>\n      <\/span>closeCtx<\/span>();<\/span><\/span>\n   }<\/span><\/span>\n}<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    SQL Query & Result<\/h3>\n\n\n\n
    We are executing the query twice. Hence we see two output sets.<\/p>\n\n\n\n
    On lines 13\/45, you see the bind variable placeholders (?).<\/p>\n\n\n\n
    On lines 17-18\/49-50, you see the values of the bind variables.<\/p>\n\n\n\n
    Plaintext<\/span><\/path><\/path><\/svg><\/span>
    Employees in Salary Range: <\/span><\/span>\n<\/span><\/span>\nselect <\/span><\/span>\n  "HR"."JOBS_V"."JOB_TITLE", <\/span><\/span>\n  "HR"."EMPLOYEES_V"."LAST_NAME", <\/span><\/span>\n  "HR"."EMPLOYEES_V"."FIRST_NAME", <\/span><\/span>\n  "HR"."EMPLOYEES_V"."SALARY", <\/span><\/span>\n  "HR"."JOBS_V"."MIN_SALARY", <\/span><\/span>\n  "HR"."JOBS_V"."MAX_SALARY"<\/span><\/span>\nfrom "HR"."EMPLOYEES_V"<\/span><\/span>\n  join "HR"."JOBS_V"<\/span><\/span>\n  on "HR"."EMPLOYEES_V"."JOB_ID" = "HR"."JOBS_V"."JOB_ID"<\/span><\/span>\nwhere "HR"."EMPLOYEES_V"."SALARY" between ? and ?<\/span><\/span>\norder by "HR"."EMPLOYEES_V"."SALARY" desc<\/span><\/span>\n<\/span><\/span>\nEmployees in Salary Range binds:<\/span><\/span>\n   :1 = 13000<\/span><\/span>\n   :2 = 100000<\/span><\/span>\n<\/span><\/span>\nEmployees in Salary Range result (6 rows): <\/span><\/span>\n<\/span><\/span>\n+-----------------------------+---------+----------+------+----------+----------+<\/span><\/span>\n|JOB_TITLE                    |LAST_NAME|FIRST_NAME|SALARY|MIN_SALARY|MAX_SALARY|<\/span><\/span>\n+-----------------------------+---------+----------+------+----------+----------+<\/span><\/span>\n|President                    |King     |Steven    | 24000|     20080|     40000|<\/span><\/span>\n|Administration Vice President|Kochhar  |Neena     | 17000|     15000|     30000|<\/span><\/span>\n|Administration Vice President|De Haan  |Lex       | 17000|     15000|     30000|<\/span><\/span>\n|Sales Manager                |Russell  |John      | 14000|     10000|     20080|<\/span><\/span>\n|Sales Manager                |Partners |Karen     | 13500|     10000|     20080|<\/span><\/span>\n|Marketing Manager            |Hartstein|Michael   | 13000|      9000|     15000|<\/span><\/span>\n+-----------------------------+---------+----------+------+----------+----------+<\/span><\/span>\n<\/span><\/span>\nEmployees in Salary Range: <\/span><\/span>\n<\/span><\/span>\nselect <\/span><\/span>\n  "HR"."JOBS_V"."JOB_TITLE", <\/span><\/span>\n  "HR"."EMPLOYEES_V"."LAST_NAME", <\/span><\/span>\n  "HR"."EMPLOYEES_V"."FIRST_NAME", <\/span><\/span>\n  "HR"."EMPLOYEES_V"."SALARY", <\/span><\/span>\n  "HR"."JOBS_V"."MIN_SALARY", <\/span><\/span>\n  "HR"."JOBS_V"."MAX_SALARY"<\/span><\/span>\nfrom "HR"."EMPLOYEES_V"<\/span><\/span>\n  join "HR"."JOBS_V"<\/span><\/span>\n  on "HR"."EMPLOYEES_V"."JOB_ID" = "HR"."JOBS_V"."JOB_ID"<\/span><\/span>\nwhere "HR"."EMPLOYEES_V"."SALARY" between ? and ?<\/span><\/span>\norder by "HR"."EMPLOYEES_V"."SALARY" desc<\/span><\/span>\n<\/span><\/span>\nEmployees in Salary Range binds:<\/span><\/span>\n   :1 = 10000<\/span><\/span>\n   :2 = 13000<\/span><\/span>\n<\/span><\/span>\nEmployees in Salary Range result (14 rows): <\/span><\/span>\n<\/span><\/span>\n+-------------------------------+---------+----------+------+----------+----------+<\/span><\/span>\n|JOB_TITLE                      |LAST_NAME|FIRST_NAME|SALARY|MIN_SALARY|MAX_SALARY|<\/span><\/span>\n+-------------------------------+---------+----------+------+----------+----------+<\/span><\/span>\n|Marketing Manager              |Hartstein|Michael   | 13000|      9000|     15000|<\/span><\/span>\n|Finance Manager                |Greenberg|Nancy     | 12008|      8200|     16000|<\/span><\/span>\n|Accounting Manager             |Higgins  |Shelley   | 12008|      8200|     16000|<\/span><\/span>\n|Sales Manager                  |Errazuriz|Alberto   | 12000|     10000|     20080|<\/span><\/span>\n|Sales Representative           |Ozer     |Lisa      | 11500|      6000|     12008|<\/span><\/span>\n|Purchasing Manager             |Raphaely |Den       | 11000|      8000|     15000|<\/span><\/span>\n|Sales Manager                  |Cambrault|Gerald    | 11000|     10000|     20080|<\/span><\/span>\n|Sales Representative           |Abel     |Ellen     | 11000|      6000|     12008|<\/span><\/span>\n|Sales Representative           |Vishney  |Clara     | 10500|      6000|     12008|<\/span><\/span>\n|Sales Manager                  |Zlotkey  |Eleni     | 10500|     10000|     20080|<\/span><\/span>\n|Sales Representative           |Tucker   |Peter     | 10000|      6000|     12008|<\/span><\/span>\n|Sales Representative           |Bloom    |Harrison  | 10000|      6000|     12008|<\/span><\/span>\n|Public Relations Representative|Baer     |Hermann   | 10000|      4500|     10500|<\/span><\/span>\n|Sales Representative           |King     |Janette   | 10000|      6000|     12008|<\/span><\/span>\n+-------------------------------+---------+----------+------+----------+----------+<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    SQL Trace Output<\/h3>\n\n\n\n
    The tkprof<\/code> excerpt on line 4 shows that the query is executed twice. Line 3 reveals that the query is parsed only once. The total of 20 rows are read in two network roundtrips. That’s perfect.<\/p>\n\n\n\n
    Bind Variables – TKPROF excerpt<\/span><\/path><\/path><\/svg><\/span>
    call     count       cpu    elapsed       disk      query    current        rows<\/span><\/span>\n------- ------  -------- ---------- ---------- ---------- ----------  ----------<\/span><\/span>\nParse        1      0.00       0.00          0          0          0           0<\/span><\/span>\nExecute      2      0.01       0.05          0          0          0           0<\/span><\/span>\nFetch        2      0.00       0.00          0         16          0          20<\/span><\/span>\n------- ------  -------- ---------- ---------- ---------- ----------  ----------<\/span><\/span>\ntotal        5      0.01       0.05          0         16          0          20<\/span><\/span>\n<\/span><\/span>\nMisses in library cache during parse: 1<\/span><\/span>\nMisses in library cache during execute: 1<\/span><\/span>\nOptimizer mode: ALL_ROWS<\/span><\/span>\nParsing user id: 133  <\/span><\/span>\nNumber of plan statistics captured: 2<\/span><\/span>\n<\/span><\/span>\nRows (1st) Rows (avg) Rows (max)  Row Source Operation<\/span><\/span>\n---------- ---------- ----------  ---------------------------------------------------<\/span><\/span>\n         6         10         14  SORT ORDER BY (cr=8 pr=0 pw=0 time=160 us starts=1 cost=7 size=366 card=6)<\/span><\/span>\n         6         10         14   FILTER  (cr=8 pr=0 pw=0 time=156 us starts=1)<\/span><\/span>\n         6         10         14    MERGE JOIN  (cr=8 pr=0 pw=0 time=153 us starts=1 cost=6 size=366 card=6)<\/span><\/span>\n        16         16         17     TABLE ACCESS BY INDEX ROWID JOBS (cr=2 pr=0 pw=0 time=38 us starts=1 cost=2 size=627 card=19)<\/span><\/span>\n        16         16         17      INDEX FULL SCAN JOB_ID_PK (cr=1 pr=0 pw=0 time=20 us starts=1 cost=1 size=0 card=19)(object id 78619)<\/span><\/span>\n         6         10         14     SORT JOIN (cr=6 pr=0 pw=0 time=94 us starts=16 cost=4 size=168 card=6)<\/span><\/span>\n         6         10         14      TABLE ACCESS FULL EMPLOYEES (cr=6 pr=0 pw=0 time=68 us starts=1 cost=3 size=168 card=6)<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\nElapsed times include waiting on following events:<\/span><\/span>\n  Event waited on                             Times   Max. Wait  Total Waited<\/span><\/span>\n  ----------------------------------------   Waited  ----------  ------------<\/span><\/span>\n  SQL*Net message to client                       2        0.00          0.00<\/span><\/span>\n  SQL*Net message from client                     2        0.00          0.00<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    <\/a>10. Run a Top N Query<\/h2>\n\n\n\n
    In the next example we query the 4 employees with the lowest salary along with some general salary information. It’ll be interesting to see if JOOQ uses Oracle’s native top-n query syntax.<\/p>\n\n\n\n
    JOOQ Query<\/h3>\n\n\n\n
    On line 8 we see that JOOQ has no problems dealing with analytic functions.<\/p>\n\n\n\n
    On line 14 the top n query is defined. Limit the result to 4 rows with ties, this means rows with the same order by values are considered as well.<\/p>\n\n\n\n
    Top N Query – JOOQ Query<\/span><\/path><\/path><\/svg><\/span>
       <\/span>private<\/span> <\/span>static<\/span> <\/span>void<\/span> <\/span>queryTopBadEarners<\/span>() {<\/span><\/span>\n       <\/span>final<\/span> <\/span>ResultQuery<\/span><<\/span>?<\/span>> <\/span>query<\/span> = ctx<\/span><\/span>\n            .<\/span>select<\/span>(<\/span>JOBS_V<\/span>.<\/span>JOB_TITLE<\/span>,<\/span><\/span>\n                  <\/span>EMPLOYEES_V<\/span>.<\/span>LAST_NAME<\/span>,<\/span><\/span>\n                  <\/span>EMPLOYEES_V<\/span>.<\/span>FIRST_NAME<\/span>,<\/span><\/span>\n                  <\/span>EMPLOYEES_V<\/span>.<\/span>HIRE_DATE<\/span>,<\/span><\/span>\n                  <\/span>EMPLOYEES_V<\/span>.<\/span>SALARY<\/span>,<\/span><\/span>\n                  <\/span>avg<\/span>(<\/span>EMPLOYEES_V<\/span>.<\/span>SALARY<\/span>).<\/span>over<\/span>().<\/span>partitionBy<\/span>(<\/span>EMPLOYEES_V<\/span>.<\/span>JOB_ID<\/span>).<\/span>as<\/span>(<\/span>"avg_salary"<\/span>),<\/span><\/span>\n                  <\/span>JOBS_V<\/span>.<\/span>MIN_SALARY<\/span>,<\/span><\/span>\n                  <\/span>JOBS_V<\/span>.<\/span>MAX_SALARY<\/span>)<\/span><\/span>\n            .<\/span>from<\/span>(EMPLOYEES_V)<\/span><\/span>\n            .<\/span>join<\/span>(JOBS_V).<\/span>onKey<\/span>()<\/span><\/span>\n            .<\/span>orderBy<\/span>(<\/span>EMPLOYEES_V<\/span>.<\/span>SALARY<\/span>)<\/span><\/span>\n            .<\/span>limit<\/span>(<\/span>4<\/span>).<\/span>withTies<\/span>();<\/span><\/span>\n      <\/span>fetchAndPrint<\/span>(<\/span>"Top 4 Bad Earners"<\/span>, query);<\/span><\/span>\n   }<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    SQL Query & Result<\/h3>\n\n\n\n
    The query crafted by JOOQ is a native Oracle top n query. See line 16. Very good.<\/p>\n\n\n\n
    Interesting is, that JOOQ also creates a bind variable for the literal 4. This could be good or bad. If you want to use a literal instead of a bind variable, you can simply use inline(4)<\/code>  instead of 4<\/code>. The default behaviour of JOOQ reminds a little of CURSOR_SHARING=FORCE<\/code>. But since you may control the behaviour on statement level, I think it is a good and sensible default.<\/p>\n\n\n\n
    We got a result of 5 rows, because Landry and Gee both have a salary of 2400. That’s the result of WITH TIES<\/code>.<\/p>\n\n\n\n
    Top N Query – SQL Query & Result<\/span><\/path><\/path><\/svg><\/span>
    Top 4 Bad Earners: <\/span><\/span>\n<\/span><\/span>\nselect <\/span><\/span>\n  "HR"."JOBS_V"."JOB_TITLE", <\/span><\/span>\n  "HR"."EMPLOYEES_V"."LAST_NAME", <\/span><\/span>\n  "HR"."EMPLOYEES_V"."FIRST_NAME", <\/span><\/span>\n  "HR"."EMPLOYEES_V"."HIRE_DATE", <\/span><\/span>\n  "HR"."EMPLOYEES_V"."SALARY", <\/span><\/span>\n  avg("HR"."EMPLOYEES_V"."SALARY") over (partition by "HR"."EMPLOYEES_V"."JOB_ID") "avg_salary", <\/span><\/span>\n  "HR"."JOBS_V"."MIN_SALARY", <\/span><\/span>\n  "HR"."JOBS_V"."MAX_SALARY"<\/span><\/span>\nfrom "HR"."EMPLOYEES_V"<\/span><\/span>\n  join "HR"."JOBS_V"<\/span><\/span>\n  on "HR"."EMPLOYEES_V"."JOB_ID" = "HR"."JOBS_V"."JOB_ID"<\/span><\/span>\norder by "HR"."EMPLOYEES_V"."SALARY"<\/span><\/span>\nfetch next ? rows with ties<\/span><\/span>\n<\/span><\/span>\nTop 4 Bad Earners binds:<\/span><\/span>\n   :1 = 4<\/span><\/span>\n<\/span><\/span>\nTop 4 Bad Earners result (5 rows): <\/span><\/span>\n<\/span><\/span>\n+-----------+----------+----------+----------+------+----------+----------+----------+<\/span><\/span>\n|JOB_TITLE  |LAST_NAME |FIRST_NAME|HIRE_DATE |SALARY|avg_salary|MIN_SALARY|MAX_SALARY|<\/span><\/span>\n+-----------+----------+----------+----------+------+----------+----------+----------+<\/span><\/span>\n|Stock Clerk|Olson     |TJ        |2007-04-10|  2100|      2785|      2008|      5000|<\/span><\/span>\n|Stock Clerk|Markle    |Steven    |2008-03-08|  2200|      2785|      2008|      5000|<\/span><\/span>\n|Stock Clerk|Philtanker|Hazel     |2008-02-06|  2200|      2785|      2008|      5000|<\/span><\/span>\n|Stock Clerk|Landry    |James     |2007-01-14|  2400|      2785|      2008|      5000|<\/span><\/span>\n|Stock Clerk|Gee       |Ki        |2007-12-12|  2400|      2785|      2008|      5000|<\/span><\/span>\n+-----------+----------+----------+----------+------+----------+----------+----------+<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    SQL Trace Output<\/h3>\n\n\n\n
    The tkprof<\/code> excerpt looks good. Good performance, single network roundtrip.<\/p>\n\n\n\n
    Top N Query – TKPROF excerpt<\/span><\/path><\/path><\/svg><\/span>
    call     count       cpu    elapsed       disk      query    current        rows<\/span><\/span>\n------- ------  -------- ---------- ---------- ---------- ----------  ----------<\/span><\/span>\nParse        1      0.00       0.00          0          0          0           0<\/span><\/span>\nExecute      1      0.00       0.00          0          0          0           0<\/span><\/span>\nFetch        1      0.00       0.00          0          8          0           5<\/span><\/span>\n------- ------  -------- ---------- ---------- ---------- ----------  ----------<\/span><\/span>\ntotal        3      0.00       0.01          0          8          0           5<\/span><\/span>\n<\/span><\/span>\nMisses in library cache during parse: 1<\/span><\/span>\nOptimizer mode: ALL_ROWS<\/span><\/span>\nParsing user id: 133  <\/span><\/span>\nNumber of plan statistics captured: 1<\/span><\/span>\n<\/span><\/span>\nRows (1st) Rows (avg) Rows (max)  Row Source Operation<\/span><\/span>\n---------- ---------- ----------  ---------------------------------------------------<\/span><\/span>\n         5          5          5  VIEW  (cr=8 pr=0 pw=0 time=335 us starts=1 cost=7 size=14124 card=107)<\/span><\/span>\n        11         11         11   WINDOW SORT PUSHED RANK (cr=8 pr=0 pw=0 time=326 us starts=1 cost=7 size=7383 card=107)<\/span><\/span>\n       107        107        107    WINDOW BUFFER (cr=8 pr=0 pw=0 time=260 us starts=1 cost=7 size=7383 card=107)<\/span><\/span>\n       107        107        107     MERGE JOIN  (cr=8 pr=0 pw=0 time=634 us starts=1 cost=6 size=7383 card=107)<\/span><\/span>\n        19         19         19      TABLE ACCESS BY INDEX ROWID JOBS (cr=2 pr=0 pw=0 time=42 us starts=1 cost=2 size=627 card=19)<\/span><\/span>\n        19         19         19       INDEX FULL SCAN JOB_ID_PK (cr=1 pr=0 pw=0 time=30 us starts=1 cost=1 size=0 card=19)(object id 78619)<\/span><\/span>\n       107        107        107      SORT JOIN (cr=6 pr=0 pw=0 time=97 us starts=19 cost=4 size=3852 card=107)<\/span><\/span>\n       107        107        107       TABLE ACCESS FULL EMPLOYEES (cr=6 pr=0 pw=0 time=28 us starts=1 cost=3 size=3852 card=107)<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\nElapsed times include waiting on following events:<\/span><\/span>\n  Event waited on                             Times   Max. Wait  Total Waited<\/span><\/span>\n  ----------------------------------------   Waited  ----------  ------------<\/span><\/span>\n  SQL*Net message to client                       1        0.00          0.00<\/span><\/span>\n  SQL*Net message from client                     1        0.01          0.01<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    <\/a>11. Using Row Pattern Matching<\/h2>\n\n\n\n
    What about match_recognize<\/code>, does JOOQ support that? No, not currently. JOOQ has some limitations regarding SQL support. match_recognize<\/code> is one and the model_clause<\/code> is another. This is something to be expected, since the SQL grammar is still evolving. However, the question is, how do we deal with queries that must apply some SQL which cannot be created by JOOQ’s query builder? The solution is simple. We simply pass the pure SQL to JOOQ.<\/p>\n\n\n\n
     JOOQ Query<\/h3>\n\n\n\n
    There are other ways to produce the same result without using match_recognize<\/code>. However, if you would like to use JOOQ and match_recognize<\/code> then you have to build the SQL yourself and pass it to JOOQ as on line 58.<\/p>\n\n\n\n
    It’s sad that Java still does not support multiline strings. The code is much simpler in other JVM languages that support multiline strings, such as Scala, Groovy, Kotlin or Xtend.<\/p>\n\n\n\n
    Row Pattern Matching – JOOQ Query<\/span><\/path><\/path><\/svg><\/span>
       <\/span>private<\/span> <\/span>static<\/span> <\/span>void<\/span> <\/span>queryBestPayedNewEntries<\/span>() {<\/span><\/span>\n      <\/span>\/\/ using {{\\\\??\\\\}} instead of "??" to ensure JDBC does not interpret questions marks as a bind variable placeholders<\/span><\/span>\n      <\/span>\/\/ see https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/18\/jjdbc\/JDBC-reference-information.html#GUID-3454411C-5F24-4D46-83A9-5DA0BA704F5D<\/span><\/span>\n      <\/span>\/\/ documentation is wrong, escaping is required.<\/span><\/span>\n      <\/span>StringBuffer<\/span> <\/span>sb<\/span> = <\/span>new<\/span> <\/span>StringBuffer<\/span>();<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"WITH<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"   base AS (<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"      SELECT emp.employee_id,<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"             emp.last_name,<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"             emp.first_name,<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"             job.job_title,<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"             jhist.start_date,<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"             emp.salary<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"        FROM hr.employees_v emp<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"        JOIN hr.job_history_v jhist<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"          ON jhist.employee_id = emp.employee_id<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"        JOIN hr.jobs_v job<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"          ON job.job_id = jhist.job_id<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"      UNION<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"      SELECT emp.employee_id,<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"             emp.last_name,<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"             emp.first_name,<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"             job.job_title,<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"             emp.hire_date AS start_date,<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"             emp.salary<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"        FROM hr.employees_v emp<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"        JOIN hr.jobs_v job<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"          ON job.job_id = emp.job_id<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"   ),<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"   aggr AS (<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"      SELECT employee_id,<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"             last_name,<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"             first_name,<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"             job_title,<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"             MAX(start_date) AS start_date,<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"             salary,<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"             MAX(salary) OVER (PARTITION BY job_title ORDER BY MAX(start_date)<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS max_salary<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"        FROM base<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"       GROUP BY employee_id, last_name, first_name, job_title, salary<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"   )<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"SELECT job_title, start_date, last_name, first_name, salary<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"  FROM aggr MATCH_RECOGNIZE (<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"          PARTITION BY job_title<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"          ORDER BY start_date<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"          MEASURES LAST(employee_id) AS employee_id,<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"                   LAST(last_name) AS last_name,<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"                   LAST(first_name) AS first_name,<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"                   LAST(start_date) AS start_date,<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"                   LAST(salary) AS salary<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"          ONE ROW PER MATCH<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"          PATTERN((strt down*){{<\/span>\\\\<\/span>??<\/span>\\\\<\/span>}} up)<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"          DEFINE strt AS salary = MAX(max_salary),<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"                 down AS salary < MAX(max_salary),<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"                 up AS salary = MAX(max_salary)<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>"       )<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n      <\/span>sb<\/span>.<\/span>append<\/span>(<\/span>" ORDER BY job_title, start_date"<\/span>);<\/span><\/span>\n      <\/span>final<\/span> <\/span>ResultQuery<\/span><<\/span>Record<\/span>> <\/span>query<\/span> = <\/span>ctx<\/span>.<\/span>resultQuery<\/span>(<\/span>sb<\/span>.<\/span>toString<\/span>());<\/span><\/span>\n      <\/span>fetchAndPrint<\/span>(<\/span>"Best Payed New Entries"<\/span>, query);<\/span><\/span>\n   }<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    SQL Query & Result<\/h3>\n\n\n\n
    On line 49 you see that {\\??\\}<\/code>  is used instead of ??<\/code>. This is a necessity for the JDBC driver. Otherwise, the driver would expect bind variables for these question marks. All Java-based tools such as SQL Developer and SQLcl do have the same “problem”.<\/p>\n\n\n\n
    Row Pattern Matching – SQL Query & Result<\/span><\/path><\/path><\/svg><\/span>
    Best Payed New Entries: <\/span><\/span>\n<\/span><\/span>\nWITH<\/span><\/span>\n   base AS (<\/span><\/span>\n      SELECT emp.employee_id,<\/span><\/span>\n             emp.last_name,<\/span><\/span>\n             emp.first_name,<\/span><\/span>\n             job.job_title,<\/span><\/span>\n             jhist.start_date,<\/span><\/span>\n             emp.salary<\/span><\/span>\n        FROM hr.employees_v emp<\/span><\/span>\n        JOIN hr.job_history_v jhist<\/span><\/span>\n          ON jhist.employee_id = emp.employee_id<\/span><\/span>\n        JOIN hr.jobs_v job<\/span><\/span>\n          ON job.job_id = jhist.job_id<\/span><\/span>\n      UNION<\/span><\/span>\n      SELECT emp.employee_id,<\/span><\/span>\n             emp.last_name,<\/span><\/span>\n             emp.first_name,<\/span><\/span>\n             job.job_title,<\/span><\/span>\n             emp.hire_date AS start_date,<\/span><\/span>\n             emp.salary<\/span><\/span>\n        FROM hr.employees_v emp<\/span><\/span>\n        JOIN hr.jobs_v job<\/span><\/span>\n          ON job.job_id = emp.job_id<\/span><\/span>\n   ),<\/span><\/span>\n   aggr AS (<\/span><\/span>\n      SELECT employee_id,<\/span><\/span>\n             last_name,<\/span><\/span>\n             first_name,<\/span><\/span>\n             job_title,<\/span><\/span>\n             MAX(start_date) AS start_date,<\/span><\/span>\n             salary,<\/span><\/span>\n             MAX(salary) OVER (PARTITION BY job_title ORDER BY MAX(start_date)<\/span><\/span>\n                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS max_salary<\/span><\/span>\n        FROM base<\/span><\/span>\n       GROUP BY employee_id, last_name, first_name, job_title, salary<\/span><\/span>\n   )<\/span><\/span>\nSELECT job_title, start_date, last_name, first_name, salary<\/span><\/span>\n  FROM aggr MATCH_RECOGNIZE (<\/span><\/span>\n          PARTITION BY job_title<\/span><\/span>\n          ORDER BY start_date<\/span><\/span>\n          MEASURES LAST(employee_id) AS employee_id,<\/span><\/span>\n                   LAST(last_name) AS last_name,<\/span><\/span>\n                   LAST(first_name) AS first_name,<\/span><\/span>\n                   LAST(start_date) AS start_date,<\/span><\/span>\n                   LAST(salary) AS salary<\/span><\/span>\n          ONE ROW PER MATCH<\/span><\/span>\n          PATTERN((strt down*){\\??\\} up)<\/span><\/span>\n          DEFINE strt AS salary = MAX(max_salary),<\/span><\/span>\n                 down AS salary < MAX(max_salary),<\/span><\/span>\n                 up AS salary = MAX(max_salary)<\/span><\/span>\n       )<\/span><\/span>\n ORDER BY job_title, start_date<\/span><\/span>\n<\/span><\/span>\nBest Payed New Entries result (25 rows): <\/span><\/span>\n<\/span><\/span>\n+-------------------------------+----------+---------+----------+------+<\/span><\/span>\n|JOB_TITLE                      |START_DATE|LAST_NAME|FIRST_NAME|SALARY|<\/span><\/span>\n+-------------------------------+----------+---------+----------+------+<\/span><\/span>\n|Accountant                     |2002-08-16|Faviet   |Daniel    |  9000|<\/span><\/span>\n|Accounting Manager             |2001-10-28|Kochhar  |Neena     | 17000|<\/span><\/span>\n|Administration Assistant       |2003-09-17|Whalen   |Jennifer  |  4400|<\/span><\/span>\n|Administration Vice President  |2001-01-13|De Haan  |Lex       | 17000|<\/span><\/span>\n|Administration Vice President  |2005-09-21|Kochhar  |Neena     | 17000|<\/span><\/span>\n|Finance Manager                |2002-08-17|Greenberg|Nancy     | 12008|<\/span><\/span>\n|Human Resources Representative |2002-06-07|Mavris   |Susan     |  6500|<\/span><\/span>\n|Marketing Manager              |2004-02-17|Hartstein|Michael   | 13000|<\/span><\/span>\n|Marketing Representative       |2004-02-17|Hartstein|Michael   | 13000|<\/span><\/span>\n|President                      |2003-06-17|King     |Steven    | 24000|<\/span><\/span>\n|Programmer                     |2001-01-13|De Haan  |Lex       | 17000|<\/span><\/span>\n|Public Accountant              |1997-09-21|Kochhar  |Neena     | 17000|<\/span><\/span>\n|Public Relations Representative|2002-06-07|Baer     |Hermann   | 10000|<\/span><\/span>\n|Purchasing Clerk               |2003-05-18|Khoo     |Alexander |  3100|<\/span><\/span>\n|Purchasing Manager             |2002-12-07|Raphaely |Den       | 11000|<\/span><\/span>\n|Sales Manager                  |2004-10-01|Russell  |John      | 14000|<\/span><\/span>\n|Sales Representative           |2004-01-30|King     |Janette   | 10000|<\/span><\/span>\n|Sales Representative           |2004-05-11|Abel     |Ellen     | 11000|<\/span><\/span>\n|Sales Representative           |2005-03-11|Ozer     |Lisa      | 11500|<\/span><\/span>\n|Shipping Clerk                 |2004-01-27|Sarchand |Nandita   |  4200|<\/span><\/span>\n|Stock Clerk                    |2003-07-14|Ladwig   |Renske    |  3600|<\/span><\/span>\n|Stock Clerk                    |2006-03-24|Raphaely |Den       | 11000|<\/span><\/span>\n|Stock Manager                  |2003-05-01|Kaufling |Payam     |  7900|<\/span><\/span>\n|Stock Manager                  |2004-07-18|Weiss    |Matthew   |  8000|<\/span><\/span>\n|Stock Manager                  |2005-04-10|Fripp    |Adam      |  8200|<\/span><\/span>\n+-------------------------------+----------+---------+----------+------+<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    SQL Trace Output<\/h3>\n\n\n\n
    The tkprof<\/code> excerpt shows that most of the time is spent parsing the query, but it is still fast. All 25 result rows are fetched in a single network roundtrip.<\/p>\n\n\n\n
    Row Pattern Matching – TKPROF excerpt<\/span><\/path><\/path><\/svg><\/span>
    call     count       cpu    elapsed       disk      query    current        rows<\/span><\/span>\n------- ------  -------- ---------- ---------- ---------- ----------  ----------<\/span><\/span>\nParse        1      0.01       0.20          0          0          0           0<\/span><\/span>\nExecute      1      0.00       0.00          0          0          0           0<\/span><\/span>\nFetch        1      0.00       0.00          0         22          0          25<\/span><\/span>\n------- ------  -------- ---------- ---------- ---------- ----------  ----------<\/span><\/span>\ntotal        3      0.01       0.20          0         22          0          25<\/span><\/span>\n<\/span><\/span>\nMisses in library cache during parse: 1<\/span><\/span>\nOptimizer mode: ALL_ROWS<\/span><\/span>\nParsing user id: 133  <\/span><\/span>\nNumber of plan statistics captured: 1<\/span><\/span>\n<\/span><\/span>\nRows (1st) Rows (avg) Rows (max)  Row Source Operation<\/span><\/span>\n---------- ---------- ----------  ---------------------------------------------------<\/span><\/span>\n        25         25         25  SORT ORDER BY (cr=22 pr=0 pw=0 time=1965 us starts=1 cost=22 size=7839 card=117)<\/span><\/span>\n        25         25         25   VIEW  (cr=22 pr=0 pw=0 time=2016 us starts=1 cost=21 size=7839 card=117)<\/span><\/span>\n        25         25         25    MATCH RECOGNIZE SORT (cr=22 pr=0 pw=0 time=2016 us starts=1 cost=21 size=9360 card=117)<\/span><\/span>\n       115        115        115     VIEW  (cr=22 pr=0 pw=0 time=1905 us starts=1 cost=20 size=9360 card=117)<\/span><\/span>\n       115        115        115      WINDOW SORT (cr=22 pr=0 pw=0 time=1904 us starts=1 cost=20 size=9360 card=117)<\/span><\/span>\n       115        115        115       HASH GROUP BY (cr=22 pr=0 pw=0 time=1718 us starts=1 cost=20 size=9360 card=117)<\/span><\/span>\n       116        116        116        VIEW  (cr=22 pr=0 pw=0 time=385 us starts=1 cost=18 size=9360 card=117)<\/span><\/span>\n       116        116        116         SORT UNIQUE (cr=22 pr=0 pw=0 time=384 us starts=1 cost=18 size=7879 card=117)<\/span><\/span>\n       117        117        117          UNION-ALL  (cr=22 pr=0 pw=0 time=213 us starts=1)<\/span><\/span>\n        10         10         10           HASH JOIN  (cr=14 pr=0 pw=0 time=210 us starts=1 cost=9 size=710 card=10)<\/span><\/span>\n        10         10         10            NESTED LOOPS  (cr=8 pr=0 pw=0 time=126 us starts=1 cost=9 size=710 card=10)<\/span><\/span>\n        10         10         10             NESTED LOOPS  (cr=8 pr=0 pw=0 time=116 us starts=1)<\/span><\/span>\n        10         10         10              STATISTICS COLLECTOR  (cr=8 pr=0 pw=0 time=115 us starts=1)<\/span><\/span>\n        10         10         10               MERGE JOIN  (cr=8 pr=0 pw=0 time=121 us starts=1 cost=6 size=480 card=10)<\/span><\/span>\n        10         10         10                TABLE ACCESS BY INDEX ROWID JOB_HISTORY (cr=2 pr=0 pw=0 time=34 us starts=1 cost=2 size=210 card=10)<\/span><\/span>\n        10         10         10                 INDEX FULL SCAN JHIST_JOB_IX (cr=1 pr=0 pw=0 time=24 us starts=1 cost=1 size=0 card=10)(object id 78632)<\/span><\/span>\n        10         10         10                SORT JOIN (cr=6 pr=0 pw=0 time=72 us starts=10 cost=4 size=513 card=19)<\/span><\/span>\n        19         19         19                 TABLE ACCESS FULL JOBS (cr=6 pr=0 pw=0 time=42 us starts=1 cost=3 size=513 card=19)<\/span><\/span>\n         0          0          0              INDEX UNIQUE SCAN EMP_EMP_ID_PK (cr=0 pr=0 pw=0 time=0 us starts=0)(object id 78622)<\/span><\/span>\n         0          0          0             TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=0 pr=0 pw=0 time=0 us starts=0 cost=3 size=23 card=1)<\/span><\/span>\n       107        107        107            TABLE ACCESS FULL EMPLOYEES (cr=6 pr=0 pw=0 time=11 us starts=1 cost=3 size=2461 card=107)<\/span><\/span>\n       107        107        107           MERGE JOIN  (cr=8 pr=0 pw=0 time=366 us starts=1 cost=6 size=7169 card=107)<\/span><\/span>\n        19         19         19            TABLE ACCESS BY INDEX ROWID JOBS (cr=2 pr=0 pw=0 time=27 us starts=1 cost=2 size=513 card=19)<\/span><\/span>\n        19         19         19             INDEX FULL SCAN JOB_ID_PK (cr=1 pr=0 pw=0 time=5 us starts=1 cost=1 size=0 card=19)(object id 78619)<\/span><\/span>\n       107        107        107            SORT JOIN (cr=6 pr=0 pw=0 time=67 us starts=19 cost=4 size=4280 card=107)<\/span><\/span>\n       107        107        107             TABLE ACCESS FULL EMPLOYEES (cr=6 pr=0 pw=0 time=7 us starts=1 cost=3 size=4280 card=107)<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\nElapsed times include waiting on following events:<\/span><\/span>\n  Event waited on                             Times   Max. Wait  Total Waited<\/span><\/span>\n  ----------------------------------------   Waited  ----------  ------------<\/span><\/span>\n  SQL*Net message to client                       1        0.00          0.00<\/span><\/span>\n  PGA memory operation                            5        0.00          0.00<\/span><\/span>\n  SQL*Net message from client                     1        0.01          0.01<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    <\/a>12. Conclusion<\/h2>\n\n\n\n
    A view-API has a value. You can change the physical data model and keep the existing view layer compatible for the consuming applications. This makes the database application independent, at least to a certain extent. That’s a great value when you think of testing interfaces or releasing new versions.\u00a0Building an initial 1:1 view-API is no big deal,\u00a0especially since it can be generated. The maintenance costs for such a view-API depend on many things. But I can’t imagine a scenario that isn’t worth it.<\/p>\n\n\n\n
    I was positively surprised by the feature richness and good usability of JOOQ. Things like the deep data model awareness including referential integrity constraints to make joins simpler and less error-prone are impressive. Ok, the DSL\u00a0needs some getting used to, but the excellent documentation helps a lot.\u00a0Although I have only scratched the surface of JOOQ, I am convinced that it is very well suited for developing high-performing PinkDB applications. Because JOOQ developers have control over the SQL statements sent to the database.<\/p>\n","protected":false},"excerpt":{"rendered":"
    In this blog post, I show how to build a read-only view-API for Oracle’s\u00a0HR sample schema. And I will use this view-API in a JOOQ application.\u00a0This application will fully comply with the\u00a0Pink Database Paradigm (PinkDB). This means the application executes set-based SQL and retrieves data with as few network roundtrips as possible. [\u2026]<\/span><\/p>\n","protected":false},"author":1,"featured_media":8745,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[107,118,94,109,13,85],"class_list":["post-8722","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","tag-java","tag-jooq","tag-oddgen","tag-pinkdb","tag-plsql","tag-sql"],"yoast_head":"\nView-API for JOOQ Application - Philipp Salvisberg's Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.salvis.com\/blog\/2018\/08\/05\/view-api-jooq-application\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"View-API for JOOQ Application - Philipp Salvisberg's Blog\" \/>\n<meta property=\"og:description\" content=\"In this blog post, I show how to build a read-only view-API for Oracle’s\u00a0HR sample schema. And I will use this view-API in a JOOQ application.\u00a0This application will fully comply with the\u00a0Pink Database Paradigm (PinkDB). This means the application executes set-based SQL and retrieves data with as few network roundtrips as possible. [\u2026]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.salvis.com\/blog\/2018\/08\/05\/view-api-jooq-application\/\" \/>\n<meta property=\"og:site_name\" content=\"Philipp Salvisberg's Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-08-05T20:34:07+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-11-08T02:11:28+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/08\/HR-View-API.png\" \/>\n\t<meta property=\"og:image:width\" content=\"949\" \/>\n\t<meta property=\"og:image:height\" content=\"657\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Philipp Salvisberg\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:site\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Philipp Salvisberg\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"19 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/08\\\/05\\\/view-api-jooq-application\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/08\\\/05\\\/view-api-jooq-application\\\/\"},\"author\":{\"name\":\"Philipp Salvisberg\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"headline\":\"View-API for JOOQ Application\",\"datePublished\":\"2018-08-05T20:34:07+00:00\",\"dateModified\":\"2023-11-08T02:11:28+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/08\\\/05\\\/view-api-jooq-application\\\/\"},\"wordCount\":1476,\"commentCount\":1,\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/08\\\/05\\\/view-api-jooq-application\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2018\\\/08\\\/HR-View-API.png\",\"keywords\":[\"Java\",\"JOOQ\",\"oddgen\",\"PinkDB\",\"PL\\\/SQL\",\"SQL\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/08\\\/05\\\/view-api-jooq-application\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/08\\\/05\\\/view-api-jooq-application\\\/\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/08\\\/05\\\/view-api-jooq-application\\\/\",\"name\":\"View-API for JOOQ Application - Philipp Salvisberg's Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/08\\\/05\\\/view-api-jooq-application\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/08\\\/05\\\/view-api-jooq-application\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2018\\\/08\\\/HR-View-API.png\",\"datePublished\":\"2018-08-05T20:34:07+00:00\",\"dateModified\":\"2023-11-08T02:11:28+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/08\\\/05\\\/view-api-jooq-application\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/08\\\/05\\\/view-api-jooq-application\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/08\\\/05\\\/view-api-jooq-application\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2018\\\/08\\\/HR-View-API.png\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2018\\\/08\\\/HR-View-API.png\",\"width\":949,\"height\":657},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/08\\\/05\\\/view-api-jooq-application\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"View-API for JOOQ Application\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\",\"name\":\"Philipp Salvisberg's Blog\",\"description\":\"Database-centric development\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\",\"name\":\"Philipp Salvisberg\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"width\":400,\"height\":400,\"caption\":\"Philipp Salvisberg\"},\"logo\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\"},\"sameAs\":[\"http:\\\/\\\/www.salvis.com\\\/\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"View-API for JOOQ Application - Philipp Salvisberg's Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.salvis.com\/blog\/2018\/08\/05\/view-api-jooq-application\/","og_locale":"en_US","og_type":"article","og_title":"View-API for JOOQ Application - Philipp Salvisberg's Blog","og_description":"In this blog post, I show how to build a read-only view-API for Oracle’s\u00a0HR sample schema. And I will use this view-API in a JOOQ application.\u00a0This application will fully comply with the\u00a0Pink Database Paradigm (PinkDB). This means the application executes set-based SQL and retrieves data with as few network roundtrips as possible. [\u2026]","og_url":"https:\/\/www.salvis.com\/blog\/2018\/08\/05\/view-api-jooq-application\/","og_site_name":"Philipp Salvisberg's Blog","article_published_time":"2018-08-05T20:34:07+00:00","article_modified_time":"2023-11-08T02:11:28+00:00","og_image":[{"width":949,"height":657,"url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/08\/HR-View-API.png","type":"image\/png"}],"author":"Philipp Salvisberg","twitter_card":"summary_large_image","twitter_creator":"@phsalvisberg","twitter_site":"@phsalvisberg","twitter_misc":{"Written by":"Philipp Salvisberg","Est. reading time":"19 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.salvis.com\/blog\/2018\/08\/05\/view-api-jooq-application\/#article","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/2018\/08\/05\/view-api-jooq-application\/"},"author":{"name":"Philipp Salvisberg","@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"headline":"View-API for JOOQ Application","datePublished":"2018-08-05T20:34:07+00:00","dateModified":"2023-11-08T02:11:28+00:00","mainEntityOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2018\/08\/05\/view-api-jooq-application\/"},"wordCount":1476,"commentCount":1,"publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2018\/08\/05\/view-api-jooq-application\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/08\/HR-View-API.png","keywords":["Java","JOOQ","oddgen","PinkDB","PL\/SQL","SQL"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.salvis.com\/blog\/2018\/08\/05\/view-api-jooq-application\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.salvis.com\/blog\/2018\/08\/05\/view-api-jooq-application\/","url":"https:\/\/www.salvis.com\/blog\/2018\/08\/05\/view-api-jooq-application\/","name":"View-API for JOOQ Application - Philipp Salvisberg's Blog","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2018\/08\/05\/view-api-jooq-application\/#primaryimage"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2018\/08\/05\/view-api-jooq-application\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/08\/HR-View-API.png","datePublished":"2018-08-05T20:34:07+00:00","dateModified":"2023-11-08T02:11:28+00:00","breadcrumb":{"@id":"https:\/\/www.salvis.com\/blog\/2018\/08\/05\/view-api-jooq-application\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.salvis.com\/blog\/2018\/08\/05\/view-api-jooq-application\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/2018\/08\/05\/view-api-jooq-application\/#primaryimage","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/08\/HR-View-API.png","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/08\/HR-View-API.png","width":949,"height":657},{"@type":"BreadcrumbList","@id":"https:\/\/www.salvis.com\/blog\/2018\/08\/05\/view-api-jooq-application\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.salvis.com\/blog\/"},{"@type":"ListItem","position":2,"name":"View-API for JOOQ Application"}]},{"@type":"WebSite","@id":"https:\/\/www.salvis.com\/blog\/#website","url":"https:\/\/www.salvis.com\/blog\/","name":"Philipp Salvisberg's Blog","description":"Database-centric development","publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.salvis.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515","name":"Philipp Salvisberg","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","width":400,"height":400,"caption":"Philipp Salvisberg"},"logo":{"@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg"},"sameAs":["http:\/\/www.salvis.com\/"]}]}},"_links":{"self":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/8722","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/comments?post=8722"}],"version-history":[{"count":103,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/8722\/revisions"}],"predecessor-version":[{"id":12645,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/8722\/revisions\/12645"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media\/8745"}],"wp:attachment":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media?parent=8722"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/categories?post=8722"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/tags?post=8722"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}