H2 数据库使用 demo

项目中添加jar：h2-1.0.20070617.jar

 

实例一：

import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import org.h2.tools.Server;public class H2Demo {private Server server;private String port = "9080";private String dbDir = "./h2db/sample";private String user = "sa";private String password = "";public void startServer() {try {System.out.println("正在启动h2...");server = Server.createTcpServer(new String[] { "-tcpPort", port }).start();} catch (SQLException e) {System.out.println("启动h2出错：" + e.toString());e.printStackTrace();throw new RuntimeException(e);}}public void stopServer() {if (server != null) {System.out.println("正在关闭h2...");server.stop();System.out.println("关闭成功.");}}public void useH2() {try {Class.forName("org.h2.Driver");Connection conn = DriverManager.getConnection("jdbc:h2:" + dbDir,user, password);Statement stat = conn.createStatement();// insert data//stat.execute("DROP TABLE IF EXISTS TEST");stat.execute("CREATE TABLE TEST(NAME VARCHAR)");stat.execute("INSERT INTO TEST VALUES('Hello World')");// use dataResultSet result = stat.executeQuery("select name from test ");int i = 1;while (result.next()) {System.out.println(i++ + ":" + result.getString("name"));}result.close();stat.close();conn.close();} catch (Exception e) {e.printStackTrace();}}public static void main(String[] args) {H2Demo h2 = new H2Demo();h2.startServer();h2.useH2();h2.stopServer();System.out.println("==END==");}} 

 

实例二：

import java.sql.*;public class MenTest {public void runInsertDelete() {try {String sourceURL = "jdbc:h2:./h2db/sample";// H2DB// mem// modeString user = "sa";String key = "";try {Class.forName("org.h2.Driver");// HSQLDB Driver} catch (Exception e) {e.printStackTrace();}Connection conn = DriverManager.getConnection(sourceURL, user, key);// 把驱动放入连接Statement stmt = conn.createStatement();// 创建一个 Statement 对象来将 SQL 语句发送到数据库。// stmt.executeUpdate("DELETE FROM mytable WHERE name=/'NO.2/'");// 执行方法找到一个与 methodName 属性同名的方法，并在目标上调用该方法。// stmt.execute("CREATE TABLE idtable(id INT,name VARCHAR(100));");stmt.execute("INSERT INTO test VALUES('MuSoft')");stmt.execute("INSERT INTO test VALUES('StevenStander')");stmt.close();conn.close();} catch (SQLException sqle) {System.out.println("SQL ERROR!");}}public void query(String SQL) {try {String sourceURL = "jdbc:h2:./h2db/sample";String user = "sa";String key = "";try {Class.forName("org.h2.Driver");} catch (Exception e) {e.printStackTrace();}Connection conn = DriverManager.getConnection(sourceURL, user, key);// 把驱动放入连接Statement stmt = conn.createStatement();// 创建一个 Statement 对象来将 SQL// 语句发送到数据库。ResultSet rset = stmt.executeQuery(SQL);// 执行方法找到一个与 methodName// 属性同名的方法，并在目标上调用该方法。while (rset.next()) {System.out.println(" " + rset.getString("name"));}rset.close();stmt.close();conn.close();} catch (SQLException sqle) {System.err.println(sqle);}}public static void main(String[] args) {MenTest mt = new MenTest();mt.runInsertDelete();mt.query("SELECT * FROM test");}} 

 

实例三：

 

import java.sql.*;public class UpdateTest {public void runInsertDelete() {try {String sourceURL = "jdbc:h2:./h2db/sample";// H2 databaseString user = "sa";String key = "";try {Class.forName("org.h2.Driver");// H2 Driver} catch (Exception e) {e.printStackTrace();}Connection conn = DriverManager.getConnection(sourceURL, user, key);Statement stmt = conn.createStatement();stmt.execute("DROP TABLE if EXISTS mytable");stmt.execute("CREATE TABLE mytable(name VARCHAR(100),sex VARCHAR(10))");stmt.executeUpdate("INSERT INTO mytable VALUES('Steven Stander','male')");stmt.executeUpdate("INSERT INTO mytable VALUES('Elizabeth Eames','female')");stmt.executeUpdate("DELETE FROM mytable WHERE sex='male'");ResultSet rset = stmt.executeQuery("select name,sex from mytable");while (rset.next()) {System.out.println(rset.getString("name") + " "+ rset.getString("sex"));}rset.close();stmt.close();conn.close();} catch (SQLException sqle) {System.err.println(sqle);}}public static void main(String[] args) {new UpdateTest().runInsertDelete();}} 

 

实例四：

 

import java.sql.*; public class SelectTest {public void query(String SQL) { try { // //String sourceURL = "jdbc:h2:h2/bin/mydb"; String sourceURL = "jdbc:h2:./h2db/sample"; String user = "sa"; String key = ""; try { Class.forName("org.h2.Driver"); } catch (Exception e) { e.printStackTrace(); } Connection conn = DriverManager.getConnection(sourceURL, user, key); Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery(SQL); while (rset.next()) { System.out.println(rset.getString("name")+ " "); } rset.close(); stmt.close(); conn.close(); } catch (SQLException sqle) { System.err.println(sqle); } } public static void main(String[] args) {SelectTest tt = new SelectTest(); tt.query("select * from test"); }} 

 

 

实例五：

 

import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import org.h2.tools.Server;public class MixedMode {public static void main(String[] args) throws Exception {Server server = Server.createTcpServer(new String[] { "-tcpPort","9080" });server.start();Class.forName("org.h2.Driver");Connection conn = DriverManager.getConnection("jdbc:h2:./h2db/sample","sa", "");Statement stat = conn.createStatement();// stat.execute("DROP TABLE TIMER IF EXISTS");// stat.execute("CREATE TABLE TIMER(ID INT PRIMARY KEY, TIME VARCHAR)");System.out.println("Execute this a few times: SELECT TIME FROM TIMER");try {stat.execute("MERGE INTO TIMER VALUES(1, NOW())");ResultSet rset = stat.executeQuery("select id,TIME from TIMER");while (rset.next()) {System.out.println(rset.getString("id") + " "+ rset.getString("time"));}rset.close();Thread.sleep(10);} catch (SQLException e) {System.out.println("Error:" + e.toString());}stat.close();conn.close();server.stop();}}