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

wlyang666

已于 2023-04-21 21:22:54 修改

阅读量1.6k

点赞数 1

分类专栏： java程序员必知必会类库文章标签：数据库 java sql H2 嵌入式数据库

于 2023-04-21 21:21:16 首次发布

本文链接：https://blog.csdn.net/wlyang666/article/details/130157011

版权

java程序员必知必会类库专栏收录该内容

13 篇文章 2 订阅

订阅专栏

前言

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

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

1. H2

1.1 简介

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

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
可以看到如下界面：
在这里插入图片描述
点击连接，进入展示如下，可以看到界面类似我们常用的数据库连接客户端navicat。可以在窗口执行数据的增删改查，但是注意这数据都是在内存中，应用重启，数据会丢失。

1.4 注意事项

H2 维护的数据都是在内存中，应用重启数据会丢失
H2 SQL语法和mysql上面极度相似，但是在具体的函数处理上面，两边处理的结果可能会有一些细微的差异，如果你想在H2上面跑MySql的脚本，要提前感知可能的差异性
如果比对出来不一致，可以将源码拉下来调整相关源码，做兼容处理，但是这只能查漏补缺，只有发现不一致，针对性调整。比如发现日期函数处理和mysql有差异，那可以调整源码对应函数做兼容，但是这没法发现所有不一致性。
在界面上，可能会误删除连接配置项，然后项目重启，浏览器重启还是无法连接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();
            // 获得本机IP
            return 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();
    // 添加要执行的SQL
    stmt.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参考博客

wlyang666

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
8.java程序员必知必会类库之嵌入式SQL数据库

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

扫一扫