彩票计算JAVA版(三)内置数据库derby

彩票计算JAVA版(三)内置数据库derby

1.配置derby运行环境
JVM里面运行的嵌入式derby，不晓得JRE6后是咋样的。但是目前启动derby来存储本地数据，我是这样处理的

首先导入derby必要的几个包，修改POM文件pom.xml如下：
<dependency>
<groupId>org.apache.derby</groupId>
<artifactId>derby</artifactId>
<version>10.4.2.0</version>
</dependency>

还是按照dbcp老的方式配置ibatis、spring和数据库的配置，配置信息如下：
#==========================================
# database configration
#==========================================
database.driver=org.apache.derby.jdbc.EmbeddedDriver
database.url=jdbc:derby:easyluckDB;create=true
database.username=easyluck
database.pwd=easyluck
库的名字叫easyluckDB,用户名和密码都是easyluck


2.利用ibatis的工具类初始化数据库
利用ScriptRunner 初始化sql脚本，核心类DatabaseCreation.java如下：
package com.sillycat.easyluck.database;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.context.ApplicationContext;
import com.ibatis.common.jdbc.ScriptRunner;
import com.sillycat.easyluck.common.utils.SpringContextUtil;
public class DatabaseCreation {
public void init() {
this.createTables();
this.createData();
}
public void createTables() {
String sourceUrl = "sql/create_table.sql";
runScript(sourceUrl);
}
public void createData() {
String sourceUrl = "sql/create_data.sql";
runScript(sourceUrl);
}
public void runScript(String sourceUrl) {
ApplicationContext context = SpringContextUtil.create();
DataSource ds = (DataSource) context.getBean("dataSource");
ScriptRunner runner = null;
try {
runner = new ScriptRunner(ds.getConnection(), true, true);
} catch (SQLException e) {
e.printStackTrace();
}
String file = DatabaseCreation.class.getResource(sourceUrl).getFile();
Reader reader = null;
try {
reader = new FileReader(file);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
try {
runner.runScript(reader);
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
DatabaseCreation dsCreation = new DatabaseCreation();
dsCreation.init();
}
}

SQL文件放置在这个类的sql目录下，文件内容参考如下
建表sql脚本create_table.sql:
-- connect 'jdbc:derby://localhost:1527/easyluckDB;create=true;user=easyluck;password=easyluck';
drop table BALLS;
drop table LUCKS;
drop table R_LUCK_BALL;
create table BALLS (
ID integer primary key GENERATED ALWAYS AS IDENTITY,
NUM integer not null,
COLOR varchar(30)
);
create table LUCKS (
ID integer primary key GENERATED ALWAYS AS IDENTITY,
CODE varchar(30)
);
create table R_LUCK_BALL (
LUCK_ID integer,
BALL_ID integer
);
初始化数据脚本create_data.sql:
-- red balls
insert into BALLS (NUM,COLOR) values(1,'red');
insert into BALLS (NUM,COLOR) values(2,'red');
insert into BALLS (NUM,COLOR) values(3,'red');
insert into BALLS (NUM,COLOR) values(4,'red');
insert into BALLS (NUM,COLOR) values(5,'red');
insert into BALLS (NUM,COLOR) values(6,'red');
insert into BALLS (NUM,COLOR) values(7,'red');
insert into BALLS (NUM,COLOR) values(8,'red');
insert into BALLS (NUM,COLOR) values(9,'red');
insert into BALLS (NUM,COLOR) values(10,'red');
insert into BALLS (NUM,COLOR) values(11,'red');
insert into BALLS (NUM,COLOR) values(12,'red');
insert into BALLS (NUM,COLOR) values(13,'red');
insert into BALLS (NUM,COLOR) values(14,'red');
insert into BALLS (NUM,COLOR) values(15,'red');
insert into BALLS (NUM,COLOR) values(16,'red');
insert into BALLS (NUM,COLOR) values(17,'red');
insert into BALLS (NUM,COLOR) values(18,'red');
insert into BALLS (NUM,COLOR) values(19,'red');
insert into BALLS (NUM,COLOR) values(20,'red');
insert into BALLS (NUM,COLOR) values(21,'red');
insert into BALLS (NUM,COLOR) values(22,'red');
insert into BALLS (NUM,COLOR) values(23,'red');
insert into BALLS (NUM,COLOR) values(24,'red');
insert into BALLS (NUM,COLOR) values(25,'red');
insert into BALLS (NUM,COLOR) values(26,'red');
insert into BALLS (NUM,COLOR) values(27,'red');
insert into BALLS (NUM,COLOR) values(28,'red');
insert into BALLS (NUM,COLOR) values(29,'red');
insert into BALLS (NUM,COLOR) values(30,'red');
insert into BALLS (NUM,COLOR) values(31,'red');
insert into BALLS (NUM,COLOR) values(32,'red');
insert into BALLS (NUM,COLOR) values(33,'red');
-- blue balls
insert into BALLS (NUM,COLOR) values(1,'blue');
insert into BALLS (NUM,COLOR) values(2,'blue');
insert into BALLS (NUM,COLOR) values(3,'blue');
insert into BALLS (NUM,COLOR) values(4,'blue');
insert into BALLS (NUM,COLOR) values(5,'blue');
insert into BALLS (NUM,COLOR) values(6,'blue');
insert into BALLS (NUM,COLOR) values(7,'blue');
insert into BALLS (NUM,COLOR) values(8,'blue');
insert into BALLS (NUM,COLOR) values(9,'blue');
insert into BALLS (NUM,COLOR) values(10,'blue');
insert into BALLS (NUM,COLOR) values(11,'blue');
insert into BALLS (NUM,COLOR) values(12,'blue');
insert into BALLS (NUM,COLOR) values(13,'blue');
insert into BALLS (NUM,COLOR) values(14,'blue');
insert into BALLS (NUM,COLOR) values(15,'blue');
insert into BALLS (NUM,COLOR) values(16,'blue');

3.spring+ibatis连接derby撰写DAO层代码：
其实和平时写ibatis是一样的，只是在处理ID的时候和平时用的ORACLE等在XML有些区别，核心区别如下：
<insert id="insert" parameterClass="luck">
insert into
LUCKS (
CODE
) values (
#code:VARCHAR#
)
<selectKey keyProperty="id" resultClass="int">  
VALUES IDENTITY_VAL_LOCAL()
</selectKey>
</insert>

另外，在网上查到的各种数据库的ID处理方式
Cloudscape  VALUES IDENTITY_VAL_LOCAL()
DB2         VALUES IDENTITY_VAL_LOCAL()
Derby       VALUES IDENTITY_VAL_LOCAL()
HSQLDB      CALL IDENTITY()
MySql       SELECT LAST_INSERT_ID()
SqlServer   SELECT SCOPE_IDENTITY()
SYBASE      SELECT @@IDENTITY
ORACLE      SELECT CUSTOM_SQL.NEXTVAL AS ID FROM DUAL