PolyJDBC

PolyJDBC is a polyglot, lightweight wrapper around standard JDBC drivers with schema inspection/creation capabilities.

Why?

When developing SmartParam JDBC repository i realized that i was writing polyglot JDBC wrapper instead of focusing on core JDBC repository responsibilities. PolyJDBC came to life by extracting low-level wrapping code to separate project.

Before starting work on SmartParam JDBC repo i was looking for a good JDBC wrapper that would do more than suppress SQLExceptions. Specifically i was looking for transaction-aware wrapper that would also make it easy to perform DDL operations.

Target

PolyJDBC primary target are libraries that need light and cross-platform JDBC persistence. Hibernate is great, but it leaves little place for end-user customization, which is important when offering a library. It is also quite heavy. PolyJDBC size is only 75kB, no dependencies except for slf4j logging API.

Features

• polyglot (or better poly-dialect? multiple DB dialect support, including id generation strategies)
• transaction-oriented
• resources (Statements, ResultSets) are managed inside transaction scope
• intiutive transaction commit/rollback support
• option to leave transaction management to external framework (i.e. Spring)
• DDL operation DSL (CREATE TABLE/INDEX/SEQUENCE with constraints, DROP *)
• SQL query DSL (INSERT, SELECT, UPDATE, DELETE)
• lightweight
• schema inspection (table/sequence exists?)
• schema alteration

Supported database engines

• H2
• PostgreSQL
• MySQL
• Oracle
• Microsoft SQL Server
• DB2 and DB2400 (beta)

Thanks to testng magic PolyJDBC runs integration tests on each database. This way i can be sure that all features all supported across all engines.

How to get it?

repositories {
    mavenCentral()
}

dependencies {
    compile group: 'org.polyjdbc', name: 'polyjdbc', version: '0.5.0'
}

Enough, show me the code

Instantiation

By default, PolyJDBC takes care of transaction management on it's own:

Dialect dialect = DialectRegistry.H2.getDialect();
PolyJDBC polyjdbc = PolyJDBCBuilder.polyJDBC(dialect).connectingToDataSource(dataSource).build();

But it is possible to leave connection management to any external framework:

Dialect dialect = DialectRegistry.H2.getDialect();
PolyJDBC polyjdbc = PolyJDBCBuilder.polyJDBC(dialect).usingManagedConnections(() -> frameworkManager::getConnection).build();

Querying

To ask simple questions, use simple tool. SimpleQueryRunner performs each query in new transaction:

SelectQuery query = polyJdbc.query().selectAll().from("test").where("name = :name")
        .withArgument("name", "test");
        
Test test = polyJdbc.simpleQueryRunner().queryUnique(query, new TestMapper());

You might want to span your transaction across multiple statements, if so use TransactionRunner:

TransactionRunner transactionRunner = polyjdbc.transactionRunner();

Test test = transactionRunner.run(new TransactionWrapper<Test>() {
    @Override
    public Test perform(QueryRunner queryRunner) {
        SelectQuery query = polyJdbc.query().selectAll().from("test").where("name = :name")
            .withArgument("name", "test");
        return queryRunner.queryUnique(query, new TestMapper());
    }
});

transactionRunner.run(new VoidTransactionWrapper() {
    @Override
    public void performVoid(QueryRunner queryRunner) {
        DeleteQuery query = polyJdbc.query().delete().from("test").where("year < :year")
            .withArgument("year", 2012);
        queryRunner.delete(query);
    }
});

Or if you need to get your hands dirty, see QueryRunner, but remember to free the resources:

QueryRunner queryRunner = null;

try {
    queryRunner = polyjdbc.queryRunner();
    SelectQuery query = polyJdbc.query().selectAll().from("test").where("year = :year")
        .withArgument("year", 2013).limit(10);
    List<Test> tests = queryRunner.selectList(query, new TestMapper());
    queryRunner.commit()
}
catch(Exception exception) {
    polyjdbc.rollback(queryRunner);
    throw exception;
}
finally {
    polyjdbc.close(queryRunner);
}

Schema management

PolyJDBC comes with tools for schema creating and deletion. More options for schema inspection are planned, although there is no concrete release date.

To check if relation exists:

SchemaInspector schemaInspector = null;
try {
    schemaInspector = polyjdbc.schemaInspector();
    boolean relationExists = schemaInspector.relationExists("testRelation");
} finally {
    polyjdbc.close(schemaManager);
}

To create new schema (group of relations):

SchemaManager schemaManager = null;
try {
    schemaManager = polyjdbc.schemaManager();

    Schema schema = new Schema(configuration.getDialect());
    schema.addRelation("test_one")
        .withAttribute().longAttr("id").withAdditionalModifiers("AUTO_INCREMENT").notNull().and()
        .withAttribute().string("name").withMaxLength(200).notNull().unique().and()
        .withAttribute().integer("age").notNull().and()
        .primaryKey("pk_test_one").using("id").and()
        .build();
    schema.addSequence("seq_test_one").build();
    schema.addRelation("test_two")
        .withAttribute().longAttr("id").withAdditionalModifiers("AUTO_INCREMENT").notNull().and()
        .withAttribute().longAttr("fk_test_one").notNull().and()
        .foreignKey("fk_test_one_id").references("test_one", "id").on("fk_test_one").and()
        .build();
    schema.addSequence("seq_test_two").build();

    schemaManager.create(schema);
} finally {
    polyjdbc.close(schemaManager);
}

You don't need to define Schema object. Single Relation, Sequence or Index can be created using SchemaManager as well.

License

PolyJDBC is published under Apache License 2.0.

Changelog

• 0.6.3 (05.10.2016)
  • added the possibility to create relations in given schema (pr by @ianagius)
• 0.5.0
  • added possibility to plug in external framework for transaction management
  • [API change] PolyJDBC is build using PolyJDBCBuilder
• 0.4.0 (24.08.2014)
  • fixed bug with closing transaction on exception in query runners
  • [API change] QueryRunner.close() does not commit, use QueryRunner.commit() explicitly
  • [API change] some queries from QueryFactory.* now need Dialect (better Oracle support in future), use dialect-aware PolyJDBC.query()
• previous versions
  • support for PostgreSQL, MySQL, H2 and partial support for Oracle