h2使用心得

您所在的位置：网站首页 › 吉比特h2-2密码 › h2使用心得

h2使用心得

2023-04-15 11:46| 来源: 网络整理| 查看: 265

前提条件

以下结论基于这些前提条件：

1. H2版本 = 2.1.214

2. Druid连接池，版本 1.1.10

3. jdk11

4. 内存模式下，切实默认的dbUrl，如：

jdbc:h2:mem:db1 结论关闭数据库报错？

1. 直接获取连接的方式下，该连接执行了shutdown数据库后，该连接再执行close是不会报错抛异常的：

import java.sql.*; public class TestH2DB { public static void main(String[] args) throws Exception { // testEmbed(); testMem(); } public static void testEmbed() throws Exception { Connection connection = DriverManager.getConnection("jdbc:h2:./db_embed"); commonCode(connection); } /** * test passed * * @throws Exception */ public static void testMem() throws Exception { Connection connection = DriverManager.getConnection("jdbc:h2:mem:db1;DB_CLOSE_ON_EXIT=FALSE"); //DriverManager.getConnection("jdbc:h2:mem:db1;DB_CLOSE_ON_EXIT=FALSE"); commonCode(connection); // connection = DriverManager.getConnection("jdbc:h2:mem:db1"); // queryData(connection); //不报异常 connection.close(); } public static void insertData(Connection connection) throws Exception { Statement statement = connection.createStatement(); statement.executeUpdate("create table person(id integer, name varchar(100), age integer, dis varchar(100))"); for (int i = 0; i < 100; i++) { statement.executeUpdate("insert into person(id, name, age, dis) values(" + i + ",'jinfeng', 12313, 'ijiofjwojfiweojfiowjiefwi')"); } statement.close(); } public static void commonCode(Connection connection) throws Exception { insertData(connection); queryData(connection); // dropAllOfDB(connection); // connection.close(); shutDown(connection); // connection.close(); /*try { connection = DriverManager.getConnection("jdbc:h2:mem:db1"); queryData(connection); } catch (Exception e) { e.printStackTrace(); }*/ // connection.close(); } private static void shutDown(Connection connection) throws Exception { // truncateTable(connection); // dropAllOfDB(connection); final boolean shutdown = connection.createStatement().execute("SHUTDOWN COMPACT"); System.out.println(shutdown); } private static void truncateTable(Connection connection) throws Exception { final Statement statement = connection.createStatement(); statement.execute("TRUNCATE TABLE person"); statement.close(); } private static void dropAllOfDB(Connection connection) throws Exception { final boolean execute = connection.createStatement().execute("DROP ALL OBJECTS"); System.out.println(execute); } private static void queryData(Connection connection) throws Exception { Statement preparedStatement = connection.createStatement(); ResultSet resultSet = preparedStatement.executeQuery("select * from person limit 1"); while (resultSet.next()) { int id = resultSet.getInt("id"); String name = resultSet.getString("name"); System.out.println(String.format("id=%s, name=%s", id, name)); } resultSet.close(); preparedStatement.close(); } }

2. 但是如果该链接是从Druid连接池中获取的，执行shutdown后，再执行close，就会报错了（至于其它的连接池是否也会这样，还没有实践，以后有机会再实践，应该跟连接池底层的实现有关）。

package com.jfqqqq.h2gis; import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.pool.DruidPooledConnection; import org.h2.jdbc.JdbcSQLNonTransientConnectionException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class TestH2DBWuthDataSourcePool { public static void main(String[] args) throws Exception { // testEmbed(); testMem(); } /** * test passed * * @throws Exception */ public static void testMem() throws Exception { DruidDataSource druidDataSource = new DruidDataSource(); druidDataSource.setUrl("jdbc:h2:mem:db1"); insertData(druidDataSource); queryData(druidDataSource); final DruidDataSource dataSource = druidDataSource; final DruidPooledConnection connection = dataSource.getConnection(); final Statement statement = connection.createStatement();//); final boolean res = statement.execute("SHUTDOWN COMPACT");//statement.execute("DROP ALL OBJECTS"); /* 这段代码同理，在try的圆括号中的connection会被默认执行close，就会报错 try ( final DruidPooledConnection connection = dataSource.getConnection(); final Statement statement = connection.createStatement()) { // final DruidDataSource dataSource = entry.getValue(); // final DruidPooledConnection connection = dataSource.getConnection(); final boolean res = statement.execute("SHUTDOWN COMPACT");//statement.execute("DROP ALL OBJECTS"); // connection.close(); // dataSource.close(); } catch (JdbcSQLNonTransientConnectionException jd) { // logger.info("mem db closed..."); System.out.println("ddddd"); } catch (Exception e) { e.printStackTrace(); }*/ connection.close(); druidDataSource.close(); try { Connection connection2 = DriverManager.getConnection("jdbc:h2:mem:db1"); queryData(connection2); } catch (Exception e) { e.printStackTrace(); }// connection.close(); } public static void insertData(DruidDataSource dataSource) throws Exception { final DruidPooledConnection connection = dataSource.getConnection(); Statement statement = connection.createStatement(); statement.executeUpdate("create table person(id integer, name varchar(100), age integer, dis varchar(100))"); for (int i = 0; i < 100; i++) { statement.executeUpdate("insert into person(id, name, age, dis) values(" + i + ",'jinfeng', 12313, 'ijiofjwojfiweojfiowjiefwi')"); } statement.close(); //数据库连接池关闭，是否会导致H2关闭。分析：不会，因为连接池仅仅是回收了而已，不是真的关闭。 connection.close(); } private static void queryData(Connection connection) throws Exception { Statement preparedStatement = connection.createStatement(); ResultSet resultSet = preparedStatement.executeQuery("select * from person limit 1"); while (resultSet.next()) { int id = resultSet.getInt("id"); String name = resultSet.getString("name"); System.out.println(String.format("id=%s, name=%s", id, name)); } resultSet.close(); preparedStatement.close(); connection.close(); } private static void queryData(DruidDataSource dataSource) throws Exception { final DruidPooledConnection connection = dataSource.getConnection(); Statement preparedStatement = connection.createStatement(); ResultSet resultSet = preparedStatement.executeQuery("select * from person limit 1"); while (resultSet.next()) { int id = resultSet.getInt("id"); String name = resultSet.getString("name"); System.out.println(String.format("id=%s, name=%s", id, name)); } resultSet.close(); preparedStatement.close(); connection.close(); } } 关闭连接就会关闭数据库

3. 直接获取连接到方式下，连接close了，就会关闭H2的DB实例（默认dbUrl连接地址下，如），Druid下，就是得关闭连接池（因为连接池的连接的close只是归还而已）。

package com.jfqqqq.h2gis; import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.pool.DruidPooledConnection; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; /** * 测试，直接获取connection的情况下，该connection执行shutdown后，connection再close()是否会报错。 */ public class TestH2WaysToCloseDB { public static void main(String[] args) throws Exception { // testDruid(); testJDBCDirectConnection(); } public static void testJDBCDirectConnection() throws Exception{ Connection connection = DriverManager.getConnection("jdbc:h2:mem:db1"); //插入数据 insertData(connection); //查询数据 queryData(connection); //关闭连接池 connection.close(); //新建立一个连接，再查询报错，表已经不存在了 connection = DriverManager.getConnection("jdbc:h2:mem:db1"); queryData(connection); } public static void testDruid()throws Exception { DruidDataSource druidDataSource = new DruidDataSource(); druidDataSource.setUrl("jdbc:h2:mem:db1"); //插入数据 insertData(druidDataSource.getConnection()); //查询数据 queryData(druidDataSource.getConnection()); //关闭连接池; 连接池的连接执行close也不会关闭，因为这是被回收了 druidDataSource.close(); //新建立一个连接，再查询报错，表已经不存在了 Connection connection = DriverManager.getConnection("jdbc:h2:mem:db1"); queryData(connection); } public static void insertData(Connection connection) throws Exception { // final DruidPooledConnection connection = dataSource.getConnection(); Statement statement = connection.createStatement(); statement.executeUpdate("create table person(id integer, name varchar(100), age integer, dis varchar(100))"); for (int i = 0; i < 100; i++) { statement.executeUpdate("insert into person(id, name, age, dis) values(" + i + ",'jinfeng', 12313, 'ijiofjwojfiweojfiowjiefwi')"); } statement.close(); //数据库连接池关闭，是否会导致H2关闭。分析：不会，因为连接池仅仅是回收了而已，不是真的关闭。 // connection.close(); } private static void queryData(Connection connection) throws Exception { Statement preparedStatement = connection.createStatement(); ResultSet resultSet = preparedStatement.executeQuery("select * from person limit 1"); while (resultSet.next()) { int id = resultSet.getInt("id"); String name = resultSet.getString("name"); System.out.println(String.format("id=%s, name=%s", id, name)); } resultSet.close(); preparedStatement.close(); connection.close(); } }

4.持续更新

【本文地址】

h2使用心得

h2使用心得

今日新闻

推荐新闻