纯JAVA数据库derby

纯JAVA数据库derby简介2008-11-14 09:47derby简介

1. 安装
首页
http://db.apache.org/derby/index.html

下载
http://db.apache.org/derby/derby_downloads.html

文件
db-derby-10.4.2.0-bin.zip

The Apache Derby plug-ins for Eclipse provide a seamless integration between Eclipse and Apache Derby
seamless [ 'si:mlis ] 无缝的

eclipse插件
http://db.apache.org/derby/releases/release-10.4.2.0.cgi

derby_core_plugin - provides the Derby jar files to other plugins in Eclipse.
derby_ui_plugin - provides an Apache Derby Nature in Eclipse for easy database application development.

插件文件
derby_ui_plugin_1.1.2.zip
derby_core_plugin_10.4.2.zip

解开压缩包，将plugins目录下面的所有文件拷贝到eclipse下面的plugins下面

重启eclipse，选中某项目 右键-----》Apache Derby -----》 Add Apache Derby nature

2.启动Derby Server
With the Java project selected, bring up the context menu and select the menu item, Apache Derby, Start Derby Network Server

The pop-up box will appear which states the Apache Derby Network Server is attempting to be started

默认启动是按照默认参数，端口为1527

修改启动参数
With the Java project active in the Package Explorer or Navigator view, select the menu item Project, Properties

3.启动工具ij
Select the project and bring up the context menu. Select the menu item, Apache Derby, ij (Interactive SQL).

The first step to using Derby is to connect to the database using a database JDBC connection URL.
The database connection URL we'll use for this example will connect to our Network Server using the Derby Network Client driver on the localhost. We'll create a database called myDB as the user 'me' with a password of 'mine.'

To connect to the database from ij we need to issue the connect command, so the entire command to connect to our database looks like this:

connect 'jdbc:derby://localhost:1527/myDB;create=true;user=me;password=mine';

connect 'jdbc:derby://localhost:1527/springside-example;create=true;user=APP;password=APP';

Cut and paste the above connection URL into the ij console window. It should create a database in the current workspace, under the current Java project, called myDB.

参考mini-web的derby例子
可以写一个建表的SQL文件schema.sql如下：
connect 'jdbc:derby://localhost:1527/springside-example;create=true;user=APP;password=APP';

drop table ROLES_AUTHORITIES;
drop table USERS_ROLES;
drop table USERS;
drop table ROLES;
drop table AUTHORITIES;


create table USERS (
ID integer primary key GENERATED ALWAYS AS IDENTITY,
LOGIN_NAME varchar(20) not null unique,
PASSWORD varchar(20),
NAME varchar(20),
EMAIL varchar(30)
);

create table ROLES (
ID integer primary key GENERATED ALWAYS AS IDENTITY,
NAME varchar(20) not null unique
);

create table USERS_ROLES (
USER_ID integer not null,
ROLE_ID integer not null,
FOREIGN KEY (ROLE_ID) references ROLES(ID),
FOREIGN KEY (USER_ID) references USERS(ID)
);

CREATE TABLE AUTHORITIES (
ID integer primary key GENERATED ALWAYS AS IDENTITY,
NAME varchar(20) not null,
DISPLAY_NAME varchar(20) not null
);

create table ROLES_AUTHORITIES (
ROLE_ID integer not null,
AUTHORITY_ID integer not null,
FOREIGN KEY (ROLE_ID) references ROLES(ID),
FOREIGN KEY (AUTHORITY_ID) references AUTHORITIES(ID)
);

disconnect;
exit;

一个导入数据的SQL文件如下load-data.sql如下：
connect 'jdbc:derby://localhost:1527/springside-example;create=true;user=APP;password=APP';

insert into USERS (LOGIN_NAME,PASSWORD,NAME,EMAIL) values('admin','admin','Admin','admin@springside.org.cn');
insert into USERS (LOGIN_NAME,PASSWORD,NAME,EMAIL) values('user','user','User','user@springside.org.cn');

insert into USERS (LOGIN_NAME,PASSWORD,NAME,EMAIL) values('user2','user2','Jack','jack@springside.org.cn');
insert into USERS (LOGIN_NAME,PASSWORD,NAME,EMAIL) values('user3','user3','Kate','kate@springside.org.cn');
insert into USERS (LOGIN_NAME,PASSWORD,NAME,EMAIL) values('user4','user4','Sawyer','sawyer@springside.org.cn');
insert into USERS (LOGIN_NAME,PASSWORD,NAME,EMAIL) values('user5','user5','Ben','ben@springside.org.cn');


insert into ROLES (NAME) values('管u29702 员);
insert into ROLES (NAME) values('用u25143 ');

insert into AUTHORITIES (NAME,DISPLAY_NAME) values('ROLE_VIEW_USER','查u30475 用u25143 ');
insert into AUTHORITIES (NAME,DISPLAY_NAME) values('ROLE_MODIFY_USER','管u29702 用u25143 ');
insert into AUTHORITIES (NAME,DISPLAY_NAME) values('ROLE_VIEW_ROLE','查u30475 角u-32142 ');
insert into AUTHORITIES (NAME,DISPLAY_NAME) values('ROLE_MODIFY_ROLE','管u29702 角u-32142 ');

insert into USERS_ROLES values(1,1);
insert into USERS_ROLES values(1,2);
insert into USERS_ROLES values(2,2);

insert into USERS_ROLES values(3,2);
insert into USERS_ROLES values(4,2);
insert into USERS_ROLES values(5,2);
insert into USERS_ROLES values(6,2);

insert into ROLES_AUTHORITIES values(1,1);
insert into ROLES_AUTHORITIES values(1,2);
insert into ROLES_AUTHORITIES values(1,3);
insert into ROLES_AUTHORITIES values(1,4);
insert into ROLES_AUTHORITIES values(2,1);
insert into ROLES_AUTHORITIES values(2,3);

disconnect;
exit;

执行SQL的时候如下操作
The last step is to run the SQL script. Right-click the restaurants.sql file in the Package Explorer view and select Apache Derby, Run SQL Script using 'ij'.

出现一个问题，执行出来到derby的数据是乱码
修改eclipse配置打开"windows->preferences->general->workspace",把text file encoding从GBK改回UTF-8就行了.