彩票计算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 |
|