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

作者: 小小蒜头 | 来源:发表于2017-09-18 21:50 被阅读13次

通过deptno查询Emp表中雇员信息（oracle）
3、MySQL查询（控制列）
MySQL入门练习：单表查询
oracel练习题
mysql查询练习2
Oracle例题（一）
ORACLE REVIEW 1
Hive中的表操作
HIVESQL语句
sql 聚合函数

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

具体流程就是：当访问首页index.jsp时，加载框架，显示head.jsp里的内容，里面的下拉框的option值是从数据库里取出来显示的（通过List getDeptno()方法从数据库里取出deptno），当点击其中一个选项时，由jQuery传值到后台的同时页面在frame的name为main的框架里加载，在EmpListServlet获取到前台传过来的deptno的值，再通过List<Emp> findByDno(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();
    //根据部门编号查找雇员信息
    List<Emp> findByDno(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 List<Emp> findByDno(int deptno) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        List<Emp> list = 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();
    List<Emp> findByDno(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 List<Emp> findByDno(int deptno) {
        return empDao.findByDno(deptno);
    }
}

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

index.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>雇员列表</title>
</head>
<frameset rows="20%,*" frameborder="no">
    <frame name="head" src="${pageContext.request.contextPath}/DeptnoServlet">
    <frame name="main" src="">
</frameset>
</html>

head.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
    <title>头部</title>
    <script src="js/jquery-1.10.2.min.js" language="JavaScript"></script>
    <script type="text/javascript">
        $(function () {
            $("#select").change(function () {
                var deptno = $(this).val();
                $("this #no_select").attr("disabled", "disabled");
                window.parent.main.location.href = "${pageContext.request.contextPath}/EmpListServlet?deptno=" + deptno;
            });
        });
    </script>
</head>
<body style="text-align: center;">
<h1>雇员列表</h1>
<br>
部门编号：<select name="deptno" id="select" style="width: 100px;">
    <option value="0" id="no_select" selected style="color: darkgray">请选择：</option>
    <c:forEach var="dno" items="${list}">
        <option value="${dno}">${dno}</option>
    </c:forEach>
</select>
</body>
</html>

listEmp.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
    <title>雇员列表展示界面</title>
</head>
<body style="text-align: center;">
<table border="1" align="center" cellpadding="0" cellspacing="0">
    <tr>
        <td>雇员编号</td>
        <td>雇员姓名</td>
        <td>雇员工作</td>
        <td>上级编号</td>
        <td>雇用日期</td>
        <td>雇员工资</td>
        <td>雇员奖金</td>
        <td>部门编号</td>
    </tr>
    <c:forEach var="c" items="${list}">
        <tr>
            <td>${c.empno}</td>
            <td>${c.ename}</td>
            <td>${c.job}</td>
            <td>${c.mgr}</td>
            <td>${c.hireDate}</td>
            <td>${c.sal}</td>
            <td>${c.comm}</td>
            <td>${c.deptno}</td>
        </tr>
    </c:forEach>
</table>
</body>
</html>

控制层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();
        List<Emp> list = 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

通过deptno查询Emp表中雇员信息（oracle）
本项目比较适合初学者浏览。具体流程就是：当访问首页index.jsp时，加载框架，显示head.jsp里的内容，...
3、MySQL查询（控制列）
一、案例表 2.1、部门信息表：dept 2.2、员工(雇员)信息表：emp(employees) 二、查询(...
MySQL入门练习：单表查询
单表查询查询出部门编号为30的所有员工SELECT * FROM emp WHERE deptno=30; 查询...
oracel练习题
查询20部门的所有员工信息。select * from emp where deptno='20'; 查询所有工种...
mysql查询练习2
1. 查询20号部门的所有员工信息： select * from emp where deptno = 20; 2...
Oracle例题（一）
备注：以下例题均基于Oracle的scott账户中的表 1、插入一条信息至SCOTT的EMP表 2、查询首字母为"...
ORACLE REVIEW 1
Oracle Review1 基本查询 select[distinct] * from dept; 显示EMP表的...
Hive中的表操作
Hive中的表的基本操作。如下。首先创建一个雇员表和部门表雇员表create table emp(empno ...
HIVESQL语句
#1、查找部门30中员工的详细信息。 select * from emp where deptno = 30; #...
sql 聚合函数
emp 表，有14条记录，其中 deptno 有重复字段，comm 字段有的为 null： emp: enames...