动态SQL的原生方式

描述：
通过动态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>