技术库 > 数据库

H2数据库使用

技术库:tec.5lulu.com

from:tec.5lulu.com

H2 Database (http://www.h2database.com/html/main.html) 是一个开源的嵌入式数据库引擎,采用java语言编写,支持SQL,同时H2提供了一个十分方便的web控制台用于操作和管理数据库内容。

H2 官网介绍如下:

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

1 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

2 Database URL

Topic URL Format and Examples
Embedded (local) connection jdbc:h2:[file:][<path>]<databaseName>
jdbc:h2:~/test
jdbc:h2:file:/data/sample
jdbc:h2:file:C:/data/sample (Windows only)
In-memory (private) jdbc:h2:mem:
In-memory (named) jdbc:h2:mem:<databaseName>
jdbc:h2:mem:test_mem
Server mode (remote connections) using TCP/IP jdbc:h2:tcp://<server>[:<port>]/[<path>]<databaseName>
jdbc:h2:tcp://localhost//test<br>jdbc:h2:tcp://dbserv:8084//sample
jdbc:h2:tcp://localhost/mem:test
Using encrypted files jdbc:h2:<url>;CIPHER=AES
jdbc:h2:ssl://localhost//test;CIPHER=AES<br>jdbc:h2:file:/secure;CIPHER=AES

完整内容:Database URL Overview

3 实战

maven依赖

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


创建本地文件数据库

代码如下

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();
            }
        }  


创建本地内存数据库

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();
            }
        } 

创建表

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数据库使用


标签: jdbc本文链接 http://tec.5lulu.com/detail/104d9n2aany8w85fa.html

我来评分 :7.5
1

转载注明:转自5lulu技术库

本站遵循:署名-非商业性使用-禁止演绎 3.0 共享协议

www.5lulu.com

相关文章
1H2数据库使用