使用JSP实现简易的SQL报表

网络整理 - 07-27
这个内容也是根据企业学员的要求准备的. 其实这个小项目是刚毕业时候做的, 很多时候我们希望执行下面的SQL/HQL然后得到一个HTML表格输出:

  输入: select ID as 编号, NAME as 姓名, AGE as 年龄 from XXX

  输出:

  编号姓名年龄

  要求是如果SQL变动, 仍然要显示出来所有的别名字段信息和数据.

  因为现在Hibernate用的比较广泛, 所以优先考虑用Hibernate来实现, 结果发现如果是实体映射查询语句, 可以方便的用:List Query.getReturnAliases() 获得别名, 然而我们知道查询时有时候语句是很复杂的, 不全是HQL, 这时候用SQLQuery的时候, 惊讶的提示这个方法尚未实现(最新版的Hibernate 3.3 是否实现尚未测试), 用的版本是Hibernate 3.2, 对应的代码是:


 DAO
  /**
  * 根据查询语句返回结果, 并包含结果的列名
  * @param hql
  * @param args
  * @return
  */
  public List queryAllForReport( final String hql, final Object... args) {
  List list = getHibernateTemplate().executeFind(new HibernateCallback() {
  public Object doInHibernate(Session session)
  throws HibernateException, SQLException {
  Query query = session.createQuery(hql);
  for(int i =0; i < args.length; i++) {
  query.setParameter(i, args[i]);
  }
  List list = query.list();
  list.add(0, query.getReturnAliases());
  return list;
  }
  });
  // Hibernate做count计算返回一般都是对象
  return list;
  }

  测试代码:


List list = dao.queryAllForReport("select id as 编号,
  name as 登录名, address as 地址, realName from User");
  System.out.println(list.size());
  for(Object[] row : list) {
  for(Object v : row) {
  System.out.print(v + "t");
  }
  System.out.println();
  }

  最后不得不回到JDBC, 用 ResultSet和ResultSetMetaData实现了这个功能, 详细代码(自己进行修改即可实现)如下:


 ﹤%@ page language="Java" import="java.util.*, java.sql.*" pageEncoding="UTF-8"%﹥
  ﹤%@ taglib prefix="c" uri="com/JSP/jstl/core" %﹥
  ﹤!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"﹥
  ﹤html﹥
  ﹤head﹥
  ﹤title﹥SQL报表﹤/title﹥
  ﹤meta http-equiv="pragma" content="no-cache"﹥
  ﹤meta http-equiv="cache-control" content="no-cache"﹥
  ﹤meta http-equiv="expires" content="0"﹥
  ﹤/head﹥
  ﹤body﹥
  ﹤form action=""﹥
  ﹤textarea name=sql cols=80 rows=10﹥${param.sql}﹤/textarea﹥﹤br﹥
  ﹤input type=submit value=查询﹥
  ﹤/form﹥
  ﹤c:if test="${!empty param.sql}"﹥
  ﹤%
  //new Oracle.jdbc.driver.OracleDriver();
  Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.41:1521:xe", "hr", "hr");
  Statement stmt = conn.createStatement();
  ResultSet rs = stmt.executeQuery(request.getParameter("sql"));
  if(rs == null) {
  stmt.close();
  conn.close();
  return;
  }
  // 取列名
  ResultSetMetaData meta = rs.getMetaData();
  int cols = meta.getColumnCount();
  ArrayList colNames = new ArrayList();
  for(int i = 1; i ﹤= cols; i++) {
  colNames.add(meta.getColumnLabel(i));
  }
  request.setAttribute("colNames", colNames);
  %﹥
  ﹤table border="1"  cellpadding="0" style="border-collapse: collapse; "  width="100%" bordercolor="#000000" align=center ﹥
  ﹤tr﹥
  ﹤c:forEach items="${colNames}" var="c"﹥
  ﹤td﹥${c}﹤/td﹥
  ﹤/c:forEach﹥
  ﹤/tr﹥
  ﹤%
  while(rs.next()) {
  colNames.clear();
  for(int i = 1; i ﹤= cols; i++) {
  Object value = rs.getObject(i);
  System.out.println(value.getClass());
  // TODO 更多格式化控制
  if(value instanceof java.sql.Date) {
  value = rs.getTimestamp(i);// 取出精确日期
  java.text.SimpleDateFormat df = new java.text.SimpleDateFormat("yyyy年MM月dd日HH点mm分ss秒EEE");
  value = df.format(value);
  }
  if(value instanceof java.math.BigDecimal) {
  java.math.BigDecimal v = (java.math.BigDecimal)value;
  value = v.doubleValue();
  // 要求输出时候最少显示两位小数, 最多输出小数点后3个数
  java.text.NumberFormat format = java.text.NumberFormat.getInstance();// 只对小数格式化
  format.setMaximumFractionDigits(2);// 最多3位小数
  format.setMinimumFractionDigits(1);// 最少2位小数
  value = format.format(value);// ==﹥ String
  }
  colNames.add(value);
  }
  request.setAttribute("colNames", colNames);
  %﹥
  ﹤tr﹥
  ﹤c:forEach items="${colNames}" var="c"﹥
  ﹤td﹥${c}﹤/td﹥
  ﹤/c:forEach﹥
  ﹤/tr﹥
  ﹤%
  }
  rs.close();
  stmt.close();
  conn.close();
  %﹥
  ﹤/table﹥
  ﹤/c:if﹥
  ﹤/body﹥
  ﹤/html﹥

  用到了JSTL和EL, 总的来说要改版还是很方便的. 但是运行需要比较高的Tomcat版本, 如5.5以上, 并需要JSTL类库. 不过, 类似的模版项目开源框架应该是很多很多的, 例如众多的Report框架.