JAVA内嵌数据库H2的使用入门

H2 数据库是开源的，非常适合做嵌入式数据库使用，尤其用java编码的时候。

H2的优势：
　　　　1、h2采用纯Java编写，因此不受平台的限制。
　　　　2、h2只有一个jar文件，十分适合作为嵌入式数据库试用。
　　　　3、h2提供了一个十分方便的web控制台用于操作和管理数据库内容。

一、所需工具：

　　JDK

　　h2-1.4.x.jar

二、写代码如下：

package com.my.enter;

import java.sql.Connection;

import java.sql.SQLException;

import org.h2.jdbcx.JdbcConnectionPool;

public class ConnectionPool {

    private static ConnectionPool cp = null;

    private JdbcConnectionPool jdbcCP = null;

    private ConnectionPool() {

        String dbPath ="./config/test";

        jdbcCP = JdbcConnectionPool.create("jdbc:h2:" + dbPath, "sa", "");

        jdbcCP.setMaxConnections(50);

    }

    public static ConnectionPool getInstance() {

        if (cp == null) {

            cp = new ConnectionPool();

        }

        return cp;

    }

    public Connection getConnection() throws SQLException {

        return jdbcCP.getConnection();

    }

}

实例化时若数据库test.mv.db不存在，则会创建，路径是src的同级目录config/test.mv.db;

三、使用数据库：

package com.my.enter;

import java.sql.Connection;

import java.sql.DatabaseMetaData;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class CommonDao {

    public static void crateTable() throws SQLException {

        Connection conn = null;

        Statement stmt = null;

        try {

            conn = ConnectionPool.getInstance().getConnection();

            DatabaseMetaData meta = conn.getMetaData();

            ResultSet rsTables = meta.getTables(null, null, "WEATHERINFO",

                    new String[] { "TABLE" });

            if (!rsTables.next()) {

                stmt = conn.createStatement();

                stmt.execute("CREATE TABLE WEATHERINFO(WEATHERSTR VARCHAR(1024),LASTMODIFYTIME VARCHAR(1024),STATUS VARCHAR(1024),PRIMARY KEY(WEATHERSTR,LASTMODIFYTIME))");

            }

            rsTables.close();

        } finally {

            releaseConnection(conn, stmt, null);

        }

    }

    public static void addInfo(String str, long lastModifyTime,

            String status) throws SQLException {

        Connection conn = null;

        PreparedStatement stmt = null;

        try {

            conn = ConnectionPool.getInstance().getConnection();

            stmt = conn

                    .prepareStatement("INSERT INTO WEATHERINFO VALUES(?,?,?)");

            stmt.setString(1, str);

            stmt.setString(2, String.valueOf(lastModifyTime));

            stmt.setString(3, status);

            stmt.execute();

        } finally {

            releaseConnection(conn, stmt, null);

        }

    }

    public static boolean isInfoExits(String filePath, long lastModifyTime)

            throws SQLException {

        Connection conn = null;

        PreparedStatement stmt = null;

        ResultSet rs = null;

        try {

            conn = ConnectionPool.getInstance().getConnection();

            stmt = conn

                    .prepareStatement("SELECT WEATHERSTR FROM WEATHERINFO WHERE STATUS=? AND LASTMODIFYTIME=?");

            stmt.setString(1, filePath);

            stmt.setString(2, String.valueOf(lastModifyTime));

            rs = stmt.executeQuery();

            return rs.next();

        } finally {

            releaseConnection(conn, stmt, rs);

        }

    }

    private static void releaseConnection(Connection conn, Statement stmt,

            ResultSet rs) throws SQLException {

        if (rs != null) {

            rs.close();

        }

        if (stmt != null) {

            stmt.close();

        }

        if (conn != null) {

            conn.close();

        }

    }

}