Connection

In this section, we will explore how you can establish a connection to a datasource and how you can use it with Spgeed.

To establish the connection and make it available to your application, we will go through two basic steps :

1. Create and configure the datasource itself
2. Create a session object which will hold the datasource object, using it whenever a database access is required. As we will see, the session object will also be responsible for delivering DAO instances to the rest of your application.

Establishing a connection to the datasource

The datasource connection itself will be handled by the Spgeed session as you will see in the next sub section. However, you still need to create a datasource object which will hold the connection parameters to your datasource. This can be done in various ways :

• either you can defined the datasource manually within your application
• or you can use JNDI and rely on your application server configuration for instance.

Here is how you can define manually a datasource within your application:

PGSimpleDataSource dataSource = new PGSimpleDataSource();
dataSource.setUrl("jdbc:postgresql://127.0.0.1:5432/mydb");
dataSource.setUser("myuser");
dataSource.setPassword("mypasswd");

And here is how you could do the same using JNDI with Tomcat:

1. Declare the datasource in the configuration file

<Context>
    ...
    <Resource name="jdbc/postgres" auth="Container"
          type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
          url="jdbc:postgresql://127.0.0.1:5432/mydb"
          username="myuser" password="mypasswd" maxActive="20" maxIdle="10" maxWait="-1"/>
</Context>

1. Retrive the datasource in your application :

Context initContext = new InitialContext();
Context envContext  = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/postgres");

Using a Spgeed session to manage datasource connection

The Spgeed session is used to manage the connection and the transactions to the datasource. It is also responsible for instanciating and delivering the DAO classes to the rest of your application.

The session holds a reference to the connection thanks to the datasource object we created in the previous sub section.

Warning

Don't forget to close the session, when you no longer needs it, to avoid keeping active a connection to the database.

Autoclose

The session is autoclosable which make it easy to use with try-whith-ressources syntax : when you use try-with-resources syntax, you don't need to worry about closing the connection, it will be automatically closed when it is no longer needed.

Here is how you can create a session from a datasource:

DataSource ds = /* get your datasource */;

try (SqlSession session = new SqlSession(ds)) {
    // ...
}

If you only need a one-time session, you can actually aslo create a session without bothering with the datasource creation:

try (SqlSession session = new SqlSession(url, username, password)) {
    // ...
}

Once you created a session, you can now get a DAO to perform a request to your database.

Here is how you can retrieve an instance of one of your DAO:

JourneyDao journeyDao = session.getDao(JourneyDao);
Journey journey = journeyDao.getJourneyByName("Guadeloupe")

Dao constructor params

If you need to give some arguments to the constructor of your DAO, you can pass them to the getDao function : JourneyDao journeyDao = session.getDao(JourneyDao, myParam1, myParam2)

How to deal with transactions

Transactions are managed implicitly by the session object : a new transaction is created on the first execution of a statement. After that, the transaction is kept alive until someone :

• commit or rollback the change,
• close the session

Close

When the session is close, the transaction is automatically committed.

Here is how you can commit the change of a transaction:

session.commit();

And here is how you can rollback:

session.rollback();

Once the change of a transaction have been commited or rolled back, a new transaction will be created for the next statement. As mentionned before, this new transaction will stay open until the new change are either commited or rolled back.

Using a connection pool

Spgeed is compatible with connection pools. We recommand to use HikariCP.

Example :

HikariDataSource ds = new HikariDataSource();
ds.setJdbcUrl("jdbc:postgresql://127.0.0.1:5432/mydb");
ds.setUsername("myuser");
ds.setPassword("mypasswd");

Using an embedded PostgreSQL database

Sometimes, you might want to embed a PostgreSQL database directly into your application. This can be really useful for application testing (unit tests for instance).

The following example relies on otj-pg-embedded which is a library that allows you to embed PostgreSQL into your application with no external dependencies.

public class YourTest {

    @Rule
    public SingleInstancePostgresRule database = EmbeddedPostgresRules.singleInstance();

    @Test
    public void yourTest() throws Exception {
        DataSource ds = database.getEmbeddedPostgres().getPostgresDatabase();
        try (SqlSession session = new SqlSession(ds)) {
            // ...
        }
    }

    // ...
}