jpa.sql

-- This ia a code-generator for JPA classes in Java.
-- Give this table function the Shema and table name and 
-- it will produce a JPA class you can paste into your project.
-- Take a look at the use cases below.

-- This table function requires "candidate_key" and "snake_case" 
-- you will find elsewhere on my gist - build them first.

-- Simply paste this gist into ACS SQL and run it to create the UDTF.

-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library

-- It is a cool example how far you can go with SQL: Have fun - 
-- (C) Niels Liisberg 2021

-- This gist is distributed on an "as is" basis, without warranties 
-- or conditions of any kind, either express or implied.
------------------------------------------------------------------------------------------------------------------------------------------------------------
create or replace function qusrsys.jpa (
    schema_name varchar(10),
    table_name varchar(64) 
)
returns table (
    source_lines varchar(256)
)
deterministic
set option output=*print, commit=*none, dbgview = *source --list
begin 

    declare table_text varchar(256);
    declare candidate_key varchar(256);

    Select trim(table_text) 
    into table_text 
    from systables a 
    where a.table_schema = jpa.schema_name 
    and   a.table_name  =  jpa.table_name;

    pipe('// This code is build by Sitemule JPA generator:');
    pipe('// https://gist.github.com/NielsLiisberg/c3b356fcfc969e6d1125fd3d38806036');


    pipe('package com.example.models;');
    pipe ('import java.math.BigDecimal;');
    pipe ('import java.time.LocalDate;');
    pipe ('import java.time.LocalDateTime;');
    pipe ('import java.time.LocalTime;');

    pipe ('');
    pipe ('import javax.persistence.Column;');
    pipe ('import javax.persistence.Entity;');
    pipe ('import javax.persistence.Id;');
    pipe ('import javax.persistence.Table;');
    pipe ('import javax.validation.constraints.NotNull;');
    pipe ('import javax.validation.constraints.Size;');

    pipe ('');
    pipe ('import lombok.Getter;');
    pipe ('import lombok.Setter;');

    pipe ('// Be careful with @Data. Equals/hash in lombok is not compatible with spring.');
    pipe ('@Getter');
    pipe ('@Setter');
    pipe ('@Entity');
    pipe ('@Table(name="' ||  trim(jpa.table_name) || '")');
    pipe ('public class ' ||  substr(jpa.table_name , 1 , 1) || snake_case(trim(substr(jpa.table_name, 2)) || '_' ||  table_text) || ' {');
    pipe ('');

    set candidate_key = qusrsys.candidate_key ( 
        table   => jpa.table_name,
        library => jpa.schema_name 
    ); 
    
    if candidate_key is null then
        pipe ('     // No primary key available');
    else 
        pipe ('     // Candidate key : ' || candidate_key);
    end if; 
    
    pipe ('');

    for Select
            '// Datatype: ' || data_type 
            || ', name: '   || trim(lower(column_name)) 
            || ', fieldLabel: ' || trim(ifnull(column_text,'')) 
            || ', len: '  || a.length 
            || case when numeric_scale is null then '' else ', prec: ' || numeric_scale end  
            as text,
            
            case  
                when numeric_scale > 0 or (numeric_scale = 0 and length > 10) then 
                    'BigDecimal'
                when numeric_scale = 0 and length >= 4 then 
                    'Long'
                when numeric_scale = 0 then 
                    'Integer'
                when data_type = 'DATE' then 
                    'LocalDate'
                when data_type = 'TIMESTAMP' then 
                    'LocalDateTime'
                when data_type = 'TIME' then 
                    'LocalTime'
                else     
                    'String'
            end datatype,
            column_name,
            data_type,
            length,
            column_text, 
            is_nullable
        from syscolumns a
        where a.table_name = jpa.table_name  
        and a.table_schema = jpa.schema_name
    do 
        pipe ('     ' || text);
        
        -- When the column is in the list of candidate keys, we add the @Id annotation
         
        if  posstr( 
                ',' ||qusrsys.candidate_key ( 
                    table   => jpa.table_name,
                    library => jpa.schema_name 
                ) || ',' ,
                ',' || trim(column_name) || ','
            ) > 0 then 
            pipe ('     @Id');
        end if;

        if is_nullable = 'N' then
            pipe ('     @NotNull');
        end if;    

        pipe ('     @Size(max = ' || length || ')');
        pipe ('     @Column(name="' || trim(column_name) || '")');
        pipe ('     ' || datatype || ' ' || camel_case(rtrim(column_name) || '_' || rtrim(ifnull(column_text,''))) || ';');
        pipe ('');
    end for;

    pipe ('}');

    return;
end;

-- test cases
Select * from table (qusrsys.jpa (
    schema_name => 'QIWS' ,
    table_name => 'QCUSTCDT'
)); 

values( 
  qusrsys.candidate_key ( 
            table   => 'QCUSTCDT',
            library => 'QIWS' 
        ) 
); 


Select * from table (qusrsys.jpa (
    schema_name => 'ICEBREAK' ,
    table_name => 'SVR00'
)); 

Select * from table (qusrsys.jpa (
    schema_name => 'QSYS2' ,
    table_name => 'SYSROUTINES'
)); 

-- Works with S/36 files with national characters as well 
Select * from table (qusrsys.jpa (
    schema_name => 'QS36F' ,
    table_name => 'XX.BELØB'
)); 

values qusrsys.candidate_key (
    library  => 'QSYS2' ,
    table => 'SYSROUTINES'
); 

Select
        
        

A suggestion is that class names should be CamelCased, and not snake_cased :)