ZetCode-数据库教程-三-
ZetCode 数据库教程（三）
原文：ZetCode
PostgreSQL PHP 教程
这是 PostgreSQL 数据库的 PHP 教程。 它涵盖了使用 PHP 进行 PostgreSQL 编程的基础。 这些示例是在 Linux 上创建并测试的。
目录
PostgreSQL & PHP
PostgreSQL 是一个功能强大的开源对象关系数据库系统。 它是一个多用户数据库管理系统。 它可以在包括 Linux，FreeBSD，Solaris，Microsoft Windows 和 Mac OS X 在内的多个平台上运行。PostgreSQL 由 PostgreSQL 全球开发小组开发。
PHP 是一种专为 Web 开发设计的脚本语言。 它用于产生动态网页。 当前，PHP 是使用最广泛的编程语言之一。 它的大部分语法是从 C，Java 和 Perl 借来的，它们具有一些特定于 PHP 的独特功能。 PHP 可以嵌入 HTML 代码中，并且通常在 Web 服务器上运行。 PHP 支持面向对象和过程编程风格。
相关教程
如果您需要重新了解 PHP 语言，可以在 ZetCode 上找到完整的 PHP 教程。 中介绍了 PHP 7 的新语言功能。 您还可以在 ZetCode 上找到 MySQL PHP 教程， SQLite PHP 教程和 MongoDB PHP 教程。
PostgreSQL PHP 编程简介
在 PostgreSQL PHP 教程的第一章中，我们将提供必要的定义。 我们将展示如何安装 PostgreSQL 数据库和所需的包。 这些示例将使用 PHP CLI 在命令行上运行。
为了运行示例，我们需要安装 PHP 语言（以 PHP CLI 的形式）和 PostgreSQL 数据库。 我们还需要php5-psql包。
关于 PostgreSQL 数据库
PostgreSQL 是一个功能强大的开源对象关系数据库系统。 它是一个多用户数据库管理系统。 它可以在多种平台上运行，包括 Linux，FreeBSD，Solaris，Microsoft Windows 和 Mac OS。 PostgreSQL 由 PostgreSQL 全球开发小组开发。
设置 PostgreSQL
我们必须安装 PostgreSQL 数据库。
$ sudo apt-get install postgresql
在基于 Debian 的系统上，我们可以使用上述命令安装 PostgreSQL 数据库。
$ sudo update-rc.d -f postgresql remove
Removing any system startup links for /etc/init.d/postgresql ...
/etc/rc0.d/K21postgresql
/etc/rc1.d/K21postgresql
/etc/rc2.d/S19postgresql
/etc/rc3.d/S19postgresql
/etc/rc4.d/S19postgresql
/etc/rc5.d/S19postgresql
/etc/rc6.d/K21postgresql
如果我们从包中安装 PostgreSQL 数据库，它将自动添加到操作系统的启动脚本中。 如果我们仅学习使用数据库，则不必在每次引导系统时都启动数据库。 上面的命令删除 PostgreSQL 数据库的所有系统启动链接。
$ /etc/init.d/postgresql status
Running clusters: 9.1/main
$ service postgresql status
Running clusters: 9.1/main
我们检查 PostgreSQL 服务器是否正在运行。 如果没有，我们需要启动服务器。
$ sudo service postgresql start
* Starting PostgreSQL 9.1 database server [ OK ]
在 Ubuntu Linux 上，我们可以使用service postgresql start命令启动服务器。
$ sudo service postgresql stop
[sudo] password for janbodnar:
* Stopping PostgreSQL 9.1 database server [ OK ]
我们使用service postgresql stop命令停止 PostgreSQL 服务器。
$ sudo -u postgres createuser janbodnar
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
我们在 PostgreSQL 系统中创建了一个新角色。 我们允许它具有创建新数据库的能力。 角色是数据库世界中的用户。 角色与操作系统用户是分开的。 我们创建了一个没有-W选项的新用户，例如我们尚未指定密码。 这使我们能够使用该用户连接到数据库，而无需密码验证。 请注意，这仅适用于本地主机。
$ sudo -u postgres createdb testdb -O janbodnar
createdb命令使用所有者 janbodnar 创建一个新的 PostgreSQL 数据库。
PHP CLI
已知 PHP 语言可在 Web 服务器上运行。 但是它也可以在命令行上使用。 PHP 命令行界面（PHP CLI）是一个库，使程序员可以在命令行上使用 PHP。 使用 PHP CLI，我们可以使用 PHP-GTK 构建 GUI 应用，也可以创建简单的测试脚本。 在本教程中，我们将使用命令行 PHP 解释器连接到 PostgreSQL 数据库。
$ sudo apt-get install php5-cli
我们在 Linux 系统上安装 PHP CLI 模块。
php5-pgsql 包
php5-pgsql 是一个使用 PHP 语言与 PostgreSQL 数据库一起使用的包。 在其他系统上，包名称可能不同。
$ sudo apt-get install php5-pgsql
我们启动以上命令来安装包。
处理错误
我们对处理 PHP 中的错误有一个简短的评论。 PHP 具有对错误报告的内置支持。 细节可以在php.ini文件中控制。 请注意，PHP CLI 版本具有单独的 INI 文件。 它位于我们系统上的/etc/php5/cli/php.ini中。
display_errors指令控制是否显示内置错误消息。 在开发环境中，将显示这些错误消息。 在生产中，它们被抑制了。 没有理由向用户显示这些技术消息。 另外，这是潜在的安全风险。
通常，我们应该将更具体的错误消息记录到日志文件中。 log_errors指令控制是否记录错误。 error_log指定应该记录脚本错误的文件的名称。 如果未设置，则默认为 PHP CLI 的stderr。
pg_last_error()函数获取连接的最后一条错误消息字符串。 它与内置错误报告中生成的错误消息相同。
在本教程的示例中，我们不使用pg_last_error()函数，因为它复制了内置错误消息。 我们有以下设置：
...
display_errors = On
...
log_errors = On
; Our own custom based log file
error_log = /home/janbodnar/.phpcli_log
...
我们显示内置错误； 它们显示在命令行上。 错误消息还将记录到指定的日志文件中。 如果我们不想在控制台上显示错误消息，只需关闭display_errors指令即可。
$rs = pg_query($con, $query) or die("Cannot execute query: $query\n");
在脚本中，我们使用die()函数显示一条简单的错误消息，该消息易于理解。 更具体的详细信息将保存到日志文件中。 die()函数也会终止脚本。
版本
在第一个代码示例中，我们将获取 PostgreSQL 数据库的版本。
<?php
$host = "localhost";
$user = "user12";
$pass = "34klq*";
$db = "testdb";
$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
or die ("Could not connect to server\n");
$query = "SELECT VERSION()";
$rs = pg_query($con, $query) or die("Cannot execute query: $query\n");
$row = pg_fetch_row($rs);
echo $row[0] . "\n";
pg_close($con);
?>
在上面的 PHP 脚本中，我们连接到先前创建的testdb数据库。 我们执行一条 SQL 语句，该语句返回 PostgreSQL 数据库的版本。
$host = "localhost";
$user = "user12";
$pass = "34klq*";
$db = "testdb";
这些是连接字符串变量。 要创建到 PostgreSQL 数据库的连接，我们必须提供主机名，用户名和密码以及数据库名。
$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
or die ("Could not connect to server\n");
我们连接到数据库服务器。 如果无法创建连接，则die()函数将终止脚本并将错误消息打印到控制台。 pg_connect()函数返回连接资源，该资源将在以后与其他模块功能一起使用。
$query = "SELECT VERSION()";
该 SQL 语句选择 PostgreSQL 数据库的版本。
$rs = pg_query($con, $query) or die("Cannot execute query: $query\n");
使用pg_query()函数执行查询。
$row = pg_fetch_row($rs);
我们从返回的结果中获取数据。
echo $row[0] . "\n";
我们将检索到的数据打印到控制台。 数据以 PHP 数组的形式返回。 数组的第一个元素是我们要查找的字符串。
pg_close($con);
使用pg_close()函数关闭与数据库的连接。
$ php version.php
PostgreSQL 9.1.3 on i686-pc-linux-gnu, compiled by gcc-4.6.real
(Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 32-bit
运行version.php脚本。
插入数据
我们将创建一个cars表并在其中插入几行。
<?php
$host = "localhost";
$user = "user12";
$pass = "34klq*";
$db = "testdb";
$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
or die ("Could not connect to server\n");
$query = "DROP TABLE IF EXISTS cars";
pg_query($con, $query) or die("Cannot execute query: $query\n");
$query = "CREATE TABLE cars(id INTEGER PRIMARY KEY, mame VARCHAR(25), price INT)";
pg_query($con, $query) or die("Cannot execute query: $query\n");
$query = "INSERT INTO cars VALUES(1,'Audi',52642)";
pg_query($con, $query) or die("Cannot execute query: $query\n");
$query = "INSERT INTO cars VALUES(2,'Mercedes',57127)";
pg_query($con, $query) or die("Cannot execute query: $query\n");
$query = "INSERT INTO cars VALUES(3,'Skoda',9000)";
pg_query($con, $query) or die("Cannot execute query: $query\n");
$query = "INSERT INTO cars VALUES(4,'Volvo',29000)";
pg_query($con, $query) or die("Cannot execute query: $query\n");
$query = "INSERT INTO cars VALUES(5,'Bentley',350000)";
pg_query($con, $query) or die("Cannot execute query: $query\n");
$query = "INSERT INTO cars VALUES(6,'Citroen',21000)";
pg_query($con, $query) or die("Cannot execute query: $query\n");
$query = "INSERT INTO cars VALUES(7,'Hummer',41400)";
pg_query($con, $query) or die("Cannot execute query: $query\n");
$query = "INSERT INTO cars VALUES(8,'Volkswagen',21606)";
pg_query($con, $query) or die("Cannot execute query: $query\n");
pg_close($con);
?>
上面的脚本创建一个Cars表，并将 8 行插入到该表中。
$query = "DROP TABLE IF EXISTS cars";
pg_query($con, $query) or die("Cannot execute query: $query\n");
如果汽车表已经存在，我们将其删除。 pg_query()函数在指定的数据库连接上执行给定查询。
$query = "CREATE TABLE cars(id INTEGER PRIMARY KEY, mame VARCHAR(25), price INT)";
该 SQL 语句创建一个新的 cars 表。 该表有三列。
$query = "INSERT INTO cars VALUES(1,'Audi',52642)";
pg_query($con, $query) or die("Cannot execute query: $query\n");
$query = "INSERT INTO cars VALUES(2,'Mercedes',57127)";
pg_query($con, $query) or die("Cannot execute query: $query\n");
我们要把两辆车插入表。
pg_close($con);
与数据库的连接已关闭。
$ psql testdb
psql (9.1.3)
Type "help" for help.
testdb=# SELECT * FROM cars;
id | name | price
----+------------+--------
1 | Audi | 52642
2 | Mercedes | 57127
3 | Skoda | 9000
4 | Volvo | 29000
5 | Bentley | 350000
6 | Citroen | 21000
7 | Hummer | 41400
8 | Volkswagen | 21606
9 | BMW | 36000
(9 rows)
我们使用psql工具验证写入的数据。
预备语句
现在，我们将以预备语句来关注自己。 在编写预备语句时，我们使用占位符，而不是直接将值写入语句中。 预准备的语句可提高安全性和性能。
预备语句是可用于优化性能的服务器端对象。 准备查询后，将对其进行解析，重写和计划。 以后只需要执行预备语句。 因此，解析，重写和计划阶段仅执行一次，而不是每次执行语句时。 准备的语句仅在当前数据库会话期间持续。 会话结束时，预备语句将被遗忘，因此必须在重新使用之前重新创建它。
$host = "localhost";
$user = "user12";
$pass = "34klq*";
$db = "testdb";
$id = 9;
$name = "BMW";
$price = 36000;
$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
or die ("Could not connect to server\n");
$query = "INSERT INTO cars VALUES($1, $2, $3)";
pg_prepare($con, "prepare1", $query)
or die ("Cannot prepare statement\n");
pg_execute($con, "prepare1", array($id, $name, $price))
or die ("Cannot execute statement\n");
echo "Row successfully inserted\n";
pg_close($con);
我们向汽车表添加一行。 我们使用预备查询。
$id = 9;
$name = "BMW";
$price = 36000;
我们有三个变量将用于构建查询。 这些值可以例如来自网络表单。
$query = "INSERT INTO cars VALUES($1, $2, $3)";
这是带有$1，$2和$3占位符的 SQL 查询。 占位符将在以后填充。
pg_prepare($con, "prepare1", $query)
or die ("Cannot prepare statement\n");
在这里，我们通过调用pg_prepare()函数准备查询。 函数的第二个参数是预备语句的名称。 每个连接必须唯一。 预备语句更快，并且可以防止 SQL 注入攻击。
pg_execute($con, "prepare1", array($id, $name, $price))
or die ("Cannot execute statement\n");
pg_execute()函数发送一个请求，以执行带有给定参数的预备语句，并等待结果。 这些值绑定到占位符。
$ php prepared.php
Row successfully inserted
testdb=# SELECT * FROM cars;
id | name | price
----+------------+--------
1 | Audi | 52642
2 | Mercedes | 57127
3 | Skoda | 9000
4 | Volvo | 29000
5 | Bentley | 350000
6 | Citroen | 21000
7 | Hummer | 41400
8 | Volkswagen | 21606
9 | BMW | 36000
(9 rows)
我们在表上放了一辆新车。
数据来源
创建本教程时，请参考 PostgreSQL PHP 手册和 PostgreSQL 文档。
这是 PostgreSQL PHP 教程的介绍性章节。
在 PostgreSQL 中使用 PHP 检索数据
有几种函数可以从数据库读取数据。 数据可以作为枚举数组，对象或关联数组来获取。
从数据库检索数据需要执行三个步骤。 首先，我们定义一个 SQL SELECT语句。 该语句通过pg_query()函数执行。 （在准备语句的情况下，我们将使用pg_execute()函数。）我们收到一个结果集对象。 使用结果集，我们使用pg_fetch_row()，pg_fetch_assoc()或pg_fetch_object()函数获取数据。
<?php
$host = "localhost";
$user = "user12";
$pass = "34klq*";
$db = "testdb";
$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
or die ("Could not connect to server\n");
$query = "SELECT * FROM cars LIMIT 5";
$rs = pg_query($con, $query) or die("Cannot execute query: $query\n");
while ($row = pg_fetch_row($rs)) {
echo "$row[0] $row[1] $row[2]\n";
}
pg_close($con);
?>
我们从cars表中获得 5 辆汽车，并将它们打印到控制台。
$query = "SELECT * FROM cars LIMIT 5";
这是提取 5 行汽车的 SQL。
$rs = pg_query($con, $query) or die("Cannot execute query: $query\n");
使用pg_query()函数执行查询。 该函数返回结果集。
while ($row = pg_fetch_row($rs)) {
echo "$row[0] $row[1] $row[2]\n";
}
pg_fetch_row()函数返回一个字符串值数组。 我们可以使用数组索引符号来获取数组字段。 当没有更多的行时，该函数返回false，而while循环终止。
$ php retrieve1.php
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
示例输出。
在第二个示例中，我们将使用pg_fetch_assoc()函数获取数据。
<?php
$host = "localhost";
$user = "user12";
$pass = "34klq*";
$db = "testdb";
$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
or die ("Could not connect to server\n");
$query = "SELECT * FROM cars LIMIT 5";
$rs = pg_query($con, $query) or die("Cannot execute query: $query\n");
while ($row = pg_fetch_assoc($rs)) {
echo $row['id'] . " " . $row['name'] . " " . $row['price'];
echo "\n";
}
pg_close($con);
?>
pg_fetch_assoc()函数获取一行作为关联数组。 关联数组的键是列名。
while ($row = pg_fetch_assoc($rs)) {
echo $row['id'] . " " . $row['name'] . " " . $row['price'];
echo "\n";
}
id，name和price是返回的关联数组的键。
在最后一个示例中，我们将使用pg_fetch_object()函数获取数据。 它返回一个对象，该对象的属性与获取的行的字段名称相对应。
<?php
$host = "localhost";
$user = "user12";
$pass = "34klq*";
$db = "testdb";
$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
or die("Could not connect to server\n");
$query = "SELECT * FROM cars LIMIT 5";
$rs = pg_query($con, $query) or die("Cannot execute query: $query\n");
while ($ro = pg_fetch_object($rs)) {
echo $ro->id . " ";
echo $ro->name . " ";
echo $ro->price . " ";
echo "\n";
}
pg_close($con);
?>
我们从cars表中选择五辆车。
while ($ro = pg_fetch_object($rs)) {
echo $ro->id . " ";
echo $ro->name . " ";
echo $ro->price . " ";
echo "\n";
}
列名称是对象属性，其中包含值。
我们已经使用pg_fetch_row()，pg_fetch_assoc()和pg_fetch_object()函数完成了数据读取。
在 PostgreSQL 中使用 PHP 处理图像
在 PostgreSQL PHP 教程的这一章中，我们将使用图像文件。 有些人不同意将图像放入数据库。 在这里，我们只展示如何做。 我们不讨论是否将图像保存在数据库中的技术问题。
testdb=> CREATE TABLE images(id INT PRIMARY KEY, data BYTEA);
对于此示例，我们创建一个名为images的新表。 对于图像，我们使用BYTEA数据类型。 它允许存储二进制字符串。
插入图像
在第一个示例中，我们将图像插入 PostgreSQL 数据库。
<?php
$host = "localhost";
$user = "user12";
$pass = "34klq*";
$db = "testdb";
$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
or die ("Could not connect to server\n");
$file_name = "woman.jpg";
$img = fopen($file_name, 'r') or die("cannot read image\n");
$data = fread($img, filesize($file_name));
$es_data = pg_escape_bytea($data);
fclose($img);
$query = "INSERT INTO images(id, data) Values(1, '$es_data')";
pg_query($con, $query);
pg_close($con);
?>
我们从当前工作目录中读取图像，并将其写入 PostgreSQL testdb数据库的图像表中。
$file_name = "woman.jpg";
这是我们将插入数据库的图像文件的名称。 该图像位于当前工作目录中。
$img = fopen($file_name, 'r') or die("cannot read image\n");
$data = fread($img, filesize($file_name));
我们从文件系统读取二进制数据。
$es_data = pg_escape_bytea($data);
二进制数据可能包含一些字符，这些字符可能在将它们插入数据库表时引起问题。 pg_escape_bytea()函数对要插入到bytea field中的字符串进行转义。 以后，当从数据库中读取二进制数据时，必须对数据进行转义。
fclose($img);
指向图像文件的句柄已关闭。
$query = "INSERT INTO images(id, data) Values(1, '$es_data')";
pg_query($con, $query);
图像已插入数据库。
读取图像
在本节中，我们将执行相反的操作。 我们将从数据库表中读取图像。
<?php
$host = "localhost";
$user = "user12";
$pass = "34klq*";
$db = "testdb";
$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
or die ("Could not connect to server\n");
$query = "SELECT data FROM images WHERE id=1";
$res = pg_query($con, $query) or die (pg_last_error($con));
$data = pg_fetch_result($res, 'data');
$unes_image = pg_unescape_bytea($data);
$file_name = "woman2.jpg";
$img = fopen($file_name, 'wb') or die("cannot open image\n");
fwrite($img, $unes_image) or die("cannot write image data\n");
fclose($img);
pg_close($con);
?>
我们从图像表中读取图像数据并将其写入另一个文件，我们称为woman2.jpg。
$query = "SELECT data FROM images WHERE id=1";
这行是一条 SQL SELECT语句，用于从表中检索图像数据。
$data = pg_fetch_result($res, 'data');
我们从images表的 data 列中获取数据。
$unes_image = pg_unescape_bytea($data);
当我们将图像数据插入数据库时，我们对其进行了转义。 现在我们必须将其转义回原始状态。
$file_name = "woman2.jpg";
$img = fopen($file_name, 'wb') or die("cannot open image\n");
我们打开一个文件进行写入。 新文件名将为woman2.jpg。
fwrite($img, $unes_image) or die("cannot write image data\n");
数据被写入文件系统。
PostgreSQL PHP 教程的这一部分专门用于读取和写入图像。
用 PHP 获取 PostgreSQL 元数据
元数据是有关数据库中数据的信息。 PostgreSQL 中的元数据包含有关表和列的信息，我们在其中存储数据。 受 SQL 语句影响的行数是元数据。 结果集中返回的行数和列数也属于元数据。
有一个实验性函数pg_meta_data()，它以表的形式返回表名的表定义。
列和行
如前所述，结果集中的列数和行数被视为元数据。
<?php
$host = "localhost";
$user = "user12";
$pass = "34klq*";
$db = "testdb";
$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
or die ("Could not connect to server\n");
$query = "SELECT Name, Price FROM Cars LIMIT 4";
$rs = pg_query($con, $query) or die (pg_last_error($con));
$num_rows = pg_num_rows($rs);
$num_cols = pg_num_fields($rs);
echo "There are $num_rows rows and $num_cols columns in the query\n";
pg_close($con);
?>
在上面的示例中，我们获取查询返回的行数和列数。
$query = "SELECT Name, Price FROM Cars LIMIT 4";
从 SQL 查询中可以看到我们选择了 2 列和 4 行。 该查询也可以动态创建。
$num_rows = pg_num_rows($rs);
$num_cols = pg_num_fields($rs);
pg_num_rows()函数返回 PostgreSQL 结果资源中的行数。 pg_num_rows()函数返回 PostgreSQL 结果资源中的列数（字段）。
$ php colsrows.php
There are 4 rows and 2 columns in the query.
示例输出。
列标题
接下来，我们将展示如何使用数据库表中的数据打印列标题。
<?php
$host = "localhost";
$user = "user12";
$pass = "34klq*";
$db = "testdb";
$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
or die ("Could not connect to server\n");
$query = "SELECT id, name, price FROM cars LIMIT 5";
$rs = pg_query($con, $query) or die (pg_last_error($con));
$fname1 = pg_field_name($rs, 0);
$fname2 = pg_field_name($rs, 1);
$fname3 = pg_field_name($rs, 2);
printf("%3s %-10s %8s\n", $fname1, $fname2, $fname3);
while ($row = pg_fetch_row($rs)) {
printf("%3s %-10s %8s\n", $row[0], $row[1], $row[2]);
}
pg_close($con);
?>
在此程序中，我们从cars表中选择 5 行，并带有列名。
$fname1 = pg_field_name($rs, 0);
$fname2 = pg_field_name($rs, 1);
$fname3 = pg_field_name($rs, 2);
pg_field_name()函数返回指定列号的列（字段）名称。
printf("%3s %-10s %8s\n", $fname1, $fname2, $fname3);
我们打印列标题。 我们使用printf函数进行一些格式化。
$ php column_headers.php
id name price
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
该程序的输出。
受影响的行
在下面的示例中，我们将发现特定的 SQL 命令已进行了多少更改。
<?php
$host = "localhost";
$user = "user12";
$pass = "34klq*";
$db = "testdb";
$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
or die ("Could not connect to server\n");
$query = "DROP TABLE IF EXISTS friends";
pg_query($con, $query) or die("Cannot execute query: $query\n");
$query = "CREATE TABLE friends(id INT, name TEXT)";
pg_query($con, $query) or die("Cannot execute query: $query\n");
$query = "INSERT INTO friends VALUES (1, 'Jane'), (2, 'Thomas')"
. ", (3, 'Beky'), (4, 'Robert'), (5, 'Lucy')";
$res = pg_query($con, $query) or die("Cannot execute query: $query\n");
$ar = pg_affected_rows($res);
echo "The query has affected $ar rows\n";
$query = "DELETE FROM friends WHERE id IN (3, 4, 5)";
$res = pg_query($con, $query) or die("Cannot execute query: $query\n");
$ar = pg_affected_rows($res);
echo "The query has affected $ar rows\n";
pg_close($con);
?>
我们创建一个好友表。 在最后一个 SQL 命令中，我们删除三行。 我们有一个INSERT和一个DELETE语句，我们可以对其调用pg_affected_rows()以获取受影响的行数。
$query = "INSERT INTO friends VALUES (1, 'Jane'), (2, 'Thomas')"
. ", (3, 'Beky'), (4, 'Robert'), (5, 'Lucy')";
我们在friends表中插入五行。
$ar = pg_affected_rows($res);
echo "The query has affected $ar rows\n";
pg_affected_rows()函数返回受最后一条 SQL 语句影响的行数。
$ php affected_rows.php
The query has affected 5 rows
The query has affected 3 rows
INSERT语句创建了五行，DELETE语句删除了三行。
表元数据
有一个实验pg_meta_data()。 它为数据库表的每一列返回元数据。
<?php
$host = "localhost";
$user = "user12";
$pass = "34klq*";
$db = "testdb";
$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
or die ("Could not connect to server\n");
$ary = pg_meta_data($con, 'cars');
var_dump($ary);
pg_close($con);
?>
该示例打印有关cars表的表列的元数据。
$ary = pg_meta_data($con, 'cars');
pg_meta_data()返回汽车表的元数据信息。 它返回一个数组。
var_dump($ary);
var_dump()函数转储有关变量的信息。 在我们的例子中，它是返回的元数据信息数组。
$ php metadata.php
array(3) {
["id"]=>
array(6) {
["num"]=>
int(1)
["type"]=>
string(4) "int4"
["len"]=>
int(4)
["not null"]=>
bool(true)
...
摘自示例输出。
在 PostgreSQL PHP 教程的这一部分中，我们使用了数据库元数据。
在 PostgreSQL 中使用 PHP 进行事务
在本章中，我们将处理事务。 首先，我们提供一些基本定义。 然后，我们将使用事务的示例程序。
事务是针对一个或多个数据库中数据的数据库操作的基本单位。 事务中的 SQL 语句可以全部提交给数据库，也可以全部回滚。 为了数据安全和完整性，将 SQL 语句放入事务中。
在 PostgreSQL PHP 中，每个 SQL 语句在执行后都会提交给数据库。 并非所有语言绑定都是如此。 例如，在 Python 的psycopg2模块中，默认情况下，必须使用commit()方法显式提交所有更改。
在直接 SQL 中，事务以BEGIN TRANSACTION语句开始，并以END TRANSACTION和COMMIT语句结束。 在 PostgreSQL 中，这些语句是BEGIN和COMMIT。 在某些驱动程序中，这些语句被省略。 它们由驱动处理。 在 PHP 中没有这样的方法，我们必须使用直接 SQL。 （在 PHP PDO 中有这样的方法。）
<?php
$host = "localhost";
$user = "user12";
$pass = "34klq*";
$db = "testdb";
$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
or die ("Could not connect to server\n");
pg_query("BEGIN") or die("Could not start transaction\n");
$res1 = pg_query("DELETE FROM cars WHERE id IN (1, 9)");
$res2 = pg_query("INSERT INTO cars VALUES (1, 'BMW', 36000), (9, 'Audi', 52642)");
if ($res1 and $res2) {
echo "Commiting transaction\n";
pg_query("COMMIT") or die("Transaction commit failed\n");
} else {
echo "Rolling back transaction\n";
pg_query("ROLLBACK") or die("Transaction rollback failed\n");;
}
pg_close($con);
?>
我们有汽车表。 我们要交换第一行和最后一行。 我们分两个步骤进行。 首先，我们删除两行。 然后，我们将其插入并交换 ID。 这两个操作需要放在事务中。 如果第一步成功而第二步失败，则数据将被破坏。 因此，我们需要全部完成或什么都不做。
pg_query("BEGIN") or die("Could not start transaction\n");
我们通过发出BEGIN语句开始新的事务。
$res1 = pg_query("DELETE FROM cars WHERE id IN (1, 9)");
$res2 = pg_query("INSERT INTO cars VALUES (1, 'BMW', 36000), (9, 'Audi', 52642)");
这是两个修改表的 SQL 语句。 两个pg_query()函数都返回true或false布尔值，指示 SQL 命令是否失败。
if ($res1 and $res2) {
echo "Commiting transaction\n";
pg_query("COMMIT") or die("Transaction commit failed\n");
} else {
echo "Rolling back transaction\n";
pg_query("ROLLBACK") or die("Transaction rollback failed\n");;
}
如果两个函数调用都返回true，则使用COMMIT语句提交事务。 否则，我们将使用ROLLBACK语句回滚更改。
$ php transaction.php
Commiting transaction
testdb=# SELECT * FROM cars ORDER BY id;
id | name | price
----+------------+--------
1 | BMW | 36000
2 | Mercedes | 57127
3 | Skoda | 9000
4 | Volvo | 29000
5 | Bentley | 350000
6 | Citroen | 21000
7 | Hummer | 41400
8 | Volkswagen | 21606
9 | Audi | 52642
(9 rows)
行已成功交换。
在 PostgreSQL PHP 教程的这一部分中，我们提到了事务。
PostgreSQL Java 教程
这是 PostgreSQL 数据库的 Java 教程。 它涵盖了使用 Java 进行 PostgreSQL 编程的基础。
在 ZetCode 上，有类似的相关教程，MySQL Java 教程， Spring JdbcTemplate 教程和 Apache Derby 教程。
JDBC
JDBC 是 Java 编程语言的 API，用于定义客户端如何访问数据库。 它提供了查询和更新数据库中数据的方法。 JDBC 面向关系数据库。 从技术角度来看，API 是java.sql包中的一组类。 要将 JDBC 与特定数据库一起使用，我们需要该数据库的 JDBC 驱动程序。
关于 PostgreSQL 数据库
PostgreSQL 是一个功能强大的开源对象关系数据库系统。 它是一个多用户数据库管理系统。 它可以在包括 Linux，FreeBSD，Solaris，Microsoft Windows 和 Mac OS X 在内的多个平台上运行。PostgreSQL 由 PostgreSQL 全球开发小组开发。
PostgreSQL 驱动
要包含 PostgreSQL Java 驱动程序，我们需要添加以下 Maven 依赖项：
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.0</version>
</dependency>
Maven Exec 插件
为了从 Maven 运行命令行应用，我们可以使用以下 Maven 插件。
<build>
<plugins>
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>exec-maven-plugin</artifactId>
<version>1.6.0</version>
<configuration>
<mainClass>com.zetcode.AppName</mainClass>
</configuration>
</plugin>
</plugins>
</build>
我们可以使用mvn -q exec:java命令运行该应用。 在mainClass标记中，编写主类的完全限定名称，例如com.zetcode.JavaPostgreSqlVersion。 -q以完全模式运行 Maven； 它禁用除错误消息外的所有 Maven 消息。
PostgreSQL 设置
我们将展示如何在 Debian Linux 系统上安装 PostgreSQL 数据库。
$ sudo apt-get install postgresql
此命令将安装 PostgreSQL 服务器和相关包。
$ /etc/init.d/postgresql status
我们使用postgresql status命令检查数据库的状态。
$ sudo -u postgres psql postgres
psql (9.5.10)
Type "help" for help.
postgres=# \password postgres
Enter new password:
Enter it again:
安装后，将使用空的默认密码创建一个具有管理权限的postgres用户。 第一步，我们需要为postgres设置密码。
$ sudo -u postgres createuser --interactive --password user12
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
Password:
我们创建一个新的数据库用户。
$ sudo -u postgres createdb testdb -O user12
我们使用createdb命令创建一个新的testdb数据库，该数据库将由user12拥有。
$ sudo vi /etc/postgresql/9.5/main/pg_hba.conf
我们编辑pg_hba.conf文件。
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
为了能够在本地 PostgreSQL 安装中运行 Spring Boot 应用，我们将 Unix 域套接字和本地连接的认证方法更改为trust。
$ sudo service postgresql restart
我们重新启动 PostgreSQL 以启用更改。
$ psql -U user12 -d testdb -W
Password for user user12:
psql (9.5.10)
Type "help" for help.
testdb=>
现在我们可以使用psql工具连接到数据库。
从启动脚本中删除 PostgreSQL
如果我们从包中安装 PostgreSQL 数据库，它将自动添加到操作系统的启动脚本中。 如果我们仅学习使用数据库，则不必在每次引导系统时都启动数据库。
$ sudo update-rc.d -f postgresql remove
Removing any system startup links for /etc/init.d/postgresql ...
/etc/rc0.d/K21postgresql
/etc/rc1.d/K21postgresql
/etc/rc2.d/S19postgresql
/etc/rc3.d/S19postgresql
/etc/rc4.d/S19postgresql
/etc/rc5.d/S19postgresql
/etc/rc6.d/K21postgresql
上面的命令删除 PostgreSQL 数据库的所有系统启动链接。
Java PostgreSQL 版本
如果以下程序运行正常，则我们已安装一切正常。 我们检查 PostgreSQL 服务器的版本。
JavaPostgreSqlVersion.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JavaPostgreSqlVersion {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/testdb";
String user = "user12";
String password = "34klq*";
try (Connection con = DriverManager.getConnection(url, user, password);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("SELECT VERSION()")) {
if (rs.next()) {
System.out.println(rs.getString(1));
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JavaPostgreSqlVersion.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
我们连接到数据库，并获取有关 PostgreSQL 服务器的一些信息。
String url = "jdbc:postgresql://localhost:5432/testdb";
这是 PostgreSQL 数据库的连接 URL。 每个驱动程序对于 URL 都有不同的语法。 在本例中，我们提供一个主机（localhost），一个端口（5432）和一个数据库名称（testdb）。
try (Connection con = DriverManager.getConnection(url, user, password);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("SELECT VERSION()")) {
使用getConnection()建立与数据库的连接。 连接对象的createStatement()方法创建一个Statement对象，用于将 SQL 语句发送到数据库。 executeQuery()执行给定的 SQL 语句，该语句返回单个ResultSet对象。 ResultSet是由特定 SQL 语句返回的数据表。
try-with-resources语句可确保在语句末尾关闭每个资源。
if (rs.next()) {
System.out.println(rs.getString(1));
}
ResultSet对象维护一个游标，该游标指向其当前数据行。 最初，光标位于第一行之前。 next()方法将光标移动到下一行。 如果没有剩余的行，则该方法返回false。 getString()方法检索指定列的值。 第一列的索引为 1。
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JavaPostgreSqlVersion.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
如果发生异常，我们将记录错误消息。
$ mvn -q exec:java
PostgreSQL 9.5.10 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
这是程序的示例输出。
创建和填充表
接下来，我们将创建数据库表并用数据填充它们。 这些表将在本教程中使用。
authors_books_postgresql.sql
DROP TABLE IF EXISTS books, authors, testing, images;
CREATE TABLE IF NOT EXISTS authors (
id serial PRIMARY KEY,
name VARCHAR(25)
);
CREATE TABLE IF NOT EXISTS books (
id serial PRIMARY KEY,
author_id INT references authors(id), title VARCHAR(100)
);
CREATE TABLE IF NOT EXISTS testing(id INT);
CREATE TABLE IF NOT EXISTS images(id serial, data bytea);
INSERT INTO authors(id, name) VALUES(1, 'Jack London');
INSERT INTO authors(id, name) VALUES(2, 'Honore de Balzac');
INSERT INTO authors(id, name) VALUES(3, 'Lion Feuchtwanger');
INSERT INTO authors(id, name) VALUES(4, 'Emile Zola');
INSERT INTO authors(id, name) VALUES(5, 'Truman Capote');
INSERT INTO books(id, author_id, title) VALUES(1, 1, 'Call of the Wild');
INSERT INTO books(id, author_id, title) VALUES(2, 1, 'Martin Eden');
INSERT INTO books(id, author_id, title) VALUES(3, 2, 'Old Goriot');
INSERT INTO books(id, author_id, title) VALUES(4, 2, 'Cousin Bette');
INSERT INTO books(id, author_id, title) VALUES(5, 3, 'Jew Suess');
INSERT INTO books(id, author_id, title) VALUES(6, 4, 'Nana');
INSERT INTO books(id, author_id, title) VALUES(7, 4, 'The Belly of Paris');
INSERT INTO books(id, author_id, title) VALUES(8, 5, 'In Cold blood');
INSERT INTO books(id, author_id, title) VALUES(9, 5, 'Breakfast at Tiffany');
我们有一个authors_books_postgresql.sql文件。 它创建四个数据库表，作者，书籍，测试和图像。 我们在books表的author_id列上放置一个外键约束。 我们用初始数据填充作者和书籍表。
$ psql -U user12 -d testdb -W
Password for user user12:
psql (9.5.10)
Type "help" for help.
testdb=> \i authors_books_postgresql.sql
psql:authors_books_postgresql.sql:1: NOTICE: table "books" does not exist, skipping
psql:authors_books_postgresql.sql:1: NOTICE: table "authors" does not exist, skipping
psql:authors_books_postgresql.sql:1: NOTICE: table "testing" does not exist, skipping
psql:authors_books_postgresql.sql:1: NOTICE: table "images" does not exist, skipping
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
...
psql 是 PostgreSQL 的基于终端的前端。 我们可以使用它来执行交互式查询，将其发布到 PostgreSQL，并查看查询结果。 在psql工具内部，我们导入并执行authors_books_postgresql.sql文件。
testdb=> \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+--------
public | authors | table | user12
public | books | table | user12
public | cities | table | user12
public | images | table | user12
public | testing | table | user12
(5 rows)
我们检查创建的表。
Java PostgreSQL 预备语句
现在，我们将以预备语句来关注自己。 在编写预备语句时，我们使用占位符，而不是直接将值写入语句中。 预准备的语句可提高安全性和性能。
在 Java 中，PreparedStatement是代表预编译的 SQL 语句的对象。
JavaPostgreSqlPrepared.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JavaPostgreSqlPrepared {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/testdb";
String user = "user12";
String password = "34klq*";
int id = 6;
String author = "Trygve Gulbranssen";
String query = "INSERT INTO authors(id, name) VALUES(?, ?)";
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement pst = con.prepareStatement(query)) {
pst.setInt(1, id);
pst.setString(2, author);
pst.executeUpdate();
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JavaPostgreSqlPrepared.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
我们将新作者添加到authors表中。
String query = "INSERT INTO authors(id, name) VALUES(?, ?)";
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement pst = con.prepareStatement(query)) {
在这里，我们创建一个预备语句。 在编写预备语句时，我们使用占位符，而不是直接将值写入语句中。 预备语句更快，并且可以防止 SQL 注入攻击。 ?是一个占位符，稍后将填充。 在我们的例子中，我们将填充两个值：一个整数 id 和一个字符串名。
pst.setInt(1, id);
整数值绑定到占位符。
pst.setString(2, author);
字符串值绑定到占位符。
pst.executeUpdate();
执行预备语句。 当我们不希望返回任何数据时，我们使用语句对象的executeUpdate()方法。 这是当我们创建数据库或执行INSERT，UPDATE和DELETE语句时。
testdb=> SELECT * FROM Authors;
id | name
----+--------------------
1 | Jack London
2 | Honore de Balzac
3 | Lion Feuchtwanger
4 | Emile Zola
5 | Truman Capote
6 | Trygve Gulbranssen
(6 rows)
我们在表中插入了一位新作者。
Java PostgreSQL 检索数据
接下来，我们将展示如何从数据库表中检索数据。 我们从authors表中获取所有数据。
JavaPostgreSqlRetrieve.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JavaPostgreSqlRetrieve {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/testdb";
String user = "user12";
String password = "34klq*";
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement pst = con.prepareStatement("SELECT * FROM authors");
ResultSet rs = pst.executeQuery()) {
while (rs.next()) {
System.out.print(rs.getInt(1));
System.out.print(": ");
System.out.println(rs.getString(2));
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JavaPostgreSqlRetrieve.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
我们从authors表中获取所有作者，并将它们打印到控制台。
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement pst = con.prepareStatement("SELECT * FROM authors");
ResultSet rs = pst.executeQuery()) {
我们执行一个查询，该查询从authors表中选择所有列。 我们使用executeQuery()方法。 该方法执行给定的 SQL 语句，该语句返回单个ResultSet对象。 ResultSet是 SQL 查询返回的数据表。
while (rs.next()) {
System.out.print(rs.getInt(1));
System.out.print(": ");
System.out.println(rs.getString(2));
}
next()方法将光标移至下一条记录。 当结果集中没有更多行时，它将返回false。 getInt()和getString()方法以 Java 编程语言中int和String的形式检索此ResultSet对象当前行中指定列的值。
$ mvn -q exec:java
1: Jack London
2: Honore de Balzac
3: Lion Feuchtwanger
4: Emile Zola
5: Truman Capote
6: Trygve Gulbranssen
作者的 ID 和名称将打印到控制台。
属性
通常的做法是将配置数据放在程序外部的单独文件中。 这样程序员可以更加灵活。 我们可以更改用户，密码或连接 URL，而无需重新编译程序。
在 Java 中，Properties是为此经常使用的类。 该类用于轻松读取和保存键/值属性。
database.properties
db.url=jdbc:postgresql://localhost:5432/testdb
db.user=user12
db.passwd=34klq*
我们有一个database.properties文件，其中有三个键/值对。 它们在程序执行期间被加载。
JavaPostgreSqlRetrieveProperties.java
package com.zetcode;
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JavaPostgreSqlRetrieveProperties {
public static Properties readProperties() {
Properties props = new Properties();
Path myPath = Paths.get("src/main/resources/database.properties");
try {
BufferedReader bf = Files.newBufferedReader(myPath,
StandardCharsets.UTF_8);
props.load(bf);
} catch (IOException ex) {
Logger.getLogger(JavaPostgreSqlRetrieveProperties.class.getName()).log(
Level.SEVERE, null, ex);
}
return props;
}
public static void main(String[] args) {
Properties props = readProperties();
String url = props.getProperty("db.url");
String user = props.getProperty("db.user");
String passwd = props.getProperty("db.passwd");
try (Connection con = DriverManager.getConnection(url, user, passwd);
PreparedStatement pst = con.prepareStatement("SELECT * FROM Authors");
ResultSet rs = pst.executeQuery()) {
while (rs.next()) {
System.out.print(rs.getInt(1));
System.out.print(": ");
System.out.println(rs.getString(2));
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(
JavaPostgreSqlRetrieveProperties.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
我们连接到testdb数据库，并将authors表的内容打印到控制台。 这次，我们从文件加载连接属性。 他们没有在程序中硬编码。
public static Properties readProperties() {
Properties props = new Properties();
Path myPath = Paths.get("src/main/resources/database.properties");
try {
BufferedReader bf = Files.newBufferedReader(myPath, StandardCharsets.UTF_8);
props.load(bf);
} catch (IOException ex) {
Logger.getLogger(JavaPostgreSqlRetrieveProperties.class.getName()).log(
Level.SEVERE, null, ex);
}
return props;
}
这些属性通过load()加载到Properties类中。 从位于src/main/resources中的database.properties文件读取数据。
String url = props.getProperty("db.url");
String user = props.getProperty("db.user");
String passwd = props.getProperty("db.passwd");
使用getProperty()方法检索这些值。
Java PostgreSQL 多条语句
可以在一个查询中执行多个 SQL 语句。
JavaPostgreSqlMultipleStatements.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JavaPostgreSqlMultipleStatements {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/testdb";
String user = "user12";
String password = "34klq*";
String query = "SELECT id, name FROM authors WHERE Id=1;"
+ "SELECT id, name FROM authors WHERE Id=2;"
+ "SELECT id, name FROM authors WHERE Id=3";
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement pst = con.prepareStatement(query)) {
boolean isResult = pst.execute();
do {
try (ResultSet rs = pst.getResultSet()) {
while (rs.next()) {
System.out.print(rs.getInt(1));
System.out.print(": ");
System.out.println(rs.getString(2));
}
isResult = pst.getMoreResults();
}
} while (isResult);
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(
JavaPostgreSqlMultipleStatements.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
在代码示例中，我们从authors表中检索三行。 我们使用三个SELECT语句来获取三行。
String query = "SELECT id, name FROM authors WHERE Id=1;"
+ "SELECT id, name FROM authors WHERE Id=2;"
+ "SELECT id, name FROM authors WHERE Id=3";
在这里，我们有一个包含多个语句的查询。 语句用分号分隔。
boolean isResult = pst.execute();
我们调用已预备语句对象的execute()方法。 该方法返回一个布尔值，该布尔值指示第一个结果是否为ResultSet对象。 使用getMoreResults()方法调用后续结果。
do {
try (ResultSet rs = pst.getResultSet()) {
while (rs.next()) {
System.out.print(rs.getInt(1));
System.out.print(": ");
System.out.println(rs.getString(2));
}
isResult = pst.getMoreResults();
}
} while (isResult);
结果的处理在do/while循环内完成。 通过getResultSet()方法调用检索ResultSet。 为了找出是否还有其他结果，我们调用getMoreResults()方法。
$ mvn -q exec:java
1: Jack London
2: Honore de Balzac
3: Lion Feuchtwanger
前三行是从authors表中检索的。
元数据
元数据是有关数据库中数据的信息。 PostgreSQL 数据库中的元数据包含有关表和列的信息，我们在其中存储数据。 受 SQL 语句影响的行数是元数据。 结果集中返回的行数和列数也属于元数据。
可以通过调用结果集对象的getMetaData()方法或从information_schema表获得 PostgreSQL 中的元数据。
在下一个示例中，我们使用数据库表中的数据打印列标题。
JavaPostgreSqlColumnHeaders.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Formatter;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JavaPostgreSqlColumnHeaders {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/testdb";
String user = "user12";
String password = "34klq*";
String query = "SELECT name, title From authors, "
+ "books WHERE authors.id=books.author_id";
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement pst = con.prepareStatement(query);
ResultSet rs = pst.executeQuery()) {
ResultSetMetaData meta = rs.getMetaData();
String colname1 = meta.getColumnName(1);
String colname2 = meta.getColumnName(2);
Formatter fmt1 = new Formatter();
fmt1.format("%-21s%s", colname1, colname2);
System.out.println(fmt1);
while (rs.next()) {
Formatter fmt2 = new Formatter();
fmt2.format("%-21s", rs.getString(1));
System.out.print(fmt2);
System.out.println(rs.getString(2));
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(
JavaPostgreSqlColumnHeaders.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
在此程序中，我们从作者表中选择作者，并从books表中选择他们的书。 我们打印结果集中返回的列的名称。 我们格式化输出。
String query = "SELECT name, title From authors, " +
"books WHERE authors.id=books.author_id";
这是将作者与他们的书联系在一起的 SQL 语句。
ResultSetMetaData meta = rs.getMetaData();
要获取列名，我们需要获取ResultSetMetaData。 它是一个对象，可用于获取有关ResultSet对象中列的类型和属性的信息。
String colname1 = meta.getColumnName(1);
String colname2 = meta.getColumnName(2);
从获得的元数据中，我们获得列名。
Formatter fmt1 = new Formatter();
fmt1.format("%-21s%s", colname1, colname2);
System.out.println(fmt1)
我们将列名称打印到控制台。 我们使用Formatter对象格式化数据。
while (rs.next()) {
Formatter fmt2 = new Formatter();
fmt2.format("%-21s", rs.getString(1));
System.out.print(fmt2);
System.out.println(rs.getString(2));
}
我们将数据打印到控制台。 我们再次使用Formatter对象来格式化数据。 第一列为 21 个字符，并在左侧对齐。
$ mvn exec:java -q
name title
Jack London Call of the Wild
Jack London Martin Eden
Honore de Balzac Old Goriot
Honore de Balzac Cousin Bette
Lion Feuchtwanger Jew Suess
Emile Zola Nana
Emile Zola The Belly of Paris
Truman Capote In Cold blood
Truman Capote Breakfast at Tiffany
这是程序的输出。
在下面的示例中，我们将列出testdb数据库中的所有表。
JavaPostgreSqlListTables.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JavaPostgreSqlListTables {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/testdb";
String user = "user12";
String password = "34klq*";
String query = "SELECT table_name FROM information_schema.tables "
+ "WHERE table_schema = 'public'";
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement pst = con.prepareStatement(query);
ResultSet rs = pst.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getString(1));
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JavaPostgreSqlListTables.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
该代码示例将当前数据库中的所有可用表打印到终端。
String query = "SELECT table_name FROM information_schema.tables "
+ "WHERE table_schema = 'public'";
表名存储在系统information_schema表中。
$ mvn -q exec:java
authors
books
images
testing
cities
列出testdb数据库中的可用表。
写入图像
有些人喜欢将其图像放入数据库中，有些人则希望将其保留在文件系统中以供其应用使用。 当我们处理大量图像时，会出现技术难题。 图像是二进制数据。 PostgreSQL 数据库具有一种特殊的数据类型来存储称为bytea的二进制数据。 这是非标准数据类型。 数据库中的标准数据类型为BLOB。
在此示例中，我们使用images表。
JavaPostgreSqlWriteImage.java
package com.zetcode;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JavaPostgreSqlWriteImage {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost/testdb";
String user = "user12";
String password = "34klq*";
String query = "INSERT INTO images(data) VALUES(?)";
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement pst = con.prepareStatement(query)) {
File img = new File("src/main/resources/sid.jpg");
try (FileInputStream fin = new FileInputStream(img)) {
pst.setBinaryStream(1, fin, (int) img.length());
pst.executeUpdate();
} catch (IOException ex) {
Logger.getLogger(JavaPostgreSqlWriteImage.class.getName()).log(
Level.SEVERE, ex.getMessage(), ex);
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JavaPostgreSqlWriteImage.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
在前面的示例中，我们从当前工作目录中读取 JPG 图像，并将其插入到images表中。
String query = "INSERT INTO images(data) VALUES(?)";
这是插入图像的 SQL。
File img = new File("src/main/resources/sid.jpg");
try (FileInputStream fin = new FileInputStream(img)) {
我们为图像文件创建一个File对象。 要从该文件读取字节，我们创建一个FileInputStream对象。
pst.setBinaryStream(1, fin, (int) img.length());
二进制流设置为预备语句。 setBinaryStream()方法的参数是要绑定的参数索引，输入流和流中的字节数。
pst.executeUpdate();
我们执行该语句。
testdb=> select count(id) from images;
count
-------
1
(1 row)
如果我们已经写了图像，我们在表中查找。
读取图像
在前面的示例中，我们已将图像插入数据库表中。 现在，我们将从表中读取图像。
警告：如果我们使用的是 PostgreSQL 9 和更高版本，则还必须使用最新的 JDBC 驱动程序。 PostgreSQL 改变了 bytea 数据的存储方式。 因此，当将旧版驱动程序与 PostgreSQL 9.x 一起使用时，我们会遇到麻烦。 请注意，NetBeans 或其他应用可能使用较旧的驱动程序。
JavaPostgreSqlReadImage.java
package com.zetcode;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JavaPostgreSqlReadImage {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/testdb";
String user = "user12";
String password = "34klq*";
String query = "SELECT data, LENGTH(data) FROM images WHERE id = 1";
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement pst = con.prepareStatement(query);
ResultSet rs = pst.executeQuery()) {
rs.next();
File myFile = new File("src/main/resources/sid.jpg");
try (FileOutputStream fos = new FileOutputStream(myFile)) {
int len = rs.getInt(2);
byte[] buf = rs.getBytes("data");
fos.write(buf, 0, len);
}
} catch (IOException | SQLException ex) {
Logger lgr = Logger.getLogger(JavaPostgreSqlReadImage.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
我们从images表中读取了一张图像。
String query = "SELECT data, LENGTH(data) FROM images WHERE id = 1";
我们从数据库表中选择数据和图像大小。
File myFile = new File("src/main/resources/sid.jpg");
try (FileOutputStream fos = new FileOutputStream(myFile)) {
创建FileOutputStream对象以写入文件。 它旨在写入原始字节流，例如图像数据。
int len = result.getInt(2);
我们以字节为单位获取图像数据的长度。
byte[] buf = result.getBytes("data");
getBytes()方法从结果集中以字节数组的形式检索所有字节。
fos.write(buf, 0, len);
字节被写入输出流。 该映像是在文件系统上创建的。
事务支持
事务是针对一个或多个数据库中数据的数据库操作的基本单位。 事务中所有 SQL 语句的影响可以全部提交给数据库，也可以全部回滚。
创建连接后，它处于自动提交模式。 这意味着每个单独的 SQL 语句都被视为事务，并在执行后立即自动提交。 对于所有 JDBC 驱动程序（包括 PostgreSQL 的驱动程序）都是如此。 要开始新的事务，我们关闭自动提交。
在直接 SQL 中，事务以BEGIN TRANSACTION语句开始，并以END TRANSACTION和COMMIT语句结束。 在 PostgreSQL 中，这些语句是BEGIN和COMMIT。 但是，在使用驱动程序时，将省略这些语句。 它们由驱动处理。 确切的细节是特定于驱动程序的。 例如，psycopg2 Python 驱动程序在第一个 SQL 语句之后启动事务。 必须通过将autocommit属性设置为True来设置自动提交模式。 相反，默认情况下，JDBC 驱动程序处于自动提交模式。 并且要开始新事务，必须关闭自动提交。
JavaPostgreSqlTransactionEx.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JavaPostgreSqlTransactionEx {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/testdb";
String user = "user12";
String password = "34klq*";
try (Connection con = DriverManager.getConnection(url, user, password)) {
try (Statement st = con.createStatement()) {
con.setAutoCommit(false);
st.executeUpdate("UPDATE authors SET name = 'Leo Tolstoy' "
+ "WHERE Id = 1");
st.executeUpdate("UPDATE books SET title = 'War and Peace' "
+ "WHERE Id = 1");
st.executeUpdate("UPDATE books SET titl = 'Anna Karenina' "
+ "WHERE Id = 2");
con.commit();
} catch (SQLException ex) {
if (con != null) {
try {
con.rollback();
} catch (SQLException ex1) {
Logger lgr = Logger.getLogger(JavaPostgreSqlTransactionEx.class.getName());
lgr.log(Level.WARNING, ex1.getMessage(), ex1);
}
}
Logger lgr = Logger.getLogger(JavaPostgreSqlTransactionEx.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JavaPostgreSqlTransactionEx.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
在此程序中，我们要在authors表的第一行中更改作者的名称。 我们还必须更改与该作者相关的书籍。 一个需要进行事务的很好的例子。 如果我们更改作者但不更改作者的书，则数据已损坏。
con.setAutoCommit(false);
要进行事务，我们必须将autocommit设置为false。 默认情况下，数据库连接处于自动提交模式。 在这种模式下，每条语句在执行后都会立即提交给数据库。 声明无法撤消。 当自动提交关闭时，我们通过调用commit()提交更改，或通过调用rollback()方法将其回滚。
st.executeUpdate("UPDATE books SET titl = 'Anna Karenina' "
+ "WHERE Id = 2");
第三个 SQL 语句有一个错误。 表中没有titl栏。
con.commit();
如果没有异常，则提交事务。 如果自动提交关闭，则必须显式调用commit()方法。
if (con != null) {
try {
con.rollback();
} catch (SQLException ex1) {
Logger lgr = Logger.getLogger(JavaPostgreSqlTransactionEx.class.getName());
lgr.log(Level.WARNING, ex1.getMessage(), ex1);
}
}
发生异常时，事务将回滚。 没有更改提交到数据库。
testdb=> SELECT name, title FROM authors, books WHERE authors.id=books.author_id;
name | title
-------------------+----------------------
Jack London | Call of the Wild
Jack London | Martin Eden
Honore de Balzac | Old Goriot
Honore de Balzac | Cousin Bette
Lion Feuchtwanger | Jew Suess
Emile Zola | Nana
Emile Zola | The Belly of Paris
Truman Capote | In Cold blood
Truman Capote | Breakfast at Tiffany
(9 rows)
运行应用后，我们将验证数据。 事务已回滚，并且未进行任何更改。
但是，如果没有事务，数据是不安全的。
JavaPostgreSqlNoTransactionEx.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JavaPostgreSqlNoTransactionEx {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/testdb";
String user = "user12";
String password = "34klq*";
try (Connection con = DriverManager.getConnection(url, user, password);
Statement st = con.createStatement()) {
st.executeUpdate("UPDATE authors SET name = 'Leo Tolstoy' "
+ "WHERE Id = 1");
st.executeUpdate("UPDATE books SET title = 'War and Peace' "
+ "WHERE Id = 1");
st.executeUpdate("UPDATE books SET titl = 'Anna Karenina' "
+ "WHERE Id = 2");
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(
JavaPostgreSqlNoTransactionEx.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
我们有同样的例子。 这次没有事务支持。
testdb=> SELECT name, title FROM authors, books WHERE authors.id=books.author_id;
name | title
-------------------+----------------------
Leo Tolstoy | Martin Eden
Honore de Balzac | Old Goriot
Honore de Balzac | Cousin Bette
Lion Feuchtwanger | Jew Suess
Emile Zola | Nana
Emile Zola | The Belly of Paris
Truman Capote | In Cold blood
Truman Capote | Breakfast at Tiffany
Leo Tolstoy | War and Peace
(9 rows)
列夫·托尔斯泰没有写马丁·伊甸园。 数据已损坏。
批量更新
当我们需要使用多个语句更新数据时，可以使用批处理更新。 批量更新可用于INSERT，UPDATE和DELETE语句以及CREATE TABLE和DROP TABLE语句。
JavaPostgreSqlBatchUpdates.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JavaPostgreSqlBatchUpdates {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/testdb";
String user = "user12";
String password = "34klq*";
try (Connection con = DriverManager.getConnection(url, user, password)) {
try (Statement st = con.createStatement()) {
con.setAutoCommit(false);
st.addBatch("DROP TABLE IF EXISTS friends");
st.addBatch("CREATE TABLE friends(id serial, name VARCHAR(10))");
st.addBatch("INSERT INTO friends(name) VALUES ('Jane')");
st.addBatch("INSERT INTO friends(name) VALUES ('Tom')");
st.addBatch("INSERT INTO friends(name) VALUES ('Rebecca')");
st.addBatch("INSERT INTO friends(name) VALUES ('Jim')");
st.addBatch("INSERT INTO friends(name) VALUES ('Robert')");
int counts[] = st.executeBatch();
con.commit();
System.out.println("Committed " + counts.length + " updates");
} catch (SQLException ex) {
if (con != null) {
try {
con.rollback();
} catch (SQLException ex1) {
Logger lgr = Logger.getLogger(
JavaPostgreSqlBatchUpdates.class.getName());
lgr.log(Level.WARNING, ex1.getMessage(), ex1);
}
}
Logger lgr = Logger.getLogger(
JavaPostgreSqlBatchUpdates.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(
JavaPostgreSqlBatchUpdates.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
这是用于批处理更新的示例程序。 我们创建一个名为friends的新表，并在其中插入五行。
con.setAutoCommit(false);
进行批处理更新时，应始终关闭自动提交。
st.addBatch("DROP TABLE IF EXISTS friends");
st.addBatch("CREATE TABLE friends(id serial, name VARCHAR(10))");
st.addBatch("INSERT INTO friends(name) VALUES ('Jane')");
st.addBatch("INSERT INTO friends(name) VALUES ('Tom')");
...
我们使用addBatch()方法向该语句添加新命令。
int counts[] = st.executeBatch();
添加所有命令后，我们调用executeBatch()进行批量更新。 该方法返回已提交更改的数组。
con.commit();
批处理更新在事务中提交。
$ mvn -q exec:java
Committed 7 updates
我们执行BatchUpdate程序。 我们创建了一个新的friends表，并成功插入了 5 行。 DROP TABLE和CREATE TABLE语句也包含在更新计数中。
数据导入导出
PostgreSQL 有一个COPY语句，可用于在表和文件之间复制数据。 从 JDBC 的角度来看，它是对标准的扩展。
JavaPostgreSqlCopyToTextFile.java
package com.zetcode;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;
public class JavaPostgreSqlCopyToTextFile {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/testdb";
String user = "user12";
String password = "34klq*";
try {
Connection con = DriverManager.getConnection(url, user, password);
CopyManager cm = new CopyManager((BaseConnection) con);
String fileName = "src/main/resources/friends.txt";
try (FileOutputStream fos = new FileOutputStream(fileName);
OutputStreamWriter osw = new OutputStreamWriter(fos,
StandardCharsets.UTF_8)) {
cm.copyOut("COPY friends TO STDOUT WITH DELIMITER AS '|'", osw);
}
} catch (SQLException | IOException ex) {
Logger lgr = Logger.getLogger(
JavaPostgreSqlCopyToTextFile.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
在前面的示例中，创建了一个简单的friends表。 在上面的代码中，我们将把friends表复制到一个文件中。
CopyManager cm = new CopyManager((BaseConnection) con);
在这里，我们创建CopyManager的实例。 CopyManager是用于 PostgreSQL 复制的批量数据传输的 API。
String fileName = "src/main/resources/friends.txt";
try (FileOutputStream fos = new FileOutputStream(fileName);
OutputStreamWriter osw = new OutputStreamWriter(fos,
StandardCharsets.UTF_8)) {
我们为friends.txt文件创建一个OutputStreamWriter。
cm.copyOut("COPY friends TO STDOUT WITH DELIMITER AS '|'", fw);
我们使用copyOut()方法将COPY TO STDOUT查询的结果从数据库传递给编写器。 列将以|字符分隔。
$ cat src/main/resources/friends.txt
1|Jane
2|Tom
3|Rebecca
4|Jim
5|Robert
这是创建的文件。
在第二个示例中，我们执行相反的操作。 我们将数据从文件复制到数据库表中。
JavaPostgreSqlCopyFromTextFile.java
package com.zetcode;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;
public class JavaPostgreSqlCopyFromTextFile {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/testdb";
String user = "user12";
String password = "34klq*";
try (Connection con = DriverManager.getConnection(url, user, password)) {
CopyManager cm = new CopyManager((BaseConnection) con);
String fileName = "src/main/resources/friends.txt";
try (FileInputStream fis = new FileInputStream(fileName);
InputStreamReader isr = new InputStreamReader(fis,
StandardCharsets.UTF_8)) {
cm.copyIn("COPY friends FROM STDIN WITH DELIMITER '|'", isr);
}
} catch (SQLException | IOException ex) {
Logger lgr = Logger.getLogger(
JavaPostgreSqlCopyFromTextFile.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
该示例使用FileReader类读取friends.txt表的内容，并使用COPY语句将数据传输到friends类。
String fileName = "src/main/resources/friends.txt";
try (FileInputStream fis = new FileInputStream(fileName);
InputStreamReader isr = new InputStreamReader(fis,
StandardCharsets.UTF_8)) {
我们将从friends.txt文件中读取。
cm.copyIn("COPY friends FROM STDIN WITH DELIMITER '|'", fr);
我们使用COPY语句从文件中复制数据。
testdb=> delete from friends;
DELETE 5
我们从friends表中删除数据。
$ mvn -q exec:java
我们运行程序。
testdb=> select * from friends;
id | name
----+---------
1 | Jane
2 | Tom
3 | Rebecca
4 | Jim
5 | Robert
(5 rows)
我们检查friends表的内容。
这是 PostgreSQL Java 教程。 您可能也对 MySQL Java 教程， Java H2 教程， MongoDB Java 教程或 PostgreSQL Python 教程感兴趣。
Apache Derby 教程
这是 Apache Derby 教程。 本教程将介绍 Derby 数据库引擎，Derby 可以理解的 SQL 以及使用 JDBC 编程 Derby。
目录
Derby
Derby 是用 Java 语言编写的数据库引擎。 它具有 2-3MB 的较小内存。 它以两种模式工作：嵌入式和客户端服务器。
Derby 成立于 1996 年，最初为 JBMS。 后来，它被重命名为 Cloudscape。 1999 年，开发 Cloudscape 的公司被 Informix 收购，后来被 IBM 收购。 2004 年，IBM 将代码贡献给了 Apache 软件基金会。 Derby 项目诞生了。 Sun 加入了该项目，并在 Java 6 中包含了 Derby。该项目在 JDK 中更名为 Java DB。
相关教程
在 EJB 简介中，我们使用 Derby 数据库保存实体。 Tomcat Derby 教程显示了如何使用 Tomcat，Derby 和 NetBeans 创建 Java Web 应用。 在显示数据网格中的数据教程中，我们展示了如何在 EasyUI datagrid 控件中显示来自 Derby 数据库的数据。
在 MySQL 中创建，更改和删除表
在 MySQL 教程的这一部分中，我们将创建，更改和删除表。
我们将使用以下 SQL 语句：
CREATEALTERDROP
CREATE，ALTER和DROP语句不限于表。 我们可以使用它们来创建其他数据库对象，例如事件，触发器，视图，函数或过程。 这些语句是 SQL 规范的数据定义语言（DDL）的一部分。
CREATE和DROP语句
CREATE语句用于创建表。 它还用于创建索引，视图，事件，例程和触发器。
要创建表，我们给表及其列命名。 每列都有一个数据类型。 在上一章中，我们介绍了各种 MySQL 数据类型。 为列选择正确的数据类型是数据库初始设计的一部分。
mysql> CREATE TABLE Testing(Id INTEGER);
我们使用CREATE TABLE语句创建一个简单的测试表。 表名称是Testing。 该表具有称为Id的一列。 列的数据类型为INTEGER。
mysql> SHOW CREATE TABLE Testing;
+---------+------------------------------------------
| Table | Create Table
+---------+------------------------------------------
| Testing | CREATE TABLE `Testing` (
`Id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+------------------------------------------
1 row in set (0.00 sec)
使用SHOW CREATE TABLE语句，我们可以看到创建特定表的确切 SQL 语句。 还有一些默认设置。 如果我们不提供一些明确的属性，则选择这些属性。 如果没有另外指定，默认的 MySQL 引擎是 InnoDB。 （这适用于 MySQL 5.5+）。 默认字符集为 latin1。
mysql> SHOW TABLES LIKE 'T%';
+---------------------+
| Tables_in_mydb (T%) |
+---------------------+
| Testing |
+---------------------+
可以使用SHOW TABLES语法显示已创建的非临时表。 我们可以使用LIKE子句限制显示的表。 在我们的例子中，我们显示所有以T开头的表。
mysql> DROP TABLE Testing;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW TABLES LIKE 'T%';
Empty set (0.00 sec)
DROP TABLE语句从数据库中删除一个表。
mysql> CREATE TABLE Testing(Id INT NOT NULL) ENGINE=MEMORY CHARACTER SET='utf8'
-> COLLATE='utf8_slovak_ci';
我们重新创建Testing表。 INT是INTEGER的同义词。 数据库引擎显式设置为MEMORY。 我们还指定字符集和排序规则。
ALTER TABLE语句
ALTER TABLE语句更改现有表的结构。 可以添加新列，删除列，重命名列和表或更改表的类型。 在下面的示例中，我们将演示一些可能性。
mysql> ALTER TABLE Testing RENAME TO TestTable;
mysql> SHOW TABLES LIKE 'T%';
+---------------------+
| Tables_in_mydb (T%) |
+---------------------+
| TestTable |
+---------------------+
我们使用RENAME TO子句将测试表重命名为TestTable。
mysql> ALTER TABLE TestTable ADD iValues INT;
我们将一个名为iValues的新列添加到表中。
mysql> SHOW COLUMNS FROM TestTable;
+---------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| Id | int(11) | NO | | NULL | |
| iValues | int(11) | YES | | NULL | |
+---------+---------+------+-----+---------+-------+
该语句显示表中的可用列。 我们看到新添加的列。
可以向表中添加约束。
mysql> ALTER TABLE TestTable ADD PRIMARY KEY (Id);
我们向TestTable添加PRIMARY KEY约束。
mysql> DESCRIBE TestTable;
+---------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| Id | int(11) | NO | PRI | NULL | |
| iValues | int(11) | YES | | NULL | |
+---------+---------+------+-----+---------+-------+
DESCRIBE是SHOW COLUMNS FROM的同义词。 我们可以在Key列下看到为 ID 列设置了主键约束。
mysql> ALTER TABLE TestTable CHANGE COLUMN iValues iValues1 INT;
在此 SQL 语句中，我们将列名从iValues更改为iValues1。
mysql> ALTER TABLE TestTable MODIFY COLUMN iValues1 MEDIUMINT;
mysql> DESCRIBE TestTable;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| Id | int(11) | NO | PRI | NULL | |
| iValues1 | mediumint(9) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
我们使用上面的 SQL 语句来修改列定义。 我们将列数据类型从INTEGER更改为MEDIUMINTEGER。
mysql> ALTER TABLE TestTable DROP COLUMN iValues1;
mysql> DESCRIBE TestTable;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| Id | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
在最后一个示例中，我们从表中删除了现有的列。
在 MySQL 教程的这一部分中，我们正在创建，更改和删除表。
Derby 简介
在本章中，我们介绍了 Derby 的基本概念和定义。
Derby 是用 Java 编写的关系数据库管理系统。 它实现了 SQL-92 核心子集以及一些 SQL-99 功能。 它使用 IBM DB2 SQL 语法。 Derby 的占用空间很小，约为 2MB。 它具有事务支持。 Derby 使用的数据库格式是可移植的且与平台无关。
部署选项
Derby 可以两种模式运行：嵌入式或客户端服务器。 在嵌入式模式中，Derby 引擎在应用的 Java 虚拟机中运行。 该应用直接且排他地访问数据库。 没有其他应用可以同时访问数据库。 连接到数据库之前，无需设置 Derby 服务器。 当我们加载嵌入式驱动程序时，将启动 Derby 引擎。
在客户端-服务器模式下，Derby 提供跨网络的多用户连接。 Derby 引擎在服务器的 JVM 中运行。 其他应用可以从不同的 JVM 连接到数据库。 连接到数据库之前，我们必须启动数据库服务器。 这类似于其他 RDBMS，例如 Oracle 或 MySQL。
Derby 系统
Derby 系统由 Derby 引擎及其环境的实例组成。 它包括系统目录，数据库和系统范围的配置。 每个数据库位于系统目录的子目录中，该子目录的名称与数据库相同。 此外，它还有一个错误日志文件和一个可选的derby.properties文件。 Derby 将信息和错误消息写入默认名称为derby.log的日志文件中。 该属性文件包含特定于 Derby 系统的配置数据和参数。 Derby 系统不是持久性的。 我们必须在每次启动时提供系统的位置。
通过derby.system.home属性指定 Derby 系统。 如果属性中指定的目录不存在，则 Derby 会自动创建它。 如果我们未明确指定属性，则使用当前目录。 当前目录是 JVM user.dir属性的值。 建议始终明确指定derby.system.home。
-Dderby.system.home=/home/janbodnar/.derby
可以在命令行，环境变量，对话框（如果使用 IDE 的情况下）或derby.properties文件中设置属性。
Derby 属性
derby.properties是 Derby 系统的配置文件。 它是一个简单的文本文件，使我们能够配置整个 Derby 系统，特定的数据库或企业集团。 企业集团是 Derby 中的表或索引。 我们可以配置几个选项，例如用户授权，要启动的数据库，日志文件的名称，系统目录的位置。 derby.properties不会自动创建； 如果要使用它，我们必须创建该文件。
启动和停止 Derby 数据库
在嵌入式模式下，数据库在应用首次建立连接时启动。 当 Derby 系统以derby.system.bootAll属性开头时，可以将其配置为引导所有可用的数据库。 数据库启动后，将消息写入derby.log文件。 通过在连接 URL 中指定shutdown=true属性来关闭数据库。 我们可以关闭数据库或整个 Derby 系统。
DriverManager.getConnection("jdbc:derby:testdb;shutdown=true");
DriverManager.getConnection("jdbc:derby:;shutdown=true");
第一个 JDBC 调用将关闭testdb数据库。 第二个调用结束整个 Derby 系统。 请注意，系统关闭时会弹出SQLExpection。 此异常应以编程方式处理。 在关闭数据库或系统之前，我们还应该关闭所有现有资源。
在客户机/服务器模式下，Derby 服务器以startNetworkServer脚本启动，以stopNetworkServer脚本结束。 它们位于安装目录的bin子目录中。
JAR 文件
在安装目录的lib子目录中，我们可以找到几个库。 每个 JAR 文件都具有特定功能。
$ ls lib
derbyclient.jar derbyLocale_it.jar derbyLocale_zh_TW.jar
derby.jar derbyLocale_ja_JP.jar derbynet.jar
derbyLocale_cs.jar derbyLocale_ko_KR.jar derbyoptionaltools.jar
derbyLocale_de_DE.jar derbyLocale_pl.jar derbyrun.jar
derbyLocale_es.jar derbyLocale_pt_BR.jar derbytools.jar
derbyLocale_fr.jar derbyLocale_ru.jar derby.war
derbyLocale_hu.jar derbyLocale_zh_CN.jar
derby.jar是用于嵌入式数据库的库。 我们需要在嵌入式应用的类路径中具有此 JAR 文件。 在客户端-服务器模式下，该库必须在服务器上。 derbynet.jar用于启动 Derby 网络服务器。 连接到 Derby Network Server 的客户端使用derbyclient.jar。 derbytools.jar与 Derby 工具一起使用。 derbyrun.jar文件是一个特殊的 jar 文件，可简化我们调用 Derby 工具和网络服务器的方式。 最后，还有一些语言环境库。 它们用于提供翻译后的消息。 例如，derbyLocale_cs.jar提供捷克信息。
Derby 模式
在数据库理论中，模式是数据库的结构。 用正式语言（SQL）描述。 它是指数据的组织。 数据库模式的正式定义是一组公式，称为对数据库施加的完整性约束。
在 Derby 中，数据库模式的含义较窄。 它是用于对对象进行逻辑分组的容器。 它类似于 Java 包或 C++ 名称空间。 Derby 数据库可能具有多个架构。 我们可以创建两个具有相同名称的表，前提是它们被放置在两个不同的模式中。 Derby 的默认架构为 APP。 如果我们不提供任何模式，则会将数据库对象分配给该默认模式。
使用CREATE SCHEMA语句在 Derby 中创建模式。 如果我们连接到数据库，则在连接 URL 中提供的用户名将成为连接的当前模式。 所有数据库对象都将在此架构内创建。 可以使用SET SCHEMA语句更改当前模式。 还有一个称为 SYS 的内置模式，用于隔离系统表。
Derby 有一个名为dblook的工具，可以转储数据库及其架构。 该工具的输出是 DDL（数据定义语言）对数据库的形式化描述。
连接网址
加载驱动程序后，将创建与数据库的连接。 连接是与数据库的会话。 在工作结束时，与数据库的连接已关闭。 为了建立连接，我们调用DriverManager类的getConnection()方法。
连接 URL 指定连接的特征。
jdbc:derby:[subsubprotocol:][databaseName][;attribute=value]*
上面是 Derby 数据库连接 URL 的语法。 默认子协议为directory:，通常会省略。 当指定memory:子协议时，我们只能在内存中使用数据库。 databaseName是我们要创建和/或连接到的数据库的名称。
我们可以在连接 URL 中使用几个属性。 我们可以使用属性来创建数据库，并使用用户名和密码连接到受保护的数据库。 此外，我们使用连接属性来关闭数据库或 Derby 系统，加密数据或从备份还原数据库。
jdbc:derby:testdb
jdbc:derby://localhost:1527/testdb
我们使用不同的连接字符串来连接嵌入式系统和客户端-服务器 Derby 系统。 第一个连接字符串连接到嵌入式数据库，第二个连接字符串连接到客户服务器数据库。 Derby 的默认端口是 1527。
jdbc:derby:testdb;create=true
jdbc:derby:testdb;shutdown=true
jdbc:derby:memory:testdb
我们还有另外三个连接字符串。 第一个连接字符串创建testdb数据库。 第二个关闭testdb数据库。 第三个连接到在内存中创建的testdb。
SQL 文件
在以下两个 SQL 文件cars.sql和authors_books.sql中，我们创建了三个表，将在本教程中使用它们。
cars.sql
-- SQL for the CARS table
SET SCHEMA USER12;
CREATE TABLE CARS(ID BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1), NAME VARCHAR(30), PRICE INT);
INSERT INTO CARS(Name, Price) VALUES('Audi', 52642);
INSERT INTO CARS(Name, Price) VALUES('Mercedes', 57127);
INSERT INTO CARS(Name, Price) VALUES('Skoda', 9000);
INSERT INTO CARS(Name, Price) VALUES('Volvo', 29000);
INSERT INTO CARS(Name, Price) VALUES('Bentley', 350000);
INSERT INTO CARS(Name, Price) VALUES('Citroen', 21000);
INSERT INTO CARS(Name, Price) VALUES('Hummer', 41400);
INSERT INTO CARS(Name, Price) VALUES('Volkswagen', 21600);
cars.sql文件创建CARS表。
authors_books.sql
-- SQL for AUTHORS and BOOKS tables
SET SCHEMA USER12;
CREATE TABLE AUTHORS(ID BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1), NAME VARCHAR(25));
CREATE TABLE BOOKS(ID BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1), AUTHOR_ID BIGINT, TITLE VARCHAR(150),
FOREIGN KEY(AUTHOR_ID) REFERENCES AUTHORS(ID));
INSERT INTO AUTHORS(NAME) VALUES('Jack London');
INSERT INTO AUTHORS(NAME) VALUES('Honore de Balzac');
INSERT INTO AUTHORS(NAME) VALUES('Lion Feuchtwanger');
INSERT INTO AUTHORS(NAME) VALUES('Emile Zola');
INSERT INTO AUTHORS(NAME) VALUES('Truman Capote');
INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(1, 'Call of the Wild');
INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(1, 'Martin Eden');
INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(2, 'Old Goriot');
INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(2, 'Cousin Bette');
INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(3, 'Jew Suess');
INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(4, 'Nana');
INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(4, 'The Belly of Paris');
INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(5, 'In Cold blood');
INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(5, 'Breakfast at Tiffany');
authors_books.sql文件创建两个表：AUTHORS和BOOKS。
数据来源
以下材料用于创建本教程：Derby 开发者指南，Derby 服务器和管理指南，Derby 入门，Derby 工具和工具指南以及 Derby 参考手册。
在本章中，我们介绍了 Derby 数据库的基本概念。
Derby 的安装&配置
在接下来的页面中，我们将展示如何安装 Derby 并根据需要配置 Derby。
我们从 db.apache.org/derby/ 下载了最新版本的 Derby。 我们创建一个安装目录，并将压缩文件解压缩到该目录。
$ wget http://tux.rainside.sk/apache//db/derby/db-derby-10.13.1.1/db-derby-10.13.1.1-bin.zip
使用wget工具，我们下载 Derby 版本 10.13.1。
$ unzip db-derby-10.13.1.1-bin.zip
$ cd db-derby-10.13.1.1-bin/
我们解压缩档案并进入目录。
$ ls
bin docs javadoc lib NOTICE test
demo index.html KEYS LICENSE RELEASE-NOTES.html
我们列出了安装目录的内容。 在bin子目录中，我们有几个 Derby 工具。 docs目录以 HTML 和 PDF 格式提供 Derby 的文档。 在lib子目录中，存在使用 Derby 所需的各种 JAR 文件。
Java 数据库
从 Java 6 开始，DDK 就包含在 JDK 中。Java DB 是 Apache Derby 的受支持版本，并且包含与 Apache Derby 相同的二进制文件。
$ ls $JAVA_HOME/db
3RDPARTY bin lib LICENSE NOTICE README-JDK.html RELEASE-NOTES.html
在 JDK 安装目录中，我们有一个名为db的子目录，可在其中找到 Java DB 文件。
在发行说明的“概述”部分中，我们阅读以下内容： Derby 是使用标准 SQL 和 JDBC 作为其 API 的纯 Java 关系数据库引擎。
Derby 功能包括：
- 具有 JDBC 驱动程序的嵌入式引擎
- 网络服务器
- 网络客户端 JDBC 驱动程序
- 命令行工具：
ij（SQL 脚本），dblook（模式转储）和sysinfo（系统信息）
环境变量
设置环境变量是可选的。 使用环境变量将使我们的生活更轻松。
$ export DERBY_HOME=~/bin/derby
$ export PATH=$PATH:~/bin/derby/bin
$ export DERBY_OPTS=-Dderby.system.home=/home/janbodnar/.derby
我们将DERBY_HOME变量设置为 Derby 安装目录。 某些工具可能会使用此变量。 然后，将bin子目录添加到PATH变量中。 这样，我们不必完全指定 Derby 工具的路径。 在DERBY_OPTS环境变量中，我们可以设置各种 Derby 或 Java 相关选项。 例如，我们设置derby.system.home属性。
$ java -jar $DERBY_HOME/lib/derbyrun.jar sysinfo
------------------ Java Information ------------------
Java Version: 1.8.0_111
Java Vendor: Oracle Corporation
Java home: /home/janbodnar/bin/jdk1.8.0_111/jre
Java classpath: /home/janbodnar/bin/jdk1.8.0_111/db/lib/derbyrun.jar
OS name: Linux
OS architecture: amd64
OS version: 4.4.0-66-generic
...
我们使用derbyrun.jar文件来执行sysinfo工具，以获取有关 Java 和 Derby 的一些信息。
建立数据库
Derby 没有像 MySQL 或 Oracle 这样的CREATE DATABASE语句。 我们必须通过创建连接并设置连接属性create=true来创建数据库。
$ $DERBY_HOME/bin/ij
ij version 10.11
ij>
我们启动ij工具。
ij> CONNECT 'jdbc:derby:tmpdb;user=tmpuser;create=true';
在 Derby 系统目录中创建tmpdb数据库，并与新创建的数据库建立连接。 该连接以嵌入式模式创建。
$ ls ~/.derby/
derby.log testdb tmpdb
该数据库在 Derby 系统目录中创建。
ij> CREATE TABLE FRIENDS(ID INT PRIMARY KEY, NAME VARCHAR(25));
0 rows inserted/updated/deleted
ij> INSERT INTO FRIENDS(ID, NAME) VALUES(1, 'Jane');
1 row inserted/updated/deleted
ij> INSERT INTO FRIENDS(ID, NAME) VALUES(2, 'Thomas');
1 row inserted/updated/deleted
ij> INSERT INTO FRIENDS(ID, NAME) VALUES(3, 'Beky');
1 row inserted/updated/deleted
我们创建一个FRIENDS表，并向其中添加三行。
ij> SELECT * FROM FRIENDS;
ID |NAME
-------------------------------------
1 |Jane
2 |Thomas
3 |Beky
3 rows selected
我们检查数据。
ij> SHOW TABLES IN TMPUSER;
TABLE_SCHEM |TABLE_NAME |REMARKS
------------------------------------------------------------------------
TMPUSER |FRIENDS |
1 row selected
创建连接时，已指定用户名。 用户名是数据库架构，在其中创建FRIENDS表。
ij> DISCONNECT;
ij> SHOW CONNECTIONS;
No connections available.
ij> EXIT;
$
我们从tmpdb数据库断开连接。 SHOW CONNECTIONS语句通知没有打开的连接。 我们使用EXIT命令退出ij工具。
$ rm -rf ~/.derby/tmpdb/
我们从 Derby 系统中删除数据库。 Derby 没有DROP DATABASE语句。
这是 Derby 教程章节的结尾，在该章中，我们已经安装并配置了 Derby。
Derby 工具
在本章中，我们提到了 Derby 工具。 Derby 工具和工具是 Derby 随附的一组脚本。 它们通常用于创建，检查和更新 Derby 数据库。
在此页面中，我们将提及sysinfo，dblook，ij，startNetworkServer和stopNetworkServer工具。
启动 Derby 工具
Derby 工具可以两种方式运行。 我们使用位于 Derby 安装目录的 bin 目录中的脚本名称，也可以使用derbyrun.jar文件启动它们。
$ $DERBY_HOME/bin/ij
$ java -jar $DERBY_HOME/lib/derbyrun.jar ij
我们可以通过在终端中指定脚本名称来启动ij工具。 第二行使用derbyrun.jar文件运行ij。
系统信息
sysinfo工具提供有关操作系统，Java 和 Derby 的信息。 它将打印 Java 版本，Java 主目录，操作系统版本，Java 运行时版本，Derby 版本，当前和支持的语言环境。 该工具对于跟踪 Derby 的某些安装或配置问题很有用。
$ $DERBY_HOME/bin/sysinfo
------------------ Java Information ------------------
Java Version: 1.8.0_111
Java Vendor: Oracle Corporation
Java home: /home/janbodnar/bin/jdk1.8.0_111/jre
Java classpath: /home/janbodnar/bin/jdk1.8.0_111/db/lib/derby.jar:/home/janbodnar/bin/jdk1.8.0_111/db/lib/derbynet.jar:/home/janbodnar/bin/jdk1.8.0_111/db/lib/derbytools.jar:/home/janbodnar/bin/jdk1.8.0_111/db/lib/derbyoptionaltools.jar:/home/janbodnar/bin/jdk1.8.0_111/db/lib/derbyclient.jar
OS name: Linux
OS architecture: amd64
OS version: 4.4.0-66-generic
...
这是特定系统上提供的信息的摘录。
ij
ij是一个交互式脚本工具。 它用于对 Derby 数据库运行脚本或交互式查询。
$ cat cars.sql
SET SCHEMA USER12;
CREATE TABLE CARS(ID BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1), NAME VARCHAR(30), PRICE INT);
INSERT INTO CARS(Name, Price) VALUES('Audi', 52642);
INSERT INTO CARS(Name, Price) VALUES('Mercedes', 57127);
INSERT INTO CARS(Name, Price) VALUES('Skoda', 9000);
INSERT INTO CARS(Name, Price) VALUES('Volvo', 29000);
INSERT INTO CARS(Name, Price) VALUES('Bentley', 350000);
INSERT INTO CARS(Name, Price) VALUES('Citroen', 21000);
INSERT INTO CARS(Name, Price) VALUES('Hummer', 41400);
INSERT INTO CARS(Name, Price) VALUES('Volkswagen', 21600);
我们有一个cars.sql文件，它创建一个数据库模式和一个CARS表。
$ $DERBY_HOME/bin/ij
ij version 10.11
ij> CONNECT 'jdbc:derby:testdb;user=user12;create=true';
我们启动ij工具。 我们创建一个testdb数据库并建立连接。
ij> SHOW CONNECTIONS;
CONNECTION0* - jdbc:derby:testdb
* = current connection
SHOW CONNECTIONS语句显示与 Derby 数据库的打开的连接。
ij> RUN 'cars.sql';
ij> CREATE SCHEMA USER12;
0 rows inserted/updated/deleted
ij> CREATE TABLE CARS(ID INT PRIMARY KEY, NAME VARCHAR(30), PRICE INT);
0 rows inserted/updated/deleted
ij> INSERT INTO CARS VALUES(1, 'Audi', 52642);
1 row inserted/updated/deleted
ij> INSERT INTO CARS VALUES(2, 'Mercedes', 57127);
1 row inserted/updated/deleted
ij> INSERT INTO CARS VALUES(3, 'Skoda', 9000);
...
我们加载并执行cars.sql网站。 我们被告知正在进行的操作。
ij> SELECT * FROM CARS;
ID |NAME |PRICE
------------------------------------------------------
1 |Audi |52642
2 |Mercedes |57127
3 |Skoda |9000
4 |Volvo |29000
5 |Bentley |350000
6 |Citroen |21000
7 |Hummer |41400
8 |Volkswagen |21600
8 rows selected
我们从CARS表中选择所有行。
ij> CONNECT 'jdbc:derby:testdb;shutdown=true';
ERROR 08006: Database 'testdb' shutdown.
在 Derby 中关闭数据库会导致异常。 预期错误 08006。
ij> SHOW CONNECTIONS;
No current connection
连接已关闭。
ij> EXIT;
我们使用EXIT命令退出ij工具。 请注意，每个命令后都有分号。
dblook
dblook工具用于保存数据库对象的数据定义语言，包括表，视图，索引和触发器。
$DERBY_HOME/bin/dblook -d jdbc:derby:testdb
-- Timestamp: 2017-03-13 20:05:43.281
-- Source database is: testdb
-- Connection URL is: jdbc:derby:testdb
-- appendLogs: false
-- ----------------------------------------------
-- DDL Statements for schemas
-- ----------------------------------------------
CREATE SCHEMA "USER12";
-- ----------------------------------------------
-- DDL Statements for tables
-- ----------------------------------------------
CREATE TABLE "USER12"."CARS" ("ID" INTEGER NOT NULL, "NAME" VARCHAR(30), "PRICE" INTEGER);
CREATE TABLE "APP"."CARS" ("ID" BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "NAME" VARCHAR(30), "PRICE" INTEGER);
-- ----------------------------------------------
-- DDL Statements for keys
-- ----------------------------------------------
-- PRIMARY/UNIQUE
ALTER TABLE "APP"."CARS" ADD CONSTRAINT "SQL170313140819740" PRIMARY KEY ("ID");
ALTER TABLE "USER12"."CARS" ADD CONSTRAINT "SQL170313200304680" PRIMARY KEY ("ID");
在上面的示例中，我们已从testdb数据库中转储了对象。 使用-d选项，我们提供了数据库的连接 URL。 在我们的案例中，dblook工具保存了一个数据库架构和一个表。 使用-o选项，可以将输出重定向到文件。
startNetworkServer和stopNetworkServer
这些脚本启动和停止 Derby Network 服务器。 如果是联网服务器，则可以创建到 Derby 数据库的多个连接。
$ $DERBY_HOME/bin/startNetworkServer &
[1] 12421
$ Mon Mar 13 20:12:39 CET 2017 : Security manager installed using the Basic server security policy.
Mon Mar 13 20:12:40 CET 2017 : Apache Derby Network Server - 10.11.1.2 - (1629631) started and ready to accept connections
在这里，我们使用startNetworkServer脚本启动 Derby Network Server。
ij> CONNECT 'jdbc:derby://localhost:1527/testdb';
在这里，我们通过 Derby Network Server 连接到testdb数据库。 网络连接的连接 URL 是不同的。
ij> SELECT * FROM USER12.CARS;
ID |NAME |PRICE
------------------------------------------------------
1 |Audi |52642
2 |Mercedes |57127
3 |Skoda |9000
4 |Volvo |29000
5 |Bentley |350000
6 |Citroen |21000
7 |Hummer |41400
8 |Volkswagen |21600
8 rows selected
我们从CARS表中选择所有汽车。 由于我们没有在连接 URL 中提供数据库模式，因此我们现在必须指定它。 数据库模式是用户名； 在我们的情况下USER12。
$ $DERBY_HOME/bin/stopNetworkServer
Mon Mar 13 20:15:42 CET 2017 : Apache Derby Network Server - 10.11.1.2 - (1629631) shutdown
$ Mon Mar 13 20:15:42 CET 2017 : Apache Derby Network Server - 10.11.1.2 - (1629631) shutdown
我们已经使用stopNetworkServer脚本停止了服务器。
NetworkServerControl
NetworkServerControl是一个系统工具，可以启动和停止 Derby 网络服务器并配置或检索诊断信息。 除ping以外，所有命令只能从运行服务器的计算机上执行。
$ $DERBY_HOME/bin/NetworkServerControl start &
使用start命令，我们启动 Derby 服务器。
$ $DERBY_HOME/bin/NetworkServerControl ping
Tue Mar 21 15:53:29 CET 2017 : Connection obtained for host: localhost, port number 1527.
ping命令测试 Derby 服务器是否已启动。
$ $DERBY_HOME/bin/NetworkServerControl sysinfo
--------- Derby Network Server Information --------
Version: CSS10110/10.11.1.2 - (1629631) Build: 1629631 DRDA Product Id: CSS10110
-- listing properties --
derby.drda.traceDirectory=/home/janbodnar/.derby/
derby.drda.maxThreads=0
derby.drda.sslMode=off
derby.drda.keepAlive=true
...
sysinfo命令提供系统信息。
$ $DERBY_HOME/bin/NetworkServerControl runtimeinfo
--- Derby Network Server Runtime Information ---
---------- Session Information ---------------
Session # :3
-------------------------------------------------------------
# Connection Threads : 1
# Active Sessions : 1
# Waiting Sessions : 0
Total Memory : 78643200 Free Memory : 75359512
runtimeinfo命令提供有关正在运行的网络服务器的会话，线程，预备语句以及内存使用的大量调试信息。
$ $DERBY_HOME/bin/NetworkServerControl shutdown
Tue Mar 21 15:56:43 CET 2017 : Apache Derby Network Server - 10.11.1.2 - (1629631) shutdown
Tue Mar 21 15:56:44 CET 2017 : Apache Derby Network Server - 10.11.1.2 - (1629631) shutdown
shutdown命令停止 Derby 服务器。
在本章中，我们写了有关 Derby 工具的文章。
ij工具
在第四章中，我们将更详细地介绍 Derby 的ij工具。
ij是 Derby 随附的交互式脚本工具。 它是 Derby 数据库系统的命令行客户端。 它可以以两种方式使用：运行 SQL 文件或以交互方式执行 SQL 语句。 ij位于 Derby 安装目录的bin目录中。
$ ls $DERBY_HOME/bin | grep ij
ij
ij.bat
有两个脚本：扩展名为.bat的脚本适用于 Windows。
启动ij
ij可以通过三种基本方式启动。
$ $DERBY_HOME/bin/ij
ij version 10.11
ij>
可以使用ij脚本启动 Derby ij。
$ java -cp $DERBY_HOME/lib/derbytools.jar org.apache.derby.tools.ij
ij version 10.11
ij>
另一种方法是执行已编译的 Java 程序。 我们的类路径中必须有derbytools.jar。 第一种方法在脚本文件中执行的操作基本相同。 它还可以与环境变量一起使用。
$ java -jar $DERBY_HOME/lib/derbyrun.jar ij
ij version 10.11
ij>
在第三种方法中，我们使用derbyrun.jar文件启动ij。
运行 SQL 脚本
与 MySQL 或 PostgreSQL 命令行工具不同，ij非常简单。 它没有命令的历史记录。 不能使用光标键。
$ cat cars.sql
-- SQL for the CARS table
SET SCHEMA USER12;
CREATE TABLE CARS(ID BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1), NAME VARCHAR(30), PRICE INT);
INSERT INTO CARS(Name, Price) VALUES('Audi', 52642);
INSERT INTO CARS(Name, Price) VALUES('Mercedes', 57127);
INSERT INTO CARS(Name, Price) VALUES('Skoda', 9000);
INSERT INTO CARS(Name, Price) VALUES('Volvo', 29000);
INSERT INTO CARS(Name, Price) VALUES('Bentley', 350000);
INSERT INTO CARS(Name, Price) VALUES('Citroen', 21000);
INSERT INTO CARS(Name, Price) VALUES('Hummer', 41400);
INSERT INTO CARS(Name, Price) VALUES('Volkswagen', 21600);
在模式USER12中创建了CARS表，并插入了五行。
ij> RUN 'cars.sql';
ij> -- SQL for the CARS table
SET SCHEMA USER12;
0 rows inserted/updated/deleted
ij> CREATE TABLE CARS(ID BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1), NAME VARCHAR(30), PRICE INT);
0 rows inserted/updated/deleted
ij> INSERT INTO CARS(Name, Price) VALUES('Audi', 52642);
1 row inserted/updated/deleted
ij> INSERT INTO CARS(Name, Price) VALUES('Mercedes', 57127);
1 row inserted/updated/deleted
...
我们使用RUN命令执行cars.sql文件。 该文件位于启动ij的目录中。
ij> SELECT * FROM CARS;
ID |NAME |PRICE
------------------------------------------------------
1 |Audi |52642
2 |Mercedes |57127
3 |Skoda |9000
4 |Volvo |29000
5 |Bentley |350000
6 |Citroen |21000
7 |Hummer |41400
8 |Volkswagen |21600
8 rows selected
我们检查数据。 该表已成功创建。
ij工具可以将 SQL 文件名用作参数。
$ cat cars2.sql
CONNECT 'jdbc:derby://localhost:1527/testdb';
SET SCHEMA USER12;
CREATE TABLE CARS(ID BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1), NAME VARCHAR(30), PRICE INT);
INSERT INTO CARS(Name, Price) VALUES('Audi', 52642);
INSERT INTO CARS(Name, Price) VALUES('Mercedes', 57127);
INSERT INTO CARS(Name, Price) VALUES('Skoda', 9000);
INSERT INTO CARS(Name, Price) VALUES('Volvo', 29000);
INSERT INTO CARS(Name, Price) VALUES('Bentley', 350000);
INSERT INTO CARS(Name, Price) VALUES('Citroen', 21000);
INSERT INTO CARS(Name, Price) VALUES('Hummer', 41400);
INSERT INTO CARS(Name, Price) VALUES('Volkswagen', 21600);
我们将CONNECT语句添加到cars2.sql文件中。 当我们启动ij工具时，我们尚未连接到数据库。
ij> DROP TABLE CARS;
0 rows inserted/updated/deleted
ij> EXIT;
$
我们在执行cars2.sql脚本之前先删除表。
$ java -Dderby.system.home=/home/janbodnar/.derby \
> -jar $DERBY_HOME/lib/derbyrun.jar ij cars2.sql
我们设置 Derby 系统目录，并以cars2.sql作为参数启动ij工具。 再次创建CARS表。
基本命令
我们可以发出两种命令。 ij工具和 SQL 语句专用的命令。 ij中的每个命令都以分号终止。 所有ij命令，标识符和关键字都不区分大小写。
ij> HELP;
Supported commands include:
PROTOCOL 'JDBC protocol' [ AS ident ];
-- sets a default or named protocol
DRIVER 'class for driver'; -- loads the named class
CONNECT 'url for database' [ PROTOCOL namedProtocol ] [ AS connectionName ];
-- connects to database URL
-- and may assign identifier
...
HELP命令显示ij命令的列表
ij> CONNECT 'jdbc:derby://localhost:1527/testdb';
CONNECT命令连接到数据库。 在我们的例子中，数据库名称为testdb。 本示例假定我们已经设置了 Derby 系统目录。 （有关下一部分的更多信息。）默认情况下，Derby 不需要用户名和密码。 我们可以配置 Derby 以要求它。
ij> SHOW CONNECTIONS;
CONNECTION0* - jdbc:derby://localhost:1527/testdb
* = current connection
SHOW CONNECTIONS语句列出了所有打开的连接。 在我们的例子中，我们可以看到与testdb数据库的打开连接。
ij> SHOW TABLES;
TABLE_SCHEM |TABLE_NAME |REMARKS
------------------------------------------------------------------------
SYS |SYSALIASES |
SYS |SYSCHECKS |
SYS |SYSCOLPERMS |
...
SHOW TABLES命令显示数据库中的所有表。 有一些SYS表。
ij> SHOW TABLES IN USER12;
TABLE_SCHEM |TABLE_NAME |REMARKS
------------------------------------------------------------------------
USER12 |CARS |
1 row selected
我们可以列出特定架构中的表。 SHOW TABLES IN USER12显示USER12模式中的表。
ij> DESCRIBE USER12.CARS;
COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
ID |BIGINT |0 |10 |19 |AUTOINCRE&|NULL |NO
NAME |VARCHAR |NULL|NULL|30 |NULL |60 |YES
PRICE |INTEGER |0 |10 |10 |NULL |NULL |YES
3 rows selected
DESCRIBE命令提供对指定表或视图的解密。 如果USER12模式不是连接的当前模式，则必须在表名之前指定它。 当前模式在连接字符串中指定为用户名。
ij> DISCONNECT;
ij> SHOW CONNECTIONS;
No connections available.
DISCONNECT命令与数据库断开连接。 随后的SHOW CONNECTIONS命令显示没有可用的连接。
ij> EXIT;
$
最后，我们退出ij工具。 在嵌入式环境中，它也会关闭数据库。 等效于CONNECT 'jdbc:derby:testdb;shutdown=true';命令。
SQL 语句
ij工具的主要目的是发出 SQL 命令。 我们重新连接到testdb数据库。
$ $DERBY_HOME/bin/ij
ij version 10.11
ij> CONNECT 'jdbc:derby://localhost/testdb;user=USER12';
现在，当前架构为USER12架构。 当为位于USER12模式中的表发布 SQL 语句时，我们可以省略模式名称。
ij> SELECT * FROM CARS WHERE ID IN (1, 3, 5);
ID |NAME |PRICE
------------------------------------------------------
1 |Audi |52642
3 |Skoda |9000
5 |Bentley |350000
3 rows selected
在上面的 SQL 语句中，我们为 ID 为 1、3 和 5 的行选择所有三列。
friends.sql
CREATE TABLE APP.FRIENDS(ID INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1), NAME VARCHAR(25));
INSERT INTO APP.FRIENDS(NAME) VALUES('Jane');
INSERT INTO APP.FRIENDS(NAME) VALUES('Thomas');
INSERT INTO APP.FRIENDS(NAME) VALUES('Beky');
我们在APP模式中有一个FRIENDS表。
ij> SELECT * FROM APP.FRIENDS;
ID |NAME
-------------------------------------
1 |Jane
2 |Thomas
3 |Beka
3 rows selected
从FRIENDS表中选择了三行。 由于该表不在当前模式中，因此我们必须完全限定该表名。
ij> INSERT INTO APP.FRIENDS(Name) VALUES ('Robert');
1 row inserted/updated/deleted
ij> SELECT ID, NAME FROM APP.FRIENDS WHERE ID=4;
ID |NAME
-------------------------------------
4 |Robert
1 row selected
我们在FRIENDS表中插入新行，然后选择它。
指定 Derby 系统目录
Derby 系统目录包含数据库文件，配置数据或日志文件。 如果我们不提供 Derby 系统目录，则假定当前目录为 Derby 系统目录。 为了提供 Derby 系统目录，我们分配了derby.system.home属性。 可以通过 JVM 选项，配置文件，环境变量或 Java 文件来完成。
$ java -Dderby.system.home=/home/janbodnar/.derby \
> -jar $DERBY_HOME/lib/derbyrun.jar ij
ij version 10.11
在这里，我们使用-D JVM 选项指定 Derby 系统目录。 derby.log文件在系统目录中创建。 每次我们连接到 Derby 数据库时，都会重新创建derby.log文件。 我们可以看一下时间戳。 如果derby.log文件出现在预期的目录中，则我们尚未正确设置 Derby 系统目录。
我们可能不想每次都指定 Derby 系统目录。 我们可以利用DERBY_OPTS环境变量。
$ export DERBY_OPTS=-Dderby.system.home=/home/janbodnar/.derby
Derby 将自动尝试连接到指定系统目录中的数据库。
ij属性
启动ij工具时，我们可以在命令行或属性文件中指定属性。 这些属性是ij工具采用的各种参数。 它们可以为我们节省一些重复的工作。
$ java -Dij.user=USER12 -Dij.database=testdb -Dij.protocol=jdbc:derby://localhost/ \
> -Dderby.system.home=/home/janbodnar/.derby \
> -jar $DERBY_HOME/lib/derbyrun.jar ij
ij version 10.11
CONNECTION0* - jdbc:derby://localhost:1527/testdb
* = current connection
ij> SELECT * FROM CARS WHERE ID = 1;
ID |NAME |PRICE
------------------------------------------------------
1 |Audi |52642
1 row selected
ij>
我们使用-D选项在命令行上提供三个ij属性。 ij.user指定用于建立连接的用户名。 提供的用户名将成为当前架构。 ij.database具有我们连接到的数据库名称。 ij.protocol属性指定数据库连接 URL 的默认协议。 我们准备启动 SQL 语句。
在下一个示例中，我们创建一个ij.properties文件，在其中设置三个ij属性。
$ cat ij.properties
ij.user=USER12
ij.database=testdb
ij.protocol=jdbc:derby://localhost:1527/
使用cat命令，显示ij.properties文件的内容。 我们设置与第一个示例相同的属性。
$ java -Dderby.system.home=/home/janbodnar/.derby \
> -jar $DERBY_HOME/lib/derbyrun.jar ij -p ij.properties
ij version 10.11
CONNECTION0* - jdbc:derby://localhost:1527/testdb
* = current connection
ij> SELECT * FROM CARS WHERE ID=2;
ID |NAME |PRICE
------------------------------------------------------
2 |Mercedes |57127
1 row selected
ij工具的-p选项采用属性文件名。
在本章中，我们介绍了ij工具。
Derby 中的 SQL 查询
在本章中，我们将使用 Derby 数据库引擎理解的 SQL。 它是 Derby 中存在的最重要的 SQL 语句的快速列表。
SQL（结构化查询语言） 是一种数据库计算机语言，旨在管理关系数据库管理系统中的数据。 Derby 仅支持一组有限的 SQL 语句。 缺少其他数据库系统已知的一些重要语句。 Derby 实现了 SQL-92 核心子集以及一些 SQL-99 函数。
DROP TABLE
DROP TABLE语句从数据库中删除一个表。
ij> DROP TABLE AUTHORS;
0 rows inserted/updated/deleted
ij> DROP TABLE BOOKS;
0 rows inserted/updated/deleted
假设我们先前已经创建了AUTHORS和BOOKS表，我们将删除它们并再次创建。 DROP TABLE SQL语句从数据库中删除该表。 请注意，DROP TABLE IF EXISTS语句在 Derby 中不存在。
CREATE TABLE
CREATE TABLE语句创建一个新表。
ij> CREATE TABLE AUTHORS(ID BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY
> (START WITH 1, INCREMENT BY 1), NAME VARCHAR(25));
0 rows inserted/updated/deleted
我们用两列创建AUTHORS：ID和NAME。 在ID列中，我们将在NAME列中放置最多 25 个字符的大整数。 PRIMARY KEY唯一标识表中的每个记录。 每个作者都是一个独特的个性。 即使有相同名字的作者，他们每个人都在AUTHORS表中的单独行中。 表中只有一列可以具有此约束。
GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)创建和标识列。 身份列是存储数字的列，该数字在每次插入时都增加一个。 标识列有时称为自动增量列。
ij> CREATE TABLE BOOKS(ID BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY
> (START WITH 1, INCREMENT BY 1), AUTHOR_ID BIGINT, TITLE VARCHAR(150),
> FOREIGN KEY(AUTHOR_ID) REFERENCES AUTHORS(ID));
0 rows inserted/updated/deleted
我们创建一个包含三列的BOOKS表。 FOREIGN KEY指定AUTHOR_ID列中的值必须与AUTHORS表的ID列中的值匹配。 外键提供了一种加强数据库参照完整性的方法。 每本书都是由一个或多个作者撰写的。 因此，在AUTHOR_ID列的BOOKS表中，我们只能拥有AUTHORS表中存在的值。
插入行
INSERT语句用于在数据库表中创建一个或多个行。
ij> INSERT INTO AUTHORS(NAME) VALUES('Jack London');
ij> INSERT INTO AUTHORS(NAME) VALUES('Honore de Balzac');
ij> INSERT INTO AUTHORS(NAME) VALUES('Lion Feuchtwanger');
ij> INSERT INTO AUTHORS(NAME) VALUES('Emile Zola');
ij> INSERT INTO AUTHORS(NAME) VALUES('Truman Capote');
我们使用INSERT INTO SQL 语句向AUTHORS表添加五行。
ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(1, 'Call of the Wild');
ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(1, 'Martin Eden');
ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(2, 'Old Goriot');
ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(2, 'Cousin Bette');
ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(3, 'Jew Suess');
ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(4, 'Nana');
ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(4, 'The Belly of Paris');
ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(5, 'In Cold blood');
ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(5, 'Breakfast at Tiffany');
我们在BOOKS表中插入八行。
ij> SELECT NAME, TITLE FROM AUTHORS, BOOKS
> WHERE AUTHORS.ID = BOOKS.AUTHOR_ID;
NAME |TITLE
-------------------------------------------------
Jack London |Call of the Wild
Jack London |Martin Eden
Honore de Balzac |Old Goriot
Honore de Balzac |Cousin Bette
Lion Feuchtwanger |Jew Suess
Emile Zola |Nana
Emile Zola |The Belly of Paris
Truman Capote |In Cold blood
Truman Capote |Breakfast at Tiffany
9 rows selected
上面的 SQL 查询将两个表连接在一起。 它将每个书名分配给作者。
查询
查询用于从数据库表中查找数据。 SELECT语句是执行查询的主要语句。
限制数据输出
限制数据输出至关重要，因为许多数据库都有成千上万的行。 Derby 不支持其他数据库中已知的LIMIT子句。 Derby 10.7 引入了执行相同函数的FETCH和OFFSET子句。
ij> SELECT * FROM BOOKS FETCH FIRST 4 ROWS ONLY;
ID |AUTHOR_ID |TITLE
-------------------------------------------------
1 |1 |Call of the Wild
2 |1 |Martin Eden
3 |2 |Old Goriot
4 |2 |Cousin Bette
在第一个示例中，我们仅从BOOKS表中提取了前 4 行。
ij> SELECT * FROM BOOKS OFFSET 4 ROWS;
ID |AUTHOR_ID |TITLE
-----------------------------------------------
5 |3 |Jew Suess
6 |4 |Nana
7 |4 |The Belly of Paris
8 |5 |In Cold blood
9 |5 |Breakfast at Tiffany
使用OFFSET数据包，我们跳过了前四行并显示其余行。
ij> SELECT * FROM BOOKS OFFSET 4 ROWS FETCH NEXT 3 ROWS ONLY;
ID |AUTHOR_ID |TITLE
-----------------------------------------------------------------
5 |3 |Jew Suess
6 |4 |Nana
7 |4 |The Belly of Paris
3 rows selected
我们可以使用OFFSET和FETCH子句的组合选择一部分行。
使用WHERE子句选择特定的行
WHERE 子句可用于过滤结果。 它提供了选择条件，仅从数据中选择特定的行。
ij> SELECT * FROM CARS WHERE PRICE > 40000;
ID |NAME |PRICE
------------------------------------------------------
1 |Audi |52642
2 |Mercedes |57127
5 |Bentley |350000
7 |Hummer |41400
4 rows selected
使用WHERE子句，我们仅选择价格高于 40000 的汽车。
ij> SELECT NAME FROM CARS WHERE NAME LIKE '%en';
NAME
------------------------------
Citroen
Volkswagen
2 rows selected
通过LIKE子句，我们选择适合搜索模式的特定汽车名称。 在我们的例子中，汽车以"en"字符结尾。
ij> SELECT * FROM CARS WHERE ID IN (2, 5, 7);
ID |NAME |PRICE
------------------------------------------------------
2 |Mercedes |57127
5 |Bentley |350000
7 |Hummer |41400
3 rows selected
IN子句可用于从特定值范围中选择行。 上面的 SQL 语句返回 ID 等于 2、5 和 7 的行。
ij> SELECT * FROM CARS WHERE PRICE BETWEEN 20000 AND 50000;
ID |NAME |PRICE
------------------------------------------------------
4 |Volvo |29000
6 |Citroen |21000
7 |Hummer |41400
8 |Volkswagen |21600
4 rows selected
我们选择价格在 20000 到 50000 之间的汽车。为此，我们在WHERE子句之后使用BETWEEN AND关键字。
排序数据
可以使用ORDER BY子句完成订购数据。
ij> SELECT * FROM CARS ORDER BY PRICE;
ID |NAME |PRICE
------------------------------------------------------
3 |Skoda |9000
6 |Citroen |21000
8 |Volkswagen |21600
4 |Volvo |29000
7 |Hummer |41400
1 |Audi |52642
2 |Mercedes |57127
5 |Bentley |350000
8 rows selected
我们按价格订购汽车。 默认订单类型为升序。
ij> SELECT * FROM CARS ORDER BY PRICE DESC;
ID |NAME |PRICE
------------------------------------------------------
5 |Bentley |350000
2 |Mercedes |57127
1 |Audi |52642
7 |Hummer |41400
4 |Volvo |29000
8 |Volkswagen |21600
6 |Citroen |21000
3 |Skoda |9000
为了按降序对数据进行排序，我们添加了DESC关键字。
Derby 函数
Derby 支持一些有用的函数。 这些内置函数是使用 SQL 关键字或特殊运算符执行某些操作的表达式。
汇总函数
聚合函数求值一组行上的表达式。 其他内置函数在单个表达式上运行，而聚合在一组值上运行并将它们缩减为单个标量值。 内置的聚合可以计算一组值以及计数行中表达式的最小值，最大值，总和，计数和平均值。
ij> SELECT COUNT(ID) FROM AUTHORS;
1
-----------
5
COUNT()是一个聚合函数，用于计算在表达式中访问的行数。 AUTHORS表中有五位作者。
ij> SELECT MIN(PRICE) AS "PRICE", MAX(PRICE) AS "MAX",
> AVG(PRICE) AS "AVG", SUM(PRICE) AS "SUM" FROM CARS;
PRICE |MAX |AVG |SUM
-----------------------------------------------
9000 |350000 |72721 |581769
1 row selected
在上面的查询中，我们使用其他四个函数：MAX()，MIN()，AVG()和SUM()。 AS子句为列提供标签。
日期和时间函数
日期和时间函数与日期和时间一起使用
ij> VALUES CURRENT_DATE;
1
----------
2017-03-15
ij> VALUES CURRENT SCHEMA;
1
--------------------------
USER12
VALUES CURRENT_DATE返回当前日期。
ij> VALUES CURRENT_TIME;
1
--------
17:22:49
VALUES CURRENT_TIME返回当前时间。
ij> VALUES CURRENT_TIMESTAMP;
1
-----------------------------
2017-03-15 17:29:49.987
VALUES CURRENT_TIMESTAMP返回当前时间戳，即当前日期和时间作为一个值。
字符串函数
Derby 包含可用于字符串的函数。
ij> VALUES LENGTH('Wonderful day');
1
-----------
13
1 row selected
LENGTH()函数返回字符串中的字符数。
ij> VALUES UPPER('derby');
1
-----
DERBY
1 row selected
ij> VALUES LOWER('Derby');
1
-----
derby
1 row selected
UPPER()函数将字符转换为大写字母，LOWER()将字符转换为小写字母。
ij> VALUES SUBSTR('blueberries', 5);
1
-----------
berries
SUBSTR()返回字符串的一部分。 第一个参数是字符串，第二个参数是起始位置。 第一位置的索引为 1。
ij> VALUES SUBSTR('blueberries', 1, 4);
1
----
blue
第三个参数是可选的； 它提供了要返回的子字符串的长度。
数学函数
Derby 包含一些数学函数。
ij> VALUES ABS(-4);
1
-----------
4
ABS()返回数字表达式的绝对值。
ij> VALUES CEIL(3.4), CEIL(3.8);
1
------------------------
4.0
4.0
CEIL()函数将指定的数字四舍五入。
ij> VALUES FLOOR(3.4), FLOOR(3.8);
1
------------------------
3.0
3.0
FLOOR()函数将指定的数字四舍五入。
ij> VALUES COS(0.6), SIN(0.6);
1
------------------------
0.8253356149096783
0.5646424733950354
COS()和SIN()是三角余弦和正弦函数。
ij> VALUES RADIANS(180), DEGREES(3.141592653589793);
1
------------------------
3.141592653589793
180.0
RADIANS()函数将度数转换为弧度，DEGREES()函数将度数转换为弧度。
ij> VALUES SQRT(16.0);
1
------------------------
4.0
SQRT()函数返回浮点数的平方根。
更新和删除数据
现在，我们将关注更新和删除CARS表中的数据。
ij> UPDATE CARS SET PRICE=58000 WHERE ID=2;
1 row inserted/updated/deleted
UPDATE语句用于修改数据库表中的数据。 梅赛德斯汽车的PRICE设置为 58000。
ij> SELECT * FROM CARS WHERE ID=2;
ID |NAME |PRICE
------------------------------------------------------
2 |Mercedes |58000
1 row selected
随后的SELECT语句确认数据的修改。
ij> CREATE TABLE CARS2(ID BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY
> (START WITH 1, INCREMENT BY 1), NAME VARCHAR(30), PRICE INT);
对于下一种情况，我们创建一个新的CARS2表。
ij> INSERT INTO CARS2(NAME, PRICE) SELECT NAME, PRICE FROM CARS;
8 rows inserted/updated/deleted
我们将CARS表中的所有行插入CARS2表中，从而复制所有数据。
ij> SELECT * FROM CARS2;
ID |NAME |PRICE
------------------------------------------------------
1 |Audi |52642
2 |Mercedes |58000
3 |Skoda |9000
4 |Volvo |29000
5 |Bentley |350000
6 |Citroen |21000
7 |Hummer |41400
8 |Volkswagen |21600
8 rows selected
我们检查CARS2表，发现所有数据都在复制 OK。
ij> DELETE FROM CARS2 WHERE ID=8;
1 row inserted/updated/deleted
我们使用DELETE FROM语句删除表中的行。
ij> DELETE FROM CARS2;
7 rows inserted/updated/deleted
没有WHERE子句的DELETE FROM语句删除表中的所有行。
ij> DROP TABLE CARS2;
0 rows inserted/updated/deleted
DROP TABLE语句从数据库中完全删除该表。
RENAME声明
RENAME语句属于 SQL 的数据定义语言。
ij> RENAME TABLE CARS TO MYCARS;
RENAME TABLE语句允许我们重命名现有表。 我们将FRIENDS表重命名为MYFRIENDS。
ij> RENAME COLUMN MYCARS.ID TO CID;
RENAME COLUMN语句重命名特定的表列。
在本章中，我们使用了 Derby 中 SQL 语言的基础知识。
在 Derby 中使用 JDBC 进行编程
在本章中，我们将创建将与 Derby 数据库一起使用的 Java 程序。
JDBC
JDBC 是 Java 编程语言的 API，用于定义客户端如何访问数据库。 它提供了查询和更新数据库中数据的方法。 JDBC 面向关系数据库。 从技术角度来看，API 是java.sql包中的一组类。 要将 JDBC 与特定数据库一起使用，我们需要该数据库的 JDBC 驱动程序。
客户端/服务器和嵌入式 Derby 应用
Derby 可以通过两种基本方式在 Java 应用中使用：客户端/服务器和嵌入式。 对于客户端/服务器应用，我们使用org.apache.derby.jdbc.ClientDriver；对于 Derby 嵌入式应用，我们使用org.apache.derby.jdbc.EmbeddedDriver。
Maven 依赖
Derby 驱动程序有两个 Maven 依赖项：derby和derbynet。 derby依赖关系用于嵌入式应用，derbynet依赖关系用于客户端/服务器应用。
<dependency>
<groupId>org.apache.derby</groupId>
<artifactId>derby</artifactId>
<version>10.13.1.1</version>
</dependency>
这是包含derby驱动程序的 Maven 依赖项。
<dependency>
<groupId>org.apache.derby</groupId>
<artifactId>derbyclient</artifactId>
<version>10.13.1.1</version>
</dependency>
这是包含derbyclient驱动程序的 Maven 依赖项。
连接字符串
客户端/服务器和嵌入式应用的连接字符串不同。
jdbc:derby://localhost:1527/dbname
这是客户端/服务器应用的连接 URL。
jdbc:derby:dbname
这是嵌入式应用的连接 URL。
创建CARS表
在我们的示例中，我们使用嵌入式 Derby 数据库。 在第一个示例中，我们将创建一个CARS表并在其中插入八行。
$ $DERBY_HOME/bin/ij
ij version 10.11
ij> CONNECT 'jdbc:derby:testdb';
ij> DROP TABLE USER12.CARS;
0 rows inserted/updated/deleted
如果在运行示例之前已经创建了CARS表，则应该从数据库中删除该表。
CreateCars.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class CreateCars {
public static void main(String[] args) {
Connection con = null;
Statement st = null;
String url = "jdbc:derby:testdb;user=USER12";
try {
System.setProperty("derby.system.home", "/home/janbodnar/.derby");
con = DriverManager.getConnection(url);
st = con.createStatement();
st.executeUpdate("CREATE TABLE CARS(ID INT PRIMARY KEY,"
+ "NAME VARCHAR(30), PRICE INT)");
st.executeUpdate("INSERT INTO CARS VALUES(1, 'Audi', 52642)");
st.executeUpdate("INSERT INTO CARS VALUES(2, 'Mercedes', 57127)");
st.executeUpdate("INSERT INTO CARS VALUES(3, 'Skoda', 9000)");
st.executeUpdate("INSERT INTO CARS VALUES(4, 'Volvo', 29000)");
st.executeUpdate("INSERT INTO CARS VALUES(5, 'Bentley', 350000)");
st.executeUpdate("INSERT INTO CARS VALUES(6, 'Citroen', 21000)");
st.executeUpdate("INSERT INTO CARS VALUES(7, 'Hummer', 41400)");
st.executeUpdate("INSERT INTO CARS VALUES(8, 'Volkswagen', 21600)");
DriverManager.getConnection("jdbc:derby:;shutdown=true");
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(CreateCars.class.getName());
if (((ex.getErrorCode() == 50000)
&& ("XJ015".equals(ex.getSQLState())))) {
lgr.log(Level.INFO, "Derby shut down normally", ex);
} else {
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
} finally {
try {
if (st != null) {
st.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(CreateCars.class.getName());
lgr.log(Level.WARNING, ex.getMessage(), ex);
}
}
}
}
该示例以嵌入式模式连接到 Derby。 它创建一个CARS表，并向其中添加 8 行。 最终，它关闭了 Derby。
String url = "jdbc:derby:testdb;user=USER12";
这是用于以嵌入式模式和USER12模式连接到testdb数据库的 URL。
System.setProperty("derby.system.home", "/home/janbodnar/.derby");
我们为 Derby 系统目录设置了系统属性。
con = DriverManager.getConnection(url);
创建与 Derby 数据库的连接。 创建连接后，将启动 Derby 数据库。
st.executeUpdate("CREATE TABLE CARS(ID INT PRIMARY KEY,"
+ "NAME VARCHAR(30), PRICE INT)");
st.executeUpdate("INSERT INTO CARS VALUES(1, 'Audi', 52642)");
...
我们执行创建数据库并填充一些数据的 SQL 语句。 对于INSERT，UPDATE和DELETE语句以及类似CREATE TABLE的 DDL 语句，我们使用executeUpdate()方法。
DriverManager.getConnection("jdbc:derby:;shutdown=true");
Derby 数据库引擎已关闭。
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(CreateCars.class.getName());
我们抓到SQLException。 Logger类用于记录错误消息。
if (((ex.getErrorCode() == 50000)
&& ("XJ015".equals(ex.getSQLState())))) {
lgr.log(Level.INFO, "Derby shut down normally", ex);
}
当 Derby 引擎关闭时，将抛出SQLException。 我们捕获此异常并记录一条信息消息。
} finally {
try {
if (st != null) {
st.close();
}
if (con != null) {
con.close();
}
在finally子句中，我们释放资源。
Mar 22, 2017 12:22:15 PM com.zetcode.CreateCars main
INFO: Derby shut down normally
java.sql.SQLException: Derby system shutdown.
...
我们编译并运行该示例。 Derby 的关闭将以SQLException结尾。 这是 Derby 数据库的功能。
检索数据
接下来，我们将展示如何从数据库表中检索数据。 我们从CARS表中获取所有数据。
SelectAllCars.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class SelectAllCars {
public static void main(String[] args) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
String url = "jdbc:derby:testdb";
try {
System.setProperty("derby.system.home", "/home/janbodnar/.derby");
con = DriverManager.getConnection(url);
st = con.createStatement();
rs = st.executeQuery("SELECT * FROM USER12.CARS");
while (rs.next()) {
System.out.print(rs.getInt(1));
System.out.print(" ");
System.out.print(rs.getString(2));
System.out.print(" ");
System.out.println(rs.getString(3));
}
DriverManager.getConnection("jdbc:derby:;shutdown=true");
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(SelectAllCars.class.getName());
if (((ex.getErrorCode() == 50000)
&& ("XJ015".equals(ex.getSQLState())))) {
lgr.log(Level.INFO, "Derby shut down normally", ex);
} else {
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
} finally {
try {
if (rs != null) {
rs.close();
}
if (st != null) {
st.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(SelectAllCars.class.getName());
lgr.log(Level.WARNING, ex.getMessage(), ex);
}
}
}
}
我们从CARS表中获得所有汽车，并将它们打印到控制台。
st = con.createStatement();
rs = st.executeQuery("SELECT * FROM USER12.CARS");
我们执行一个查询，该查询从CARS表中选择所有列。 我们使用executeQuery()方法。 该方法执行给定的 SQL 语句，该语句返回单个ResultSet对象。 ResultSet是 SQL 查询返回的数据表。 还要注意，由于我们尚未在 URL 中指定用户名，因此必须在 SQL 语句中显式提及架构名称。
while (rs.next()) {
System.out.print(rs.getInt(1));
System.out.print(" ");
System.out.print(rs.getString(2));
System.out.print(" ");
System.out.println(rs.getString(3));
}
next()方法将光标移至结果集的下一条记录。 当结果集中没有更多行时，它将返回false。 getInt()和getString()方法检索此ResultSet对象当前行中指定列的值； Java 编程语言中的int和String。
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
8 Volkswagen 21600
Mar 22, 2017 12:28:36 PM com.zetcode.SelectAllCars main
INFO: Derby shut down normally
java.sql.SQLException: Derby system shutdown.
...
我们编译并运行该示例。 我们有testdb数据库的CARS表中的所有汽车的列表。
属性
通常的做法是将配置数据放在程序外部的单独文件中。 我们可以更改用户，密码或连接字符串，而无需重新编译程序。 它在需要大量测试，调试，保护数据等的动态环境中特别有用。
在 Java 中，Properties是经常用于存储基本配置数据的类。 该类用于轻松读取和保存键/值属性。
db.properties
db.url=jdbc:derby:testdb;user=USER12
db.user=USER12
db.passwd=34klq*
db.syshome=/home/janbodnar/.derby
我们有一个db.roperties文件，其中有四个键/值对。 这些是在程序执行期间动态加载的。 该文件位于src/main/resources目录中。
PropertiesExample.java
package com.zetcode;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
public class PropertiesExample {
public static void main(String[] args) {
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
Properties props = new Properties();
FileInputStream in = null;
try {
in = new FileInputStream("src/main/resources/db.properties");
props.load(in);
} catch (FileNotFoundException ex) {
Logger lgr = Logger.getLogger(PropertiesExample.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
} catch (IOException ex) {
Logger lgr = Logger.getLogger(PropertiesExample.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
} finally {
try {
if (in != null) {
in.close();
}
} catch (IOException ex) {
Logger lgr = Logger.getLogger(PropertiesExample.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
String url = props.getProperty("db.url");
String user = props.getProperty("db.user");
String passwd = props.getProperty("db.passwd");
try {
System.setProperty("derby.system.home",
props.getProperty("db.syshome"));
con = DriverManager.getConnection(url, user, passwd);
pst = con.prepareStatement("SELECT * FROM CARS");
rs = pst.executeQuery();
while (rs.next()) {
System.out.print(rs.getInt(1));
System.out.print(": ");
System.out.println(rs.getString(2));
}
DriverManager.getConnection("jdbc:derby:;shutdown=true");
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(PropertiesExample.class.getName());
if (((ex.getErrorCode() == 50000)
&& ("XJ015".equals(ex.getSQLState())))) {
lgr.log(Level.INFO, "Derby shut down normally", ex);
} else {
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
} finally {
try {
if (rs != null) {
rs.close();
}
if (pst != null) {
pst.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(PropertiesExample.class.getName());
lgr.log(Level.WARNING, ex.getMessage(), ex);
}
}
}
}
我们连接到testdb，然后从CARS表中选择所有汽车。 该示例的配置数据是从db.properties文件中读取的。
Properties props = new Properties();
FileInputStream in = null;
try {
in = new FileInputStream("src/main/resources/db.properties");
props.load(in);
创建Properties类。 数据是从名为db.properties的文件中加载的，其中包含我们的配置数据。
String url = props.getProperty("db.url");
String user = props.getProperty("db.user");
String passwd = props.getProperty("db.passwd");
使用getProperty()方法检索这些值。
con = DriverManager.getConnection(url, user, passwd);
请注意，在默认的 Derby 配置中，密码将被忽略。
预备语句
现在，我们将以预备语句来关注自己。 在编写预备语句时，我们使用占位符，而不是直接将值写入语句中。 预准备的语句可提高安全性和性能。
在 Java 中，PreparedStatement是代表预编译的 SQL 语句的对象。
Prepared.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
public class Prepared {
public static void main(String[] args) {
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
String url = "jdbc:derby:testdb;user=USER12";
int price = 58000;
int id = 2;
try {
System.setProperty("derby.system.home", "/home/janbodnar/.derby");
con = DriverManager.getConnection(url);
pst = con.prepareStatement("UPDATE CARS SET PRICE = ? WHERE ID = ?");
pst.setInt(1, price);
pst.setInt(2, id);
pst.executeUpdate();
DriverManager.getConnection("jdbc:derby:;shutdown=true");
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(Prepared.class.getName());
if (((ex.getErrorCode() == 50000)
&& ("XJ015".equals(ex.getSQLState())))) {
lgr.log(Level.INFO, "Derby shut down normally", ex);
} else {
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
} finally {
try {
if (rs != null) {
rs.close();
}
if (pst != null) {
pst.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(Prepared.class.getName());
lgr.log(Level.WARNING, ex.getMessage(), ex);
}
}
}
}
我们更改 ID 等于 2 的汽车的价格。
int price = 58000;
int id = 2;
这些是将要预备语句的值。 这些值可能来自用户，并且来自用户的所有内容都应被视为潜在危险。
pst = con.prepareStatement("UPDATE CARS SET PRICE = ? WHERE ID = ?");
在这里，我们创建一个预备语句。 在编写预备语句时，我们使用占位符，而不是直接将值写入语句中。 预备语句更快，并且可以防止 SQL 注入攻击。 ?是一个占位符，稍后将填充。
pst.setInt(1, price);
pst.setInt(2, id);
值绑定到占位符。
pst.executeUpdate();
执行预备语句。 当我们不希望返回任何数据时，我们使用语句对象的executeUpdate()方法。 这是当我们创建数据库或执行INSERT，UPDATE和DELETE语句时。
ij> SELECT * FROM CARS WHERE ID=2;
ID |NAME |PRICE
------------------------------------------------------
2 |Mercedes |58000
1 row selected
运行示例后，我们使用ij工具检查结果。
列标题
接下来，我们将展示如何使用数据库表中的数据打印列标题。 我们将列名称称为元数据。 元数据是有关数据库中核心数据的数据。
ColumnHeaders.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Formatter;
import java.util.logging.Level;
import java.util.logging.Logger;
public class ColumnHeaders {
public static void main(String[] args) {
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
String url = "jdbc:derby:testdb;user=USER12";
try {
System.setProperty("derby.system.home", "/home/janbodnar/.derby");
con = DriverManager.getConnection(url);
String query = "SELECT NAME, TITLE From AUTHORS, "
+ "Books WHERE AUTHORS.ID=BOOKS.AUTHOR_ID";
pst = con.prepareStatement(query);
rs = pst.executeQuery();
ResultSetMetaData meta = rs.getMetaData();
String colname1 = meta.getColumnName(1);
String colname2 = meta.getColumnName(2);
Formatter fmt1 = new Formatter();
fmt1.format("%-21s%s", colname1, colname2);
System.out.println(fmt1);
while (rs.next()) {
Formatter fmt2 = new Formatter();
fmt2.format("%-21s", rs.getString(1));
System.out.print(fmt2);
System.out.println(rs.getString(2));
}
DriverManager.getConnection("jdbc:derby:;shutdown=true");
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(ColumnHeaders.class.getName());
if (((ex.getErrorCode() == 50000)
&& ("XJ015".equals(ex.getSQLState())))) {
lgr.log(Level.INFO, "Derby shut down normally", ex);
} else {
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
} finally {
try {
if (rs != null) {
rs.close();
}
if (pst != null) {
pst.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(ColumnHeaders.class.getName());
lgr.log(Level.WARNING, ex.getMessage(), ex);
}
}
}
}
在此程序中，我们从AUTHORS表中选择作者，并从BOOKS表中选择他们的书。 我们打印结果集中返回的列的名称。 我们格式化输出。 用于创建表的 SQL 文件位于本教程的第一章中。
String query = "SELECT NAME, TITLE From AUTHORS, "
+ "Books WHERE AUTHORS.ID=BOOKS.AUTHOR_ID";
这是将作者与他们的书联系在一起的 SQL 语句。
ResultSetMetaData meta = rs.getMetaData();
要获取列名，我们需要获取ResultSetMetaData。 它是一个对象，可用于获取有关ResultSet对象中列的类型和属性的信息。 ResultSetMetaData是通过getMetaData()方法从ResultSet获得的。
String colname1 = meta.getColumnName(1);
String colname2 = meta.getColumnName(2);
从获得的元数据中，我们使用getColumnName()方法获得列名。
Formatter fmt1 = new Formatter();
fmt1.format("%-21s%s", colname1, colname2);
System.out.println(fmt1);
我们将列名称打印到控制台。 Formatter对象格式化数据。
while (rs.next()) {
Formatter fmt2 = new Formatter();
fmt2.format("%-21s", rs.getString(1));
System.out.print(fmt2);
System.out.println(rs.getString(2));
}
我们将数据打印到控制台。 我们再次使用Formatter对象来格式化数据。 第一列为 21 个字符，并在左侧对齐。
NAME TITLE
Jack London Call of the Wild
Jack London Martin Eden
Honore de Balzac Old Goriot
Honore de Balzac Cousin Bette
Lion Feuchtwanger Jew Suess
Emile Zola Nana
Emile Zola The Belly of Paris
Truman Capote In Cold blood
Truman Capote Breakfast at Tiffany
Mar 22, 2017 12:52:56 PM com.zetcode.ColumnHeaders main
INFO: Derby shut down normally
java.sql.SQLException: Derby system shutdown.
...
这是示例的输出。
写入图像
有些人喜欢将其图像放入数据库中，有些人则希望将其保留在文件系统中以供其应用使用。 当我们处理大量图像时，会出现技术难题。 图像是二进制数据。 Derby 具有一种特殊的数据类型来存储称为BLOB（二进制大对象）的二进制数据。
我们为此示例和以下示例创建一个名为IMAGES的新表。
ij> CREATE TABLE IMAGES(ID INT PRIMARY KEY, DATA BLOB);
0 rows inserted/updated/deleted
DATA列具有BLOB类型。 在那里，我们将插入编码的二进制数据。
WriteImage.java
package com.zetcode;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
public class WriteImage {
public static void main(String[] args) {
Connection con = null;
PreparedStatement pst = null;
String url = "jdbc:derby:testdb;user=USER12";
try {
System.setProperty("derby.system.home", "/home/janbodnar/.derby");
con = DriverManager.getConnection(url);
File imgFile = new File("woman.jpg");
try (FileInputStream fin = new FileInputStream(imgFile)) {
con = DriverManager.getConnection(url);
pst = con.prepareStatement("INSERT INTO IMAGES(ID, DATA) VALUES(1, ?)");
pst.setBinaryStream(1, fin, (int) imgFile.length());
pst.executeUpdate();
}
DriverManager.getConnection("jdbc:derby:;shutdown=true");
} catch (FileNotFoundException ex) {
Logger lgr = Logger.getLogger(WriteImage.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(WriteImage.class.getName());
if (((ex.getErrorCode() == 50000)
&& ("XJ015".equals(ex.getSQLState())))) {
lgr.log(Level.INFO, "Derby shut down normally", ex);
} else {
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
} catch (IOException ex) {
Logger.getLogger(WriteImage.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
if (pst != null) {
pst.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(WriteImage.class.getName());
lgr.log(Level.WARNING, ex.getMessage(), ex);
}
}
}
}
在此示例中，我们从当前工作目录中读取 JPG 图像，然后插入IMAGES表中。
File imgFile = new File("woman.jpg");
try (FileInputStream fin = new FileInputStream(imgFile)) {
我们为图像文件创建一个File对象。 要从该文件读取字节，我们创建一个FileInputStream对象。
pst = con.prepareStatement("INSERT INTO IMAGES(ID, DATA) VALUES(1, ?)");
该 SQL 语句将图像插入Images表。
pst.setBinaryStream(1, fin, (int) img.length());
二进制流设置为预备语句。 setBinaryStream()方法的参数是要绑定的参数索引，输入流和流中的字节数。
pst.executeUpdate();
我们使用executeUpdate()方法执行该语句。
读取图像
在前面的示例中，我们已将图像插入数据库表中。 现在，我们将从表中读取图像。
ReadImage.java
package com.zetcode;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
public class ReadImage {
public static void main(String[] args) {
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
String url = "jdbc:derby:testdb;user=USER12";
try {
System.setProperty("derby.system.home", "/home/janbodnar/.derby");
System.out.println(System.getProperty("user.dir"));
con = DriverManager.getConnection(url);
String query = "SELECT DATA FROM IMAGES WHERE ID = 1";
pst = con.prepareStatement(query);
rs = pst.executeQuery();
rs.next();
String fileName = "src/main/resources/woman.jpg";
try (FileOutputStream fos = new FileOutputStream(fileName)) {
Blob blob = rs.getBlob("DATA");
int len = (int) blob.length();
byte[] buf = blob.getBytes(1, len);
fos.write(buf, 0, len);
}
DriverManager.getConnection("jdbc:derby:;shutdown=true");
} catch (IOException ex) {
Logger lgr = Logger.getLogger(ReadImage.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(ReadImage.class.getName());
if (((ex.getErrorCode() == 50000)
&& ("XJ015".equals(ex.getSQLState())))) {
lgr.log(Level.INFO, "Derby shut down normally", ex);
} else {
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
} finally {
try {
if (rs != null) {
rs.close();
}
if (pst != null) {
pst.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(ReadImage.class.getName());
lgr.log(Level.WARNING, ex.getMessage(), ex);
}
}
}
}
我们从IMAGES表中读取了一张图像。
String query = "SELECT DATA FROM IMAGES WHERE ID = 1";
选择一条记录。
try (FileOutputStream fos = new FileOutputStream(fileName)) {
创建FileOutputStream对象以写入文件。 它旨在写入原始字节流，例如图像数据。
Blob blob = result.getBlob("DATA");
我们通过调用getBlob()方法从DATA列中获取图像数据。
int len = (int) blob.length();
我们找出斑点数据的长度。 换句话说，我们得到字节数。
byte[] buf = blob.getBytes(1, len);
getBytes()方法以字节数组的形式检索BLOB对象的所有字节。
fos.write(buf, 0, len);
字节被写入输出流。 该映像是在文件系统上创建的。
事务支持
事务是针对一个或多个数据库中数据的数据库操作的基本单位。 事务中所有 SQL 语句的影响可以全部提交给数据库，也可以全部回滚。
创建连接后，它处于自动提交模式。 这意味着每个单独的 SQL 语句都被视为事务，并在执行后立即自动提交。 对于所有 JDBC 驱动程序（包括 Derby 的驱动程序）都是如此。 要开始新的事务，我们关闭自动提交。
在直接 SQL 中，事务以BEGIN TRANSACTION语句开始，并以END TRANSACTION / COMMIT语句结束。 在 Derby 中，这些语句是BEGIN和COMMIT。 但是，在使用驱动程序时，将省略这些语句。 它们由驱动处理。 确切的细节是特定于驱动程序的。 例如，psycopg2 Python 驱动程序在第一个 SQL 语句之后启动事务。 如果要使用自动提交模式，则必须将autocommit属性设置为 True。 相反，默认情况下，JDBC 驱动程序处于自动提交模式。 并且要开始新事务，必须关闭自动提交。
Transaction.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class Transaction {
public static void main(String[] args) {
Connection con = null;
Statement st = null;
String url = "jdbc:derby:testdb;user=USER12";
try {
System.setProperty("derby.system.home", "/home/janbodnar/.derby");
con = DriverManager.getConnection(url);
st = con.createStatement();
con.setAutoCommit(false);
st.executeUpdate("UPDATE AUTHORS SET NAME = 'Leo Tolstoy' "
+ "WHERE Id = 1");
st.executeUpdate("UPDATE BOOKS SET TITLE = 'War and Peace' "
+ "WHERE Id = 1");
st.executeUpdate("UPDATE BOOKS SET TITL = 'Anna Karenina' "
+ "WHERE Id = 2");
con.commit();
DriverManager.getConnection("jdbc:derby:;shutdown=true");
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(Transaction.class.getName());
if (((ex.getErrorCode() == 50000)
&& ("XJ015".equals(ex.getSQLState())))) {
lgr.log(Level.INFO, "Derby shut down normally", ex);
} else {
if (con != null) {
try {
con.rollback();
} catch (SQLException ex1) {
lgr.log(Level.WARNING, ex1.getMessage(), ex1);
}
}
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
} finally {
try {
if (st != null) {
st.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(Transaction.class.getName());
lgr.log(Level.WARNING, ex.getMessage(), ex);
}
}
}
}
在此程序中，我们想在AUTHORS表的第一行中更改作者的姓名。 我们还必须更改与该作者相关的书籍。 如果我们更改作者但不更改作者的书，则数据已损坏。
con.setAutoCommit(false);
要处理事务，必须将autocommit设置为false。 默认情况下，数据库连接处于自动提交模式。 在这种模式下，每条语句在执行后都会立即提交给数据库。 声明无法撤消。 当自动提交关闭时，我们通过调用commit()提交更改，或通过调用rollback()方法将其回滚。
st.executeUpdate("UPDATE BOOKS SET TITL = 'Anna Karenina' "
+ "WHERE Id = 2");
第三个 SQL 语句有一个错误。 BOOKS表中没有TITL列。
con.commit();
如果没有异常，则提交事务。 如果自动提交关闭，则必须显式调用commit()方法。
if (con != null) {
try {
con.rollback();
} catch (SQLException ex1) {
lgr.log(Level.WARNING, ex1.getMessage(), ex1);
}
}
如果发生 Derby 系统关闭以外的异常，则事务将回滚。 没有更改提交到数据库。
Mar 22, 2017 2:00:40 PM com.zetcode.Transaction main
SEVERE: 'TITL' is not a column in table or VTI 'USER12.BOOKS'.
java.sql.SQLSyntaxErrorException: 'TITL' is not a column in table or VTI 'USER12.BOOKS'.
执行失败，并显示'TITL' is not a column in table消息。 引发异常。 事务已回滚，并且未进行任何更改。
ij> CONNECT 'jdbc:derby:testdb';
ij> SET CURRENT SCHEMA = USER12;
ij> SELECT NAME, TITLE FROM AUTHORS, BOOKS WHERE AUTHORS.ID = BOOKS.AUTHOR_ID;
NAME |TITLE
------------------------------------------------------------
Jack London |Call of the Wild
Jack London |Martin Eden
Honore de Balzac |Old Goriot
Honore de Balzac |Cousin Bette
Lion Feuchtwanger |Jew Suess
Emile Zola |Nana
Emile Zola |The Belly of Paris
Truman Capote |In Cold blood
Truman Capote |Breakfast at Tiffany
9 rows selected
数据未损坏。
但是，如果没有事务，数据是不安全的。
NonTransaction.java
package com.zetcode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class NonTransaction {
public static void main(String[] args) {
Connection con = null;
Statement st = null;
String url = "jdbc:derby:testdb;user=USER12";
try {
System.setProperty("derby.system.home", "/home/janbodnar/.derby");
con = DriverManager.getConnection(url);
st = con.createStatement();
st.executeUpdate("UPDATE AUTHORS SET NAME = 'Leo Tolstoy' "
+ "WHERE Id = 1");
st.executeUpdate("UPDATE BOOKS SET TITLE = 'War and Peace' "
+ "WHERE Id = 1");
st.executeUpdate("UPDATE BOOKS SET TITL = 'Anna Karenina' "
+ "WHERE Id = 2");
DriverManager.getConnection("jdbc:derby:;shutdown=true");
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(NonTransaction.class.getName());
if (((ex.getErrorCode() == 50000)
&& ("XJ015".equals(ex.getSQLState())))) {
lgr.log(Level.INFO, "Derby shut down normally", ex);
} else {
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
} finally {
try {
if (st != null) {
st.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(NonTransaction.class.getName());
lgr.log(Level.WARNING, ex.getMessage(), ex);
}
}
}
}
我们有同样的例子。 这次，没有事务支持。
Mar 22, 2017 2:08:40 PM com.zetcode.NonTransaction main
SEVERE: 'TITL' is not a column in table or VTI 'USER12.BOOKS'.
java.sql.SQLSyntaxErrorException: 'TITL' is not a column in table or VTI 'USER12.BOOKS'.
...
ij> CONNECT 'jdbc:derby:testdb';
ij> SET CURRENT SCHEMA = USER12;
ij> SELECT NAME, TITLE FROM AUTHORS, BOOKS WHERE AUTHORS.ID = BOOKS.AUTHOR_ID;
NAME |TITLE
----------------------------------------------------------------
Leo Tolstoy |War and Peace
Leo Tolstoy |Martin Eden
Honore de Balzac |Old Goriot
Honore de Balzac |Cousin Bette
Lion Feuchtwanger |Jew Suess
Emile Zola |Nana
Emile Zola |The Belly of Paris
Truman Capote |In Cold blood
Truman Capote |Breakfast at Tiffany
9 rows selected
再次引发异常。 列夫·托尔斯泰（Leo Tolstoy）没有写马丁·伊登（Martin Eden）：数据已损坏。
批量更新
当我们需要使用多个语句更新数据时，可以使用批处理更新。 批量更新可用于INSERT，UPDATE，DELETE语句以及CREATE TABLE和DROP TABLE语句。
BatchUpdates.java
package com.zetcode;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
public class BatchUpdates {
public static void main(String[] args) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
String url = "jdbc:derby:testdb;user=USER12";
try {
System.setProperty("derby.system.home", "/home/janbodnar/.derby");
con = DriverManager.getConnection(url);
con.setAutoCommit(false);
st = con.createStatement();
st.addBatch("DELETE FROM CARS");
st.addBatch("INSERT INTO CARS VALUES(1, 'Audi', 52642)");
st.addBatch("INSERT INTO CARS VALUES(2, 'Mercedes', 57127)");
st.addBatch("INSERT INTO CARS VALUES(3, 'Skoda', 9000)");
st.addBatch("INSERT INTO CARS VALUES(4, 'Volvo', 29000)");
st.addBatch("INSERT INTO CARS VALUES(5, 'Bentley', 350000)");
st.addBatch("INSERT INTO CARS VALUES(6, 'Citroen', 21000)");
st.addBatch("INSERT INTO CARS VALUES(7, 'Hummer', 41400)");
st.addBatch("INSERT INTO CARS VALUES(8, 'Volkswagen', 21600)");
st.addBatch("INSERT INTO CARS VALUES(9, 'Jaguar', 95000)");
int counts[] = st.executeBatch();
con.commit();
System.out.println("Committed " + counts.length + " updates");
DriverManager.getConnection("jdbc:derby:;shutdown=true");
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(BatchUpdates.class.getName());
if (((ex.getErrorCode() == 50000)
&& ("XJ015".equals(ex.getSQLState())))) {
lgr.log(Level.INFO, "Derby shut down normally", ex);
} else {
if (con != null) {
try {
con.rollback();
} catch (SQLException ex1) {
lgr.log(Level.WARNING, ex1.getMessage(), ex1);
}
}
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
} finally {
try {
if (rs != null) {
rs.close();
}
if (st != null) {
st.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(BatchUpdates.class.getName());
lgr.log(Level.WARNING, ex.getMessage(), ex);
}
}
}
}
这是用于批处理更新的示例程序。 我们从CARS表中删除所有行，并在其中插入 9 行。
con.setAutoCommit(false);
进行批处理更新时，应始终关闭自动提交。
st.addBatch("DELETE FROM CARS");
st.addBatch("INSERT INTO CARS VALUES(1, 'Audi', 52642)");
st.addBatch("INSERT INTO CARS VALUES(2, 'Mercedes', 57127)");
st.addBatch("INSERT INTO CARS VALUES(3, 'Skoda', 9000)");
...
我们使用addBatch()方法向该语句添加新命令。
int counts[] = st.executeBatch();
添加所有命令后，我们调用executeBatch()进行批量更新。 该方法返回已提交更改的数组。
con.commit();
批处理更新在事务中提交。
ij> SELECT * FROM CARS;
ID |NAME |PRICE
------------------------------------------------------
1 |Audi |52642
2 |Mercedes |57127
3 |Skoda |9000
4 |Volvo |29000
5 |Bentley |350000
6 |Citroen |21000
7 |Hummer |41400
8 |Volkswagen |21600
9 |Jaguar |95000
我们已经成功地重新创建了CARS表。
在本章中，我们使用 Java 和 Derby 进行了一些 JDBC 编程。
Derby 安全
在下一章中，我们将提到 Derby 的安全性选项。
我们将在本章中简要提及两个基本安全概念。 用户认证和用户授权。 用户认证正在验证用户凭据，然后再授予对 Derby 系统的访问权限。 用户授权是授予读取和/或写入 Derby 数据库权限的手段。
此外，Derby 允许对磁盘上存储的数据库文件进行加密。 Derby 网络流量可以使用 SSL/TLS 加密协议进行加密。
Derby 默认
默认情况下，Derby 不需要用户认证。 用户名成为程序中的默认架构，并且用户密码将被忽略。 要启用认证，我们必须修改 Derby 属性。 用户授权已关闭。 另外，Derby 没有数据库超级用户。
数据库所有者
数据库所有者是创建数据库的用户。 如果创建数据库时没有提供用户，则数据库所有者将设置为默认授权标识符 APP。 当我们启用 SQL 授权时，控制数据库所有者很重要。
数据库加密
Derby 为我们提供了一种加密磁盘上数据的方法。 引导数据库的用户必须提供启动密码。 创建数据库时可以对其进行加密。 也可以加密现有的未加密数据库。 在加密数据库时，还必须指定启动密码，该密码是用于生成加密键的字母数字字符串。
ij> CONNECT 'jdbc:derby:testdb;create=true;dataEncryption=true;
bootPassword=3344kkllqq**';
创建数据库时，我们可以对其进行加密。 我们将dataEncryption属性设置为true，并提供启动密码。 现在，每次启动数据库时，我们都必须提供启动密码。
ij> CONNECT 'jdbc:derby:testdb';
ERROR XJ040: Failed to start database 'testdb' with class loader
sun.misc.Launcher$AppClassLoader@360be0, see the next exception for details.
ERROR XBM06: Startup failed. An encrypted database cannot be accessed without
the correct boot password.
在嵌入式模式下，当我们连接到数据库时，我们还将引导它。 当我们尝试在没有启动密码的情况下连接到加密数据库时，Derby 将显示以上错误消息。
ij> CONNECT 'jdbc:derby:testdb;bootPassword=3344kkllqq**';
ij> SHOW CONNECTIONS;
CONNECTION0* - jdbc:derby:testdb
* = current connection
使用正确的启动密码，我们已成功连接到testdb数据库。
认证方式
认证限制了对正确用户的访问。 默认情况下，Derby 中的认证处于关闭状态。
Derby 通过三种方式提供认证。
- LDAP 外部认证
- 自定义 Java 类
- 内置系统
Derby 官方文档警告说 Derby 的内置认证机制仅适用于开发和测试目的。 强烈建议生产系统依赖 LDAP 或用户定义的类进行认证。
嵌入
认证可以设置为两个级别。 在系统级别或数据库级别。
ij> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.user12', '34klq*');
0 rows inserted/updated/deleted
ij> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.connection.requireAuthentication',
'true');
0 rows inserted/updated/deleted
以上两个语句可在数据库级别为当前连接的数据库启用用户认证。 我们使用密码创建了一个用户，并启用了derby.connection.requireAuthentication属性。
ij> CONNECT 'jdbc:derby:testdb';
ERROR 08004: Connection authentication failure occurred. Reason: Invalid authentication..
ij> CONNECT 'jdbc:derby:testdb;user=user12;password=34klq*';
ij> SHOW CONNECTIONS;
CONNECTION0* - jdbc:derby:testdb
* = current connection
启用用户认证后，当我们要连接到testdb数据库时，我们必须提供用户凭据。
客户端服务器
在下一个示例中，我们将在客户端/服务器模式下使用 Derby。 我们有一个加密的testdb数据库。
$ startNetworkServer &
Derby 服务器已启动。
ij> CONNECT 'jdbc:derby://localhost:1527/dbs/testdb;bootPassword=3344kkllqq**';
首次连接testdb数据库时，必须提供启动密码。 这是因为以前我们已经加密了testdb数据库。
ij> CONNECT 'jdbc:derby://localhost:1527/dbs/testdb';
ij> SHOW CONNECTIONS;
CONNECTION0* - jdbc:derby://localhost:1527/dbs/testdb
* = current connection
一旦数据库已经启动，我们就不需要以客户端/服务器模式启动数据库。 与嵌入式模式不同，在嵌入式模式下，我们每次都连接到数据库时，也会对其进行引导。
在下一步中，我们将在客户端/服务器模式下启用用户认证。 为此，我们需要编辑derby.properties文件。
$ stopNetworkServer
首先，如果 Derby 服务器正在运行，我们将其停止。 请注意，启用用户认证后，我们需要提供用户凭据才能停止服务器。 stopNetworkServer脚本带有-user和-password选项。
$ cat dbs/derby.properties
derby.connection.requireAuthentication=true
derby.user.user12=34klq*
derby.authentication.provider=BUILTIN
在 Derby 系统目录中，我们修改derby.properties文件。 如果文件不存在，我们将创建它。 在属性文件中，我们启用身份验证并使用密码创建用户。 我们还将认证供应器设置为 Derby BUILTIN。
$ startNetworkServer &
我们启动 Derby 服务器。
$ java -Dderby.system.home=/home/janbodnar/programming/derby/dbs \
-Dij.protocol=jdbc:derby: -jar $DERBY_HOME/lib/derbyrun.jar ij
ij version 10.8
ij>
我们启动ij工具。
ij> CONNECT 'jdbc:derby:testdb;bootPassword=3344kkllqq**';
ERROR 08004: Connection authentication failure occurred. Reason: Invalid authentication..
我们尝试连接到testdb数据库。 由于 Derby 服务器已重新启动，因此我们提供了启动密码。 但是，我们看到一条错误消息。 这是因为我们启用了用户认证。 我们还必须提供用户凭据。
ij> CONNECT 'jdbc:derby:testdb;user=user12;password=34klq*;
bootPassword=3344kkllqq**';
使用此连接字符串，我们已成功连接到testdb数据库。
用户授权
通过 Derby 中的用户授权，可以授予和撤消访问系统，数据库，对象或 SQL 操作的权限。 我们可以在 Derby 中将用户授权属性设置为系统级属性或数据库级属性。
Derby 具有一些影响用户授权的属性。 derby.database.defaultConnectionMode属性控制默认访问模式。 如果未设置该属性，则该属性默认为fullAccess，即具有读写访问权限。 其他两个选项是noAccess和readOnlyAccess。 通过derby.database.fullAccessUsers和derby.database.readOnlyAccessUsers，我们可以控制哪些用户可以读写，哪些用户可以对数据库进行只读访问。 derby.database.sqlAuthorization属性启用 SQL 标准授权。 当derby.database.sqlAuthorization属性设置为true时，对象所有者可以使用GRANT和REVOKE SQL语句来设置特定数据库对象或特定 SQL 操作的用户权限。
我们可以授予或撤销的特权是：DELETE，EXECUTE，INSERT，SELECT，REFERENCES，TRIGGER和UPDATE。
为derby.database.defaultConnectionMode属性指定的访问模式将覆盖数据库对象所有者授予的权限。
$ cat dbs/derby.properties
derby.connection.requireAuthentication=true
derby.user.user12=34klq*
derby.user.user13=33kl33
derby.user.user14=14kl14
derby.user.user15=35rr++
derby.authentication.provider=BUILTIN
derby.database.defaultConnectionMode=readOnlyAccess
derby.database.fullAccessUsers=user12
我们修改derby.properties文件。 我们添加了三个用户。 一个用户user12拥有对数据库的完全访问权限。 其他三个具有默认的只读访问权限。
export DERBY_OPTS=-Dderby.system.home=/home/janbodnar/programming/derby/dbs
请注意，为了使网络服务器知道带有derby.property的系统目录在哪里，我们将DERBY_OPTS变量设置为包含 derby 系统目录。
$ stopNetworkServer
$ startNetworkServer &
$ java -Dderby.system.home=/home/janbodnar/programming/derby/dbs \
-Dij.protocol=jdbc:derby: -jar $DERBY_HOME/lib/derbyrun.jar ij
我们重新启动网络服务器并启动ij工具。
ij> CONNECT 'jdbc:derby://localhost/testdb;user=user13;
password=33kl33;bootPassword=3344kkllqq**';
我们使用user13用户连接到testdb数据库。 由于我们是第一次连接数据库，因此我们也将其引导。 因此，我们需要启动密码，因为该数据库先前已加密。
ij> SELECT * FROM USER12.CARS;
ID |NAME |PRICE
------------------------------------------------------
1 |Audi |52642
2 |Mercedes |57127
3 |Skoda |9000
4 |Volvo |29000
5 |Bentley |350000
6 |Citroen |21000
7 |Hummer |41400
8 |Volkswagen |21600
8 rows selected
user13有权查看位于USER12模式中的CARS表中的数据。
ij> INSERT INTO USER12.CARS VALUES(9, 'Toyota', 27000);
ERROR 25502: An SQL data change is not permitted for a read-only connection,
user or database.
但是，尝试修改CARS表中的数据会导致错误。 未授予执行更改的权限。
ij> DISCONNECT;
ij> CONNECT 'jdbc:derby://localhost/testdb;user=user12;
password=34klq*';
我们关闭连接，并连接为user12。 该用户在属性文件中具有完全访问权限。 即使user12是数据库的所有者和CARS表的所有者，他也不能修改表，除非使用 Derby 属性具有完全访问权限。
ij> INSERT INTO CARS VALUES(9, 'Toyota', 27000);
1 row inserted/updated/deleted
ij> SELECT * FROM CARS WHERE ID = 9;
ID |NAME |PRICE
------------------------------------------------------
9 |Toyota |27000
1 row selected
我们已经成功地在CARS表中添加了新行。
SQL 授权
数据库或表之类的对象的所有者可以进一步限制使用数据库对象的权限。 我们可以使用GRANT和REVOKE语句来授予或撤消权限。 数据库和表的所有者是创建它们的当前用户。 请注意，derby.database.defaultConnectionMode会覆盖GRANT语句赋予的权限。 因此，如果用户具有通过默认连接方式指定的readOnlyAccess，则即使GRANT语句授予了该权限，该用户也无法修改数据库对象。
当derby.database.sqlAuthorization属性设置为true时，对象所有者可以使用GRANT和REVOKE SQL语句来设置特定数据库对象或特定 SQL 操作的用户权限。 请注意，在derby.properties文件中设置系统范围的属性仅对新数据库有效。 对于现有数据库，我们只能设置数据库范围的derby.database.sqlAuthorization属性。 在将derby.database.sqlAuthorization属性设置为true之后，我们无法将该属性设置回false。
ij> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.sqlAuthorization',
'true');
derby.database.sqlAuthorization属性已设置为 true。 该属性是静态的。 我们必须重新启动testdb数据库以使该属性正常工作。
ij> CONNECT 'jdbc:derby://localhost/testdb;shutdown=true;
user=user12;password=34klq*';
ij> CONNECT 'jdbc:derby://localhost/testdb;user=user12;
password=34klq*;bootPassword=3344kkllqq**';
我们关闭了testdb数据库，然后重新启动它。
ij(CONNECTION1)> GRANT SELECT ON CARS TO user15;
0 rows inserted/updated/deleted
我们向表CARS的user15提供SELECT特权。
ij(CONNECTION1)> UPDATE CARS SET PRICE=27001 WHERE ID=9;
1 row inserted/updated/deleted
ij(CONNECTION1)> SELECT * FROM CARS;
ID |NAME |PRICE
------------------------------------------------------
1 |Audi |52642
2 |Mercedes |57127
3 |Skoda |9000
4 |Volvo |29000
5 |Bentley |350000
6 |Citroen |21000
7 |Hummer |41400
8 |Volkswagen |21600
9 |Toyota |27001
作为表所有者的user12具有完全特权。 上面的命令确认他在CARS表上具有UPDATE和SELECT特权。
ij(CONNECTION1)> DISCONNECT;
ij> CONNECT 'jdbc:derby://localhost/testdb;user=user14;
password=14kl14';
ij(CONNECTION1)> SELECT * FROM USER12.CARS;
ERROR 42502: User 'USER14' does not have SELECT permission
on column 'ID' of table 'USER12'.'CARS'.
我们从数据库断开连接，并以user14身份连接。 尝试执行SELECT语句会导致错误。 user14不具有对CARS表中SELECT数据的特权。
ij(CONNECTION1)> DISCONNECT;
ij> CONNECT 'jdbc:derby://localhost/testdb;user=user15;
password=35rr++';
ij> SELECT * FROM USER12.CARS;
ID |NAME |PRICE
------------------------------------------------------
1 |Audi |52642
2 |Mercedes |57127
3 |Skoda |9000
4 |Volvo |29000
5 |Bentley |350000
6 |Citroen |21000
7 |Hummer |41400
8 |Volkswagen |21600
9 |Toyota |27000
8 rows selected
接下来，我们以user15连接。 用户可以从CARS表中选择数据。
ij(CONNECTION1)> SELECT * FROM USER12.AUTHORS;
ERROR 42502: User 'USER15' does not have SELECT
permission on column 'ID' of table 'USER12'.'AUTHORS'.
但是他不能从AUTHORS表中选择数据。 表所有者user12没有授予从该表中选择数据的权限。
ij(CONNECTION1)> UPDATE USER12.CARS SET PRICE=27000 WHERE ID=9;
ERROR 25502: An SQL data change is not permitted for a read-only
connection, user or database.
user15在CARS表上也没有UPDATE特权。
在本章中，我们讨论了 Derby 中的安全性选项。
使用 Derby & Apache Tomcat
在本章中，我们将展示如何将 Derby 与 Apache Tomcat Web 容器组合在一起。
Apache Tomcat 是 Java 编程语言的 Web 容器。 它用于服务 JSP 页面和 servlet。 可以轻松地将 Apache Tomcat 与 Derby 一起使用。 Derby 在其lib子目录中有一个derby.war文件。 该 Web 存档仅用于控制 Derby 数据库。 Tomcat 和 Derby 都是 Apache Software Foundation 的项目。
$ pwd
/home/janbodnar/bin/tomcat
$ ls lib/derby*
lib/derbyclient.jar lib/derby.jar lib/derbynet.jar
首先，我们必须将derbyclient.jar，derby.jar和derbynet.jar文件复制到 Tomcat 安装目录的lib子目录中。
$ ls webapps
derby.war docs examples host-manager manager ROOT
然后，我们必须将 derby.war 文件复制到 Tomcat 安装目录的webapps子目录文件中。 Tomcat 启动时，将解压缩并部署文件。
$ export JAVA_OPTS=-Dderby.system.home=/home/janbodnar/programming/derby/dbs
当我们通过 Tomcat 启动 Derby 时，不考虑DERBY_OPTS变量。 在启动 Tomcat 和 Derby 服务器之前，必须先设置derby.system.home。 我们可以在JAVA_OPTS变量中设置 Derby 系统目录。
$ bin/startup.sh
Using CATALINA_BASE: /home/janbodnar/bin/tomcat
Using CATALINA_HOME: /home/janbodnar/bin/tomcat
Using CATALINA_TMPDIR: /home/janbodnar/bin/tomcat/temp
Using JRE_HOME: /home/janbodnar/bin/jdk1.6.0_30
Using CLASSPATH: /home/janbodnar/bin/tomcat/bin/bootstrap.jar:
/home/janbodnar/bin/tomcat/bin/tomcat-juli.jar
使用startup.sh脚本启动 Tomcat 服务器。
图：Tomcat 启动页面
导航到localhost:8080，这是 Tomcat 监听的默认 URL，我们会看到 Tomcat 欢迎页面。
图：Derby 启动
要启动 Derby 数据库，我们导航到localhost:8080/derby/derbynet。 这将启动 Derby。 我们有几个按钮可用于启动/停止服务器，启用/禁用日志记录或跟踪。
<load-on-startup>0</load-on-startup>
每次启动 Tomcat 服务器时，我们都必须导航至上述 URL。 要自动启动 Derby，我们可以在web.xml文件的<servlet>标记内添加以上行。 该文件位于webapps/derby/WEB-INF目录中。
创建测试数据库
对于那些从一开始就没有遵循教程的人，我们将再次创建testdb数据库。 我们将一个表添加到数据库中。 您可以跳过数据库和表（如果已经存在）的创建。
$ cat cars.sql
CREATE SCHEMA USER12;
CREATE TABLE CARS(ID INT PRIMARY KEY, NAME VARCHAR(30), PRICE INT);
INSERT INTO CARS VALUES(1, 'Audi', 52642);
INSERT INTO CARS VALUES(2, 'Mercedes', 57127);
INSERT INTO CARS VALUES(3, 'Skoda', 9000);
INSERT INTO CARS VALUES(4, 'Volvo', 29000);
INSERT INTO CARS VALUES(5, 'Bentley', 350000);
INSERT INTO CARS VALUES(6, 'Citroen', 21000);
INSERT INTO CARS VALUES(7, 'Hummer', 41400);
INSERT INTO CARS VALUES(8, 'Volkswagen', 21600);
我们将需要此 SQL 文件。
$ cat dbs/derby.properties
derby.stream.error.logSeverityLevel=0
derby.database.fullAccessUsers=user12
derby.database.defaultConnectionMode=readOnlyAccess
derby.connection.requireAuthentication=true
derby.user.user12=34klq*
derby.user.user13=33kl33
derby.user.user14=14kl14
derby.user.user15=35rr++
derby.authentication.provider=builtin
在 Derby 系统目录中，我们有derby.properties文件。 在此文件中，我们配置一些选项。 我们将日志严重性级别设置为 0 以报告所有可能的问题。 这是在测试环境中完成的。 我们启用身份验证。 我们使用相应的密码创建四个用户。 用户 12 中只有一个拥有完全访问权限。 其他人只有readOnlyAccess。
$ java -Dderby.system.home=/home/janbodnar/programming/derby/dbs \
-Dij.protocol=jdbc:derby: -jar $DERBY_HOME/lib/derbyrun.jar ij
ij version 10.8
ij>
我们启动ij命令行工具。 我们将使用它来创建数据库和表。 Derby 系统目录位于/home/janbodnar/programming/derby/dbs。
ij> CONNECT 'jdbc:derby://localhost:1527/testdb;create=true;
user=user12;password=34klq*';
我们创建testdb数据库并连接到它。 我们提供用户凭证。
ij> run 'cars.sql';
我们执行cars.sql脚本，该脚本创建CARS表并将其填充数据。
ij> SELECT * FROM CARS;
ID |NAME |PRICE
------------------------------------------------------
1 |Audi |52642
2 |Mercedes |57127
3 |Skoda |9000
4 |Volvo |29000
5 |Bentley |350000
6 |Citroen |21000
7 |Hummer |41400
8 |Volkswagen |21600
8 rows selected
这是我们的CARS表。 接下来，我们将创建一个 Java servlet，它将在 Web 浏览器中显示这些值。
项目
我们将创建一个简单的 Web 应用，该应用将连接到 Derby 数据库。 一个 Java Servlet 将连接到 Derby，并从CARS表中检索所有数据。
$ tree
.
├── build.xml
├── context.xml
├── lib
│ └── servlet-api.jar
├── src
│ └── zetcode
│ └── SelectAllCars.java
└── web.xml
3 directories, 5 files
在当前工作目录中，我们有一个 Ant build.xml文件，context.xml配置文件，web.xml部署描述符文件以及src和lib子目录。 build.xml文件是 Ant 构建文件，它描述了构建，部署或清理项目的任务。 web.xml定义了 Web 应用的结构。 在lib目录中，有servlet-api.jar文件，用于编译源文件。 （可以在 Tomcat 安装目录的lib子目录中找到它。）在src目录中，我们有 Java 源文件。
web.xml文件定义 Web 应用的结构。
<?xml version="1.0" encoding="UTF8"?>
<web-app xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
version="3.0"
metadata-complete="true">
<description>
Servlet which connects to Derby
</description>
<display-name>Derby, Tomcat</display-name>
<servlet>
<servlet-name>SelectAllCars</servlet-name>
<servlet-class>zetcode.SelectAllCars</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>SelectAllCars</servlet-name>
<url-pattern>/SelectAllCars</url-pattern>
</servlet-mapping>
</web-app>
这些是web.xml文件的内容。 在此文件中，我们注册SelectAllCars servlet。
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource name="jdbc/testdb"
auth="Container"
type="javax.sql.DataSource"
username="user12"
password="34klq*"
driverClassName="org.apache.derby.jdbc.ClientDriver"
url="jdbc:derby://localhost:1527/testdb"
maxActive="10"
maxIdle="4"/>
</Context>
在context.xml文件中，我们定义 JDBC 数据源。 可以为所有 Web 应用或单个应用定义context.xml文件。 后者是我们的情况。
我们将显示 Ant 构建文件，该文件将用于构建和部署我们的小型应用。
<?xml version="1.0" ?>
<project name="allcars" default="deploy">
<property name="src.dir" value="src"/>
<property name="build.dir" value="build"/>
<property name="dist.dir" value="dist"/>
<property name="deploy.dir" value="/home/janbodnar/bin/tomcat/webapps"/>
<echo>${ant.project.name}</echo>
<target name="init">
<mkdir dir="${build.dir}/classes" />
<mkdir dir="${dist.dir}"/>
<echo>Directories created.</echo>
</target>
<target name="compile" depends="init">
<javac srcdir="${src.dir}" destdir="${build.dir}/classes"
includeantruntime="false">
<classpath path="lib/servlet-api.jar"/>
</javac>
<echo>Source files compiled.</echo>
</target>
<target name="archive" depends="compile">
<war destfile="${dist.dir}/${ant.project.name}.war" webxml="web.xml">
<classes dir="${build.dir}/classes"/>
<metainf file="context.xml"/>
</war>
<echo>Archive created.</echo>
</target>
<target name="deploy" depends="archive">
<copy file="${dist.dir}/${ant.project.name}.war" todir="${deploy.dir}"/>
<echo>Project deployed.</echo>
</target>
<target name="clean">
<delete dir="${dist.dir}"/>
<delete dir="${build.dir}"/>
<echo>Project cleaned.</echo>
</target>
</project>
构建文件包括五个任务。 初始化任务将创建必要的目录。 编译任务将编译源代码。 存档任务将创建一个网络存档。 deploy 任务会将归档文件部署到 Tomcat 服务器。 最后，清洁任务将进行清洁。
以下是SelectAllCars Servlet。
package zetcode;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
public class SelectAllCars extends HttpServlet {
protected void processRequest(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = null;
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
out = response.getWriter();
Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/testdb");
con = ds.getConnection();
st = con.createStatement();
out.println("<html>");
out.println("<head>");
out.println("<title>SimpleServlet</title>");
out.println("</head>");
out.println("<body>");
rs = st.executeQuery("SELECT * FROM CARS");
while (rs.next()) {
out.print(rs.getInt(1));
out.print(" ");
out.print(rs.getString(2));
out.print(" ");
out.print(rs.getString(3));
out.print("<br>");
}
out.println("</body>");
out.println("</html>");
} catch (NamingException | SQLException ex) {
Logger lgr = Logger.getLogger(SelectAllCars.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
} finally {
try {
if (rs != null) {
rs.close();
}
if (con != null) {
con.close();
}
if (out != null) {
out.close();
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(SelectAllCars.class.getName());
lgr.log(Level.WARNING, ex.getMessage(), ex);
}
}
}
@Override
protected void doGet(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}
@Override
protected void doPost(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}
}
在上面的 servlet 中，我们连接到 Derby testdb数据库并从CARS表中获取所有行。
Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/testdb");
我们使用 JNDI 命名查找来获取数据源。 从数据源，我们创建连接对象。
rs = st.executeQuery("SELECT * FROM CARS");
while (rs.next()) {
out.print(rs.getInt(1));
out.print(" ");
out.print(rs.getString(2));
out.print(" ");
out.print(rs.getString(3));
out.print("<br>");
}
我们使用 SQL 语句从CARS表中检索所有数据。 我们从结果集对象中打印数据。
$ ant
Buildfile: /home/janbodnar/programming/derby/servlet/build.xml
[echo] allcars
init:
[mkdir] Created dir: /home/janbodnar/programming/derby/servlet/build/classes
[mkdir] Created dir: /home/janbodnar/programming/derby/servlet/dist
[echo] Directories created.
compile:
[javac] Compiling 1 source file to /home/janbodnar/programming/derby/
servlet/build/classes
[echo] Source files compiled.
archive:
[war] Building war: /home/janbodnar/programming/derby/servlet/dist/allcars.war
[echo] Archive created.
deploy:
[copy] Copying 1 file to /home/janbodnar/bin/tomcat/webapps
[echo] Project deployed.
BUILD SUCCESSFUL
Total time: 1 second
我们启动 ant 来构建和部署项目。
图：SelectAllCars servlet 的输出
我们导航到localhost:8080/allcars/SelectAllCars URL 并接收输出。
在本章中，我们使用了 Derby 和 Apache Tomcat。
NetBeans 和 Derby
在本章中，我们将使用 NetBeans IDE 中的 Derby 数据库。 NetBeans 在其基本 Java SE 捆绑软件中对 Derby 数据库提供了内置支持。
到目前为止，我们已经与 Apache Derby 发行版合作。 在本章中，我们将使用 Java DB。 它是相同的数据库，只是名称不同。 Java DB 是 Java 6 版本附带的 Java 发行版。
图：NetBeans 服务窗口
在“服务”窗口中，我们展开“数据库”节点。 我们可以看到两个节点。 Java DB 节点和驱动节点。 在 Java DB 节点中，我们拥有所有数据库。 在驱动程序节点中，我们可以看到用于连接到 RDBMS 的各种 JDBC 驱动程序。 这些数据库驱动程序随 NetBeans IDE 一起提供。 我们有适用于嵌入式和服务器模式的 Java DB 驱动程序。
图：Java DB 属性窗口
上图是“Java DB 属性”对话框窗口。 当我们右键单击 Java DB 节点并选择Properties选项时，将显示该窗口。 在此对话框中，我们可以设置两个重要设置。 Java DB 安装目录和 Java DB 系统目录。
建立数据库
首先要做的是创建一个新的数据库。 我们将创建testdb数据库。
图：Java DB 上下文菜单
当我们右键单击 Java DB 节点时，将显示一个上下文菜单。 它具有四个选项：启动服务器，停止服务器，创建数据库和属性。 我们选择创建数据库项来创建一个新数据库。
图：“创建 Java DB 数据库”对话框
弹出对话框。 在此对话框窗口中，我们提供数据库名称，用户名和密码。 注意数据库位置字符串。 这是 Java DB 系统目录，将在其中创建我们的数据库文件。 默认的 Java DB 系统目录是主目录中的.netbeans-derby目录。
图：创建新数据库
目前，我们已经创建了一个新数据库。 它通过 Java DB 节点下的新数据库图标直观地指示。
数据库连接
创建数据库后，我们创建一个数据库连接。
NetBeans 将这些图标用于连接对象。 第一个图标用于断开连接的数据库连接对象，第二个图标用于已建立的数据库连接对象。
上面的连接是使用 Java DB 服务器驱动程序创建的 Java DB 连接。 请注意，当我们创建testdb数据库时，将自动启动 Java DB 服务器并创建连接。 可以通过右键单击 Java DB 驱动程序并选择“连接使用”选项来创建新的数据库连接。
我们将创建一个嵌入式 Java DB 数据库连接。 创建连接之前，如果 Java DB 服务器正在运行，则需要停止它。 Java DB 数据库不能由 Java DB 服务器引导，也不能同时由嵌入式驱动程序连接。 注意，我们不必显式启动服务器。 该服务器可以在后台启动。 例如，通过连接到 Java DB 服务器连接对象或创建新数据库。
图：停止服务器
我们用鼠标右键单击 Java DB 节点。 如果启用了“停止服务器”选项，则表示服务器正在运行。 我们选择它来停止服务器。
图：创建嵌入式连接
要创建嵌入式连接，我们右键单击 Java DB 嵌入式驱动程序，然后选择连接使用选项。 同样，我们通过选择 Java DB 服务驱动程序来创建服务器连接。
图：新建连接向导
我们有一个新建连接向导对话框。 在此对话框中，我们填写数据库名称和用户凭据。 JDBC URL 是根据此数据创建的。 我们已经指定了testdb数据库的完整路径。 这里似乎没有考虑 Java DB 系统目录。
成功创建嵌入式数据库连接后，我们在 NetBeans Services 窗口中看到以上图标。
建立表
数据库连接已创建。 接下来要做的是创建一个新的数据库表。 我们将创建一个名为FRIENDS的简单表，其中包含两列：Id和Name。 ID 为INTEGER和Name VARCHAR(30)。
我们扩展数据库连接节点，并进一步扩展USER12模式。 我们右键单击表图标，然后选择创建表选项。
图：创建一个新表
出现创建表对话框。 我们创建两列。 ID 和名称。
图：创建的Friends表
FRIENDS表已创建。 现在Tables节点是可扩展的，我们看到一个新的表图标。
图：执行命令
接下来，我们将执行一些 SQL 语句。 我们右键单击FRIENDS表图标，然后选择执行命令选项。 NetBeans 中将出现一个新的 SQL 命令窗口。
图：将数据插入FRIENDS表
在“SQL 命令”窗口中，我们编写了几个INSERT INTO SQL语句。 我们通过单击“运行 SQL”图标来执行语句。 该图标是带有绿色三角形的棕色椭圆形对象。 我们也可以使用Ctrl + Shift + E快捷方式执行 SQL 语句。
图：查看FRIENDS数据
在 SQL 命令窗口中运行SELECT * FROM FRIENDS，我们看到一个新窗口弹出。 在此窗口中，我们有一个表小部件，其中的数据按列和行进行组织。 我们有图标来修改此 gui 组件中的数据。
上图显示了用于处理表中数据的图标。 前两个图标分别用于插入新记录和删除所选记录。 如果我们使用鼠标指针选择更多的行并同时按下Shift键，则可以删除多个记录。 如果修改了数据，则会启用“提交记录”图标。 仅在我们执行此操作后才保存数据。 除了 SQL 语句，我们可以使用 GUI 工具修改数据。 通过双击记录，将显示一个行小部件。 在此小部件中，我们可以更改数据。 通过单击“提交记录”操作来保存更改。
在本章中，我们已经在 NetBeans IDE 中使用 Java DB。
SQLAlchemy 教程
这是 SQLAlchemy 教程。 它涵盖了 SQLAlchemy SQL 工具包和对象关系映射器的基础。
目录
SQLAlchemy
SQLAlchemy SQL 工具包和对象关系映射器是一组用于处理数据库和 Python 的综合工具。 它提供了一整套知名的企业级持久性模式，旨在实现高效和高性能的数据库访问。 SQLAlchemy 和 Django 的 ORM 是 Python 社区中使用最广泛的两个对象关系映射工具。
相关教程和电子书
SQLite Python 电子书是使用 Python 语言进行 SQLite 编程的深入材料。 MySQL 教程涵盖了 MySQL 数据库系统。 SQLite 教程涵盖了 SQLite 数据库系统。 以下教程是 Python 数据库编程教程： SQLite Python 教程和 MySQL Python 教程。
