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