H2 Database (http://www.h2database.com/html/main.html) 是一個(gè)開源的嵌入式數(shù)據(jù)庫引擎登夫，采用java語言編寫璃诀，支持SQL月培，同時(shí)H2提供了一個(gè)十分方便的web控制臺(tái)用于操作和管理數(shù)據(jù)庫內(nèi)容。

H2 官網(wǎng)介紹如下：

H2, the Java SQL database. The main features of H2 are:

• Very fast, open source, JDBC API
• Embedded and server modes; in-memory databases
• Browser based Console application
• Small footprint: around 1.5 MB jar file size

Connection Modes

The following connection modes are supported:

• Embedded mode (local connections using JDBC)
• Server mode (remote connections using JDBC or ODBC over TCP/IP)
• Mixed mode (local and remote connections at the same time)

Embedded Mode

This database can be used in embedded mode, or in server mode. To use it in embedded mode, you need to:

• Add the h2*.jar to the classpath (H2 does not have any dependencies)
• Use the JDBC driver class: org.h2.Driver
• The database URL jdbc:h2:~/test opens the database test in your user home directory
• A new database is automatically created

Server Mode

H2 currently supports three server: a web server (for the H2 Console), a TCP server (for client/server connections) and an PG server (for PostgreSQL clients). Please note that only the web server supports browser connections. The servers can be started in different ways, one is using the Server tool. Starting the server doesn't open a database - databases are opened as soon as a client connects.

Starting the Server Tool from Command Line

To start the Server tool from the command line with the default settings, run:

java -cp h2*.jar org.h2.tools.Server

Connecting to the TCP Server

o remotely connect to a database using the TCP server, use the following driver and database URL:

• JDBC driver class: org.h2.Driver
• Database URL: jdbc:h2:tcp://localhost/~/test

Database URL

完整內(nèi)容：Database URL Overview

實(shí)戰(zhàn)

maven依賴：

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.193</version>
</dependency>

創(chuàng)建本地文件數(shù)據(jù)庫

代碼如下：

       Connection conn = null;
        try{
            Class.forName("org.h2.Driver");
            conn = DriverManager. getConnection("jdbc:h2:~/test", "sa", "sa");
            
            //do your business
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

創(chuàng)建本地內(nèi)存數(shù)據(jù)庫

        Connection conn = null;
        try{
            Class.forName("org.h2.Driver");
            conn = DriverManager. getConnection("jdbc:h2:mem:test_mem", "sa", "sa");

            // do your business

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

創(chuàng)建表

SQL：

CREATE TABLE t_user (
    id BIGINT NOT NULL, 
    name VARCHAR(20) NOT NULL,
    password VARCHAR(32) NOT NULL, 
    age SMALLINT, 
    birthday TIMESTAMP,
    PRIMARY KEY (id)
);

代碼如下：

package com.bytebeats.codelab.h2;

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

/**
 * ${DESCRIPTION}
 *
 * @author Ricky Fung
 * @create 2017-03-05 14:43
 */
public class CreateTable {

    public static void main(String[] args) {

        Connection conn = null;
        Statement stmt = null;
        try{
            Class.forName("org.h2.Driver");
            conn = DriverManager. getConnection("jdbc:h2:~/test", "sa", "sa");

            stmt = conn.createStatement();

            int result = stmt.executeUpdate("CREATE TABLE t_user (" +
                    "id BIGINT NOT NULL, " +
                    "name VARCHAR(20) NOT NULL," +
                    "password VARCHAR(32) NOT NULL, " +
                    "age SMALLINT, " +
                    "birthday TIMESTAMP," +
                    "PRIMARY KEY (id)" +
                    ");");

            System.out.println("result:"+result);
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

插入記錄

INSERT INTO t_user VALUES (1,'ricky', '12345', 28, '1989-09-15 15:00:00');

代碼如下：

package com.bytebeats.codelab.h2;

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

/**
 * ${DESCRIPTION}
 *
 * @author Ricky Fung
 * @create 2017-03-05 14:57
 */
public class InsertQuery {

    public static void main(String[] args) {

        Connection conn = null;
        Statement stmt = null;
        try{
            Class.forName("org.h2.Driver");
            conn = DriverManager. getConnection("jdbc:h2:~/test", "sa", "sa");

            stmt = conn.createStatement();
            int update = stmt.executeUpdate("INSERT INTO t_user VALUES (1,'ricky', '12345', 28, '1989-09-15 15:00:00');");
            System.out.println(update);

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

查詢記錄

SQL：

SELECT id, name, password, age, birthday FROM t_user where id=1

代碼如下：

package com.bytebeats.codelab.h2;

import java.sql.*;

/**
 * ${DESCRIPTION}
 *
 * @author Ricky Fung
 * @create 2017-03-05 14:57
 */
public class SelectQuery {

    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet result = null;
        try{
            Class.forName("org.h2.Driver");
            conn = DriverManager. getConnection("jdbc:h2:~/test", "sa", "sa");

            stmt = conn.prepareStatement("SELECT id, name, password, age, birthday FROM t_user WHERE id=?");
            stmt.setLong(1, 1);

            result = stmt.executeQuery();
            while(result.next()){
                System.out.println(result.getInt("id")+" | "+
                        result.getString("name")+" | "+
                        result.getString("password")+" | "+
                        result.getShort("age")+" | "+
                        result.getTimestamp("birthday"));
            }

        } catch (Exception e){
            e.printStackTrace();
        } finally {
            try {
                result.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

參考資料

更多用法參考：H2 Database features