DBConnection.java代码：
package com.qcby.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://127.0.0.1:3306/wy2022?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=false";
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();
}
}
}
JDBC
MysqlUtil.java代码：
package com.qcby.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) {
}
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);
preStmt.executeUpdate();
preStmt.close();
db.close();//关闭连接
i = 1;
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);
preStmt.executeUpdate();
preStmt.close();
db.close();
i = 1;
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);
preStmt.executeUpdate();
preStmt.close();
db.close();
i = 1;
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;
}
public static String getJsonBySql( String sqlcount,String sql,String[] colums){
int count = getCount(sqlcount);
System.err.println("标红信息展示sql:" + sql);
ArrayList result = new ArrayList();
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,count);
}
public static ArrayList showUtil( String sql, String[] colums){
ArrayList result = new ArrayList();
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 result;
}
public static String listToJson( ArrayList 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;
}
}
前端部分
login.html代码：
Insert title here 账号：
密码：
content.html代码：
servlet部分Insert title here 账号：
密码：
LoginServlet.java代码：
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 com.qcby.db.MysqlUtil;
@WebServlet("/login")
public class LoginServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public LoginServlet() {
super();
// TODO Auto-generated constructor stub
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.getWriter().append("Served at: ").append(request.getContextPath());
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//解决中文乱码
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
response.setContentType("text/json;charset=utf-8");
//接受数据
String account = request.getParameter("account");
String password = request.getParameter("password");
//查询数据库
String sql = "select count(*) from admin where account='"+account+"' and password='"+password+"'";
int res = MysqlUtil.getCount(sql);
System.out.println(res);
//根据查询数据库的结果准备给前端返回数据
String json="{"code":0,"msg":"登录失败"}";
if (res!=0) {
json = "{"code":1,"msg":"登录成功"}";
}
//给前端响应数据
response.getWriter().write(json);
}
}
SearchServlet.java代码：
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 com.qcby.db.MysqlUtil;
@WebServlet("/search")
public class SearchServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public SearchServlet() {
super();
// TODO Auto-generated constructor stub
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//解决中文乱码
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
response.setContentType("text/json;charset=utf-8");
String sql = "select * from admin";
String sqlcount = "select count(*) from admin";
String[] colums = {"id","account","password"};
String data = MysqlUtil.getJsonBySql(sqlcount, sql, colums);
System.out.println(data);
response.getWriter().write(data);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
DeleteServlet.java代码：
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 com.qcby.db.MysqlUtil;
@WebServlet("/DeleteServlet")
public class DeleteServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public DeleteServlet() {
super();
// TODO Auto-generated constructor stub
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//解决中文乱码
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
response.setContentType("text/json;charset=utf-8");
String id = request.getParameter("id");
System.out.println(id);
String sql = "delete from admin where id="+id;
int res = MysqlUtil.del(sql);
String json = "";
if (res==0) {
json = "{"code":"2020","message":"删除失败"}";
}else {
json = "{"code":"2020","message":"删除成功"}";
}
response.getWriter().append(json);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
InsertServlet.java代码：
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 com.qcby.db.MysqlUtil;
@WebServlet("/InsertServlet")
public class InsertServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public InsertServlet() {
super();
// TODO Auto-generated constructor stub
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//解决中文乱码
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
response.setContentType("text/json;charset=utf-8");
String account = request.getParameter("account");
String password = request.getParameter("password");
System.out.println(account+" "+password);
String sql = "insert into admin(account,password) values('"+account+"','"+password+"');";
int res = MysqlUtil.add(sql);
String json = "";
if (res==0) {
json = "{"code":"2020","message":"注册失败"}";
}else {
json = "{"code":"2020","message":"注册成功"}";
}
response.getWriter().append(json);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
UpdateServlet.java代码：
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 com.qcby.db.MysqlUtil;
@WebServlet("/UpdateServlet")
public class UpdateServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public UpdateServlet() {
super();
// TODO Auto-generated constructor stub
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//解决中文乱码
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
response.setContentType("text/json;charset=utf-8");
String id = request.getParameter("id");
String account = request.getParameter("account");
String password = request.getParameter("password");
System.out.println(id+" "+account+" "+password);
String sql = "update admin set account = '"+account+"',password = '"+password+"' where id = "+id;
int res = MysqlUtil.update(sql);
String json = "";
if (res==0) {
json = "{"code":"2020","message":"修改失败"}";
}else {
json = "{"code":"2020","message":"修改成功"}";
}
response.getWriter().append(json);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
数据库部分
使用navicat创建，设置id，account，password三个数据，都设定不能为空，将id设为主键，自增
