ajax请求实现学生信息的查找

发布于:2023-01-26 ⋅ 阅读:(16) ⋅ 点赞:(0) ⋅ 评论:(0)

1.ajax请求实现学生信息的查

实现的效果:

在这里插入图片描述
在这里插入图片描述

在这里插入图片描述
设置响应编码:resp.setContentType(“text/html;charset=utf-8”); 然后运行:
在这里插入图片描述

代码目录框架:

数据库:
在这里插入图片描述
代码:
在这里插入图片描述

代码部分:

创建数据库/表:

#创建数据库
create database 70806_db
default character set utf8mb4 #设置字符集
default collate utf8mb4_general_ci #设置排序规则 
#创建班级表
create table classInfo
(
	classId int primary key auto_increment,
	className varchar(20)
);

select * from classInfo;

insert into classInfo
(className)
values
('AAA01'),
('AAA02');

#创建学生表
create table studentInfo
(
	studentId int primary key auto_increment,
	name varchar(20) not null,
	sex char(1) not null,
	birthday date,
	province varchar(20) default '河南',
	classId int,
	foreign key (classId)
	references classInfo(classId)
);

select * from studentInfo;

insert into studentInfo
(name,sex,birthday,province,classId)
values
('张三','男','2002-01-01','湖北',1),
('李四','女','2003-01-05','河南',2),
('王五','男','2010-03-01','湖北',1),
('赵六','男','2009-01-08','河南',2),
('孙琪','女','2001-09-01','湖北',1);

BaseDAO:

package com.util;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.*;

public class BaseDAO {

	//四大金刚
	//驱动类
	private static final String DRIVER="com.mysql.cj.jdbc.Driver";
	//连接地址
	private static final String URL="jdbc:mysql://localhost:3306/70806_db?useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
	//用户名
	private static final String USER="root";
	//密码
	private static final String PASSWORD="123456";



	//获取连接
	public static Connection getConnection(){

		Connection con = null;

		try{
			//加载驱动类
			Class.forName(DRIVER);
			//获取连接
			con = DriverManager.getConnection(URL,USER,PASSWORD);
			
		}catch(Exception ex){
			ex.printStackTrace();
		}

		return con;
	}

	//关闭数据库对象
	public static void closeAll(Connection con,Statement st,ResultSet rs){
		
		if(rs!=null){
			try{
				rs.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
			
		}

		if(st!=null){

			try{
				st.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
			
		}

		if(con!=null){
			try{
				con.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
			
		}

	}


	//通用设置参数方法
	public static void setParams(PreparedStatement pst,Object[] params){

		if(params==null){
			return;
		}

		for(int i=0;i<params.length;i++){
			try{
				pst.setObject(i+1,params[i]);
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
	}


	//通用增删改
	public static int executeUpdate(String sql,Object[] params){

		Connection con = null;
		PreparedStatement pst = null;
		
		int res = -1;
		
		try{

			//获取连接
			con = getConnection();
			//创建预编译命令执行对象
			pst = con.prepareStatement(sql);
			//设置参数
			setParams(pst,params);
			//执行
			res = pst.executeUpdate();

		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeAll(con,pst,null);
		}
		
		return res;
	}

	//获取总记录数的查询:select count(*) from ..
	public static int getTotal(String sql,Object[] params){
		int total = 0;

		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		try{

			con = getConnection();
			pst = con.prepareStatement(sql);
			setParams(pst,params);
			rs = pst.executeQuery();
			//判断是否查询除了一个记录
			if(rs.next()){
				total = rs.getInt(1);
			}

		}catch (Exception ex){
			ex.printStackTrace();
		}finally {
			closeAll(con,pst,rs);
		}

		return total;
	}


	//通用查询
	public static List<Map<String,Object>> executeQuery(String sql,Object[] params) {

		List<Map<String,Object>> rows = new ArrayList<>();

		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		try{
			//获取连接	
			con = getConnection();			
			//获取命令对象
			pst = con.prepareStatement(sql);
			//设置参数
			setParams(pst,params);
			//执行查询
			rs = pst.executeQuery();

			//通过rs获取结果集的结构信息
			ResultSetMetaData rsmd =  rs.getMetaData();
			//获取结果集的列数
			int colCount = rsmd.getColumnCount();

			//遍历查询结果,并封装到List<Map>中
			while(rs.next()){
				//用Map存储当前行的各个列数据
				Map<String,Object> map = new HashMap<>();
				//循环获取每一列的信息
				for(int i=1;i<=colCount;i++){
					//获取列名(使用rsmd)
					String colName = rsmd.getColumnLabel(i);
					//获取列值(使用rs)
					Object colVal = rs.getObject(i);
					//将当前列存储到map中
					map.put(colName,colVal);								
				}
				
				//将遍历的当前行的数据存储到List中
				rows.add(map);
							
			}


		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeAll(con,pst,rs);
		}
		
		return rows;

	}

}

IStudentDAO:

package com.dao;

import java.util.List;
import java.util.Map;

public interface IStudentDAO {
    List<Map<String,Object>> listAll();

}

StudentDAOImpl :

package com.dao.impl;

import com.dao.IStudentDAO;
import com.util.BaseDAO;

import java.util.List;
import java.util.Map;

public class StudentDAOImpl implements IStudentDAO {
    @Override
    public List<Map<String, Object>> listAll() {
        String sql = "select s.studentId,s.name,s.sex,s.birthday,s.province," +
                "     c.classId,c.className " +
                "     from studentInfo s " +
                "     join classInfo c " +
                "     on s.classId = c.classId ";
        System.out.println(sql);
        return BaseDAO.executeQuery(sql,null);
    }

}

IStudentService:

package com.service;

import java.util.List;
import java.util.Map;

public interface IStudentService {
    List<Map<String,Object>> listAll();

}

StudentServiceImpl:

package com.service.impl;

import com.dao.IStudentDAO;
import com.dao.impl.StudentDAOImpl;
import com.service.IStudentService;

import java.util.List;
import java.util.Map;

public class StudentServiceImpl implements IStudentService {
    private IStudentDAO studentDAO=new StudentDAOImpl();
    @Override
    public List<Map<String, Object>> listAll() {
        return studentDAO.listAll();
    }

}

StudentServlet:

package com.servlet;

import com.alibaba.fastjson.JSONObject;
import com.service.IStudentService;
import com.service.impl.StudentServiceImpl;

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 java.io.IOException;
import java.io.PrintWriter;

@WebServlet(urlPatterns = "/StudentServlet/*")
public class StudentServlet extends HttpServlet {
    private IStudentService studentService = new StudentServiceImpl();

    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //获取请求方法
        String uri = req.getRequestURI();
        String process = uri.substring(uri.lastIndexOf("/")+1);
		
		//设置响应编码
        resp.setContentType("text/html;charset=utf-8");

        //定义处理之后返回的数据对象
        Object data = null;

        switch (process){
            case "query":
                data = this.query(req,resp);
                break;
        }

        //将数据转为json字符串
        String jsonStr = JSONObject.toJSONStringWithDateFormat(data,"yyyy-MM-dd");
        PrintWriter out = resp.getWriter(); //获取输出流对象
        out.println(jsonStr); //输出json字符串给浏览器
        out.flush(); //清空缓存
        out.close();//关闭流对象
    }

    /**
     * 执行查询
     * @param req
     * @param resp
     * @return
     */
    private Object query(HttpServletRequest req, HttpServletResponse resp) {
        return studentService.listAll();
    }
}

student.jsp:

<%--
  Created by IntelliJ IDEA.
  User: 33154
  Date: 2022/8/8
  Time: 1:07
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
    <table border="1" align="center">
        <thead>
        <tr>
            <th>学号</th>
            <th>姓名</th>
            <th>性别</th>
            <th>生日</th>
            <th>省份</th>
            <th>班级</th>
        </tr>
        </thead>
        <tbody id="stuBody">

        </tbody>
    </table>

    <!--引入jq-->
    <script type="text/javascript" src="${pageContext.request.contextPath}/assets/plugins/jq/jquery-1.12.4.min.js"></script>
    <!--定义自己的代码块-->
    <script type="text/javascript">
        //页面加载完成
        $(function (){
            //调用查询
            query();
        })

        function query(){
            $.ajax({
                url:"${pageContext.request.contextPath}/StudentServlet/query",
                type:"get",
                data:{},
                dataType:"json",
                success:function (res){
                    //输出查询的数据结果
                    console.log(res);
                    //遍历服务器返回的json数组
                    for(var i=0;i<res.length;i++){
                        var stu = res[i];
                        var tr = "";
                        tr+="<tr>";
                        tr+="<td>"+stu.studentId+"</td>";
                        tr+="<td>"+stu.name+"</td>";
                        tr+="<td>"+stu.sex+"</td>";
                        tr+="<td>"+stu.birthday+"</td>";
                        tr+="<td>"+stu.province+"</td>";
                        tr+="<td>"+stu.className+"</td>";
                        tr+="</tr>";
                        //添加到tbody中
                        $("#stuBody").append(tr);
                    }
                },
                error:function (xhr,msg){
                    alert("错误:"+msg);
                }
            });
        }
    </script>

</body>
</html>