JAVA 构建动态表格，数据统计处理利用内存数据库H2研究

这几天，临时帮忙其他项目组做高级查询，后台处理好数据，动态生成报表一样的表格。

做成表格内容

[img]http://dl.iteye.com/upload/attachment/0070/7746/70318afd-19dc-322a-b26f-e1defac4fd90.jpg[/img]

------------------------------------------------------
考虑到，要把数据后期处理，若用Map,List，保存，不断的遍历，搜索，算法复杂
性能也不能保证，
考虑到内存数据库的优越性，只要把原始数据插入到H2中，后期的数据筛选，统计，只要查询SQL就可，而且H2支持JDBC操作，方便。
附件中有完整样例。

使用内存数据库H2工具类，

package com;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class CacheDB {

	private static final CacheDB instance = new CacheDB();
	private Connection conn = null;

	private CacheDB() {

	}

	@Override
	protected void finalize() throws Throwable {

		super.finalize();
		closeDB(conn);
	}

	public static CacheDB getInstance() {
		return instance;
	}

	public void openDB() {
		if(isOpenDB()){
			return;
		}
		try {
			Class.forName("org.h2.Driver");
			conn = DriverManager.getConnection("jdbc:h2:~/test", "sa", "");
			System.out.println("---------------------H2 数据库打开成功------------------");
		} catch (ClassNotFoundException e) {
			System.out
					.println("-------------------H2 数据库驱动找不到----------------");
		} catch (SQLException e) {
			System.out.println("-----------H2 数据库打开出错误------------");
			e.printStackTrace();
		}

	}

	public boolean createTable(String sql) {
		Statement st = null;
		try {
			st = getStatement();
			return st.execute(sql);
		} catch (SQLException e) {
			System.out.println("--------H2 数据库建表出错误---------");
			System.out.println(sql);
			e.printStackTrace();
		} finally {
			closeStatement(st);
		}
		return false;
	}
    /**
     * @deprecated
     * */
	public Object queryForObject(String sql){

		return null;
	} 


	public List queryForList(String sql,RowMapperH2  rowMapper){
		Statement st = null;
		ResultSet  rs=null;
		List result=new ArrayList();
			try {
				st = getStatement();
				rs=st.executeQuery(sql);
				int lineNumber=0;
				while(rs.next()){
					if(rowMapper!=null){

						result.add(rowMapper.mapRow(rs, lineNumber++));
					}else{

					}
				}


			} catch (SQLException e) {
				System.out.println("--------H2 数据库查询出错误---------");
				System.out.println(sql);
				e.printStackTrace();
			}finally{
				closeStatement(st);
				closeResultSet( rs);
			}
		return result;
	}
	public Double queryForDouble(String sql){

		Statement st = null;
		ResultSet  rs=null;

			try {
				st = getStatement();
				rs=st.executeQuery(sql);

				Double result=0.0;

				while(rs.next()){
					result=rs.getDouble(1);
				}
				//System.out.println("queryForDouble sql="+sql+"\nresult="+result);
				return result;

			} catch (SQLException e) {
				System.out.println("--------H2 数据库查询出错误---------");
				System.out.println(sql);
				e.printStackTrace();
			}finally{
				closeStatement(st);
				closeResultSet( rs);
			}
		return null;
	}



	public boolean delFromTable(String table, String whereSql) {
		if (table != null && table.length() != 0) {
			Statement st = null;
			StringBuffer bf = new StringBuffer("DELETE FROM ");
			try {
				st = getStatement();

				bf.append(table);
				if (whereSql != null)
					bf.append(whereSql);
				return st.execute(bf.toString());
			} catch (SQLException e) {
				System.out.println("--------H2 数据库删除数据出错误---------");
				System.out.println(bf.toString());
				e.printStackTrace();
			} finally {
				closeStatement(st);
			}
		}
		return false;

	}

	public boolean clearTable(String table) {

		return this.delFromTable(table, null);

	}

	public boolean insertTable(String table, String[] column, String[] values) {
		StringBuffer bf = new StringBuffer("INSERT INTO ");
		bf.append(table).append("(").append(change2str(column))
				.append(") value ( ").append(change2str(values)).append(" )");

		return insertTable(bf.toString());

	}
	public boolean insertTable(String sql) {
		if (conn != null) {
			Statement st = null;
			try {
				st = conn.createStatement();
				return st.execute(sql);

			} catch (SQLException e) {
				System.out.println("--------H2 数据库插入数据出错误---------");
				System.out.println(sql);
				e.printStackTrace();
			} finally {
				closeStatement(st);
			}
		}

		return false;
	}

	private Statement getStatement() throws SQLException {
		if (conn != null)
			return conn.createStatement();
		return null;
	}


	public void closeDB(Connection _conn) {
		if (_conn != null)
			try {
				_conn.close();
			} catch (SQLException e) {
				System.out.println("--------H2 数据库Connection关闭出错误---------");
				e.printStackTrace();
			}
	}

	public void closeDB() {
		if (conn != null)
			try {
				conn.close();
			} catch (SQLException e) {
				System.out.println("--------H2 数据库Connection关闭出错误---------");
				e.printStackTrace();
			}
	}

	private void closeStatement(Statement st) {
		if (st != null)
			try {
				st.close();
			} catch (SQLException e) {
				System.out.println("--------H2 数据库Statement关闭出错误---------");
				e.printStackTrace();
			}
	}
	private void closeResultSet(ResultSet rs) {
		if (rs != null)
			try {
				rs.close();
			} catch (SQLException e) {
				System.out.println("--------H2 数据结果集ResultSet关闭出错误---------");
				e.printStackTrace();
			}
	}
	private String change2str(String[] obj) {
		return obj.toString().replace("[", "").replace("]", "");
	}
	private boolean isOpenDB(){
		return conn!=null?true:false; 
	}
}

数据格式

[img]http://dl.iteye.com/upload/attachment/0070/7744/cdf24827-e88f-33c9-8252-d4f9aa8eb87d.jpg[/img]