use prepared and callable statements
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); } } }
Comments