MySQL in Java Database
MySQL is a popular relational database management system. Michael Widenius, David Axmark, and Allan Larsson founded and developed it in Sweden. Oracle Corporation now develops, distributes, and supports it. MySQL’s speed, stability, and usability are well-known. SQL is used to manage databases on Windows, UNIX, LINUX, and Mac OS. MySQL tables contain rows and columns of data.
A “Java Database,” or relational database system like MySQL, is accessed and controlled using Java computer language. Java is not a database, but it has a comprehensive architecture and API for database connectivity.
Java Database Connectivity (JDBC)
JDBC connects Java to relational databases like MySQL. A Java client connects to a database via JDBC. Java developers can construct database applications using its standard, unified interface for connecting to relational databases. A JDBC driver connects the JDBC API to the database’s native protocol. MySQL has its own JDBC driver (mysql-connector-java).
Using JDBC with MySQL requires multiple steps:
- Loading the JDBC Driver: This makes the driver available for your application.
- Establishing a Connection: Using the driver, you connect to the MySQL database by providing the database URL, username, and password.
- Creating a Statement: Once connected, you create Statement objects to execute SQL queries.
- Executing Queries: You can execute DDL (Data Definition Language) or DML (Data Manipulation Language) commands like CREATE, SELECT, INSERT, UPDATE, or DELETE.
- Processing Results: For SELECT queries, results are returned in a ResultSet object, which can be iterated to retrieve data.
- Closing Resources: It is crucial to close ResultSet, Statement, and Connection objects to release database resources.
Here’s a basic example to connect to a MySQL database and create a table using JDBC:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class MySQLJavaExample {
static final String DB_URL = "jdbc:mysql://localhost:3306/your_database_name"; // Database URL
static final String USER = "your_username"; // MySQL username
static final String PASS = "your_password"; // MySQL password
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
// Register JDBC driver (if not already loaded by JVM)
// Class.forName("com.mysql.cj.jdbc.Driver"); // Often not strictly necessary in modern Java
// Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
// Set auto-commit to false to manage transactions manually (optional but good practice)
conn.setAutoCommit(false);
// Execute a query
System.out.println("Creating table in given database...");
stmt = conn.createStatement();
String sql = "CREATE TABLE IF NOT EXISTS Students (" + // Creating a table
"id INT AUTO_INCREMENT PRIMARY KEY, " +
"name VARCHAR(100) NOT NULL, " +
"age INT)";
stmt.executeUpdate(sql);
System.out.println("Table 'Students' created successfully...");
conn.commit(); // Commit the transaction
} catch (SQLException se) {
// Handle errors for JDBC
se.printStackTrace();
try {
if (conn != null) conn.rollback(); // Rollback on error
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
// Close resources
try {
if (stmt != null) stmt.close();
} catch (SQLException se2) {
// Do nothing
}
try {
if (conn != null) conn.close();
System.out.println("Database connection closed.");
} catch (SQLException se) {
se.printStackTrace();
}
}
}
}
Inserting Data
To insert records into a table, the INSERT INTO SQL command is used. PreparedStatement is recommended for inserting data to prevent SQL injection vulnerabilities.
// ... (inside try block, after table creation)
System.out.println("Inserting records into the table...");
String insertSql = "INSERT INTO Students (name, age) VALUES (?, ?)"; // Use placeholders for security
PreparedStatement pstmt = conn.prepareStatement(insertSql);
pstmt.setString(1, "Alice");
pstmt.setInt(2, 20);
pstmt.executeUpdate();
System.out.println("Alice inserted.");
pstmt.setString(1, "Bob");
pstmt.setInt(2, 22);
pstmt.executeUpdate();
System.out.println("Bob inserted.");
conn.commit();
Retrieving Data
The SELECT SQL command is used to fetch data. Results are processed using a ResultSet object.
// ... (inside try block)
System.out.println("Retrieving records from the table...");
String selectSql = "SELECT id, name, age FROM Students";
ResultSet rs = stmt.executeQuery(selectSql); // Using Statement for simple select
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age);
}
rs.close(); // Close the result set
Updating Data
The UPDATE SQL command modifies existing records.
// ... (inside try block)
System.out.println("Updating a record...");
String updateSql = "UPDATE Students SET age = ? WHERE name = ?";
PreparedStatement updatePstmt = conn.prepareStatement(updateSql);
updatePstmt.setInt(1, 21);
updatePstmt.setString(2, "Alice");
int rowsAffected = updatePstmt.executeUpdate();
System.out.println(rowsAffected + " row(s) updated for Alice.");
conn.commit();
updatePstmt.close();
Deleting Data
The DELETE FROM SQL command removes records.
// ... (inside try block)
System.out.println("Deleting a record...");
String deleteSql = "DELETE FROM Students WHERE name = ?";
PreparedStatement deletePstmt = conn.prepareStatement(deleteSql);
deletePstmt.setString(1, "Bob");
int deletedRows = deletePstmt.executeUpdate();
System.out.println(deletedRows + " row(s) deleted for Bob.");
conn.commit();
deletePstmt.close();
It is crucial to use try-catch-finally blocks for proper error handling and to ensure that database resources like Connection, Statement, PreparedStatement, and ResultSet are always closed in the finally block to prevent resource leaks. Using PreparedStatement with parameterized queries (? placeholders) is a best practice to protect against SQL injection attacks, where malicious SQL code can be inserted through user input. Transaction control with commit() and rollback() ensures data integrity.