原生SQL语句进行统计
程序员文章站
2022-04-30 08:29:21
...
最近看到一个根据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;
}
}