通过DAO设计模式实现雇员的添加、查询操作
设计实现 DAO 设计模式中的雇员的添加、查询操作,并通过 JSP 进行界面展示
数据库页面
项目结构
Emp.java
package test05;
import java.util.Date;
public class Emp {
private int empno;
private String ename;
private String job;
private Date hiredate;
private float sal;
public int getEmpno() {
return empno;
}
public String getEname() {
return ename;
}
public String getJob() {
return job;
}
public Date getHiredate() {
return hiredate;
}
public float getSal() {
return sal;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public void setEname(String ename) {
this.ename = ename;
}
public void setJob(String job) {
this.job = job;
}
public void setHiredate(Date hiredate) {
this.hiredate = hiredate;
}
public void setSal(float sal) {
this.sal = sal;
}
}
DataBaseConnection.java
package test05;
import java.sql.Connection;
public interface DataBaseConnection {
public Connection getConnection();
public void close() throws Exception;
}
MysqlDatabaseConnection.java
package test05;
import java.sql.*;
public class MysqlDatabaseConnection implements DataBaseConnection{
private static final String DBDRIVER = "com.mysql.cj.jdbc.Driver";
private static final String DBURL = "jdbc:mysql://localhost:3306/mldn?useUnicode=true&characterEncoding=UTF-8";
private static final String DBUSER = "root";
private static final String DBPASSWORD = "123456";
private Connection conn = null;
public MysqlDatabaseConnection() throws Exception {
try{
Class.forName(DBDRIVER);
this.conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWORD);
}catch (Exception e){
throw e;
}
}
@Override
public Connection getConnection(){
return this.conn;
}
@Override
public void close() throws Exception{
if(this.conn != null){
try{
this.conn.close();
}catch (Exception e){
throw e;
}
}
}
}
DatabaseConnectionFactory.java
package test05;
public class DatabaseConnectionFactory {
public static DataBaseConnection getDataBaseConnection() throws Exception{
return new MysqlDatabaseConnection();
}
}
IEmpDAO.java
package test05;
import test05.Emp;
import java.util.List;
public interface IEmpDAO {
public boolean doCreate(Emp emp) throws Exception;
public List<Emp> findAll(String keyWorld) throws Exception;
public Emp findByid(int empno) throws Exception;
}
EmpDAOImpl.java
package test05;
import test05.Emp;
import test05.IEmpDAO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class EmpDAOImpl implements IEmpDAO {
private Connection conn = null;
private PreparedStatement pstmt = null;
public EmpDAOImpl(Connection conn){
this.conn = conn;
}
@Override
public boolean doCreate(Emp emp) throws Exception {
boolean flag = false;
String sql = "INSERT INTO emp(empno, ename, job, hiredate, sal) VALUES (?, ?, ?, ?, ?);";
this.pstmt = this.conn.prepareStatement(sql);
this.pstmt.setInt(1, emp.getEmpno());
this.pstmt.setString(2, emp.getEname());
this.pstmt.setString(3, emp.getJob());
this.pstmt.setDate(4, new java.sql.Date(emp.getHiredate().getTime()));
this.pstmt.setFloat(5, emp.getSal());
if(this.pstmt.executeUpdate() > 0){
flag = true;
}
this.pstmt.close();
return flag;
}
@Override
public List<Emp> findAll(String keyWorld) throws Exception {
List<Emp> all = new ArrayList<Emp>();
String sql = "SELECT * FROM emp WHERE ename like ? OR job like ?";
this.pstmt = this.conn.prepareStatement(sql);
this.pstmt.setString(1, "%" + keyWorld + "%");
this.pstmt.setString(2, "%" + keyWorld + "%");
ResultSet rs = this.pstmt.executeQuery();
Emp emp = null;
while(rs.next()){
emp = new Emp();
emp.setEmpno(rs.getInt(1));
emp.setEname(rs.getString(2));
emp.setJob(rs.getString(3));
emp.setHiredate(rs.getDate(4));
emp.setSal(rs.getFloat(5));
all.add(emp);
}
this.pstmt.close();
return all;
}
@Override
public Emp findByid(int empno) throws Exception {
Emp emp = null;
String sql = "SELECT empno, ename, job, hiredate, sal FROM emp WHERE empno = ?";
this.pstmt = this.conn.prepareStatement(sql);
this.pstmt.setInt(1, empno);
ResultSet rs = this.pstmt.executeQuery();
if(rs.next()){
emp = new Emp();
emp.setEmpno(rs.getInt(1));
emp.setEname(rs.getString(2));
emp.setJob(rs.getString(3));
emp.setHiredate(rs.getDate(4));
emp.setSal(rs.getFloat(5));
}
this.pstmt.close();
return emp;
}
}
EmpDAOProxy.java
package test05;
import java.util.List;
import test05.Emp;
public class EmpDAOProxy implements IEmpDAO{
private DataBaseConnection dbc =null;
private IEmpDAO dao=null;
public EmpDAOProxy() throws Exception{
this.dbc=new MysqlDatabaseConnection();
this.dao=new EmpDAOImpl(this.dbc.getConnection());
}
@Override
public boolean doCreate(Emp emp) throws Exception {
// TODO Auto-generated method stub
boolean flag=false;
try {
if(this.dao.findByid(emp.getEmpno())==null) {
flag=this.dao.doCreate(emp);
}
}catch (Exception e) {
// TODO: handle exception
throw e;
}finally {
this.dbc.close();
}
return flag;
}
@Override
public List<Emp> findAll(String keyWorld) throws Exception {
// TODO Auto-generated method stub
List<Emp> all=null;
try {
all=this.dao.findAll(keyWorld);
}catch (Exception e) {
// TODO: handle exception
throw e;
}finally {
this.dbc.close();
}
return all;
}
@Override
public Emp findByid(int empno) throws Exception {
// TODO Auto-generated method stub
Emp emp=null;
try {
emp=this.dao.findByid(empno);
}catch (Exception e) {
// TODO: handle exception
throw e;
}finally {
this.dbc.close();
}
return emp;
}
}
DAOFactory.java
package test05;
import test05.IEmpDAO;
import test05.EmpDAOProxy;
public class DAOFactory {
public static IEmpDAO getIEmpDAOInstance() throws Exception{
// TODO Auto-generated method stub
return new EmpDAOProxy();
}
}
(插入测试类)
TestDAOInsert.java
package test05;
import test05.DAOFactory;
import test05.Emp;
public class TestDAOInsert {
public static void main(String[] args) throws Exception{
Emp emp=null;
for(int i=0;i<5;i++) {
emp=new Emp();
emp.setEmpno(i+100);
emp.setEname("张三-"+i);
emp.setJob("音效师-"+i);
emp.setHiredate(new java.util.Date());
emp.setSal(10000+i);
DAOFactory.getIEmpDAOInstance().doCreate(emp);
}
}
}
(检索测试类)
TestDAOSelect.java
package test05;
import java.util.Iterator;
import java.util.List;
import test05.DAOFactory;
import test05.Emp;
public class TestDAOSelect {
public static void main(String[] args) throws Exception{
// TODO Auto-generated method stub
List<Emp> all=DAOFactory.getIEmpDAOInstance().findAll("");
Iterator<Emp> iter=all.iterator();
while(iter.hasNext()) {
Emp emp=iter.next();
System.out.println(emp.getEmpno()+"、"+emp.getEname()+"-->"+emp.getEname());
}
}
}
(首页)
index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>首页</title>
</head>
<body>
<center>
<h1>首页</h1>
<br>
<a href="insert.jsp">添加雇员</a>
<br>
<a href="select.jsp">查询雇员</a>
</center>
</body>
</html>
(添加雇员)
insert.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>添加雇员</title>
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
%>
<form action="insert_do.jsp" method="post">
雇员编号:<input type="text" name="empno"><br>
雇员姓名:<input type="text" name="ename"><br>
雇员职位:<input type="text" name="job"><br>
雇佣日期:<input type="text" name="hiredate"><br>
基本工资:<input type="text" name="sal"><br>
<input type="submit" value="添加">
<input type="reset" value="重置">
</form>
</body>
</html>
(添加雇员操作)
insert_do.jsp
<%@page import="java.text.*"%>
<%@page import="test05.DAOFactory"%>
<%@page import="test05.DatabaseConnectionFactory" %>
<%@page import="test05.Emp" %>
<%@page import="test05.*" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>添加雇员</title>
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
Emp emp=new Emp();
emp.setEmpno(Integer.parseInt(request.getParameter("empno")));
emp.setEname(request.getParameter("ename"));
emp.setJob(request.getParameter("job"));
emp.setHiredate(new SimpleDateFormat("yyyy-MM-dd").parse(request.getParameter("hiredate")));
emp.setSal(Float.parseFloat(request.getParameter("sal")));
try{
if(DAOFactory.getIEmpDAOInstance().doCreate(emp)){
%>
<h3>雇员信息添加成功!</h3>
<%
}else{
%>
<h3>雇员信息添加失败!</h3>
<%
}
}catch(Exception e){
e.printStackTrace();
}
%>
</body>
</html>
(雇员查询)
select.jsp
<%@page import="test05.DAOFactory"%>
<%@page import="test05.DatabaseConnectionFactory" %>
<%@page import="test05.EmpDAOProxy" %>
<%@page import="test05.DataBaseConnection" %>
<%@page import="test05.EmpDAOImpl" %>
<%@page import="test05.IEmpDAO" %>
<%@page import="test05.MysqlDatabaseConnection" %>
<%@page import="test05.Emp" %>
<%@page import="java.util.*" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<title>雇员查询</title>
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
try{
String keyWord=request.getParameter("kw");
if(keyWord==null){
keyWord="";
}
List<Emp> all=DAOFactory.getIEmpDAOInstance().findAll(keyWord);
Iterator<Emp> iter=all.iterator();
%>
<center>
<form action="select.jsp" method="post">
请输入查询关键字:<input type="text" name="kw">
<input type="submit" value="查询">
</form>
<table border="1">
<tr>
<td>雇员编号</td>
<td>雇员姓名</td>
<td>雇员职位</td>
<td>雇佣日期</td>
<td>基本工资</td>
</tr>
<%
while(iter.hasNext()){
Emp emp=iter.next();
%>
<tr>
<td><%=emp.getEmpno() %></td>
<td><%=emp.getEname() %></td>
<td><%=emp.getJob() %></td>
<td><%=emp.getHiredate() %></td>
<td><%=emp.getSal() %></td>
</tr>
<%
}
%>
</table>
</center>
<%
}catch(Exception e){
e.printStackTrace();
}
%>
</body>
</html>