Apache Derby 简明教程
Apache Derby - Update Data
UPDATE 语句用于更新表中的数据。Apache Derby 提供了两种类型的更新（语法），即 searched 更新和 positioned 更新。
The UPDATE statement is used to update data in a table. Apache Derby provides two types of updates (syntax) namely searched update and positioned update.
要搜索的 UPDATE 语句更新表的全部指定列。
The searched UPDATE statement updates all the specified columns of a table.
Syntax
以下是 UPDATE 查询的语法 −
Following is the syntax of the UPDATE query −
ij> UPDATE table_name
SET column_name = value, column_name = value, ...
WHERE conditions;WHERE 子句可以使用比较运算符，例如 =、!=、<、>、⇐ 和 >=，以及 BETWEEN 和 LIKE 运算符。
The WHERE clause can use the comparison operators such as =, !=, <, >, ⇐, and >=, as well as the BETWEEN and LIKE operators.
Example
假设你在数据库中有一个名为 Employee 的表，它包含 4 条记录，如下所示 −
Suppose you have a table Employee in the database with the 4 records as shown below −
ID |NAME |SALARY |LOCATION
----------------------------------------------------------
1 |Amit |30000 |Hyderabad
2 |Kalyan |40000 |Vishakhapatnam
3 |Renuka |50000 |Delhi
4 |Archana|15000 |Mumbai下面的 SQL UPDATE 语句更新了名为 Kaylan 的员工的位置和薪水。
The following SQL UPDATE statement updates the location and salary of an employee whose name is Kaylan.
ij> UPDATE Employees SET Location = 'Chennai', Salary = 43000 WHERE Name='Kalyan';
1 rows inserted/updated/deleted如果你获取 Employees 表的内容，你可以观察到 UPDATE 查询所做的更改。
If you get the contents of the Employees table, you can observe the changes done by the UPDATE query.
ij> select * from Employees;
ID |NAME |SALARY |LOCATION
----------------------------------------------------------
1 |Amit |30000 |Hyderabad
2 |Kalyan |43000 |Chennai
3 |Renuka |50000 |Delhi
4 |Archana |15000 |Mumbai
4 rows selectedUpdate Data using JDBC program
本节说明了如何使用 JDBC 应用程序更新 Apache Derby 数据库中现有记录。
This section explains how to update the existing records of a table in the 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 "
If you want to request the Derby network server using network client, make sure that the server is up and running. The class name for the Network client driver is org.apache.derby.jdbc.ClientDriver and the URL is jdbc:derby://localhost:1527/DATABASE_NAME;create=true;user=USER_NAME ;password=PASSWORD"
按照以下步骤在 Apache Derby 中更新表的现有记录。
Follow the steps given below to update the existing records of a table in Apache Derby.
Step 1: Register the driver
要与数据库通信，首先需要注册驱动程序。类 Class 的 forName() 方法接受表示类名称的 String 值，将其加载到内存中，这会自动注册它。使用此方法注册驱动程序。
To communicate with the database, first of all, you need to register the driver. 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
通常，我们与数据库进行通信时执行的第一步就是与数据库连接。连接类表示与数据库服务器之间的物理连接。你可以通过调用类 DriverManager 的方法 getConnection() 创建连接对象。使用此方法来创建连接。
In general, the first step we do to communicate to the database is to connect with it. The Connection class represents the 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() 和 prepareCall() 方法创建这些对象。使用适当的方法创建其中一个对象。
You need to create a Statement or PreparedStatement or, CallableStatement objects to send SQL statements to the database. You can create these using the methods createStatement(), prepareStatement() and, prepareCall() respectively. Create either of these objects using the appropriate method.
Step 4: Execute the query
创建该语句后，你需要执行它。类 Statement 提供了多个方法来执行查询，如方法 execute() 以执行返回多个结果集的语句。方法 executeUpdate() 执行诸如 INSERT、UPDATE、DELETE 的查询。方法 executeQuery() 返回数据。使用其中一种方法并执行之前创建的语句。
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 returns data. Use either of these methods and execute the statement created previously.
Example
以下 JDBC 示例演示了如何使用 JDBC 程序更新 Apache Derby 中表的现有记录。在这里，我们使用嵌入式驱动程序连接到名为 sampleDB（如果不存在将创建）的数据库。
Following JDBC example demonstrates how to update the existing records of a table in Apache Derby using JDBC program. Here, we are connecting to a database named sampleDB (will create if it does not exist) using the embedded driver.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class UpdateData {
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'), "
+ "('Trupti', 45000, 'Kochin')";
//Executing the query
String query = "UPDATE Employees SET Location = 'Chennai', Salary = 43000 WHERE
Name = 'Kalyan'";
int num = stmt.executeUpdate(query);
System.out.println("Number of records updated are: "+num);
}
}