Apache Derby 简明教程

Apache Derby - Delete Data

DELETE 语句用于删除表的行。就像 UPDATE 语句一样，Apache Derby 提供两种类型的 Delete（语法）： searched delete 和 positioned delete。

searched delete 语句删除表的所有指定列。

Syntax

DELETE 语句的语法如下 −

ij> DELETE FROM table_name WHERE condition;

Example

Let us suppose we have a table named employee with 5 records as shown below −

ID |NAME     |SALARY |LOCATION
----------------------------------------------------------------------------
1  |Amit     |30000  |Hyderabad
2  |Kalyan   |40000  |Vishakhapatnam
3  |Renuka   |50000  |Delhi
4  |Archana  |15000  |Mumbai
5  |Trupti   |45000  |Kochin
5 rows selected

The following SQL DELETE statement deletes the record with name Trupti.

ij> DELETE FROM Employees WHERE Name = 'Trupti';
1 row inserted/updated/deleted

If you get the contents of the Employees table, you can see only four records as shown below −

ID |NAME    |SALARY |LOCATION
----------------------------------------------------------------------------
1  |Amit    |30000  |Hyderabad
2  |Kalyan  |40000  |Vishakhapatnam
3  |Renuka  |50000  |Delhi
4  |Archana |15000  |Mumbai
4 rows selected

To delete all the records in the table, execute the same query without where clause.

ij> DELETE FROM Employees;
4 rows inserted/updated/deleted

Now, if you try to get the contents of the Employee table, you will get an empty table as given below −

ij> select * from employees;
ID |NAME |SALARY |LOCATION
--------------------------------------------------------
0 rows selected

Delete Data using JDBC program

This section explains how to delete the existing records of a table in Apache Derby database using JDBC application.

如果你想使用网络客户端访问 Derby 网络服务器，请确保服务器正在运行。网络客户端驱动程序的类名为 org.apache.derby.jdbc.ClientDriver，URL 为 jdbc:derby://localhost:1527/ DATABASE_NAME;*create=true;user=*USER_NAME;*password=*PASSWORD "。

按照以下步骤删除 Apache Derby 表中的现有记录：

Step 1: Register the driver

Firstly, you need to register the driver to communicate with the database. The forName() method of the class Class accepts a String value representing a class name loads it in to the memory, which automatically registers it. Register the driver using this method.

Step 2: Get the connection

In general, the first step we do to communicate to the database is to connect with it. The Connection class represents physical connection with a database server. You can create a connection object by invoking the getConnection() method of the DriverManager class. Create a connection using this method.

Step 3: Create a statement object

您需要创建一个 Statement 或 PreparedStatement or, CallableStatement 对象以将 SQL 语句发送到数据库。您可以分别使用 createStatement(), prepareStatement() and, prepareCall() 方法创建这些对象。使用相应的方法创建其中任何一个对象。

Step 4: Execute the query

After creating a statement, you need to execute it. The Statement class provides various methods to execute a query like the execute() method to execute a statement that returns more than one result set. The executeUpdate() method executes queries like INSERT, UPDATE, DELETE. The executeQuery() method results that returns data. Use either of these methods and execute the statement created previously.

Example

以下 JDBC 示例演示了如何使用 JDBC 程序删除 Apache Derby 中表的现有记录。在此，我们使用嵌入式驱动程序连接到名为 sampleDB 的数据库（如果不存在则创建）。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class DeleteData {
   public static void main(String args[]) throws Exception {
      //Registering the driver
      Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
      //Getting the Connection object
      String URL = "jdbc:derby:sampleDB;create=true";
      Connection conn = DriverManager.getConnection(URL);

      //Creating the Statement object
      Statement stmt = conn.createStatement();
      //Creating a table and populating it
      String query = "CREATE TABLE Employees("
         + "Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, "
         + "Name VARCHAR(255), Salary INT NOT NULL, "
         + "Location VARCHAR(255), "
         + "PRIMARY KEY (Id))";
      String query = "INSERT INTO Employees("
         + "Name, Salary, Location) VALUES "
         + "('Amit', 30000, 'Hyderabad'), "
         + "('Kalyan', 40000, 'Vishakhapatnam'), "
         + "('Renuka', 50000, 'Delhi'), "
         + "('Archana', 15000, 'Mumbai'), "
         + "('Trupthi', 45000, 'Kochin'), "
         + "('Suchatra', 33000, 'Pune'), "
         + "('Rahul', 39000, 'Lucknow'), "
         + "('Trupthi', 45000, 'Kochin')";
      //Executing the query
      String query = "DELETE FROM Employees WHERE Name = 'Trupthi'";
      int num = stmt.executeUpdate(query);
      System.out.println("Number of records deleted are: "+num);
   }
}

Output

执行上述程序时，你将得到以下输出：

Number of records deleted are: 1