JPortal2¶

From the docs of sqlc

And lo, the Great One looked down upon the people and proclaimed:
“SQL is actually pretty great”

Compile SQL to type-safe code, in any language¶

JPortal2 is the newest version of the JPortal SI-file based generator.

Full Documentation¶

For full Documentation see here.

What is JPortal2?¶

JPortal2 generates fully type-safe idiomatic code from SQL.

You write table definitions and SQL queries
You run JPortal2 to generate code that presents type-safe interfaces to those queries
You write application code that calls the methods JPortal2 generated.

It's that easy. You don't have to write any boilerplate SQL querying code ever again.
See the current list of supported programming languages and databases here: Generator Docs.

How it Works¶

Simply put, JPortal2 (and JPortal before it) is a data-definition first code generator. To use it, you:

Define database tables using a small, easy-to-understand Domain-Specific Language (DSL).
You define the queries you want to expose for the table. Standard CRUD queries like SELECT, INSERT, UPDATE, DELETE and MERGE can be created for you.
More complex queries can easily be defined using standard SQL.

Once you have defined your tables, you run the JPortal2 generator, and specify the code you want generated.

JPortal2 can generate:

DDL for all the popular RDBMS systems (Postgres, MySQL, Oracle, SQLServer, DB/2 and others)
DAL (Data Access Layers) for most languages (C/C++, Java, C#, Python and others)
Code stubs that can be used in other generators to create Client/Server and other Applications
Anything else you want to generate off your database structure

JPortal2 has a number of built-in code generators written in Java, but also has built-in Freemarker support to allow you to easily add your own generators.

Quickstart¶

Docker¶

First pull the docker image for the version of JPortal you want to use. You can browse to https://github.com/si-gen/jportal2 to look at the available versions.

echo Running JPortal2 from ${PWD}...

docker run --rm -v ${PWD}:/local ghcr.io/si-gen/jportal2:latest \
                      --inputdir=/local/sql/si \
                      --builtin-generator PostgresDDL:/local/generated_sources/generated_sql \
                      --template-generator SQLAlchemy:/local/generated_sources/python/jportal \
                      --download-template "SQLAlchemy:https://github.com/SI-Gen/jportal2-generator-vanguard-sqlalchemy/archive/refs/tags/1.8.zip|stripBaseDir"                      

Java¶

The Java JAR is hosted at https://ossindex.sonatype.org/component/pkg:maven/za.co.bbd/jportal2

To use JPortal2 in your Maven based Java project, simply add the following to your POM: properties:

<properties>    
    <jportal2.version>1.3.0</jportal2.version>
    <jportal2maven.version>1.2.0</jportal2maven.version>
</properties>

dependencies:

        <dependency>
            <groupId>za.co.bbd</groupId>
            <artifactId>jportal2</artifactId>
            <version>1.3.0</version>
            <scope>compile</scope>
        </dependency>

and plugins:

            <plugin>
            <groupId>za.co.bbd</groupId>
            <artifactId>jportal2-maven-plugin</artifactId>
            <version>${jportal2maven.version}</version>
            <configuration>
                <sourcePath>${basedir}/src/main/sql/</sourcePath>
                <generators>
                    <generator>JavaJCCode:${basedir}/target/generated-sources/java/com/example/db</generator>
                    <generator>PostgresDDL:${basedir}/target/generated-sources/scripts/sql</generator>
                </generators>
                <compilerFlags>
                    <compilerFlag>utilizeEnums</compilerFlag>
                </compilerFlags>
<!--                    <additionalArguments>&#45;&#45;template-generator JdbiSqlObjects:${basedir}/target/generated-sources/java/</additionalArguments>-->
            </configuration>
            <dependencies>
                <dependency>
                    <groupId>za.co.bbd</groupId>
                    <artifactId>jportal2</artifactId>
                    <version>${jportal2.version}</version>
                </dependency>
            </dependencies>
            <executions>
               <execution>
                 <phase>generate-sources</phase>
                     <goals>
                        <goal>jportal</goal>
                    </goals>
            </execution>
            </executions>
        </plugin>

JPortal2 is the actual Data Access Layer (DAL) generator. jportal2-maven-plugin is a plugin for maven that will run the generator at build time.

Create a file called todolist.si in the ${basedir}/src/main/sql/ directory (this is defined above in sourcePath tag)

todolist.si

DATABASE ExampleDatabase
PACKAGE com.example.db
SERVER ExampleServer
SCHEMA ToDoList_App

TABLE ToDoList
   ID               SEQUENCE
   ListName         CHAR(255)
   ListType         SHORT (Private=1, Public=2)
   Description      CHAR
   LastUpdated      TIMESTAMP

KEY PKEY PRIMARY
    ID

//Simple CRUD queries are available out of the box with JPortal2
PROC Insert Returning
PROC Update
PROC SelectOne
PROC DeleteOne

//More complex custom queries can be defined using standard SQL
PROC SelectListNameAndListTypeAsString
INPUT
    ID          =
OUTPUT
    ListName    =
    ListType    CHAR
SQLCODE
SELECT
    ListName,
    CASE
        WHEN ListType = 1 THEN 'Private'
        WHEN ListType = 2 THEN 'Public'
END
FROM
    TodoList
WHERE
    ID = :ID
ENDCODE

//Dynamic queries can be done with the ampersand below
//REMEMBER!!! Dynamic SQL is open to SQL injection! So use with care and make sure to sanitize inputs!
//Dynamic SQL is a last resort escape hatch, not the first tool to reach for!!
//In the DAL class created for the below query, a property called MyDynamicWhereClause will be created
//This will allow you to pass through the remainder of the where clause as a string.
PROC SelectWithDynamicQuery
INPUT
    ListName    =
OUTPUT
   ID               SEQUENCE
   ListName         CHAR(255)
   ListType         SHORT (Private=1, Public=2)
   Description      CHAR
   LastUpdated      TIMESTAMP
SQLCODE
SELECT
   ID
   ,ListName
   ,ListType
   ,Description
   ,LastUpdated
FROM
    ToDoList
WHERE
    ListName = :ListName
    AND &MyDynamicWhereClause
ENDCODE

Now create a file called todo_items.si in the same directory (${basedir}/src/main/sql/) todo_items.si

DATABASE ExampleDatabase
PACKAGE com.example.db
SERVER ExampleServer
SCHEMA ToDoList_App

TABLE ToDo_Item
   ID               SEQUENCE
   TodoList_ID      INT     //This is a foreign key to the ToDoList table
   ItemName         CHAR(255)
   ItemDescription  CLOB
   LastUpdated      TIMESTAMP

//This define ID as the Primary Key
KEY PKEY PRIMARY
    ID

PROC Insert Returning
PROC Update
PROC SelectOne
PROC DeleteOne

//The SelectBy function automatically creates
//a SELECT query using the given fields as the
//WHERE clause
PROC SelectBy TodoList_ID
OUTPUT
    ID                  =
    ItemName            =
    ItemDescription     =
    LastUpdated         =

Now compile your maven project. If all went well, you should see 2 files inside the directory ${basedir}/target/generated-sources/java/com/example/db.