<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>学生信息</title>
<style>
.box{
width: 80%;
height:auto;
margin: 40px auto;
}
.inputBox{
width: 60%;
margin: 20px auto;
}
.tableBox{
width:80%;
margin: 0 auto;
height: auto;
}
.xiugai{ position: absolute;
top: 70px;
left: 340px;
background-color: red;
height: 400px;
width: 800px;
display: none;
text-align:center ;
}
</style>
<script src="https://apps.bdimg.com/libs/jquery/2.1.4/jquery.min.js"></script>
</head>
<body>
<div class="box">
<div class="inputBox">
学号:<input type="text" id="sno">
姓名:<input type="text" id="name">
年龄:<input type="text" id="age">
<button id="add">添加</button>
<button id="search">查询</button>
</div>
<div class="tableBox">
<table id="yi" width="900" border="1" cellspacing="0" cellpadding="10">
<tr>
<th>学号</th>
<th>姓名</th>
<th>年龄</th>
<th>操作</th>
</tr>
</table>
</div>
<div class="xiugai">
学号:<input type="text" id="sno1">
姓名:<input type="text" id="name1">
年龄:<input type="text" id="age1">
<button class="queding">确定</button>
<button class="quxiao">取消</button>
</div>
</div>
</body>
<script>
var url = "http://localhost:8080/servlet3"
//添加
$("#add").click(function () {
var para = {
method:"add",
name:$("#name").val(),
age:$("#age").val(),
sno:$("#sno").val(),
};
$.ajax({//传入参数,传入亲求地址,请求方法
url: url,
type: "GET",
data: para,
success:function (res) {//回调函数
console.log(res);
//改变页面的信息
var resu =JSON.parse(res);
console.log(resu);
var sno = resu.data.sno;
var name = resu.data.name;
var age = resu.data.age;
$("table").append("<tr class='er'> + <td>"+sno+"</td><td>"+name+"</td><td>"+age+"</td><td><button class='change'>删除</button><button class='chang'>修改</button></td></tr>")
},
error:function (err) {
console.log(err)
}
})
});
//修改
var stu1;
var se;
$("table").on("click",".chang",function () {
stu1 = $(this).parent().parent().find("td:first").text();
se = $(this);
$(".xiugai").show();
$("#sno1").val($(this).parent().parent().children().eq(0).text());
$("#name1").val($(this).parent().parent().children().eq(1).text());
$("#age1").val($(this).parent().parent().children().eq(2).text());
$(".queding").click(function () {
$.ajax({
url:url,
type:"GET",
data:{
method:"update",
stu1:stu1,
sno1:$("#sno1").val(),
name1:$("#name1").val(),
age1:$("#age1").val(),
},
dataType:"json",
success:function (res) {
se.parent().parent().find("td:first").text($("#sno1").val());
se.parent().parent().find("td:nth-child(2)").text($("#name1").val());
se.parent().parent().find("td:nth-child(3)").text($("#age1").val());
},
error:function (err) {
console.log(err);
alert(err.msg);
}
});
});
$(".quxiao").click(function () {
$(".xiugai").hide();
})
});
//删除
$("table").on("click", ".change",function () {
var tr1 = $(this).parent().parent();
var para = {
method: "delete",
sno:$(this).parent().parent().children().eq(0).text(),
};
$.ajax(
{
url: url,
type: "GET",
data: para,
success: function (result) {
tr1.remove();
alert("删除成功")
},
error: function (error) {
console.log(error);
}
})
})
$("#search").click(function () {
var para = {
method:"showStudent"
};
$.ajax({//传入参数,传入亲求地址,请求方法
url: url,
type: "GET",
data: para,
success:function (res) {//回调函数
console.log(res);
var res =JSON.parse(res);
//改变页面的信息
for (var i=0;i<res.data.length;i++){
var sno = res.data[i].sno;
var name = res.data[i].name;
var age = res.data[i].age;
$("#yi").append("<tr class='er'> + <td>"+sno+"</td><td>"+name+"</td><td>"+age+"</td><td><button class='change'>删除</button><button class='chang'>修改</button></td></tr>")
}
},
error:function (err) {
console.log(err)
}
})
})
</script>
</html>
public class User {
public static int update(Student student,String stu1){
int row =0;
Connection connection = JDBCUnit.getConnection();
String sql = "UPDATE stuenlt1 SET sno=?,name=?,age=? WHERE sno=?";
PreparedStatement ps = null;
try {
ps = connection.prepareStatement(sql);
ps.setString(1,student.getSno());
ps.setString(2,student.getName());
ps.setInt(3,student.getAge());
ps.setString(4,stu1);
row = ps.executeUpdate();
JDBCUnit.releaseResource(connection,ps);
} catch (SQLException e) {
e.printStackTrace();
}
return row;
}
public static int cancel(String sno){
int row =0;
//1.注册数据库驱动
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//2.和数据库建立连接
String url = "jdbc:mysql://localhost:3306/student";
String name1 = "root";
String password = "123";
Connection connection = null;
try {
connection = DriverManager.getConnection(url,name1,password);
} catch (SQLException e) {
e.printStackTrace();
}
String sql ="DELETE FROM stuenlt1 WHERE sno=?";
PreparedStatement ps;
try {
ps = connection.prepareStatement(sql);
ps.setString(1,sno);
row = ps.executeUpdate();
JDBCUnit.releaseResource(connection,ps);
} catch (SQLException e) {
e.printStackTrace();
}
return row;
}
public static int insert(Student student) {
int row =0;
//1.注册数据库驱动
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//2.和数据库建立连接
String url = "jdbc:mysql://localhost:3306/student";
String name1 = "root";
String password = "123";
Connection connection = null;
try {
connection = DriverManager.getConnection(url,name1,password);
} catch (SQLException e) {
e.printStackTrace();
}
String sql = "insert into stuenlt1(sno,name,age) values(?,?,?)";
PreparedStatement ps;
try {
ps = connection.prepareStatement(sql);
ps.setString(1,student.getSno());
ps.setString(2,student.getName());
ps.setInt(3,student.getAge());
row = ps.executeUpdate();
JDBCUnit.releaseResource(connection,ps);
} catch (SQLException e) {
e.printStackTrace();
}
return row;
}
public static LinkedList<Student> showAll() {
int row = 0;
//1.注册数据库驱动
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//2.和数据库建立连接
String url = "jdbc:mysql://localhost:3306/student";
String name1 = "root";
String password = "123";
Connection connection = null;
try {
connection = DriverManager.getConnection(url,name1,password);
} catch (SQLException e) {
e.printStackTrace();
}
//3.获取执行SQL语句对象
LinkedList<Student> linkedLists = new LinkedList<Student>();
try{
Statement statement = connection.createStatement();
String sql = "SELECT * FROM stuenlt1";
ResultSet result = statement.executeQuery(sql);
while (result.next()){
Student student = new Student();
student.setSno(result.getString("sno"));
student.setName(result.getString("name"));
student.setAge(result.getInt("age"));
System.out.println(student.toString());
linkedLists.add(student);
}
connection.close();
statement.close();
result.close();
}catch(SQLException e){
e.printStackTrace();
}
return linkedLists;
}
}
@WebServlet(name = "servlet.Servlet3",urlPatterns = "/servlet3")
public class Servlet3 extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request,response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html;charset=utf-8");
String str = request.getParameter("method");
System.out.println(str);
System.out.println("调用");
switch(str) {
case "login":
login(request, response);
break;
case "add":
add(request, response);
break;
case"showStudent":
showStudent(request,response);
break;
case "delete":
delete(request,response);
break;
case "update":
try {
update(request,response);
} catch (SQLException e) {
e.printStackTrace();
}
break;
}
}
private void update(HttpServletRequest request, HttpServletResponse response) throws SQLException, IOException {
String sno = request.getParameter("sno1");
String name = request.getParameter("name1");
int age = Integer.parseInt(request.getParameter("age1"));
String stu1 = request.getParameter("stu1");
Student student = new Student();
student.setSno(sno);
student.setName(name);
student.setAge(age);
int row = User.update(student,stu1);
//相当于JSON,将字符串转换为对象
JSONObject jsonObject = new JSONObject();
if (row==1){
jsonObject.put("code",1);
jsonObject.put("msg","修改成功");
}else {
jsonObject.put("code",0);
jsonObject.put("msg","修改失败");
}
PrintWriter out = response.getWriter();
out.write(jsonObject.toJSONString());
out.flush();
out.close();
}
private void delete(HttpServletRequest request, HttpServletResponse response) throws IOException {
String sno = request.getParameter("sno");
int row = User.cancel(sno);
JSONObject jsonObject = new JSONObject();
if (row == 1) {
jsonObject.put("code", 1);
jsonObject.put("msg", "删除成功");
} else {
jsonObject.put("code", 2);
jsonObject.put("msg", "删除失败");
}
PrintWriter out = response.getWriter();//获得输出流的方法
out.write(jsonObject.toJSONString());//变成字符串输出到前台
out.flush();//清除缓存
out.close();//请求成功后关
}
private void showStudent(HttpServletRequest request, HttpServletResponse response) throws IOException {
LinkedList<Student> linkedList =User.showAll();
JSONObject jsonObject=new JSONObject();
jsonObject.put("code",0);
jsonObject.put("msg","获取成功");
jsonObject.put("data",linkedList);
PrintWriter out = response.getWriter();
out.write(jsonObject.toJSONString());
out.flush();
out.close();
}
private void add(HttpServletRequest request,HttpServletResponse response) throws IOException {
/*String referer = request.getHeader("Referer");
*/
String sno = request.getParameter("sno");
String name = request.getParameter("name");
int age = Integer.parseInt(request.getParameter("age"));
Student student = new Student();
student.setSno(sno);
student.setName(name);
student.setAge(age);
int row = User.insert(student);
JSONObject jsonObject = new JSONObject();
if (row == 1) {
jsonObject.put("code", 1);
jsonObject.put("msg", "添加成功");
jsonObject.put("data", student);
} else {
jsonObject.put("code", 2);
jsonObject.put("msg", "添加失败");
}
//请求带回来的数据
/* jsonObject.put("code",1);//请求标记
jsonObject.put("msg","添加成功");//请求提示*/
PrintWriter out = response.getWriter();//获得输出流的方法
out.write(jsonObject.toJSONString());//变成字符串输出到前台
out.flush();//清除缓存
out.close();//请求成功后关闭输出流
}
public void login(HttpServletRequest request, HttpServletResponse response) {
try {
PrintWriter out = response.getWriter();
out.write("欢迎登录");
} catch (IOException e) {
e.printStackTrace();
}
}
}