JarCasting
JarCasting JarCasting

Maven Auto PLSQL/SP Generator Plugin

Maven plugin to generate Java classes from StoredProcedure and Functions in Database

License

License
Categories

Categories

Auto Application Layer Libs Code Generators
GroupId

GroupId

com.github.yadickson
ArtifactId

ArtifactId

autoplsp
Last Version

Last Version

1.7.28
Release Date

Release Date
Type

Type

maven-plugin
Description

Description

Maven Auto PLSQL/SP Generator Plugin
Maven plugin to generate Java classes from StoredProcedure and Functions in Database
Project URL

Project URL

https://github.com/yadickson/autoplsp
Project Organization

Project Organization

Yadickson Soto
Source Code Management

Source Code Management

https://github.com/yadickson/autoplsp

Download autoplsp

Filename Size
autoplsp-1.7.28.pom
autoplsp-1.7.28.jar 146 KB
autoplsp-1.7.28-sources.jar 136 KB
autoplsp-1.7.28-javadoc.jar 895 KB
Browse

How to add to project

<plugin>
    <groupId>com.github.yadickson</groupId>
    <artifactId>autoplsp</artifactId>
    <version>1.7.28</version>
</plugin>

Dependencies

compile (7)

Group / Artifact Type Version
org.apache.maven : maven-plugin-api jar 3.5.2
org.apache.maven.plugin-tools : maven-plugin-tools-api jar 3.5
org.apache.maven.plugin-tools : maven-plugin-annotations jar 3.5
org.freemarker : freemarker jar 2.3.23
commons-lang : commons-lang jar 2.6
commons-io : commons-io jar 2.5
commons-dbutils : commons-dbutils jar 1.7

test (3)

Group / Artifact Type Version
junit : junit jar 4.12
org.jmockit : jmockit jar 1.37
org.mockito : mockito-core jar 1.10.19

Project Modules

There are no modules declared in this project.

Maven Auto PLSQL/SP Generator Plugin

TravisCI Status Codecov Status Central OSSRH Central Maven

Maven plugin to generate Java classes from StoredProcedure and Functions in Database

Support

  • Oracle DataBase 11g and 12c
  • Basic PostgresSQL
  • Basic SQL Server (Tested 2017, Driver jTDS)
  • Java >= 7
  • Spring Framework >= 4
  • Auto package name detection
  • Configuration file generation for Spring
  • Use output parameters to evaluate process
  • Transaction annotation
  • Command line for driver, user, pass and connectionString parameters
  • JsonNonNull support

Examples

Oracle

  • Procedures
  • Functions
  • Numerics (NUMBER, DECIMAL, INTEGER, FLOAT, REAL, DEC, INT, SMALLINT, BINARY_DOUBLE, BINARY_FLOAT)
  • Texts (CHAR, NCHAR, VARCHAR, VARCHAR2, NVARCHAR2)
  • Lobs (CLOB, NCLOB, BLOB)
  • Time (DATE, TIMESTAMP)
  • ROWID and UROWID
  • REF CURSOR (only output)
  • TYPE OBJECT (only input)
  • TYPE TABLE [NATIVE] (only input)
  • TYPE TABLE [TYPE OBJECT] (only input)

PostgresSQL

  • Procedures
  • Functions
  • Numerics (INTEGER, REAL)
  • Texts (TEXT, CHARACTER)
  • Working in progress

SQL Server

  • Procedures
  • Functions
  • Numerics (INT, BIGINT, SMALLINT, TINYINT, DECIMAL, NUMERIC, FLOAT, REAL, BIT, MONEY, SMALLMONEY)
  • Texts (CHAR, NCHAR, VARCHAR, NVARCHAR, TEXT, NTEXT)
  • Binaries (BINARY, VARBINARY, IMAGE)
  • Time (DATE, TIME, DATETIME, DATETIME2, DATETIMEOFFSET, SMALLDATETIME)
  • REF CURSOR (Not supported)
  • Working in progress

POM properties

<properties>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>

POM dependencies

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>4.x.x.RELEASE</version>
    <scope>provided</scope>
</dependency> 

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>4.x.x.RELEASE</version>
    <scope>provided</scope>
</dependency> 
        
<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.5</version>
    <scope>provided</scope>
</dependency>

<dependency>
    <groupId>javax.annotation</groupId>
    <artifactId>javax.annotation-api</artifactId>
    <version>1.3.2</version>
    <scope>provided</scope>
</dependency>

<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
    <version>2.5.0</version>
    <scope>provided</scope>
</dependency>

Oracle

<dependency>
    <groupId>com.jslsolucoes</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.1.0</version>
    <scope>provided</scope>
</dependency>

PostgreSQL

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.1.4</version>
    <scope>provided</scope>
</dependency>

SQL Server

<dependency>
    <groupId>net.sourceforge.jtds</groupId>
    <artifactId>jtds</artifactId>
    <version>1.3.1</version>
    <scope>provided</scope>
</dependency>

POM plugin config

<plugin>
    <groupId>com.github.yadickson</groupId>
    <artifactId>autoplsp</artifactId>
    <version>...</version>
    <executions>
        <execution>
            <goals>
                <goal>generator</goal>
            </goals>
        </execution>
    </executions>
    <configuration>
        <driver>...</driver>
        <connectionString>...</connectionString>
        <user>...</user>
        <pass>...</pass>
        <javaDataSourceName>...DataSource</javaDataSourceName>
        <javaJdbcTemplateName>...JdbcTemplate</javaJdbcTemplateName>
        <jndiDataSourceName>JDBC/...</jndiDataSourceName>
        <objectSuffix>Object</objectSuffix>
        <arraySuffix>Array</arraySuffix>
        <outputDirectory>...</outputDirectory>
        <outputDirectoryResource>...</outputDirectoryResource>
        <folderNameGenerator>...</folderNameGenerator>
        <folderNameResourceGenerator>...</folderNameResourceGenerator>
        <outputConfigFileName>...</outputConfigFileName>
        <outParameterCode>...</outParameterCode>
        <outParameterMessage>...</outParameterMessage>
        <javaPackageName>...</javaPackageName>
        <jsonNonNull>...</jsonNonNull>
        <includes>
            <param>...</param>
            <param>...</param>
        </includes>
        <excludes>
            <param>...</param>
            <param>...</param>
        </excludes>
        <mappers>
            <param>...</param>
            <param>...</param>
        </mappers>
        <resultset>
            <param>...</param>
            <param>...</param>
        </resultset>
        <tables>
            <param>...</param>
            <param>...</param>
        </tables>
    </configuration>
    <dependencies>
        <dependency>
            <groupId>...driver..</groupId>
            <artifactId>..driver..</artifactId>
            <version>..version..</version>
        </dependency>
    </dependencies>
</plugin>

driver (required)

JDBC Driver class name, examples:

oracle.jdbc.driver.OracleDriver

org.postgresql.Driver

net.sourceforge.jtds.jdbc.Driver

connectionString (required)

Database connection string, examples:

jdbc:oracle:thin:@${host}:${port}:${db}

jdbc:postgresql://${host}:${port}/${db}

jdbc:jtds:sqlserver://${host}:${port}/${db}

user (required)

Database username

pass (required)

Database password

javaDataSourceName (required)

Datasource stereotype name

javaJdbcTemplateName (optional, default jdbcTemplate)

JdbcTemplate stereotype name

jndiDataSourceName (required)

Datasource JNDI name

outputConfigFileName (optional)

Default value ${project.artifactId}-context.xml

outParameterCode (optional - default value OUT_RETURN_CODE)

Output parameter code to evaluate process.

If code is Zero (O), OK

If code is not Zero (0), throw SQLException (OUT_RETURN_MSG, null, OUT_RETURN_CODE)

outParameterMessage (optional - default value OUT_RETURN_MSG)

Output parameter message.

javaPackageName (required)

Package name for Java classes

jsonNonNull (optional - default value false) (since 1.7.12)

Add @JsonInclude(JsonInclude.Include.NON_NULL) annotation.

includes -> include sp (optional, default .*)

Regular expression to include procedure and functions names, example SP_YES.*

excludes -> exclude sp (optional, default none)

Regular expression to exclude procedure and functions names, example SP_NOT.*

resultset -> resultset (optional, default .*)

Regular expression to find resultset on procedure and functions names, example SP_YES.*

Command line support

mvn clean package -Dautoplsp.driver=... -Dautoplsp.connectionString=... -Dautoplsp.user=... -Dautoplsp.pass=...

POM Basic Configuration (include all procedure and function)

<plugin>
    <groupId>com.github.yadickson</groupId>
    <artifactId>autoplsp</artifactId>
    <version>...</version>
    <executions>
        <execution>
            <goals>
                <goal>generator</goal>
            </goals>
            <configuration>
                <driver>...</driver>
                <connectionString>...</connectionString>
                <user>...</user>
                <pass>...</pass>
                <javaDataSourceName>...DataSource</javaDataSourceName>
                <jndiDataSourceName>JDBC/...</jndiDataSourceName>
                <javaPackageName>...</javaPackageName>
            </configuration>
        </execution>
    </executions>
    <dependencies>
        <dependency>
            <groupId>...driver..</groupId>
            <artifactId>..driver..</artifactId>
            <version>..version..</version>
        </dependency>
    </dependencies>
</plugin>

POM Basic Configuration (include one procedure)

<plugin>
    <groupId>com.github.yadickson</groupId>
    <artifactId>autoplsp</artifactId>
    <version>...</version>
    <executions>
        <execution>
            <goals>
                <goal>generator</goal>
            </goals>
            <configuration>
                <driver>...</driver>
                <connectionString>...</connectionString>
                <user>...</user>
                <pass>...</pass>
                <javaDataSourceName>...DataSource</javaDataSourceName>
                <jndiDataSourceName>JDBC/...</jndiDataSourceName>
                <outputDirectory>...</outputDirectory>
                <javaPackageName>...</javaPackageName>
                <includes>
                    <param>SP_ADD_VALUES</param>
                </includes>
            </configuration>
        </execution>
    </executions>
    <dependencies>
        <dependency>
            <groupId>...driver..</groupId>
            <artifactId>..driver..</artifactId>
            <version>..version..</version>
        </dependency>
    </dependencies>
</plugin>

POM Basic Configuration (include two procedures)

<plugin>
    <groupId>com.github.yadickson</groupId>
    <artifactId>autoplsp</artifactId>
    <version>...</version>
    <executions>
        <execution>
            <goals>
                <goal>generator</goal>
            </goals>
            <configuration>
                <driver>...</driver>
                <connectionString>...</connectionString>
                <user>...</user>
                <pass>...</pass>
                <javaDataSourceName>...DataSource</javaDataSourceName>
                <jndiDataSourceName>JDBC/...</jndiDataSourceName>
                <outputDirectory>...</outputDirectory>
                <javaPackageName>...</javaPackageName>
                <includes>
                    <param>SP_ADD_VALUES</param>
                    <param>SP_SUB_VALUES</param>
                </includes>
            </configuration>
        </execution>
    </executions>
    <dependencies>
        <dependency>
            <groupId>...driver..</groupId>
            <artifactId>..driver..</artifactId>
            <version>..version..</version>
        </dependency>
    </dependencies>
</plugin>

POM Basic Configuration (exclude one procedure)

<plugin>
    <groupId>com.github.yadickson</groupId>
    <artifactId>autoplsp</artifactId>
    <version>...</version>
    <executions>
        <execution>
            <goals>
                <goal>generator</goal>
            </goals>
            <configuration>
                <driver>...</driver>
                <connectionString>...</connectionString>
                <user>...</user>
                <pass>...</pass>
                <javaDataSourceName>...DataSource</javaDataSourceName>
                <jndiDataSourceName>JDBC/...</jndiDataSourceName>
                <outputDirectory>...</outputDirectory>
                <javaPackageName>...</javaPackageName>
                <excludes>
                    <exculde>SP_DIV_VALUES</exculde>
                </excludes>
            </configuration>
        </execution>
    </executions>
    <dependencies>
        <dependency>
            <groupId>...driver..</groupId>
            <artifactId>..driver..</artifactId>
            <version>..version..</version>
        </dependency>
    </dependencies>
</plugin>

How to evaluate result code

Examples

CREATE OR REPLACE PROCEDURE SP_PROCEDURE
(
    OUT_P_CODE OUT NUMBER,
    OUR_P_MSG OUT VARCHAR2
)
AS
BEGIN

    NULL;

END SP_PROCEDURE;
CREATE OR REPLACE FUNCTION FN_FUNCTION
(
    OUT_P_CODE OUT NUMBER,
    OUR_P_MSG OUT VARCHAR2
)
RETURN NUMBER
AS
BEGIN

    RETURN 0;

END FN_FUNCTION;

Build project

mvn clean package install

Configuration file generated

spring/database/[outputConfigFileName]

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="
            http://www.springframework.org/schema/beans
            http://www.springframework.org/schema/beans/spring-beans.xsd
            http://www.springframework.org/schema/context
            http://www.springframework.org/schema/context/spring-context-4.0.xsd
            http://www.springframework.org/schema/tx
            http://www.springframework.org/schema/tx/spring-tx.xsd">

    <bean id="..." name="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean" >
        <property name="jndiName" value="..." />
        <property name="resourceRef" value="true" />
        <property name="proxyInterface" value="javax.sql.DataSource" />
    </bean>
    
    <tx:annotation-driven transaction-manager="transactionManager"/>

    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="..."/>
    </bean>

    <bean id="..." name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" >
        <property name="dataSource" ref="..." />
    </bean>

    <context:component-scan base-package="....repository"/>

</beans>

Configuration in the parent project

Add import resource in root-context.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd">

    ...

    <import resource="classpath*:spring/database/[outputConfigFileName]" />

</beans>

Transaction annotation example

@Service
public class CustomServiceImpl implements CustomService {

...

    @Transactional(rollbackFor = CustomException.class)
    @Override
    public Long customMethod() throws CustomException {
        dao1.execute(...);
        dao2.execute(...);
    }
}

Versions

Version
1.7.28
1.7.27
1.7.26
1.7.25
1.7.24
1.7.23
1.7.22
1.7.21
1.7.20
1.7.19
1.7.18
1.7.17
1.7.15
1.7.14
1.7.11
1.7.10
1.7.9
1.7.8
1.7.7
1.7.6
1.7.5
1.7.4
1.7.3
1.7.2
1.7.1
1.7.0
1.6.0
1.5.4
1.5.2
1.5.0
1.4.2
1.4.1
1.4.0
1.3.3
1.3.2
1.3.1
1.3.0
1.2.0
1.1.13
1.1.12
1.1.11