Getting Started With Apache Derby – 开始使用Apache Derby

$ ls 
bin  demo  docs  index.html  javadoc  KEYS  lib  LICENSE  NOTICE  RELEASE-NOTES.html  test

export DERBY_HOME=/opt/derby-db

export PATH="$DERBY_HOME/bin:$PATH"

$ ls
derbyclient.jar     derbynet.jar            derbyshared.jar
derby.jar           derbyoptionaltools.jar  derbytools.jar
derbyrun.jar        derby.war               derbyLocale_XX.jar ...

$ $DERBY_HOME/bin/ij
ij version 10.13
ij> 

$ java -jar $DERBY_HOME/lib/derbyrun.jar ij
ij version 10.13
ij> 

• By specifying a properties file using the -p property-file option on the command line
• By using the -D option on the command line

We can create a properties file, add all properties that are needed, and then run the following command:

我们可以创建一个属性文件，添加所有需要的属性，然后运行以下命令。

$ java -jar derbyrun.jar ij -p file-name.properties

If the file-name.properties file doesn’t exist in the current directory, we’ll get a java.io.FileNotFoundException.

如果file-name.properties文件在当前目录中不存在，我们会得到一个 java.io.FileNotFoundException.。

For example, suppose we want to create a connection to a specific database with a specific name. We can do it with the ij.database property:

例如，假设我们想创建一个连接到一个有特定名称的数据库。我们可以用ij.database属性来做。

$ java -jar -Dij.protocol=jdbc:derby: -Dij.database=baeldung derbyrun.jar ij
ij version 10.13
CONNECTION0* - 	jdbc:derby:baeldung
* = current connection

4.2. DBLook

4.2 DBLook

The dblook tool provides the DDL (Data Definition Language) of the database. For example, we can write the DDL of the baeldung database to the console:

dblook工具提供数据库的DDL（数据定义语言）。例如，我们可以把baeldung数据库的DDL写到控制台。

$ $DERBY_HOME/bin/dblook -d jdbc:derby:baeldung
-- Timestamp: 2021-08-23 01:29:48.529
-- Source database is: baeldung
-- Connection URL is: jdbc:derby:baeldung
-- appendLogs: false

-- ----------------------------------------------
-- DDL Statements for schemas
-- ----------------------------------------------

CREATE SCHEMA "basic_users";

-- ----------------------------------------------
-- DDL Statements for tables
-- ----------------------------------------------

CREATE TABLE "APP"."authors" ("id" INTEGER NOT NULL, "first_name" VARCHAR(255) , "last_name" VARCHAR(255));

-- ----------------------------------------------
-- DDL Statements for keys
-- ----------------------------------------------

-- PRIMARY/UNIQUE
ALTER TABLE "APP"."authors" ADD CONSTRAINT "SQL0000000000-582f8014-017b-6e26-ada1-00000644e000" PRIMARY KEY ("id");

The dblook has other options that are described below.

dblook还有其他选项，如下所述。

We can use -o for writing the DDL to a file like baeldung.sql:

我们可以使用-o将DDL写入baeldung.sql等文件。

$ sudo $DERBY_HOME/bin/dblook -d jdbc:derby:baeldung -o baeldung.sql

We also can specify the schema with -z and the table with -t:

我们还可以用-z指定模式，用-t:指定表。

$ sudo $DERBY_HOME/bin/dblook -d jdbc:derby:baeldung -o baeldung.sql -z SCHEMA_NAME -t "TABLE_NAME"

4.3. Sysinfo

4.3. Sysinfo

The Apache Derby sysinfo tool displays information regarding our Java environment and the Derby version. In addition, the sysinfo tool displays system information on the console:

Apache Derby sysinfo工具显示关于我们的Java环境和Derby版本的信息。此外，sysinfo工具在控制台显示系统信息。

$ java -jar $DERBY_HOME/lib/derbyrun.jar sysinfo

------------------ Java Information ------------------
Java Version:    11.0.11
Java Vendor:     Ubuntu
Java home:       /usr/lib/jvm/java-11-openjdk-amd64
Java classpath:  /opt/derby-db/lib/derbyrun.jar
OS name:         Linux
OS architecture: amd64
OS version:      5.11.0-27-generic
Java user name:  arash
Java user home:  /home/arash
Java user dir:   /opt/derby-db
java.specification.name: Java Platform API Specification
java.specification.version: 11
java.runtime.version: 11.0.11+9-Ubuntu-0ubuntu2.20.04
--------- Derby Information --------
[/opt/derby-db/lib/derby.jar] 10.13.1.1 - (1873585)
[/opt/derby-db/lib/derbytools.jar] 10.13.1.1 - (1873585)
[/opt/derby-db/lib/derbynet.jar] 10.13.1.1 - (1873585)
[/opt/derby-db/lib/derbyclient.jar] 10.13.1.1 - (1873585)
[/opt/derby-db/lib/derbyshared.jar] 10.13.1.1 - (1873585)
[/opt/derby-db/lib/derbyoptionaltools.jar] 10.13.1.1 - (1873585)

5. SQL Statements in Apache Derby

5.Apache Derby中的SQL语句

Here we’ll examine some of the essential SQL statements that Apache Derby provided. We’ll look at each statement’s syntax with an example.

这里我们将检查Apache Derby提供的一些基本SQL声明。我们将通过一个例子来看看每个语句的语法。

5.1. Create Database

5.1.创建数据库

We can create a new database with the connect command and create=true attribute in our connection string:

我们可以用connect命令和create=true属性在我们的连接字符串中创建一个新的数据库。

ij> connect 'jdbc:derby:databaseName;create=true';

5.2. Connect to Database

5.2.连接到数据库

IJ automatically loads the appropriate driver based on the URL syntax when we interact interactively with a Derby database:

当我们与Derby数据库进行交互时，IJ会根据URL语法自动加载相应的驱动程序。

ij> connect 'jdbc:derby:baeldung' user 'user1' password 'pass123';

5.3. Create Schema

5.3.创建模式

The CREATE SCHEMA statement defines a schema, which is a way to identify a specific namespace for a set of objects:

CREATE SCHEMA语句定义了一个模式，这是一种为一组对象确定特定命名空间的方式。

CREATE SCHEMA schema_name AUTHORIZATION userName;

Schema names cannot exceed 128 characters and must be unique within the database also. In addition, schema names cannot begin with the prefix SYS.

模式名称不能超过128个字符，而且在数据库中必须是唯一的。此外，模式名称不能以前缀SYS开头。

Here is an example of a schema named baeldung_authors:

下面是一个名为baeldung_authors的模式的例子。

ij> CREATE SCHEMA baeldung_authors;

In addition, we can create a schema for baeldung_authors that just specific users with ID arash can access:

此外，我们可以为baeldung_authors创建一个模式，只有ID为arash的特定用户可以访问：

ij> CREATE SCHEMA baeldung_authors AUTHORIZATION arash;

5.4. Drop Schema

5.4.辍学时间表

We can drop a schema by using the DROP SCHEMA statement, and also, the target schema must be empty for DROP SCHEMA to succeed. We cannot drop the APP schema (the default user schema) or the SYS schema:

我们可以通过使用的DROP SCHEMA语句来放弃模式。而且，目标模式必须是空的，以便DROP SCHEMA能够成功。我们不能删除APP模式（默认的用户模式）或的SYS模式：

DROP SCHEMA schema_name RESTRICT;

By using the RESTRICT keyword, we can enforce the rule that there cannot be any objects defined in a specified schema for the schema to be deleted from the database.

通过使用的RESTRICT关键字。 我们 可以执行规则，有的规则。changedText-0-2-17″>不能被定义在a指定模式的任何对象，以便从数据库删除该模式。

Let’s see an example to drop the schema:

让我们看一个放弃模式的例子。

ij> DROP SCHEMA baeldung_authors RESTRICT;

5.5. Create Table

5.5.创建表

We can use the CREATE TABLE statement to create a table, which contains columns and constraints:

我们可以使用CREATE TABLE语句来创建一个表。 其中包含列和约束。

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

Let’s see an example:

让我们看一个例子。

ij> CREATE TABLE posts(post_id INT NOT NULL, publish_date DATE NOT NULL,
    view_number INT DEFAULT 0, PRIMARY KEY (post_id));

If we do not specify a default value, NULL is inserted in the column as the default value. 

17″>做不指定一个默认值。NULL是插入的在中。17″>的列作为的列。changedText-0-2-17″>thedefault value。

Other SQL statements like INSERT, UPDATE, DELETE, and SELECT for CRUD actions are similar to standard SQL.

其他SQL语句，如用于CRUD操作的INSERT、UPDATE、DELETE和SELECT，与标准SQL类似。

6. JDBC Programming With Apache Derby

6.使用Apache Derby的JDBC编程

Here we’re going to learn how to create a Java application that uses Apache Derby as a database engine.

在这里，我们将学习如何创建一个使用Apache Derby作为数据库引擎的Java应用程序。

6.1. Maven Dependencies

6.1.Maven的依赖性

There are two Derby drivers in Maven: derby and derbynet. The former is used for embedded applications, while the latter is used for client/server applications.

有两个德比司机在司机 在 Maven。derby和derbynet。前者是用于嵌入式应用。而后者用于客户/服务器应用。

Let’s add a Maven dependency for derby:

让我们为derby添加一个Maven依赖项。

<dependency>
    <groupId>org.apache.derby</groupId>
    <artifactId>derby</artifactId>
    <version>10.13.1.1</version>
</dependency>

Also, we add Maven dependency for derbyclient:

另外，我们为derbyclient添加Maven依赖。

<dependency>
    <groupId>org.apache.derby</groupId>
    <artifactId>derbyclient</artifactId>
    <version>10.13.1.1</version>
</dependency>

6.2. JDBC URL Connection

6.2.JDBC URL连接

We can connect to the database with different connection string parameters for client/server and embedded applications.

我们可以通过不同的连接到数据库。page-changedText-0-2-17″>连接字符串参数用于客户/服务器和嵌入式应用程序。

In the below syntax, we can use it for embedded mode:

在下面的语法中，我们可以将其用于嵌入式模式。

jdbc:derby:[subsubprotocol:][databaseName][;attribute=value]

And also, we can use the syntax below for client/server mode:

而且，我们也可以使用下面的语法来处理客户/服务器模式。

jdbc:derby://server[:port]/databaseName[;attribute=value]

The database URL doesn’t contain the hostname and port number in embedded mode. For example:

在嵌入式模式下，数据库的URL不包含主机名和端口号。比如说。

String urlConnection = "jdbc:derby:baeldung;create=true";

6.3. Use Apache Derby in Embedded Mode

6.3.在嵌入式模式下使用Apache Derby

Let’s connect to an Apache Derby database in embedded mode, create it in the current directory if it doesn’t already exist, create a table, and insert rows into the table using SQL statements:

让我们以嵌入式模式连接到Apache Derby数据库，如果它不存在，就在当前目录下创建它，创建一个表，并使用SQL语句向表中插入行。

String urlConnection = "jdbc:derby:baeldung;create=true";
Connection con = DriverManager.getConnection(urlConnection);
Statement statement = con.createStatement();
String sql = "CREATE TABLE authors (id INT PRIMARY KEY,first_name VARCHAR(255),last_name VARCHAR(255))";
statement.execute(sql);
sql = "INSERT INTO authors VALUES (1, 'arash','ariani')";
statement.execute(sql);

6.4. Use Apache Derby in Client/Server Mode

6.4.在客户/服务器模式下使用Apache Derby

First, we run the command below to start up the Apache Derby in client/server mode:

首先，我们运行下面的命令，在客户/服务器模式下启动Apache Derby。

$ java -jar $DERBY_HOME/lib/derbyrun.jar server start 
Sat Aug 28 20:47:58 IRDT 2021 : Security manager installed using the Basic server security policy.
Sat Aug 28 20:47:58 IRDT 2021 : Apache Derby Network Server - 10.13.1.1 -
(1873585) started and ready to accept connections on port 1527

We use the JDBC API to connect to an Apache Derby server on localhost and select all entries from the authors table in the baeldung database:

我们使用JDBC API连接到本地主机上的Apache Derby服务器，并从baeldung数据库中的authors表中选择所有条目。

String urlConnection = "jdbc:derby://localhost:1527/baeldung";
   try (Connection con = DriverManager.getConnection(urlConnection)) {
       Statement statement = con.createStatement();
       String sql = "SELECT * FROM authors";
       ResultSet result = statement.executeQuery(sql);
         while (result.next()) {
           // We can print or use ResultSet here
         }
   } catch (SQLException ex) {
       ex.printStackTrace();
 }

7. Apache Derby With Spring Boot

7.Apache Derby与Spring Boot

This section will not explain how to create an application based on Spring Boot in detail, but only the settings relating to interacting with the Apache Derby database.

本节将不详细解释如何创建基于Spring Boot的应用程序，而只解释与Apache Derby数据库交互有关的设置。

7.1. Dependencies for Apache Derby

7.1.Apache Derby的依赖性

We just have to add the Spring Data and Apache Derby dependencies from Spring Boot to the project to incorporate Apache Derby into it:

我们只需将Spring Boot中的Spring Data和Apache Derby依赖项添加到该项目中，以便将Apache Derby纳入其中。

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.apache.derby</groupId>
    <artifactId>derbyclient</artifactId>
    <version>10.13.1.1</version>
</dependency>

7.2. Spring Boot Configuration

7.2.Spring Boot配置

We can use Derby as our persistent database by adding these application properties:

我们可以通过添加这些应用属性来使用德比作为我们的持久性数据库。

spring.datasource.url=jdbc:derby://localhost:1527/baeldung 
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.DerbyTenSevenDialect 
spring.jpa.hibernate.ddl-auto=update
spring.datasource.driver-class-name=org.apache.derby.jdbc.ClientDriver

8. Conclusion

8.结语

In this tutorial, we’ve looked into installing and configuring the Apache Derby. After that, we had an overview of tools and some of the most important SQL statements. We covered JDBC programming with snippet codes in Java and finally learned how to configure Spring Boot to use Apache Derby as a persistent database.

在本教程中，我们已经研究了安装和配置Apache Derby。之后，我们对工具和一些最重要的SQL语句进行了概述。我们用Java中的片段代码介绍了JDBC编程，最后学习了如何配置Spring Boot来使用Apache Derby作为持久性数据库。

As usual, the examples used in this article are available over on GitHub.

像往常一样，本文中使用的例子可以在GitHub上找到。