oracle中雇员表查询,通过deptno查询Emp表中雇员信息（oracle）

本项目比较适合初学者浏览。

具体流程就是：当访问首页index.jsp时，加载框架，显示head.jsp里的内容，里面的下拉框的option值是从数据库里取出来显示的(通过List getDeptno()方法从数据库里取出deptno)，当点击其中一个选项时，由jQuery传值到后台的同时页面在frame的name为main的框架里加载，在EmpListServlet获取到前台传过来的deptno的值，再通过ListfindByDno(int deptno)获取到Emp对象信息的list集合，最后将请求转发给listEmp.jsp显示。

实体类Emp

package com.chinasofti.domain;

import java.util.Date;

public class Emp {

private int empno;

private String ename;

private String job;

private int mgr;

private Date hireDate;

private double sal;

private double comm;

private int deptno;

public Emp() {

super();

}

public Emp(int empno, String ename, String job, int mgr, Date hireDate, double sal, double comm, int deptno) {

this.empno = empno;

this.ename = ename;

this.job = job;

this.mgr = mgr;

this.hireDate = hireDate;

this.sal = sal;

this.comm = comm;

this.deptno = deptno;

}

public int getEmpno() {

return empno;

}

public void setEmpno(int empno) {

this.empno = empno;

}

public String getEname() {

return ename;

}

public void setEname(String ename) {

this.ename = ename;

}

public String getJob() {

return job;

}

public void setJob(String job) {

this.job = job;

}

public int getMgr() {

return mgr;

}

public void setMgr(int mgr) {

this.mgr = mgr;

}

public Date getHireDate() {

return hireDate;

}

public void setHireDate(Date hireDate) {

this.hireDate = hireDate;

}

public double getSal() {

return sal;

}

public void setSal(double sal) {

this.sal = sal;

}

public double getComm() {

return comm;

}

public void setComm(double comm) {

this.comm = comm;

}

public int getDeptno() {

return deptno;

}

public void setDeptno(int deptno) {

this.deptno = deptno;

}

@Override

public String toString() {

return "Emp{" +

"empno=" + empno +

", ename='" + ename + '\'' +

", job='" + job + '\'' +

", mgr=" + mgr +

", hireDate=" + hireDate +

", sal=" + sal +

", comm=" + comm +

", deptno=" + deptno +

'}';

}

}

EmpDao和EmpDaoImpl

package com.chinasofti.dao;

import com.chinasofti.domain.Emp;

import java.util.List;

public interface EmpDao {

//部门编号遍历

List getDeptno();

//根据部门编号查找雇员信息

ListfindByDno(int deptno);

}

package com.chinasofti.dao.impl;

import com.chinasofti.dao.EmpDao;

import com.chinasofti.domain.Emp;

import com.chinasofti.utils.JdbcUtilsSingle;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

public class EmpDaoImpl implements EmpDao {

@Override

public ListfindByDno(int deptno) {

Connection conn = null;

PreparedStatement ps = null;

ResultSet rs = null;

Listlist = new ArrayList();

try {

conn = JdbcUtilsSingle.getInstance().getConnection();

String sql = "select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where deptno=?";

ps = conn.prepareStatement(sql);

ps.setInt(1, deptno);

rs = ps.executeQuery();

while (rs.next()) {

Emp e = mappingEmp(rs);

list.add(e);

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

JdbcUtilsSingle.getInstance().release(rs, ps, conn);

}

return list;

}

private Emp mappingEmp(ResultSet rs) throws SQLException {

Emp e = new Emp();

e.setEmpno(rs.getInt("empno"));

e.setEname(rs.getString("ename"));

e.setJob(rs.getString("job"));

e.setMgr(rs.getInt("mgr"));

e.setHireDate(rs.getDate("hiredate"));

e.setSal(rs.getDouble("sal"));

e.setComm(rs.getDouble("comm"));

e.setDeptno(rs.getInt("deptno"));

return e;

}

@Override

public List getDeptno() {

Connection conn = null;

PreparedStatement ps = null;

ResultSet rs = null;

List list = new ArrayList();

try {

conn = JdbcUtilsSingle.getInstance().getConnection();

String sql = "select distinct deptno from emp";

ps = conn.prepareStatement(sql);

rs = ps.executeQuery();

while (rs.next()) {

list.add(rs.getInt("deptno"));

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

JdbcUtilsSingle.getInstance().release(rs, ps, conn);

}

return list;

}

}

service层EmpService和EmpServiceImpl

package com.chinasofti.service;

import com.chinasofti.domain.Emp;

import java.util.List;

public interface EmpService {

List getDeptno();

ListfindByDno(int deptno);

}

package com.chinasofti.service.impl;

import com.chinasofti.dao.EmpDao;

import com.chinasofti.dao.impl.EmpDaoImpl;

import com.chinasofti.domain.Emp;

import com.chinasofti.service.EmpService;

import java.util.List;

public class EmpServiceImpl implements EmpService {

private EmpDao empDao = new EmpDaoImpl();

@Override

public List getDeptno() {

return empDao.getDeptno();

}

@Override

public ListfindByDno(int deptno) {

return empDao.findByDno(deptno);

}

}

显示层index.jsp,head.jsp,listEmp.jsp

index.jsp

雇员列表

head.jsp

头部

雇员列表

部门编号：请选择：

${dno}

listEmp.jsp

雇员列表展示界面

雇员编号

雇员姓名

雇员工作

上级编号

雇用日期

雇员工资

雇员奖金

部门编号

${c.empno}

${c.ename}

${c.job}

${c.mgr}

${c.hireDate}

${c.sal}

${c.comm}

${c.deptno}

控制层DeptnoServlet和EmpListServlet

DeptnoServlet

package com.chinasofti.web;

import com.chinasofti.service.EmpService;

import com.chinasofti.service.impl.EmpServiceImpl;

import javax.servlet.ServletException;

import javax.servlet.annotation.WebServlet;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import java.io.IOException;

import java.util.List;

@WebServlet(name = "DeptnoServlet", urlPatterns = "/DeptnoServlet")

public class DeptnoServlet extends HttpServlet {

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

EmpService empService = new EmpServiceImpl();

List list = empService.getDeptno();

request.setAttribute("list", list);

request.getRequestDispatcher("/head.jsp").forward(request, response);

}

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

doGet(request, response);

}

}

EmpListServlet

package com.chinasofti.web;

import com.chinasofti.domain.Emp;

import com.chinasofti.service.EmpService;

import com.chinasofti.service.impl.EmpServiceImpl;

import javax.servlet.ServletException;

import javax.servlet.annotation.WebServlet;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import java.io.IOException;

import java.util.List;

@WebServlet(name = "EmpListServlet", urlPatterns = "/EmpListServlet")

public class EmpListServlet extends HttpServlet {

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

request.setCharacterEncoding("UTF-8");

int deptno = Integer.parseInt(request.getParameter("deptno"));

System.out.println(deptno);

EmpService empService = new EmpServiceImpl();

Listlist = empService.findByDno(deptno);

request.setAttribute("list",list);

request.getRequestDispatcher("/listEmp.jsp").forward(request,response);

}

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

doGet(request, response);

}

}

源代码：https://github.com/yvettee36/JdbcEmp