Java Notes

MySQL Connection with JDBC

Step-by-step guide to connecting Java with MySQL — driver setup, connection URLs, properties, SSL configuration, and troubleshooting common errors.

Prerequisites

MySQL Server installed and running (MySQL 8.0+)
MySQL Connector/J (JDBC driver for MySQL)
Java 8+ installed

Install MySQL (if needed)

bash example

# Ubuntu/Debian
sudo apt install mysql-server

# macOS (Homebrew)
brew install mysql

# Windows - download from mysql.com or use XAMPP

# Start MySQL
sudo systemctl start mysql    # Linux
brew services start mysql     # macOS

# Log in and create database
mysql -u root -p

sql example

-- Create database and user
CREATE DATABASE IF NOT EXISTS java_course;
CREATE USER IF NOT EXISTS 'javauser'@'localhost' IDENTIFIED BY 'JavaPass123!';
GRANT ALL PRIVILEGES ON java_course.* TO 'javauser'@'localhost';
FLUSH PRIVILEGES;

-- Create tables
USE java_course;

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO employees (first_name, last_name, email, department, salary, hire_date) VALUES
('John', 'Doe', 'john.doe@company.com', 'Engineering', 85000.00, '2020-03-15'),
('Jane', 'Smith', 'jane.smith@company.com', 'Marketing', 72000.00, '2019-07-22'),
('Bob', 'Johnson', 'bob.johnson@company.com', 'Engineering', 92000.00, '2018-01-10'),
('Alice', 'Williams', 'alice.w@company.com', 'HR', 68000.00, '2021-09-01'),
('Charlie', 'Brown', 'charlie.b@company.com', 'Engineering', 78000.00, '2022-02-14');

Connection Methods

Method 1: Basic DriverManager

Try it in WoHoCode

Edit the program, then run it here.

Lesson example

Loading editor...

Ln 1, Col 1java | Ctrl+Enter: run | Ctrl+S: save

Output

Run this example to see its output.

Method 2: With Connection Properties

Try it in WoHoCode

Edit the program, then run it here.

Lesson example

Loading editor...

Ln 1, Col 1java | Ctrl+Enter: run | Ctrl+S: save

Output

Run this example to see its output.

Method 3: URL with Parameters

java example

String url = "jdbc:mysql://localhost:3306/java_course" +
    "?useSSL=false" +
    "&serverTimezone=UTC" +
    "&characterEncoding=UTF-8" +
    "&useUnicode=true" +
    "&rewriteBatchedStatements=true" +
    "&cachePrepStmts=true" +
    "&prepStmtCacheSize=250" +
    "&prepStmtCacheSqlLimit=2048";

Connection conn = DriverManager.getConnection(url, "javauser", "JavaPass123!");

Method 4: Production-Ready Configuration File

java example

// db.properties file
/*
db.url=jdbc:mysql://localhost:3306/java_course
db.user=javauser
db.password=JavaPass123!
db.driver=com.mysql.cj.jdbc.Driver
db.pool.size=10
*/

import java.io.InputStream;
import java.util.Properties;

public class ConfigFileConnection {
    private static Properties dbProps = new Properties();
    
    static {
        try (InputStream is = ConfigFileConnection.class
                .getClassLoader().getResourceAsStream("db.properties")) {
            dbProps.load(is);
            Class.forName(dbProps.getProperty("db.driver"));
        } catch (Exception e) {
            throw new RuntimeException("Failed to load DB config", e);
        }
    }
    
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(
            dbProps.getProperty("db.url"),
            dbProps.getProperty("db.user"),
            dbProps.getProperty("db.password")
        );
    }
}

Complete CRUD Example

Try it in WoHoCode

Edit the program, then run it here.

Lesson example

Loading editor...

Ln 1, Col 1java | Ctrl+Enter: run | Ctrl+S: save

Output

Run this example to see its output.

Troubleshooting Common Errors

Error	Cause	Fix
`Communications link failure`	MySQL not running or wrong host/port	Start MySQL, check URL
`Access denied for user`	Wrong username/password	Verify credentials
`Unknown database`	Database doesn't exist	CREATE DATABASE first
`No suitable driver found`	Driver not in classpath	Add MySQL connector JAR
`SSL connection error`	SSL required but not configured	Add `?useSSL=false` for dev
`Public Key Retrieval not allowed`	Auth plugin issue	Add `&allowPublicKeyRetrieval=true`
`The server time zone value`	Timezone mismatch	Add `&serverTimezone=UTC`

Interview Questions

Q1: What JDBC URL format does MySQL use?

Answer: jdbc:mysql://hostname:port/database?param1=value1&param2=value2. Default port is 3306.

Q2: Why use PreparedStatement instead of Statement for CRUD?

Answer: PreparedStatement prevents SQL injection, enables query plan caching, handles data type conversion, and supports binary data. Statement with string concatenation is a critical security vulnerability.

Q3: What is the purpose of try-with-resources in JDBC?

Answer: Automatically closes Connection, Statement, and ResultSet when the block exits (even on exceptions), preventing resource leaks that exhaust the connection pool.

Q4: How do you get the auto-generated ID after an INSERT?

Answer: Pass Statement.RETURN_GENERATED_KEYS to prepareStatement(), then call pstmt.getGeneratedKeys() after executeUpdate() to get a ResultSet with the generated key.

Q5: What is the difference between executeQuery, executeUpdate, and execute?

Answer: executeQuery() — for SELECT, returns ResultSet. executeUpdate() — for INSERT/UPDATE/DELETE, returns affected row count. execute() — for any SQL, returns boolean (true=ResultSet, false=update count).

Summary

In this chapter, we learned about MySQL Connection with JDBC in detail. Here are the key points:

Basic introduction to the concept and why it is needed
Syntax and structure with complete examples
Internal working and best practices
Real-world applications and use cases
Common mistakes and how to avoid them
How this topic is asked in interviews

To practice these concepts, write and run the code in your IDE and verify the output. Modify each example and experiment so that you deeply understand the concept.

Continue the lesson path

Study next in this module

4 connected lessons

01JDBCJDBC ArchitectureDeep dive into JDBC architecture — two-tier and three-tier models, driver manager, connection pooling, DataSource, and internal execution flow.02JDBCStatement Interface in JDBCComplete guide to JDBC Statement interface — executing static SQL, DDL operations, multiple results, batch execution, and understanding when to use Statement vs PreparedStatement.03JDBCJDBC IntroductionComplete introduction to JDBC — Java Database Connectivity concepts, API overview, driver types, and how Java applications communicate with databases.04JDBCPreparedStatement in JDBCMaster PreparedStatement — parameterized queries, SQL injection prevention, performance benefits, batch operations, and CRUD patterns.

PreviousJDBC Architecture NextStatement Interface in JDBC

Java Notes

MySQL Connection with JDBC

Step-by-step guide to connecting Java with MySQL — driver setup, connection URLs, properties, SSL configuration, and troubleshooting common errors.

Prerequisites

MySQL Server installed and running (MySQL 8.0+)
MySQL Connector/J (JDBC driver for MySQL)
Java 8+ installed

Install MySQL (if needed)

bash example

# Ubuntu/Debian
sudo apt install mysql-server

# macOS (Homebrew)
brew install mysql

# Windows - download from mysql.com or use XAMPP

# Start MySQL
sudo systemctl start mysql    # Linux
brew services start mysql     # macOS

# Log in and create database
mysql -u root -p

sql example

-- Create database and user
CREATE DATABASE IF NOT EXISTS java_course;
CREATE USER IF NOT EXISTS 'javauser'@'localhost' IDENTIFIED BY 'JavaPass123!';
GRANT ALL PRIVILEGES ON java_course.* TO 'javauser'@'localhost';
FLUSH PRIVILEGES;

-- Create tables
USE java_course;

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO employees (first_name, last_name, email, department, salary, hire_date) VALUES
('John', 'Doe', 'john.doe@company.com', 'Engineering', 85000.00, '2020-03-15'),
('Jane', 'Smith', 'jane.smith@company.com', 'Marketing', 72000.00, '2019-07-22'),
('Bob', 'Johnson', 'bob.johnson@company.com', 'Engineering', 92000.00, '2018-01-10'),
('Alice', 'Williams', 'alice.w@company.com', 'HR', 68000.00, '2021-09-01'),
('Charlie', 'Brown', 'charlie.b@company.com', 'Engineering', 78000.00, '2022-02-14');

Connection Methods

Method 1: Basic DriverManager

Try it in WoHoCode

Edit the program, then run it here.

Lesson example

Loading editor...

Ln 1, Col 1java | Ctrl+Enter: run | Ctrl+S: save

Output

Run this example to see its output.

Method 2: With Connection Properties

Try it in WoHoCode

Edit the program, then run it here.

Lesson example

Loading editor...

Ln 1, Col 1java | Ctrl+Enter: run | Ctrl+S: save

Output

Run this example to see its output.

Method 3: URL with Parameters

java example

String url = "jdbc:mysql://localhost:3306/java_course" +
    "?useSSL=false" +
    "&serverTimezone=UTC" +
    "&characterEncoding=UTF-8" +
    "&useUnicode=true" +
    "&rewriteBatchedStatements=true" +
    "&cachePrepStmts=true" +
    "&prepStmtCacheSize=250" +
    "&prepStmtCacheSqlLimit=2048";

Connection conn = DriverManager.getConnection(url, "javauser", "JavaPass123!");

Method 4: Production-Ready Configuration File

java example

// db.properties file
/*
db.url=jdbc:mysql://localhost:3306/java_course
db.user=javauser
db.password=JavaPass123!
db.driver=com.mysql.cj.jdbc.Driver
db.pool.size=10
*/

import java.io.InputStream;
import java.util.Properties;

public class ConfigFileConnection {
    private static Properties dbProps = new Properties();
    
    static {
        try (InputStream is = ConfigFileConnection.class
                .getClassLoader().getResourceAsStream("db.properties")) {
            dbProps.load(is);
            Class.forName(dbProps.getProperty("db.driver"));
        } catch (Exception e) {
            throw new RuntimeException("Failed to load DB config", e);
        }
    }
    
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(
            dbProps.getProperty("db.url"),
            dbProps.getProperty("db.user"),
            dbProps.getProperty("db.password")
        );
    }
}

Complete CRUD Example

Try it in WoHoCode

Edit the program, then run it here.

Lesson example

Loading editor...

Ln 1, Col 1java | Ctrl+Enter: run | Ctrl+S: save

Output

Run this example to see its output.

Troubleshooting Common Errors

Error	Cause	Fix
`Communications link failure`	MySQL not running or wrong host/port	Start MySQL, check URL
`Access denied for user`	Wrong username/password	Verify credentials
`Unknown database`	Database doesn't exist	CREATE DATABASE first
`No suitable driver found`	Driver not in classpath	Add MySQL connector JAR
`SSL connection error`	SSL required but not configured	Add `?useSSL=false` for dev
`Public Key Retrieval not allowed`	Auth plugin issue	Add `&allowPublicKeyRetrieval=true`
`The server time zone value`	Timezone mismatch	Add `&serverTimezone=UTC`

Interview Questions

Q1: What JDBC URL format does MySQL use?

Answer: jdbc:mysql://hostname:port/database?param1=value1&param2=value2. Default port is 3306.

Q2: Why use PreparedStatement instead of Statement for CRUD?

Q3: What is the purpose of try-with-resources in JDBC?

Answer: Automatically closes Connection, Statement, and ResultSet when the block exits (even on exceptions), preventing resource leaks that exhaust the connection pool.

Q4: How do you get the auto-generated ID after an INSERT?

Answer: Pass Statement.RETURN_GENERATED_KEYS to prepareStatement(), then call pstmt.getGeneratedKeys() after executeUpdate() to get a ResultSet with the generated key.

Q5: What is the difference between executeQuery, executeUpdate, and execute?

Summary

In this chapter, we learned about MySQL Connection with JDBC in detail. Here are the key points:

Basic introduction to the concept and why it is needed
Syntax and structure with complete examples
Internal working and best practices
Real-world applications and use cases
Common mistakes and how to avoid them
How this topic is asked in interviews

To practice these concepts, write and run the code in your IDE and verify the output. Modify each example and experiment so that you deeply understand the concept.

Continue the lesson path

Study next in this module

4 connected lessons

PreviousJDBC Architecture NextStatement Interface in JDBC