Java console 使用 H2 嵌入式数据库(jdbc,D

1. Maven 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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>jdbcMavneTest</artifactId>
    <version>1.0-SNAPSHOT</version>

    <packaging>jar</packaging>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <maven.compiler.source>11</maven.compiler.source>
        <maven.compiler.target>11</maven.compiler.target>
        <java.version>11</java.version>
    </properties>



    <dependencies>
        <!-- https://mvnrepository.com/artifact/com.h2database/h2 -->
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.4.200</version>
            <scope>runtime</scope>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.zaxxer/HikariCP-java7 -->
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP-java7</artifactId>
            <version>2.4.13</version>
        </dependency>


        <!-- https://mvnrepository.com/artifact/ch.qos.logback/logback-classic -->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>1.2.3</version>
            <scope>runtime</scope>
        </dependency>
    </dependencies>

</project>

注意 scpoe；从maven中复制过来的是 test,需要修改为：runtime

2. 编写代码：

import java.sql.*;
import java.util.UUID;

public class AppMain {

    //数据库连接URL，当前连接的是E:/temp/目录下的h2test数据库
    private static final String JDBC_URL = "jdbc:h2:file:E:\\temp\\h2test";
    //连接数据库时使用的用户名
    private static final String USER = "gacl";
    //连接数据库时使用的密码
    //private static final String PASSWORD = "123";
    private static final String PASSWORD = "";
    //连接H2数据库时使用的驱动类，org.h2.Driver这个类是由H2数据库自己提供的，在H2数据库的jar包中可以找到
    private static final String DRIVER_CLASS="org.h2.Driver";


    public static void main(String []args){
        Connection connection = null;
        Statement statement = null;

//        try {
//            Class.forName(DRIVER_CLASS);
//        }catch (ClassNotFoundException e){
//
//        }


        try {
            // 根据连接URL，用户名，密码获取数据库连接
            connection = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
            statement = connection.createStatement();

            //如果存在USER_INFO表就先删除USER_INFO表
            statement.execute("DROP TABLE IF EXISTS USER_INFO");

            //创建USER_INFO表
            statement.execute("CREATE TABLE USER_INFO(id VARCHAR(36) PRIMARY KEY, name VARCHAR(100), sex VARCHAR(4))");

            //新增
            statement.executeUpdate("INSERT INTO USER_INFO VALUES('" + UUID.randomUUID() + "','大日如来', '男')");
            statement.executeUpdate("INSERT INTO USER_INFO VALUES('" + UUID.randomUUID()+ "','青龙','男')");
            statement.executeUpdate("INSERT INTO USER_INFO VALUES('" + UUID.randomUUID()+ "','白虎','男')");
            statement.executeUpdate("INSERT INTO USER_INFO VALUES('" + UUID.randomUUID()+ "','朱雀','女')");
            statement.executeUpdate("INSERT INTO USER_INFO VALUES('" + UUID.randomUUID()+ "','玄武','男')");
            statement.executeUpdate("INSERT INTO USER_INFO VALUES('" + UUID.randomUUID()+ "','苍狼','男')");


            //查询
            ResultSet resultSet = statement.executeQuery("SELECT * FROM USER_INFO");

            //显示
            while (resultSet.next()){
                System.out.println(resultSet.getString("id")+ "," + resultSet.getString("name") + "," + resultSet.getString("sex"));
            }
        }catch (SQLException e){
            String err = e.getStackTrace().toString();
            System.out.println("1");
            System.out.println(err);
        }finally {
            try {
                if(null != statement){
                    statement.close();
                }
                if(null != connection){
                    connection.close();
                }
            }catch (SQLException e){
                String err = e.getStackTrace().toString();
                System.out.println("2");
                System.out.println(err);
            }

        }

        System.out.println("test");
    }
}

不用调用 Class.forName(DRIVER_CLASS); 任然可以使用

3. 优化及JDBC Batch 批处理版：

import java.sql.*;
import java.util.UUID;

public class AppMain {

    private static final String[] names = {"小青","小白", "小朱","小玄","小苍","小如"};

    //数据库连接URL，当前连接的是E:/temp/目录下的h2test数据库
    private static final String JDBC_URL = "jdbc:h2:file:E:\\temp\\h2test";
    //连接数据库时使用的用户名
    private static final String USER = "gacl";
    //连接数据库时使用的密码
    //private static final String PASSWORD = "123";
    private static final String PASSWORD = "";
    //连接H2数据库时使用的驱动类，org.h2.Driver这个类是由H2数据库自己提供的，在H2数据库的jar包中可以找到
    private static final String DRIVER_CLASS="org.h2.Driver";


    public static void main(String []args){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

//        try {
//            Class.forName(DRIVER_CLASS);
//        }catch (ClassNotFoundException e){
//            e.printStackTrace();
//        }


        try {
            // 根据连接URL，用户名，密码获取数据库连接
            connection = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);

            (preparedStatement = connection.prepareStatement("DROP TABLE IF EXISTS USER_INFO")).execute();
            if(null != preparedStatement){
                preparedStatement.close();
            }

            (preparedStatement = connection.prepareStatement("CREATE TABLE USER_INFO(id VARCHAR(36) PRIMARY KEY, name VARCHAR(100), sex VARCHAR(4))")).execute();
            if(null != preparedStatement){
                preparedStatement.close();
            }


            preparedStatement = connection.prepareStatement("INSERT INTO USER_INFO VALUES(?,?,?)");

            for(String var : names){
                UUID id = UUID.randomUUID();
                preparedStatement.setObject(1, id);
                preparedStatement.setString(2, names[(int)(Math.abs(id.getLeastSignificantBits()) % names.length)]);
                preparedStatement.setString(3, 0 == id.getLeastSignificantBits() % 2 ? "男":"女");

              // JDBC Batch 批处理
                preparedStatement.addBatch();
                //preparedStatement.executeUpdate();
            }

            //JDBC Batch 批处理
            preparedStatement.executeBatch();

            //查询
            resultSet = connection.prepareStatement("SELECT * FROM USER_INFO").executeQuery();

            //显示
            while (resultSet.next()){
                System.out.println(resultSet.getString("id")+ "," + resultSet.getString("name") + "," + resultSet.getString("sex"));
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            try {

                if(null != resultSet){
                    resultSet.close();
                }

                if(null != preparedStatement){
                    preparedStatement.close();
                }
                if(null != connection){
                    connection.close();
                }
            }catch (SQLException e){
                e.printStackTrace();
            }
        }

        System.out.println("test");
    }
}

4. 事务：

import java.sql.*;
import java.util.UUID;

public class AppMain {

   private static final String[] names = {"小青", "小白", "小朱", "小玄", "小苍", "小如"};

   //数据库连接URL，当前连接的是E:/temp/目录下的h2test数据库
   private static final String JDBC_URL = "jdbc:h2:file:E:\\temp\\h2test";
   //连接数据库时使用的用户名
   private static final String USER = "gacl";
   //连接数据库时使用的密码
   //private static final String PASSWORD = "123";
   private static final String PASSWORD = "";
   //连接H2数据库时使用的驱动类，org.h2.Driver这个类是由H2数据库自己提供的，在H2数据库的jar包中可以找到
   private static final String DRIVER_CLASS = "org.h2.Driver";


   public static void main(String[] args) {
       Connection connection = null;
       PreparedStatement preparedStatement = null;
       ResultSet resultSet = null;

//        try {
//            Class.forName(DRIVER_CLASS);
//        }catch (ClassNotFoundException e){
//            e.printStackTrace();
//        }


       try {
           // 根据连接URL，用户名，密码获取数据库连接
           connection = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);

           (preparedStatement = connection.prepareStatement("DROP TABLE IF EXISTS USER_INFO")).execute();
           if (null != preparedStatement) {
               preparedStatement.close();
           }

           (preparedStatement = connection.prepareStatement("CREATE TABLE USER_INFO(id VARCHAR(36) PRIMARY KEY, name VARCHAR(100), sex VARCHAR(4))")).execute();
           if (null != preparedStatement) {
               preparedStatement.close();
           }


           preparedStatement = connection.prepareStatement("INSERT INTO USER_INFO VALUES(?,?,?)");

           for (String var : names) {
               UUID id = UUID.randomUUID();
               preparedStatement.setObject(1, id);
               preparedStatement.setString(2, names[(int) (Math.abs(id.getLeastSignificantBits()) % names.length)]);
               preparedStatement.setString(3, 0 == id.getLeastSignificantBits() % 2 ? "男" : "女");

                //JDBC Batch 批处理
               preparedStatement.addBatch();
               //preparedStatement.executeUpdate();
           }
          
           //JDBC Batch 批处理
           preparedStatement.executeBatch();

           //查询
           resultSet = connection.prepareStatement("SELECT * FROM USER_INFO").executeQuery();

           //显示
           while (resultSet.next()) {
               System.out.println(resultSet.getString("id") + "," + resultSet.getString("name") + "," + resultSet.getString("sex"));
           }


           //事务
           try {
               connection.setAutoCommit(false);

               preparedStatement = connection.prepareStatement("INSERT INTO USER_INFO VALUES(?,?,?)");
               preparedStatement.setObject(1, UUID.randomUUID());
               preparedStatement.setString(2, "aaa");
               preparedStatement.setString(3, "F");
               preparedStatement.execute();

               preparedStatement.clearParameters();
               preparedStatement.setObject(1, UUID.randomUUID());
               preparedStatement.setString(2, "bbb");
               preparedStatement.setString(3, "M");
               preparedStatement.execute();

               preparedStatement.clearParameters();
               preparedStatement.setObject(1, UUID.randomUUID());
               preparedStatement.setString(2, "ccc");
               preparedStatement.setString(3, "/");
               preparedStatement.execute();

               connection.commit();
           } catch (SQLException e) {
               connection.rollback();
               e.printStackTrace();
           } finally {
               connection.setAutoCommit(true);
           }


           //查询
           resultSet = connection.prepareStatement("SELECT * FROM USER_INFO").executeQuery();

           //显示
           while (resultSet.next()) {
               System.out.println(resultSet.getString("id") + "," + resultSet.getString("name") + "," + resultSet.getString("sex"));
           }


       } catch (SQLException e) {
           e.printStackTrace();
       } finally {
           try {

               if (null != resultSet) {
                   resultSet.close();
               }

               if (null != preparedStatement) {
                   preparedStatement.close();
               }
               if (null != connection) {
                   connection.close();
               }
           } catch (SQLException e) {
               e.printStackTrace();
           }
       }


       System.out.println("test");
   }
}

5. HikariCP 连接池：

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import javax.sql.DataSource;
import java.sql.*;
import java.util.UUID;

public class AppMain {
    private enum TestType{
        TEST_JDBC_CONNECTION,
        TEST_JDBC_CONNECTION_POOL
    }

    private static void PrintHikariConfigMessage(boolean before, HikariConfig hikariConfig){
        if(before){
            System.out.println("HiKariCP default config start....");
        }else {
            System.out.println("HiKariCP setted config start....");
        }

        //显示一些配置参数：
        System.out.println("HiKariCP Max pool size = :" + hikariConfig.getMaximumPoolSize());
        System.out.println("HiKariCP Idle timeout = :" + hikariConfig.getIdleTimeout());
        System.out.println("HiKariCP Connection timeout = :" + hikariConfig.getConnectionTimeout());
        System.out.println("HiKariCP Transaction isolation = :" + hikariConfig.getTransactionIsolation());
        System.out.println("HiKariCP Catalog = :" + hikariConfig.getCatalog());
        System.out.println("HiKariCP Connection init Sql = :" + hikariConfig.getConnectionInitSql());
        System.out.println("HiKariCP Connection test query = :" + hikariConfig.getConnectionTestQuery());
        System.out.println("HiKariCP DataSource class name = :" + hikariConfig.getDataSourceClassName());
        System.out.println("HiKariCP DataSource JNDI = :" + hikariConfig.getDataSourceJNDI());
        System.out.println("HiKariCP Driver class name = :" + hikariConfig.getDriverClassName());
        System.out.println("HiKariCP DataSource properties = :" + hikariConfig.getDataSourceProperties());
        System.out.println("HiKariCP DataSource = :" + hikariConfig.getDataSource());
        System.out.println("HiKariCP JDBC URL = :" + hikariConfig.getJdbcUrl());
        System.out.println("HiKariCP Password = :" + hikariConfig.getPassword());
        System.out.println("HiKariCP Pool name = :" + hikariConfig.getPoolName());
        System.out.println("HiKariCP User name = :" + hikariConfig.getUsername());
        System.out.println("HiKariCP Health check properties = :" + hikariConfig.getHealthCheckProperties());
        System.out.println("HiKariCP Health check registry = :" + hikariConfig.getHealthCheckRegistry());
        System.out.println("HiKariCP Initialization fail timeout = :" + hikariConfig.getInitializationFailTimeout());
        System.out.println("HiKariCP Leak detection threshold = :" + hikariConfig.getLeakDetectionThreshold());
        System.out.println("HiKariCP Max life time = :" + hikariConfig.getMaxLifetime());
        System.out.println("HiKariCP Mini idle = :" + hikariConfig.getMinimumIdle());
        System.out.println("HiKariCP Validation timeout = :" + hikariConfig.getValidationTimeout());


        if(before){
            System.out.println("....HiKariCP default config end");
        }else {
            System.out.println("....HiKariCP setted config end");
        }

    }

    //private static final boolean testJdbc = false;

    private static final TestType testType = TestType.TEST_JDBC_CONNECTION_POOL;

    private static final String[] names = {"小青", "小白", "小朱", "小玄", "小苍", "小如"};

    //数据库连接URL，当前连接的是E:/temp/目录下的h2test数据库
    private static final String JDBC_URL = "jdbc:h2:file:E:\\temp\\h2test";
    //连接数据库时使用的用户名
    private static final String USER = "root";
    //连接数据库时使用的密码
    //private static final String PASSWORD = "root";
    private static final String PASSWORD = "root";
    //连接H2数据库时使用的驱动类，org.h2.Driver这个类是由H2数据库自己提供的，在H2数据库的jar包中可以找到
    private static final String DRIVER_CLASS = "org.h2.Driver";

    private static DataSource dataSource = null;

    public static void main(String[] args) {
        RunTest();
    }

    private static void RunTest(){

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            switch (testType){
                case TEST_JDBC_CONNECTION:{
                    connection = DriverManager.getConnection(JDBC_URL, USER,PASSWORD);
                }break;
                case TEST_JDBC_CONNECTION_POOL:{
                    HikariConfig hikariConfig = new HikariConfig();

                    PrintHikariConfigMessage(true, hikariConfig);

                    hikariConfig.setJdbcUrl(JDBC_URL);
                    hikariConfig.setUsername(USER);
                    hikariConfig.setPassword(PASSWORD);

                    DataSource dataSource = new HikariDataSource(hikariConfig);
                    connection = dataSource.getConnection();

                    PrintHikariConfigMessage(false, hikariConfig);

                }break;
            }

            (preparedStatement = connection.prepareStatement("DROP TABLE IF EXISTS USER_INFO")).execute();
            if (null != preparedStatement) {
                preparedStatement.close();
            }

            (preparedStatement = connection.prepareStatement("CREATE TABLE USER_INFO(id VARCHAR(36) PRIMARY KEY, name VARCHAR(100), sex VARCHAR(4))")).execute();
            if (null != preparedStatement) {
                preparedStatement.close();
            }


            preparedStatement = connection.prepareStatement("INSERT INTO USER_INFO VALUES(?,?,?)");

            for (String var : names) {
                UUID id = UUID.randomUUID();
                preparedStatement.setObject(1, id);
                preparedStatement.setString(2, names[(int) (Math.abs(id.getLeastSignificantBits()) % names.length)]);
                preparedStatement.setString(3, 0 == id.getLeastSignificantBits() % 2 ? "男" : "女");

                preparedStatement.addBatch();
                //preparedStatement.executeUpdate();
            }

            int[] ps = preparedStatement.executeBatch();
            for (int var : ps) {
                System.out.println(var + "  inserted");
            }

            //查询
            resultSet = connection.prepareStatement("SELECT * FROM USER_INFO").executeQuery();

            //显示
            while (resultSet.next()) {
                System.out.println(resultSet.getString("id") + "," + resultSet.getString("name") + "," + resultSet.getString("sex"));
            }


            //事务
            try {
                connection.setAutoCommit(false);

                preparedStatement = connection.prepareStatement("INSERT INTO USER_INFO VALUES(?,?,?)");
                preparedStatement.setObject(1, UUID.randomUUID());
                preparedStatement.setString(2, "aaa");
                preparedStatement.setString(3, "F");
                preparedStatement.execute();

                preparedStatement.clearParameters();
                preparedStatement.setObject(1, UUID.randomUUID());
                preparedStatement.setString(2, "bbb");
                preparedStatement.setString(3, "M");
                preparedStatement.execute();

                preparedStatement.clearParameters();
                preparedStatement.setObject(1, UUID.randomUUID());
                preparedStatement.setString(2, "ccc");
                preparedStatement.setString(3, "/");
                preparedStatement.execute();

                connection.commit();
            } catch (SQLException e) {
                connection.rollback();
                e.printStackTrace();
            } finally {
                connection.setAutoCommit(true);
            }


            //查询
            resultSet = connection.prepareStatement("SELECT * FROM USER_INFO").executeQuery();

            //显示
            while (resultSet.next()) {
                System.out.println(resultSet.getString("id") + "," + resultSet.getString("name") + "," + resultSet.getString("sex"));
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            try {
                if(null != connection){
                    connection.close();
                }
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
    }
}