JSP实现简易的SQL报表 - BeanSoft's Java Blog

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

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

输出:

编号	姓名	年龄

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

因为现在Hibernate用的比较广泛, 所以优先考虑用Hibernate来实现, 结果发现如果是实体映射查询语句, 可以方便的用:List<String> 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<Object[]> 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="http://java.sun.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>