一、引言

作者目前在做的平台使用到了H2,这里介绍下使用场景、使用方式,出于以下两个原因会使用H2:

1、平台化的项目一般是用户使用脚本或者sql进行通用的执行,这样可以实现低代码平台,不需要管理类之间的引入、依赖、编译,页面上点点点和输入就可以了,所以很多时候需要把数据放入H2进行sql解析实现跨库、跨实例、跨服务的数据分析

2、在数据集合写起来非常复杂的时候,举个例子:集合a、b,ab的对象有字段c是一一对应的,然后集合a的其他字段和b元素下面挂着的集合又是对应的,这个组合写起来就很麻烦,但是如果存入H2用sql进行关联组合,形成一张大宽表,那么接下来的逻辑和分析验证就会好做。

二、H2介绍

H2是一个用Java编写的开源关系型数据库管理系统(RDBMS)。它被设计为一个嵌入式数据库,可以作为应用程序的一部分直接嵌入到Java应用程序中使用,也可以作为独立的数据库服务器运行。

H2数据库具有以下特点:

1. 嵌入式数据库:H2数据库可以作为一个库文件嵌入到Java应用程序中,这样应用程序可以直接访问和管理数据库,而无需额外的数据库服务器。
2. 支持多种模式:H2数据库支持多种模式,包括内存模式、磁盘模式和混合模式。内存模式可以用于临时数据存储,磁盘模式可以持久化数据到磁盘,混合模式可以将数据存储在内存和磁盘上,提供更高的性能和可靠性。
3. 支持多种数据库引擎:H2数据库支持多种数据库引擎,包括嵌入式模式、服务器模式和集群模式。嵌入式模式适用于单个应用程序,服务器模式适用于多个应用程序共享数据库,集群模式适用于高可用性和负载均衡的场景。
4. 支持标准SQL语法:H2数据库支持标准的SQL语法,包括DDL(数据定义语言)、DML(数据操作语言)和DQL(数据查询语言)。它还支持事务、索引、触发器、存储过程和用户定义函数等高级特性。
5. 轻量级和高性能:H2数据库是一个轻量级的数据库管理系统,具有快速的启动时间和低内存消耗。它采用了高效的数据存储和索引算法,提供了优秀的读写性能。
6. 跨平台支持:H2数据库可以在多个平台上运行,包括Windows、Linux和Mac OS等。

三、使用

1、Pom

要引入H2的依赖,还有链接池有已经封装好的,毕竟不能用一下h2就开一个链接,这个消耗的句柄和socket就可怕了。链接复用是必须的。

com.h2databaseh22.2.220com.zaxxerHikariCP4.0.3

2、链接

这里只要引入H2的依赖包就会自动在运行时创建

url = “jdbc:h2:mem:testdb”;

username = “sa”;
password = “”;

的内存数据库,所以要是不用很多包是不能随便引入的,你不知道这些包都自己做了什么事。

public class H2ConnectionPool {private static final String url = "jdbc:h2:mem:testdb";private static final String username = "sa";private static final String password = "";private static final HikariConfig config = new HikariConfig();private static final HikariDataSource dataSource;static {config.setJdbcUrl(url);config.setUsername(username);config.setPassword(password);// 设置连接池大小,默认为10config.setMaximumPoolSize(10);dataSource = new HikariDataSource(config);}public static Connection getConnection() throws SQLException {return dataSource.getConnection();}public static void close() {dataSource.close();}}

3、建表

实时动态的建表就需要知道表名称,字段名称和类型,有两种情况,一种是从数据库查出来的ResultSet,经过转化之后会变成List<HashMap>,hashmap是表的字段名和值,每行数据形成了一个map,做过底层通用转化的应该都知道,不清楚的同学也没关系,可以私聊作者。

这种情况就可以根据map生成建表语句。

public static boolean createTable(String tableName, HashMap map) throws SQLException {if (StringUtilsExt.isBlank(tableName)) {return false;}if (map == null) {return false;}Connection conn = null;Statement stmt = null;try {// 连接到H2数据库conn = H2ConnectionPool.getConnection();// 创建Statement对象stmt = conn.createStatement();// 创建表的SQL语句String sql = getCreateSql(tableName, map);// 执行SQL语句stmt.executeUpdate(sql);} finally {if (conn != null) {conn.close();}if (stmt != null) {stmt.close();}}return true;}private static String getCreateSql(String tableName, HashMap map) {StringBuilder builder = new StringBuilder("CREATE TABLE " + tableName + " (");map.forEach((key, value) -> {builder.append(key);builder.append(" ");if (value instanceof Long) {builder.append("bigint");} else if (value instanceof Integer) {builder.append("int");} else if (value instanceof String) {builder.append("varchar");} else if (value instanceof BigDecimal) {builder.append("decimal");} else if (value instanceof Boolean) {builder.append("int");} else if (value instanceof Timestamp) {builder.append("datetime");}builder.append(",");});builder.deleteCharAt(builder.length() - 1);// 创建表的SQL语句return builder + ")";}

第二种情况是调用接口或者查其他一些数据的时候,得到的是List,那就要根据反射获取T的字段名称和值了。

public boolean createTableByT(String tableName, T t) throws SQLException {if (StringUtilsExt.isBlank(tableName)) {return false;}if (t == null) {return false;}Connection conn = null;Statement stmt = null;try {// 连接到H2数据库conn = H2ConnectionPool.getConnection();// 创建Statement对象stmt = conn.createStatement();// 创建表的SQL语句String sql = getCreateSqlByT(tableName, t);// 执行SQL语句stmt.executeUpdate(sql);} finally {if (conn != null) {conn.close();}if (stmt != null) {stmt.close();}}return true;}private String getCreateSqlByT(String tableName, T t) {StringBuilder builder = new StringBuilder("CREATE TABLE " + tableName + " (");Class

4、插数据

第一种是根据数据库转化的map插入

public static boolean insertByHashMap(String tableName, List listMap) throws SQLException {Connection connection = null;PreparedStatement statement = null;try {// 连接到H2数据库connection = H2ConnectionPool.getConnection();HashMap indexMap = new HashMap(listMap.get(0).size());String sql = getInsertSql(tableName, listMap.get(0), indexMap);statement = connection.prepareStatement(sql);setValue(listMap, indexMap, statement);// 执行批处理int[] rowsInserted = statement.executeBatch();if (rowsInserted.length != listMap.size()) {return false;}} finally {if (connection != null) {connection.close();}if (statement != null) {statement.close();}}return true;}private static void setValue(List listMap, HashMap indexMap,PreparedStatement statement) throws SQLException {// 批量插入数据for (HashMap m : listMap) {m.forEach((key, value) -> {if (value instanceof Long) {try {statement.setLong(indexMap.get(key), (Long)value);} catch (SQLException e) {throw new RuntimeException(e);}} else if (value instanceof Integer) {try {statement.setInt(indexMap.get(key), (Integer)value);} catch (SQLException e) {throw new RuntimeException(e);}} else if (value instanceof String) {try {statement.setString(indexMap.get(key), (String)value);} catch (SQLException e) {throw new RuntimeException(e);}} else if (value instanceof BigDecimal) {try {statement.setBigDecimal(indexMap.get(key), (BigDecimal)value);} catch (SQLException e) {throw new RuntimeException(e);}} else if (value instanceof Boolean) {int flag = (Boolean) value ? 1 : 0;try {statement.setInt(indexMap.get(key), flag);} catch (SQLException e) {throw new RuntimeException(e);}} else if (value instanceof Byte) {try {statement.setByte(indexMap.get(key), (Byte) value);} catch (SQLException e) {throw new RuntimeException(e);}}});statement.addBatch();}}private static String getInsertSql(String tableName, HashMap map, HashMap indexMap) {StringBuilder builder = new StringBuilder("INSERT INTO " + tableName + " (");StringBuilder builder2 = new StringBuilder(" VALUES (");AtomicInteger index = new AtomicInteger(1);map.forEach((key, value) -> {builder.append(key);builder.append(" ");builder.append(",");builder2.append("?");builder2.append(" ");builder2.append(",");indexMap.put((String)key, index.get());index.getAndIncrement();});builder.deleteCharAt(builder.length() - 1);builder2.deleteCharAt(builder2.length() - 1);// 插入表的SQL语句return builder + ")" + builder2 + ")";}

第二种就是根据list插入数据

public boolean insertByList(String tableName, List list) throws SQLException {Connection connection = null;PreparedStatement statement = null;try {// 连接到H2数据库connection = H2ConnectionPool.getConnection();HashMap indexMap = new HashMap();String sql = getInsertSqlByList(tableName, list.get(0), indexMap);statement = connection.prepareStatement(sql);setValueByList(list, indexMap, statement);// 执行批处理int[] rowsInserted = statement.executeBatch();if (rowsInserted.length != list.size()) {return false;}} finally {if (connection != null) {connection.close();}if (statement != null) {statement.close();}}return true;}private void setValueByList(List list, HashMap indexMap, PreparedStatement statement) throws SQLException {// 批量插入数据for (T t : list) {Class clazz = t.getClass();Field[] fields = clazz.getDeclaredFields();for (Field field : fields) {field.setAccessible(true);String key = field.getName();Object value = null;try {value = field.get(t);} catch (IllegalAccessException e) {e.printStackTrace();}if (value instanceof Long) {try {statement.setLong(indexMap.get(key), (Long)value);} catch (SQLException e) {throw new RuntimeException(e);}} else if (value instanceof Integer) {try {statement.setInt(indexMap.get(key), (Integer)value);} catch (SQLException e) {throw new RuntimeException(e);}} else if (value instanceof String) {try {statement.setString(indexMap.get(key), (String)value);} catch (SQLException e) {throw new RuntimeException(e);}} else if (value instanceof BigDecimal) {try {statement.setBigDecimal(indexMap.get(key), (BigDecimal)value);} catch (SQLException e) {throw new RuntimeException(e);}} else if (value instanceof Boolean) {int flag = (Boolean) value ? 1 : 0;try {statement.setInt(indexMap.get(key), flag);} catch (SQLException e) {throw new RuntimeException(e);}} else if (value instanceof Byte) {try {statement.setByte(indexMap.get(key), (Byte) value);} catch (SQLException e) {throw new RuntimeException(e);}}}statement.addBatch();}}private String getInsertSqlByList(String tableName, T t, HashMap indexMap) {StringBuilder builder = new StringBuilder("INSERT INTO " + tableName + " (");StringBuilder builder2 = new StringBuilder(" VALUES (");AtomicInteger index = new AtomicInteger(1);Class clazz = t.getClass();Field[] fields = clazz.getDeclaredFields();for (Field field : fields) {field.setAccessible(true);String fieldName = field.getName();builder.append(fieldName);builder.append(" ");builder.append(",");builder2.append("?");builder2.append(" ");builder2.append(",");indexMap.put(fieldName, index.get());index.getAndIncrement();}builder.deleteCharAt(builder.length() - 1);builder2.deleteCharAt(builder2.length() - 1);// 插入表的SQL语句return builder + ")" + builder2 + ")";}

5、查询

查询没什么,执行sql就好,这里就能看到数据库查询出来的ResultSet被通用的转化

public static List query(String sql) throws SQLException {// 创建List对象来存储查询结果List resultList = new ArrayList();Connection connection = null;Statement stmt = null;ResultSet rs = null;try {connection = H2ConnectionPool.getConnection();// 执行查询stmt = connection.createStatement();rs = stmt.executeQuery(sql);// 获取结果集的元数据ResultSetMetaData metaData = rs.getMetaData();int columnCount = metaData.getColumnCount();// 处理查询结果while (rs.next()) {HashMap map = new HashMap(columnCount);for (int i = 1; i <= columnCount; i++) {map.put(metaData.getColumnName(i), rs.getObject(i));}resultList.add(map);}} finally {if (connection != null) {connection.close();}if (stmt != null) {stmt.close();}if (rs != null) {rs.close();}}return resultList;}

6、删表

public static boolean dropTable(String tableName) throws SQLException {if (StringUtilsExt.isBlank(tableName)) {return false;}Connection conn = null;Statement stmt = null;try {// 连接到H2数据库conn = H2ConnectionPool.getConnection();// 创建Statement对象stmt = conn.createStatement();String sql = "DROP TABLE " + tableName;stmt.executeUpdate(sql);} finally {if (conn != null) {conn.close();}if (stmt != null) {stmt.close();}}return true;}

四、总结

平台化的建设要考虑低代码和通用,不然源码处处改就很完蛋,这里作者介绍了H2做通用化跨库、跨实例、跨服务数据分析和逻辑,有兴趣的同学欢迎讨论!