Mybatis - 12.Mybatis动态SQL（1） - 《Java笔记》

实现动态展示员工信息

controller.SelectDeptnoAndJobController

package controller;
import com.alibaba.fastjson.JSONObject;
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 service = new EmpService();
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //控制层的目的是为了查询deptno和job
        List<Integer> deptnoList = service.selectAllDeptno();
        List<String> jobList = service.selectAllJob();
        //将查询到的两个集合响应回浏览器 JSON
        JSONObject json=new JSONObject();//可以理解为json是一个大容器
        json.put("deptnoList",deptnoList);
        json.put("jobList",jobList);
        //给予响应
        response.setCharacterEncoding("UTF-8");
        response.getWriter().write(json.toJSONString());
    }
}

controller.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 {
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        String deptno=req.getParameter("deptno");
        String job=req.getParameter("job");
        String order=req.getParameter("order");
        EmpService service = new EmpService();
        List<Emp> empList= service.selectEmpByMybatis(deptno,job,order);
        JSONObject json=new JSONObject();
        json.put("empList",empList);
        resp.setCharacterEncoding("UTf-8");
        resp.getWriter().write(json.toJSONString());
    }
}

dao.EmpDao

package dao;
import domain.Emp;
import org.apache.ibatis.session.SqlSession;
import util.MyUtil;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public class EmpDao {
    private SqlSession sqlSession = MyUtil.getSqlSession(true);
    //设计两个方法 分别查询所有的deptno  和  所有的job
    public List<Integer> selectAllDeptno(){
        return sqlSession.selectList("selectAllDeptno");
    }
    public List<String> selectAllJob(){
        return sqlSession.selectList("selectAllJob");
    }
    //设计一个方法 根据service层给定的两个条件查询emp对象
    public List<Emp> selectEmpByMybatis(Map<String,Object> params){
        return sqlSession.selectList("selectEmpByMybatis",params);
    }
}

domain.Emp(省略，见表格自行想象)
mapper.EmpMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.EmpDao">
    <select id="selectAllDeptno" resultType="java.lang.Integer">
        select distinct deptno from emp order by deptno
    </select>
    <select id="selectAllJob" resultType="java.lang.String">
        select distinct job from emp order by job
    </select>
    <select id="selectEmpByMybatis" resultType="domain.Emp">
        select * from emp
      <!--满足if语句中的条件则拼接上where以及if标签内包含的语句-->
        <where>
            <if test="deptno!=null and deptno!='' ">
                and deptno = #{deptno}
            </if>
            <if test="job!=null and job!='' ">
                and job = #{job}
            </if>
        </where>
        order by sal
      <!--用法与where标签类似，when和otherwise类似于if else（在otherwise中写条件相当于elseif）-->
        <choose>
            <when test="order!=null and order=='desc' ">
                ${order}
            </when>
            <otherwise>
                asc
            </otherwise>
        </choose>
    </select>
</mapper>

service.EmpService

package service;

import dao.EmpDao;
import domain.Emp;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class EmpService {
    private EmpDao empDao=new EmpDao();
    public List<Integer> selectAllDeptno(){
        return empDao.selectAllDeptno();
    }

    public List<String> selectAllJob(){
        return empDao.selectAllJob();
    }

    //提供一个业务方法 根据给定的两个信息条件 查询emp对象的结果
    public List<Emp> selectEmpByMybatis(String deptno, String job,String order){
        Map<String,Object> params = new HashMap<>();
        params.put("deptno",deptno);
        params.put("job",job);
        params.put("order",order);
        return empDao.selectEmpByMybatis(params);
    }

}

util包中类以及configuration.xml中的代码省略
index.html

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="UTF-8">
        <script type="text/javascript">
            window.onload=function () {
                //获取下拉列表对象
                var deptnoSelectEle = document.getElementById("deptnoSelect");
                var jobSelectEle = document.getElementById("jobSelect");
                var orderSelectEle = document.getElementById("orderSelect");
                var xhr=new XMLHttpRequest();
                xhr.open("post","selectDeptnoAndJob",true);
                xhr.onreadystatechange=function () {
                    if(xhr.readyState==4&&xhr.status==200){
                        //处理响应信息，展示在下拉列表上
                        var json=JSON.parse(xhr.responseText);
                        //获取json当中的两个集合
                        var deptnoList=json.deptnoList;
                        var jobList=json.jobList;
                        //将集合中的信息取出，放在下拉列表中
                        for(var i=0;i<deptnoList.length;i++){
                            var newOptionObject=document.createElement("option");
                            newOptionObject.value=deptnoList[i];
                            newOptionObject.innerHTML=deptnoList[i];
                            deptnoSelectEle.appendChild(newOptionObject);
                        }
                        for(var i=0;i<jobList.length;i++){
                            var newOptionObject=document.createElement("option");
                            newOptionObject.value=jobList[i];
                            newOptionObject.innerHTML=jobList[i];
                            jobSelectEle.appendChild(newOptionObject);
                        }

                    }
                }
                xhr.send();

                //给查询按钮绑定一个功能
                document.getElementById("selectButton").onclick=function () {
                    //异步请求AJaX
                    xhr.open("post","selectEmp?deptno="+deptnoSelectEle.value+"&job="+jobSelectEle.value+"&order="+orderSelectEle.value,true);
                    xhr.onreadystatechange=function () {
                        if(xhr.readyState==4&&xhr.status==200){
                            //清空tbody部分
                            var tbody=document.getElementById("tbody");
                            tbody.innerHTML='';
                            var empList=JSON.parse(xhr.responseText).empList;
                            for(var i=0;i<empList.length;i++){
                                //每一次循环代表一个emp对象---展示在表格的一行内  tr
                                var newTrObject = document.createElement("tr");
                                //每一个emp对象中的8个属性
                                var empTdObject = document.createElement("td");
                                empTdObject.innerHTML=empList[i].empno;
                                newTrObject.appendChild(empTdObject);

                                var enameTdObject = document.createElement("td");
                                enameTdObject.innerHTML=empList[i].ename;
                                newTrObject.appendChild(enameTdObject);

                                var jobTdObject = document.createElement("td");
                                jobTdObject.innerHTML=empList[i].job;
                                newTrObject.appendChild(jobTdObject);

                                var mgrTdObject = document.createElement("td");
                                mgrTdObject.innerHTML=empList[i].mgr;
                                newTrObject.appendChild(mgrTdObject);

                                var hiredateTdObject = document.createElement("td");
                                hiredateTdObject.innerHTML=empList[i].hiredate;
                                newTrObject.appendChild(hiredateTdObject);

                                var salTdObject = document.createElement("td");
                                salTdObject.innerHTML=empList[i].sal;
                                newTrObject.appendChild(salTdObject);

                                var commTdObject = document.createElement("td");
                                commTdObject.innerHTML=empList[i].comm;
                                newTrObject.appendChild(commTdObject);

                                var deptnoTdObject = document.createElement("td");
                                deptnoTdObject.innerHTML=empList[i].deptno;
                                newTrObject.appendChild(deptnoTdObject);

                                //将新的行对象添加在tbody内
                                tbody.appendChild(newTrObject);
                            }
                        }
                    }
                    xhr.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>
                order:
                <select id="orderSelect" name="jobSelect">
                    <option value="asc">ASC</option>
                    <option value="desc">DESC</option>
                </select>
                <input id="selectButton"type="button" name="selectButton" value="查询">
            </div>
        </form>
        <br>
        <table id="showTable" align="center" border="1" width="80%">
            <tr>
                <th>empno</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>

    </body>
</html>