Stored Procedure Wrapper

Library to make PostgreSQL stored procedures available through simple Java "*SProcService" interfaces including automatic object serialization and deserialization (using typemapper and convention-over-configuration). Supports sharding, advisory locking, statement timeouts and PostgreSQL types such as enums and hstore.

License

License

GroupId

GroupId

de.zalando
ArtifactId

ArtifactId

zalando-sprocwrapper
Last Version

Last Version

2.0.0
Release Date

Release Date

Type

Type

jar
Description

Description

Stored Procedure Wrapper
Library to make PostgreSQL stored procedures available through simple Java "*SProcService" interfaces including automatic object serialization and deserialization (using typemapper and convention-over-configuration). Supports sharding, advisory locking, statement timeouts and PostgreSQL types such as enums and hstore.
Project URL

Project URL

https://github.com/zalando-incubator/java-sproc-wrapper
Project Organization

Project Organization

Zalando/Technology/Platform
Source Code Management

Source Code Management

https://github.com/zalando-incubator/java-sproc-wrapper.git

Download zalando-sprocwrapper

How to add to project

<!-- https://jarcasting.com/artifacts/de.zalando/zalando-sprocwrapper/ -->
<dependency>
    <groupId>de.zalando</groupId>
    <artifactId>zalando-sprocwrapper</artifactId>
    <version>2.0.0</version>
</dependency>
// https://jarcasting.com/artifacts/de.zalando/zalando-sprocwrapper/
implementation 'de.zalando:zalando-sprocwrapper:2.0.0'
// https://jarcasting.com/artifacts/de.zalando/zalando-sprocwrapper/
implementation ("de.zalando:zalando-sprocwrapper:2.0.0")
'de.zalando:zalando-sprocwrapper:jar:2.0.0'
<dependency org="de.zalando" name="zalando-sprocwrapper" rev="2.0.0">
  <artifact name="zalando-sprocwrapper" type="jar" />
</dependency>
@Grapes(
@Grab(group='de.zalando', module='zalando-sprocwrapper', version='2.0.0')
)
libraryDependencies += "de.zalando" % "zalando-sprocwrapper" % "2.0.0"
[de.zalando/zalando-sprocwrapper "2.0.0"]

Dependencies

compile (12)

Group / Artifact Type Version
org.springframework : spring-jdbc jar 4.3.8.RELEASE
org.postgresql : postgresql jar 42.1.1
org.slf4j : slf4j-api jar 1.7.9
org.slf4j : slf4j-log4j12 jar 1.7.9
commons-lang : commons-lang jar 2.6
commons-beanutils : commons-beanutils jar 1.9.2
javax.validation : validation-api jar 1.0.0.GA
org.hibernate : hibernate-validator jar 4.3.0.Final
org.reflections : reflections jar 0.9.10
com.google.guava : guava jar 22.0
javax.persistence : persistence-api jar 1.0.2
joda-time : joda-time jar 2.6

test (6)

Group / Artifact Type Version
junit : junit jar 4.12
org.springframework : spring-context-support jar 4.3.8.RELEASE
org.springframework : spring-test jar 4.3.8.RELEASE
org.cthul : cthul-matchers jar 1.1.0
com.zaxxer : HikariCP jar 2.4.7
org.mockito : mockito-core jar 2.8.47

Project Modules

There are no modules declared in this project.

SProcWrapper

Build Status Coverage Status Javadoc Maven Central License

Library to make PostgreSQL stored procedures(SProcs) available through simple Java "SProcService" interfaces including automatic object serialization and deserialization (using typemapper and convention-over-configuration).

Supports horizontal database sharding (partition/access logic lies within application), easy use of pg_advisory_lock through annotations to ensure single Java node execution, configurable statement timeouts per stored procedure, and PostgreSQL types including enums and hstore.

Usage

To use SProcWrapper, add the following lines to your pom.xml:

<dependency>
    <groupId>org.zalando</groupId>
    <artifactId>zalando-sprocwrapper</artifactId>
    <version>${zalando-sprocwrapper.version}</version>
</dependency>

Type Mapping

SProcWrapper provides an efficient and easy-to-use mechanism for translating values from database to Java objects and vice-versa. It allows us to map not only primitive types, but also complex types (Java domain objects).

Here are some examples!

Using basic types:

@SProcService
public interface CustomerSProcService {
  @SProcCall
  int registerCustomer(@SProcParam String name, @SProcParam String email);
}
CREATE FUNCTION register_customer(p_name text, p_email text)
RETURNS int AS
$$
  INSERT INTO z_data.customer(c_name, c_email)
       VALUES (p_name, p_email)
    RETURNING c_id
$$
LANGUAGE 'sql' SECURITY DEFINER;

And a complex type:

@SProcService
public interface OrderSProcService {
  @SProcCall
  List<Order> findOrders(@SProcParam String email);
}
CREATE FUNCTION find_orders(p_email text,
  OUT order_id int,
  OUT order_date timestamp,
  OUT shipping_address order_address)
RETURNS SETOF RECORD AS
$$
  SELECT o_id,
         o_date,
         ROW(oa_street, oa_city, oa_country)::order_address
    FROM z_data.order
    JOIN z_data.order_address
      ON oa_order_id = o_id
    JOIN z_data.customer
      ON c_id = o_customer_id
   WHERE c_email = p_email
$$
LANGUAGE 'sql' SECURITY DEFINER;

Please check unit/integration tests for more examples.

The following table shows the mapping between a database type and a Java type:

Database Java Type
smallint int
integer int
bigint long
decimal java.math.BigDecimal
numeric java.math.BigDecimal
real float
double precision double
serial int
bigserial long
varchar java.lang.String
char char
text java.lang.String
timestamp java.sql.Timestamp
timestamptz java.sql.Timestamp
date java.sql.Timestamp
time java.sql.Timestamp
timetz java.sql.Timestamp
boolean boolean
enum java.lang.Enum
array java.util.List / java.util.Set
hstore java.util.Map<java.lang.String, java.lang.String>

Note: SProcwrapper doesn't support functions returning arrays as a single output. If one wants to return a collection, please return a SETOF instead.

Prerequisites

  • Java 8
  • To compile, one should use Maven 2.1.0 or above

Dependencies

  • Spring Framework
  • PostgreSQL JDBC driver ;)
  • Google Guava
  • and more see pom.xml for details

How to run integration tests

The provided helper script will start a PostgreSQL instance with Docker on port 5432 and run integration tests:

./test.sh

You can use the provided Vagrant box to run the script in.

Known issues

  • PostgreSQL JDBC driver does not honor identical type names in different schemas, this may lead to issues if typemapper is used where types are present with equal name in more than one schema (this problem is solved now with the commit 3ca94e64d6322fa91c477200bfb3719deaeac153 to pgjdbc driver);
  • PostgreSQL domains are not supported as for now;
  • PostgreSQL hstore type is mapped from and to Map<String,String>, there is no way to use Map of different types for now;
  • Two different datasources with the same JDBC URL and different search paths might not work properly when we have types with the identical name;
  • SProcWrapper relies on the search path to resolve conflicting types with the same name (right now, we are not checking the schema). If one specifies the schema of the stored procedure's return type, SProcWrapper might end up using the wrong one, because it will use the search_path to resolve the conflict. For more info check test: SimpleIT.testTypeLookupBugWithSchema;
  • For integration with Spring's transaction management use the TransactionAwareDataSourceProxy as the data source injected into the data source provider.

Documentation

You can find some more information about the SProcWrapper in our various Zalando Technology blog posts:

Contributing

See contributing guideline.

License

MIT license. See license file.

de.zalando

The Zalando Incubator

Projects in development at Zalando. We accept contributions and feedback—leave an issue at the relevant project's issues tracker.

Versions

Version
2.0.0
1.5.1
1.4.0
1.4.0.M2
1.3.0
1.2.4
1.2.3
1.2.1
1.0.6