Java JDBC H2 内存数据库示例

喜欢ヅ旅行 2023-10-01 13:16 16阅读 0赞

在这个**H2 内存数据库示例**中，我们将数据库内容存储在系统的内存中。这里持久性发生在系统的内存上。在这个例子中，我们正在创建一个 java 类，展示如何加载驱动程序、创建数据库、创建表并将一些值插入到表中

**[H2][]**是 Java SQL 数据库的开源软件实现。H2的主要特点是。

*  非常快，开源，JDBC API
 *  嵌入式和服务器模式；内存数据库
 *  基于浏览器的控制台应用程序
 *  占用空间小：大约 1.5 MB jar 文件大小

所需的库

对于使用 H2 数据库，您需要下载

1.  [h2-1.4.182.jar][]

### 项目结构 ###

![H2 内存数据库示例][H2 1]

### 使用内存的 H2 数据库 ###

在这里，我们展示了使用 H2 内存数据库的 SQL 语句和 PreparedStatement 的实现。

在下面的代码中，您可以看到我们使用以下 H2 JDBC URL **jdbc:h2:mem:test;DB\_CLOSE\_DELAY=-1**来连接数据库

通过以下方式，将H2数据库内容存储在系统内存中

package com.h2.examples;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    // H2 In-Memory Database Example shows about storing the database contents into memory.
    
    public class H2MemoryDatabaseExample {
    
        private static final String DB_DRIVER = "org.h2.Driver";
        private static final String DB_CONNECTION = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1";
        private static final String DB_USER = "";
        private static final String DB_PASSWORD = "";
    
        public static void main(String[] args) throws Exception {
            try {
                insertWithStatement();
                insertWithPreparedStatement();
    
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    
        private static void insertWithPreparedStatement() throws SQLException {
            Connection connection = getDBConnection();
            PreparedStatement createPreparedStatement = null;
            PreparedStatement insertPreparedStatement = null;
            PreparedStatement selectPreparedStatement = null;
    
            String CreateQuery = "CREATE TABLE PERSON(id int primary key, name varchar(255))";
            String InsertQuery = "INSERT INTO PERSON" + "(id, name) values" + "(?,?)";
            String SelectQuery = "select * from PERSON";
    
            try {
                connection.setAutoCommit(false);
    
                createPreparedStatement = connection.prepareStatement(CreateQuery);
                createPreparedStatement.executeUpdate();
                createPreparedStatement.close();
    
                insertPreparedStatement = connection.prepareStatement(InsertQuery);
                insertPreparedStatement.setInt(1, 1);
                insertPreparedStatement.setString(2, "Jose");
                insertPreparedStatement.executeUpdate();
                insertPreparedStatement.close();
    
                selectPreparedStatement = connection.prepareStatement(SelectQuery);
                ResultSet rs = selectPreparedStatement.executeQuery();
                System.out.println("H2 In-Memory Database inserted through PreparedStatement");
                while (rs.next()) {
                    System.out.println("Id " + rs.getInt("id") + " Name " + rs.getString("name"));
                }
                selectPreparedStatement.close();
    
                connection.commit();
            } catch (SQLException e) {
                System.out.println("Exception Message " + e.getLocalizedMessage());
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                connection.close();
            }
        }
    
        private static void insertWithStatement() throws SQLException {
            Connection connection = getDBConnection();
            Statement stmt = null;
            try {
                connection.setAutoCommit(false);
                stmt = connection.createStatement();
                stmt.execute("CREATE TABLE PERSON(id int primary key, name varchar(255))");
                stmt.execute("INSERT INTO PERSON(id, name) VALUES(1, 'Anju')");
                stmt.execute("INSERT INTO PERSON(id, name) VALUES(2, 'Sonia')");
                stmt.execute("INSERT INTO PERSON(id, name) VALUES(3, 'Asha')");
    
                ResultSet rs = stmt.executeQuery("select * from PERSON");
                System.out.println("H2 In-Memory Database inserted through Statement");
                while (rs.next()) {
                    System.out.println("Id " + rs.getInt("id") + " Name " + rs.getString("name"));
                }
    
                stmt.execute("DROP TABLE PERSON");
                stmt.close();
                connection.commit();
            } catch (SQLException e) {
                System.out.println("Exception Message " + e.getLocalizedMessage());
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                connection.close();
            }
        }
    
        private static Connection getDBConnection() {
            Connection dbConnection = null;
            try {
                Class.forName(DB_DRIVER);
            } catch (ClassNotFoundException e) {
                System.out.println(e.getMessage());
            }
            try {
                dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
                return dbConnection;
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
            return dbConnection;
        }
    }

输出

H2 In-Memory Database inserted through Statement
    Id 1 Name Anju
    Id 2 Name Sonia
    Id 3 Name Asha
    H2 In-Memory Database inserted through PreparedStatement
    Id 1 Name Jose

[H2]: http://www.h2database.com/html/main.html
[h2-1.4.182.jar]: http://www.h2database.com/h2-2014-10-17.zip
[H2 1]: https://img-blog.csdnimg.cn/img_convert/88e813b33a6e873c9c2c2e0266654811.png