Detailed JDBC Guide: Loading Drivers, Creating Connections, Statements, and Executing SQL

6.2 JDBC Detailed Explanation

After understanding the JDBC workflow, we dive into each stage with Java code examples and detailed explanations to help test engineers perform MySQL performance testing confidently.

6.2.1 Loading the Driver

Loading the driver is the first step for a JDBC connection to MySQL, similar to installing an app to connect to a service.

// Load MySQL driver to connect to FunTester test database
Class.forName("com.mysql.cj.jdbc.Driver");

Since JDBC 4.0, the Service Provider mechanism auto‑loads drivers, so explicit Class.forName is optional for modern drivers, though it may be kept for compatibility with older versions.

6.2.2 Creating a Connection

Creating a database connection is the core step, analogous to dialing a restaurant to place an order. Three key parameters are required: URL, username, and password.

// Configure FunTester test database connection information
String url = "jdbc:mysql://127.0.0.1:3306/funtester";
String user = "root";
String password = "funtester";
Connection connection = DriverManager.getConnection(url, user, password); // Establish connection

The URL structure includes the protocol, host IP (e.g., 192.168.1.100 ), port (default 3306), and database name (funtester). In high‑concurrency performance tests, using a connection pool such as HikariCP is recommended to reduce overhead.

6.2.3 Creating Statement Objects

From a Connection you can create Statement or PreparedStatement objects.

Statement : suitable for static SQL. // Create Statement object for FunTester test SQL Statement statement = connection.createStatement();

PreparedStatement : supports pre‑compiled SQL with parameters, preventing injection and improving performance. // Create PreparedStatement for querying FunTester user data PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM user WHERE id = ?");

Use PreparedStatement for dynamic parameters and high‑frequency queries; use Statement for quick, static queries.

6.2.4 Executing SQL Statements

After creating a statement object, execute SQL either as queries (SELECT) or updates (INSERT, UPDATE, DELETE).

Using Statement

// Query FunTester user table
statement.executeQuery("SELECT * FROM user");
// Insert FunTester user data
statement.executeUpdate("INSERT INTO user (name, age, level, region, address) VALUES ('FunTester', 15, 8, '小八超市', '101号')");
// Update FunTester user data
statement.executeUpdate("UPDATE user SET name = 'FunTester' WHERE id = 5");
// Delete FunTester user data
statement.executeUpdate("DELETE FROM user WHERE id = 5");

Using PreparedStatement

// Insert using PreparedStatement
PreparedStatement insertPreparedStatement = connection.prepareStatement("INSERT INTO user (name, age, level, region, address) VALUES (?, ?, ?, ?, ?)");
insertPreparedStatement.setString(1, "FunTester");
insertPreparedStatement.setInt(2, 15);
insertPreparedStatement.setInt(3, 8);
insertPreparedStatement.setString(4, "小八超市");
insertPreparedStatement.setString(5, "101号");
insertPreparedStatement.executeUpdate();
// Delete using PreparedStatement
PreparedStatement deletePreparedStatement = connection.prepareStatement("DELETE FROM user WHERE id = ?");
deletePreparedStatement.setInt(1, 5);
deletePreparedStatement.executeUpdate();
// Update using PreparedStatement
PreparedStatement updatePreparedStatement = connection.prepareStatement("UPDATE user SET name = ? WHERE id = ?");
updatePreparedStatement.setString(1, "FunTester");
updatePreparedStatement.setInt(2, 5);
updatePreparedStatement.executeUpdate();
// Query using PreparedStatement
PreparedStatement queryPreparedStatement = connection.prepareStatement("SELECT * FROM user WHERE id = ?");
queryPreparedStatement.setInt(1, 5);
queryPreparedStatement.executeQuery();

Although PreparedStatement requires more code, its pre‑compilation improves throughput in high‑concurrency scenarios, such as bulk inserts during performance testing.

Batch Execution

Batch execution dramatically speeds up large data loads.

// Batch insert FunTester user data
statement.addBatch("INSERT INTO user (name, age, level, region, address) VALUES ('FunTester', 15, 8, '小八超市', '101号')");
statement.addBatch("INSERT INTO user (name, age, level, region, address) VALUES ('FunTester', 15, 8, '小八超市', '101号')");
statement.addBatch("INSERT INTO user (name, age, level, region, address) VALUES ('FunTester', 15, 8, '小八超市', '101号')");
statement.addBatch("INSERT INTO user (name, age, level, region, address) VALUES ('FunTester', 15, 8, '小八超市', '101号')");
statement.executeBatch(); // Execute batch operation

The executeBatch method automatically clears the batch, allowing rapid insertion of thousands of rows, which is essential for simulating high‑traffic scenarios in performance testing.