package com.xiaoshi.Dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import sun.security.pkcs11.Secmod.DbMode;
import com.xiaoshi.DBHelper.DBHelper;
import com.xiaoshi.entity.Dept;
import com.xiaoshi.entity.Employee;
public class DAO {
//通过传入的id查找数据库中相应的信息
//显示数据库的全部信息 返回一个ArrayList
public ArrayList<Employee> getAllEmployees(){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
ArrayList<Employee> list = new ArrayList<Employee>();
try {
//te.ename,te.egender,te.empid,td.dname
conn = DBHelper.getConnection();
String sql = "select te.ename,te.egender,te.empid,td.dname from TblEmp as te inner join TblDept as td on te.depid=td.deptid;";
ps = conn.prepareStatement(sql);//预编译sql语句
rs = ps.executeQuery();
if(rs.next()){
Employee emp = new Employee();
Dept dept = new Dept();
dept.setDname(rs.getString("dname"));
emp.setEmpid(rs.getInt("empid"));
emp.setEname(rs.getString("ename"));
emp.setEgender(rs.getInt("egender"));
emp.setDept(dept);
list.add(emp);
return list;
}else{
return null;
}
} catch (Exception e) {
e.printStackTrace();
return null;
}finally{
if(rs!=null){
try {
rs.close();
rs = null;
} catch (SQLException e) {
e.printStackTrace();
}
if(ps!=null){
try {
ps.close();
ps = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
//根据传入的id来查找相应的全部信息
public Employee getEmployees(int id){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DBHelper.getConnection();
String sql = "select * from TblEmp where empid=?;";
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
rs = ps.executeQuery();
if(rs.next()){
Employee emp = new Employee();
emp.setEmpid(rs.getInt("empid"));
emp.setEname(rs.getString("ename"));
emp.setEgender(rs.getInt("egender"));
emp.setDepid(rs.getInt("depid"));
return emp;
}else{
return null;
}
} catch (Exception e) {
e.printStackTrace();
return null;
}finally{
// 释放数据集对象
if (rs != null) {
try {
rs.close();
rs = null;
} catch (Exception ex) {
ex.printStackTrace();
}
}
// 释放语句对象
if (ps != null) {
try {
ps.close();
ps = null;
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
}
public boolean isdelete(int dmpid){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DBHelper.getConnection();
String sql = "delete from TblEmp where dmpid=?;";
ps = conn.prepareStatement(sql);
ps.setInt(1, dmpid);
rs = ps.executeQuery();
return true;
} catch (Exception e) {
e.printStackTrace();
}
return false;
}
}package com.xiaoshi.entity;
//部门实体类
public class Dept {
private int deptid;//部门编号
private String dname;//部门名称
public Dept() {
super();
}
public Dept(String dname) {
super();
this.dname = dname;
}
public Dept(int deptid, String dname) {
super();
this.deptid = deptid;
this.dname = dname;
}
public int getDeptid() {
return deptid;
}
public void setDeptid(int deptid) {
this.deptid = deptid;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
}package com.xiaoshi.entity;
//员工实体类
public class Employee {
private int empid;//员工编号
private String ename;//员工姓名
private int egender;//员工性别 1男2女
private int depid;//员工所在部门编号
private Dept newdept;
public Dept getDept() {
return newdept;
}
public void setDept(Dept newdept) {
this.newdept = newdept;
}
public Employee() {
}
public Employee(Dept newdept) {
super();
this.newdept = newdept;
}
public Employee(int empid, String ename, int egender, int depid) {
super();
this.empid = empid;
this.ename = ename;
this.egender = egender;
this.depid = depid;
}
public int getEmpid() {
return empid;
}
public void setEmpid(int empid) {
this.empid = empid;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public int getEgender() {
return egender;
}
public void setEgender(int egender) {
this.egender = egender;
}
public int getDepid() {
return depid;
}
public void setDepid(int depid) {
this.depid = depid;
}
}<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Insert title here</title> </head> <body> <a href="listemp.jsp">显示员工列表</a> </body> </html>
<%@page import="java.util.ArrayList"%>
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%@ page import="com.xiaoshi.Dao.DAO" %>
<%@ page import="com.xiaoshi.entity.Employee" %>
<%@ page import="com.xiaoshi.entity.Dept" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
</head>
<body>
<table>
<tr>
<th>员工编号</th>
<th>姓名</th>
<th>部门名称</th>
<th>修改</th>
<th>删除</th>
</tr>
<tr>
<%
DAO dao = new DAO();
ArrayList<Employee>list =dao.getAllEmployees();
if(list!=null&&list.size()>0){
for(int i=0;i<list.size();i++){
Employee emp = list.get(i);
%>
<td> <%=emp.getEmpid() %></td>
<td><a href= "servlet/DisplayEmpServlet?action=find&empid=<%=emp.getEmpid() %>"><%=emp.getEname() %></a></td>
<td><%=emp.getDept().getDname() %></td>
<td>修改</td>
<td><a href= "servlet/DisplayEmpServlet?action=delete&dmpid=<%=emp.getEmpid() %>">删除</a></td>
<%
}
}
%>
</tr>
</table>
</body>
</html>//sql是根据上面的这个例子 http://www.doc88.com/p-311748277733.html
我的南方