H2 Database Connection Pool Example

By admin | July 19, 2014 | Updated : May 01, 2016 database, h2, in-memory, No Comments

In this H2 Database Connection Pool Example explains about the implementation of connection pool using H2 Database

H2 is an open source software implementation of Java SQL database. The main features of H2 are.

Very fast, open source, JDBC API
Embedded and server modes; in-memory databases
Browser based Console application
Small footprint: around 1.5 MB jar file size

Reference -> http://www.h2database.com/html/main.html

Note

Other H2 examples

1)    H2 Database Example
2)    H2 File Database Example
3)    H2 In-Memory Database Example
4)    H2 Database Connection Pool Example
5)    How To view Content Of H2 In-memory & File Database
6)    H2 Batch Update Example With SQL Statement & PreparedStatment

Note

Different Connection Pool Implementation examples

1)    DBCP Connection Pooling Example
2)    C3P0 Connection Pooling Example
3)    BoneCP Connection Pooling Example
4)    H2 Database Connection Pool Example

Usage Of H2 JdbcConnectionPool Class

Creating a new connection for each user can be time consuming, which affects the over all performance of the system. In order to solve this issue, we are using a connection pool.

In H2 database, we can implement JDBC Connection Pool without using any third party libraries. It is is in-built on H2, we are using JdbcConnectionPool class in order to create the jdbc connection pool.

Required Libraries

For using H2 database, You need to download

h2-1.4.182.jar

Project Structure

H2 Database Connection Pool Example

Simple H2 Database Connection Pool Example

In below code you can see that we are using following H2 JDBC URL jdbc:h2:~/test for connecting the database, here 'test' is in the user home directory

In the method getConnectionPool(), we are using JdbcConnectionPool class for creating the Connection Pool

package com.h2.examples;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

import org.h2.jdbcx.JdbcConnectionPool;
import org.h2.tools.DeleteDbFiles;

// H2 Database ConnectionPool Example
public class H2DatabaseConnectionPoolExample {

    private static final String DB_DRIVER = "org.h2.Driver";
    private static final String DB_CONNECTION = "jdbc:h2:~/test";
    private static final String DB_USER = "";
    private static final String DB_PASSWORD = "";

    public static void main(String[] args) throws Exception {
        try {
            // delete the database named 'test' in the user home directory for initialization
            DeleteDbFiles.execute("~", "test", true);
            batchInsertWithStatement();
            batchInsertWithPreparedStatement();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void batchInsertWithPreparedStatement() throws SQLException {
        JdbcConnectionPool jdbcConnectionPool = getConnectionPool();
        Connection connection = jdbcConnectionPool.getConnection();
        PreparedStatement preparedStatement = null;

        String Query = "INSERT INTO PERSON" + "(id, name) values" + "(?,?)";
        try {
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(Query);

            preparedStatement.setInt(1, 4);
            preparedStatement.setString(2, "Rockey");
            preparedStatement.addBatch();

            preparedStatement.setInt(1, 5);
            preparedStatement.setString(2, "Jacky");
            preparedStatement.addBatch();

            int[] countWithoutException = preparedStatement.executeBatch();
            System.out.println("OK: countWithoutException = " + countWithoutException.length);
            connection.commit();
        } catch (BatchUpdateException e) {
            System.out.println("Exception Message " + e.getLocalizedMessage());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            preparedStatement.close();
            connection.close();
            jdbcConnectionPool.dispose();
        }
    }

    private static void batchInsertWithStatement() throws SQLException {
        JdbcConnectionPool jdbcConnectionPool = getConnectionPool();
        Connection connection = jdbcConnectionPool.getConnection();
        Statement stmt = null;
        try {
            connection.setAutoCommit(false);
            stmt = connection.createStatement();
            stmt.execute("CREATE TABLE PERSON(id int primary key, name varchar(255))");
            stmt.addBatch("INSERT INTO PERSON(id, name) VALUES(1, 'A')");
            stmt.addBatch("INSERT INTO PERSON(id, name) VALUES(2, 'B')");
            stmt.addBatch("INSERT INTO PERSON(id, name) VALUES(3, 'C')");

            int[] countWithoutException = stmt.executeBatch();
            System.out.println("OK: countWithoutException = " + countWithoutException.length);

            connection.commit();
        } catch (BatchUpdateException e) {
            System.out.println("Exception Message " + e.getLocalizedMessage());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            stmt.close();
            connection.close();
            jdbcConnectionPool.dispose();
        }
    }

    // Create H2 JdbcConnectionPool
    private static JdbcConnectionPool getConnectionPool() {
        JdbcConnectionPool cp = null;
        try {
            Class.forName(DB_DRIVER);
        } catch (ClassNotFoundException e) {
            System.out.println(e.getMessage());
        }
        cp = JdbcConnectionPool.create(DB_CONNECTION, DB_USER, DB_PASSWORD);
        return cp;
    }
}

Output

Inserted Count Using SQL Statement  = 3
Inserted Count Using SQL PreparedStatement = 2

You might also like following tutorials :

Usage Of H2 JdbcConnectionPool Class

Required Libraries

Project Structure

Simple H2 Database Connection Pool Example

Output

Related Posts