一.添加
1.跳转添加页面
2.获取主表数据并追加到下拉框中
3.完善表单数据成功添加并跳转到列表页面
二.删除
1.通过id实现单个删除和批量删除
2.利用delete from staff where yid in ( );语句实现单个删除与批量删除共用删除方法,减少代码冗余
三.改
1.跳转修改页面
2.获取主表数据并追加到下拉框中
3.通过id获取要修改的数据并回显的表单中
4.通过id修改数据
四.查
1.查询所有信息
案例
主表
从表
列表jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<script type="text/javascript" src="<%=request.getContextPath() %>/js/jquery-1.8.3.js"></script>
<script type="text/javascript">
$(function() {
//全选
$("#qx").click(function() {
$(":checkbox:not(:first)").attr("checked", true);
})
//全不选
$("#qbx").click(function() {
$(":checkbox:not(:first)").attr("checked", false);
})
//反选
$("#fx").click(function() {
$(":checkbox:not(:first)").each(function() {
$(this).attr("checked",!$(this).attr("checked"))
})
})
//全选或全不选
$("#chs").click(function() {
if($(this).attr("checked")){
$(":checkbox").attr("checked", true);
}else{
$(":checkbox").attr("checked", false);
}
})
//添加数据
$("#add").click(function() {
location="add.jsp";
})
//批量删除
$("#dels").click(function() {
var a="";
$(":checkbox:not(:first):checked").each(function() {
a+=","+$(this).val();
})
var yid=a.substring(1);
$.post("staff?action=del", {yid:yid}, function(i) {
if(i>0){
alert("删除成功");
location="getall.jsp";
}
}, "text")
})
})
//修改
function xg(yid) {
location="show.jsp?yid="+yid;
}
//单个删除
function del(yid) {
$.post("staff?action=del", {yid:yid}, function(i) {
if(i>0){
alert("删除成功");
location="getall.jsp";
}
}, "text")
}
</script>
</head>
<body>
<table border="1" cellspacing="0">
<tr>
<td><input type="checkbox" id="chs"></td>
<th>编号</th>
<th>姓名</th>
<th>性别</th>
<th>生日</th>
<th>爱好</th>
<th>部门</th>
<th>操作1</th>
<th>操作2</th>
</tr>
<c:if test="${list==null }">
<c:redirect url="staff?action=getall"></c:redirect>
</c:if>
<c:forEach items="${list }" var="lis" varStatus="a">
<tr>
<td><input type="checkbox" value="${lis.yid}"></td>
<td>${a.count }</td>
<td>${lis.yname }</td>
<td>${lis.sex }</td>
<td>${lis.birth }</td>
<td>${lis.hobby }</td>
<td>${lis.bname }</td>
<td><input type="button" value="修改" onclick="xg(${lis.yid})"> </td>
<td><input type="button" value="删除" onclick="del(${lis.yid})"></td>
</tr>
</c:forEach>
<tr>
<td align="center" colspan="10">
<input type="button" value="全选" id="qx">
<input type="button" value="全不选" id="qbx">
<input type="button" value="反选" id="fx">
<input type="button" value="添加" id="add">
<input type="button" value="批量删除" id="dels">
</td>
</tr>
</table>
</body>
</html>
添加jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<script type="text/javascript" src="<%=request.getContextPath() %>/js/jquery-1.8.3.js"></script>
<script type="text/javascript">
$(function() {
//获取下拉框的值并追加到下拉框
$.post("staff?action=option", function(obj) {
for ( var i in obj) {
$("[name='bname']").append("<option value='"+obj[i].bmid+"'>"+obj[i].bname+"</option>")
}
}, "json")
//从form表单获取数据并添加
$("#add").click(function() {
$.post("staff?action=add", $("form").serialize(), function(i) {
if(i>0){
alert("添加成功");
location="getall.jsp";
}
}, "text")
})
})
</script>
</head>
<body>
<form>
<input type="hidden" name="yid">
姓名: <input type="text" name="yname"><br>
性别:
<input type="radio" value="男" name="sex">男
<input type="radio" value="女" name="sex">女<br>
生日: <input type="text" name="birth"><br>
爱好:
<input type="checkbox" value="旅游" name="hobby" >旅游
<input type="checkbox" value="读书" name="hobby" >读书
<input type="checkbox" value="音乐" name="hobby" >音乐
<input type="checkbox" value="运动" name="hobby" >运动
<input type="checkbox" value="美食" name="hobby" >美食<br>
部门:
<select name="bname">
</select><br>
<input type="button" value="添加" id="add">
</form>
</body>
</html>
修改jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<script type="text/javascript" src="<%=request.getContextPath() %>/js/jquery-1.8.3.js"></script>
<script type="text/javascript">
$(function() {
var yid=${param.yid}
alert(yid);
$.post("staff?action=show", {yid:yid}, function(obj) {
var staff=obj.staff;
var lis=obj.list;
//获取下拉框
for ( var i in lis) {
$("[name='bname']").append("<option value='"+lis[i].bmid+"'>"+lis[i].bname+"</option>")
}
///回显
$("[name='yid']").val(staff.yid);
$("[name='yname']").val(staff.yname);
$("[name='sex'][value='"+staff.sex+"']").attr("checked", true);
$("[name='birth']").val(staff.birth);
//爱好
var hobbys=staff.hobby.split(",");
for ( var i in hobbys) {
$("[name='hobby'][value='"+hobbys[i]+"']").attr("checked", true);
}
//部门
$("[name='bname']").val(staff.bmid);
}, "json")
/提交修改内容
$("#change").click(function() {
$.post("staff?action=change", $("form").serialize(), function(i) {
if(i>0){
alert("修改成功");
location="getall.jsp"
}
}, "text")
})
})
</script>
</head>
<body>
<form>
<input type="hidden" name="yid">
姓名: <input type="text" name="yname"><br>
性别:
<input type="radio" value="男" name="sex">男
<input type="radio" value="女" name="sex">女<br>
生日: <input type="text" name="birth"><br>
爱好:
<input type="checkbox" value="旅游" name="hobby" >旅游
<input type="checkbox" value="读书" name="hobby" >读书
<input type="checkbox" value="音乐" name="hobby" >音乐
<input type="checkbox" value="运动" name="hobby" >运动
<input type="checkbox" value="美食" name="hobby" >美食<br>
部门:
<select name="bname">
</select><br>
<input type="button" value="修改" id="change">
</form>
</body>
</html>
表示层servlet
package control;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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 bean.Bumen;
import bean.Staff;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import servicedao.ServiceDao;
import serviceimpl.ServiceImpl;
@WebServlet("/staff")
public class StaffServlet extends HttpServlet{
ServiceDao sdao=new ServiceImpl();
@Override
protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
String action=request.getParameter("action");
System.out.println(action);
if(action.equals("getall")) {
getall(request,response);
}
if(action.equals("show")) {
show(request,response);
}
if(action.equals("change")) {
change(request,response);
}
if(action.equals("del")) {
del(request,response);
}
if(action.equals("add")) {
add(request,response);
}
if(action.equals("option")) {
option(request,response);
}
}
private void add(HttpServletRequest request, HttpServletResponse response) {
String yname=request.getParameter("yname");
String sex=request.getParameter("sex");
String birth=request.getParameter("birth");
String bmid=request.getParameter("bname");
String hobbys[]=request.getParameterValues("hobby");
String hobby="";
for (String string : hobbys) {
hobby+=","+string;
}
Staff staff=new Staff();
staff.setYname(yname);
staff.setSex(sex);
staff.setBirth(birth);
staff.setBmid(Integer.parseInt(bmid));
staff.setHobby(hobby);
int i=sdao.add(staff);
try {
response.getWriter().print(i);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//获取下拉框
private void option(HttpServletRequest request, HttpServletResponse response) {
// TODO Auto-generated method stub
List<Bumen> list=sdao.option();
JSONArray json=JSONArray.fromObject(list);
try {
response.getWriter().print(json);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//删除
private void del(HttpServletRequest request, HttpServletResponse response) {
String yid=request.getParameter("yid");
int i=sdao.del(yid);
try {
response.getWriter().print(i);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//修改
private void change(HttpServletRequest request, HttpServletResponse response) {
String yname=request.getParameter("yname");
String yid=request.getParameter("yid");
String sex=request.getParameter("sex");
String birth=request.getParameter("birth");
String bmid=request.getParameter("bname");
String hobbys[]=request.getParameterValues("hobby");
String hobby="";
for (String string : hobbys) {
hobby+=","+string;
}
Staff staff=new Staff(Integer.parseInt(yid), yname, sex, birth, hobby.substring(1),Integer.parseInt(bmid), null);
int i=sdao.change(staff);
try {
response.getWriter().print(i);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//回显
private void show(HttpServletRequest request, HttpServletResponse response) {
String yid=request.getParameter("yid");
Staff staff=sdao.show(yid);
List<Bumen> list=sdao.option();
System.out.println(list);
//将获取到的下拉框list集合,和需要回显的数据对象放入到map集合中
Map<String, Object> map=new HashMap<String, Object>();
map.put("staff",staff);
map.put("list", list);
//将map集合通过json技术转换成json对象
JSONObject json=JSONObject.fromObject(map);
try {
//将json对象传到jsp页面中的回调函数中
response.getWriter().print(json);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//获取列表信息
private void getall(HttpServletRequest request, HttpServletResponse response) {
List<Staff> list=sdao.getall();
if(!list.isEmpty()) {
request.setAttribute("list", list);
try {
request.getRequestDispatcher("getall.jsp").forward(request, response);
} catch (ServletException | IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
数据访问层
package impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import bean.Bumen;
import bean.Staff;
import dao.StaffDao;
import util.Getconn;
public class StaffImpl implements StaffDao{
//获取列表所有信息
@Override
public List<Staff> getall() {
List<Staff> list =new ArrayList<Staff>();
Connection conn=Getconn.getconn();
String sql="select a.*,b.bname from staff a , bumen b where a.bmid=b.bmid";
try {
PreparedStatement pre=conn.prepareStatement(sql);
ResultSet re=pre.executeQuery();
while(re.next()) {
Staff staff=new Staff(re.getInt(1),re.getString(2),re.getString(3),re.getString(4),re.getString(5),re.getInt(6),re.getString(7));
list.add(staff);
}
Getconn.closes(conn, pre, re);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
//回显要修改数据的值
@Override
public Staff show(String yid) {
Staff staff=null;
Connection conn=Getconn.getconn();
String sql="select * from staff where yid=?";
try {
PreparedStatement pre=conn.prepareStatement(sql);
pre.setString(1, yid);
ResultSet re=pre.executeQuery();
if(re.next()) {
staff=new Staff(re.getInt(1),re.getString(2),re.getString(3),re.getString(4),re.getString(5),re.getInt(6),null);
}
Getconn.closes(conn, pre, re);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return staff;
}
//查询部门表,获取下拉框值
@Override
public List<Bumen> option() {
List<Bumen> list =new ArrayList<Bumen>();
Connection conn=Getconn.getconn();
String sql="select * from bumen";
try {
PreparedStatement pre=conn.prepareStatement(sql);
ResultSet re=pre.executeQuery();
while(re.next()) {
Bumen bumen=new Bumen(re.getInt(1),re.getString(2));
list.add(bumen);
}
Getconn.closes(conn, pre, re);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
//修改
@Override
public int change(Staff staff) {
int i=0;
Connection conn=Getconn.getconn();
String sql="update staff set yname=?,sex=?,birth=?,hobby=?,bmid=? where yid=?";
try {
PreparedStatement pre=conn.prepareStatement(sql);
pre.setString(1, staff.getYname());
pre.setString(2, staff.getSex());
pre.setString(3, staff.getBirth());
pre.setString(4, staff.getHobby());
pre.setInt(5, staff.getBmid());
pre.setInt(6, staff.getYid());
i=pre.executeUpdate();
Getconn.closes(conn, pre);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return i;
}
//删除
@Override
public int del(String yid) {
int i=0;
Connection conn=Getconn.getconn();
String sql="delete from staff where yid in ("+yid+")";
try {
PreparedStatement pre=conn.prepareStatement(sql);
i=pre.executeUpdate();
Getconn.closes(conn, pre);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return i;
}
@Override
public int add(Staff staff) {
int i=0;
Connection conn=Getconn.getconn();
String sql="insert into staff value(null,?,?,?,?,?)";
try {
PreparedStatement pre=conn.prepareStatement(sql);
pre.setString(1, staff.getYname());
pre.setString(2, staff.getSex());
pre.setString(3, staff.getBirth());
pre.setString(4, staff.getHobby());
pre.setInt(5, staff.getBmid());
i=pre.executeUpdate();
Getconn.closes(conn, pre);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return i;
}
}
以上是两表连查的核心代码部分
小白技术,大佬勿喷