原生SQL语句进行统计

最新推荐文章于 2021-03-14 07:38:16 发布

wjiaoling136

最新推荐文章于 2021-03-14 07:38:16 发布

阅读量539

点赞数

分类专栏： Java 文章标签：原生SQL语句统计 c:foreach s:iterator

本文链接：https://blog.csdn.net/wjiaoling136/article/details/84624536

版权

Java 专栏收录该内容

81 篇文章 0 订阅

订阅专栏

最近看到一个根据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

2、处理SQL语句：FactoryDAO.java

package com.wjl.test;

import java.io.Serializable;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.NumberUtils;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

public class FactoryDAO extends HibernateDaoSupport {
	/***
	 * 根据原生 SQL查询
	 * @param sql
	 * @return
	 */
	public List  findBySQL(final String sql) {
		try {
			List<Object [] > result = null;
			try {
                                //一定要记得注入sessionFactory，不然getHibernateTemplate()会空指针
				 result =  getHibernateTemplate().executeFind(new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						return session.createSQLQuery(sql).list();
					}
				});
				
			} catch (Exception e) {
				System.out.println(sql);
				System.out.println("ERROR：" + e.getMessage());
				e.printStackTrace();
			}
			List listy = new ArrayList();
			List listx = null;
			for (int i = 0; i < result.size(); i++) {
				Object [] objs=  result.get(i);
				listx = new ArrayList();
				for (int j = 0; j < objs.length; j++) {
					listx.add(objs[j]);
				}
			listy.add(listx);
			}
			return listy;
		} catch (RuntimeException re) {
			throw re;
		}

	}
}

3、Action：StatisticsAction.java

package com.wjl.test;

import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class StatisticsAction{
	private Map<String,List<?>> allMap;		
	private FactoryDAO fdao =null;//这个地方可以new，也可以不new，但是一定要注入，否则会报空指针的
	
	public String statistics(){
		allMap = new HashMap<String,List<?>>();
		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], fdao.findBySQL(sql.select(array[i])));
		}
		return "statistics";
	}
	public Map<String, List<?>> getAllMap() {
		return allMap;
	}
	public void setAllMap(Map<String, List<?>> allMap) {
		this.allMap = allMap;
	}
	public FactoryDAO getFdao() {
		return fdao;
	}
	public void setFdao(FactoryDAO fdao) {
		this.fdao = fdao;
	}
}