利用jQuery,json,以及三层架构技术实现对单表数据库进行增删改查
三层建构:
1.表示层
2.业务逻辑层
3.数据访问层
具体操作如下:
表格数据
登录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 () {
$("#dl").click(function() {
$.post("student?action=logo", $("form").serialize(), function(obj) {
if(obj=="yes"){
alert("登录成功");
location="getall.jsp";
}else {
$("span").remove();
alert("登录失败");
var a=$("<span>账户密码错误,请重登录</span>");
$("form").before(a);
}
}, "text")
})
})
</script>
</head>
<body>
<form>
姓名:<input type="text" name="sname"><br>
密码:<input type="text" name="spwd"><br>
<input type="button" value="登录" id="dl">
<input type="reset" value="重置">
</form>
</body>
</html>
列表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>
<link rel="stylesheet" href="css/index_work.css" type="text/css"></link>
<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:not(:first)").attr("checked", true);
}else{
$(":checkbox:not(:first)").attr("checked", false);
}
})
//批量删除
$("#sc").click(function() {
var a="";
if(confirm("是否批量删除")){
$(":checkbox:not(:first):checked").each(function() {
a+=","+$(this).attr("value");
})
var b=a.substring(1);
$.post("student?action=del", {sid:b}, function(i) {
if(i>0){
alert("删除成功")
location="getall.jsp";
}else {
alert("删除失败");
location="getall.jsp";
}
}, "text")
}
})
$("#add").click(function() {
if(confirm("是否新增")){
location="add.jsp";
}
})
})
//单个删除
function del(sid) {
if(confirm("确认删除吗?")){
$.post("student?action=del", {sid:sid}, function(i) {
if(i>0){
alert("删除成功")
location="getall.jsp";
}else {
alert("删除失败");
location="getall.jsp";
}
}, "text")
}
}
function change(sid) {
location="show.jsp?sid="+sid;
}
</script>
</head>
<body>
<table border="1" cellspacing="0">
<tr>
<th><input type="checkbox" id="chs"></th>
<th>编号</th>
<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="/student?action=getall"></c:redirect>
</c:if>
<c:forEach items="${list }" var="lis" varStatus="a">
<tr>
<td><input type="checkbox" value="${lis.sid }"></td>
<td>${a.count }</td>
<td>${lis.sname }</td>
<td>${lis.spwd }</td>
<td>${lis.sex }</td>
<td>${lis.hobby }</td>
<td>${lis.age }</td>
<td>${lis.birth }</td>
<td><button onclick="del(${lis.sid})">删除</button></td>
<td><button onclick="change(${lis.sid})">修改</button></td>
</tr>
</c:forEach>
<tr>
<td colspan="10">
<button id="qx">全选</button>
<button id="qbx">全不选</button>
<button id="fx">反选</button>
<button id="add">新增</button>
<button id="sc">批量删除</button>
</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() {
$("#add").click(function() {
alert("aa");
$.post("student?action=add", $("form").serialize(), function(i) {
if(i>0){
alert("添加成功");
location="getall.jsp"
}else {
alert("添加失败");
location="getall.jsp"
}
}, "text")
})
})
</script>
</head>
<body>
<form>
姓名:<input type="text" name="sname"><br>
密码:<input type="text" name="spwd"><br>
性别:<input type="radio" name="sex" value="男">男
<input type="radio" name="sex" value="女">女<br>
年龄:<input type="text" name="age"><br>
爱好:
<input type="checkbox" name="hobby" value="抽烟">抽烟
<input type="checkbox" name="hobby" value="喝酒">喝酒
<input type="checkbox" name="hobby" value="烫头">烫头<br>
生日:<input type="text" name="birth"><br>
<input type="button" id="add" value="添加">
<input type="reset" value="重置">
</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 a=${param.sid };
alert(a);
$.post("student?action=show",{sid:a }, function(obj) {
$("[name='sid']").val(obj.sid);
alert("编号"+obj.sid)
$("[name='sname']").val(obj.sname);
$("[name='spwd']").val(obj.spwd);
$("[name='age']").val(obj.age);
$("[name='birth']").val(obj.birth);
//性别
$("[name='sex'][value='"+obj.sex+"']").attr("checked", true);
var hobbys=obj.hobby.split(",");
for ( var i in hobbys) {
$("[name='hobby'][value='"+hobbys[i]+"']").attr("checked", true);
}
}, "json")
//修改
$("#xg").click(function() {
alert("aa");
$.post("student?action=xg", $("form").serialize(), function(i) {
if(i>0){
alert("修改成功");
location="getall.jsp"
}else {
alert("修改失败");
location="show.jsp"
}
}, "text")
})
})
</script>
</head>
<body>
<form>
<input type="hidden" name="sid" >
姓名:<input type="text" name="sname"><br>
密码:<input type="text" name="spwd"><br>
性别:<input type="radio" name="sex" value="男">男
<input type="radio" name="sex" value="女">女<br>
年龄:<input type="text" name="age"><br>
爱好:
<input type="checkbox" name="hobby" value="抽烟">抽烟
<input type="checkbox" name="hobby" value="喝酒">喝酒
<input type="checkbox" name="hobby" value="烫头">烫头<br>
生日:<input type="text" name="birth"><br>
<input type="button" value="修改" id="xg">
<input type="reset" value="重置">
</form>
</body>
</html>
表示层servlet调用业务逻辑层service
package control;
import java.io.IOException;
import java.util.List;
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.Student;
import net.sf.json.JSONObject;
import servicdao.ServiceDao;
import serviceimpl.ServiceImpl;
@WebServlet("/student")
public class StudentServlet 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("del")) {
del(request,response);
}
if(action.equals("add")) {
add(request,response);
}
if(action.equals("logo")) {
logo(request,response);
}
if(action.equals("show")) {
show(request,response);
}
if(action.equals("xg")) {
xg(request,response);
}
}
//修改
private void xg(HttpServletRequest request, HttpServletResponse response) {
String sid=request.getParameter("sid");
String sname=request.getParameter("sname");
String spwd=request.getParameter("spwd");
String sex=request.getParameter("sex");
String age=request.getParameter("age");
String birth=request.getParameter("birth");
String hobby[]=request.getParameterValues("hobby");
String a="";
for (String string : hobby) {
a+=","+string;
}
Student student=new Student();
student.setSid(Integer.parseInt(sid) );
student.setSname(sname);
student.setSpwd(spwd);
student.setSex(sex);
student.setAge(age);
student.setHobby(a.substring(1));
student.setBirth(birth);
int i=sdao.xg(student);
try {
response.getWriter().print(i);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//回显
private void show(HttpServletRequest request, HttpServletResponse response) {
String sid=request.getParameter("sid");
Student student=sdao.show(sid);
if(student!=null) {
try {
JSONObject json=JSONObject.fromObject(student);
response.getWriter().print(json);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//登录
private void logo(HttpServletRequest request, HttpServletResponse response) {
String sname=request.getParameter("sname");
String spwd=request.getParameter("spwd");
Student student=new Student();
student.setSname(sname);
student.setSpwd(spwd);
Student student1=sdao.logo(student);
if(student1!=null) {
try {
request.getSession().setAttribute("name", student1);
response.getWriter().print("yes");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//增加
private void add(HttpServletRequest request, HttpServletResponse response) {
String sname=request.getParameter("sname");
String spwd=request.getParameter("spwd");
String sex=request.getParameter("sex");
String age=request.getParameter("age");
String birth=request.getParameter("birth");
String hobby[]=request.getParameterValues("hobby");
String a="";
for (String string : hobby) {
a+=","+string;
}
Student student=new Student();
student.setSname(sname);
student.setSpwd(spwd);
student.setSex(sex);
student.setAge(age);
student.setHobby(a.substring(1));
student.setBirth(birth);
int i=sdao.add(student);
try {
response.getWriter().print(i);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//删除
private void del(HttpServletRequest request, HttpServletResponse response) {
String sid=request.getParameter("sid");
int i=sdao.del(sid);
try {
response.getWriter().print(i);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//列表
private void getall(HttpServletRequest request, HttpServletResponse response) {
// TODO Auto-generated method stub
List<Student> 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.Student;
import dao.StudentDao;
import util.Getconn;
public class StudentImpl implements StudentDao{
//列表
@Override
public List<Student> getall() {
List<Student> list=new ArrayList<Student>();
Connection conn=Getconn.getconn();
String sql="select * from student";
try {
PreparedStatement pre=conn.prepareStatement(sql);
ResultSet re=pre.executeQuery();
while(re.next()) {
Student student=new Student(re.getInt(1),re.getString(2),re.getString(3),
re.getString(4),re.getString(5),re.getString(6),re.getString(7));
list.add(student);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
//删除
@Override
public int del(String sid) {
int i=0;
Connection conn=Getconn.getconn();
String sql="delete from student where sid in("+sid+")";
try {
PreparedStatement pre=conn.prepareStatement(sql);
i=pre.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return i;
}
//添加
@Override
public int add(Student student) {
int i=0;
Connection conn=Getconn.getconn();
String sql="insert into student value(null,?,?,?,?,?,?)";
try {
PreparedStatement pre=conn.prepareStatement(sql);
pre.setString(1, student.getSname());
pre.setString(2, student.getSpwd());
pre.setString(3, student.getSex());
pre.setString(4, student.getAge());
pre.setString(5, student.getHobby());
pre.setString(6, student.getBirth());
i=pre.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return i;
}
//登录查询
@Override
public Student logo(Student student) {
Student student1=null;
Connection conn=Getconn.getconn();
String sql="select * from student where sname=? and spwd=?";
try {
PreparedStatement pre=conn.prepareStatement(sql);
pre.setString(1, student.getSname());
pre.setString(2, student.getSpwd());
ResultSet re=pre.executeQuery();
if(re.next()) {
student1=new Student(re.getInt(1),re.getString(2),re.getString(3),
re.getString(4),re.getString(5),re.getString(6),re.getString(7));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return student1;
}
//回显查询
@Override
public Student show(String sid) {
Student student=null;
Connection conn=Getconn.getconn();
String sql="select * from student where sid=?";
try {
PreparedStatement pre=conn.prepareStatement(sql);
pre.setString(1, sid);
ResultSet re=pre.executeQuery();
if(re.next()) {
student=new Student(re.getInt(1),re.getString(2),re.getString(3),
re.getString(4),re.getString(5),re.getString(6),re.getString(7));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return student;
}
//修改
@Override
public int xg(Student student) {
int i=0;
Connection conn=Getconn.getconn();
String sql="update student set sname=?,spwd=?,sex=?,age=?,hobby=?,birth=? where sid=?";
try {
PreparedStatement pre=conn.prepareStatement(sql);
pre.setString(1, student.getSname());
pre.setString(2, student.getSpwd());
pre.setString(3, student.getSex());
pre.setString(4, student.getAge());
pre.setString(5, student.getHobby());
pre.setString(6, student.getBirth());
pre.setInt(7, student.getSid());
i=pre.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return i;
}
}
其中修改需要两步:
1.对需要修改的数据进行回显到客户端页面
2.对回显的数据进行修改
以上是三层架构的单表增删改查主要代码部分
本博客相当于博主的笔记,主要是为了自己本人在学习工作中方便查询,不接受任何反驳