Java connection pool postgresql

# Connect to PostgreSQL from Java

The API to use a relational database from Java is JDBC.

This API is implemented by a JDBC driver.

To use it, put the JAR-file with the driver on the JAVA class path.

This documentation shows samples how to use the JDBC driver to connect to a database.

# Connecting with java.sql.DriverManager

This is the simplest way to connect.

First, the driver has to be registered with java.sql.DriverManager so that it knows which class to use.
This is done by loading the driver class, typically with java.lang.Class.forname(****) .

/** * Connect to a PostgreSQL database. * @param url the JDBC URL to connect to; must start with "jdbc:postgresql:" * @param user the username for the connection * @param password the password for the connection * @return a connection object for the established connection * @throws ClassNotFoundException if the driver class cannot be found on the Java class path * @throws java.sql.SQLException if the connection to the database fails */ private static java.sql.Connection connect(String url, String user, String password) throws ClassNotFoundException, java.sql.SQLException < /* * Register the PostgreSQL JDBC driver. * This may throw a ClassNotFoundException. */ Class.forName("org.postgresql.Driver"); /* * Tell the driver manager to connect to the database specified with the URL. * This may throw an SQLException. */ return java.sql.DriverManager.getConnection(url, user, password); > 

Not that user and password can also be included in the JDBC URL, in which case you don’t have to specify them in the getConnection method call.

# Connecting with java.sql.DriverManager and Properties

Instead of specifying connection parameters like user and password (see a complete list here

(opens new window) ) in the URL or a separate parameters, you can pack them into a java.util.Properties object:

/** * Connect to a PostgreSQL database. * @param url the JDBC URL to connect to. Must start with "jdbc:postgresql:" * @param user the username for the connection * @param password the password for the connection * @return a connection object for the established connection * @throws ClassNotFoundException if the driver class cannot be found on the Java class path * @throws java.sql.SQLException if the connection to the database fails */ private static java.sql.Connection connect(String url, String user, String password) throws ClassNotFoundException, java.sql.SQLException < /* * Register the PostgreSQL JDBC driver. * This may throw a ClassNotFoundException. */ Class.forName("org.postgresql.Driver"); java.util.Properties props = new java.util.Properties(); props.setProperty("user", user); props.setProperty("password", password); /* don't use server prepared statements */ props.setProperty("prepareThreshold", "0"); /* * Tell the driver manager to connect to the database specified with the URL. * This may throw an SQLException. */ return java.sql.DriverManager.getConnection(url, props); > 

# Connecting with javax.sql.DataSource using a connection pool

It is common to use javax.sql.DataSource with JNDI in application server containers, where you register a data source under a name and look it up whenever you need a connection.

This is code that demonstrates how data sources work:

/** * Create a data source with connection pool for PostgreSQL connections * @param url the JDBC URL to connect to. Must start with "jdbc:postgresql:" * @param user the username for the connection * @param password the password for the connection * @return a data source with the correct properties set */ private static javax.sql.DataSource createDataSource(String url, String user, String password) < /* use a data source with connection pooling */ org.postgresql.ds.PGPoolingDataSource ds = new org.postgresql.ds.PGPoolingDataSource(); ds.setUrl(url); ds.setUser(user); ds.setPassword(password); /* the connection pool will have 10 to 20 connections */ ds.setInitialConnections(10); ds.setMaxConnections(20); /* use SSL connections without checking server certificate */ ds.setSslMode("require"); ds.setSslfactory("org.postgresql.ssl.NonValidatingFactory"); return ds; > 

Once you have created a data source by calling this function, you would use it like this:

/* get a connection from the connection pool */ java.sql.Connection conn = ds.getConnection(); /* do some work */ /* hand the connection back to the pool - it will not be closed */ conn.close(); 

# Remarks

The JDBC URL can take one of these forms:

**parameters** is a list of **key**[=**value**] pairs, headed by ? and separated by & . If the **value** is missing, it is assumed to be true .

jdbc:postgresql://localhost/test?user=fred&password=secret&ssl&sslfactory=org.postgresql.ssl.NonValidatingFactory 

Источник

Пулы соединений к БД — зачем и почему

Когда Ваш проект начинает пользуется популярностью и каждая миллисекунда обработки запроса от пользователя становится критической, приходится искать узкие места в системе. Часто больше всего времени занимает выполнение SQL запроса из приложения к базе данных. Попробуем разобраться, что можно оптимизировать в программе при работе с БД.

Теория

1. Открытие соединения к БД и отправка запроса серверу.
2. Сервер парсит SQL запрос.
3. Сервер оптимизирует запрос исходя из правил, а так же из статистики по таблицам. В результате строится план выполнения запроса.
4. Сервер выполняет запрос в соответствии с ранее построенном планом и отправляет результаты пользователю.

Так же можно избежать повторного исполнения шагов два и три если мы будем использовать связанные переменные при написание запросов и кешировать результаты шага три, которые мы получаем от сервера.

В настоящее время большинство драйверов для работы с БД поддерживают работу с пулами соединений. Однако всегда есть соблазн написать свою реализацию, которая будет работать быстрее. Давайте проверим сколько мы выиграем используя пулы соединений и кеширование, как в коробочном решении так и в самописном.

Способ измерения

Для тестов используем свободно распространяемую СУБД PostgreSQL, а клиент напишем на JAVA. В БД создадим небольшую таблицу test.test_table (около 10 строк), состоящую из первичного ключа id и строкового значения value. Пусть у нас клиенты параллельно выполняют запросы к БД, для этого создадим потоки, которые будут делать простые запросы поиска по первичному ключу в этой таблице. При создании потоков мы будем указывать различную реализацию пулов соединений, что позволит нам сравнить производительность, т.к. поток будет считать суммарное время потраченное им на выполнение 100 запросов.

 class TestThread extends Thread < private DBPool pool; private long workTime = 0; private long foundStr = 0; @Override public void run() < workTime = System.currentTimeMillis(); // Засекаем время Connection con = null; PreparedStatement st = null; ResultSet rs = null; Random rnd = new Random();// будем использовать как первичный ключ в запросе for (int i = 0; i < 100; i++) < try < con = pool.getConnection();// получем соединение к БД // отправляем запрос на парсинг и построение плана выполнения st = con.prepareStatement("SELECT a.* FROM test.test_table a WHERE st.setObject(1, rnd.nextInt(10)); rs = st.executeQuery();// выполняем запрос if (rs.next()) < String tmp = (rs.getString(2)); // обрабатываем результат if (tmp != null) < foundStr++; >> > catch (SQLException ex) < //ошибка при выполнении, выводим в консоль System.out.println("Pool " + pool + " exeption " + ex); >finally < // и в конце, аккуратно закрываем все использованные нами объекты try < if (rs != null) rs.close(); >catch (SQLException e) < //ignore >try < if (st != null) st.close(); >catch (SQLException e) < //ignore >try < if (con != null) pool.putConnection(con); // кладем соединение обратно в пул >catch (SQLException e) < //ignore >> > workTime = System.currentTimeMillis() - workTime; // получаем потраченное время > > 

Теперь сделаем несколько пулов, и сравним производительность.
Первым будет классический, который на каждый запрос открывает соединение с сервером и после выполнения запроса закрывающий его.

 class DBPool < private String url, user, password; DBPool(String url, String user, String password) throws ClassNotFoundException < this.url = url; this.user = user; this.password = password; Class.forName("org.postgresql.Driver"); >public Connection getConnection() throws SQLException < return DriverManager.getConnection(url, user, password); >public void putConnection(Connection connection) throws SQLException < connection.close(); >> 

Вторым будет, использующий специальный кеширующий источник данных класс из JDBC драйвера к PostgreSQL — PGPoolingDataSource. Который позволяет задать размер пула соединений, а так же начальное количество соединений. Кроме того в настройках у PreparedStatement есть настройка setPrepareThreshold — отвечающая за количество выполнений запроса, после которого запрос кешируется и не требует парсинга и построения плана выполнения.

 class DBPoolCache extends DBPool < private PGPoolingDataSource source; DBPoolCache(String host, String database, String user, String password) < source = new PGPoolingDataSource(); source.setDataSourceName("A Data Source"); source.setServerName(host); source.setDatabaseName(database); source.setUser(user); source.setPassword(password); source.setMaxConnections(20);//Максимальное значение source.setInitialConnections(20);//Сколько соединений будет сразу открыто >public Connection getConnection() throws SQLException < return source.getConnection(); >public void putConnection(Connection connection) throws SQLException < connection.close(); >> 

Ну и в конце нашу реализацию пулов, когда мы сами кешируем соединения к БД а также результаты разбора SQL запроса (PreparedStatement).

 class DBPoolCacheMy extends DBPool < private String url, user, password; private PGSimpleDataSource source; private BlockingQueueconnections = new ArrayBlockingQueue(20); DBPoolCacheMy(String host, String database, String user, String password) throws SQLException < source = new PGSimpleDataSource(); source.setServerName(host); source.setDatabaseName(database); source.setUser(user); source.setPassword(password); for (int i = 0; i < 20; i++) > public Connection getConnection() throws SQLException < try < // пробуем получить свободное соединение return connections.poll(2, TimeUnit.SECONDS); >catch (InterruptedException e) < return null; >> public void putConnection(Connection connection) throws SQLException < connections.add(connection); >> 

Так же придётся реализовать свой класс соединения с БД, который будет осуществлять кеширование PreparedStatement.

 class MyConnection implements Connection < private Connection connection; protected MyConnection(Connection connection) < this.connection = connection; >private ConcurrentHashMap statements = new ConcurrentHashMap(); public PreparedStatement prepareStatement(String sql) throws SQLException < PreparedStatement statement = statements.get(sql); if (statement == null) < statement = new MyStatement(connection.prepareStatement(sql)); statements.put(sql, statement); >return statement; > . > 

 class MyStatement implements PreparedStatement < private PreparedStatement statement; MyStatement(PreparedStatement statement) throws SQLException < this.statement = statement; ((PGStatement) statement).setPrepareThreshold(1); >public void close() throws SQLException < //ignore >. > 

Заключение

Ну и наконец сравним производительность трех различных пулов соединений, запустим тесты с количеством параллельных потоков от 1 до 10, для различных реализаций. В результате получился следующая зависимость общего времени выполнения задачи от количества потоков.

Из графика видно, что кешировать соединения с БД явно нужно, это даёт значительный прирост производительности системы. А вот писать самописную реализацию кеширования соединений и PreparedStatement не даёт ощутимой выгоды.

Источник

postgresql Connect to PostgreSQL from Java Connecting with javax.sql.DataSource using a connection pool

It is common to use javax.sql.DataSource with JNDI in application server containers, where you register a data source under a name and look it up whenever you need a connection.

This is code that demonstrates how data sources work:

/** * Create a data source with connection pool for PostgreSQL connections * @param url the JDBC URL to connect to. Must start with "jdbc:postgresql:" * @param user the username for the connection * @param password the password for the connection * @return a data source with the correct properties set */ private static javax.sql.DataSource createDataSource(String url, String user, String password) < /* use a data source with connection pooling */ org.postgresql.ds.PGPoolingDataSource ds = new org.postgresql.ds.PGPoolingDataSource(); ds.setUrl(url); ds.setUser(user); ds.setPassword(password); /* the connection pool will have 10 to 20 connections */ ds.setInitialConnections(10); ds.setMaxConnections(20); /* use SSL connections without checking server certificate */ ds.setSslMode("require"); ds.setSslfactory("org.postgresql.ssl.NonValidatingFactory"); return ds; >

Once you have created a data source by calling this function, you would use it like this:

/* get a connection from the connection pool */ java.sql.Connection conn = ds.getConnection(); /* do some work */ /* hand the connection back to the pool - it will not be closed */ conn.close(); 

PDF — Download postgresql for free

Источник