Apache Derby 简明教程

Apache Derby - Create Table

CREATE TABLE 语句用于在 Derby 数据库中创建新表。

The CREATE TABLE statement is used for creating a new table in Derby database.

Syntax

以下是 CREATE 语句的语法。

Following is the syntax of the CREATE statement.

CREATE TABLE table_name (
   column_name1 column_data_type1 constraint (optional),
   column_name2 column_data_type2 constraint (optional),
   column_name3 column_data_type3 constraint (optional)
);

在 Apache Derby 中创建表的另一种方式是您可以通过查询指定列名和数据类型。语法如下所示 −

Another way to create a table in Apache Derby is that you can specify the column names and data types using a query. The syntax for this is given below −

CREATE TABLE table_name AS SELECT * FROM desired_table WITH NO DATA;

Example

以下 SQL 语句创建了一个名为 Student 的表，其中包含四列，其中 id 是主键并且自动生成。

The following SQL statement creates a table named Student with four columns, where id is the primary key and it is auto generated.

ij> CREATE TABLE Student (
   Id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
   Age INT NOT NULL,
   First_Name VARCHAR(255),
   last_name VARCHAR(255),
   PRIMARY KEY (Id)
);
> > > > > > > 0 rows inserted/updated/deleted

DESCRIBE 命令通过列出列及其详细信息（如果表存在）来描述指定的表。您可以使用此命令验证表是否创建。

The DESCRIBE command describes specified table by listing the columns and their details, if the table exists. You can use this command to verify if the table is created.

ij> DESCRIBE Student;
COLUMN_NAME |TYPE_NAME |DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
ID |INTEGER |0 |10 |10 |AUTOINCRE&|NULL |NO
AGE |INTEGER |0 |10 |10 |NULL |NULL |NO
FIRST_NAME |VARCHAR |NULL|NULL|255 |NULL |510 |YES
LAST_NAME |VARCHAR |NULL|NULL|255 |NULL |510 |YES
4 rows selected

Create a Table using JDBC Program

本节介绍如何使用 JDBC 应用程序在 Apache Derby 数据库中创建表。

This section teaches you how to create 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 create a table in Apache Derby −

Step 1: Register the driver

要与数据库通信，首先，您需要注册驱动。类的 forName() 方法 Class 接受表示类名称的 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 or, CallableStatement 对象以将 SQL 语句发送到数据库。您可以分别使用 createStatement(), prepareStatement() and, 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 create 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 CreateTable {
   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();

      //Executing the query
      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))";
         stmt.execute(query);
         System.out.println("Table created");
   }
}

Output

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

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

Table created