use prepared and callable statements

PHOTO EMBED

Thu Nov 21 2024 18:06:29 GMT+0000 (Coordinated Universal Time)

Saved by @coding1

SQL 
CREATE DATABASE sampledb; 
USE sampledb; 
CREATE TABLE users ( 
id INT AUTO_INCREMENT PRIMARY KEY, 
name VARCHAR(100), 
email VARCHAR(100) 
); 
JAVA 
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.PreparedStatement; 
import java.sql.SQLException; 
import java.sql.CallableStatement; 
import java.sql.ResultSet; 
public class JdbcDmlExample { 
private static final String URL = "jdbc:mysql://localhost:3306/sampledb"; 
private static final String USERNAME = "your_username"; // Replace with your DB username 
private static final String PASSWORD = "your_password"; // Replace with your DB password 
    public static void main(String[] args) { 
        Connection connection = null; 
 
        try { 
            // Establishing the connection 
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); 
            System.out.println("Connected to the database successfully."); 
 
            // Inserting data using PreparedStatement 
            insertUser(connection, "Alice Johnson", "alice@example.com"); 
            insertUser(connection, "Bob Smith", "bob@example.com"); 
 
            // Updating data using PreparedStatement 
            updateUserEmail(connection, 1, "alice.new@example.com"); 
 
            // Calling stored procedure using CallableStatement 
            callUserCountProcedure(connection); 
 
        } catch (SQLException e) { 
            System.err.println("SQL Exception: " + e.getMessage()); 
        } finally { 
            // Closing the connection 
            try { 
                if (connection != null && !connection.isClosed()) { 
                    connection.close(); 
                    System.out.println("Database connection closed."); 
} 
} catch (SQLException e) { 
System.err.println("Failed to close the connection: " + e.getMessage()); 
} 
} 
} 
// Method to insert user using PreparedStatement 
private static void insertUser(Connection connection, String name, String email) throws 
SQLException { 
String insertSQL = "INSERT INTO users (name, email) VALUES (?, ?)"; 
try (PreparedStatement pstmt = connection.prepareStatement(insertSQL)) { 
pstmt.setString(1, name); 
pstmt.setString(2, email); 
int rowsAffected = pstmt.executeUpdate(); 
System.out.println(rowsAffected + " user(s) inserted."); 
} 
} 
// Method to update user email using PreparedStatement 
private static void updateUserEmail(Connection connection, int userId, String newEmail) 
throws SQLException { 
String updateSQL = "UPDATE users SET email = ? WHERE id = ?"; 
try (PreparedStatement pstmt = connection.prepareStatement(updateSQL)) { 
pstmt.setString(1, newEmail); 
pstmt.setInt(2, userId); 
int rowsAffected = pstmt.executeUpdate(); 
System.out.println(rowsAffected + " user(s) updated."); 
} 
} 
// Method to call a stored procedure using CallableStatement 
private static void callUserCountProcedure(Connection connection) throws SQLException { 
// Assuming there is a stored procedure named `GetUserCount` that returns the count of 
users 
} 
} 
String procedureCall = "{ CALL GetUserCount() }"; 
try (CallableStatement cstmt = connection.prepareCall(procedureCall)) { 
try (ResultSet rs = cstmt.executeQuery()) { 
if (rs.next()) { 
int userCount = rs.getInt(1); 
System.out.println("Total users: " + userCount); 
} 
} 
}
content_copyCOPY