Apache Derby 简明教程

Apache Derby - Retrieve Data

SELECT 语句用于从表中检索数据。这会以称为结果集的表格形式返回数据。

The SELECT statement is used to retrieve data from a table. This returns the data in the form of a table known as result set.

Syntax

下面是 SELECT 语句的语法：

Following is the syntax of the SELECT statement −

ij> SELECT column_name, column_name, ... FROM table_name;
Or,
Ij>SELECT * from table_name

Example

假设我们在数据库中有一个名为 Employees 的表，如下所示：

Let us suppose we have a table named Employees in the database as shown below −

ij> CREATE TABLE Employees (
   Id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
   Name VARCHAR(255),
   Salary INT NOT NULL,
   Location VARCHAR(255),
   PRIMARY KEY (Id)
);
> > > > > > > 0 rows inserted/updated/deleted

并且在其中插入了四条记录，如下所示：

And, inserted four records in it as shown below −

ij> INSERT INTO Employees (Name, Salary, Location) VALUES
   ('Amit', 30000, 'Hyderabad'),
   ('Kalyan', 40000, 'Vishakhapatnam'),
   ('Renuka', 50000, 'Delhi'),
   ('Archana', 15000, 'Mumbai');
> > > > 4 rows inserted/updated/deleted

以下 SQL 语句检索表中所有员工的姓名、年龄和工资详细信息：

The following SQL statement retrieves the name, age and salary details of all the employees in the table:

ij> SELECT Id, Name, Salary FROM Employees;

此查询的输出为：

The output of this query is −

ID|NAME   |SALARY
------------------------------------------------------------------------
1 |Amit   |30000
2 |Kalyan |40000
3 |Renuka |50000
4 |Archana|15000
4 rows selected

如果您想一次获取此表的全部记录，请使用 * 而非列名称。

If you want to get all the records of this table at once, use * instead of the names of the columns.

ij> select * from Employees;

这会产生以下结果 −

This will produce the following result −

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

Retrieve Data using JDBC program

本部分将教您如何使用 JDBC 应用程序从 Apache Derby 数据库中的表中检索数据。

This section teaches you how to Retrieve data from 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 ;passw ord= 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;passw ord=PASSWORD"

按照以下步骤从 Apache Derby 表中检索数据：

Follow the steps given below to Retrieve data from 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

通常，我们与数据库进行通信时执行的第一步就是与数据库连接。类 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 或 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 to results that returns data etc. Use either of these methods and execute the statement created previously.

Example

下面的 JDBC 示例演示了如何使用 JDBC 程序从 Apache Derby 中的表格检索数据。在这里，我们使用嵌入驱动程序连接到名为 sampleDB（如果不存在，则创建）的数据库。

Following JDBC example demonstrates how to Retrieve data from 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.

方法 executeQuery() 返回 ResultSet 对象，该对象包含该语句的结果。最初，结果集指针将位于第一条记录处，你可以使用其方法 next() 和 getXXX() 打印 ResultSet 对象的内容。

The executeQuery() method returns a ResultSet object which holds the result of the statement. Initially the result set pointer will be at the first record, you can print the contents of the ResultSet object using its next() and getXXX() methods.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class RetrieveData {
   public static void main(String args[]) throws SQLException,
      ClassNotFoundException {
      //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
      4Statement 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 = "SELECT Id, Name, Salary FROM Employees";
      ResultSet rs = stmt.executeQuery(query);
      while(rs.next()) {
         System.out.println("Id: "+rs.getString("Id"));
         System.out.println("Name: "+rs.getString("Name"));
         System.out.println("Salary: "+rs.getString("Salary"));
         System.out.println(" ");
      }
   }
}

Output

执行上述程序后，你将获得以下输出。

On executing the above program, you will get the following output.

Id: 1
Name: Amit
Salary: 30000

Id: 2
Name: Kalyan
Salary: 43000

Id: 3
Name: Renuka
Salary: 50000

Id: 4
Name: Archana
Salary: 15000

Id: 5
Name: Trupthi
Salary: 45000

Id: 6
Name: Suchatra
Salary: 33000

Id: 7
Name: Rahul
Salary: 39000