H2 连接方式、简单使用

H2 连接方式、简单使用  
2012-09-29 10:53:19|  分类： database|举报|字号 订阅
1.数据库连接的jdbc url
h2分嵌入模式和服务模式，根据不同的模式和情况。连接数据库jdbc url的写法不同
连接内嵌模式的数据库
"jdbc:h2:file:D:/test"  连接自定目录下的指定数据库
"jdbc:h2:~/test" 连接默认目录下的指定数据库
jdbc:h2:[file:][<path>]<databaseName>
jdbc:h2:~/test
jdbc:h2:file:/data/sample
jdbc:h2:file:C:/data/sample (Windows only)




连接内存模式的数据库
jdbc:h2:mem:test


连接server模式的数据库
jdbc:h2:tcp://localhost/~/test
jdbc:h2:tcp://<server>[:<port>]/[<path>]<databaseName>
jdbc:h2:tcp://localhost/~/test
jdbc:h2:tcp://dbserv:8084/~/sample 
jdbc:h2:tcp://localhost/mem:test


ssl连接数据库
jdbc:h2:ssl://localhost/~/test
dbc:h2:ssl://<server>[:<port>]/<databaseName>
jdbc:h2:ssl://secureserv:8085/~/sample;


连接压缩文件内的数据库
jdbc:h2:zip:D:/test.zip!/test


链接加密文件数据库
jdbc:h2:<url>;CIPHER=[AES|XTEA]
jdbc:h2:ssl://secureserv/~/testdb;CIPHER=AES
jdbc:h2:file:~/secure;CIPHER=XTEA




其他好多，我不翻译了
File locking methods jdbc:h2:<url>;FILE_LOCK={FILE|SOCKET|NO}
jdbc:h2:file:~/private;CIPHER=XTEA;FILE_LOCK=SOCKET


Only open if it already exists jdbc:h2:<url>;IFEXISTS=TRUE
jdbc:h2:file:~/sample;IFEXISTS=TRUE


Don't close the database when the VM
exits jdbc:h2:<url>;DB_CLOSE_ON_EXIT=FALSE


Execute SQL on connection
jdbc:h2:<url>;INIT=RUNSCRIPT FROM '~/create.sql'
jdbc:h2:file:~/sample;INIT=RUNSCRIPT FROM '~/create.sql'\\;RUNSCRIPT FROM
'~/populate.sql'


User name and/or password jdbc:h2:<url>[;USER=<username>][;PASSWORD=<value>]
jdbc:h2:file:~/sample;USER=sa;PASSWORD=123


Debug trace settings jdbc:h2:<url>;TRACE_LEVEL_FILE=<level 0..3>
jdbc:h2:file:~/sample;TRACE_LEVEL_FILE=3


Ignore unknown settings jdbc:h2:<url>;IGNORE_UNKNOWN_SETTINGS=TRUE


Custom file access mode jdbc:h2:<url>;ACCESS_MODE_DATA=rws


Database in a zip file jdbc:h2:zip:<zipFileName>!/<databaseName>
jdbc:h2:zip:~/db.zip!/test


Compatibility mode jdbc:h2:<url>;MODE=<databaseType>
jdbc:h2:~/test;MODE=MYSQL


Auto-reconnect jdbc:h2:<url>;AUTO_RECONNECT=TRUE
jdbc:h2:tcp://localhost/~/test;AUTO_RECONNECT=TRUE


Automatic mixed mode jdbc:h2:<url>;AUTO_SERVER=TRUE
jdbc:h2:~/test;AUTO_SERVER=TRUE


Page size jdbc:h2:<url>;PAGE_SIZE=512


Changing other settings jdbc:h2:<url>;<setting>=<value>[;<setting>=<value>...]
jdbc:h2:file:~/sample;TRACE_LEVEL_SYSTEM_OUT=3




2.h2自带的连接池
JdbcConnectionPool cp = JdbcConnectionPool.create(url,username,password);
通过连接池获取连接
Connection conn = cp.getConnection();


note:
默认连接池是初始10个连接


3.嵌入模式数据库管理
通过代码启动数据库服务
Server server = Server.createTcpServer();
server.start();
关闭数据库服务
server.stop();
.数据库注销
server.shutdown();
 
4.通过h2自带的方法跟cvs文件读写数据
1.通过代码读取csv的数据
ResultSet rs = Csv.getInstance().read(fileName, null, null);
ResultSetMetaData meta = rs.getMetaData();
 
while (rs.next()) {
for (int i = 0; i < meta.getColumnCount(); i++) {
System.out.println(meta.getColumnLabel(i + 1) + ":"
+ rs.getString(i + 1));
}
 
System.out.println();
}
 
rs.close();
 
通过代码写csv数据
SimpleResultSet rs = new SimpleResultSet();
rs.addColumn("name", Types.VARCHAR, 255, 0);
rs.addColumn("email", Types.VARCHAR, 255, 0);
rs.addRow("陈盈", "yingchen@midea.com.cn");
rs.addRow("邹嘉欣", "zoujx@midea.com.cn");
Csv.getInstance().write(fileName, rs, null);
 
 
2.使用h2自带cvs函数通过sql语句实现
csvread()实现读csv文件
例:SELECT * FROM CSVREAD('d:/test.csv');
 
CSVWRITE()实现写csv文件
例:CALL CSVWRITE('d:/test.csv', 'SELECT * FROM TEST');
 
3.根据cvs文件创建h2数据库表


CREATE TABLE TEST AS SELECT * FROM CSVREAD('test.csv');//csv文件数据创建test表 CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))     AS SELECT * FROM CSVREAD('test.csv');/创建test表，csv文件相应的列插入到test表相应的字段


 
5.H2数据库备份
1.备份sql脚本
运行
java -cp h2*.jar org.h2.tools.Script -url jdbc:h2:~/test -user sa -script test.zip -options compression zip
 
2.备份数据库文件
方法1:进入h2 shell 命令行模式
java -cp h2*.jar org.h2.tools.Shell
 
方法2:在shell命令行环境下执行备份命令
BACKUP TO 'backup.zip'