import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.dbcp2.cpdsadapter.DriverAdapterCPDS;
import org.apache.commons.dbcp2.datasources.SharedPoolDataSource;

public class DBConnectionPool {

    private static final String DB_DRIVER = "org.postgresql.Driver";
    private static final String DB_CONNECTION = "jdbc:postgresql://localhost:5432/gtm";
    private static final String DB_USER = "user";
    private static final String DB_PASSWORD = "12345";

    /**
     * Максимальное количество активных соединений, которые можно использовать
     * одновременно. -1 - неограниченное количество соединений
     */
    private static final int MAX_ACTIVE = 100;

    /**
     * Максимальное количество милисекунд, которые пул будед ждать (когда нет
     * свободных соединений) прежде, чем выбросить исключение. -1 - ожидание
     * будет длится неопределенное время
     */
    private static final int MAX_WAIT = 100;

    /**
     * Соедеинение с БД
     */
    private Connection conn = null;

    /**
     * Источник данных
     */
    private static DataSource ds = null;

    /**
     * Конструктор, определяет переменные для соединения с БД
     */
    public DBConnectionPool() {
        if (ds == null) {
            try {
                //Адаптер для JDBC-драйвера. Хранит параметры подключения к БД 
                DriverAdapterCPDS pcds = new DriverAdapterCPDS();
                pcds.setDriver(DB_DRIVER);
                pcds.setUrl(DB_CONNECTION);
                pcds.setUser(DB_USER);
                pcds.setPassword(DB_PASSWORD);

                //Реализация интерфейса DataSource для разделяемого пула соеднинений 
                SharedPoolDataSource tds = new SharedPoolDataSource();
                tds.setConnectionPoolDataSource(pcds);

                tds.setMaxTotal(MAX_ACTIVE);

                tds.setMaxConnLifetimeMillis(MAX_WAIT);

                ds = tds;

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

    /**
     * Возвращает соединение с БД
     */
    public Connection openConnection() throws SQLException {
        if (conn == null) {
            conn = ds.getConnection();
        }
        return conn;

    }

    /**
     * Возвращает количество активных соединений с БД
     *
     * @return количество активных соединений с БД
     */
    public static int getActiveConnection() {
        return ((SharedPoolDataSource) ds).getNumActive();
    }

    /**
     * Если соединение открыто, то возвращает соединение с DB
     */
    public Connection getConnection() throws SQLException {
        return openConnection();
    }

    /**
     * Закрыть соединение
     */
    public void close() {
        try {
            if (conn != null) {
                conn.close();
                conn = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws Exception {
        DBConnectionPool pool = new DBConnectionPool();
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        try {
            final Connection connection = pool.getConnection();
            pstmt = connection.prepareStatement("select visibleName from Users where enable is true");
            rset = pstmt.executeQuery();
            while (rset.next()) {
                final String name = rset.getString("visibleName");
                System.out.println("Пользователь: " + name);
            }
            System.out.println(connection);
            System.out.println(pstmt);
            System.out.println(rset);
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            if (rset != null) {
                rset.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            //Обязательно возвращаем соединение в пул 
            pool.close();
        }

    }
}

private final List<Users> usersList = new ArrayList<Users>();
private DBConnectionPool pool = new DBConnectionPool();
private Connection con = null;
private PreparedStatement pstmt = null;
private ResultSet rs = null;

 public Users[] getAllUsers() throws Exception {
        String sql = "select * from users";
        usersList.clear();
        try {
            con = pool.getConnection();
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Users user = new Users();
                user.setId(rs.getInt("id"));
                user.setLogin(rs.getString("login"));
                usersList.add(user);
            }
            return usersList.toArray(new Users[0]);
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            try {
                try (Connection con1 = con) {
                }
                try (PreparedStatement pstmt1 = pstmt) {
                }
                try (ResultSet rs1 = rs) {
                }
                try {
                    pool.close();
                } catch (Exception e) {
                }
            } catch (Exception ignore) {
            }
        }
        return null;
    }

package com.javatalks.example.dao;
 
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
 
import javax.sql.DataSource;
 
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
 
import ru.javatalks.example.model.Book;
import ru.javatalks.example.model.BookSearchCriteria;
 
public class BookDaoImpl implements BookDao {
 
	private JdbcTemplate jdbcTemplate;
	private NamedParameterJdbcTemplate namedTemplate;
 
    public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
		this.namedTemplate = new NamedParameterJdbcTemplate(dataSource);
	}
 
	private RowMapper<Book> rowMapper = new RowMapper<Book>() {
		@Override
		public Book mapRow(ResultSet rs, int rowNum) throws SQLException {
			Book book = new Book();
			book.setId(rs.getInt("id"));
			book.setComment(rs.getString("comment"));
			book.setDateRelease(rs.getDate("date_release"));
			book.setTitle(rs.getString("title"));
			book.setAuthorId(rs.getInt("author_id"));
			return book;
		}
	};
 
	@Override
	public void insert(Book book) {
		String sql = "INSERT INTO book(title, comment, date_release, author_id, id) VALUES(?,?,?,?,?)";
		jdbcTemplate.update(sql, getPreparedStatementSetter(book));
	}
 
	@Override
	public void update(Book book) {
		String sql = "UPDATE book SET title=?, comment=?, date_release=?, author_id=? WHERE id=?";
		jdbcTemplate.update(sql, getPreparedStatementSetter(book));
	}
 
	@Override
	public void delete(Book book) {
		jdbcTemplate.update("DELETE FROM book WHERE id=?", book.getId());
	}
 
	@Override
	public Book getById(Integer id) {
		return jdbcTemplate.queryForObject("SELECT * FROM book WHERE id=?", rowMapper, id);
	}
 
	@Override
	public List<Book> getAll() {
		return jdbcTemplate.query("SELECT * FROM book", rowMapper);
	}
 
	@Override
	public List<Book> findByCriteria(BookSearchCriteria criteria) {
		if (criteria.isEmpty()) {
			return getAll();
		}
		String sql = "SELECT * FROM book WHERE true";
		if (criteria.getComment() != null) {
			sql += " AND comment=:comment";
		}
		if (criteria.getTitle() != null) {
			sql += " AND title=:title";
		}
		if (criteria.getMaxDateRelease() != null) {
			sql += " AND date_release<:maxDateRelease";
		}
		if (criteria.getMinDateRelease() != null) {
			sql += " AND date_release>:minDateRelease";
		}
		if (criteria.getAuthorId() != null) {
			sql += " AND author_id=:authorId";
		}
		BeanPropertySqlParameterSource namedParameters = new BeanPropertySqlParameterSource(criteria);
		return namedTemplate.query(sql, namedParameters, rowMapper);
	}
	
	private PreparedStatementSetter getPreparedStatementSetter(final Book book) {
		return new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				int i = 0;
				ps.setString(++i, book.getTitle());
				ps.setString(++i, book.getComment());
				ps.setDate(++i, new java.sql.Date(book.getDateRelease().getTime()));
				ps.setInt(++i, book.getAuthorId());
				ps.setInt(++i, book.getId());
			}
		};
	}
 
}