H2数据库使用
H2数据库说明
H2是一个文本数据库,你可以引用到自己的项目中,或在电脑上直接使用。好处在于不需要像Oracle或Mysql那样要安装软件,而且是轻量的。H2在使用时,只要引入h2*.jar文件,并指明JDBC驱动:org.h2.Driver,还有就是数据库的路径即可。H2在项目中使用时,你可以看作H2有一个服务平台,将你的项目web端和H2数据库连接起来。
电脑上使用H2
首先,在电脑上使用H2时,需要到官网上下载安装包http://www.h2database.com/html/main.html。下载All Platforms (zip, 8 MB)的文件。解压之后,你就可以看到如下的文件:
其次,如果是windows系统,点击bin目录下的h2.bat或h2w.bat都可以, 如果是linux系统,者执行 sh h2.sh.笔者是在自己的windows系统上运行,所以双击h2w.bat后,在电脑的右下角可以看到这个图标。,同时会弹出浏览器的登录窗口。访问的地址是:http://192.168.111.1:8082/login.jsp?jsessionid=d3de299ee67f8926edc84ffda951e248
注意,在测试的情况下,JDBC URL的路径最后要写上test.上图是笔者自己指定了数据库文件所放置的位置,所以写上了具体的路径,其实读者也可以直接写上jdbc:h2: test。test的密码都是sa。点击Connect,你将会看到如下的页面。
好了在上面的页面中,你就可以写上自己的SQL语句了。但是H2有好多在Oracle或Mysq中常用的函数是没有的,这样在开发中可能会带来麻烦。
项目中使用H2
以上只是笔者简单的对H2的使用,也只能说是带读者入门吧。如果想要了解更多H2的使用,可到官网上找资料,或下载使用文档http://www.h2database.com/h2.pdf 。
- 项目中使用H2,需要引入h2*.jar。这就好比在Oracle和Mysql中引入相应的jar文件是一样的。但是H2不需要在电脑上安装任何的软件,除非你是要在浏览器中使用,那就需要上面介绍的那样了。
- 在项目中创建一个类,名为H2ConnectionPool,这个是连接池的方法。在连接池中你需要指名H2数据库文本所要放置的路径,一般在项目中都是直接写上:"jdbc:h2:~/test”,这是说明放置到项目的同等路径下。笔者为了项目中好查看数据库的数据,所以指明子路径是:E:/workspace/SelfMonitor/SelfMonitor/data/test。还有需要注意的是,在同一电脑上,只能有一个H2是开着,也就是说如果你用h2w.bat启用了H2,而在项目中还想使用h2,这是不请允许的,项目启动或浏览器连接都会报在使用中。另外,在项目中可能会有多个线程在操作数据库,有时候会引起线程使用上的冲突,即一个线程在连接数据库后关闭,导致另一个线程在使用时出现异常。在这种的情况下,你需要加上MVCC=TRUE的设置。代码如下。
package com.owen.h2.database2; import java.sql.Connection; import java.sql.SQLException; import org.h2.jdbcx.JdbcConnectionPool; /** *H2数据库操作 * * @author owen * @DATE 20190228 * */ public class H2ConnectionPool { private static H2ConnectionPool cp = null; private JdbcConnectionPool jdbcCP = null; private H2ConnectionPool() { String dbPath = "E:/workspace/SelfMonitor/SelfMonitor/data/test"; jdbcCP = JdbcConnectionPool.create("jdbc:h2:" + dbPath+";MVCC=TRUE", "sa", ""); jdbcCP.setMaxConnections(50); } public static H2ConnectionPool getInstance() { if (cp == null) { cp = new H2ConnectionPool(); } return cp; } public Connection getConnection() throws SQLException { return jdbcCP.getConnection(); } }
- 有了以上的这个连接池,接下就可以对H2数据库进行操作。以下是提供几个常用的增删改查的方法封装。
package com.dc.bd.dao; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Set; import java.util.UUID; import org.apache.commons.lang3.StringUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.dc.bd.entity.Event; import com.dc.bd.entity.KpiInfo; /** * H2数据库操作 * * @author owen * @DATE 20190228 * */ public class H2CommonDao { private static Logger logger = LoggerFactory.getLogger(H2CommonDao.class); /** * 建表方法 * * @param tableName * @param items * @return * @throws SQLException */ public static Boolean crateTable(String tableName, String[] items) throws SQLException { Connection conn = null; Statement stmt = null; try { conn = H2ConnectionPool.getInstance().getConnection(); DatabaseMetaData meta = conn.getMetaData(); ResultSet rsTables = meta.getTables(null, null, tableName, new String[] { "TABLE" }); if (!rsTables.next()) { stmt = conn.createStatement(); StringBuilder sql = new StringBuilder(); sql.append(" CREATE TABLE IF NOT EXISTS "); if (StringUtils.isNotEmpty(tableName)) { sql.append(tableName); } if (items != null && items.length > 0) { sql.append(" ( "); sql.append(" hid VARCHAR(1024), "); for (int i = 0; i < items.length; i++) { sql.append(items[i]); sql.append(" VARCHAR(5000), "); } sql.append("PRIMARY KEY(hid)) "); } stmt.execute(sql.toString()); } rsTables.close(); return true; } finally { releaseConnection(conn, stmt, null); } } /** * 建表方法 * * @param tableName * @param items * @return * @throws SQLException */ public static Boolean crateTables(Map<String,String[]> tables) throws SQLException { Connection conn = null; Statement stmt = null; try { conn = H2ConnectionPool.getInstance().getConnection(); DatabaseMetaData meta = conn.getMetaData(); for(Map.Entry<String,String[]> table : tables.entrySet()) { String tableName = table.getKey(); logger.info("******创建数据库表:"+tableName); String[] items= table.getValue(); ResultSet rsTables = meta.getTables(null, null, tableName, new String[] { "TABLE" }); if (!rsTables.next()) { stmt = conn.createStatement(); StringBuilder sql = new StringBuilder(); sql.append(" CREATE TABLE IF NOT EXISTS "); if (StringUtils.isNotEmpty(tableName)) { sql.append(tableName); } if (items != null && items.length > 0) { sql.append(" ( "); sql.append(" hid VARCHAR(1024), "); for (int i = 0; i < items.length; i++) { sql.append(items[i]); sql.append(" VARCHAR(5000), "); } sql.append("PRIMARY KEY(hid)) "); } stmt.execute(sql.toString()); } rsTables.close(); } return true; } finally { releaseConnection(conn, stmt, null); } } /** * h2数据库插入数据 * * @param tableName * @param items * @param values * @return * @throws SQLException */ public static Boolean insertH2(String tableName, String[] items, String[] values) throws SQLException { Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { conn = H2ConnectionPool.getInstance().getConnection(); StringBuilder sql = new StringBuilder(); sql.append(" INSERT INTO "); if (StringUtils.isNotEmpty(tableName)) { sql.append(tableName); } if (items != null && items.length > 0) { sql.append(" ( "); sql.append(" hid, "); String strItems = StringUtils.join(items, ","); sql.append(strItems); sql.append(" ) "); sql.append(" VALUES( ?,"); for (int i = 0; i < items.length; i++) { sql.append("? "); if (i < items.length - 1) { sql.append(", "); } } sql.append(") "); } stmt = conn.prepareStatement(sql.toString()); // values stmt.setString(1, getUuid()); if (values != null && values.length > 0) { for (int i = 0; i < values.length; i++) { stmt.setString(i + 2, values[i]); } } return stmt.execute(); } finally { conn.commit(); releaseConnection(conn, stmt, rs); } } /** * h2数据库插入数据 * * @param tableName * @param items * @param values * @return * @throws SQLException */ public static Boolean insertH2(String tableName, String[] items, Map<String, String[]> values) throws SQLException { Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; Boolean result = false; try { conn = H2ConnectionPool.getInstance().getConnection(); for (Map.Entry<String, String[]> value : values.entrySet()) { // String kpiCode = value.getKey(); String[] kpiValue = value.getValue(); StringBuilder sql = new StringBuilder(); sql.append(" INSERT INTO "); if (StringUtils.isNotEmpty(tableName)) { sql.append(tableName); } if (items != null && items.length > 0) { sql.append(" ( "); sql.append(" hid, "); String strItems = StringUtils.join(items, ","); sql.append(strItems); sql.append(" ) "); sql.append(" VALUES( ?,"); for (int i = 0; i < items.length; i++) { sql.append("? "); if (i < items.length - 1) { sql.append(", "); } } sql.append(") "); } stmt = conn.prepareStatement(sql.toString()); // values stmt.setString(1, getUuid()); if (kpiValue != null && kpiValue.length > 0) { for (int i = 0; i < kpiValue.length; i++) { stmt.setString(i + 2, kpiValue[i]); } } result = stmt.execute(); } return result; } finally { conn.commit(); releaseConnection(conn, stmt, rs); } } /** * h2数据库更新数据 * * @param tableName * @param items * @param values * @return * @throws SQLException */ public static Boolean updateH2(String tableName, String[] items, Map<String, String[]> values,Map<String,String> params) throws SQLException { Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; Boolean result = false; try { conn = H2ConnectionPool.getInstance().getConnection(); for (Map.Entry<String, String[]> value : values.entrySet()) { String[] kpiValue = value.getValue(); StringBuilder sql = new StringBuilder(); StringBuilder dealSql = new StringBuilder(); sql.append(" UPDATE "); if (StringUtils.isNotEmpty(tableName)) { sql.append(tableName); } if (items != null && items.length > 0) { sql.append(" SET "); for (int i = 0; i < items.length; i++) { sql.append(items[i] + "=?, "); } String strSql = sql.toString().trim(); dealSql = new StringBuilder(strSql.substring(0, strSql.length()-1)); dealSql.append(" WHERE "); for(Map.Entry<String, String> param : params.entrySet()) { dealSql.append(param.getKey() + "='" + param.getValue()+"'"); } } logger.info(dealSql.toString()); stmt = conn.prepareStatement(dealSql.toString()); // values if (kpiValue != null && kpiValue.length > 0) { for (int i = 0; i < kpiValue.length; i++) { stmt.setString(i + 1, kpiValue[i]); } } result = stmt.execute(); } return result; } finally { conn.commit(); releaseConnection(conn, stmt, rs); } } /** * 查询方法 * * @param tableName * @param items * @param params * @return * @throws SQLException */ public static List<Map<String, String>> selectH2(String tableName, String[] items, Map<String, String> params) throws SQLException { Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; List<Map<String, String>> result = new ArrayList<Map<String, String>>(); try { conn = H2ConnectionPool.getInstance().getConnection(); StringBuilder sql = new StringBuilder(); sql.append(" Select "); if (items != null && items.length > 0) { String strItems = StringUtils.join(items, ","); sql.append(strItems); } sql.append(" FROM "); if (StringUtils.isNotEmpty(tableName)) { sql.append(tableName); } // 存在查询条件 if (params != null && params.size() > 0) { sql.append(" WHERE "); Set<String> kSet = params.keySet(); for (String key : kSet) { sql.append(key); sql.append(" = ? and "); } sql.append("1 = 1"); } stmt = conn.prepareStatement(sql.toString()); // 存在查询条件 if (params != null && params.size() > 0) { Set<String> kSet = params.keySet(); Integer index = 1; for (String key : kSet) { stmt.setString(index, params.get(key)); index++; } } rs = stmt.executeQuery(); while (rs.next()) { Map<String, String> resultMap = new HashMap<String, String>(); for (int i = 0; i < items.length; i++) { resultMap.put(items[i], rs.getString(items[i])); } result.add(resultMap); } return result; } finally { releaseConnection(conn, stmt, rs); } } /** * 自定义sql * * @param sql * @param items * @param params * @return * @throws SQLException */ public static List<Map<String, String>> selectH2BySql(String sql, List<String> items, List<String> params) throws SQLException { Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; List<Map<String, String>> result = new ArrayList<Map<String, String>>(); try { conn = H2ConnectionPool.getInstance().getConnection(); stmt = conn.prepareStatement(sql); // 存在查询条件 if (params != null && params.size() > 0) { for (int i = 0; i < params.size(); i++) { stmt.setString(i + 1, params.get(i)); } } rs = stmt.executeQuery(); while (rs.next()) { Map<String, String> resultMap = new HashMap<String, String>(); for (int i = 0; i < items.size(); i++) { resultMap.put(items.get(i), rs.getString(items.get(i))); } result.add(resultMap); } return result; } finally { releaseConnection(conn, stmt, rs); } } /** * 自定义sql * * @param sql * @param items * @param params * @return * @throws SQLException */ public static boolean h2BySql(String sql) throws SQLException { Connection conn = null; PreparedStatement stmt = null; try { conn = H2ConnectionPool.getInstance().getConnection(); stmt = conn.prepareStatement(sql); return stmt.execute(); } finally { releaseConnection(conn, stmt, null); } } /** * 自定义sql * * @param sql * @param items * @param params * @return * @throws SQLException */ public static boolean h2BySqls(List<String> sqls) throws SQLException { Connection conn = null; PreparedStatement stmt = null; try { conn = H2ConnectionPool.getInstance().getConnection(); boolean result = false; for(String sql : sqls) { stmt = conn.prepareStatement(sql); result = stmt.execute(); } return result; } finally { releaseConnection(conn, stmt, null); } } 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(); } } /** * 删除表 * * @param tableName * @param items * @return * @throws SQLException */ public static Boolean dropTable(String tableName) throws SQLException { Connection conn = null; Statement stmt = null; try { conn = H2ConnectionPool.getInstance().getConnection(); DatabaseMetaData meta = conn.getMetaData(); ResultSet rsTables = meta.getTables(null, null, tableName, new String[] { "TABLE" }); if (!rsTables.next()) { stmt = conn.createStatement(); StringBuilder sql = new StringBuilder(); sql.append(" DROP TABLE "); if (StringUtils.isNotEmpty(tableName)) { sql.append(tableName); } sql.append(" IF EXISTS"); stmt.execute(sql.toString()); } rsTables.close(); return true; } finally { releaseConnection(conn, stmt, null); } } public static String getUuid() { return UUID.randomUUID().toString().replace("-", ""); } }
总结
以上只是笔者简单的对H2的使用,也只能说是带读者入门吧。如果想要了解更多H2的使用,可到官网上找资料,或下载使用文档http://www.h2database.com/h2.pdf 。