Hibernate 的查詢語言 HQL 與進階查詢
資料庫操作實作類的開發
import java.util.List;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.sf.dao.IDepartmentDao;
import org.sf.entity.Department;
import org.sf.util.HibernateSessionFactory;
public class DepartmentDaoImpl implements IDepartmentDao {
/**
* 取得所有的系院資訊集合
* @return
*/
@SuppressWarnings("unchecked")
public List<Department> getAllDepartmentList(){
List<Department> list = null;
Session session = HibernateSessionFactory.getSession();
Query q=session.createQuery("from Department");
list = q.list();
session.close();
return list;
}
/**
* 通過id 取得某個系院資訊
* @param id
* @return
*/
public Department getDepartmentById(int id){
Department department = null;
Session session = HibernateSessionFactory.getSession();
Query q =session.createQuery("from Department where id=:id").setParameter("id",
id);
department=(Department)q.uniqueResult();
session.close();
return department;
}
/**
* 添加系院資訊
* @param department
* @return
*/
public Department addDepartment(Department department){
Session session = HibernateSessionFactory.getSession();
Transaction tran = session.beginTransaction();
session.save(department);
tran.commit();
session.close();
return department;
}
/**
* 修改標明的系院資訊
* @param department
*/
public void editDepartment(Department department){
Session session = HibernateSessionFactory.getSession();
Transaction tran = session.beginTransaction();
Department depEdit = (Department) session.load(Department.class,
department.getId());
depEdit.setBm(department.getBm());
depEdit.setMc(department.getMc());
depEdit.setTell(department.getTell());
depEdit.setLeader(department.getLeader());
session.update(depEdit);
tran.commit();
session.close();
}
/**
* 通過id 删除系院資訊
* @param id
* @return
*/
public int delDepartmentById(int id){
int i = 0;
Session session = HibernateSessionFactory.getSession();
Transaction tran = session.beginTransaction();
SQLQuery q = (SQLQuery) session.createSQLQuery("delete from department where
id=:id").setParameter("id", id);
q.executeUpdate();
tran.commit();
session.close();
return i;
}
}
制作 IStudentDao 接口及 及 StudentDaoImpl
import java.util.List;
import org.sf.entity.Student;
/**
* 學生資料庫操作接口
* @author 宋鋒
* @date 2013-04-25
*/
public interface IStudentDao {
/**
* 取得所有的學生資訊
* @return
*/
public List<Student> getAllStudentList();
/**
* 通過 id 取得某個學生資訊
* @param id
* @return
*/
public Student getStuentById(int id);
/**
* 通過學号和密碼取得學生資訊,用于登入
* @param stunumber
* @param stupwd
* @return
*/
public Student getStudentByStunumberAndStupwd(String stunumber, String stupwd);
/**
* 取得學生的總數資訊
* @return
*/
public int getStudentCount();
/**
* 取得學生分頁資訊
* @param pageNum
* @param pageSize
* @return
*/
public List<Student> getStudentPageList(int pageNum, int pageSize);
/**
* 通過系院 id 取得學生資訊
* @param depid
* @return
*/
public List<Student> getStudentListByDepid(int depid);
/**
* 添加學生資訊
* @param student
* @return
*/
public Student addStudent(Student student);
/**
* 修改標明的學生資訊
* @param student
*/
public void editStudent(Student student);
/**
* 删除學生資訊
* @param id
* @return
*/
public int delStudentById(int id);
}
資料庫操作類的開發
import java.util.List;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.sf.entity.Student;
import org.sf.util.HibernateSessionFactory;
import org.sf.dao.IStudentDao;
/**
* 學生資料庫操作實作類
* @author 宋鋒
* @date 2013-04-25
*/
public class StudentDaoImpl implements IStudentDao {
/**
* 取得所有的學生資訊
* @return
*/
@SuppressWarnings("unchecked")
public List<Student> getAllStudentList(){
List<Student> list = null;
Session session = HibernateSessionFactory.getSession();
Query q = session.createQuery("from Student");
list = q.list();
session.close();
return list;
}
/**
* 通過id 取得某個學生資訊
* @param id
* @return
*/
public Student getStuentById(int id){
Student student = null;
Session session = HibernateSessionFactory.getSession();
Query q = session.createQuery("from Student where id=?");
q.setInteger(0, id);
student = (Student)q.uniqueResult();
session.close();
return student;
}
/**
* 通過學号和密碼取得學生資訊,用于登入
* @param stunumber
* @param stupwd
* @return
*/
public Student getStudentByStunumberAndStupwd(String stunumber, String stupwd){
Student student = null;
Session session = HibernateSessionFactory.getSession();
Query q = session.createQuery("from Student where stunumber=? and stupwd=?");
q.setString(0, stunumber);
q.setString(1, stupwd);
student = (Student)q.uniqueResult();
session.close();
return student;
}
/**
* 取得學生的總數資訊
* @return
*/
public int getStudentCount(){
int count = 0;
Session session = HibernateSessionFactory.getSession();
Query q = session.createQuery("select count(s) from Student s");
Number number = (Number)q.uniqueResult();
count = number.intValue();
session.close();
return count;
}
/**
* 取得學生分頁資訊
* @param pageNum
* @param pageSize
* @return
*/
@SuppressWarnings("unchecked")
public List<Student> getStudentPageList(int pageNum, int pageSize){
List<Student> list = null;
Session session = HibernateSessionFactory.getSession();
Query q = session.createQuery("from Student");
q.setFirstResult((pageNum-1)*pageSize);
q.setMaxResults(pageSize);
list = q.list();
session.close();
return list;
}
/**
* 通過 系院id 取得學生資訊
* @param depid
* @return
*/
@SuppressWarnings("unchecked")
public List<Student> getStudentListByDepid(int depid){
List<Student> list = null;
Session session = HibernateSessionFactory.getSession();
Query q= session.createQuery("from Student where department.id=?");
q.setInteger(0, depid);
list = q.list();
session.close();
return list;
}
/**
* 添加學生資訊
* @param student
* @return
*/
public Student addStudent(Student student){
Session session = HibernateSessionFactory.getSession();
Transaction tran = session.beginTransaction();
session.save(student);
tran.commit();
session.close();
return student;
}
/**
* 修改標明的學生資訊
* @param student
*/
public void editStudent(Student student){
Session session = HibernateSessionFactory.getSession();
Transaction tran = session.beginTransaction();
Student stuEdit = (Student)session.load(Student.class, student.getId());
stuEdit.setStunumber(student.getStunumber());
stuEdit.setStuname(student.getStuname());
stuEdit.setStupwd(student.getStupwd());
stuEdit.setDepartment(student.getDepartment());
session.update(stuEdit);
tran.commit();
session.close();
}
/**
* 删除學生資訊
* @param id
* @return
*/
public int delStudentById(int id){
int i = 0;
Session session = HibernateSessionFactory.getSession();
Transaction tran = session.beginTransaction();
SQLQuery sq= session.createSQLQuery("delete from student where id=:id");
sq.setParameter("id", id);
i = sq.executeUpdate();
tran.commit();
session.close();
return i;
}
}
修改 Student.hbm.xml 中 中的 的 many-to-one 關系
<many-to-one name="department" class="org.sf.entity.Department" fetch="select" lazy="false">
<column name="depid">
<comment> 系别id</comment>
</column>
</many-to-one>
實作 students.jsp 頁面
<%@ page language="java" import="java.util.*" pageEncoding="gbk"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title> 取得并顯示所有的學生</title>
</head>
<body>
<jsp:useBean id="stuDao" class="org.sf.dao.impl.StudentDaoImpl"></jsp:useBean>
<table align="center" >
<tr>
<th> 學号</th>
<th> 姓名</th>
<th> 系院</th>
</tr>
<c:forEach var="stu" items="${stuDao.allStudentList}">
<tr>
<td>${stu.stunumber}</td>
<td>${stu.stuname}</td>
<td>${stu.department.mc}</td>
</tr>
</c:forEach>
</table>
</body>
</html>
制作學生的分頁顯示 pagestudents.jsp
<%@ page language="java" import="java.util.*" pageEncoding="gbk"%>
<%@page import="org.sf.dao.impl.StudentDaoImpl"%>
<%@page import="org.sf.dao.IStudentDao"%>
<%@page import="org.sf.entity.Student"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title> 分頁顯示學生資訊</title>
</head>
<body>
<table align="center" >
<tr>
<th> 學号</th>
<th> 姓名</th>
<th> 系院</th>
</tr>
<%
String strPageNum = request.getParameter("pageNum");
int pageNum = 1; // 目前 頁面
int pageSize = 3; // 每頁的記錄條數
int pageSum = 0; // 總頁面數
if(strPageNum!=null) // 如果取到了資料,則為調用而來
{
pageNum = Integer.parseInt(strPageNum);
}
IStudentDao stuDao = new StudentDaoImpl();
List<Student> list = stuDao.getStudentPageList(pageNum, pageSize);
iCount = stuDao.getStudentCount(); // 記錄總數
// 計算總頁面數
pageSum = iCount/pageSize;
if(iCount%pageSize!=0){
pageSum+=1;
}
if(list.size()>0){
for(Student stu:list){
%>
<tr>
<td><%=stu.getStunumber() %></td>
<td><%=stu.getStuname() %></td>
<td><%=stu.getDepartment().getMc() %></td>
</tr>
<%
}
}
%>
<tr><td colspan="3" align="center">
<%
out.print(" 第["+pageNum+"] 頁,共["+pageSum+"] 頁");
if(pageNum<2){
%>
上 上1 頁
<%
}else{
%>
<a href="pagestudents.jsp?pageNum=<%=pageNum-1 %>" target="_blank" rel="external nofollow" > 上1 頁</a>
<%
}
%>
<%
if(pageNum>=pageSum){
%>
上 上1 頁
<%
}else{
%>
<a href="pagestudents.jsp?pageNum=<%=pageNum+1 %>" target="_blank" rel="external nofollow" > 下1 頁</a>
<%
}
%>
</td></tr>
</table>
</body>
</html>
制作通過系院查詢學生資訊的 depstudents.jsp
在 WebRoot 下建立 depstudent.jsp 頁面, 當進入頁面時顯示全部的學生資訊,
點選選擇系院資訊後,顯示該系院中的學生資訊,
<%@ page language="java" import="java.util.*" pageEncoding="gbk"%>
<%@page import="org.sf.entity.Student"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title> 取得并顯示所有的學生</title>
</head>
<body>
<jsp:useBean id="stuDao" class="org.sf.dao.impl.StudentDaoImpl"></jsp:useBean>
<jsp:useBean id="depDao" class="org.sf.dao.impl.DepartmentDaoImpl"></jsp:useBean>
<%
String strDepid = request.getParameter("depid");
List<Student> list= null;
if(strDepid==null || strDepid.equals("") || strDepid.equals("0")){ // 查詢所有的資料
list = stuDao.getAllStudentList();
}else{
int depid = Integer.parseInt(strDepid);
request.setAttribute("depid", depid);
list = stuDao.getStudentListByDepid(depid);
}
%>
<table align="center" >
<tr>
<td colspan="3" align="center">
<form action="depstudents.jsp">
系院資訊:
<select id="depid" name="depid">
<option value="0">-- 請選擇--</option>
<c:forEach var="dep" items="${depDao.allDepartmentList}">
<c:choose>
<c:when test="${depid==dep.id}">
<option value="${dep.id}" selected="selected">${dep.mc}</option>
</c:when>
<c:otherwise>
<option value="${dep.id}">${dep.mc}</option>
</c:otherwise>
</c:choose>
</c:forEach>
</select>
<input type="submit" value=" 查詢學生 " />
</form>
</td>
</tr>
<tr>
<th> 學号</th>
<th> 姓名</th>
<th> 系院</th>
</tr>
<%
if(list!=null){
for(Student stu:list){
%>
<tr>
<td><%=stu.getStunumber() %></td>
<td><%=stu.getStuname() %></td>
<td><%=stu.getDepartment().getMc() %></td>
</tr>
<%
}
}
%>
</table>
</body>
</html>
下面關于所用到的資料庫,實體映射請參考上一篇文章 實體映射