根据条件动态查询 和 批量修改
Emp
package domain;
import java.sql.Date;
public class Emp {
private Integer empno;
private String ename;
private String job;
private Integer mgr;
private Date hiredate;
private Float sal;
private Float comm;
private Integer deptno;
public Emp() {
}
public Emp(Integer empno, String ename, String job, Integer mgr, Date hiredate, Float sal, Float comm, Integer 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;
}
@Override
public String toString() {
return "Emp{" +
"empno=" + empno +
", ename='" + ename + '\'' +
", job='" + job + '\'' +
", mgr=" + mgr +
", hiredate=" + hiredate +
", sal=" + sal +
", comm=" + comm +
", 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 getHiredate() {
return hiredate;
}
public void setHiredate(Date hiredate) {
this.hiredate = hiredate;
}
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;
}
}
EmpDao
package dao;
import domain.Emp;
import org.apache.ibatis.session.SqlSession;
import util.MyUtil;
import java.lang.reflect.Constructor;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;
@SuppressWarnings("all")
public class EmpDao {
//一个SqlSession对象作为属性 支持dao中好多方法的执行
private SqlSession sqlSession = MyUtil.getSqlSession(true);
//设计一个方法 根据service层提供的条件查询emp对象(基于原生JDBC)
public List<Emp> selectEmp(String deptno, String job){
//设置一个返回值容器 用来存放查询出来的所有emp对象
List<Emp> empList = new ArrayList();
//为了以后将参数和SQL都交给pstat对象来处理
//将参数包装在一个容器里 集合(有序) 对象
Map<String,Object> params = new LinkedHashMap<>();
//一条基本的SQL语句 利用StringBuilder拼接 提升性能
String sql = "select * from emp where 1=1 ";
//需要自己动态的拼接SQL语句
if(deptno!=null && !"".equals(deptno)){
sql += "and deptno = ? ";
params.put("deptno",deptno);
}
if(job!=null && !"".equals(job)){
sql += "and job = ? ";
params.put("job",job);
}
System.out.println(sql);
System.out.println(params);
try {
//JDBC流程
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/testmybatis?useSSL=false";
String username = "root";
String password = "123456";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url,username,password);
PreparedStatement pstat = conn.prepareStatement(sql);
//将SQL和参数信息拼接完整才能执行 参数赋值
//循环params集合内的每一个元素 对应问号信息
int index = 1;//控制问号的位置(第一个开始)
Iterator it = params.keySet().iterator();
while(it.hasNext()){
Object value = params.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.setMgr(rs.getInt("mgr"));
emp.setHiredate(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;
}
//设计一个方法 根据service层提供的条件查询emp对象(基于MyBatis框架)
public List<Emp> selectEmpByMybatis(Map<String,Object> params){
//1.找寻SqlSession对象(属性)
//2.让他帮我们去查询 sqlSession.selectList("sqlid",参数);
System.out.println("这是MyBatisDao提供的支持");
return sqlSession.selectList("selectEmpByMybatis",params);
}
//设计一个方法 根据service层提供的多个deptno 进行emp数据的批量更新(基于原生JDBC)
public void updateEmp(String[] empnos){
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/testmybatis?useSSL=false";
String username = "root";
String password = "123456";
StringBuilder sql = new StringBuilder("update emp set job = 'MANAGER' where empno in ");
sql.append("(");
for(int i=0;i<empnos.length;i++){
sql.append("?,");
}
sql.delete(sql.length()-1,sql.length());
sql.append(")");
try {
Class.forName(driver);
Connection conn = DriverManager.getConnection(url,username,password);
PreparedStatement pstat = conn.prepareStatement(sql.toString());
//需要给sql的问号赋值
for(int i=0;i<empnos.length;i++){
pstat.setObject(i+1,empnos[i]);
}
pstat.executeUpdate();
pstat.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//设计一个方法 根据service层提供的多个deptno 进行emp数据的批量更新(基于MyBatis框架)
public void updateEmpByMybatis(String[] empnos){
System.out.println("mybatis提供的支持");
sqlSession.update("updateEmpByMybatis",Arrays.asList(empnos));
}
//设计两个方法 分别查询所有的deptno 和 所有的job
public List<Integer> selectAllDeptno(){
List<Integer> deptnoList = new ArrayList();
try {
//原生JDBC
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/testmybatis?useSSL=false";
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"));
}
rs.close();
pstat.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
return deptnoList;
}
public List<String> selectAllJob(){
List<String> jobList = new ArrayList();
try {
//原生JDBC
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/testmybatis?useSSL=false";
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"));
}
rs.close();
pstat.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
return jobList;
}
//设计一个方法 查询全部的emp
public List<Emp> selectAllEmp(){
return sqlSession.selectList("selectAllEmp");
}
}
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="selectEmpByMybatis" resultType="domain.Emp">
select * from emp
<where>
<if test="deptno!=null and deptno!='' "> <--当查询条件deptno不为空时-->
and deptno = #{deptno}
</if>
<if test="job!=null and job!='' ">
and job = #{job}
</if>
</where>
order by sal
<choose>
<when test=" orderFlag=='desc' "> <--注意这里是双等号==-->
${orderFlag}
</when>
<when test=" orderFlag=='asc' ">
asc
</when>
</choose>
</select>
<update id="updateEmpByMybatis"> <!--将员工编号为7301,7403,7213的工作都修改为SALESMAN-->
update emp set job = 'SALESMAN' where empno in
<foreach collection="list" item="empno" index="i" open="(" close=")" separator=",">
#{empno}
</foreach>
</update>
<!--业务方法的支持-->
<select id="selectAllEmp" resultType="domain.Emp">
select * from emp
</select>
</mapper>
index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<script type="text/javascript">
window.onload = function(){
//获取下拉列表对象
var deptnoSelectObject = document.getElementById("deptnoSelect");
var jobSelectObject = document.getElementById("jobSelect");
var orderSelectObject = document.getElementById("orderSelect");
//获取AJAX对象
var xhr = new XMLHttpRequest();
//发送异步请求 分别查询deptno(3)和job(5)
xhr.open("post","selectDeptnoAndJob",true);//打开一个AJAX通道 模拟一个请求
xhr.onreadystatechange = function() {
if(xhr.readyState==4 && xhr.status==200){
//处理响应信息 展示在下拉列表上面
var json = JSON.parse(xhr.responseText);
//获取json当中的两个集合 deptnoList jobList
var deptnoList = json.deptnoList;
//将deptnoList集合中的信息取出来 放在下拉列表内 需要option组件包含
for(var i=0;i<deptnoList.length;i++){
var newOptionObject = document.createElement("option");
newOptionObject.value = deptnoList[i];
newOptionObject.innerHTML = deptnoList[i];
deptnoSelectObject.appendChild(newOptionObject);
}
//获取jobList
var jobList = json.jobList;
//将jobList中的信息取出来 放在下拉列表内 需要option组件包含
for(var i=0;i<jobList.length;i++){
var newOptionObject = document.createElement("option");
newOptionObject.value = jobList[i];
newOptionObject.innerHTML = jobList[i];
jobSelectObject.appendChild(newOptionObject);
}
}
}
xhr.send();
//=============================================================
//给查询按钮绑定一个功能
document.getElementById("selectButton").onclick = function(){
//异步请求 AJAX
xhr.open("post","selectEmp?deptno="+deptnoSelectObject.value+"&job="+jobSelectObject.value+"&orderFlag="+orderSelectObject.value,true);
xhr.onreadystatechange = function(){
if(xhr.readyState==4 && xhr.status==200){
//1.让表格的tbody部分清空
var tbody = document.getElementById("tbody");
tbody.innerHTML="";
//2.接收异步请求回来的信息
var json = JSON.parse(xhr.responseText);
var empList = json.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="orderSelect">
<option value="asc">ASC</option>
<option value="desc">DESC</option>
</select>
<input id="selectButton" type="button" name="selectButton" value="查询">
</div>
<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>
</form>
</body>
</html>
update.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<script type="text/javascript">
window.onload = function() {
//1.发送异步请求 获取全部的emp数据List<Emp> json
var xhr = new XMLHttpRequest();
xhr.open("post","selectAllEmp",true);
xhr.onreadystatechange = function() {
if(xhr.readyState==4 && xhr.status==200){
//2.展示在table的tbody内部
var json = JSON.parse(xhr.responseText);
var empList = json.empList;
var tbodyObject = document.getElementById("showEmpTBody");
for(var i=0;i<empList.length;i++){
//每一次循环代表一个emp对象---展示在表格的一行内 tr
var newTrObject = document.createElement("tr");
//每一行记录前面多一个checkbox
var inputObject = document.createElement("input");
inputObject.type="checkbox";
inputObject.name="empno";
inputObject.value=empList[i].empno;
var checkBoxTdObject = document.createElement("td");
checkBoxTdObject.appendChild(inputObject);
newTrObject.appendChild(checkBoxTdObject);
//每一个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内
tbodyObject.appendChild(newTrObject);
}
}
}
xhr.send();
//3.给updateButton按钮绑定一个事件 提交表单
document.getElementById("updateButton").onclick = function() {
var value = window.confirm("确认更新么?");
if(value){
document.getElementById("empForm").submit();
}
}
}
</script>
</head>
<body>
<form id="empForm" action="updateEmpByEmpno" method="post">
<div align="center">
<input id="updateButton" type="button" value="update">
</div>
<table id="empTable" border="1" align="center" width="80%">
<tr>
<th></th><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="showEmpTBody">
</tbody>
</table>
</form>
</body>
</html>
网友评论