网站导航
> 文章列表 > 8.java程序员必知必会类库之嵌入式SQL数据库

8.java程序员必知必会类库之嵌入式SQL数据库

网友: 文章列表 2024-03-22 03:35:09

8.java程序员必知必会类库之嵌入式SQL数据库

前言

嵌入式内存数据库，作为嵌入到应用内部的数据库，在正常生产业务流程中使用不多。现在一般公司通用架构都是应用和数据分离，解耦数据和应用。但是，在某些特殊场景中，这种嵌入式数据库是比较好的选择。

  1. 在某些单元测试的时候，如果需要一个数据库验证你的SQL脚本，此时H2就是一个很好的选择
  2. 应用需要通过SQL计算大批量指标，这些指标如果放在mysql这种可能会很慢，通过内存计算管理会极大提高效率
  3. 开发的程序里面包含很多指标计算逻辑（通过SQL计算），当需要导出作为依赖包运行的时候，此时内存数据库就是一个很好的选择

1. H2

1.1 简介

  1. h2采用纯Java编写，因此不受平台的限制。
  2. h2只有一个jar文件，十分适合作为嵌入式数据库试用。
  3. h2提供了一个十分方便的web控制台用于操作和管理数据库内容。
  4. 开源代码，方便修改，自定义函数等需求

1.2 使用

1.2.1 pom坐标导入

<!-- https://mvnrepository.com/artifact/com.h2database/h2 -->
<dependency><groupId>com.h2database</groupId><artifactId>h2</artifactId><version>2.1.210</version><scope>test</scope>
</dependency>

1.2.2 demo

1.2.2.1 H2连接工具类

public class H2DbUtil {private static final ArrayBlockingQueue<Connection> CONNECTION_POOL = new ArrayBlockingQueue<>(50);static {try {synchronized (CONNECTION_POOL) {Class.forName("org.h2.Driver");//String url = "jdbc:h2:mem:pbocdb;MODE=MySQL;DB_CLOSE_DELAY=-1;MULTI_THREADED=1;MV_STORE=FALSE;LOG=0;REDO_LOG_BINARY=0;UNDO_LOG=0";String url = "jdbc:h2:~/H2Database/h2/bin/test";for (int i = 0; i < 10; i++) {CONNECTION_POOL.add(DriverManager.getConnection(url, "sa", ""));}}} catch (Exception e) {e.printStackTrace();throw new RuntimeException(e);}}public static List<Map<String, Object>> execute(String sql, String reportNo) throws Exception {List<Map<String, Object>> records = new ArrayList<>();Connection connection = null;try {connection = CONNECTION_POOL.take();PreparedStatement preparedStatement = connection.prepareStatement(sql);int count = preparedStatement.getParameterMetaData().getParameterCount();for (int i = 1; i <= count; i++) {preparedStatement.setString(i, reportNo);}ResultSet resultSet = preparedStatement.executeQuery();ResultSetMetaData md = resultSet.getMetaData();int columnCount = md.getColumnCount();// 数据Map<String, Object> rowData;while (resultSet.next()) {rowData = new HashMap<>(columnCount);for (int i = 1; i <= columnCount; i++) {String columnName = md.getColumnLabel(i).toLowerCase().replaceAll("[ ()'`\\r\\n\\t]","");columnName = columnName.replaceAll("public\\\\.", "");rowData.put(columnName, resultSet.getObject(i));}records.add(rowData);}} finally {if (connection != null) {release(connection);}}return records;}public static Connection getConnection() throws Exception {return CONNECTION_POOL.take();}public static void release(Connection connection){if (connection != null) {try {CONNECTION_POOL.put(connection);} catch (Exception ignored) {}}}}

1.2.2.2 测试类

通过H2执行脚本示例如下：

@Test
public void testH2() throws Exception{Connection connection = H2DbUtil.getConnection();Statement stmt = connection.createStatement();// 如果存在USERS表就先删除USERS表stmt.execute("DROP TABLE IF EXISTS USERS");// 创建users表stmt.execute("create table users("+ "    id int primary key,"+ "    name varchar(40),"+ "    password varchar(40))");// 新增stmt.executeUpdate("INSERT INTO users VALUES(1,'张三','12')");stmt.executeUpdate("INSERT INTO users VALUES(2,'李四','34')");stmt.executeUpdate("INSERT INTO users VALUES(3,'王五','56')");stmt.executeUpdate("INSERT INTO users VALUES(4,'麻六','78')");stmt.executeUpdate("INSERT INTO users VALUES(5,'邹七','90')");List<Map<String, Object>> execute = H2DbUtil.execute("select * from  users where  id = ?", "1");//遍历结果集for (Map<String, Object> objectMap : execute) {System.out.println(objectMap.toString());}H2DbUtil.release(connection);
}

代码输出结果：

{name=张三, password=12, id=1}

1.3 界面展示

1.3.1 新建一个空的springboot web项目

pom文件如下：


<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.1.3.RELEASE</version><relativePath/> <!-- lookup parent from repository --></parent><groupId>com.example</groupId><artifactId>demo</artifactId><version>0.0.1-SNAPSHOT</version><name>demo</name><description>Demo project for Spring Boot</description><properties><java.version>8</java.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>com.h2database</groupId><artifactId>h2</artifactId><scope>runtime</scope></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin></plugins></build></project>

1.3.2 springboot配置文件添加如下内容

server.port=8080
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console
spring.h2.console.settings.web-allow-others=true

1.3.3 访问页面

启动项目，访问地址 ： http://localhost:8080/h2-console/login.jsp
可以看到如下界面：
8.java程序员必知必会类库之嵌入式SQL数据库
点击连接，进入展示如下，可以看到界面类似我们常用的数据库连接客户端navicat。可以在窗口执行数据的增删改查，但是注意这数据都是在内存中，应用重启，数据会丢失。
8.java程序员必知必会类库之嵌入式SQL数据库

1.4 注意事项

  1. H2 维护的数据都是在内存中，应用重启数据会丢失
  2. H2 SQL语法和mysql上面极度相似，但是在具体的函数处理上面，两边处理的结果可能会有一些细微的差异，如果你想在H2上面跑MySql的脚本，要提前感知可能的差异性
  3. 如果比对出来不一致，可以将源码拉下来调整相关源码，做兼容处理，但是这只能查漏补缺，只有发现不一致，针对性调整。比如发现日期函数处理和mysql有差异，那可以调整源码对应函数做兼容，但是这没法发现所有不一致性。
  4. 在界面上，可能会误删除连接配置项，然后项目重启，浏览器重启还是无法连接H2,这是因为删除信息会写到本地磁盘里面， 需要删除window用户目录下面的h2.server.properties文件，liunx 是home文件下。

1.5 进阶

1.5.1 自定义函数

1.5.1.1 自定义函数类

public class CustomFunction {public static String hi() {return "hello";}public static String my_uuid() {return "test" + UUID.randomUUID().toString();}public static String now() {SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");Date date = new Date();String dateStr = simpleDateFormat.format(date);return dateStr;}public static String getIp() {try {InetAddress addr = InetAddress.getLocalHost();// 获得本机IPreturn addr.getHostAddress();} catch (UnknownHostException e) {e.printStackTrace();return "未知的IP地址";}}public static String date_format(String date, String pattern) {if (date != null) {SimpleDateFormat sdf = new SimpleDateFormat(pattern);try {Date temp = sdf.parse(date);return sdf.format(temp);} catch (ParseException e) {e.printStackTrace();}}return "";}
}

1.5.1.2 测试类

@BeforeClass
public static void testH2Function() throws Exception {Connection connection = H2DbUtil.getConnection();// 0、注册hi函数的SQL语句String sql0 = "CREATE ALIAS IF NOT EXISTS hello FOR \\"com.wanlong.memoryDB.h2.CustomFunction.hi\\"";// 1、注册uuid函数的SQL语句String sql1 = "CREATE ALIAS IF NOT EXISTS my_uuid FOR \\"com.wanlong.memoryDB.h2.CustomFunction.my_uuid\\"";// 2、注册currentTime函数的SQL语句String sql2 = "CREATE ALIAS IF NOT EXISTS currentTime FOR \\"com.wanlong.memoryDB.h2.CustomFunction.now\\"";// 3、注册IP函数的SQL语句String sql3 = "CREATE ALIAS IF NOT EXISTS IP FOR \\"com.wanlong.memoryDB.h2.CustomFunction.getIp\\"";// 4、注册date_format函数的SQL语句String sql4 = "CREATE ALIAS IF NOT EXISTS date_format FOR \\"com.wanlong.memoryDB.h2.CustomFunction.date_format\\"";Statement stmt = null;// 获取Statement对象stmt = connection.createStatement();// 添加要执行的SQLstmt.addBatch(sql0);stmt.addBatch(sql1);stmt.addBatch(sql2);stmt.addBatch(sql3);stmt.addBatch(sql4);// 批量执行stmt.executeBatch();System.out.println("H2数据库扩展函数注册成功！");H2DbUtil.release(connection);
}@Test
public void testCustomeFuntion() throws Exception {Connection connection = H2DbUtil.getConnection();Statement stmt = connection.createStatement();// 如果存在USERS表就先删除USERS表List<Map<String, Object>> execute = H2DbUtil.execute("select my_uuid()", "1");for (Map<String, Object> objectMap : execute) {System.out.println(objectMap.toString());}H2DbUtil.release(connection);
}

1.5.2.3 代码运行结果

可以看到，自定义函数执行了

 H2数据库扩展函数注册成功！
{my_uuid=test90899962-93e2-4f5e-be16-d6ca52c3a98c}

2 参考文献：

H2官网
H2参考博客

网络标签：

相关问题