java 连接到基于文件的 derby 数据库

connecting to a file-based derby database

提问by Amir.F

I want to work with a file-based database using apache derby. I was wondering if anyone can carify how to connect & create this database using netbeans as an IDE. I passed through derby manuals trying to figure this one out, but all i got was "Embedded Derby JDBC Database Connection", which i was told is not a file-based approach, and either way, the connection didn't seem to work. any help would be much appreciated

我想使用 apache derby 处理基于文件的数据库。我想知道是否有人可以解释如何使用 netbeans 作为 IDE 来连接和创建这个数据库。我通过了 derby 手册试图解决这个问题，但我得到的只是“嵌入式 Derby JDBC 数据库连接”，我被告知这不是基于文件的方法，无论哪种方式，连接似乎都不起作用。任何帮助将非常感激

采纳答案by simgineer

When you download NetBeans 7.1.2, the "All" package you get the glassfish app server with it. After installing w/ glassfish, in the IDE you should be able to select the Services tab > Expand Databases and you should see Java DB. R. Click on Java DB and select Start Server. Then R. Click again and select Create Database. Enter the db name, user and a password. BTW i normally use APP for both the user and password because this way it also becomes the default schema and I don't have to go changing around anything for production environment.

当您下载 NetBeans 7.1.2 时，“All”包将包含 glassfish 应用服务器。安装了 glassfish 后，在 IDE 中，您应该能够选择“服务”选项卡 >“展开数据库”，您应该会看到 Java DB。R. 单击 Java DB 并选择启动服务器。然后 R。再次单击并选择创建数据库。输入数据库名称、用户和密码。顺便说一句，我通常将 APP 用于用户和密码，因为这样它也成为默认模式，我不必为生产环境更改任何内容。

Now in the Java DB group you should see the new database you created. R. Click on that and select connect. You should see a connection item appear under the Databases group. Expand this item and you should see the APP schema in bold indicating it is the default schema. Expand that and R. Click on the Tables select Create Table and you'll get a ui that helps you populate a table. Repeat until all your tables are created. Other ways to create tables using the ide is right click on the Tables and select Execute Command where you can run DDL to define the tables schema. This is the way i do db creation, by saving my script as an .sql file so i can delete the db and rerun it again as needed.

现在在 Java DB 组中，您应该会看到您创建的新数据库。R. 单击它并选择连接。您应该会看到一个连接项出现在 Databases 组下。展开此项，您应该会看到以粗体显示的 APP 架构，表明它是默认架构。展开它和 R。单击 Tables 选择 Create Table，您将获得一个可帮助您填充表格的 ui。重复直到创建所有表。使用 ide 创建表的其他方法是右键单击“表”并选择“执行命令”，您可以在其中运行 DDL 来定义表模式。这是我创建数据库的方式，将我的脚本保存为 .sql 文件，这样我就可以删除数据库并根据需要重新运行它。

Here is an example of the my dbinit.sql script i use to create my tables in derby.

这是我用于在 derby 中创建表的 dbinit.sql 脚本的示例。

create table usertable (
    username varchar(128) NOT NULL CONSTRAINT USER_PK PRIMARY KEY ,
    password varchar(128) NOT NULL,
    email varchar(128) NOT NULL,
    firstname varchar(128) NOT NULL,
    lastname varchar(128) NOT NULL
);

create table grouptable(
    username varchar(128) NOT NULL,
    groupid  varchar(128) NOT NULL,
    CONSTRAINT GROUP_PK PRIMARY KEY(username, groupid),
    CONSTRAINT USER_FK FOREIGN KEY(username) REFERENCES usertable(username)
        ON DELETE CASCADE ON UPDATE RESTRICT
);

insert into usertable(username,password,firstname,lastname) 
    values ('admin', '21232f297a57a5a743894a0e4a801fc3','','');
insert into grouptable(username,groupid) values ('admin', 'USER');
insert into grouptable(username,groupid) values ('admin', 'ADMIN');

You can easily delete the database you've created by right clicking on the database, R. click on the database you wish to remove and select delete. and reuse your script to regenerate it.

您可以通过右键单击数据库，R. 单击要删除的数据库并选择删除来轻松删除您创建的数据库。并重用您的脚本来重新生成它。

Hope this helps! :)

希望这可以帮助！:)

回答by Bill

To create one, your jdbc url will be: jdbc:derby:foo;create=true, and it will create a database called foo in the derby system directory. If you want to create one in an absolute location on your hard drive, specify an absolute path. jdbc:derby:/home/me/foo;create=true.

要创建一个，您的 jdbc url 将是: jdbc:derby:foo;create=true，它将在 derby 系统目录中创建一个名为 foo 的数据库。如果要在硬盘驱动器上的绝对位置创建一个，请指定绝对路径。jdbc:derby:/home/me/foo;create=true.

Once the database is created, you can connect to it with the same url, or you can drop the ;create=truepart off.

创建数据库后，您可以使用相同的 url 连接到它，或者您可以删除该;create=true部分。

You can set the derby system directory via system properties, e.g. System.setProperty("derby.system.homeSystem.setProp", "/home/bar/whatever");. I think you would need to do this before starting a derby database has been started, but I've never tried to do it afterwards. I have found that setting the derby system home for the app and specifying relative database url to work better, but that's a personal preference.

您可以通过系统属性设置 derby 系统目录，例如System.setProperty("derby.system.homeSystem.setProp", "/home/bar/whatever");. 我认为您需要在启动 derby 数据库之前执行此操作，但之后我从未尝试过这样做。我发现为应用程序设置 derby 系统主页并指定相关数据库 url 可以更好地工作，但这是个人偏好。

回答by tinkert

Most certainly the driver that you want to use is the embedded driver in derby.jar. Rather than getting into the "coding side of things" as there are enough examples running about, I kind of like to use netbeans itself to do everything up prior to starting the coding.

最肯定的是，您要使用的驱动程序是 derby.jar 中的嵌入式驱动程序。与其进入“事物的编码方面”，因为有足够多的例子在运行，我有点喜欢在开始编码之前使用 netbeans 本身来完成所有工作。

Since I use the embedded approach to convert wireshark / tcpdump / nmap files into database entries, I kind of use a slightly different approach.

由于我使用嵌入式方法将wireshark / tcpdump / nmap 文件转换为数据库条目，所以我使用了一种稍微不同的方法。

Since I prefer to us the latest stable versions of Derby instead of the installed version I have a slightly different approach which allows me to use whatever version I desire as well as putting the database in a r/w data directory where ever I want in the file system.

由于我更喜欢​​我们最新的稳定版本的 Derby 而不是已安装的版本，我有一个稍微不同的方法，它允许我使用我想要的任何版本，以及将数据库放在文件中我想要的 ar/w 数据目录中系统。

1. Create: mkdir -p $HOME/opt/derby and cd ~/opt/derby.
2. Download latest version from db.apache.org and unbundle is in $HOME/opt/derby/
3. Create the symbolic link: ln -s pwd/versionpwd/latest.
4. Create the symbolic link: ln -s pwd/latest pwd
5. Startup netbeans.

1. 创建： mkdir -p $HOME/opt/derby 和 cd ~/opt/derby。
2. 从 db.apache.org 下载最新版本，unbundle 位于 $HOME/opt/derby/
3. 创建符号链接：ln -s pwd/ versionpwd/latest。
4. 创建符号链接：ln -s pwd/latestpwd
5. 启动netbeans。

From here on you can do everything in netbeans:

从这里开始，您可以在 netbeans 中执行所有操作：

1. Create an Ant Library and call it "ASF-Derby-Emb.": Tools->Ant Libraries->New Library.
2. In the settings panel set the classpath to /home/[loginid]/opt/derby/default/lib/derby.jar and, optionally, add in the derbytools.jar.
3. Click in on the source tab and added in the path to the source code if you have downloaded it.
4. Click on the javadoc tab and and in /home/[loginid]/opt/derby/default/javadocs

1. 创建一个 Ant 库并命名为“ASF-Derby-Emb。”：Tools->Ant Libraries->New Library。
2. 在设置面板中，将类路径设置为 /home/[loginid]/opt/derby/default/lib/derby.jar，并可以选择添加 derbytools.jar。
3. 如果您下载了源代码，请单击源选项卡并添加到源代码的路径中。
4. 单击 javadoc 选项卡和 /home/[loginid]/opt/derby/default/javadocs

When you get into coding, just add in the ASF-Derby-Emb Library and it will be automatically copied into your 'dist.'

当您开始编码时，只需添加 ASF-Derby-Emb 库，它就会自动复制到您的“dist”中。

Now set up the JavaDB.

现在设置JavaDB。

1. Set up the Services tab: Window->Services and select that tab.
2. Select JavaDB and right-click and stop the JavaDB that may be running.
3. Select JavaDB and right-click and click on Properties menu entry which will bring up a properties dialog.
4. Browse to the JavaDB installation directory: in $HOME/opt/derby/default.
5. Either leave the Database installation directory location as is or browse to the directory where you want to put it, but make sure you have read/write access.
6. click 'OK' and now you have a different JavaDB installation and database location.

1. 设置服务选项卡：窗口->服务并选择该选项卡。
2. 选择 JavaDB 并右键单击并停止可能正在运行的 JavaDB。
3. 选择 JavaDB 并右键单击并单击 Properties 菜单项，这将显示一个属性对话框。
4. 浏览到 JavaDB 安装目录：在 $HOME/opt/derby/default 中。
5. 保留数据库安装目录位置原样或浏览到要放置它的目录，但请确保您具有读/写访问权限。
6. 单击“确定”，现在您有一个不同的 JavaDB 安装和数据库位置。

Just by changing the database location you can put a database where ever you want it. Or you can accomplish the same thing in code as the url is: jdbc:derby://database location directory.

只需更改数据库位置，您就可以将数据库放置在您想要的任何位置。或者您可以在代码中完成与 url 相同的事情：jdbc:derby://数据库位置目录。

Any way, now I can setup my database.

无论如何，现在我可以设置我的数据库。

1. select JavaDB and right-click and select 'Create Database' and just fillin the dialog and the database will be initialized in the directory which was set up in the properties file.
2. Create a connection: select JavaDB and then select the name of the database that has been created.

1. 选择JavaDB并右键单击并选择“创建数据库”，只需填写对话框，数据库将在属性文件中设置的目录中初始化。
2. 创建连接：选择JavaDB，然后选择已创建的数据库名称。

From this point there are three choices:

从这点来说，有三个选择：

1. Do nothing and create the code needed to create the tables, etc.
2. Create a project and create a top-level project directory called sql and place all your sql scripts defining tables, etc. in that directory.
3. Use the 'builtin facility: Service->jdbc:derby://??? and create the tables using the facility that appears and allows you to enter the necessary sql.

1. 什么都不做，创建创建表等所需的代码。
2. 创建一个项目并创建一个名为 sql 的顶级项目目录，并将所有定义表等的 sql 脚本放在该目录中。
3. 使用'内置工具：Service->jdbc:derby://??? 并使用出现的工具创建表，并允许您输入必要的 sql。

Personally, (2) I find to be the best approach, initially. If you have pre-existing sql scripts with the the .sql extension the simply by opening them under the project, they will be loaded into the sql editor and can be executed there.

就个人而言，(2) 我发现最初是最好的方法。如果您已有扩展名为 .sql 的 sql 脚本，只需在项目下打开它们，它们就会被加载到 sql 编辑器中并可以在那里执行。

Alternatively (3) can be used and the resulting script saved.

或者，可以使用 (3) 并保存生成的脚本。

Any way, much can be created before the coding begins. There are a lot of ways to do it. I would much rather have all created before coding -- it makes developing test cases much easier and using the preceding I don't have to jump in and out of netbeans to modify things.

无论如何，在编码开始之前可以创建很多。有很多方法可以做到。我更愿意在编码之前创建所有内容——它使开发测试用例变得更加容易，并且使用前面的我不必跳入和跳出 netbeans 来修改内容。

回答by Priyank Doshi

In case you want to have a file based database in Derby , you should download , Derby

如果您想在 Derby 中拥有一个基于文件的数据库，您应该下载Derby

Unzip the file. Run startNetworkServer.bat inside bin directory. It will start derby server which will listen to port no 1527 .

解压缩文件。在 bin 目录中运行 startNetworkServer.bat。它将启动 derby 服务器，该服务器将侦听端口号 1527 。

Now in your java code :

现在在你的java代码中：

DriverName= org.apache.derby.jdbc.ClientDriver

ConnectionString =jdbc:derby://localhost:1527/schema_name;create=true

驱动程序名称= org.apache.derby.jdbc.ClientDriver

ConnectionString =jdbc:derby://localhost:1527/schema_name;create=true

Morover,you can also query the derby DB like we do in oracle.

此外，您还可以像我们在 oracle 中那样查询 derby DB。

run ij.bat located inside bin directory. then on the prompt :

运行位于 bin 目录中的 ij.bat。然后在提示上：

connect 'specify connection url here'

connect '在此处指定连接 url'

and you can fire the sql queries like the oracle prompt.

您可以像 oracle 提示一样触发 sql 查询。

In case you want to run derby server on different port , execute the following command at cmd :

如果您想在不同的端口上运行 derby 服务器，请在 cmd 处执行以下命令：

startnetworkserver.bat -p 1234

startnetworkserver.bat -p 1234