`

原生SQL语句进行统计

    博客分类：
  • Java
原生SQL语句统计c:foreachs:iterator 
阅读更多

最近看到一个根据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;
	}
}
分享到：
| 配置Java的环境变量
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    hibernate执行原生sql语句

    "hibernate执行原生sql语句" Hibernate 是一种流行的 ORM（Object-Relational Mapping）框架，用于将 Java 对象映射到关系数据库中。然而，在一些情况下，我们需要直接执行原生 SQL 语句，而不是使用 Hibernate 的...

    mybatis直接执行sql语句后续之一

    在IT行业中，MyBatis是一个广泛使用的持久层框架，它允许开发者直接编写SQL语句，提供了灵活的数据访问和映射机制。这篇博客“mybatis直接执行sql语句后续之一”可能探讨了如何在MyBatis中高效且有效地执行SQL操作。...

    原生封装sql语句，增删改查，附带连接数据库

    原生封装sql语句，增删改查，附带连接数据库

    动态拼接sql语句工具类,拼接where后面语句

    动态拼接sql语句工具类,拼接where后面语句 配合原生jdbc仿动态sql注入 if (ObjectUtil.isNotEmpty(maxLat)&&ObjectUtil.isNotEmpty(minLat)){ sqlParamList.add(new SqlParam("lat",minLat, SqlOpEnum.GE)); ...

    thinkPHP框架中执行原生SQL语句的方法

    主要介绍了thinkPHP框架中执行原生SQL语句的方法,结合实例形式分析了thinkPHP中执行原生SQL语句的相关操作技巧,并简单分析了query与execute方法的使用区别,需要的朋友可以参考下

    EF Core执行原生SQL语句的一个扩展方法

    在EFCore下执行原生SQL查询语句的方法——FromSqlRaw和FromSqlInterpolated，不能查找部分列，只能查找全部列，而且只能单表查询，不能使用join联查，这是这两个方法的局限性。 而实际场景中，我们经常会处理一些...

    C#将Lambda表达式转成Sql语句

    然而，在某些情况下，我们需要将这些表达式转换为实际的SQL语句，以便在数据库中执行。本文将深入探讨如何实现这一过程。 首先，我们需要了解Lambda表达式的基本结构。Lambda表达式通常以参数列表开始，后面跟着一...

    mysql原生sql语句单表多条件查询的封装

    mysql原生sql语句单表多条件查询的封装

    Diango原生SQL语句增删数据库原码.zip

    Diango原生SQL语句增删数据库原码，小而简单的图书管理，基本实现一些简单的功能，学习笔记，备忘

    Laravel框架执行原生SQL语句及使用paginate分页的方法

    本文实例讲述了Laravel框架执行原生SQL语句及使用paginate分页的方法。分享给大家供大家参考，具体如下： 1、运行原生sql public function getList($data){ //获取前端传过来的参数 $user = $data['userId']; $...

    PHP tp5中使用原生sql查询代码实例

    注意事项： ... 2.只要是数据库操作必须引用 use/think/Db;严格区分大小写。... /****************tp5中使用原生语句*******************/ //query 用于查询 其他的用execute // 插入记录 // $result = Db::e

    ThinkPHP3.2.3框架实现执行原生SQL语句的方法示例

    本篇文章将深入探讨如何在ThinkPHP3.2.3框架中实现执行原生SQL语句，包括查询、插入、更新和删除操作。 首先，我们要了解ThinkPHP3.2.3中的Model层，它是用于处理数据模型的，包含了与数据库交互的主要方法。在执行...

    Laravel使用原生sql语句并调用的方法

    有一些sql语句比较复杂，用构造器还不如直接用sql来的方便，我们在laravel中使用原生语句，首先要在开头use DB，然后： $arr = DB::select(select id,sum...以上这篇Laravel使用原生sql语句并调用的方法就是小编分享

    SQL语句拼接

    SQL语句拼接

    JS实现Sql语句格式化效果

    我们知道，在编辑SQL语句的时候，可以通过各种客户端软件对SQL语句进行格式化，但是我们如果想通过前端来格式化SQL怎么办呢？这时我们可以借助sql-formatter.js插件来实现，具体参考Demo附件（可直接运行）。

    学会idea列操作，快速写SQL语句

    idea列操作，指的是我们可以同时操作多列。在我们写sql语句的插入和更新等需要输入属性名时，可以以我们创建的类为基础，通过idea的多列操作，我们可以简单方便的获取到属性名，而不用自己去复制或者手动敲。

    Hibernat使用原生的SQL

    标题 "Hibernat使用原生的SQL" 涉及的是在Java开发中如何利用Hibernate框架执行自定义的SQL语句。Hibernate是一个流行的...为了进一步学习，建议阅读该文档，进行实际的编码练习，以加深对Hibernate原生SQL使用的理解。

    基于SQLAlchemy实现操作MySQL并执行原生sql语句

    执行原生SQL语句有两种方式： 1. 通过`session`对象：`session.execute('sql语句')`，如示例中的`res = session.execute('select * from ServiceOrder')`，然后可以调用`fetchall()`方法获取所有结果。 2. 直接使用`...

    Go轻量ORM,零依赖,零侵入分布式事务,基于原生sql语句

    Go轻量ORM,零依赖,零侵入分布式事务,基于原生sql语句,学习成本更低，代码生成器，代码精简,主体2500行,零依赖4000行,注释详细,方便定制修改．支持事务传播,这是zorm诞生的主要原因．支持达梦(dm),金仓(kingbase),...

Magicbox
Global site tag (gtag.js) - Google Analytics