struts2+jdbc+oracle查询(模糊查询+页面查询）
public class EmpDao {
//查询所有的员工
public List<EmpBean> findAllEmp(){
List<EmpBean> listemp = new ArrayList<EmpBean>();
Session session = HibernateUtil.getSession();
// String hql = "from EmpBean emp";
// Query query = session.createQuery(hql);
// listemp = query.list();
listemp = session.createQuery("from EmpBean emp order by emp.sal desc").list();
session.close();
return listemp;
}
public List<EmpBean> findAllEmpPage(int start,int count)
{
List<EmpBean> listemp = new ArrayList<EmpBean>();
Session session = HibernateUtil.getSession();
String hql = "from EmpBean emp";
Query query = session.createQuery(hql);
query.setFirstResult(start);
query.setMaxResults(count);
listemp = query.list();
session.close();
return listemp;
}
public List<EmpBean> findAllEmpLikeName(String likename)
{
List<EmpBean> listemp = new ArrayList<EmpBean>();
Session session = HibernateUtil.getSession();
String hql = "from EmpBean emp where emp.ename like '%" + likename.toUpperCase() + "%'";
Query query = session.createQuery(hql);
listemp = query.list();
session.close();
return listemp;
}
public List<EmpBean> findAllEmpBySalRange(double lsal,double hsal)
{
List<EmpBean> listemp = new ArrayList<EmpBean>();
Session session = HibernateUtil.getSession();
String hql = "from EmpBean emp where emp.sal between " + lsal + " and " + hsal;
Query query = session.createQuery(hql);
listemp = query.list();
session.close();
return listemp;
}
//查询薪水范围查询public List<EmpBean> findAllEmpBySalRange2(double lsal,double hsal)
{
List<EmpBean> listemp = new ArrayList<EmpBean>();
Session session = HibernateUtil.getSession();
String hql = "from EmpBean emp where emp.sal >= ? and emp.sal <= ?";
Query query = session.createQuery(hql);
listemp = query.list();
session.close();
return listemp;
}
//统计部门信息
public Iterator calcdeptinfo()
{
Session session = HibernateUtil.getSession();
String hql = "select dept.dname,sum(emp.sal),avg(emp.sal),max(emp.sal),min(emp.sal),count(emp.empno) " +
"from EmpBean emp,DeptBean dept " +
"where emp.deptBean.deptno = dept.deptno " +
"group by dept.dname";
Iterator itobj = session.createQuery(hql).list().iterator();
session.close();
return itobj;
}
}
findallemp.jsp:
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib uri="/struts-tags" prefix="s"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>My JSP 'index.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<link rel="stylesheet" type="text/css" href="css/workit.css">
<script type="text/javascript" src="js/jquery-1.8.3.js"></script>
<SCRIPT type="text/javascript">
$(function(){
$("#empbt").click(function(){
//alert("-------" + $("#empename").val());
$.ajax({
type: "POST",
url: "EmpAction_findempbylikename",
data: {"likename":$("#empename").val()},
success: function(msg){
//alert($("#empdiv") + "------------");
$("#emptab").children().remove();
$("#emptab").append(msg);
}
});
});
$("#empsalbt").click(function(){
alert("薪水范围查询开始。。。。。。。。。");
$.ajax({
type: "POST",
url: "EmpAction_findempbysalrange",
data: {"lsal":$("#emplsal").val(),"hsal":$("#emphsal").val()},
success: function(msg){
alert("薪水范围查询返回中。。。。。。。。");
$("#emptab").children().remove();
$("#emptab").append(msg);
}
});
});
$("#deptinfolink").click(function(){
$.ajax({
type: "POST",
url: "EmpAction_calcDeptInfo",
success: function(msg){
$("#deptdiv").append(msg);
}
});
});
$("#pagebt").click(function(){
//alert("-------" + $("#empename").val());
$.ajax({
type: "POST",
url: "EmpAction_findallemppage",
data: {"start":$("#start").val(),"count":$("#count").val()},
success: function(msg){
//alert($("#empdiv") + "------------");
$("#pageempdiv").append(msg);
}
});
});
})
</SCRIPT>
</head>
<body>
Hibernate查询测试
<hr/>
员工姓名：<input type="text" name="empname" id="empename"/>
<input type="submit" id="empbt" value="查询"/>
薪水范围：
<input type="text" name="emplsal" id="emplsal"/>
到
<input type="text" name="emphsal" id="emphsal"/>
<input type="submit" id="empsalbt" value="查询"/>
<div id="empdiv">
<table class="emptab" id="emptab">
<tr>
<td>员工工号</td>
<td>员工姓名</td>
<td>员工岗位</td>
<td>员工薪水</td>
<td>员工佣金</td>
<td>员工部门</td>
<td>操作</td>
</tr>
<s:iterator value="listemp" var="emp" status="st">
<tr>
<td>${emp.empno }</td>
<td>${emp.ename }</td>
<td>${emp.job }</td>
<td>${emp.sal }</td>
<td>${emp.comm }</td>
<td>${emp.deptBean.dname }</td>
<td>
<a href="EmpAction_findcurremp?empno=${emp.empno }">【修改】</a>
<a href="EmpAction_deletecurremp?empno=${emp.empno }">【删除】</a>
</td>
</tr>
</s:iterator>
<tr>
<td colspan="7">
开始记录：<input type="text" id="start" value="1"/>
每页条数：<input type="text" id="count" value="5"/>
<input type="submit" id="pagebt" value="分页显示"/>
</td>
</tr>
</table>
<div id="pageempdiv">
</div>
<hr/>
<input type="submit" id="deptinfolink" value="【统计部门信息】"/>
<div id="deptdiv">
</div>
</div>
</body>
</html>