JDBC Connection to MySQL – Complete Tutorial with CRUD Examples
JDBC (Java Database Connectivity) is the standard Java API for connecting to relational databases. Before ORMs like Hibernate became popular, every Java application used JDBC directly. Even today, understanding JDBC fundamentals is essential — it's what Hibernate, Spring JDBC, and JPA use under the hood. This tutorial shows you how to set up a JDBC connection to MySQL and perform all four CRUD operations.
Prerequisites
- Java 8 or later
- MySQL 8.x
- MySQL Connector/J (download from dev.mysql.com or add via Maven)
Maven Dependency
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version>
</dependency>
Database Setup
CREATE DATABASE IF NOT EXISTS java9rdb;
USE java9rdb;
CREATE TABLE IF NOT EXISTS employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(50),
salary DOUBLE,
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Step 1: Establish a JDBC Connection
package com.java9r.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* JDBC connection utility.
* Always close connections in finally blocks or use try-with-resources.
*/
public class JDBCConnectionDemo {
// Connection parameters
private static final String URL = "jdbc:mysql://localhost:3306/java9rdb" +
"?useSSL=false&serverTimezone=UTC";
private static final String USERNAME = "root";
private static final String PASSWORD = "yourpassword";
public static void main(String[] args) {
// Java 7+ try-with-resources automatically closes the connection
try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
System.out.println("Connected to MySQL successfully!");
System.out.println("Database: " + conn.getCatalog());
System.out.println("URL: " + conn.getMetaData().getURL());
System.out.println("Driver: " + conn.getMetaData().getDriverName());
} catch (SQLException e) {
System.err.println("Connection failed: " + e.getMessage());
System.err.println("SQLState: " + e.getSQLState());
System.err.println("Error Code: " + e.getErrorCode());
}
}
}
Step 2: Insert a Record (CREATE)
public static int insertEmployee(String name, String dept, double salary) throws SQLException {
String sql = "INSERT INTO employees (name, department, salary) VALUES (?, ?, ?)";
try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
PreparedStatement ps = conn.prepareStatement(sql,
java.sql.Statement.RETURN_GENERATED_KEYS)) {
ps.setString(1, name);
ps.setString(2, dept);
ps.setDouble(3, salary);
int affected = ps.executeUpdate();
// Retrieve the auto-generated primary key
if (affected > 0) {
try (var keys = ps.getGeneratedKeys()) {
if (keys.next()) {
int newId = keys.getInt(1);
System.out.println("Inserted employee with id: " + newId);
return newId;
}
}
}
return -1;
}
}
Step 3: Read Records (READ)
public static void findAllEmployees() throws SQLException {
String sql = "SELECT * FROM employees ORDER BY id";
try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
java.sql.Statement st = conn.createStatement();
java.sql.ResultSet rs = st.executeQuery(sql)) {
System.out.println(String.format("%-5s %-20s %-15s %10s", "ID", "Name", "Dept", "Salary"));
System.out.println("-".repeat(55));
while (rs.next()) {
System.out.printf("%-5d %-20s %-15s %10.2f%n",
rs.getInt("id"),
rs.getString("name"),
rs.getString("department"),
rs.getDouble("salary")
);
}
}
}
// Find one by ID
public static void findById(int id) throws SQLException {
String sql = "SELECT * FROM employees WHERE id = ?";
try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, id);
try (var rs = ps.executeQuery()) {
if (rs.next()) {
System.out.println("Found: " + rs.getString("name") +
" | Dept: " + rs.getString("department") +
" | Salary: " + rs.getDouble("salary"));
} else {
System.out.println("Employee not found with id: " + id);
}
}
}
}
Step 4: Update a Record
public static boolean updateSalary(int id, double newSalary) throws SQLException {
String sql = "UPDATE employees SET salary = ? WHERE id = ?";
try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setDouble(1, newSalary);
ps.setInt(2, id);
int rows = ps.executeUpdate();
System.out.println("Updated " + rows + " row(s).");
return rows > 0;
}
}
Step 5: Delete a Record
public static boolean deleteEmployee(int id) throws SQLException {
String sql = "DELETE FROM employees WHERE id = ?";
try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, id);
int rows = ps.executeUpdate();
System.out.println("Deleted " + rows + " row(s).");
return rows > 0;
}
}
Complete Main – Putting It All Together
public static void main(String[] args) throws SQLException {
// INSERT
int id1 = insertEmployee("Ravi Kumar", "Engineering", 75000);
int id2 = insertEmployee("Priya Singh", "Marketing", 65000);
int id3 = insertEmployee("Amit Patel", "Engineering", 80000);
// READ ALL
System.out.println("\n=== All Employees ===");
findAllEmployees();
// READ ONE
System.out.println("\n=== Find by ID ===");
findById(id2);
// UPDATE
System.out.println("\n=== Update Salary ===");
updateSalary(id1, 85000);
findById(id1);
// DELETE
System.out.println("\n=== Delete ===");
deleteEmployee(id3);
findAllEmployees();
}
Expected Output
=== All Employees ===
ID Name Dept Salary
-------------------------------------------------------
1 Ravi Kumar Engineering 75000.00
2 Priya Singh Marketing 65000.00
3 Amit Patel Engineering 80000.00
=== Find by ID ===
Found: Priya Singh | Dept: Marketing | Salary: 65000.0
=== Update Salary ===
Updated 1 row(s).
Found: Ravi Kumar | Dept: Engineering | Salary: 85000.0
=== Delete ===
Deleted 1 row(s).
ID Name Dept Salary
-------------------------------------------------------
1 Ravi Kumar Engineering 85000.00
2 Priya Singh Marketing 65000.00
Statement vs PreparedStatement
| Feature | Statement | PreparedStatement |
|---|---|---|
| SQL injection safe | No | Yes (use ? placeholders) |
| Precompiled | No | Yes (faster for repeated calls) |
| Best for | Dynamic DDL, one-time queries | All DML operations (INSERT/UPDATE/DELETE/SELECT) |
Always use PreparedStatement for queries with user input — never concatenate strings into SQL queries.
Summary
JDBC is the foundation of all Java database programming. The key steps are: load the driver (automatic in modern JDBC 4+), establish a connection with DriverManager.getConnection(), create a PreparedStatement for any query with parameters, execute the query, process the ResultSet, and close all resources. Always use try-with-resources to ensure connections and statements are closed even when exceptions occur. For production applications, use a connection pool like HikariCP rather than creating a new connection for every request.
Comments