PDB

PulseDB is a database-mapping software library written in Java, it provides a transparent access and manipulation to a great variety of database implementations. PDB provides a DSL that covers most of SQL functionalities and allows to easily integrate persistence into your projects and modules.

Using PDB

Add the following dependency to your Maven pom (example for PDB v2.8.14):

<dependencies>
    ...
	<dependency>
		<groupId>com.feedzai</groupId>
		<artifactId>pdb</artifactId>
		<version>2.8.14</version>
	</dependency>
	...
</dependencies>

Breaking changes

The timeout properties have been redefined in PdbProperties as numeric instead of strings since v2.4.6 - avoid using versions 2.4.4 and 2.4.5.

DatabaseEngine interface has a new method #dropView() since v2.5.3. This method was created without a default so this led to a breaking change. Use 2.5.5 and avoid using 2.5.3 and 2.5.4.

DatabaseEngine methods #getPSResultSet(final String name), #getPSIterator(final String name), #getPSIterator (final String name, final int fetchSize), since 2.7.0, throw an extra ConnectionResetException when the database connection is lost and recovered.

Changes from 2.0.0

It is now possible to call built-in database vendor functions [e.g. f("lower", column("COL1"))]
Added lower and upper functions
Fixed several connection leaks
Fixed MySQL large result fetching

Changes from 2.8.0

It now uses Guava 25.1-jre version, which might require the client to also upgrade it to match the same version

Changes from 2.8.10

H2 version upgraded to 2.1.210
H2Engine is now deprecated and uses the H2v2 legacy mode if this engine is used with the H2v2 driver (for more information regarding the legacy mode see: http://www.h2database.com/html/features.html)
The H2V2Engine was created and it is the engine that should be used from now on (it works on regular mode, not legacy)

Changes from 2.8.14

Added a multithreaded implementation for batches, that uses multiple connections to the database. This may improve write performance to the database, if it is not otherwise limited by resource usage.
Batch implementations should now be obtained by calling the method DatabaseEngine#createBatch(BatchConfig), where the type of config dictates the type of batch implementation that is created.
NOTE: versions 2.8.12 and 2.8.13 already had introduced this, but due to a bug, creating a batch would change the DatabaseEngine, possibly causing it to malfunction. If using that version, avoid the new method to create batches.

Changes from 2.8.16

Version 2.8.15 added back compatibility with Guice v4, but also changed the method SqlBuilder.k to return K instead of Expression. This breaks compatibility with existing code compiled with older PDB versions, so 2.8.16 reverts this change.

Compiling PDB

In order to compile PDB you will need to have the Oracle Driver JAR in your local repository.
The current version assumes Oracle Driver version 12.2.0.1 (even when compiled with this version it is possible to use version 11 drivers in runtime; it is also possible to compile with version 11 instead, but the oracle pom dependency has to be modified).
Please download the driver from the respective Oracle driver page and run the following to install the driver in your local maven repository.

mvn install:install-file -DgroupId=com.oracle.jdbc -DartifactId=ojdbc8 \
-Dversion=12.2.0.1 -Dpackaging=jar -Dfile=ojdbc8.jar

Alternatively you can setup the username/password for accessing Oracle's Maven repository in your settings.xml file.

Running PDB tests

To test PDB with different database engines there are several Maven profiles that can be used, one for each vendor (check list of supported vendors below, under Establishing a connection).

Run the following to run the tests for the chosen vendor specified in lowercase:

mvn test -P<vendor>

NOTE: there is also a "special" profile for H2 to test that engine in server mode (instead of the default H2 embedded); for that case the profile h2remote is used in the <vendor> placeholder.

This will start a docker container running the chosen vendor's database server, and run the tests. The container will be stopped at the end if all tests pass, otherwise will be kept running.

Note: the containers will be started assuming the respective vendor's license agreements have been read and accepted. More info:
Microsoft SQL Server: https://hub.docker.com/r/microsoft/mssql-server-linux/
IBM DB2: https://hub.docker.com/r/ibmcom/db2express-c/

Getting started

Example Description

We describe a scenario where there are some data Providers that share Streams of data with the world. These Streams have a data Type, and they are consumed by some Modules. The entities and its relations are modeled into SQL using PDB in the following sections.

Establishing a connection

With PDB you connect to the database of your preference using the following code.

import static com.feedzai.commons.sql.abstraction.engine.configuration.PdbProperties.*;

(...)

Properties properties = new Properties() {
	{
		setProperty(JDBC, "<JDBC-CONNECTION-STRING>");
		setProperty(USERNAME, "username");
		setProperty(PASSWORD, "password");
		setProperty(ENGINE, "<PDB-ENGINE>");
		setProperty(SCHEMA_POLICY, "create");
	}
};

DatabaseEngine engine = DatabaseFactory.getConnection(properties);

The following table shows how to connect for the supported database vendors.

|Vendor|Engine|JDBC| |:---|:---|:---| |DB2|com.feedzai.commons.sql.abstraction.engine.impl.DB2Engine|jdbc:db2://<HOST>:<PORT>/<DATABASE>| |Oracle|com.feedzai.commons.sql.abstraction.engine.impl.OracleEngine|jdbc:oracle:thin:@<HOST>:1521:<DATABASE>| |PostgreSQL|com.feedzai.commons.sql.abstraction.engine.impl.PostgreSqlEngine|jdbc:postgresql://<HOST>/<DATABASE>| |MySQL|com.feedzai.commons.sql.abstraction.engine.impl.MySqlEngine|jdbc:mysql://<HOST>/<DATABASE>| |H2|com.feedzai.commons.sql.abstraction.engine.impl.H2Engine|jdbc:h2:<FILE> | jdbc:h2:mem| |SQLServer|com.feedzai.commons.sql.abstraction.engine.impl.SqlServerEngine|jdbc:sqlserver://<HOST>;database=<DATABASE>|

It is also important to select a schema policy. There are four possible schema policies:

create - New entities are created normally.
create-drop - Same as create policy but before the connection is closed all entries created during this session will be dropped.
drop-create - New entities are dropped before creation if they already exist.
none - The program is not allowed to create new entities.

PDB Pool Usage

PDB natively supports connection pools. You can create one using either a Properties or a Map<String, String> instance when defining the properties.

final DatabaseEnginePool dbPool = DatabaseEnginePool.getConnectionPool(properties);

Additionally, you can specify a modifier for the DatabaseEngine. This can be useful when you need to load entities to be able to insert entries on them.

final DatabaseEnginePool dbPool = DatabaseEnginePool.getConnectionPool(properties, engine -> engine.loadEntity(entity));

DatabaseEnginePool implements AutoClosable so you can close the pool when you need, for instance, before exiting the application.

Apart from the already described PDB configurations, you can configure your pool using the parameters in the following table.

The pool.generic.maxTotal, pool.generic.maxIdle, pool.generic.minIdle, and pool.generic.maxWaitMillis are the most common ones.

Pdb

Install / Use

README

PDB