员工信息存储系统
第一部分、录入
1、整体架构图
2、建表
3、add.jsp
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="content-type" content="html/text;charset=utf-8"/>
<script language="javascript" src="${pageContext.request.contextPath}/js/Calendar.js">
</script>
</head>
<body>
${errorInfo}
<div id="body">
<div id="table">
<form name="form1" action="${pageContext.request.contextPath}/servlet/AddServlet" method="post">
<table>
<tr>
<td class="td1">*用户名：</td>
<td class="td2"><input type="text" size="40%" maxlength="20" name="username"></td>
</tr>
…….
<tr>
<td class="td1">性别：</td>
<td class="td2">
<input type=radio name="sex" value="男">男
<input type=radio name="sex" value="女" checked>女
</td>
</tr>
<tr>
<td class="td1">出生日期：</td>
<td class="td2">
<input type="text" id="time" name="birthday" value="1990-01-01">
<input type="button" onClick="MyCalendar.SetDate(this,document.getElementById('time'))" value="选择"/>
</td>
</tr>
<tr>
<td class="td1">学历：</td>
<td class="td2">
<select name="edu">
<option value="博士">博士</option>
<option value="硕士">硕士</option>
<option value="本科生">本科生</option>
<option value="大专">大专</option>
<option value="中专">中专</option>
<option value="高中">高中</option>
</select>
</td>
</tr>
……
<tr>
<td class="td1">入职时间：</td>
<td class="td2">
<input type="text" name="hiredate" id="time1" value="2000-01-01">
<input type="button" onClick="MyCalendar.SetDate(this,document.getElementById('time1'))" value="选择"/>
</td>
</tr>
……
<tr>
<td>
<div id="que">
<input type="button" size="10%" value="提交" onclick="save()">
</div>
</td>
<td>
<div id="chong">
<input type="reset" size="10%" value="重置">
</div>
</td>
</tr>
</table>
</form>
<script type="text/javascript">
function save(){
//forms表示此页面的form集合(从上往下)，是javascript的内置对象
// alert(document.forms[0].action);
//提交表单
document.forms[0].submit();
}
</script>
……4、Employee.java
package cn.itcast.bean;
import java.sql.Date;
public class Employee {
private Integer id;
private String username;
private String password;
private String realname;
private String sex;
private Date birthday;
private String edu;
private String major;
private String description;
private Date hiredate;
private String role;
……5、AddServlet.java
package cn.itcast.web;
import *;
public class AddServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)throws Exception {
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset:UTF-8");
PrintWriter out=response.getWriter();
//System.out.println("AddServlet*********");
String path="";
try{
//获取表单数据
String username=request.getParameter("username");
String password=request.getParameter("pasw");
String realname=request.getParameter("realname");
String sex=request.getParameter("sex");
String sbirthday=request.getParameter("birthday");
String edu=request.getParameter("edu");
String major=request.getParameter("major");
String description=request.getParameter("description");
String shiredate=request.getParameter("hiredate");
String role=request.getParameter("role");
//封装到javabean中
Employee employee=new Employee();
employee.setUsername(username);
employee.setPassword(password);
employee.setRealname(realname);
employee.setSex(sex);
employee.setEdu(edu);
employee.setMajor(major);
employee.setDescription(description);
employee.setRole(role);
//处理日期(""和格式书写正确)
Date birthday=null;
Date hiredate=null;
if(sbirthday!=null&& !("".equals(sbirthday.trim()))){
birthday=java.sql.Date.valueOf(sbirthday.trim());
}
if(shiredate!=null&& !("".equals(shiredate.trim()))){
hiredate=java.sql.Date.valueOf(shiredate.trim());
}
employee.setBirthday(birthday);
employee.setHiredate(hiredate);
//调用工厂获取dao层对象
DaoEmployee daoEmployee=DaoFactory.getDaoEmployee();
//调用dao保存员工信息的方法
daoEmployee.saveEmployee(employee);
path="/employees/add.jsp";
request.setAttribute("errorInfo", "您的数据保存成功，请继续录入!");
}catch(Exception e){
e.printStackTrace();
path="/error.jsp";
request.setAttribute("errorMsg", "日期转换异常");
}
//转发
request.getRequestDispatcher(path).forward(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}6、DaoEmployee.java
package cn.itcast.dao;
import cn.itcast.bean.Employee;
public interface DaoEmployee {
/*
* 保存员工信息到数据库中
*/
void saveEmployee(Employee employee);
}7、DaoEmployeeImpl.java
package cn.itcast.daoIml;
import *;
public class DaoEmployeeImpl implements DaoEmployee {
public void saveEmployee(Employee employee) {
Connection conn=null;
PreparedStatement pstatement=null;
DBManager dbManager=new DBManager();
conn=dbManager.getConnection();
//组织SQL语句
String sql="INSERT INTO employees (id,username,pasw,realname,sex,birthday,edu,major,des," +
"hiredate,role) values(null,?,?,?,?,?,?,?,?,?,?)";
try {
pstatement=conn.prepareStatement(sql);
pstatement.setString(1, employee.getUsername());
pstatement.setString(2, employee.getPassword());
pstatement.setString(3, employee.getRealname());
pstatement.setString(4, employee.getSex());
pstatement.setDate(5, employee.getBirthday());
pstatement.setString(6, employee.getEdu());
pstatement.setString(7, employee.getMajor());
pstatement.setString(8, employee.getDescription());
pstatement.setDate(9, employee.getHiredate());
pstatement.setString(10, employee.getRole());
pstatement.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(dbManager!=null){
dbManager.closeResource(conn, pstatement, null);
}
}
}
}8、DaoFactory.java
package fn.itcast.factory;
import cn.itcast.dao.DaoEmployee;
import cn.itcast.daoIml.DaoEmployeeImpl;
/*
* 工厂类 简单工厂 获取DaoEmployee接口对象
*/
public class DaoFactory {
public static DaoEmployee getDaoEmployee(){
return new DaoEmployeeImpl();
}
}9、DBManager.java
package cn.itcast.util;
import *;
//数据库管理类
public class DBManager {
private String className="com.mysql.jdbc.Driver";
private String username="root";
private String password="root";
private String url="jdbc:mysql://localhost:3306/test";
//1.注册驱动
public DBManager(){
try {
Class.forName(className);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//2.获取连接
public Connection getConnection(){
Connection conn=null;
try {
conn=DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
//3.关闭资源
public void closeResource(Connection conn,Statement statement,ResultSet rs){
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(statement!=null){
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}10、TestDaoEmployee.java
package cn.itcast.test;
import *;
public class TestDaoEmployee {
static DaoEmployee daoEmployee;
@BeforeClass public static void beforeClass(){
daoEmployee=DaoFactory.getDaoEmployee();
}
@Test public void save(){
//模拟数据
Employee employee=new Employee();
employee.setUsername("fandong");
employee.setPassword("111");
employee.setRealname("发到");
employee.setSex("男");
employee.setBirthday(java.sql.Date.valueOf("1990-09-08"));
employee.setEdu("大专");
employee.setMajor("电子");
employee.setDescription("很好");
employee.setHiredate(java.sql.Date.valueOf("1990-09-09"));
employee.setRole("经理");
daoEmployee.saveEmployee(employee);
}
}第二部分、编辑、更新
1、ListServlet.java
package cn.itcast.web;
import *;
public class ListServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response) throws Exception {
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");
PrintWriter out=response.getWriter();
String path="";
//获取dao对象
DaoEmployee daoEmployee=DaoFactory.getDaoEmployee();
List<Employee> list = daoEmployee.findAllEmployees();
request.setAttribute("list", list);
path="/employees/list.jsp";
request.getRequestDispatcher(path).forward(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}2、list.jsp
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="content-type" content="html/text;charset=utf-8"/>
<script language="javascript" src="Calendar.js">
</script>
</head>
<body>
<div id="body">
<div id="table">
<c:choose>
<c:when test="${!empty list}">
<table border="1">
<tr>
<td><div class="td1">用户名</div></td>
<td><div class="td1">密码</div></td>
<td><div class="td1">真实姓名</div></td>
<td><div class="td1">性别</div></td>
<td><div class="td1">出生日期</div></td>
<td><div class="td1">学历</div></td>
<td><div class="td1">专业</div></td>
<td><div class="td1">职位描述</div></td>
<td><div class="td1">入职时间</div></td>
<td><div class="td1">所属角色</div></td>
<td><div class="td1" colspan="3">操作</div></td>
</tr>
<c:forEach items="${list}" var="employee">
<tr>
<td>${employee.username}</td>
<td>${employee.password}</td>
<td>${employee.realname}</td>
<td>${employee.sex}</td>
<td>${employee.birthday}</td>
<td>${employee.edu}</td>
<td>${employee.major}</td>
<td>${employee.description}</td>
<td>${employee.hiredate}</td>
<td>${employee.role}</td>
<td>
<a href="">查看</a>
<a href="${pageContext.request.contextPath}/servlet/EditServlet?id=${employee.id}">编辑</a>
<a href="">删除</a>
</td>
</tr>
</c:forEach>
</table>
</c:when>
<c:otherwise>
没有您要查询的数据!!!
</c:otherwise>
</c:choose>
……3、DaoEmployee.java
package cn.itcast.dao;
import java.util.List;
import cn.itcast.bean.Employee;
public interface DaoEmployee {
/*
* 查询所有员工的信息
*/
List<Employee> findAllEmployees();
/*
* 通过id查询员工信息
*/
Employee findEmployeeById(Integer id);
/*
* 通过id修改员工信息
*/
void updateEmployee(Employee employee);
}4、DaoEmployeeImpl.java
package cn.itcast.daoIml;
import *;
public class DaoEmployeeImpl implements DaoEmployee {
public void saveEmployee(Employee employee) {
Connection conn=null;
PreparedStatement pstatement=null;
DBManager dbManager=new DBManager();
conn=dbManager.getConnection();
//组织SQL语句
String sql="INSERT INTO employees (id,username,pasw,realname,sex,birthday,edu,major,des," +
"hiredate,role) values(null,?,?,?,?,?,?,?,?,?,?)";
try {
pstatement=conn.prepareStatement(sql);
pstatement.setString(1, employee.getUsername());
pstatement.setString(2, employee.getPassword());
pstatement.setString(3, employee.getRealname());
pstatement.setString(4, employee.getSex());
pstatement.setDate(5, employee.getBirthday());
pstatement.setString(6, employee.getEdu());
pstatement.setString(7, employee.getMajor());
pstatement.setString(8, employee.getDescription());
pstatement.setDate(9, employee.getHiredate());
pstatement.setString(10, employee.getRole());
pstatement.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(dbManager!=null){
dbManager.closeResource(conn, pstatement, null);
}
}
}
public List<Employee> findAllEmployees() {
Connection conn=null;
ResultSet rs=null;
PreparedStatement pstatement=null;
DBManager dbManager=new DBManager();
conn=dbManager.getConnection();
List<Employee> list=new ArrayList<Employee>();
//组织SQL语句
String sql="select id,username,pasw,realname,sex,birthday,edu,major,des," +
"hiredate,role from employees order by id";
try {
pstatement=conn.prepareStatement(sql);
rs = pstatement.executeQuery();
while(rs.next()){
Employee employee=new Employee();
employee.setId(rs.getInt("id"));
employee.setUsername(rs.getString(2));
employee.setPassword(rs.getString(3));
employee.setRealname(rs.getString(4));
employee.setSex(rs.getString("sex"));
employee.setBirthday(rs.getDate(6));
employee.setEdu(rs.getString("edu"));
employee.setMajor(rs.getString("major"));
employee.setDescription(rs.getString(9));
employee.setHiredate(rs.getDate(10));
employee.setRole(rs.getString("role"));
list.add(employee);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(dbManager!=null){
dbManager.closeResource(conn, pstatement, null);
}
}
return list;
}
public Employee findEmployeeById(Integer id) {
if(id==null){
throw new RuntimeException("您要查询的员工的信息的id不能为空");
}
Connection conn=null;
ResultSet rs=null;
PreparedStatement pstatement=null;
DBManager dbManager=new DBManager();
conn=dbManager.getConnection();
Employee employee=null;
//组织SQL语句
String sql="select id,username,pasw,realname,sex,birthday,edu,major,des," +
"hiredate,role from employees where id=?";
try {
pstatement=conn.prepareStatement(sql);
pstatement.setInt(1, id);
rs = pstatement.executeQuery();
if(rs.next()){
employee = new Employee();
employee.setId(rs.getInt("id"));
employee.setUsername(rs.getString(2));
employee.setPassword(rs.getString(3));
employee.setRealname(rs.getString(4));
employee.setSex(rs.getString("sex"));
employee.setBirthday(rs.getDate(6));
employee.setEdu(rs.getString("edu"));
employee.setMajor(rs.getString("major"));
employee.setDescription(rs.getString(9));
employee.setHiredate(rs.getDate(10));
employee.setRole(rs.getString("role"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(dbManager!=null){
dbManager.closeResource(conn, pstatement, null);
}
}
return employee;
}
public void updateEmployee(Employee employee) {
if(employee.getId()==null){
throw new RuntimeException("您要查询的员工的信息的id不能为空");
}
Connection conn=null;
ResultSet rs=null;
PreparedStatement pstatement=null;
DBManager dbManager=new DBManager();
conn=dbManager.getConnection();
//组织SQL语句
String sql="update employees set username=?,pasw=?,realname=?,sex=?,birthday=?,edu=?,major=?,des=?," +
"hiredate=?,role=? where id=?";
try {
pstatement=conn.prepareStatement(sql);
pstatement.setString(1, employee.getUsername());
pstatement.setString(2, employee.getPassword());
pstatement.setString(3, employee.getRealname());
pstatement.setString(4, employee.getSex());
pstatement.setDate(5, employee.getBirthday());
pstatement.setString(6, employee.getEdu());
pstatement.setString(7, employee.getMajor());
pstatement.setString(8, employee.getDescription());
pstatement.setDate(9, employee.getHiredate());
pstatement.setString(10, employee.getRole());
pstatement.setInt(11, employee.getId());
pstatement.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(dbManager!=null){
dbManager.closeResource(conn, pstatement, null);
}
}
}
}5、TestDaoEmployee.java
package cn.itcast.test;
import *;
public class TestDaoEmployee {
static DaoEmployee daoEmployee;
@BeforeClass public static void beforeClass(){
daoEmployee=DaoFactory.getDaoEmployee();
}
@Test @Ignore public void save(){
……
}
@Test @Ignore public void find(){
List<Employee> employee = daoEmployee.findAllEmployees();
for(Employee e:employee){
System.out.println(e.getId()+":"+e.getRealname());
}
}
@Test @Ignore public void findEmployeeById(){
Employee e = daoEmployee.findEmployeeById(123);
if(e!=null){
System.out.println(e.getId()+":"+e.getRealname());
}else{
System.out.println("employee =null");
}
}
@Test public void updateEmployee(){
//模拟数据
Employee employee=new Employee();
employee.setId(1);
employee.setUsername("fandong");
employee.setPassword("111");
employee.setRealname("发到");
employee.setSex("男");
employee.setBirthday(java.sql.Date.valueOf("1990-09-08"));
employee.setEdu("大专");
employee.setMajor("电子");
employee.setDescription("很好");
employee.setHiredate(java.sql.Date.valueOf("1990-09-09"));
employee.setRole("经理");
daoEmployee.updateEmployee(employee);
}
}6、EditServlet.java
package cn.itcast.web;
import *;
public class EditServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");
PrintWriter out=response.getWriter();
String path="";
try{//获取employee的id
String sid=request.getParameter("id");
Integer id=null;
if(sid!=null && !("".equals(sid.trim()))){
id=Integer.parseInt(sid);
}
//获取dao层的对象
DaoEmployee daoEmployee=DaoFactory.getDaoEmployee();
Employee employee = daoEmployee.findEmployeeById(id);
request.setAttribute("employee", employee);
path="/employees/edit.jsp";
}catch(Exception e){
e.printStackTrace();
request.setAttribute("errorMsg", "数据转换有错误！！！");
path="/error.jsp";
}
request.getRequestDispatcher(path).forward(request, response);
}
……7、edit.jsp
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="content-type" content="html/text;charset=utf-8"/>
<script language="javascript" src="${pageContext.request.contextPath}/js/Calendar.js">
</script>
</head>
<body>
<div id="body">
员工信息编辑页面：
<div id="table">
<c:if test="${!empty employee}">
<form action="${pageContext.request.contextPath}/servlet/UpdateServlet" method="post">
<p>
<input type="hidden" name="id" value="${requestScope.employee.id}">
</p>
<table>
<tr>
<td class="td1">用户名：</td>
<td class="td2"><input type="text" name="username" value="${requestScope.employee.username}"></td>
</tr>
<tr>
<td class="td1">密码：</td>
<td class="td2"><input type="password" name="password" value="${employee.password}"></td>
</tr>
<tr>
<td class="td1">真实姓名：</td>
<td class="td2"><input type="text" size="40%" name="name" value="${requestScope.employee.realname}"></td>
</tr>
<tr>
<td class="td1">性别：</td>
<td class="td2">
<input type=radio name="sex" value="男"
<c:if test="${requestScope.employee.sex=='男'}">
checked
</c:if>
/>男
<input type=radio name="sex" value="女" ${requestScope.employee.sex=='女'?'checked':''} />女
</td>
</tr>
<tr>
<td class="td1">出生日期：</td>
<td class="td2">
<input type="text" id="time" name="birthday" value="${employee.birthday}">
<input type="button" onClick="MyCalendar.SetDate(this,document.getElementById('time'))" value="选择"/>
</td>
</tr>
<tr>
<td class="td1">学历：</td>
<td class="td2">
<select name="edu">
<option value="博士" ${employee.edu=='博士'?'selected':''}>博士</option>
<option value="硕士" ${employee.edu=='硕士'?'selected':''}>硕士</option>
<option value="本科生" ${employee.edu=='本科生'?'selected':''}>本科生</option>
<option value="大专" ${employee.edu=='大专'?'selected':''}>大专</option>
<option value="中专" ${employee.edu=='中专'?'selected':''}>中专</option>
<option value="高中" ${employee.edu=='高中'?'selected':''}>高中</option>
</select>
</td>
</tr>
<tr>
<td class="td1">专业：</td>
…….
</td>
</tr>
<tr>
<td class="td1">职位描述：</td>
<td class="td2">
<textarea cols="40" rows="5" name="description" style="overflow:auto">
${employee.description}
</textarea>
</td>
</tr>
<tr>
<td class="td1">入职时间：</td>
……
</tr>
<tr>
<td class="td1">所属角色：</td>
<td class="td2">
<select name="role">
……
</select>
</td>
</tr>
<tr>
<td>
<div id="que">
<input type="submit" size="10%" value="更新">
</div>
</td>
</tr>
</table>
</form>
</c:if>
<c:if test="${empty employee}">
您编辑的员工信息不存在！
</c:if>
</div>
</div>
</body>
</html>8、UpdateServlet.java
package cn.itcast.web;
import *;
public class UpdateServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");
PrintWriter out=response.getWriter();
String path="";
try{
//获取表单数据
String sid=request.getParameter("id");
String username=request.getParameter("username");
String password=request.getParameter("password");
String realname=request.getParameter("name");
String sex=request.getParameter("sex");
String sbirthday=request.getParameter("birthday");
String edu=request.getParameter("edu");
String major=request.getParameter("major");
String description=request.getParameter("description");
String shiredate=request.getParameter("hiredate");
String role=request.getParameter("role");
//封装到javabean中
Employee employee=new Employee();
employee.setUsername(username);
employee.setPassword(password);
employee.setRealname(realname);
employee.setSex(sex);
employee.setEdu(edu);
employee.setMajor(major);
employee.setDescription(description);
employee.setRole(role);
Integer id=null;
if(sid!=null&& !("".equals(sid.trim()))){
id=Integer.parseInt(sid);
}
employee.setId(id);
//处理日期(""和格式书写正确)
Date birthday=null;
Date hiredate=null;
if(sbirthday!=null&& !("".equals(sbirthday.trim()))){
birthday=java.sql.Date.valueOf(sbirthday.trim());
}
if(shiredate!=null&& !("".equals(shiredate.trim()))){
hiredate=java.sql.Date.valueOf(shiredate.trim());
}
employee.setBirthday(birthday);
employee.setHiredate(hiredate);
//调用工厂获取dao层对象
DaoEmployee daoEmployee=DaoFactory.getDaoEmployee();
//调用dao保存员工信息的方法
daoEmployee.updateEmployee(employee);
//保存成功****转到重新添加页面
path="/servlet/ListServlet";
/**
* 怎样看应该转到那个页面：在浏览器中在要转到的页面对应的超链接上面点击右键-->属性，就能看到了
*/
}catch(Exception e){
e.printStackTrace();
path="/error.jsp";
request.setAttribute("errorMsg", "数据转换异常");
}
//转发
request.getRequestDispatcher(path).forward(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}第三部分、查询
1、left.jsp
……
<p>
图书信息管理<br/>
<a href="${pageContext.request.contextPath}/employees/add.jsp" target="right">员工信息录入</a><br/>
<a href="${pageContext.request.contextPath}/servlet/ListServlet" target="right">员工信息查询</a><br/>
<a href="${pageContext.request.contextPath}/employees/listCondition.jsp" target="right">高级查询</a>
……2、list.jsp
……
.td1{
text-align:center;
font-size:14px;
width:70px}
.td2{
text-align:center;
font-size:14px;
width:240px;
……
<div id="table">
<c:choose>
<c:when test="${!empty list}">
<table border="1">
<tr>
<td><div class="td1">用户名</div></td>
<td><div class="td1">密码</div></td>
<td><div class="td1">真实姓名</div></td>
<td><div class="td1">性别</div></td>
<td><div class="td1">出生日期</div></td>
<td><div class="td1">学历</div></td>
<td><div class="td1">专业</div></td>
<td><div class="td1">职位描述</div></td>
<td><div class="td1">入职时间</div></td>
<td><div class="td1">所属角色</div></td>
<td><div class="td1" colspan="3">操作</div></td>
</tr>
<c:forEach items="${list}" var="employee">
<tr>
<td>${employee.username}</td>
<td>${employee.password}</td>
<td>${employee.realname}</td>
<td>${employee.sex}</td>
<td>${employee.birthday}</td>
<td>${employee.edu}</td>
<td>${employee.major}</td>
<td>${employee.description}</td>
<td>${employee.hiredate}</td>
<td>${employee.role}</td>
<td>
<a href="">查看</a>
<a href="${pageContext.request.contextPath}/servlet/EditServlet?id=${employee.id}">编辑</a>
<a href="">删除</a>
</td>
</tr>
</c:forEach>
</table>
</c:when>
<c:otherwise>
没有您要查询的数据!!!
</c:otherwise>
</c:choose>
……3、ListConditionServlet.java
package cn.itcast.web;
import *;
public class ListConditionServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response) throws Exception {
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");
String path="";
try{
//获取查询条件
String username=request.getParameter("username");
String realname=request.getParameter("realname");
String sex=request.getParameter("sex");
String sbeginhiredate=request.getParameter("beginhiredate");
String sendhiredate=request.getParameter("endhiredate");
String edu=request.getParameter("edu");
//封装到javabean
SearchEmployee searchEmployee=new SearchEmployee();
searchEmployee.setUsername(username);
searchEmployee.setRealname(realname);
searchEmployee.setSex(sex);
searchEmployee.setEdu(edu);
Date beginHiredate=null;
if(sbeginhiredate!=null&& !("".equals(sbeginhiredate.trim()))){
beginHiredate=java.sql.Date.valueOf(sbeginhiredate);
}
searchEmployee.setBeginHiredate(beginHiredate);
Date endHiredate=null;
if(sendhiredate!=null&& !("".equals(sendhiredate.trim()))){
endHiredate=java.sql.Date.valueOf(sendhiredate);
}
searchEmployee.setEndHiredate(endHiredate);
//获取dao对象
DaoEmployee daoEmployee=DaoFactory.getDaoEmployee();
//调用查询方法
List<Employee> list=daoEmployee.findEmployeesByCondition(searchEmployee);
request.setAttribute("list", list);
path="/employees/listCondition.jsp";
}catch(Exception e){
e.printStackTrace();
path="/error.jsp";
request.setAttribute("errorMsg", "查询输入条件有误！");
}
request.getRequestDispatcher(path).forward(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}4、SearchEmployee.java
package cn.itcast.bean;
import java.sql.Date;
public class SearchEmployee {
private String username;
private String sex;
private String realname;
private Date beginHiredate;
private Date endHiredate;
private String edu;
……
}5、DaoEmployee.java
package cn.itcast.dao;
import *;
public interface DaoEmployee {
/*
* 保存员工信息到数据库中
*/
void saveEmployee(Employee employee);
/*
* 查询所有员工的信息
*/
List<Employee> findAllEmployees();
/*
* 通过id查询员工信息
*/
Employee findEmployeeById(Integer id);
/*
* 通过id修改员工信息
*/
void updateEmployee(Employee employee);
/*
* 通过条件查询员工信息
*/
List<Employee> findEmployeesByCondition(SearchEmployee searchEmployee);
}6、DaoEmployeeImpl.java
package cn.itcast.daoIml;
import *;
public class DaoEmployeeImpl implements DaoEmployee {
……
public List<Employee> findEmployeesByCondition(SearchEmployee searchEmployee) {
Connection conn=null;
ResultSet rs=null;
PreparedStatement pstatement=null;
DBManager dbManager=new DBManager();
conn=dbManager.getConnection();
List<Employee> list=new ArrayList<Employee>();
//组织SQL语句
String sql="SELECT id,username,pasw,realname,sex,birthday,edu,major,des,hiredate,role FROM employees WHERE 1=1";
//组织查询条件
//封装sql语句
StringBuffer whereSql=new StringBuffer();
//?的值
List<Object> paramList=new ArrayList<Object>();
//用户名用like查询
if(searchEmployee.getUsername()!=null && !("".equals(searchEmployee.getUsername().trim()))){
whereSql.append(" and username like ?");
paramList.add("%"+searchEmployee.getUsername().trim()+"%");
}
//真实姓名用like查询
if(searchEmployee.getRealname()!=null && !("".equals(searchEmployee.getRealname().trim()))){
whereSql.append(" and realname like ?");
//注意下面的代码，不能有单引号
paramList.add("%"+searchEmployee.getRealname().trim()+"%".trim());
}
//性别用=查询
if(searchEmployee.getSex()!=null && !("".equals(searchEmployee.getSex().trim()))){
whereSql.append(" and sex=?");
paramList.add(searchEmployee.getSex().trim().trim());
}
//学历=查询
if(searchEmployee.getEdu()!=null && !("".equals(searchEmployee.getEdu().trim()))){
whereSql.append(" and edu=?");
paramList.add(searchEmployee.getEdu().trim());
}
//雇用日期查询用between and查询
if(searchEmployee.getBeginHiredate()!=null &&searchEmployee.getEndHiredate()!=null){
whereSql.append(" and hiredate between ? and ?");
paramList.add(searchEmployee.getBeginHiredate());
paramList.add(searchEmployee.getEndHiredate());
}
//组合SQL语句和查询条件
sql=sql+whereSql.toString();
//组合SQL语句和排序
sql=sql+" ORDER BY id asc";
System.out.println(sql);
Object[] params=paramList.toArray();
//开始查询
try {
pstatement=conn.prepareStatement(sql);
//设置参数
if(params!=null&¶ms.length>0){
for(int i=0;i<params.length;i++){
pstatement.setObject((i+1), params[i]);
System.out.println("params数组中的元素:"+params[i]);
}
}
rs=pstatement.executeQuery();
while(rs.next()){
Employee employee=new Employee();
employee.setId((Integer)rs.getObject(1));
employee.setUsername((String)rs.getObject(2));
employee.setPassword((String)rs.getObject(3));
employee.setRealname((String)rs.getObject(4));
employee.setSex((String)rs.getObject(5));
employee.setBirthday((java.sql.Date)rs.getObject(6));
employee.setEdu((String)rs.getObject(7));
employee.setMajor((String)rs.getObject(8));
employee.setDescription((String)rs.getObject(9));
employee.setHiredate((java.sql.Date)rs.getObject(10));
employee.setRole((String)rs.getObject(11));
list.add(employee);
System.out.println("添加完毕");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(dbManager!=null){
dbManager.closeResource(conn, pstatement, rs);
}
}
return list;
}
/*public List<Employee> findEmployeesByCondition(SearchEmployee searchEmployee) {
Connection conn=null;
ResultSet rs=null;
Statement statement=null;
DBManager dbManager=new DBManager();
conn=dbManager.getConnection();
List<Employee> list=new ArrayList<Employee>();
//组织SQL语句
String sql="SELECT id,username,pasw,realname,sex,birthday,edu,major,des,hiredate,role FROM employees WHERE 1=1";
StringBuffer whereSql=new StringBuffer("");
String username=searchEmployee.getUsername();
if(username!=null&& !("".equals(username.trim()))){
whereSql.append(" and username like "+"'%"+username+"%'");
}
if(searchEmployee.getRealname()!=null&& !("".equals(searchEmployee.getRealname().trim()))){
whereSql.append(" and realname like "+"'%"+searchEmployee.getRealname()+"%'");
}
if(searchEmployee.getSex()!=null&& !("".equals(searchEmployee.getSex().trim()))){
whereSql.append(" and sex='"+searchEmployee.getSex()+"'");
}
if(searchEmployee.getEdu()!=null&& !("".equals(searchEmployee.getEdu().trim()))){
whereSql.append(" and edu='"+searchEmployee.getEdu()+"'");
}
if(searchEmployee.getBeginHiredate()!=null&& searchEmployee.getEndHiredate()!=null){
whereSql.append(" and hiredate between '"+searchEmployee.getBeginHiredate()
+"'"+" and '"+searchEmployee.getEndHiredate()+"'");
}
whereSql.append(" order by id asc");
sql=sql+whereSql.toString();
//System.out.println(sql);
try {
statement=conn.createStatement();
rs = statement.executeQuery(sql);
while(rs.next()){
Employee employee=new Employee();
employee.setId(rs.getInt("id"));
employee.setUsername(rs.getString(2));
employee.setPassword(rs.getString(3));
employee.setRealname(rs.getString(4));
employee.setSex(rs.getString("sex"));
employee.setBirthday(rs.getDate(6));
employee.setEdu(rs.getString("edu"));
employee.setMajor(rs.getString("major"));
employee.setDescription(rs.getString(9));
employee.setHiredate(rs.getDate(10));
employee.setRole(rs.getString("role"));
list.add(employee);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(dbManager!=null){
dbManager.closeResource(conn, statement, rs);
}
}
return list;
}
*/
}7、TestEmployee.java
package cn.itcast.test;
import *;
public class TestDaoEmployee {
static DaoEmployee daoEmployee;
@BeforeClass public static void beforeClass(){
daoEmployee=DaoFactory.getDaoEmployee();
}
@Test public void TestFindEmployeesByCondition(){
SearchEmployee searchEmployee=new SearchEmployee();
searchEmployee.setUsername("f");
searchEmployee.setRealname("");
searchEmployee.setSex("");
searchEmployee.setEdu("");
/*searchEmployee.setBeginHiredate(java.sql.Date.valueOf("1990-01-01"));
searchEmployee.setEndHiredate(java.sql.Date.valueOf("2009-01-01"));*/
List list = daoEmployee.findEmployeesByCondition(searchEmployee);
System.out.println(list);
}
}8、listCondition.jsp
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
…….td1{
border:#00C solid 0px;
text-align:center;
width: 80px;
}
.td2{
border:#00C solid 0px;
text-align:center;
width: 120px;
}
</head>
<body>
<div id="body">
<div id="head"><h2>条件查询：</h2></div>
<div id="table">
<form action="${pageContext.request.contextPath}/servlet/ListConditionServlet" method="post">
<table>
<tr>
<td style="text-align: right">用户名：</td>
<td ><input type="text" size="40%" name="username"></td>
</tr>
<tr>
<td style="text-align: right">真实姓名：</td>
<td><input type="text" size="40%" name="realname"></td>
</tr>
<tr>
<td style="text-align: right">性别：</td>
<td class="td1">
<input type=radio name="sex" value="男">男
<input type=radio name="sex" value="女">女
</td>
</tr>
<tr>
<td style="text-align: right">开始雇用日期：</td>
<td>
<input type="text" id="time1" name="beginhiredate" value="1990-09-09">
<input type="button" onClick="MyCalendar.SetDate(this,document.getElementById('time1'))" value="选择"/>
</td>
</tr>
<tr>
<td style="text-align: right">结束雇用日期：</td>
<td>
<input type="text" id="time2" name="endhiredate" value="2009-09-01">
<input type="button" onClick="MyCalendar.SetDate(this,document.getElementById('time2'))" value="选择"/>
</td>
</tr>
<tr>
<td style="text-align: right">学历：</td>
<td>
<select name="edu">
<option value="" selected>请选择
<option value="博士">博士
<option value="硕士">硕士
<option value="本科">本科
<option value="大专">大专
<option value="中专">中专
<option value="高中">高中
</select>
</td>
</tr>
<tr>
<td>
<div id="que">
<input type="submit" size="10%" value="提交">
</div>
</td>
<td>
<div id="chong">
<input type="reset" size="10%" value="重置">
</div>
</td>
</tr>
</table>
</form>
</div>
<div id="resulttitle">
<h4>查询结果为：</h4>
</div>
<div id="table">
<c:choose>
<c:when test="${!empty list}">
<table border="1">
<tr>
<td><div class="td1">编号</div></td>
<td><div class="td1">用户名</div></td>
<td><div class="td1">密码</div></td>
<td><div class="td1">真实姓名</div></td>
<td><div class="td1">性别</div></td>
<td><div class="td1">出生日期</div></td>
<td><div class="td1">学历</div></td>
<td><div class="td1">专业</div></td>
<td><div class="td2">职位描述</div></td>
<td><div class="td2">入职时间</div></td>
<td><div class="td1">所属角色</div></td>
<td><div class="td2" colspan="3">操作</div></td>
</tr>
<c:forEach items="${list}" var="employee">
<tr>
<td>${employee.id}</td>
<td>${employee.username}</td>
<td>${employee.password}</td>
<td>${employee.realname}</td>
<td>${employee.sex}</td>
<td>${employee.birthday}</td>
<td>${employee.edu}</td>
<td>${employee.major}</td>
<td>${employee.description}</td>
<td>${employee.hiredate}</td>
<td>${employee.role}</td>
<td>
<a href="">查看</a>
<a href="${pageContext.request.contextPath}/servlet/EditServlet?id=${employee.id}">编辑</a>
<a href="">删除</a>
</td>
</tr>
</c:forEach>
</table>
</c:when>
<c:otherwise>
没有您要查询的数据！！！
</c:otherwise>
</c:choose>
……
扫一扫
659
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
