Quiz: DAO Pattern and HikariCP Connection Pool in JDBC

Total: 6 questions

1. 

What is the DAO pattern in Java, and why use it?

A DAO (data access object) is an object that provides an abstract interface to a database or another persistence mechanism. All SQL for the users table lives in one UserDao class, and services call its methods (findById, create) without knowing whether JDBC or Hibernate runs inside.

Benefits: a schema change touches one class; the implementation can be swapped (JDBC for JPA) without changing the calling code; a DAO is easy to test by passing it a DataSource for an in-memory H2 database.

A common approach is a generic Dao<K, T> interface (K is the key type, T the entity) with CRUD methods: findAll, findById returning Optional<T> instead of null, create, and update and delete returning a boolean — whether exactly one row changed.

A DDD Repository differs in that it describes a collection of domain objects and may assemble an object from several tables. With JPA the DAO layer stays, with an EntityManager inside, and in Spring its role is played by Spring Data interfaces.

2. 

How do you write a DAO implementation with JDBC? Which decisions matter in a UserDao class?

  • DataSource in the constructor. The DAO keeps a DataSource in a field and in every method borrows a Connection for one operation in try-with-resources. The DAO knows neither the URL nor the password.
  • Generated id. In create, the statement is prepared with the Statement.RETURN_GENERATED_KEYS flag, and the key is read with getGeneratedKeys(). SELECT MAX(id) does not work: with concurrent inserts, two users get the same id.
  • update and delete return executeUpdate() == 1: false means there is no row with that id.
  • DaoException. SQLException is not silenced with System.out.println, otherwise findAll() quietly returns an empty list when the database is down. It is wrapped in your own unchecked exception with the cause: throw new DaoException("...", e).
  • mapRow. Turning a ResultSet row into an object happens in one method that reads columns by name: rs.getInt("id") survives a change in the column order of the SELECT, rs.getInt(1) does not.
3. 

Where should you keep the database URL, user name and password, and what should read database.properties: Properties or ResourceBundle?

Connection settings move out of the code into a database.properties file (db.url, db.user, db.password). In a Maven project it goes into src/main/resources and ends up at the root of the classpath. Switching between a test and a production database then needs no recompilation, and the DAO receives a ready DataSource.

Settings are usually read with java.util.Properties: open the file with getClassLoader().getResourceAsStream("database.properties") and call props.load(in). Differences from ResourceBundle:

  • ResourceBundle is built for localization and looks up files by locale; Properties holds any "key = value" pairs from any InputStream.
  • Missing key: ResourceBundle throws MissingResourceException, Properties returns null or the default from getProperty(key, default).
  • ResourceBundle caches the file, so edits are not visible without a restart.

A file with the real password is never committed to Git: the repository gets a database.properties.example, and on the server the password comes from the environment variable System.getenv("DB_PASSWORD").

4. 

What is a connection pool, and why do you need one? What does close() do on a pooled connection?

A connection pool is a set of database connections opened in advance: the program borrows one for a single operation and gives it back. Opening a connection is expensive: TCP, authentication, session setup. With DriverManager.getConnection every request pays that price, while a pool pays it once at startup.

A pool implements javax.sql.DataSource, so DAO code stays the same: it calls dataSource.getConnection() and closes the connection with try-with-resources. But close() on a pooled connection does not disconnect from MySQL, it returns the connection to the pool.

A pool also limits the load on the database. If 500 requests arrive and the pool has 10 connections, only 10 queries reach MySQL at once and the rest wait. Without a pool, 500 threads open their own connections, and MySQL answers Too many connections as soon as max_connections is exceeded.

5. 

How do you add and configure the HikariCP connection pool? What are its key settings?

Add the com.zaxxer:HikariCP dependency (5.x and 6.x need Java 11+, for Java 8 use 4.0.x). You still need the mysql-connector-j driver: the pool manages the connections the driver opens.

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/catalog");
config.setUsername("root");
config.setPassword("admin");
config.setMaximumPoolSize(10);
HikariDataSource dataSource = new HikariDataSource(config);

Key settings and defaults: maximumPoolSize = 10; minimumIdle = maximumPoolSize; connectionTimeout = 30,000 ms; idleTimeout = 600,000 ms (10 minutes); maxLifetime = 1,800,000 ms (30 minutes); leakDetectionThreshold = 0 (off). The same keys can live in a file passed to new HikariConfig("/hikari.properties").

Close the pool when the program stops: dataSource.close(). Start the size at 10 and tune it by measurement. Spring Boot creates HikariCP itself, and pool settings use the spring.datasource.hikari prefix.

6. 

What is a connection leak, and what other mistakes are common when a DAO works with a pool?

A connection leak is a connection borrowed from the pool and never closed. The pool keeps it marked as busy. Once there are maximumPoolSize such connections, the next getConnection() waits for connectionTimeout and throws SQLTransientConnectionException (total=2, active=2, idle=0). The fix is to borrow connections in try-with-resources. setLeakDetectionThreshold(2000) helps find the leak: HikariCP logs the stack trace of the place where the connection was borrowed.

Other common mistakes:

  • A Connection in a DAO field: threads share one connection, and after the MySQL wait_timeout queries fail. Keep a DataSource in the field.
  • A new HikariDataSource in every DAO or method: each pool opens its own connections, and MySQL hits Too many connections. Use one pool per application.
  • catch (SQLException e) { System.out.println(...); }: a database error turns into an empty list. Throw a DaoException.
  • The pool is not closed on shutdown: connections hang until they time out. Call dataSource.close().
Page 1 of 1