实验八 JSP访问数据库

---

1.创建数据库

打开Navicat，创建如下数据库

2.打开Idea，连接数据库

这里使用的是mybatis框架，在src目录下创建controller，entity，mapper，util资源包，

如下：

controller用于前后端交互，mapper用于存放数据库类资源，entity用于存放实体类，util用于放工具类。

先创建学生实体类，在entity下创建java类：

package com.xxxx.entity.test8;

import java.sql.Timestamp;

public class Student {
    private Integer id;
    private String pID;
    private String no;
    private String name;
    private String sex;
    private String birthday;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getpID() {
        return pID;
    }

    public void setpID(String pID) {
        this.pID = pID;
    }

    public String getNo() {
        return no;
    }

    public void setNo(String no) {
        this.no = no;
    }

    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 getBirthday() {
        return birthday;
    }

    public void setBirthday(String birthday) {
        this.birthday = birthday;
    }
}

接着在mapper下，创建数据库连接接口和xml连接文件：

package com.xxxx.mapper;

import com.xxxx.entity.test8.Student;

import java.util.List;

public interface StudentMapper {
    List<Student> queryAll();
}

<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--
mapper标签：表示是给一个映射器接口做配置的
namespace属性：写映射器接口的全限定类名，表示给哪个映射器接口配置的
-->
<mapper namespace="com.xxxx.mapper.StudentMapper">
    <!--
    给映射器接口里每个方法，都要有一个对应的配置===statement
     select标签：用于配置查询语句
     insert标签：用于配置插入语句
     update标签：用于配置修改语句
     delete标签：用于配置删除语句

        id属性：写方法名
        resultType属性：查询结果集里每一行，要封装成什么对象，写全限定类名
    -->
    <select id="queryAll" resultType="com.xxxx.entity.test8.Student">
        select *
        from tb_student
    </select>
</mapper>

在util下创建工具类：

package com.xxxx.util;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

public class GetSqlSession {
    public static SqlSession createSqlSession() {

        SqlSessionFactory sqlSessionFactory = null;
        InputStream input = null;
        SqlSession session = null;

        try {
            String resource = "mybatis-config.xml";//根据实际情况修改

            input = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(input);
            session = sqlSessionFactory.openSession();
            return session;
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static void main(String[] args) {
        System.out.println(createSqlSession());
    }
}

在contorller创建交互类：

package com.xxxx.controller;


import com.fasterxml.jackson.databind.ObjectMapper;
import com.xxxx.entity.test8.Student;
import com.xxxx.mapper.StudentMapper;
import com.xxxx.util.GetSqlSession;
import jakarta.servlet.*;
import jakarta.servlet.http.*;
import jakarta.servlet.annotation.*;
import org.apache.ibatis.session.SqlSession;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;


@WebServlet(name = "testServlet", value = "/students")
public class testServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        SqlSession session = GetSqlSession.createSqlSession();
        assert session != null;
        StudentMapper studentMapper = session.getMapper(StudentMapper.class);
        List<Student> students = studentMapper.queryAll();

        response.setContentType("application/json");
        response.setCharacterEncoding("UTF-8");
        PrintWriter out = response.getWriter();

        ObjectMapper mapper = new ObjectMapper();
        String str = mapper.writeValueAsString(students);

        System.out.println(str);
        out.print(str);
    }
}

最后创建在web下创建html即可：

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<!DOCTYPE html>
<html>
<head>
    <title>Student Data</title>
    <style>
        table {
            border-collapse: collapse;
            width: 80%;
            margin: 20px auto;
        }

        th, td {
            border: 1px solid #ddd;
            padding: 8px;
            text-align: left;
        }

        th {
            background-color: #f2f2f2;
        }

        tr:nth-child(even) {
            background-color: #f2f2f2;
        }
    </style>
</head>
<body>
<h1>Student Data</h1>
<table>
    <thead>
    <tr>
        <th>ID</th>
        <th>pID</th>
        <th>No</th>
        <th>Name</th>
        <th>Sex</th>
        <th>Birthday</th>
    </tr>
    </thead>
    <tbody id="tableBody">
    <!-- 这里将通过JavaScript填充数据 -->
    </tbody>
</table>

<!-- 添加获取数据按钮 -->
<button id="getDataButton">获取数据</button>

<script>
    // 获取按钮元素
    const getDataButton = document.getElementById('getDataButton');
    const tableBody = document.getElementById('tableBody');

    // 添加按钮的点击事件监听器
    getDataButton.addEventListener('click', () => {
        // 使用Fetch API获取数据并填充表格
        fetch('/students')
            .then(response => response.json())
            .then(data => {
                const tableData = data; // 获取从后端返回的数据

                // 清空表格内容
                tableBody.innerHTML = '';

                <!--
                 for(let i=0;i<tableData;i++)
                {
                    const row = document.createElement('tr');
                    for(let j=0;j<6;j++)
                    {
                        const cell = document.createElement('td');
                        cell.textContent=i[j];
                        row.appendChild(cell);
                    }
                    tableBody.appendChild(row);
                }
                -->

                tableData.forEach(item => {
                    const row = document.createElement('tr');
                    const columns = ['id', 'pID', 'no', 'name', 'sex', 'birthday'];

                    columns.forEach(column => {
                        const cell = document.createElement('td');
                        cell.textContent = item[column];
                        row.appendChild(cell);
                    });

                    tableBody.appendChild(row);
                });
            })
            .catch(error => {
                console.error('Error fetching data: ', error);
            });
    });
</script>
</body>
</html>

效果图：