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 data ResultSet 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 // mode String 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 database String 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(); } }