基于MyBatis实现jsp开发访问数据库查询所有数据

基于Maven，使用MyBatis实现jsp开发，连接数据库并进行访问操作，查询数据库中表数据等

项目结构

image.png

StudentMapper

package com.javaweb.mapper;

import com.javaweb.pojo.Student;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface StudentMapper {


    /**
     * 查询所有
     *
     * @return
     */
    @Select("select * from tb_student")
    @ResultMap("studentResultMap")
    List<Student> selectAll();
    /*
    @Insert("insert into tb_student values(#{stuNo},#{stuName},#{stuSex})")
    void add(Student student);



     */
}

Student

package com.javaweb.pojo;

/*
 * 学生实体类
 */
public class Student {
    private String stuNo;
    private String stuName;
    private String stuSex;

    public Student() {
    }

    public Student(String stuNo, String stuName, String stuSex) {
        this.stuNo = stuNo;
        this.stuName = stuName;
        this.stuSex = stuSex;
    }

    public String getStuNo() {
        return stuNo;
    }

    public void setStuNo(String stuNo) {
        this.stuNo = stuNo;
    }

    public String getStuName() {
        return stuName;
    }

    public void setStuName(String stuName) {
        this.stuName = stuName;
    }

    public String getStuSex() {
        return stuSex;
    }

    public void setStuSex(String stuSex) {
        this.stuSex = stuSex;
    }

    @Override
    public String toString() {
        return "Student{" +
                "stuNo='" + stuNo + '\'' +
                ", stuName='" + stuName + '\'' +
                ", stuSex='" + stuSex + '\'' +
                '}';
    }
}

StudentService

package com.javaweb.service;

import com.javaweb.mapper.StudentMapper;
import com.javaweb.pojo.Student;
import com.javaweb.util.SqlSessionFactoryUtils;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

import java.util.List;

public class StudentService {
    SqlSessionFactory factory = SqlSessionFactoryUtils.getSqlSessionFactory();

    /**
     * 查询所有
     *
     * @return
     */
    public List<Student> selectAll() {
        SqlSession sqlSession = factory.openSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> students = mapper.selectAll();
        sqlSession.close();
        return students;
    }
/*
    public void add(Student student){
        SqlSession sqlSession = factory.openSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        mapper.add();
    }

 */
}

SqlSessionFactoryUtils

package com.javaweb.util;

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

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

public class SqlSessionFactoryUtils {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        //静态代码块会随着类的加载而自动执行，且只执行一次

        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSessionFactory getSqlSessionFactory() {
        return sqlSessionFactory;
    }
}

SelectAllServlet

package com.javaweb.web;

import com.javaweb.pojo.Student;
import com.javaweb.service.StudentService;

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.util.List;

@WebServlet("/selectAllServlet")
public class SelectAllServlet extends HttpServlet {
    private StudentService service = new StudentService();

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        List<Student> students = service.selectAll();
        request.setAttribute("students", students);
        request.getRequestDispatcher("/student.jsp").forward(request, response);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doGet(request, response);
    }
}

StudentMapper

<?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="com.javaweb.mapper.StudentMapper">
    <resultMap id="studentResultMap" type="student">
        <id column="STUNO" property="stuNo"/>
        <result column="STUNAME" property="stuName"/>
        <result column="STUSEX" property="stuSex"/>
    </resultMap>
</mapper>

mybatis-config

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <typeAliases>
        <package name="com.javaweb.pojo"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql:///mybatis?useSSL=false&amp;useServerPrepStmts=true"/>
                <property name="username" value="root"/>
                <property name="password" value="ZZXQJL@0916.com"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <package name="com.javaweb.mapper"/>
    </mappers>
</configuration>

index

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<table border="3" width="600">
    <caption>数据库交互功能清单</caption>
    <tr>
        <td rowspan="2">功能列表</td>
        <td>增加（待开发）</td>
        <td>删除（待开发）</td>
    </tr>
    <tr>
        <td>修改（待开发）</td>
        <td>
            <a href="/Test07/selectAllServlet">查询</a>
        </td>
    </tr>
</table>
</body>
</html>

student

<%@ page contentType="text/html;charset=UTF-8" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<table border="1" cellspacing="0" width="800">
    <tr>
        <th>学号</th>
        <th>姓名</th>
        <th>性别</th>
    </tr>
    <jsp:useBean id="students" scope="request" type="java.util.List"/>

    <c:forEach items="${students}" var="student">
    <tr align="center">
        <td>${student.stuNo}</td>
        <td>${student.stuName}</td>
        <td>${student.stuSex}</td>
        </c:forEach>
</table>

</body>
</html>

数据库设计

-- auto-generated definition
create table tb_student
(
STUNO char(4) not null
primary key,
STUNAME varchar(3) null,
STUSEX varchar(1) not null,
constraint STUNAME
unique (STUNAME)
);

使用注解完成SQL编写，使用ResultMap完成取别名的操作，使用工具SqlSessionFactoryUtils，此为一个简单的综合jsp-jdbc案例