Practical20:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class UpdateQuery {
public static void main(String[] args) {
try {
Class.forName("com.mysql.cj.jdbc.Driver"); // Updated driver class
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/Ddemodatabase", "root",""
);
PreparedStatement st = con.prepareStatement("UPDATE student SET roll_no = 3
WHERE name = 'Abhishek'");
st.executeUpdate();
System.out.println("Record updated successfully.");
} catch (Exception ex) {
System.out.println(ex);
}
}
}
1. Develop a program to update name of a student from Jack to John.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class UpdateQuery {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/Ddemodatabase"; // Database URL
String user = "root"; // Database username
String password = ""; // Database password
try {
// Load MySQL JDBC Driver
Class.forName("com.mysql.cj.jdbc.Driver");
// Establish connection
Connection con = DriverManager.getConnection(url, user, password);
// SQL update query to change name from "Jack" to "John"
String query = "UPDATE student SET name = ? WHERE name = ?";
PreparedStatement pstmt = con.prepareStatement(query);
// Set parameters for the prepared statement
pstmt.setString(1, "John");
pstmt.setString(2, "Jack");
// Execute the update
int rowsUpdated = pstmt.executeUpdate();
// Output the result
if (rowsUpdated > 0) {
System.out.println("Successfully updated the name from Jack to John.");
} else {
System.out.println("No record found with the name Jack.");
}
2. Develop a program to delete all record for a product whose "price is greater than 500" and
Id is "P1234".
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class UpdateQuery {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/Ddemodatabase"; // Your database name
String user = "root"; // Your database username
String password = ""; // Your database password
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con = DriverManager.getConnection(url, user, password);
// Update or delete query with corrected column name
String query = "DELETE FROM product WHERE product_price > ? AND id = ?";
PreparedStatement pstmt = con.prepareStatement(query);
pstmt.setDouble(1, 500); // Set the price condition
pstmt.setString(2, "P1234"); // Set the id condition
int rowsAffected = pstmt.executeUpdate();
if (rowsAffected > 0) {
System.out.println("Successfully deleted records where product_price > 500 and id
= 'P1234'.");
} else {
System.out.println("No records found matching the criteria.");
}
pstmt.close();
con.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
