📘 Premium Read: Access my best content on Medium member-only articles — deep dives into Java, Spring Boot, Microservices, backend architecture, interview preparation, career advice, and industry-standard best practices.
✅ Some premium posts are free to read — no account needed. Follow me on Medium to stay updated and support my writing.
🎓 Top 10 Udemy Courses (Huge Discount): Explore My Udemy Courses — Learn through real-time, project-based development.
▶️ Subscribe to My YouTube Channel (172K+ subscribers): Java Guides on YouTube
Statement interface to interact with a MySQL database. - Setting Up the MySQL Database
- Establishing a Database Connection
- Creating a Table
- Inserting Records
- Selecting Records
- Updating Records
- Deleting Records
- Batch Processing
Let's dive into each step with detailed explanations and code examples.
Prerequisites
- Ensure you have MySQL installed and running on your machine.
- Create a database named
jdbc_example. - Add the MySQL JDBC driver to your project. If you are using Maven, add the following dependency to your
pom.xmlfile:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.30</version>
</dependency>
1. Setting Up the MySQL Database
First, create a database named jdbc_example and a users table within it. Open your MySQL command line or any MySQL client and execute the following commands:
CREATE DATABASE jdbc_example;
USE jdbc_example;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
country VARCHAR(100),
password VARCHAR(100)
);
2. Establishing a Database Connection
We will start by establishing a connection to the MySQL database using JDBC.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JDBCExample {
private static final String URL = "jdbc:mysql://localhost:3306/jdbc_example";
private static final String USER = "root";
private static final String PASSWORD = "password";
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, USER, PASSWORD);
}
public static void main(String[] args) {
try (Connection connection = getConnection()) {
if (connection != null) {
System.out.println("Connected to the database!");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
3. Creating a Table
Next, we will create a users table if it doesn't already exist. We use the execute method of the Statement interface.
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class CreateTableExample {
public static void main(String[] args) {
String createTableSQL = "CREATE TABLE IF NOT EXISTS users ("
+ "id INT AUTO_INCREMENT PRIMARY KEY, "
+ "name VARCHAR(100), "
+ "email VARCHAR(100), "
+ "country VARCHAR(100), "
+ "password VARCHAR(100)"
+ ")";
try (Connection connection = JDBCExample.getConnection();
Statement statement = connection.createStatement()) {
statement.execute(createTableSQL);
System.out.println("Table created successfully!");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
4. Inserting Records
We can insert records into the users table using the executeUpdate method.
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class InsertRecordsExample {
public static void main(String[] args) {
String insertSQL = "INSERT INTO users (name, email, country, password) VALUES "
+ "('John Doe', 'john.doe@example.com', 'USA', 'secret'), "
+ "('Jane Doe', 'jane.doe@example.com', 'UK', 'secret123')";
try (Connection connection = JDBCExample.getConnection();
Statement statement = connection.createStatement()) {
int rowsInserted = statement.executeUpdate(insertSQL);
System.out.println(rowsInserted + " rows inserted!");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
5. Selecting Records
To retrieve records, we use the executeQuery method which returns a ResultSet.
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SelectRecordsExample {
public static void main(String[] args) {
String selectSQL = "SELECT * FROM users";
try (Connection connection = JDBCExample.getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(selectSQL)) {
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String email = resultSet.getString("email");
String country = resultSet.getString("country");
String password = resultSet.getString("password");
System.out.println(id + ", " + name + ", " + email + ", " + country + ", " + password);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
6. Updating Records
To update records, we use the executeUpdate method again.
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class UpdateRecordExample {
public static void main(String[] args) {
String updateSQL = "UPDATE users SET country = 'Canada' WHERE name = 'John Doe'";
try (Connection connection = JDBCExample.getConnection();
Statement statement = connection.createStatement()) {
int rowsUpdated = statement.executeUpdate(updateSQL);
System.out.println(rowsUpdated + " rows updated!");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
7. Deleting Records
To delete records, we use the executeUpdate method.
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class DeleteRecordExample {
public static void main(String[] args) {
String deleteSQL = "DELETE FROM users WHERE name = 'Jane Doe'";
try (Connection connection = JDBCExample.getConnection();
Statement statement = connection.createStatement()) {
int rowsDeleted = statement.executeUpdate(deleteSQL);
System.out.println(rowsDeleted + " rows deleted!");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
8. Batch Processing
We can execute multiple SQL commands in a batch using the addBatch and executeBatch methods.
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class BatchProcessingExample {
public static void main(String[] args) {
String insertSQL1 = "INSERT INTO users (name, email, country, password) VALUES ('Alice', 'alice@example.com', 'France', 'alice123')";
String insertSQL2 = "INSERT INTO users (name, email, country, password) VALUES ('Bob', 'bob@example.com', 'Germany', 'bob123')";
try (Connection connection = JDBCExample.getConnection();
Statement statement = connection.createStatement()) {
statement.addBatch(insertSQL1);
statement.addBatch(insertSQL2);
int[] updateCounts = statement.executeBatch();
System.out.println("Batch executed with " + updateCounts.length + " statements!");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Conclusion
In this tutorial, we have covered the basics of using the JDBC Statement interface to interact with a MySQL database. We demonstrated how to create a table, insert, select, update, delete records, and perform batch processing. This guide should help you get started with JDBC and the Statement interface.
Comments
Post a Comment
Leave Comment