描述:
通过动态sql查询员工信息和所对应的部门
涉及知识:
Mysql+JDBC+Serlvet+JAXA+JSON+HTML+JS
项目名称和包的结构
domain实体类中
package domain;
import java.util.Date;
public class Emp {
private Integer empno;
private String ename;
private String job;
private Integer mgr;
private Date hriedate;
private Float sal;
private Float comm;
private Integer deptno;
public Emp() {
}
public Emp(Integer empno, String ename, String job, Integer mgr, Date hriedate, Float sal, Float comm, Integer deptno) {
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hriedate = hriedate;
this.sal = sal;
this.comm = comm;
this.deptno = deptno;
}
public Integer getEmpno() {
return empno;
}
public void setEmpno(Integer 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 Integer getMgr() {
return mgr;
}
public void setMgr(Integer mgr) {
this.mgr = mgr;
}
public Date getHriedate() {
return hriedate;
}
public void setHriedate(Date hriedate) {
this.hriedate = hriedate;
}
public Float getSal() {
return sal;
}
public void setSal(Float sal) {
this.sal = sal;
}
public Float getComm() {
return comm;
}
public void setComm(Float comm) {
this.comm = comm;
}
public Integer getDeptno() {
return deptno;
}
public void setDeptno(Integer deptno) {
this.deptno = deptno;
}
@Override
public String toString() {
return "Emp{" +
"empno=" + empno +
", ename='" + ename + '\'' +
", job='" + job + '\'' +
", mgr=" + mgr +
", hriedate=" + hriedate +
", sal=" + sal +
", comm=" + comm +
", deptno=" + deptno +
'}';
}
}
业务层service中
package service;
import dao.EmpDao;
import domain.Emp;
import java.util.List;
public class EmpService {
private EmpDao dao=new EmpDao();
//查寻所有的deptno
public List<Integer> selectAllDeptno(){
return dao.selectAllDeptno();
}
//查寻所有的job
public List<String> selectAllJob(){
return dao.selectAllJob();
}
//根据给定的两个信息条件 查询emp对象的结果
public List<Emp> selectEmp(String deptno,String job){
return dao.selectEmp(deptno,job);
}
}
数据访问层dao中
package dao;
import domain.Emp;
import java.security.spec.ECField;
import java.sql.*;
import java.util.*;
public class EmpDao {
//查询所有的deptno
public List<Integer> selectAllDeptno(){
List<Integer> deptnoList=new ArrayList<>();
try{
String driver="com.mysql.jdbc.Driver";
String url="jdbc:mysql://localhost:3306/testmybatis?useSSL=true";
String username="root";
String password="123456";
String sql="select distinct deptno from emp order by deptno";
Class.forName(driver);
Connection conn=DriverManager.getConnection(url,username,password);
PreparedStatement pstat=conn.prepareStatement(sql);
ResultSet rs=pstat.executeQuery();
while(rs.next()){
deptnoList.add(rs.getInt("deptno"));
}
}catch (Exception e){
e.printStackTrace();
}
return deptnoList;
}
//查询所有的job
public List<String> selectAllJob(){
List<String> JobList=new ArrayList<>();
try{
String driver="com.mysql.jdbc.Driver";
String url="jdbc:mysql://localhost:3306/testmybatis?useSSL=true";
String username="root";
String password="123456";
String sql="select distinct job from emp order by job";
Class.forName(driver);
Connection conn=DriverManager.getConnection(url,username,password);
PreparedStatement pstat=conn.prepareStatement(sql);
ResultSet rs=pstat.executeQuery();
while(rs.next()){
JobList.add(rs.getString("job"));
}
}catch (Exception e){
e.printStackTrace();
}
return JobList;
}
public List<Emp> selectEmp(String deptno,String job){
List<Emp> empList=new ArrayList<>();
//为了以后将参数和sql都交给pstat对象来处理 将参数包装在一个容器中
Map<String,Object> paramsList=new LinkedHashMap<String,Object>();
String sql="select * from emp where 1=1";
if(deptno!=null&&!"".equals(deptno)){
sql+=" and deptno=#{deptno}";
paramsList.put("deptno",deptno);
}
if(job!=null&&!"".equals(job)){
sql+=" and job=#{job}";
paramsList.put("job",job);
}
System.out.println(sql+"拼接的sql");
try{
String driver="com.mysql.jdbc.Driver";
String url="jdbc:mysql://localhost:3306/testmybatis?useSSL=true";
String username="root";
String password="123456";
Class.forName(driver);
Connection conn=DriverManager.getConnection(url,username,password);
PreparedStatement pstat=conn.prepareStatement(sql);
//给参数赋值
int index=1;//控制问号的位置
Iterator it=paramsList.keySet().iterator();
while(it.hasNext()){
Object value=paramsList.get(it.next());
pstat.setObject(index++,value);
}
//执行结果集
ResultSet rs=pstat.executeQuery();
while(rs.next()){
Emp emp=new Emp();
emp.setEmpno(rs.getInt("empno"));
emp.setEname(rs.getString("ename"));
emp.setJob(rs.getString("job"));
emp.setHriedate(rs.getDate("hiredate"));
emp.setSal(rs.getFloat("sal"));
emp.setComm(rs.getFloat("comm"));
emp.setDeptno(rs.getInt("deptno"));
empList.add(emp);
}
rs.close();
pstat.close();
conn.close();
}catch (Exception e){
e.printStackTrace();
}
return empList;
}
}
流程控制层controller
SelectDeptnoAndJobController类中
package controller;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.JSONPObject;
import service.EmpService;
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("/selectDeptnoAndJob")
public class SelectDeptnoAndJobController extends HttpServlet {
private EmpService es=new EmpService();
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
List<Integer> deptnoList= es.selectAllDeptno();
List<String> jobList= es.selectAllJob();
System.out.println(deptnoList+"-----");
JSONObject json=new JSONObject();
json.put("deptnoList",deptnoList);
json.put("jobList",jobList);
response.setCharacterEncoding("UTF-8");
response.getWriter().write(json.toJSONString());
}
}
SelectEmpController类中
package controller;
import com.alibaba.fastjson.JSONObject;
import domain.Emp;
import service.EmpService;
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("/selectEmp")
public class SelectEmpController extends HttpServlet {
private EmpService es=new EmpService();
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String deptno=request.getParameter("deptno");
String job=request.getParameter("job");
System.out.println(deptno+"======="+job);
List<Emp> empList=es.selectEmp(deptno,job);
//通过json将empList带走
JSONObject json=new JSONObject();
json.put("empList",empList);
//设置字符集
response.setCharacterEncoding("UTF-8");
response.getWriter().write(json.toJSONString());
}
}
html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<title>Title</title>
<script type="text/javascript">
window.onload=function () {
var deptnoSelect=document.getElementById("deptnoSelect");
var jobSelect=document.getElementById("jobSelect");
//创建ajax对象
var xml=new XMLHttpRequest();
//发送异步请求 分别查询deptno(3)和job(3)
xml.open("post","selectDeptnoAndJob",true);//打开一个AJAX通道
xml.onreadystatechange=function () {
if(xmlHttp.readyState==4&& xml.status==200){
//处理响应信息
var json=JSON.parse(xml.responseText);
//获取json当中的两个集合
var deptonList=json.deptnList;
//将deptonList中的信息取出来,放置下拉选下 需要option组件包含
for(var i=0;i<deptonList.length;i++){
var newOptionObject=document.createElement("option");
newOptionObject.value=deptonList[i];
newOptionObject.innerHTML=deptonList[i];
deptnoSelect.append(newOptionObject);
}
var jobList=json.jobList;
for(var i=0;i<jobList.length;i++){
var newOptionObject=document.createElement("option");
newOptionObject.value=jobList[i];
newOptionObject.innerHTML=jobList[i];
jobSelect.append(newOptionObject);
}
}
}
//发送
xml.send();
//给查询按钮绑定一个功能
document.getElementById("selectButton").onclick=function () {
//异步请求
xml.open("post","selectEmp?deptnn="+deptnoSelect.value+"&job="+jobSelect.value,true);
xml.onreadystatechange=function () {
if(xml.readyState==4&&xml.readyState==200){
//将以前的tbody中的信息清空
var tbody=document.getElementById("tbody");
tbody.innerHTML="";
//接收json并解析
var json=JSON.parse(xml.responseText);
//获取json传过来的信息
var empList=json.empList;
for(var i=0;i<empList.length;i++){
//每一次循环代表一个对象---展示在表格的一行内tr
//创建一个tr
var newTrObject=document.createElement("tr");
//创建一个td
var empTdObject=document.createElement("td");
//将对象的empno值写入td中
empTdObject.innerHTML=empList[i].empno;
//将td添加到tr中
newTrObject.append(empTdObject);
//把emp对象中的所有属性按照上述方式添加到td
//最后将tr添加到tbody中
tbody.append(tr);
}
}
}
xml.send();
}
}
</script>
</head>
<body>
<form action="" method="post">
<div align="center">
deptno:
<select id="deptnoSelect" name="deptnoSelect">
<option value="">==请选择==</option>
</select>
job:
<select id="jobSelect" name="jobSelect">
<option value="">==请选择==</option>
</select>
<input id="selectButton" type="button" name="selectButton" value="查询">
</div>
<br>
<table id="showTable" align="center" border="1" width="80%">
<tr>
<th>emp</th>
<th>ename</th>
<th>job</th>
<th>mgr</th>
<th>hiredate</th>
<th>sal</th>
<th>comm</th>
<th>deptno</th>
</tr>
<tbody id="tbody"></tbody>
</table>
</form>
</body>
</html>