CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE
);
INSERT INTO users (name, email) VALUES
('Alice Johnson', 'alice.johnson@example.com'),
('Bob Smith', 'bob.smith@example.com'),
('Charlie Brown', 'charlie.brown@example.com'),
('Diana Prince', 'diana.prince@example.com'),
('Ethan Hunt', 'ethan.hunt@example.com'),
('Fiona Gallagher', 'fiona.gallagher@example.com'),
('George Miller', 'george.miller@example.com'),
('Hannah Davis', 'hannah.davis@example.com'),
('Ian Wright', 'ian.wright@example.com'),
('Julia Roberts', 'julia.roberts@example.com');
------------------------------------------------------Java code -----------------------------------------------
import java.sql.*;
public class MySQLCRUD {
static final String URL = "jdbc:mysql://localhost:3306/testdb";
static final String USER = "root";
static final String PASSWORD = "yourpassword";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
createUser(conn, "John Doe", "john@example.com");
readUsers(conn);
updateUser(conn, 1, "Jane Doe", "jane@example.com");
deleteUser(conn, 1);
} catch (SQLException e) {
e.printStackTrace();
public static void createUser(Connection conn, String name, String email) throws SQLException {
String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
stmt.setString(1, name);
stmt.setString(2, email);
stmt.executeUpdate();
System.out.println("User added successfully.");
public static void readUsers(Connection conn) throws SQLException {
String sql = "SELECT * FROM users";
try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
System.out.println("ID: " + rs.getInt("id") + ", Name: " + rs.getString("name") + ", Email: " +
rs.getString("email"));
}
}
public static void updateUser(Connection conn, int id, String name, String email) throws SQLException
{
String sql = "UPDATE users SET name = ?, email = ? WHERE id = ?";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
stmt.setString(1, name);
stmt.setString(2, email);
stmt.setInt(3, id);
stmt.executeUpdate();
System.out.println("User updated successfully.");
public static void deleteUser(Connection conn, int id) throws SQLException {
String sql = "DELETE FROM users WHERE id = ?";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
stmt.setInt(1, id);
stmt.executeUpdate();
System.out.println("User deleted successfully.");
/*
Before running this code, ensure that:
1. MySQL JDBC driver is added to the project.
2. A 'users' table exists in 'testdb' with columns: id (INT, AUTO_INCREMENT, PRIMARY KEY), name
(VARCHAR), email (VARCHAR).
*/
// Advanced JDBC Connection with PreparedStatement and Error Handling
import java.sql.*;
public class AdvancedJDBCConnection {
private static final String URL = "jdbc:mysql://localhost:3306/testdb";
private static final String USER = "root";
private static final String PASSWORD = "yourpassword";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
createUser(conn, "Advanced User", "advanced@example.com");
readUsers(conn);
updateUser(conn, 1, "Updated User", "updated@example.com");
deleteUser(conn, 1);
} catch (SQLException e) {
System.err.println("Connection failed: " + e.getMessage());
public static void createUser(Connection conn, String name, String email) throws SQLException {
String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
stmt.setString(1, name);
stmt.setString(2, email);
stmt.executeUpdate();
System.out.println("User added successfully.");
}
public static void readUsers(Connection conn) throws SQLException {
String sql = "SELECT * FROM users";
try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
System.out.println("ID: " + rs.getInt("id") + ", Name: " + rs.getString("name") + ", Email: " +
rs.getString("email"));
public static void updateUser(Connection conn, int id, String name, String email) throws SQLException
{
String sql = "UPDATE users SET name = ?, email = ? WHERE id = ?";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
stmt.setString(1, name);
stmt.setString(2, email);
stmt.setInt(3, id);
stmt.executeUpdate();
System.out.println("User updated successfully.");
public static void deleteUser(Connection conn, int id) throws SQLException {
String sql = "DELETE FROM users WHERE id = ?";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
stmt.setInt(1, id);
stmt.executeUpdate();
System.out.println("User deleted successfully.");
}
}
