JDBC

Almost every real Java app stores data in a database. By the end of this lesson you'll connect to one, run safe parameterized queries, read results, and wrap changes in transactions — the foundation every ORM is built on.

Learn JDBC in our free Java course — a beginner-friendly interactive lesson with worked examples, a practice exercise and a quick reference.

Part of the free Java course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

JDBC is the standard Java API for talking to a relational database. It's a small set of objects you use in order: a Connection (your session with the database), a Statement or PreparedStatement (the SQL you want to run), and a ResultSet (the rows that come back).

💡 Analogy: Using JDBC is like making a phone call. You dial (open a Connection), speak (send a query), listen (read the ResultSet), and hang up (close everything). A PreparedStatement is like a fill-in-the-blank form — you hand over the values separately so they can never be mistaken for instructions.

You open a connection with DriverManager.getConnection(url, user, password) . The JDBC URL tells Java which database to reach and how — for example jdbc:h2:mem:demo (H2 in memory) or jdbc:postgresql://localhost:5432/shop (Postgres).

Wrap the Connection, every Statement, and every ResultSet in try-with-resources ( try (Connection conn = ...) ). Java then closes them automatically when the block ends — even on an exception — so you never leak connections.

Two execute methods cover everything: executeQuery() for SELECT (it returns a ResultSet ), and executeUpdate() for INSERT / UPDATE / DELETE (it returns an int , the row count). Read each row by looping while (rs.next()) and pulling columns with rs.getInt(...) / rs.getString(...) .

Finish the three blanks: set the third parameter, run the insert, and advance the ResultSet. The expected output is in the comments so you can check yourself.

SQL injection is the #1 database vulnerability in the world. It happens when you build SQL by gluing user input into the query string. A crafted input can then break out of where you intended it and rewrite the query.

If input is , the WHERE clause is always true — every row leaks.

With a PreparedStatement the SQL is parsed before your value is supplied, so the input is bound as a single literal value. It can never become part of the query's structure — the attack just searches for a user literally named and finds nobody.

By default JDBC auto-commits — every statement is made permanent the instant it runs. That's wrong when several changes must happen together. A bank transfer is the classic case: if the debit succeeds but the credit fails, money vanishes.

Call conn.setAutoCommit(false) to begin a transaction . Now nothing is permanent until you call conn.commit() . If anything goes wrong, conn.rollback() undoes every change since the transaction started. Put commit() at the end of the happy path and rollback() in the catch block.

Fill the four blanks: turn off auto-commit, commit on success, and roll back on failure. The expected output is in the comments.

Opening a connection is slow — a network round trip plus authentication. Doing it for every request can be 100–1000× slower than reusing one. A connection pool keeps a handful of open connections ready and lends them out, so getConnection() is nearly instant and close() just returns the connection to the pool.

HikariCP is the fastest and most popular pool — Spring Boot uses it by default. You configure a DataSource once, then your JDBC code is identical to everything above:

Now write it yourself from an outline. Create a people table, insert a few rows, then run a parameterized COUNT(*) with the age threshold bound as a parameter — never concatenated. Read the single result and print it.

Great work! You can now open a connection with DriverManager , run safe parameterized queries with PreparedStatement , tell executeQuery from executeUpdate , walk a ResultSet , auto-close everything with try-with-resources, and make changes atomic with commit / rollback — plus why pooling with HikariCP matters in production.

Next up: REST APIs — exposing this data layer as web services with Spring Boot.

Practice quiz

What does JDBC stand for and provide?

  • Java Data Binding Class
  • JavaScript Database Connector
  • Java Database Connectivity — the standard API for relational databases
  • Java Distributed Bean Container

Answer: Java Database Connectivity — the standard API for relational databases. JDBC is Java's standard API for relational databases: Connection, Statement/PreparedStatement, and ResultSet.

Why does PreparedStatement prevent SQL injection?

  • It sends SQL with ? placeholders, then sends values separately as data, never as code
  • It encrypts the SQL
  • It blocks all user input
  • It runs queries as a read-only user

Answer: It sends SQL with ? placeholders, then sends values separately as data, never as code. Values bound to ? are always treated as literal data, so they can never change the query's structure.

Which method runs a SELECT and returns a ResultSet?

  • executeUpdate()
  • execute()
  • runQuery()
  • executeQuery()

Answer: executeQuery(). executeQuery() is for SELECT and returns a ResultSet; executeUpdate() is for INSERT/UPDATE/DELETE.

What does executeUpdate() return for an INSERT/UPDATE/DELETE?

  • A ResultSet
  • An int — the number of rows affected
  • A boolean
  • void

Answer: An int — the number of rows affected. executeUpdate() returns an int, the count of rows changed by the statement.

What does conn.setAutoCommit(false) do?

  • Starts a transaction so nothing is permanent until commit()
  • Closes the connection
  • Disables the database
  • Makes every statement commit twice

Answer: Starts a transaction so nothing is permanent until commit(). It begins a transaction: changes become permanent only on commit(), and rollback() undoes them.

A fresh ResultSet sits where, so what must you call before reading a column?

  • On the last row; call previous()
  • On the first row; read directly
  • Before the first row; call next()
  • Outside the rows; call close()

Answer: Before the first row; call next(). A new ResultSet sits before the first row, so you must call rs.next() before reading any column.

Why wrap Connection, Statement, and ResultSet in try-with-resources?

  • It runs queries faster
  • It auto-closes them even on exception, so you never leak connections
  • It enables transactions
  • It prevents SQL injection

Answer: It auto-closes them even on exception, so you never leak connections. try-with-resources closes each resource automatically when the block ends, preventing leaked connections.

Why use a connection pool like HikariCP?

  • It encrypts data
  • It writes the SQL for you
  • It replaces PreparedStatement
  • Opening a connection is expensive; a pool reuses ready connections

Answer: Opening a connection is expensive; a pool reuses ready connections. Opening a connection costs a network round trip plus auth; a pool keeps connections ready and lends them out.

In a transaction, where do commit() and rollback() typically go?

  • Both in the catch block
  • commit() at the end of the happy path, rollback() in the catch block
  • Both in finally
  • commit() in finally, rollback() in try

Answer: commit() at the end of the happy path, rollback() in the catch block. Commit at the end of the successful path and rollback in the catch; restore auto-commit in finally.

What is the N+1 query problem?

  • Running one query too few
  • Using too many connections
  • Fetching a list, then running a separate query per row in a loop
  • Committing N+1 times

Answer: Fetching a list, then running a separate query per row in a loop. N+1 is one query for a list plus one per row; fix it with a single JOIN or IN (...) to fetch related rows at once.