使用AJAX前后端分离技术实现对MySql数据库的数据查询删除和增加等操作。
功能分析：
1.登录
2.查询信息
3.增加信息
4.删除信息
效果演示
登陆页面
列表页面
点击查询信息
实现此功能要准备三部分分别是数据库，前端和后台。
一 ： MySql数据库
数据库内容:
1.连接数据库并查询数据库里的表
2.查询登录表和信息表的内容
到此位置我们的数据库部分就完成了，下面进行前端页面。
二 ： 前端展示
在进行前端页面开始之前请注意：
引入jquery.js架包
前端目录
前端页面展示
add.html内容
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<script src="js/jquery.js"></script>
</head>
<body>
姓名:<input type="text" name="name" id="name" /><br />
性别:<input type="text" name="sex" id="sex" /><br>
住址:<input type="text" name="address" id="address" /><br />
<input type="button" value="添加" id="login" />
</body>
<script>
$(function(){
$(":button").on("click",function(){
var name = $("#name").val();
var sex = $("#sex").val();
var address = $("#address").val();
$.ajax({
url:"http://localhost:8080/MySqlAjax/add",
type:"post",
data:{
name:name,
sex:sex,
address:address
},
dataType:"text",
success:function(data){
alert("添加成功！")
window.location.href="show.html";
},
error : function() {
alert('添加失败');
}
});
});
});
</script>
</html>
del.html内容
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<script src="js/jquery.js"></script>
</head>
<body>
ID:<input type="text" name="id" id="zhi">
<input type="button" value="删除" id="delete" />
</body>
<script>
$(function(){
$(":button").on("click",function(){
var id = $("#zhi").val();
$.ajax({
url:"http://localhost:8080/MySqlAjax/del",
type:"post",
data:{
id:id
},
dataType:"text",
success:function(data){
alert("删除成功！")
window.location.href="show.html";
},
error : function() {
alert('删除失败');
}
});
});
});
</script>
</html>
login.html内容
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title></title>
<script src="js/jquery.js"></script>
</head>
<style>
#login{
width: 100px;
height: 30px;
margin: 20px 50px;
border: 2px solid greenyellow;
border-radius: 10px;
}
</style>
<body>
账号:<input type="text" name="name" id="name" /><br>
密码:<input type="password" name="pwd" id="pwd" /><br />
<input type="button" value="登陆" id="login" />
</body>
<script>
$(function(){
$(":button").on("click",function(){
var name = $("#name").val();
var pwd = $("#pwd").val();
$.ajax({
url:"http://localhost:8080/MySqlAjax/login",
type:"post",
data:{
name:name,
pwd:pwd
},
dataType:"text",
success:function(data){
if(data == "ok"){
window.location.href="show.html";
}else{
window.location.href="login.html";
}
}
});
});
});
</script>
</html>
show.html内容
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title></title>
<script src="js/jquery.js"></script>
</head>
<body>
<div>
<a>查询信息</a>
<a href="add.html">添加信息</a>
<a href="del.html">删除信息</a>
</div>
<table>
<thead>
<tr>
<td>
ID
</td>
<td>
姓名
</td>
<td>
性别
</td>
<td>
住址
</td>
</tr>
</thead>
<tbody id="tbody">
</tbody>
</table>
</body>
<script>
$(function(){
$("a").on("click",function(){
$.ajax({
url:"http://localhost:8080/MySqlAjax/select",
type:"post",
data:{
},
dataType:"json",
success:function(data){
$("#tbody").empty(); //删除匹配的元素集合中所有的子节点（就是不重复执行）
var trStr = "";
for (var i = 0; i < data.length; i++) {
trStr += "<tr><td>" + data[i].id + "</td><td>" + data[i].name + "</td><td>" + data[i].sex + "</td><td>" + data[i].address + "</td></tr>"
}
$("#tbody").show().append(trStr);
},
error : function() {
alert('请求失败');
}
});
});
});
</script>
</html>
到这里我们已经完成三分之二了，下面进行后台程序。
三 ： 后台程序
在我们的后台程序开始之前请注意：
引入mysql-connector-java-5.1.39-bin.jar架包和fastjson-1.2.62.jar架包
目录结构
程序演示
bena包
Student.java
package com.zsh.bean;
public class Student {
private int id;
private String name;
private String sex;
private String address;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Student() {
super();
// TODO Auto-generated constructor stub
}
public Student(int id, String name, String sex, String address) {
super();
this.id = id;
this.name = name;
this.sex = sex;
this.address = address;
}
}
servlet包
AddServlet.java
package com.zsh.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.zsh.util.DBUtil;
public class AddServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
String name = request.getParameter("name");
String sex = request.getParameter("sex");
String address = request.getParameter("address");
response.setContentType("text/html");
//解决跨域问题
response.setHeader("Access-Control-Allow-Origin", "*");
PrintWriter out = response.getWriter();
int count = 0;
Connection conn = DBUtil.getConn();
PreparedStatement ps = null;
String sql = "insert into student(name,sex,address) values(?,?,?)";
try {
ps = conn.prepareStatement(sql);
ps.setString(1, name);
ps.setString(2, sex);
ps.setString(3, address);
count = ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.closeConn(conn, ps, null);
}
if(count>1){
out.print("success");
}else{
out.print("error");
}
out.flush();
out.close();
}
}
DeleteServlet.java
package com.zsh.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.zsh.util.DBUtil;
public class DeleteServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
String id = request.getParameter("id");
response.setContentType("text/html");
//解决跨域问题
response.setHeader("Access-Control-Allow-Origin", "*");
PrintWriter out = response.getWriter();
int count = 0;
Connection conn = DBUtil.getConn();
PreparedStatement ps = null;
String sql = "delete from student where id = ?";
try {
ps = conn.prepareStatement(sql);
ps.setInt(1, Integer.parseInt(id));
count = ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.closeConn(conn, ps, null);
}
if(count>1){
out.print("success");
}else{
out.print("error");
}
out.flush();
out.close();
}
}
LoginServlet.java
package com.zsh.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.zsh.util.DBUtil;
public class LoginServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
String name = request.getParameter("name");
String pwd = request.getParameter("pwd");
response.setContentType("text/html");
//解决跨域问题
response.setHeader("Access-Control-Allow-Origin", "*");
PrintWriter out = response.getWriter();
System.out.println(request.getRemoteAddr()+"/"+name+":"+pwd);
boolean flag = false;
Connection conn = DBUtil.getConn();
PreparedStatement ps = null;
ResultSet rs = null;
String sql = "select * from login where name = ? and pwd = ?";
try {
ps = conn.prepareStatement(sql);
ps.setString(1, name);
ps.setString(2, pwd);
rs = ps.executeQuery();
if(rs.next()){
flag = true;
}else{
flag = false;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.closeConn(conn, ps, rs);
}
if(flag){
out.print("ok");
}else{
out.print("error");
}
out.flush();
out.close();
}
}
SelectServlet.java
package com.zsh.servlet;
import java.io.IOException;
import java.io.PrintWriter;
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 javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import com.alibaba.fastjson.JSON;
import com.zsh.bean.Student;
import com.zsh.util.DBUtil;
public class SelectServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
response.setContentType("text/html");
//解决跨域问题
response.setHeader("Access-Control-Allow-Origin", "*");
PrintWriter out = response.getWriter();
Connection conn = DBUtil.getConn();
PreparedStatement ps = null;
ResultSet rs = null;
String sql = "select * from student";
List<Student> students = new ArrayList<Student>();
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
Student student = new Student();
student.setId(rs.getInt(1));
student.setName(rs.getString(2));
student.setSex(rs.getString(3));
student.setAddress(rs.getString(4));
students.add(student);
}
String json = JSON.toJSONString(students);
out.print(json);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.closeConn(conn, ps, rs);
}
out.flush();
out.close();
}
}
util包
DBUtil.java
package com.zsh.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBUtil {
public static Connection getConn(){
Connection conn = null;
String url = "jdbc:mysql://localhost:3306/school";
String user = "root";
String pwd = "111";
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, pwd);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public static void closeConn(Connection conn, PreparedStatement ps, ResultSet rs){
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
路径设置
web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5"
xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
<display-name></display-name>
<servlet>
<description>This is the description of my J2EE component</description>
<display-name>This is the display name of my J2EE component</display-name>
<servlet-name>LoginServlet</servlet-name>
<servlet-class>com.zsh.servlet.LoginServlet</servlet-class>
</servlet>
<servlet>
<description>This is the description of my J2EE component</description>
<display-name>This is the display name of my J2EE component</display-name>
<servlet-name>SelectServlet</servlet-name>
<servlet-class>com.zsh.servlet.SelectServlet</servlet-class>
</servlet>
<servlet>
<description>This is the description of my J2EE component</description>
<display-name>This is the display name of my J2EE component</display-name>
<servlet-name>AddServlet</servlet-name>
<servlet-class>com.zsh.servlet.AddServlet</servlet-class>
</servlet>
<servlet>
<description>This is the description of my J2EE component</description>
<display-name>This is the display name of my J2EE component</display-name>
<servlet-name>DeleteServlet</servlet-name>
<servlet-class>com.zsh.servlet.DeleteServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>LoginServlet</servlet-name>
<url-pattern>/login</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>SelectServlet</servlet-name>
<url-pattern>/select</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>AddServlet</servlet-name>
<url-pattern>/add</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>DeleteServlet</servlet-name>
<url-pattern>/del</url-pattern>
</servlet-mapping>
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
</web-app>
到此为止完整程序所有功能都已经实现。
扫一扫关注我的公众号获取更多资讯哟！！！
1032
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
