HTML页面代码
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>project</title>
<script src="js/jquery-3.7.1.min.js"></script>
<script>
$(function(){
$.ajax({
url:"StudentServlet",
type:"get",
success:function(value){
var arr =value.data
for(var i=0;i<arr.length;i++){
$("tbody").append("<tr>"+
"<td>"+arr[i].id+"</td>"+
"<td>"+arr[i].name+"</td>"+
"<td>"+arr[i].sex+"</td>"+
"<td>"+arr[i].age+"</td>"+
"<td><input type='button' value='修改' class='update' index='"+arr[i].id+"'><input type='button' value='删除' class='delete' index='"+arr[i].id+"'></td>"+
"</tr>"
)
}
},
error:function(){
alert("请求失败啦")
}
})
//添加小模块展示
$(".btn1").on("click",function(){
$(".add").css("display","block")
})
//添加
$(".a_btn").on("click",function(){
var name = $(".a_name").val();
var sex = $(".a_sex").val();
var age = $(".a_age").val();
$.ajax({
url:"AddServlet",
type:"post",
data:{
name,
sex,
age,
},
success:function(value){
alert(value)
location.reload()
},
error:function(){
alert("请求失败啦")
}
})
})
//删除
$("tbody").on("click",".delete",function(){
var id = $(this).attr("index")
$.ajax({
url:"DeleteServlet",
type:"post",
data:{
id,
},
success:function(value){
alert(value)
location.reload()
},
error:function(){
alert("请求失败啦")
}
})
})
//修改小模块展示
$("tbody").on("click",".update",function(){
$(".update_div").css("display","block")
})
//修改
$("tbody").on("click",".update",function(){
var id = $(this).attr("index")
$.ajax({
url:"SearchServlet?id="+id,
type:"get",
success:function(value){
var obj = value.data[0]
console.log(value)
console.log(obj)
$(".u_name").val(obj.name)
$(".u_sex").val(obj.sex)
$(".u_age").val(obj.age)
$(".u_upd").attr("index",id)
},
error:function(){
alert("请求失败啦")
}
})
})
$(".u_upd").on("click",function(){
var name = $(".u_name").val();
var sex = $(".u_sex").val();
var age = $(".u_age").val();
var id = $(".u_upd").attr("index")
$.ajax({
url:"UpdateServlet",
type:"post",
data:{
name,
sex,
age,
id,
},
success:function(value){
alert(value)
location.reload()
},
error:function(){
alert("请求失败啦")
}
})
})
})
</script>
<style>
.add,.update_div{
width:250px;
height:200px;
border:5px solid orange;
padding:10px;
display:none;
}
</style>
</head>
<body>
<div><input type="button" class="btn1" value='添加'></div>
<table border="1">
<thead>
<tr>
<th>id</th>
<th>姓名</th>
<th>性别</th>
<th>年龄</th>
<th>操作</th>
</tr>
</thead>
<tbody>
<!-- <tr>
<td>id</td>
<td>姓名</td>
<td>性别</td>
<td>年龄</td>
<td><input type='button' value='修改' class="update" ></td>
<td><input type='button' value='删除' class="delete" index="id"></td>
</tr> -->
</tbody>
</table>
<div class="add">
<h3>添加</h3>
姓名：<input type="text" class="a_name"> <br>
性别：<input type="text" class="a_sex"> <br>
年龄：<input type="text" class="a_age"> <br>
<input type="button" value="添加" class="a_btn">
</div>
<div class="update_div">
<h3>修改</h3>
姓名：<input type="text" class="u_name"> <br>
性别：<input type="text" class="u_sex"> <br>
年龄：<input type="text" class="u_age"> <br>
<input type="button" value="修改" class="u_upd">
</div>
</body>
</html>
StudentServlet
package com.qcby.servlet;
import java.io.IOException;
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 db.MysqlUtil;
/**
* Servlet implementation class StudentServlet
*/
@WebServlet("/StudentServlet")
public class StudentServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public StudentServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setCharacterEncoding("utf-8");
request.setCharacterEncoding("utf-8");
//设置后端给前端返回的数据为json格式
response.setContentType("text/json;charset=utf-8");
//接收参数
String sql = "select*from student";
String[] colums= {"id","name","age","sex"};
String res = MysqlUtil.getJsonBySql(sql,colums);
System.out.println(res);
//后端给前端相应内容
response.getWriter().write(res);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
}
AddServlet
package com.qcby.servlet;
import java.io.IOException;
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 db.MysqlUtil;
/**
* Servlet implementation class AddServlet
*/
@WebServlet("/AddServlet")
public class AddServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public AddServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.getWriter().append("Served at: ").append(request.getContextPath());
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.setCharacterEncoding("utf-8");
request.setCharacterEncoding("utf-8");
String name = request.getParameter("name");
String age = request.getParameter("age");
String sex = request.getParameter("sex");
String sql = "insert into student(name,age,sex) values(\""+name+"\","+age+",\""+sex+"\")";
System.out.println(sql);
int num = MysqlUtil.add(sql);
String res = "添加失败";
if(num>0) {
res="添加成功";
}
response.getWriter().write(res);
}
}
DeleteServlet
package com.qcby.servlet;
import java.io.IOException;
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 db.MysqlUtil;
/**
* Servlet implementation class DeleteServlet
*/
@WebServlet("/DeleteServlet")
public class DeleteServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public DeleteServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.getWriter().append("Served at: ").append(request.getContextPath());
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setCharacterEncoding("utf-8");
request.setCharacterEncoding("utf-8");
String id = request.getParameter("id");
String sql = "delete from student where id ="+id;
int num = MysqlUtil.del(sql);
String res = "删除失败";
if(num>0) {
res="删除成功";
}
response.getWriter().write(res);
}
}
SearchServlet
package com.qcby.servlet;
import java.io.IOException;
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 db.MysqlUtil;
/**
* Servlet implementation class SelectServlet
*/
@WebServlet("/SearchServlet")
public class SearchServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public SearchServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setCharacterEncoding("utf-8");
request.setCharacterEncoding("utf-8");
//设置后端给前端返回的数据为json格式
response.setContentType("text/json;charset=utf-8");
//接收参数
String id = request.getParameter("id");
String sql = "select *from student where id="+id;
String[] colums= {"id","name","age","sex"};
String res = MysqlUtil.getJsonBySql(sql,colums);
System.out.println(res);
//后端给前端相应内容
response.getWriter().write(res);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
UpdateServlet
package com.qcby.servlet;
import java.io.IOException;
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 db.MysqlUtil;
/**
* Servlet implementation class UpdateServlet
*/
@WebServlet("/UpdateServlet")
public class UpdateServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public UpdateServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.getWriter().append("Served at: ").append(request.getContextPath());
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.setCharacterEncoding("utf-8");
request.setCharacterEncoding("utf-8");
String name = request.getParameter("name");
String age = request.getParameter("age");
String sex = request.getParameter("sex");
String id = request.getParameter("id");
String sql="update student set name=\""+name+"\",sex=\""+sex+"\",age="+age+" where id ="+id;
int num = MysqlUtil.update(sql);
String res = "修改失败";
if(num>0) {
res="修改成功";
}
response.getWriter().write(res);
}
}
DBconnection
package db;
import java.sql.Connection;
import java.sql.DriverManager;
public class DBConnection {
public static void main(String[] args) {
}
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/qcby";
String user = "root";
String password = "2020";
public Connection conn;
public DBConnection() {
try {
Class.forName(driver);
conn = (Connection) DriverManager.getConnection(url, user, password);//
// if(!conn.isClosed())
// System.out.println("Succeeded connecting to the Database!");
} catch (Exception e) {
e.printStackTrace();
}
}
public void close() {
try {
this.conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
MysqlUtil
package db;
import java.rmi.StubNotFoundException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MysqlUtil {
public static void main(String[] args) {
// String sqlcount = "select count(*) from admin";
// String sql = "select * from admin";
// String[] colums = {"id","account","password"};
// String json = getJsonBySql(sqlcount, sql, colums);
// System.out.println(json);
String sql = "insert into student(name,age,sex) values(\"张三\",21,\"男\")";
//String sqldel = "delete from student where id=1";
add(sql);
System.out.println(add(sql));
/*String sqldel = "delete from gq_user where id=11";
System.out.println(del(sqldel));*/
/*String sqlupdate ="update gq_user set age = 30 where username=\"gs1\"";
System.out.println(update(sqlupdate));*/
/*String sql = "select count(*) from gq_user";
System.out.println(getCount(sql));*/
/*String[] col = {"id","name","age"};
String sql = "select * from t_student";
String sqlCount = "select count(*) from t_student";
String strJson = getJsonBySql(sqlCount,sql,col);
System.out.println(strJson);*/
/*String sqlcount = "select count(*) from gq_user";
String sql="select * from gq_user";
String[] col = {"username","age","id","sex","salary"};
System.out.println(getJsonBySql(sqlcount, sql, col));*/
/*String sql = "select * from t_user";
String[] column = {"id","username","password","available"};
ArrayList<String[]> strings = MysqlUtil.showUtil(sql, column);
for (String[] string : strings) {
System.out.println(Arrays.toString(string));
}*/
}
/*添加*/
public static int add(String sql) {
//System.out.println("sql语句是：" + sql);
int i=0;
//数据库连接
DBConnection db = new DBConnection();
try {
PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
i=preStmt.executeUpdate();
preStmt.close();
db.close();//关闭连接
System.out.println("数据插入成功，sql语句是：" + sql);
} catch (Exception e) {
e.printStackTrace();
}
return i;//返回影响的行数，1为执行成功;
}
/*修改数据*/
public static int update(String sql) {
int i =0;
DBConnection db = new DBConnection();
try {
PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
i = preStmt.executeUpdate();
preStmt.close();
db.close();
System.out.println("数据更新成功，sql语句是：" + sql);
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
/*删除*/
public static int del(String delstr) {
int i=0;
DBConnection db = new DBConnection();
try {
PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(delstr);
i=preStmt.executeUpdate();
//executeUpdate()返回受影响的行数
preStmt.close();
db.close();
System.out.println("数据删除成功，sql语句是：" + delstr);
} catch (SQLException e){
e.printStackTrace();
}
return i;
}
/*查询数量*/
public static int getCount(String sql) {
int sum = 0;
DBConnection db = new DBConnection();
try {
Statement stmt = (Statement) db.conn.createStatement();
ResultSet rs = (ResultSet) stmt.executeQuery(sql);
while (rs.next()) {
sum += rs.getInt(1);
}
rs.close();
db.close();
} catch (Exception e) {
// TODO: handle exception
}
return sum;
}
/**
*功能描述 查询json数据带数据总量
* @author rc
* @date 2021-03-22 10:30
* @param sqlcount 查询数量的sql
* @param sql 查询具体数据的sql
* @param colums 查询的字段
* @return java.lang.String
*/
public static String getJsonBySqlDataGrid( String sqlcount,String sql,String[] colums){
int count = getCount(sqlcount);
System.err.println("标红信息展示sql:" + sql);
ArrayList<String[]> result = new ArrayList<String[]>();
DBConnection db = new DBConnection();
try {
Statement stmt = (Statement) db.conn.createStatement();
ResultSet rs = (ResultSet) stmt.executeQuery(sql);
while(rs.next()){
String[] dataRow = new String[colums.length];
for( int i = 0; i < dataRow.length; i++ ) {
dataRow[i] = rs.getString( colums[i] );
}
result.add(dataRow);
}
rs.close();
db.close();//
} catch (SQLException e) {
e.printStackTrace();
}
return listToJsonDataGrid(result,colums,count);
}
/**
*功能描述 查询json数据
* @author rc
* @date 2021-03-22 10:30
* @param sql 查询具体数据的sql
* @param colums 查询的字段
* @return java.lang.String
*/
public static String getJsonBySql( String sql,String[] colums){
System.err.println("标红信息展示sql:" + sql);
ArrayList<String[]> result = new ArrayList<String[]>();
DBConnection db = new DBConnection();
try {
Statement stmt = (Statement) db.conn.createStatement();
ResultSet rs = (ResultSet) stmt.executeQuery(sql);
while(rs.next()){
String[] dataRow = new String[colums.length];
for( int i = 0; i < dataRow.length; i++ ) {
dataRow[i] = rs.getString( colums[i] );
}
result.add(dataRow);
}
rs.close();
db.close();//
} catch (SQLException e) {
e.printStackTrace();
}
return listToJson(result,colums);
}
public static String getnameBySql( String sql,String[] colums){
System.err.println("标红信息展示sql:" + sql);
String name=new String();;
//ArrayList<String[]> result = new ArrayList<String[]>();
DBConnection db = new DBConnection();
try {
Statement stmt = (Statement) db.conn.createStatement();
ResultSet rs = (ResultSet) stmt.executeQuery(sql);
while(rs.next()){
name= rs.getString( colums[0] );
}
rs.close();
db.close();
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("name:"+name);
return name;
}
/**
*功能描述 查询数据
* @author 郭帅
* @date 2021-03-22 10:38
* @param sql 查询具体数据的sql
* @param colums 查询的字段
* @return java.util.ArrayList<java.lang.String[]>
*/
public static ArrayList<String[]> showUtil( String sql, String[] colums){
System.out.println(sql);
ArrayList<String[]> result = new ArrayList<String[]>();
DBConnection db = new DBConnection();
try {
Statement stmt = (Statement) db.conn.createStatement();
ResultSet rs = (ResultSet) stmt.executeQuery(sql);
while(rs.next()){
String[] dataRow = new String[colums.length];
for( int i = 0; i < dataRow.length; i++ ) {
dataRow[i] = rs.getString( colums[i] );
//System.out.println(rs.getString( colums[i]));
}
result.add(dataRow);
System.out.println(result);
}
rs.close();
db.close();//
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
/**
* 转化为json数据字符串
* @param
* @return
*/
public static String listToJsonDataGrid( ArrayList<String[]> list,String[] colums,int count) {
String jsonStr = "{\"code\":0,\"msg\":\"成功了\",\"count\":"+count+",\"data\":[";
for(int i = 0; i < list.size(); i++) {
String arr = "{";
for( int j = 0; j < list.get(0).length; j++) {
if( list.get(i)[j] == null || "NULL".equals(list.get(i)[j])) {
arr += "\"" + colums[j] + "\":\"\"";
}else {
arr += "\"" + colums[j] + "\""+":" ;
arr += "\"" + list.get(i)[j].replace("\"","\\\"") + "\"";
}
if( j < list.get(0).length - 1 ) {
arr += ",";
}
}
arr += "}";
if( i < list.size() - 1 ) {
arr += ",";
}
jsonStr += arr;
}
jsonStr += "]}";
return jsonStr;
}
/**
* 转化为json数据字符串
* @param
* @return
*/
public static String listToJson( ArrayList<String[]> list,String[] colums) {
String jsonStr = "{\"code\":0,\"msg\":\"成功了\",\"data\":[";
for(int i = 0; i < list.size(); i++) {
String arr = "{";
for( int j = 0; j < list.get(0).length; j++) {
if( list.get(i)[j] == null || "NULL".equals(list.get(i)[j])) {
arr += "\"" + colums[j] + "\":\"\"";
}else {
arr += "\"" + colums[j] + "\""+":" ;
arr += "\"" + list.get(i)[j].replace("\"","\\\"") + "\"";
}
if( j < list.get(0).length - 1 ) {
arr += ",";
}
}
arr += "}";
if( i < list.size() - 1 ) {
arr += ",";
}
jsonStr += arr;
}
jsonStr += "]}";
return jsonStr;
}
}
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
