原生SQL语句进行统计

最近看到一个根据SQL进行统计的例子,当然这个SQl语句很复杂,跨了很多张表,各种子查询,反正就是很复杂,而且执行的SQL语句还很多,但无论SQL语句多少多复杂,查询和显示都只用了一个方法,感觉挺不错的,可能以后我会用得着。

测试数据库:MS SQL Server 2005

优点:不需要特定的实体,不需要特定的service实现类,可以一次性指定多个SQL语句

实现效果:在一个JSP页面展示人员、部门和岗位的基本信息,涉及到简单的统计。

个人建议:复杂查询时用比较好。

JDBC版本:

1、准备工作,链接数据库:BaseDao.java

package com.wjl.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BaseDao {
	private Connection conn;
	public Connection getConn(){
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			  conn =DriverManager.getConnection("jdbc:sqlserver://127.0.0.1:1433;databaseName=test","sa","sa"); 
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
	public void closeAll(ResultSet rs,PreparedStatement ps,Connection conn){
			try {
				if(rs!=null)rs.close();
				if(ps!=null)ps.close();
				if(conn!=null)conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}
	
	public static void main(String args[]){
		BaseDao bd = new BaseDao();
		Connection conn = bd.getConn();
		if(conn!=null){
			System.out.println("连接成功");
		}else{
			System.out.println("连接失败");
		}
	}
}

2、根据SQL语句处理数据:FactoryDao.java

package com.wjl.test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class FactoryDao extends BaseDao {
	private Connection conn;
	private PreparedStatement ps;
	private ResultSet rs;

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List find(String sql){
		//基本思路:将查询出来的结果一行一行取出来,再把一行的数据一个一个取出来,添加到list中,再将一行的List添加到一个大的List中
		List listy = new ArrayList();
		conn=super.getConn();
		try {
			ps = conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				List listx = new ArrayList();
				for(int i=1;i<30;i++){
					try{
					   listx.add(rs.getString(i));
					}catch(Exception e){
						break;
					}
				}
				listy.add(listx);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			super.closeAll(rs, ps, conn);
		}
		return listy;
		
	}
}

3、拼接SQL语句:QuerySql.java

package com.wjl.test;

import java.lang.reflect.Field;

public class QuerySql {
	//人员信息
	public String t1="SELECT S_USERNAME,S_SEX,S_BIRTHDAY,DATEDIFF(YEAR,S_BIRTHDAY,GETDATE()),S_Department_Name,S_JOB_NAME FROM S_USER";
	//部门信息
	public String t2="SELECT s.S_DEPARTMENT,count(u.S_USER_ID) FROM S_DEPARTMENT s LEFT JOIN S_USER u ON u.S_DEPARTMENT_ID=s.S_DEPARTMENT_ID GROUP BY s.S_DEPARTMENT";
	//岗位信息
	public String t3="SELECT j.S_JOB_NAME,j.S_DEPARTMENT,COUNT(u.S_USER_ID) FROM S_JOB j LEFT JOIN S_USER u ON j.S_JOB_ID =u.S_JOB_ID GROUP BY j.S_JOB_NAME,j.S_DEPARTMENT";
	
        //基本思路:通过反射机制,获取到传递过来指定的公共成员字段,在通过Field的get(param)方法获取公共字段的值进行返回
	public String select(String sql){
		String sqls = "";
		try {
			QuerySql s = new QuerySql();
			//s.getClass():返回此 Object 的运行时类。返回的 Class 对象是由所表示类的 static synchronized 方法锁定的对象。 
			//Class.getField(String):返回一个 Field 对象,它反映此 Class 对象所表示的类或接口的指定公共成员字段。name 参数是一个 String,用于指定所需字段的简称。
			Field fieldY = s.getClass().getField(sql);
			//Field.get(s):返回指定对象上此 Field 表示的字段的值。如果该值是一个基本类型值,则自动将其包装在一个对象中。
			Object y = (Object)fieldY.get(s);
			sqls = y.toString();			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return sqls;
	}
}

 4、Action:StatisticsAction.java

package com.wjl.test;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.opensymphony.xwork2.ActionSupport;

public class StatisticsAction extends ActionSupport {
	private Map<String,List<?>> allMap;		
	
	public String statistics(){
		allMap = new HashMap<String,List<?>>();
		FactoryDao fd = new FactoryDao();
		QuerySql sql = new QuerySql();
		String[] array = new String[]{"t1","t2","t3"};
		for(int i=0;i<array.length;i++){
			//将返回的List添加到Map中,如果只有一个查询语句,就不需要用到Map了
			allMap.put(array[i], fd.find(sql.select(array[i])));
		}
		return "statistics";
	}
	public Map<String, List<?>> getAllMap() {
		return allMap;
	}
	public void setAllMap(Map<String, List<?>> allMap) {
		this.allMap = allMap;
	}

}

5、展示页面:statistics.jsp

 a、使用S标签展示:

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib prefix="s" uri="/struts-tags"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>

<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>原生SQL统计</title>
<style type="text/css">
.p_show{ border-collapse:collapse; }
.p_show td{ border:1px solid #ccc; text-align:center; padding-left:10px; padding-right:10px; white-space:nowrap;height:26px; line-height:26px; }
.p_show th{background-color:#e1e1e1; border:1px solid #ccc; text-align:center; padding-left:10px; padding-right:10px; white-space:nowrap;height:26px; line-height:26px; }
</style>
</head >
<body>
<center>

	<h3>人员信息</h3>
	<table  border="0" cellspacing="0" cellpadding="0" class="p_show" width="">
		<tr>
			<th>序号</th>
			<th>姓名</th>
			<th>性别</th>
			<th>出生日期</th>
			<th>年龄</th>
			<th>所在部门</th>
			<th>工作岗位</th>
		</tr>
		<s:iterator value="allMap['t1']" id="list" status="xuhao" >
			<tr>
				<td>${xuhao.index+1}</td><!-- 序号 -->
				<td>${list[0]}</td><!-- 姓名 -->
				<td>${list[1]}</td><!-- 性别  -->
				<td><!-- 出生日期 -->
					${fn:substring(list[2],0,10)}
				</td>
				<td>${list[3]}</td><!-- 年龄 -->
				<td>${list[4]}</td><!-- 所在部门 -->
				<td>${list[5]}</td><!-- 工作岗位 -->
			</tr>
		</s:iterator>
	</table>
	<h3>部门信息</h3>
	<table  border="0" cellspacing="0" cellpadding="0" class="p_show" width="">
		<tr>
			<th>序号</th>
			<th>部门名称</th>
			<th>该部门人员数</th>
		</tr>
		<s:iterator value="allMap['t2']" id="list2" status="xuhao2" >
			<tr>
				<td>${xuhao2.index+1}</td><!-- 序号 -->
				<td>${list2[0]}</td><!-- 部门名称 -->
				<td>${list2[1]}</td><!-- 该部门员工数量 -->
			</tr>
		</s:iterator>
	</table>
	<h3>岗位信息</h3>
	<table  border="0" cellspacing="0" cellpadding="0" class="p_show" width="">
		<tr>
			<th>序号</th>
			<th>岗位名称</th>
			<th>所属部门</th>
			<th>该岗位人员数</th>
		</tr>
		<s:iterator value="allMap['t3']" id="list3" status="xuhao3" >
			<tr>
				<td>${xuhao3.index+1}</td><!-- 序号 -->
				<td>${list3[0]}</td><!-- 岗位名称 -->
				<td>${list3[1]}</td><!-- 所属部门 -->
				<td>${list3[2]}</td><!-- 该岗位人员数 -->
			</tr>
		</s:iterator>
	</table>
</center>
</body>
</html>

b、使用C标签展示:

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>原生SQL统计</title>
<style type="text/css">
.p_show{ border-collapse:collapse; }
.p_show td{ border:1px solid #ccc; text-align:center; padding-left:10px; padding-right:10px; white-space:nowrap;height:26px; line-height:26px; }
.p_show th{background-color:#e1e1e1; border:1px solid #ccc; text-align:center; padding-left:10px; padding-right:10px; white-space:nowrap;height:26px; line-height:26px; }
</style>
</head >
<body>
<center>

	<h3>人员信息</h3>
	<table  border="0" cellspacing="0" cellpadding="0" class="p_show" width="">
		<tr>
			<th>序号</th>
			<th>姓名</th>
			<th>性别</th>
			<th>出生日期</th>
			<th>年龄</th>
			<th>所在部门</th>
			<th>工作岗位</th>
		</tr>
		<c:forEach items="${allMap.t1}" var="list" varStatus="xuhao">
				<tr>
				<td>${xuhao.index+1}</td><!-- 序号 -->
				<td>${list[0]}</td><!-- 姓名 -->
				<td>${list[1]}</td><!-- 性别  -->
				<td><!-- 出生日期 -->
					${fn:substring(list[2],0,10)}
				</td>
				<td>${list[3]}</td><!-- 年龄 -->
				<td>${list[4]}</td><!-- 所在部门 -->
				<td>${list[5]}</td><!-- 工作岗位 -->
			</tr>
		</c:forEach>
	</table>
	<h3>部门信息</h3>
	<table  border="0" cellspacing="0" cellpadding="0" class="p_show" width="">
		<tr>
			<th>序号</th>
			<th>部门名称</th>
			<th>该部门人员数</th>
		</tr>

		<c:forEach items="${allMap.t2}" var="list2" varStatus="xuhao2">
			<tr>
				<td>${xuhao2.index+1}</td><!-- 序号 -->
				<td>${list2[0]}</td><!-- 部门名称 -->
				<td>${list2[1]}</td><!-- 该部门员工数量 -->
			</tr>
		</c:forEach>
	</table>
	<h3>岗位信息</h3>
	<table  border="0" cellspacing="0" cellpadding="0" class="p_show" width="">
		<tr>
			<th>序号</th>
			<th>岗位名称</th>
			<th>所属部门</th>
			<th>该岗位人员数</th>
		</tr>

		<c:forEach items="${allMap.t3}" var="list3" varStatus="xuhao3">
			<tr>
				<td>${xuhao3.index+1}</td><!-- 序号 -->
				<td>${list3[0]}</td><!-- 岗位名称 -->
				<td>${list3[1]}</td><!-- 所属部门 -->
				<td>${list3[2]}</td><!-- 该岗位人员数 -->
			</tr>
		</c:forEach>
	</table>
	</center>
</body>
</html>

Hibernate版本:

Hibernate版本的只需要修改FactoryDAO和Action,其他的都一样。

1、准备工作:配置事务和注入FactoryDAO

<!--配置哪些类的方法进行事务管理,当前com.wjl.test包中的子包, 类中所有方法需要,还需要参考tx:advice的设置	-->
	<aop:config proxy-target-class="true">
		<aop:pointcut id="aop5" expression="execution(* com.wjl.test.*.*(..))"/>
    	        <aop:advisor advice-ref="txAdvice" pointcut-ref="aop5"/>
	</aop:config>

	<!-- 注入FactoryDAO -->
	<bean id="factoryDAO" class="com.wjl.test.FactoryDAO">
		<property name="sessionFactory">
			<ref bean="sessionFactory" />
		</property>
	</bean>
	
	<bean id="statisAction" class="com.wjl.test.StatisticsAction">
		<property name="fdao" ref="factoryDAO"></property>
	</bean

相关推荐