derby数据库笔记

1.下载derby包. url:http://db.apache.org/derby/derby_downloads.html
2.解压配置 DERBY_HOME E:\db-derby-10.4.1.3-bin
3.配置path

4.启动数据库
...\bin startNetworkServer.bat

5.使用ij工具来进行数据库交互，执行SQL脚本，如查询、增删改、创建表等等
...\bin connect 'jdbc:derby:hostmonitor;create=true';
连接数据库,并创建hostmonitor数据库

6.运行sql脚本
run 'E:\work_lp\Test_hostmonitor\doc\derby_schema.txt';

例如:
derby脚本
如果需要将字段设置曾为自增字段可以采用在创建表时使用int generated by default as identity定义字段类型
DROP TABLE server_info;
CREATE TABLE server_info(
id bigint NOT NULL generated by default as identity(START WITH 1, INCREMENT BY 1) PRIMARY KEY,
serverType int NOT NULL,
serverKey varchar(200) NOT NULL UNIQUE,
serverIp varchar(200) default null,
serverDomain varchar(200) default null,
maxMemory bigint default 0
);

DROP TABLE login_info;
CREATE TABLE login_info (
id bigint NOT NULL generated by default as identity(START WITH 1, INCREMENT BY 1) PRIMARY KEY,
serverId bigint NOT NULL,
createTime bigint NOT NULL,
companyId bigint NOT NULL,
userId bigint NOT NULL,
userName varchar(200) NOT NULL,
isLogin SMALLINT NOT NULL
);
CREATE INDEX login_serverId ON login_info(serverId);
CREATE INDEX login_createTime ON login_info(createTime);
CREATE INDEX login_companyId ON login_info(companyId);
CREATE INDEX login_userId ON login_info(userId);
CREATE INDEX login_isLogin ON login_info(isLogin);

mysql脚本
DROP TABLE IF EXISTS `server_info`;
CREATE TABLE `server_info` (
`id` bigint(20) NOT NULL auto_increment,
`serverType` int NOT NULL,
`serverKey` varchar(200) NOT NULL,
`serverIp` varchar(200) default null,
`serverDomain` varchar(200) default null,
`maxMemory` bigint(20) default 0,

PRIMARY KEY (`id`),
UNIQUE `serverKey` (`serverKey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

不支持:
1.IF EXISTS
2."`"这个符号
3.ENGINE=InnoDB DEFAULT CHARSET=utf8;
4.bigint(20) 后边的(20)


API Reference HTML http://db.apache.org/derby/javadoc/publishedapi/
Getting Started with Derby [PDF] [HTML Book] [HTML Pages] http://db.apache.org/derby/docs/dev/getstart/
Derby Reference Manual [PDF] [HTML Book] [HTML Pages] http://db.apache.org/derby/docs/dev/ref/
Derby Developer's Guide [PDF] [HTML Book] [HTML Pages] http://db.apache.org/derby/docs/dev/devguide/
Tuning Derby [PDF] [HTML Book] [HTML Pages] http://db.apache.org/derby/docs/dev/tuning/
Derby Server and Administration Guide [PDF] [HTML Book] [HTML Pages] http://db.apache.org/derby/docs/dev/adminguide/
Derby Tools and Utilities Guide [PDF] [HTML Book] [HTML Pages] http://db.apache.org/derby/docs/dev/tools/


7.结合Spring+c3p0+ibatis
jar包:derbyclient.jar
驱动:org.apache.derby.jdbc.ClientDriver
url:jdbc:derby://localhost:1527/hostmonitor;create=true

8.其他的配置同mysql


字段类型
http://db.apache.org/ddlutils/databases/derby.html

JDBC Type Database Type Additional comments
ARRAY BLOB Will be read back as BLOB
BIGINT BIGINT
BINARY CHAR(n) FOR BIT DATA
BIT SMALLINT Cloudscape/Derby have no native boolean type
Will be read back as SMALLINT
BLOB BLOB
BOOLEAN SMALLINT Cloudscape/Derby have no native boolean type
Will be read back as BOOLEAN
CHAR CHAR
CLOB CLOB
DATALINK LONG VARCHAR FOR BIT DATA Will be read back as LONGVARBINARY
DATE DATE
DECIMAL DECIMAL
DISTINCT BLOB Will be read back as BLOB
DOUBLE Cloudscape: DOUBLE PRECISION
Derby: DOUBLE
FLOAT Cloudscape: DOUBLE PRECISION
Derby: DOUBLE Will be read back as DOUBLE
INTEGER INTEGER
JAVA_OBJECT BLOB Will be read back as BLOB
LONGVARBINARY LONG VARCHAR FOR BIT DATA
LONGVARCHAR LONG VARCHAR
NULL LONG VARCHAR FOR BIT DATA Will be read back as LONGVARBINARY
NUMERIC NUMERIC
OTHER BLOB Will be read back as BLOB
REAL REAL
REF LONG VARCHAR FOR BIT DATA Will be read back as LONGVARBINARY
SMALLINT SMALLINT
STRUCT BLOB Will be read back as BLOB
TIME TIME
TIMESTAMP TIMESTAMP
TINYINT SMALLINT Will be read back as SMALLINT
VARBINARY VARCHAR(n) FOR BIT DATA
VARCHAR VARCHAR


------------------------------------------------------
<!-- ibatis数据库配置
<bean id="c3p0" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="${DBDriverClass}"></property>
<property name="jdbcUrl" value="${DBJdbcUrl}"></property>
</bean>
-->
#derby
DBDriverClass org.apache.derby.jdbc.ClientDriver
DBJdbcUrl jdbc:derby://localhost:1527/hostmonitor;create=true