您可以使用DELETE查询从数据库的表中删除特定记录。
DELETE FROM table_name WHERE [condition];
要使用JDBC API从表中删除记录,您需要:
注册驱动程序:使用DriverManager 类的registerDriver()方法注册驱动程序类。将驱动程序类名称作为参数传递给它。
建立连接:使用DriverManager 类的getConnection()方法连接数据库。将URL(字符串),用户名(字符串),密码(字符串)作为参数传递给它。
Create Statement:使用Connection 接口的createStatement()
方法创建一个Statement对象。
执行查询:使用executeUpdate()
Statement接口的方法执行查询。
假设我们在MySQL中名为mydatabase的数据库中有一个名为customers的表,其中有12条记录为:
+----+-----------+------+---------+----------------+ | ID | NAME | AGE | SALARY | ADDRESS | +----+-----------+------+---------+----------------+ | 1 | Amit | 25 | 3000.00 | Hyderabad | | 2 | Kalyan | 27 | 4000.00 | Vishakhapatnam | | 3 | Renuka | 30 | 5000.00 | Delhi | | 4 | Archana | 24 | 1500.00 | Mumbai | | 5 | Koushik | 30 | 9000.00 | Kota | | 6 | Hardik | 45 | 6400.00 | Bhopal | | 7 | Trupthi | 33 | 4360.00 | Ahmedabad | | 8 | Mithili | 26 | 4100.00 | Vijayawada | | 9 | Maneesh | 39 | 4000.00 | Hyderabad | | 10 | Rajaneesh | 30 | 6400.00 | Delhi | | 11 | Komal | 29 | 8000.00 | Ahmedabad | | 12 | Manyata | 25 | 5000.00 | Vijayawada | +----+-----------+------+---------+----------------+
接下来的JDBC程序与MySQL数据库建立连接,并删除薪水值小于5000的客户记录,在删除操作后检索并显示表的内容。
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class DeleteRecordsExample { public static void main(String args[]) throws SQLException { //注册驱动程序 DriverManager.registerDriver(new com.mysql.jdbc.Driver()); //获得连接 String mysqlUrl = "jdbc:mysql://localhost/MyDatabase"; Connection con = DriverManager.getConnection(mysqlUrl, "root", "password"); System.out.println("Connection established......"); //创建语句 Statement stmt = con.createStatement(); //查询删除记录 String query = "Delete from customers where salary < 5000"; int i = stmt.executeUpdate(query); System.out.println("Rows deleted: "+i); //检索数据 ResultSet rs = stmt.executeQuery("Select * from customers"); System.out.println("Contents of the table after deleting the records: "); } }
输出结果
Connection established...... Rows deleted: 6 Contents of the table after deleting the records: ID: 3, Name: Renuka, Age: 30, Salary: 5000, Address: Delhi ID: 5, Name: Koushik, Age: 30, Salary: 9000, Address: Kota ID: 6, Name: Hardik, Age: 45, Salary: 6400, Address: Bhopal ID: 10, Name: Rajaneesh, Age: 30, Salary: 6400, Address: Delhi ID: 11, Name: Komal, Age: 29, Salary: 8000, Address: Ahmedabad ID: 12, Name: Manyata, Age: 25, Salary: 5000, Address: Vijayawada