H2 Database 简明教程

H2 Database - Quick Guide

H2 Database - Introduction

H2 是一个开源轻型 Java 数据库。它可以嵌入在 Java 应用程序中，或在客户端-服务器模式下运行。H2 数据库主要可以配置为内存数据库运行，这意味着数据不会保存在磁盘上。由于是嵌入式数据库，因此它不会用于生产开发，而是主要用于开发和测试。

H2 is an open-source lightweight Java database. It can be embedded in Java applications or run in the client-server mode. Mainly, H2 database can be configured to run as inmemory database, which means that data will not persist on the disk. Because of embedded database it is not used for production development, but mostly used for development and testing.

此数据库可以在嵌入模式或服务器模式下使用。以下是 H2 数据库的主要功能 -

This database can be used in embedded mode or in server mode. Following are the main features of H2 database −

Extremely fast, open source, JDBC API
Available in embedded and server modes; in-memory databases
Browser-based Console application
Small footprint − Around 1.5MB jar file size

Features of H2 Database

H2 数据库的主要功能如下 -

The main features of H2 Database are as follows −

It is an extremely fast database engine.
H2 is open source and written in Java.
It supports standard SQL and JDBC API. It can use PostgreSQL ODBC driver too.
It has embedded and Server mode.
H2 supports clustering and multi-version concurrency.
It has strong security features.

Additional Features

以下是 H2 数据库的一些附加功能 -

Following are some additional features of H2 Database −

H2 is a disk-based or in-memory databases and tables, read-only database support, temporary tables.
H2 provides transaction support (read committed), 2-phase-commit multiple connections, table level locking.
H2 is a cost-based optimizer, using a genetic algorithm for complex queries, zeroadministration.
H2 contains scrollable and updatable result set support, large result set, external result sorting, functions can return a result set.
H2 supports encrypted database (AES), SHA-256 password encryption, encryption functions, and SSL.

Components in H2 Database

为了使用 H2 数据库，您需要具有以下组件 -

In order to use H2 Database, you need to have the following components −

A web browser
A H2 console server

这是一个客户端/服务器应用程序，因此需要服务器和客户端（浏览器）才能运行它。

This is a client/server application, so both server and client (a browser) are required to run it.

H2 Database - Installation

H2 是用 Java 编写的数据库。我们可以使用 JDBC 轻松地将该数据库嵌入到应用程序中。我们可以在许多不同的平台或任何版本的 Java 运行时环境中运行它。但是，在安装数据库之前，应该在系统中安装 Java。

H2 is a database written in Java. We can easily embed this database to our application by using JDBC. We can run this on many different platforms or any version of Java Runtime Environment. However, before installing the database, there should be Java installed in the system.

Verify Java Installation

如果在系统中安装了 JDK，请尝试使用以下命令验证 Java 版本。

If JDK is installed in the system, try the following command to verify the Java version.

java –version

如果已成功在系统中安装 JDk，那么我们将会看到以下输出。

If JDk is successfully installed in the system, then we will get the following output.

java version "1.8.0_91"
Java(TM) SE Runtime Environment (build 1.8.0_91-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.91-b14, mixed mode)

如果系统中未安装 JDK，请访问以下链接 Install JDK 。

If JDK is not installed in the system, then visit the following link to Install JDK.

Install H2 Database

我们可以在许多不同的平台上运行该数据库。在本章中，我们将了解 H2 数据库在 Windows 上的安装方法。

We can run this database on many different platforms. In this chapter, we will learn about H2 Database installation on Windows.

以下是 Windows 操作系统下安装 H2 数据库的步骤。

Following are the steps to install H2 Database on Windows operating system.

Step 1: Download H2 Setup File

从提供的链接下载最新版本的 H2 Database 。在该链接中，您将获得以两种类型提供的 H2 数据库的最新版本。一种是 Windows Installer 类型（即 .exe 文件），另一种是适用于其他操作系统的平台无关的 zip 文件。

Download the latest version of H2 Database from the given link. In this link, you will get the latest version of H2 database in two types. One is Windows Installer type (that is .exe file) and second is Platform-Independent zip file for other operating systems.

下载 .exe 文件后，单击 Windows 安装程序以下载 Windows 支持的 H2 数据库。在此案例中，我们将使用带有版本 1.4.192 的 H2 数据库。

Click the Windows installer for downloading the Windows supportable H2 database after downloading the .exe file. In this case, we are using H2 Database with the version 1.4.192.

Step 2: Install H2 Database

下载后，我们在“下载”目录中获取 H2 Windows 安装程序文件（即 h2-setup-yyyy-mm-dd.exe）。要启动 H2 数据库的安装过程，请双击安装程序文件。

After downloading we get the H2 Windows installer file (i.e. h2-setup-yyyy-mm-dd.exe) in the Downloads directory. To start the installation process of H2 Database, double click on the installer file.

以下屏幕是安装过程的第一步。提供一个位置，以便像以下屏幕截图所示的那样安装 H2 数据库服务器。

The following screen is the first step in the installation process. Provide a path where we want to install the H2 database server as shown in the following screenshot.

如以上屏幕截图所示，默认情况下它会将 C:\ProgramFiles (x86)\H2 作为目标文件夹。单击下一步继续执行下一步。然后会弹出以下屏幕。

As seen in the above screenshot, by default it will take C:\ProgramFiles (x86)\H2 as the destination folder. Click next to proceed to the next step. The following screen pops up.

在以上屏幕截图中，单击“安装”按钮以启动安装过程。安装后，我们会看到以下屏幕截图。

In the above screenshot, click the Install button to start the installation process. After installation, we get the following screenshot.

单击“完成”以完成安装过程。

Click Finish to complete the installation process.

Step 3: Verify H2 Database Installation

安装后，让我们验证系统中的数据库安装。单击 Windows → 键入 H2 控制台 → 单击 H2 控制台图标。连接到 URL http://localhost:8082 。在连接时，H2 数据库将要求进行数据库注册，如下面的屏幕截图所示。

After installation, let us verify the database installation in the system. Click Windows → type H2 Console → Click H2 console icon. Connect to the URL http://localhost:8082. At the time of connecting, the H2 database will ask for database registration as shown in the following screenshot.

在上述对话框中填写所有详细信息，例如已保存设置、设置名称、驱动程序类、JDBC URL、用户名以及密码。在 JDBC URL 中，指定数据库的位置和数据库名称。用户名和密码是数据库用户名和密码的字段。单击“连接”。

Fill all the details in the above dialog box such as Saved Settings, Settings Name, Driver Class, JDBC URL, User Name, and Password. In the JDBC URL, specify the database is located and the database name. User Name and Password are the fields for user name and password of the database. Click Connect.

由此数据库欢迎页面会弹出，如下所示。

The Database welcome page pops up as shown in the following screenshot.

H2 Database - Select

选择命令用于从一个或多个表中获取记录数据。如果我们设计了一个选择查询，它将以 result sets 称为结果表的形式返回数据。

Select command is used to fetch record data from a table or multiple tables. If we design a select query, then it returns data in the form of result table called result sets.

Syntax

SELECT 语句的基本语法如下所示：

The basic syntax of SELECT statement is as follows −

SELECT [ TOP term ] [ DISTINCT | ALL ] selectExpression [,...]
FROM tableExpression [,...] [ WHERE expression ]
[ GROUP BY expression [,...] ] [ HAVING expression ]
[ { UNION [ ALL ] | MINUS | EXCEPT | INTERSECT } select ] [ ORDER BY order [,...] ]
[ [ LIMIT expression ] [ OFFSET expression ] [ SAMPLE_SIZE rowCountInt ] ]
[ FOR UPDATE ]

为了获取所有可用字段，请使用以下语法：

To fetch all the available fields, use the following syntax.

SELECT * FROM table_name;

Example

考虑 CUSTOMER 表，其中有以下记录：

Consider the CUSTOMER table having the following records −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

要获取 CUSTOMER 表中已给出的数据，执行以下查询。

To get the customer table along with the given data, execute the following queries.

CREATE TABLE CUSTOMER (id number, name varchar(20), age number, address varchar(20),
salary number);

INSERT into CUSTOMER values (1, 'Ramesh', 32, 'Ahmedabad', 2000);
INSERT into CUSTOMER values (2, 'Khilan', 25, 'Delhi', 1500);
INSERT into CUSTOMER values (3, 'kaushik', 23, 'Kota', 2000);
INSERT into CUSTOMER values (4, 'Chaitali', 25, 'Mumbai', 6500);
INSERT into CUSTOMER values (5, 'Hardik', 27, 'Bhopal', 8500);
INSERT into CUSTOMER values (6, 'Komal', 22, 'MP', 4500);
INSERT into CUSTOMER values (7, 'Muffy', 24, 'Indore', 10000);

以下命令是一个示例，它将获取 CUSTOMER 表中客户的 ID、名称和工资字段。

The following command is an example, which would fetch ID, Name and Salary fields of the customers available in the CUSTOMER table.

SELECT ID, NAME, SALARY FROM CUSTOMERS;

上述命令会产生以下结果。

The above command produces the following result.

+----+----------+----------+
| ID | NAME     | SALARY   |
+----+----------+----------+
|  1 | Ramesh   |  2000.00 |
|  2 | Khilan   |  1500.00 |
|  3 | kaushik  |  2000.00 |
|  4 | Chaitali |  6500.00 |
|  5 | Hardik   |  8500.00 |
|  6 | Komal    |  4500.00 |
|  7 | Muffy    | 10000.00 |
+----+----------+----------+

使用以下查询来获取 CUSTOMERS 表的所有字段。

Use the following query to fetch all the fields of CUSTOMERS table.

SQL> SELECT * FROM CUSTOMERS;

上述查询会产生以下结果：

The above query produces the following result −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

H2 Database - Insert

SQL INSERT 语句用于将新行的数据添加到数据库中的表。

The SQL INSERT statement is used to add new rows of data to a table in the database.

Syntax

以下是 INSERT INTO 语句的基本语法。

Following is the basic syntax of INSERT INTO statement.

INSERT INTO tableName
{ [ ( columnName [,...] ) ]
{ VALUES
{ ( { DEFAULT | expression } [,...] ) } [,...] | [ DIRECT ] [ SORTED ] select } } |
{ SET { columnName = { DEFAULT | expression } } [,...] }

使用这个 INSERT 语句，我们可以往表中插入一条新记录或多行新记录。当使用 DIRECT 子句时，结果会直接影响目标表，没有任何中间步骤。然而，在为表的所有列添加值的同时，确保值的顺序与表中的列相同。

Using this INSERT statement, we can insert a new record or new rows into a table. When using DIRECT clause, the results are directly affected to the target table without any intermediate step. However, while adding values for all the columns of the table, make sure the order of the values is in the same order as the columns in the table.

Example

我们举一个例子，并尝试将以下给定的记录插入到 Customer 表中。

Let us take an example and try to insert the following given records into the Customer table.

ID	Name	Age	Address	Salary
1	Ramesh	32	Ahmedabad	2000
2	Khilan	25	Delhi	1500
3	Kaushik	23	Kota	2000
4	Chaitail	25	Mumbai	6500
5	Hardik	27	Bhopal	8500
6	Komal	22	MP	4500
7	Muffy	24	Indore	10000

我们可以通过执行以下命令将所有给定的记录放入 customer 表中。

We can get all the given records into the customer table by executing the following commands.

INSERT INTO CUSTOMER VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000);
INSERT INTO CUSTOMER VALUES (2, 'Khilan', 25, 'Delhi', 1500);
INSERT INTO CUSTOMER VALUES (3, 'kaushik', 23, 'Kota', 2000);
INSERT INTO CUSTOMER VALUES (4, 'Chaitali', 25, 'Mumbai', 6500);
INSERT INTO CUSTOMER VALUES (5, 'Hardik', 27, 'Bhopal', 8500);
INSERT INTO CUSTOMER VALUES (6, 'Komal', 22, 'MP', 4500);
INSERT INTO CUSTOMER VALUES (7, 'Muffy', 24, 'Indore', 10000);

H2 Database - Update

UPDATE 查询用于更新或修改表中的现有记录。我们可以将 WHERE 子句和 UPDATE 查询结合在一起，用于更新选定的行，否则所有行都会受到影响。

The UPDATE query is used to update or modify the existing records in a table. We can use WHERE clause with UPDATE query to update the selected rows, otherwise all the rows would be affected.

Syntax

以下是 UPDATE 查询的基本语法。

Following is the basic syntax of the UPDATE query.

UPDATE tableName [ [ AS ] newTableAlias ] SET
{ { columnName = { DEFAULT | expression } } [,...] } |
{ ( columnName [,...] ) = ( select ) }
[ WHERE expression ] [ ORDER BY order [,...] ] [ LIMIT expression ]

在这个 UPDATE 语法中，我们可以通过使用 AND 或 OR 子句组合超过一个条件。

In this UPDATE syntax, we can combine more than one condition by using AND or OR clauses.

Example

考虑具有以下记录的 CUSTOMER 表。

Consider the CUSTOMER table having the following records.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

如果您想获得具有给定数据的客户表，请执行以下查询。

If you want to get the customer table along with the given data, execute the following queries.

CREATE TABLE CUSTOMER (id number, name varchar(20), age number, address varchar(20),
   salary number);
INSERT into CUSTOMER values (1, 'Ramesh', 32, 'Ahmedabad', 2000);
INSERT into CUSTOMER values (2, 'Khilan', 25, 'Delhi', 1500);
INSERT into CUSTOMER values (3, 'kaushik', 23, 'Kota', 2000);
INSERT into CUSTOMER values (4, 'Chaitali', 25, 'Mumbai', 6500);
INSERT into CUSTOMER values (5, 'Hardik', 27, 'Bhopal', 8500);
INSERT into CUSTOMER values (6, 'Komal', 22, 'MP', 4500);
INSERT into CUSTOMER values (7, 'Muffy', 24, 'Indore', 10000);

以下命令是一个示例，它将更新 ID 为 6 的客户的 ADDRESS −

The following command is an example, which would update ADDRESS for a customer whose ID is 6 −

UPDATE CUSTOMERS SET ADDRESS = 'Pune' WHERE ID = 6;

现在，CUSTOMERS 表将具有以下记录。我们可以通过执行以下查询来检查客户表记录。

Now, CUSTOMERS table would have the following records. We can check the customer table records by executing the following query.

SELECT * FROM CUSTOMERS;

以上查询得出以下结果。

The above query produces the following result.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | Pune      |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

要修改 CUSTOMERS 表中的所有 ADDRESS 和 SALARY 列值，我们不需要使用 WHERE 子句。UPDATE 查询如下 −

To modify all ADDRESS and SALARY column values in CUSTOMERS table, we need not use the WHERE clause. The UPDATE query would be as follows −

UPDATE CUSTOMERS SET ADDRESS = 'Pune', SALARY = 1000.00;

现在，CUSTOMERS 表将具有以下记录。我们可以通过执行以下查询来检查客户表记录。

Now, CUSTOMERS table would have the following records. We can check the customer table records by executing the following query.

SELECT * FROM CUSTOMERS;

上述查询会产生以下结果：

The above query produces the following result −

+----+----------+-----+---------+---------+
| ID | NAME     | AGE | ADDRESS | SALARY  |
+----+----------+-----+---------+---------+
|  1 | Ramesh   |  32 | Pune    | 1000.00 |
|  2 | Khilan   |  25 | Pune    | 1000.00 |
|  3 | kaushik  |  23 | Pune    | 1000.00 |
|  4 | Chaitali |  25 | Pune    | 1000.00 |
|  5 | Hardik   |  27 | Pune    | 1000.00 |
|  6 | Komal    |  22 | Pune    | 1000.00 |
|  7 | Muffy    |  24 | Pune    | 1000.00 |
+----+----------+-----+---------+---------+

H2 Database - Delete

SQL DELETE 查询用于从表中删除现有记录。我们可以对 DELETE 查询使用 WHERE 子句来删除选定的记录，否则将删除所有记录。

The SQL DELETE query is used to delete the existing records from a table. We can use WHERE clause with DELETE query to delete selected records, otherwise all the records will be deleted.

Syntax

以下是 delete 命令的通用查询语法。

Following is the generic query syntax of the delete command.

DELETE [ TOP term ] FROM tableName [ WHERE expression ] [ LIMIT term ]

以上语法从表中删除行。如果指定了 TOP 或 LIMIT，则最多删除指定数量的行（如果为 null 或小于零，则无限制）。

The above syntax deletes the rows from a table. If TOP or LIMIT is specified, at most the specified number of rows are deleted (no limit if null or smaller than zero).

Example

考虑具有以下记录的 CUSTOMER 表。

Consider the CUSTOMER table having the following records.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

以下命令将删除 ID 为 6 的客户的详细信息。

The following command will delete the details of the customer, whose ID is 6.

DELETE FROM CUSTOMERS WHERE ID = 6;

执行以上命令后，通过执行以下命令检查 Customer 表。

After execution of the above command, check the Customer table by executing the following command.

SELECT * FROM CUSTOMERS;

以上命令生成以下输出 −

The above command produces the following output −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

如果我们要从 CUSTOMERS 表中 DELETE 所有记录，则不使用 WHERE 子句。DELETE 查询如下。

If we want to DELETE all the records from CUSTOMERS table, we do not use WHERE clause. The DELETE query would be as follows.

DELETE FROM CUSTOMER;

执行以上命令后，Customer 表中将没有任何记录。

After executing the above command, no records will be available in the Customer table.

H2 Database - Backup

BACKUP 是用于将数据库备份到单独的 .zip 文件中的命令。对象未锁定，在对备份进行备份时，也会复制事务日志。执行此命令需要管理员权限。

BACKUP is the command used to take database backup into a separate .zip file. Objects are not locked, and when it takes backup the transaction log is also copied. Admin rights are required to execute this command.

Syntax

以下是 Backup 命令的通用语法。

Following is the generic syntax of the Backup command.

BACKUP TO fileNameString;

Example

在此示例中，让我们将当前数据库备份到 backup.zip 文件中。为此，请使用以下命令。

In this example, let us take a backup of the current database into backup.zip file. Use the following command for the same.

BACKUP TO 'backup.zip';

在执行以上命令时，您将在本地文件系统中获得 backup.zip 文件。

On executing the above command, you will get the backup.zip file in your local file system.

H2 Database - Call

CALL 是一个属于 H2 数据库服务器的 SQL 命令。此命令用于计算一个简单的表达式。它以单列字段形式返回给定表达式的结果。当它返回一个结果数组时，数组中的每个元素将会显示为一个列值。

CALL is a SQL command which belongs to H2 database server. This command is used to calculate a simple expression. It returns the result of the given expression in a single column field. When it returns an array of results, then each element in the array is displayed as a column value.

Syntax

以下是 CALL 命令的通用语法。

Following is the generic syntax of the CALL command.

CALL expression;

我们可以在此语法中使用算术表达式。

We can use the arithmetic expression in this syntax.

Example

让我们通过使用 call 命令来执行一个算术表达式 (15 * 25) 并作为一个实例。

Let us take an example and execute an arithmetic expression (15 * 25) using call command.

CALL 15*25;

以上命令会产生以下输出。

The above command produces the following output.

375

H2 Database - Explain

EXPLAIN 命令显示语句的执行计划。当我们使用 EXPLAIN ANALYZE 命令执行语句时，查询计划将包括每个表的实际行扫描计数。

EXPLAIN command displays the execution plan for a statement. When we execute a statement using EXPLAIN ANALYZE command, the query plan will include the actual row scan count for each table.

Syntax

以下是 EXPLAIN 命令的通用语法。

Following is the generic syntax of the EXPLAIN command.

EXPLAIN { [ PLAN FOR ] | ANALYZE } { select | insert | update | delete | merge}

除了此语法，我们还可以使用 select，insert，delete 和 merge。

Along with this syntax we can use select, insert, delete, and merge.

Example

此示例说明了具有 ID 1 的客户的查询计划详细信息。

This example explains the query plan details of the customer with ID 1.

EXPLAIN SELECT * FROM CUSTOMER WHERE ID = 1;

以上命令生成以下输出 −

The above command produces the following output −

H2 Database - Merge

MERGE 命令用于更新现有行和将新行插入表中。主键列在此命令的使用过程中发挥着重要作用；它用于查找行。

MERGE command is used to update the existing rows and insert new rows into a table. The primary key column plays an important role while using this command; it is used to find the row.

Syntax

以下是 MERGE 命令的通用语法。

Following is the generic syntax of the MERGE command.

MERGE INTO tableName [ ( columnName [,...] ) ]
[ KEY ( columnName [,...] ) ]
{ VALUES { ( { DEFAULT | expression } [,...] ) } [,...] | select }

在上面的语法中，KEY 子句用于指定主键列名。除了 VALUES 子句，我们可以使用基元值插入或使用 select 命令检索另一个表中的值并将其存储到此表中。

In the above syntax, the KEY clause is used to specify the primary key column name. Along with VALUES clause, we can use primitive values to insert or we can retrieve and store another table values into this table using the select command.

Example

在此示例中，我们尝试向 Customers 表中添加一条新记录。以下是表中新记录的详细信息。

In this example, let us try to add a new record into Customers table. Following are the details of the new record in the table.

Column Name

Value

NAME

Lokesh

AGE

ADDRESS

Hyderabad

SALARY

2500

使用以下查询，让我们将给定记录插入到 H2 数据库查询中。

Using the following query, let us insert the given record into the H2 database query.

MERGE INTO CUSTOMER KEY (ID) VALUES (8, 'Lokesh', 32, 'Hyderabad', 2500);

以上查询将产生以下输出。

The above query produces the following output.

Update count: 1

通过执行以下查询来验证 Customers 表中的记录。

Let us verify the records of the Customer table by executing the following query.

SELECT * FROM CUSTOMER;

以上查询将产生以下输出。

The above query produces the following output.

ID	Name	Age	Address	Salary
1	Ramesh	32	Ahmedabad	2000
2	Khilan	25	Delhi	1500
3	Kaushik	23	Kota	2000
4	Chaitali	25	Mumbai	6500
5	Hardik	27	Bhopal	8500
6	Komal	22	MP	4500
7	Muffy	24	Indore	10000
8	Lokesh	32	Hyderabad	2500

现在让我们尝试使用 Merge 命令更新记录。以下是将要更新的记录的详细信息。

Now let us try to update the record using the Merge command. Following are the details of the record to be updated.

Column Name

Value

NAME

Loki

AGE

ADDRESS

Hyderabad

SALARY

3000

使用以下查询将给定记录插入到 H2 数据库查询中。

Use the following query to insert the given record into the H2 database query.

MERGE INTO CUSTOMER KEY (ID) VALUES (8, 'Loki', 32, 'Hyderabad', 3000);

以上查询将产生以下输出。

The above query produces the following output.

Update count: 1

通过执行以下查询来验证 Customers 表中的记录。

Let us verify the records of the Customer table by executing the following query.

SELECT * FROM CUSTOMER;

上面的查询生成以下输出 −

The above query produces the following output −

ID	Name	Age	Address	Salary
1	Ramesh	32	Ahmedabad	2000
2	Khilan	25	Delhi	1500
3	Kaushik	23	Kota	2000
4	Chaitali	25	Mumbai	6500
5	Hardik	27	Bhopal	8500
6	Komal	22	MP	4500
7	Muffy	24	Indore	10000
8	Loki	32	Hyderabad	3000

H2 Database - Show

SHOW 是用于显示架构、表或表列的命令。

SHOW is a command used to display the list of Schemas, Tables, or Columns of the table.

Syntax

以下是 SHOW 命令的通用语法。

Following is the generic syntax of the SHOW command.

SHOW { SCHEMAS | TABLES [ FROM schemaName ] |
COLUMNS FROM tableName [ FROM schemaName ] }

Example

以下命令可用于获取当前数据库中的表列表。

The following command can be used to get the list of tables in the current database.

SHOW TABLES;

以上命令会产生以下输出。

The above command produces the following output.

TABLE_NAME

TABLE_SCHEMA

CUSTOMER

PUBLIC

EMP

PUBLIC

H2 Database - Create

CREATE 是用于在 H2 数据库服务器中创建表、模式、序列、视图和用户的通用 SQL 命令。

CREATE is a generic SQL command used to create Tables, Schemas, Sequences, Views, and Users in H2 Database server.

Create Table

创建表是用于在当前数据库中创建用户自定义表的命令。

Create Table is a command used to create a user-defined table in the current database.

Syntax

以下是创建表命令的通用语法。

Following is the generic syntax for the Create Table command.

CREATE [ CACHED | MEMORY ] [ TEMP | [ GLOBAL | LOCAL ] TEMPORARY ]
TABLE [ IF NOT EXISTS ] name
[ ( { columnDefinition | constraint } [,...] ) ]
[ ENGINE tableEngineName [ WITH tableEngineParamName [,...] ] ]
[ NOT PERSISTENT ] [ TRANSACTIONAL ]
[ AS select ]

通过使用创建表命令的通用语法，我们可以创建不同类型的表，如缓存表、内存表和临时表。以下是描述给定语法中不同子句的列表。

By using the generic syntax of the Create Table command, we can create different types of tables such as cached tables, memory tables, and temporary tables. Following is the list to describe different clauses from the given syntax.

CACHED − The cached tables are the default type for regular tables. This means the number of rows is not limited by the main memory.
MEMORY − The memory tables are the default type for temporary tables. This means the memory tables should not get too large and the index data is kept in the main memory.
TEMPORARY − Temporary tables are deleted while closing or opening a database. Basically, temporary tables are of two types − GLOBAL type − Accessible by all connections. LOCAL type − Accessible by the current connection. The default type for temporary tables is global type. Indexes of temporary tables are kept in the main memory, unless the temporary table is created using CREATE CACHED TABLE.
ENGINE − The ENGINE option is only required when custom table implementations are used.
NOT PERSISTENT − It is a modifier to keep the complete table data in-memory and all rows are lost when the database is closed.
TRANSACTIONAL − It is a keyword that commits an open transaction and this command supports only temporary tables.

Example

在这个示例中，让我们使用以下给定数据创建一个名为 tutorials_tbl 的表。

In this example, let us create a table named tutorials_tbl using the following given data.

Sr.No

Column Name

Data Type

Int

Title

Varchar(50)

Author

Varchar(20)

Submission_date

Date

以下查询用于创建表 tutorials_tbl 以及给定的列数据。

The following query is used to create a table tutorials_tbl along with the given column data.

CREATE TABLE tutorials_tbl (
   id INT NOT NULL,
   title VARCHAR(50) NOT NULL,
   author VARCHAR(20) NOT NULL,
   submission_date DATE
);

以上查询将产生以下输出。

The above query produces the following output.

(0) rows effected

Create Schema

创建模式是一个命令，用于在特定授权下（在当前注册的用户下）创建用户相关的模式。

Create Schema is a command used to create a user-dependent schema under a particular authorization (under the currently registered user).

Syntax

以下是创建模式命令的通用语法。

Following is the generic syntax of the Create Schema command.

CREATE SCHEMA [ IF NOT EXISTS ] name [ AUTHORIZATION ownerUserName ]

在上述通用语法中，AUTHORIZATION 是用于提供各自用户名的一个关键字。此命令是可选的，这意味着如果我们没有提供用户名，则它将考虑当前用户。执行此命令的用户必须拥有管理员权限以及所有者权限。

In the above generic syntax, AUTHORIZATION is a keyword used to provide the respective user name. This command is optional which means if we are not providing the user name, then it will consider the current user. The user that executes the command must have admin rights, as well as the owner.

此命令会提交此连接中的未决事务。

This command commits an open transaction in this connection.

Example

在此示例中，让我们使用以下命令在 SA 用户下创建一个名为 test_schema 的模式。

In this example, let us create a schema named test_schema under SA user, using the following command.

CREATE SCHEMA test_schema AUTHORIZATION sa;

以上命令会产生以下输出。

The above command produces the following output.

(0) rows effected

Create Sequence

序列是一个用于通过遵循 id 或任何随机列值的序列生成数字的概念。

Sequence is concept which is used to generate a number by following a sequence for id or any random column values.

Syntax

以下是 create sequence 命令的通用语法。

Following is the generic syntax of the create sequence command.

CREATE SEQUENCE [ IF NOT EXISTS ] newSequenceName [ START WITH long ]
[ INCREMENT BY long ]
[ MINVALUE long | NOMINVALUE | NO MINVALUE ]
[ MAXVALUE long | NOMAXVALUE | NO MAXVALUE ]
[ CYCLE long | NOCYCLE | NO CYCLE ]
[ CACHE long | NOCACHE | NO CACHE ]

此通用语法用于创建序列。序列的数据类型为 BIGINT 。在此序列中，即使事务回滚，值也不会被重新使用。

This generic syntax is used to create a sequence. The datatype of a sequence is BIGINT. In this the sequence, values are never re-used, even when the transaction is roll backed.

Example

在此示例中，让我们使用以下查询创建一个名为 SEQ_ID 的序列。

In this example, let us create a sequence named SEQ_ID, using the following query.

CREATE SEQUENCE SEQ_ID;

以上查询将产生以下输出。

The above query produces the following output.

(0) rows effected

H2 Database - Alter

ALTER 是一个用于通过向 alter 命令中添加不同的子句从而改变表结构的命令。根据场景，我们需要向 alter 命令添加相应的子句。在此章节中，我们将讨论 alter 命令的多种场景。

ALTER is a command used to change the table structure by adding different clauses to the alter command. Based on the scenario, we need to add respective clause to the alter command. In this chapter, we will discuss various scenarios of alter command.

Alter Table Add

Alter Table Add 是一个用于向表中添加一个新列和相应数据类型的一个命令。此命令将提交此连接中的事务。

Alter Table Add is a command used to add a new column to a table along with the respective data type. This command commits the transaction in this connection.

Syntax

以下是 Alter Table Add 命令的通用语法。

Following is the generic syntax of the Alter Table Add command.

ALTER TABLE [ IF EXISTS ] tableName ADD [ COLUMN ]
{ [ IF NOT EXISTS ] columnDefinition [ { BEFORE | AFTER } columnName ]
   | ( { columnDefinition } [,...] ) }

Example

在此实例中，我们将向表 tutorials_tbl 中添加一个新列 start_date 。start_date 的数据类型是 Date。以下是用于添加一个新列的查询。

In this example, we will add a new column start_date to the table tutorials_tbl. The datatype for start_date is Date. Following is the query to add a new column.

ALTER TABLE tutorials_tbl ADD start_date DATE;

以上查询将产生以下输出。

The above query produces the following output.

(6) rows effected

Alter Table Add Constraint

Alter table add constraint 是一个用于向表中添加不同约束的命令，例如果键、外键、非空等等。

Alter table add constraint is a command used to add different constraints to the table such as primary key, foreign key, not null, etc.

如果所需的索引尚不存在，则系统将会自动创建它们。不可能禁用对唯一约束的检查。此命令会提交此连接中的一个打开事务。

The required indexes are automatically created if they don’t exist yet. It is not possible to disable checking for unique constraint. This command commits an open transaction in this connection.

Syntax

以下是 Alter table add constraint 命令的通用语法。

Following is the generic syntax of the Alter table add constraint command.

ALTER TABLE [ IF EXISTS ] tableName ADD constraint [ CHECK | NOCHECK ]

Example

在此实例中，让我们使用以下查询向表 tutorials_tbl 的列 id 中添加一个主键约束 (tutorials_tbl_pk) 。

In this example, let us add a primary key constraint (tutorials_tbl_pk) to the column id of the table tutorials_tbl, using the following query.

ALTER TABLE tutorials_tbl ADD CONSTRAINT tutorials_tbl_pk PRIMARYKEY(id);

以上查询将产生以下输出。

The above query produces the following output.

(6) row (s) effected

Alter Table Rename Constraint

此命令用于重命名特定关系表的约束名。此命令会提交此连接中的一个打开事务。

This command is used to rename the constraint name of a particular relation table. This command commits an open transaction in this connection.

Syntax

以下是 Alter Table Rename Constraint 命令的通用语法。

Following is the generic syntax of the Alter Table Rename Constraint command.

ALTER TABLE [ IF EXISTS ] tableName RENAME oldConstraintName TO newConstraintName

在使用此语法时，请确保旧的约束名与相应的列一同存在。

While using this syntax, make sure that the old constraint name should exist with the respective column.

Example

在此实例中，我们将表 tutorials_tbl 的主键约束名从 tutorials_tbl_pk 改为 tutorials_tbl_pk_constraint 。以下是用于执行此操作的查询。

In this example, we will change the primary key constraint name of the table tutorials_tbl from tutorials_tbl_pk to tutorials_tbl_pk_constraint. Following is the query to do so.

ALTER TABLE tutorials_tbl RENAME CONSTRAINT
tutorials_tbl_pk TO tutorials_tbl_pk_constraint;

以上查询将产生以下输出。

The above query produces the following output.

(1) row (s) effected

Alter Table Alter Column

此命令用于改变特定表中列的结构和属性。改变属性指的是改变列的数据类型、重命名列、改变标识值，或者改变选择性。

This command is used to change the structure and properties of the column of a particular table. Changing the properties means changing the datatype of a column, rename a column, change the identity value, or change the selectivity.

Syntax

以下是修改表修改列命令的通用语法。

Following is the generic syntax of the Alter Table Alter Column command.

ALTER TABLE [ IF EXISTS ] tableName ALTER COLUMN columnName
{ { dataType [ DEFAULT expression ] [ [ NOT ] NULL ] [ AUTO_INCREMENT | IDENTITY ] }
| { RENAME TO name }
| { RESTART WITH long }
| { SELECTIVITY int }
| { SET DEFAULT expression }
| { SET NULL }
| { SET NOT NULL } }

在以上语法中 −

In the above syntax −

RESTART − command changes the next value of an auto increment column.
SELECTIVITY − command sets the selectivity (1-100) for a column. Based on the selectivity value we can image the value of the column.
SET DEFAULT − changes the default value of a column.
SET NULL − sets the column to allow NULL.
SET NOT NULL − sets the column to allow NOT NULL.

Example

在此示例中，我们将使用以下查询将 tutorials_tbl 表的列从 Title 重命名为 Tutorial_Title 。

In this example, we will rename the column of the table tutorials_tbl from Title to Tutorial_Title using the following query.

ALTER TABLE tutorials_tbl ALTER COLUMN title RENAME TO tutorial_title;

以上查询将产生以下输出。

The above query produces the following output.

(0) row(s) effected

以类似的方式，我们可以对 ALTER 命令执行不同的方案。

In a similar way, we can perform different scenarios with the ALTER command.

H2 Database - Drop

DROP 是从通用 SQL 语法中获取的命令。此命令用于从内存中删除数据库组件及其结构。本章将讨论使用 Drop 命令的不同情况。

DROP is a command taken from the generic SQL grammar. This command is used to delete a database component and its structure from the memory. There are different scenarios with the Drop command that we will discuss in this chapter.

Drop Table

Drop Table 是一个删除相应表及其结构的命令。

Drop Table is a command that deletes the respective table and its structure.

Syntax

以下是 Drop Table 命令的通用语法。

Following is the generic syntax of the Drop Table command.

DROP TABLE [ IF EXISTS ] tableName [,...] [ RESTRICT | CASCADE ]

如果我们正在使用 RESTRICT 并且该表具有从属视图，则该命令将失败。当我们使用 CASCADE 关键字时，所有从属视图都将被删除。

The command will fail if we are using RESTRICT and the table having dependent views exist. All dependent views are dropped, when we are using CASCADE keyword.

Example

在此示例中，我们将使用以下查询删除名为 test 的表。

In this example, we will drop a table named test using the following query.

DROP TABLE test;

以上查询将产生以下输出。

The above query produces the following output.

(6) row (s) effected

Drop Schema

Drop Schema 是一条从数据库服务器删除相应模式的命令。它不会在当前模式中运行。

Drop Schema is a command that drops a respective schema from the database server. It will not work from the current schema.

Syntax

DROP SCHEMA [ IF EXISTS ] schemaName

Example

在此示例中，我们将使用以下查询删除名为 test_schema 的模式。

In this example, we will drop a schema named test_schema using the following query.

DROP SCHEMA TEST_SCHEMA;

以上查询将产生以下输出。

The above query produces the following output.

(0) row(s) effected

Drop Sequence

Drop Sequence 是一条用于从表结构中删除序列的命令。

Drop Sequence is a command used to drop a sequence from the table structure.

Syntax

以下是 Drop Sequence 命令的通用语法。

Following is the generic syntax of the Drop Sequence command.

DROP SEQUENCE [ IF EXISTS ] sequenceName

此命令会提交此连接中的未决事务。

This command commits an open transaction in this connection.

Example

在此示例中，我们将删除名为 sequence_id 的序列。以下是该命令。

In this example, we will drop a sequence named sequence_id. Following is the command.

DROP SEQUENCE sequence_id;

以上命令会产生以下输出。

The above command produces the following output.

(0) row (s) effected

Drop View

Drop View 是一条用于删除现有视图的命令。如果使用了 CASCADE 子句，所有依赖的视图也会被删除。

Drop View is a command used to drop the existing view. All dependent views are dropped as well if the CASCADE clause is used.

Syntax

以下是 Drop View 命令的通用语法。

Following is the generic syntax of the Drop View command.

DROP VIEW [ IF EXISTS ] viewName [ RESTRICT | CASCADE ]

Example

在此示例中，我们将使用以下查询删除名为 sample_view 的视图。

In this example, we will drop a view named sample_view using the following query.

DROP VIEW sample_view;

以上查询将产生以下输出。

The above query produces the following output.

(0) row (s) effected

H2 Database - Truncate

TRUNCATE 是一条用于从表中删除数据的命令。与没有 WHERE 子句的 DELETE FROM 不同，此命令无法回滚。此命令会提交此连接中的打开事务。

TRUNCATE is a command used to delete the data from the table. Unlike DELETE FROM without WHERE clause, this command cannot be rolled back. This command commits an open transaction in this connection.

Syntax

以下是 truncate 命令的通用语法。

Following is the generic syntax of the truncate command.

TRUNCATE TABLE tableName

Example

在此示例中，我们将使用以下查询截断名为 test 的表。

In this example, we will truncate a table named test using the following query.

TRUNCATE TABLE test;

以上查询将产生以下输出。

The above query produces the following output.

(6) row (s) effected

H2 Database - Commit

COMMIT 是 SQL 语法中用于提交事务的命令。我们可以提交特定的事务或提交当前执行的事务。

COMMIT is a command from the SQL grammar used to commit the transaction. We can either commit the specific transaction or we can commit the currently executed transaction.

Syntax

COMMIT 命令有两种不同的语法。

There are two different syntaxes for COMMIT command.

以下是要提交当前事务的提交命令的通用语法。

Following is the generic syntax for the commit command to commit the current transaction.

COMMIT [ WORK ]

以下是要提交特定事务的提交命令的通用语法。

Following is the generic syntax for the commit command to commit the specific transaction.

COMMIT TRANSACTION transactionName

Example 1

在此示例中，让我们使用以下命令提交当前事务。

In this example, let us commit the current transaction using the following command.

COMMIT

以上命令会产生以下输出。

The above command produces the following output.

Committed successfully

Example 2

在此示例中，我们将使用以下命令提交名为 tx_test 的事务。

In this example, we will commit the transaction named tx_test using the following command.

COMMIT TRANSACTION tx_test;

以上命令会产生以下输出。

The above command produces the following output.

Committed successfully

H2 Database - Grant

Grant 是一条来自 SQL 语法的命令，用于向用户或角色授予表的权限。执行此命令需要管理员权限。此命令会提交此连接中的打开事务。

Grant is a command coming from the SQL grammar used to grant the rights to a table, to a user, or to a role. Admin rights are required to execute this command. This command commits an open transaction in this connection.

在本章中，我们将讨论 Grant 命令的不同场景。

In this chapter, we will discuss the different scenarios of Grant command.

Grant Right

Grant Right 是一条向用户或角色授予表的管理员权限的命令。

Grant Right is a command to provide admin rights to a table, to a user, or to a role.

Syntax

以下是 Grant 命令的通用语法。

Following is the generic syntax of the Grant command.

GRANT { SELECT | INSERT | UPDATE | DELETE | ALL } [,...] ON
{ { SCHEMA schemaName } | { tableName [,...] } }
TO { PUBLIC | userName | roleName }

Example

在此示例中，我们将使用以下命令将 test 表授予只读权限。

In this example, we will grant the test table as read-only using the following command.

GRANT SELECT ON TEST TO READONLY

以上命令会产生以下输出。

The above command produces the following output.

Grant successfully

Grant Alter Any Schema

Grant Alter Any Schema 是一条向特定用户授予模式修改权限的命令。

Grant Alter Any Schema is a command to grant schema altering rights to a respective user.

Syntax

以下是 Grant Alter Any Schema 命令的通用语法。

Following is the generic syntax of the Grant Alter Any Schema command.

GRANT ALTER ANY SCHEMA TO userName

Example

在此示例中，我们将向名为 test_user 的用户授予模式的修改权限。确保 test_user 存在。以下是授予修改权限的查询。

In this example, we will grant altering privileges of a schema to a user named test_user. Make sure that test_user exists. Following is the query to grant altering privileges.

GRANT ALTER ANY SCHEMA TO test_user;

以上查询将产生以下输出。

The above query produces the following output.

Granted successfully to test_user

H2 Database - Savepoint

SAVEPOINT 是一个用于临时保存事务的命令。最好在您的事务中保留保存点，因为它有助于在必要时将事务回滚到各自的保存点。

SAVEPOINT is a command used to temporarily save the transaction. It is better to maintain savepoints in your transaction as it is helpful to roll back the transaction to the respective Savepoint whenever necessary.

Syntax

以下是 Savepoint 命令的一般语法。

Following is the generic syntax of the Savepoint command.

SAVEPOINT savepointName

Example

在这个例子中，我们将使用以下命令创建一个名为 Half_Done 的保存点。

In this example, we will create a Savepoint named Half_Done using the following command.

SAVEPOINT Half_Done;

以上命令会产生以下输出。

The above command produces the following output.

Savepoint created

H2 Database - Rollback

回滚是 SQL 语法的命令，用于将事务回滚到保存点或前一个事务。通过使用此命令，我们可以回滚到特定保存点，也可以回滚到之前执行的事务。

ROLLBACK is a command from the SQL grammar used to roll back the transaction to a Savepoint or to the previous transaction. By using this command, we can either roll back to the specific Savepoint or we can roll back to the previous executed transaction.

Syntax

ROLLABCK 命令有两种不同的语法。

There are two different syntaxes for ROLLABCK command.

以下是对回滚命令的通用语法。

Following is the generic syntax for the rollback command.

ROLLBACK [ TO SAVEPOINT savepointName ]

以下是回滚命令的通用语法，适用于特定事务。

Following is the generic syntax of the Rollback command to the specific transaction.

ROLLBACK TRANSACTION transactionName

Example 1

在此示例中，我们将使用以下命令将当前事务回滚到名为 sp1_test 的保存点。

In this example, we will roll back the current transaction to a Savepoint named sp1_test using the following command.

ROLLBACK sp1_test;

以上命令会产生以下输出。

The above command produces the following output.

Rollback successfully

Example 2

在以下示例中，我们将使用给定的命令回滚名为 tx_test 的完整事务。

In the following example, we will roll back the complete transaction named tx_test using the given command.

ROLLBACK TRANSACTION tx_test;

以上命令会产生以下输出。

The above command produces the following output.

Rollback successfully

H2 Database - JDBC Connection

H2 是一个 JAVA 数据库。我们可以使用 JDBC 与此数据库进行交互。在本章中，我们将看到如何使用 JDBC 连接 H2 数据库以及 H2 数据库的 CRUD 操作。

H2 is a JAVA database. We can interact with this database by using JDBC. In this chapter, we will see how to create a JDBC connection with H2 database and the CRUD operations with the H2 database.

通常，有五步来创建 JDBC 连接。

Generally, there are five steps to create a JDBC connection.

Step 1 − 注册 JDBC 数据库驱动程序。

Step 1 − Registering the JDBC database driver.

Class.forName ("org.h2.Driver");

Step 2 − 打开连接。

Step 2 − Opening the connection.

Connection conn = DriverManager.getConnection ("jdbc:h2:~/test", "sa","");

Step 3 − 创建一个语句。

Step 3 − Creating a statement.

Statement st = conn.createStatement();

Step 4 − 执行语句并接收 Resultset。

Step 4 − Executing a statement and receiving Resultset.

Stmt.executeUpdate("sql statement");

Step 5 − 关闭连接。

Step 5 − Closing a connection.

conn.close();

在继续创建完整程序之前，我们需要将 h2-1.4.192.jar file 添加到 CLASSPATH。我们可以从文件夹中获得这个 jar C:\Program Files (x86)\H2\bin 。

Before moving on to create a full program, we need to add h2-1.4.192.jar file to CLASSPATH. We can get this jar from the folder C:\Program Files (x86)\H2\bin.

Create Table

在这个例子中，我们将编写一个创建表的程序。考虑一张名为 Registration 的表，它有以下字段。

In this example, we will write a program for create table. Consider a table named Registration having the following fields.

S.No

Column Name

Data Type

NOT NULL

Primary Key

Number

Yes

First

Varchar(255)

Last

Varchar(255)

Age

Number

以下是一个名为 H2jdbcCreateDemo 的示例程序。

Following is an example program named H2jdbcCreateDemo.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class H2jdbcCreateDemo {
   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "org.h2.Driver";
   static final String DB_URL = "jdbc:h2:~/test";

   //  Database credentials
   static final String USER = "sa";
   static final String PASS = "";

   public static void main(String[] args) {
      Connection conn = null;
      Statement stmt = null;
      try {
         // STEP 1: Register JDBC driver
         Class.forName(JDBC_DRIVER);

         //STEP 2: Open a connection
         System.out.println("Connecting to database...");
         conn = DriverManager.getConnection(DB_URL,USER,PASS);

         //STEP 3: Execute a query
         System.out.println("Creating table in given database...");
         stmt = conn.createStatement();
         String sql =  "CREATE TABLE   REGISTRATION " +
            "(id INTEGER not NULL, " +
            " first VARCHAR(255), " +
            " last VARCHAR(255), " +
            " age INTEGER, " +
            " PRIMARY KEY ( id ))";
         stmt.executeUpdate(sql);
         System.out.println("Created table in given database...");

         // STEP 4: Clean-up environment
         stmt.close();
         conn.close();
      } catch(SQLException se) {
         //Handle errors for JDBC
         se.printStackTrace();
      } catch(Exception e) {
         //Handle errors for Class.forName
         e.printStackTrace();
      } finally {
         //finally block used to close resources
         try{
            if(stmt!=null) stmt.close();
         } catch(SQLException se2) {
         } // nothing we can do
         try {
            if(conn!=null) conn.close();
         } catch(SQLException se){
            se.printStackTrace();
         } //end finally try
      } //end try
      System.out.println("Goodbye!");
   }
}

将上述程序保存到 H2jdbcCreateDemo.java 中。通过在命令提示符中执行以下命令编译并执行上述程序。

Save the above program into H2jdbcCreateDemo.java. Compile and execute the above program by executing the following commands in the command prompt.

\>javac H2jdbcCreateDemo.java
\>java H2jdbcCreateDemo

以上命令会产生以下输出。

The above command produces the following output.

Connecting to database...
Creating table in given database...
Created table in given database...
Goodbye!

在此执行后，我们可以使用 H2 SQL 界面检查创建的表。

After this execution, we can check the table created using the H2 SQL interface.

Insert Records

在这个例子中，我们将编写一个插入记录的程序。让我们将以下记录插入到注册表中。

In this example, we will write a program for inserting records. Let us insert the following records into the table Registration.

ID	First	Last	Age
100	Zara	Ali	18
101	Mahnaz	Fatma	25
102	Zaid	Khan	30
103	Sumit	Mital	28

以下是一个名为 H2jdbcInsertDemo 的示例程序。

Following is an example program named H2jdbcInsertDemo.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class H2jdbcInsertDemo {
   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "org.h2.Driver";
   static final String DB_URL = "jdbc:h2:~/test";

   //  Database credentials
   static final String USER = "sa";
   static final String PASS = "";

   public static void main(String[] args) {
      Connection conn = null;
      Statement stmt = null;
      try{
         // STEP 1: Register JDBC driver
         Class.forName(JDBC_DRIVER);

         // STEP 2: Open a connection
         System.out.println("Connecting to a selected database...");
         conn = DriverManager.getConnection(DB_URL,USER,PASS);
         System.out.println("Connected database successfully...");

         // STEP 3: Execute a query
         stmt = conn.createStatement();
         String sql = "INSERT INTO Registration " + "VALUES (100, 'Zara', 'Ali', 18)";

         stmt.executeUpdate(sql);
         sql = "INSERT INTO Registration " + "VALUES (101, 'Mahnaz', 'Fatma', 25)";

         stmt.executeUpdate(sql);
         sql = "INSERT INTO Registration " + "VALUES (102, 'Zaid', 'Khan', 30)";

         stmt.executeUpdate(sql);
         sql = "INSERT INTO Registration " + "VALUES(103, 'Sumit', 'Mittal', 28)";

         stmt.executeUpdate(sql);
         System.out.println("Inserted records into the table...");

         // STEP 4: Clean-up environment
         stmt.close();
         conn.close();
      } catch(SQLException se) {
         // Handle errors for JDBC
         se.printStackTrace();
      } catch(Exception e) {
         // Handle errors for Class.forName
         e.printStackTrace();
      } finally {
         // finally block used to close resources
         try {
            if(stmt!=null) stmt.close();
         } catch(SQLException se2) {
         } // nothing we can do
         try {
            if(conn!=null) conn.close();
         } catch(SQLException se) {
            se.printStackTrace();
         } // end finally try
      } // end try
      System.out.println("Goodbye!");
   }
}

将上述程序保存到 H2jdbcInsertDemo.java 中。通过在命令提示符中执行以下命令编译并执行上述程序。

Save the above program into H2jdbcInsertDemo.java. Compile and execute the above program by executing the following commands in the command prompt.

\>javac H2jdbcInsertDemo.java
\>java H2jdbcInsertDemo

以上命令会产生以下输出。

The above command produces the following output.

Connecting to a selected database...
Connected database successfully...
Inserted records into the table...
Goodbye!

Read Record

在这个例子中，我们将编写一个读取记录的程序。让我们尝试从表 Registration 中读取所有记录。

In this example, we will write a program for reading records. Let us try to read all records from the table Registration.

以下是一个名为 H2jdbcRecordDemo 的示例程序。

Following is an example program named H2jdbcRecordDemo.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class H2jdbcReadDemo {
   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "org.h2.Driver";
   static final String DB_URL = "jdbc:h2:~/test";

   //  Database credentials
   static final String USER = "sa";
   static final String PASS = "";

   public static void main(String[] args) {
      Connection conn = null;
      Statement stmt = null;
      try {
         // STEP 1: Register JDBC driver
         Class.forName(JDBC_DRIVER);

         // STEP 2: Open a connection
         System.out.println("Connecting to database...");
         conn = DriverManager.getConnection(DB_URL,USER,PASS);

         // STEP 3: Execute a query
         System.out.println("Connected database successfully...");
         stmt = conn.createStatement();
         String sql = "SELECT id, first, last, age FROM Registration";
         ResultSet rs = stmt.executeQuery(sql);

         // STEP 4: Extract data from result set
         while(rs.next()) {
            // Retrieve by column name
            int id  = rs.getInt("id");
            int age = rs.getInt("age");
            String first = rs.getString("first");
            String last = rs.getString("last");

            // Display values
            System.out.print("ID: " + id);
            System.out.print(", Age: " + age);
            System.out.print(", First: " + first);
            System.out.println(", Last: " + last);
         }
         // STEP 5: Clean-up environment
         rs.close();
      } catch(SQLException se) {
         // Handle errors for JDBC
         se.printStackTrace();
      } catch(Exception e) {
         // Handle errors for Class.forName
         e.printStackTrace();
      } finally {
         // finally block used to close resources
         try {
            if(stmt!=null) stmt.close();
         } catch(SQLException se2) {
         } // nothing we can do
         try {
            if(conn!=null) conn.close();
         } catch(SQLException se) {
            se.printStackTrace();
         } // end finally try
      } // end try
      System.out.println("Goodbye!");
   }
}

将以上程序保存到 H2jdbcReadDemo.java 中。在命令提示符中执行以下命令来编译和执行上述程序。

Save the above program into H2jdbcReadDemo.java. Compile and execute the above program by executing the following commands in the command prompt.

\>javac H2jdbcReadDemo.java
\>java H2jdbcReadDemo

以上命令会产生以下输出。

The above command produces the following output.

Connecting to a selected database...
Connected database successfully...
ID: 100, Age: 18, First: Zara, Last: Ali
ID: 101, Age: 25, First: Mahnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
Goodbye!

Update Records

在这个示例中，我们将编写一个程序来更新记录。让我们尝试从表 Registration 中读取所有记录。

In this example, we will write a program to update records. Let us try to read all records from the table Registration.

以下是一个名为 H2jdbcUpdateDemo 的示例程序。

Following is an example program named H2jdbcUpdateDemo.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class H2jdbcUpdateDemo {
   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "org.h2.Driver";
   static final String DB_URL = "jdbc:h2:~/test";

   // Database credentials
   static final String USER = "sa";
   static final String PASS = "";

   public static void main(String[] args) {
      Connection conn = null;
      Statement stmt = null;
      try {
         // STEP 1: Register JDBC driver
         Class.forName(JDBC_DRIVER);

         // STEP 2: Open a connection
         System.out.println("Connecting to a database...");
         conn = DriverManager.getConnection(DB_URL,USER,PASS);

         // STEP 3: Execute a query
         System.out.println("Connected database successfully...");
         stmt = conn.createStatement();
         String sql = "UPDATE Registration " + "SET age = 30 WHERE id in (100, 101)";
         stmt.executeUpdate(sql);

         // Now you can extract all the records
         // to see the updated records
         sql = "SELECT id, first, last, age FROM Registration";
         ResultSet rs = stmt.executeQuery(sql);

         while(rs.next()){
            // Retrieve by column name
            int id  = rs.getInt("id");
            int age = rs.getInt("age");
            String first = rs.getString("first");
            String last = rs.getString("last");

            // Display values
            System.out.print("ID: " + id);
            System.out.print(", Age: " + age);
            System.out.print(", First: " + first);
            System.out.println(", Last: " + last);
         }
         rs.close();
      } catch(SQLException se) {
         // Handle errors for JDBC
         se.printStackTrace();
      } catch(Exception e) {
         // Handle errors for Class.forName
         e.printStackTrace();
      } finally {
         // finally block used to close resources
         try {
            if(stmt!=null) stmt.close();
         } catch(SQLException se2) {
         } // nothing we can do
         try {
            if(conn!=null) conn.close();
         } catch(SQLException se) {
            se.printStackTrace();
         } // end finally try
      } // end try
      System.out.println("Goodbye!");
   }
}

将以上程序保存到 H2jdbcUpdateDemo.java 中。在命令提示符中执行以下命令来编译和执行上述程序。

Save the above program into H2jdbcUpdateDemo.java. Compile and execute the above program by executing the following commands in the command prompt.

\>javac H2jdbcUpdateDemo.java
\>java H2jdbcUpdateDemo

以上命令会产生以下输出。

The above command produces the following output.

Connecting to a selected database...
Connected database successfully...
ID: 100, Age: 30, First: Zara, Last: Ali
ID: 101, Age: 30, First: Mahnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
Goodbye!

Delete Records

在这个示例中，我们将编写一个程序来删除记录。让我们尝试从表 Registration 中读取所有记录。

In this example, we will write a program to delete records. Let us try to read all records from the table Registration.

以下是一个名为 H2jdbcDeleteDemo 的示例程序。

Following is an example program named H2jdbcDeleteDemo.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class H2jdbcDeleteDemo {
   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "org.h2.Driver";
   static final String DB_URL = "jdbc:h2:~/test";

   // Database credentials
   static final String USER = "sa";
   static final String PASS = "";

   public static void main(String[] args) {
      Connection conn = null;
      Statement stmt = null;
      try {
         // STEP 1: Register JDBC driver
         Class.forName(JDBC_DRIVER);

         // STEP 2: Open a connection
         System.out.println("Connecting to database...");
         conn = DriverManager.getConnection(DB_URL,USER,PASS);

         // STEP 3: Execute a query
         System.out.println("Creating table in given database...");
         stmt = conn.createStatement();
         String sql = "DELETE FROM Registration " + "WHERE id = 101";
         stmt.executeUpdate(sql);

         // Now you can extract all the records
         // to see the remaining records
         sql = "SELECT id, first, last, age FROM Registration";
         ResultSet rs = stmt.executeQuery(sql);

         while(rs.next()){
            // Retrieve by column name
            int id  = rs.getInt("id");
            int age = rs.getInt("age");
            String first = rs.getString("first");
            String last = rs.getString("last");

            // Display values
            System.out.print("ID: " + id);
            System.out.print(", Age: " + age);
            System.out.print(", First: " + first);
            System.out.println(", Last: " + last);
         }
         rs.close();
      } catch(SQLException se) {
         // Handle errors for JDBC
         se.printStackTrace();
      } catch(Exception e) {
         // Handle errors for Class.forName
         e.printStackTrace();
      } finally {
         // finally block used to close resources
         try {
            if(stmt!=null) stmt.close();
         } catch(SQLException se2) {
         } // nothing we can do
         try {
            if(conn!=null) conn.close();
         } catch(SQLException se) {
            se.printStackTrace();
         } // end finally try
      } // end try
      System.out.println("Goodbye!");
   }
}

将以上程序保存到 H2jdbcDeleteDemo.java 中。在命令提示符中执行以下命令来编译和执行上述程序。

Save the above program into H2jdbcDeleteDemo.java. Compile and execute the above program by executing the following commands in the command prompt.

\>javac H2jdbcDeleteDemo.java
\>java H2jdbcDeleteDemo

以上命令会产生以下输出。

The above command produces the following output.

Connecting to a selected database...
Connected database successfully...
ID: 100, Age: 30, First: Zara, Last: Ali
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
Goodbye!