H2内存数据库

最新推荐文章于 2024-05-30 10:22:50 发布

原创最新推荐文章于 2024-05-30 10:22:50 发布 · 469 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql #java

说明：

实现初始化内存数据库，sql从文件中读取

配置文件：

spring.jpa.database=h2
spring.jpa.show-sql=true
#ddl执行方式，update create 等
spring.jpa.hibernate.ddl-auto=update
#jdbc:h2:mem:yardman_usp_ea
#jdbc:h2:file:C:/Users/Administrator/Desktop/test/yardman_usp_ea
#h2 数据库文件地址 可配置为现场指定地址 IGNORECASE=TRUE 取消区分大小写
; spring.datasource.url=jdbc:h2:file:/opt/yardman/yardman-usp-ea/yardman_usp_ea;IGNORECASE=TRUE
spring.datasource.url=jdbc:h2:mem:yardman_usp_ea
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=ENC(SCQw/r/hdyHiBtiFrJm3lQ==)
spring.datasource.password=ENC(BUaPEKP8z0jOnUGFNstj2RFmLIpYx0XB)
jasypt.encryptor.iv-generator-classname=org.jasypt.iv.NoIvGenerator
# spring.datasource.schema=classpath:schema.sql
# spring.datasource.initialization-mode=always
spring.datasource.platform=h2
#web console 可远程访问 线上运行时可设置为false，避免暴露公网
spring.h2.console.settings.web-allow-others=true
#web console 路径
spring.h2.console.path=/h2-console
#web console访问内存数据库 线上运行时可设置为false，避免暴露公网
spring.h2.console.enabled=true
#h2初始化建表路径 ./usp-boot/docker/schema.sql
; analysis.config.agent.sql-url=/opt/yardman/yardman-usp-ea/schema.sql
analysis.config.agent.sql-url=./usp-boot/docker/schema.sql

引包：

    <dependency>
                <groupId>com.h2database</groupId>
                <artifactId>h2</artifactId>
                <version>1.4.191</version>
                <scope>runtime</scope>
            </dependency>

代码：



import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import com.migu.yardman.usp.ea.biz.config.AgentAnalysisConfigProperties;
import com.migu.yardman.usp.ea.biz.config.H2DataSourceConfig;
import com.migu.yardman.usp.ea.biz.utils.FileUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

/**
 * @author zc
 * @project usp-ea
 * @date 2022/12/1 9:28
 * @desc h2初始化建表
 */
@Service
public class H2InitDataService {


    private static final Logger LOG = LoggerFactory.getLogger(H2InitDataService.class);

    private H2DataSourceConfig h2DataSourceConfig;

    private FileUtils fileResourceUtils;

    private AgentAnalysisConfigProperties agentAnalysisConfigProperties;

    public H2InitDataService(FileUtils fileResourceUtils,
                             H2DataSourceConfig h2DataSourceConfig,
                             AgentAnalysisConfigProperties agentAnalysisConfigProperties) {
        this.fileResourceUtils = fileResourceUtils;
        this.h2DataSourceConfig = h2DataSourceConfig;
        this.agentAnalysisConfigProperties = agentAnalysisConfigProperties;
    }

    @PostConstruct
    public Boolean initDataBase() {
        Statement statement = null;
        Connection conn = null;
        Boolean isInitDataBase = false;
        try {
            Class.forName(h2DataSourceConfig.getDriverClassName());
            conn = DriverManager.getConnection(h2DataSourceConfig.getUrl(), h2DataSourceConfig.getUsername(), h2DataSourceConfig.getPassword());
            statement = conn.createStatement();
            LOG.info("H2数据库连接成功");
            String sql = fileResourceUtils.readFileToStr(agentAnalysisConfigProperties.getSqlUrl());
            LOG.info("读取文件sql：{}", sql);
            //创建表
            if (ObjectUtils.isNotEmpty(sql)) {
                statement.execute(sql);
                LOG.info("H2数据库表创建成功");
                isInitDataBase = true;
            }
        } catch (Exception e) {
            LOG.error("H2初始化数据库异常：", e);
        } finally {
            try {
                statement.close();
                conn.close();
            } catch (Exception e) {
                LOG.error("H2数据库关闭连接异常：", e);
            }
        }
        return isInitDataBase;
    }

}



import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;

/**
 * @author zc
 * @project usp-ea
 * @date 2022/12/1 10:13
 * @desc h2数据库配置
 */
@Configuration
@ConfigurationProperties(prefix = "spring.datasource")
public class H2DataSourceConfig {

    /**
     * 路径
     */
    private String url;

    /**
     * driver
     */
    private String driverClassName;
    /**
     * 用户名
     */
    private String username;
    /**
     * 密码
     */
    private String password;

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getDriverClassName() {
        return driverClassName;
    }

    public void setDriverClassName(String driverClassName) {
        this.driverClassName = driverClassName;
    }


    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }


}



import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ResourceLoader;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.FileInputStream;

/**
 * @author zc
 * @project usp-ea
 * @date 2022/12/1 9:28
 * @desc 读取指定文件内容
 */
@Component
public class FileUtils {

    private Logger LOG = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private ResourceLoader resourceLoader;

    /**
     * 读取文件数据
     *
     * @param filePath
     * @return
     */
    public String readFileToStr(String filePath) {
        String content = "";
        FileInputStream in = null;
        try {
            File file = new File(filePath);
            Long filelength = file.length();
            byte[] filecontent = new byte[filelength.intValue()];
            in = new FileInputStream(file);
            in.read(filecontent);
            content = new String(filecontent, "utf-8");
        } catch (Exception e) {
            LOG.error("读取文件信息异常：", e);
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (Exception e) {
                    LOG.error("关闭流异常：", e);
                }
            }
        }
        return content;
    }

}


import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;

/**
 * @author zc
 * @project usp-ea
 * @date 2022/11/24 14:06
 * @desc agent 配置文件
 */
@Configuration
@ConfigurationProperties(prefix = "analysis.config.agent")
public class AgentAnalysisConfigProperties {

   
    /**
     * sql
     */
    private String sqlUrl;

   

    public String getSqlUrl() {
        return sqlUrl;
    }

    public void setSqlUrl(String sqlUrl) {
        this.sqlUrl = sqlUrl;
    }

    
}

DROP TABLE IF EXISTS `usp_node_tag`;
CREATE TABLE `usp_node_tag`
(
    `id`         varchar(64)  NOT NULL COMMENT '主键',
    `tag`        varchar(64)  NOT NULL COMMENT '标签标识（实际上是业务ID的值）',
    `node_id`    varchar(64) DEFAULT NULL COMMENT '主机标识',
    `node_name`  varchar(255) NOT NULL COMMENT '主机名称',
    `type`       varchar(4)  DEFAULT NULL COMMENT '环境类型\r\n0：OpenStack\r\n1：Kubernetes',
    `created_at` datetime(0) DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`)
);