H2数据库使用记录

在学习ODL的3A认证时，定位故障发现用户与密码使用了H2数据库，则简单学习并记录：

Pom依赖

   <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.3.172</version>
            <scope>test</scope>
        </dependency>

其三种运行方式，都有使用场景，分别是内存、嵌入（文件）、服务器模式

@Test
    public void testMem() throws SQLException, InterruptedException {
        Connection connection = DriverManager
                .getConnection("jdbc:h2:mem:DBName;DB_CLOSE_DELAY=-1", "foo", "bar");
        boolean flag = connection.isValid(5000);
        int count = 1;
        if (flag) {
            //连接成功
            Statement tableCreate = connection.createStatement();
            tableCreate.execute("create table sq(" +
                    "uname VARCHAR(100) NOT NULL," +
                    "upasswd VARCHAR(200) )");
            tableCreate.close();
            Statement userInsert = connection.createStatement();
            userInsert.execute("insert into sq values('sunquan','123456')");
            userInsert.close();

            Statement userQuery = connection.createStatement();
            ResultSet resultSet = userQuery.executeQuery("select * from sq");
            while (resultSet.next()) {
                System.out.println("count:" + count++);
                Assert.assertEquals("sunquan", resultSet.getString(1));//从1开始
                Assert.assertEquals("sunquan", resultSet.getString("uname"));
                Assert.assertEquals("123456", resultSet.getString("upasswd"));
            }
            resultSet.close();
            userQuery.close();
        }
    }

    @Test
    public void testEmbed() throws SQLException, InterruptedException {
        //目录存在于C:\Users\Administrator\.h2  默认是嵌入式模式=jdbc:h2:file:~/.h2/DBName;
        Connection connection = DriverManager
                .getConnection("jdbc:h2:~/.h2/DBName;DB_CLOSE_DELAY=-1", "foo", "bar");
        boolean flag = connection.isValid(5000);
        int count = 1;
        if (flag) {
            //连接成功
            Statement tableCreate = connection.createStatement();
            tableCreate.execute("create table IF NOT EXISTS sq (" +
                    "uname VARCHAR(100) NOT NULL," +
                    "upasswd VARCHAR(200) )");
            tableCreate.close();
            Statement userInsert = connection.createStatement();
            userInsert.execute("insert into sq values('sunquan','123456')");
            userInsert.close();

            Statement userQuery = connection.createStatement();
            ResultSet resultSet = userQuery.executeQuery("select * from sq");
            while (resultSet.next()) {
                System.out.println("count:" + count++);
                Assert.assertEquals("sunquan", resultSet.getString(1));//从1开始
                Assert.assertEquals("sunquan", resultSet.getString("uname"));
                Assert.assertEquals("123456", resultSet.getString("upasswd"));
            }
            resultSet.close();
            userQuery.close();
        }
        //每次执行都会进行数据插入，数据会以文件方式保留
    }

服务模式，首先要启动服务端，可以手动通过命令行启动，或maven,或代码执行，并且有诸多选项：

示例中，使用了tcp协议

    @Test
    public void testServer1() throws SQLException, InterruptedException {
        //需要先启用h2的服务 org.h2.tools.Server
        Connection connection = DriverManager
                .getConnection("jdbc:h2:tcp://localhost:9092/E:/idmlight.db", "foo", "bar");
        boolean flag = connection.isValid(5);
        int count = 1;
        if (flag) {
            //连接成功
            Statement tableCreate = connection.createStatement();
            tableCreate.execute("create table IF NOT EXISTS sq (" +
                    "uname VARCHAR(100) NOT NULL," +
                    "upasswd VARCHAR(200) )");
            tableCreate.close();
            Statement userInsert = connection.createStatement();
            userInsert.execute("insert into sq values('sunquan','123456')");
            userInsert.close();

            Statement userQuery = connection.createStatement();
            ResultSet resultSet = userQuery.executeQuery("select * from sq");
            while (resultSet.next()) {
                System.out.println("count:" + count++);
                Assert.assertEquals("sunquan", resultSet.getString(1));//从1开始
                Assert.assertEquals("sunquan", resultSet.getString("uname"));
                Assert.assertEquals("123456", resultSet.getString("upasswd"));
            }
            resultSet.close();
            userQuery.close();
        }
    }

此外，还有其它选项，一个比较有用的是- web

h2-1.4.185.jar org.h2.tools.Server -trace -web

可以在控制台进行数据的操作，默认端口8082

http://localhost:8082/login.do

进入后，直接操作数据库

其它选项与URL参考http://blog.csdn.net/mafan121/article/details/50666952