PrettyJDBC Library

PrettyJDBC is a Java library that provides a simple and transparent way to work with a relational database. The library introduces a lightweight level of abstraction over JDBC technology. Using PrettyJDBC protects you from working with low-level statements and multiple checked exceptions. Since the library is a wrapper then you can always go back to a lower level to solve specific problems.

Getting started

Installation

To use PrettyJDBC you just need to include the prettyjdbc-x.x.jar file in the classpath.

If you are using automated build systems just add the following dependency to your project:

Maven:

<dependency>
  <groupId>com.github.marchenkoprojects</groupId>
  <artifactId>prettyjdbc</artifactId>
  <version>0.4.1</version>
</dependency>

Gradle:

compile("com.github.marchenkoprojects:prettyjdbc:0.4.1")

Creating a SessionFactory

Every application that uses PrettyJDBC is focused on an instance of SessionFactory. SessionFactory represents the basic mechanism for getting a Session object to work with database.

To work correctly you must configure and register javax.sql.DataSource into SessionFactory.

DataSource dataSource = ...

SessionFactory sessionFactory = SessionFactory.create(() -> dataSource);
// Work with sessionFactory instance

Working with Session

Session represents the physical connection between Java application and relational database. Session is a lightweight object, so it is always created (open session) when you need to execute a query. After working with the session it must be destroyed (close session). Right way to work with session is to wrap it in try-with-resources because then there is no need to worry about its closure.

To work with a Session it is enough to get instance from SessionFactory:

try(Session session = sessionFactory.openSession()) {
    // Working with session
}

If you don`t want to create an instance of a SessionFactory, you can use the static method of creating a Session from java.sql.Connection:

Connection connection = ...

try(Session session = SessionFactory.newSession(connection)) {
    // Working with session
}

But this method is recommended for informational purposes only!

Working with Transaction

A typical transaction should use the following idiom:

try (Session session = sessionFactory.openSession()) {
    Transaction tx = session.beginTransaction();
    try {
        // Working with transaction in the session
        tx.commit();
    } catch (Exception e) {
        tx.rollback();
    }
}

But to simplify working with transactions, you can use the lambda expression.

session.doInTransaction(currentSession -> {
    // Work in transaction
});

and to execute the transaction with returning the result:

R result = session.doInTransaction(currentSession -> {
    // Work in transaction with return of the result
    return ...;
});

Query execution

PrettyJDBC provides several types of queries:

• Query - supports basic operations and work with parameters by index;
• NamedParameterQuery - supports basic operations and work with parameters by name;
• TypedQuery - supports extraction of specific objects from the result set.

Creating and executing a simple query:

ReadOnlyScrollableResult scrollableResult = session
    .createNativeQuery("SELECT id, original_name FROM films LIMIT ?")
    .setParameter(1, 10)
    .execute();
while (scrollableResult.next()) {
    int id = scrollableResult.getInt("id");
    String originalName = scrollableResult.getString("original_name");
    // Work with data
}

but when there are many parameters then easier to specify them by name:

ReadOnlyScrollableResult scrollableResult = session
    .createQuery("SELECT * FROM films WHERE year >= :year LIMIT :limit")
    .setParameter("year", 2000)
    .setParameter("limit", 10)
    .execute();
// Work with scrollableResult

Often it is necessary to transform the data from the result set to a specific object:

// Specific object model.
public class Film {
    private int id;
    private String originalName;
    
    // default constructor and getters/setters
}

Film film = session
    .createQuery("SELECT id, original_name FROM films WHERE id = :filmId", Film.class)
    .setParameter("filmId", 100)
    .setResultMapper(resultSet -> {
        Film newFilm = new Film();
        newFilm.setId(resultSet.getInt("id"));
        newFilm.setOriginalName(resultSet.getString("original_name"));
        return newFilm;
    })
    .unique();