Your First JDBC Program

You have a running database, a project with the MySQL driver, and an IDE ready to go. Now it is time to write Java code that actually talks to MySQL. This chapter starts with the simplest possible connection and builds up to a complete CRUD example.

Prerequisites

  • MySQL running with the jdbc_demo database
  • A Maven project with mysql-connector-j in pom.xml
  • The jdbc_user account created in the installation chapters

Connecting to the Database

The Connection URL

Every JDBC connection starts with a URL that tells the driver where to find the database:

text
jdbc:mysql://host:port/database?param1=value1&param2=value2
ComponentExampleMeaning
jdbc:mysql://Fixed prefixIdentifies the MySQL JDBC driver
hostlocalhost or 127.0.0.1Where MySQL is running
port3306The TCP port MySQL listens on
databasejdbc_demoThe default database for this connection
?...useSSL=falseDriver-specific parameters

For this tutorial, use:

text
jdbc:mysql://127.0.0.1:3306/jdbc_demo?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true

The parameters do the following:

ParameterPurpose
useSSL=falseDisables SSL for local development (enable it in production)
serverTimezone=Asia/ShanghaiSets the session time zone to avoid timezone warnings
allowPublicKeyRetrieval=trueAllows the driver to request the public key from MySQL 8 for caching_sha2_password authentication

Writing the Connection Code

Create a class at src/main/java/com/example/ConnectDemo.java:

Run it:

bash
mvn compile exec:java -Dexec.mainClass="com.example.ConnectDemo"

If everything is wired up, you will see Connected: true.

Tip

Always Use try-with-resources

Connection implements AutoCloseable. Wrapping it in a try-with-resources block guarantees the connection closes automatically, even if an exception occurs. Forgetting to close connections is the fastest way to exhaust your database's connection limit.

Common Connection Errors

ErrorCauseFix
Communications link failureMySQL is not running, or the port is wrongCheck docker ps or systemctl status mysql
Access deniedWrong username, password, or host restrictionVerify credentials; for Docker, use @'%' instead of @'localhost'
Unknown databaseThe database does not existRun CREATE DATABASE jdbc_demo;
SSL connection errorSSL handshake fails on older MySQL versionsAdd useSSL=false for local dev

Performing CRUD Operations

CRUD stands for Create, Read, Update, Delete — the four basic operations on persistent data. Let us build them one at a time against a simple users table.

Step 1: Create the Table

executeUpdate returns the number of rows affected. For DDL statements like CREATE TABLE, it returns 0.

Step 2: Insert Data

Run this once. If you run it a second time, it fails with a duplicate key exception because the email is declared UNIQUE.

Step 3: Query Data

Key points about ResultSet:

  • rs.next() moves the cursor to the next row and returns true until there are no more rows.
  • rs.getInt("id") retrieves the integer value from the column named id.
  • You can also retrieve by column index: rs.getInt(1) for the first column. Names are safer because they survive schema reordering.

Step 4: Update Data

Step 5: Delete Data

Warning

Always Use a WHERE Clause

Forgetting WHERE in an UPDATE or DELETE statement affects every row in the table. This is one of the most expensive mistakes in database programming. Double-check your SQL before running it against production data.

A Complete UserDao Class

In real projects, you do not write standalone main methods for each operation. You group related database operations into a DAO (Data Access Object) class:

Notice something dangerous in the insertUser, updateAge, and deleteByName methods: the SQL string is built using simple string concatenation. This works for the demo, but it is the exact pattern that leads to SQL injection. In Core API explained, you will learn how PreparedStatement eliminates this risk entirely.

FAQ

What is the difference between executeQuery and executeUpdate?

  • executeQuery runs SELECT statements and returns a ResultSet.
  • executeUpdate runs INSERT, UPDATE, DELETE, and DDL statements. It returns an int representing the number of rows affected.

Can I retrieve column values by index instead of name?

Yes. rs.getInt(1) gets the first column, rs.getString(2) gets the second, and so on. Column indexes start at 1, not 0. Names are preferred because they make your code resilient to SELECT clause reordering.

Why does rs.getInt return 0 when the database value is NULL?

ResultSet.getInt returns the primitive int, which cannot represent null. The JDBC driver returns 0 for NULL integer columns. Use rs.wasNull() immediately after the getInt call to distinguish between a real zero and a database NULL. Alternatively, use rs.getObject("age", Integer.class) which returns null for NULL values.

Do I need to close ResultSet and Statement manually?

Yes, and the cleanest way is try-with-resources, as shown above. ResultSet is closed automatically when its parent Statement is closed, and Statement is closed when its parent Connection is closed. But relying on implicit closing is risky — always declare all three in the try-with-resources header.