从数据库取出数据并且在前端页面实现翻页显示

最新推荐文章于 2024-07-23 11:50:44 发布

LOHAS翀

最新推荐文章于 2024-07-23 11:50:44 发布

阅读量9.9k

点赞数 10

分类专栏：一些小打小闹文章标签：数据翻页显示 JSP Servlet MySQL

本文链接：https://blog.csdn.net/qq_36314461/article/details/101862428

版权

一些小打小闹专栏收录该内容

1 篇文章 0 订阅

订阅专栏

最近做了一个关于“从数据库【mysql】去除数据并且用页面来实现分页显示”的练习，记录如下：

1.主要使用到的框架或技术：

项目构建：Maven
数据库：MyBatis、MySQL
前端页面：HTML+CSS+JavaScript+JSP
后台：Servlet

2. 相关结构：

1. 项目目录结构

目录结构

2. 数据表结构：

在这里插入图片描述
CREATE TABLE scholarship (
id int(11) NOT NULL AUTO_INCREMENT,
stu_id varchar(20) DEFAULT NULL,
stu_name varchar(20) DEFAULT NULL,
major varchar(50) DEFAULT NULL,
classes varchar(30) DEFAULT NULL,
type varchar(50) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=196 DEFAULT CHARSET=utf8

3. 思路

首页利用MyBatis从数据库中获取数据，将获取的数据使用一个Page类封装起来
使用JSP来获取后台中的Page对象。
利用JavaScript创建一个与Page类相似的对象构造函数。再创建一个Array来存放这些对象
利用JSP将Page对象中封装的各个值传递给前端
利用JavaScript创建表格行来显示对应的数据

4. 一些要说的^ _ ^

创建表格是用JavaScript对表操作的函数实现的
为了独立视图，要将展示的页面[View]放在项目的WEB-INF目录下，这样可以防止浏览器【用户】来访问页面【后台是可以访问的】。如图：

在这里插入图片描述
3.
4. 使用Maven创意项目是很方便的，比如配置了Tomcat插件之后可以在没有Tomcat环境的主机上运行项目等等
5. MyBatis操作是用XMl与接口整合的方式实现的
6. 将MyBatis的相关操作用一个工具类实现。
7. 注意Maven的pom.xml文件的写法。【里面的一些代码可以解决资源路径找不见等问题】
8. 还有很多其他的实现方法，这个只是暂时想出来的一个方法

5.主要代码

1.资源文件

db.properties

url=jdbc:mysql:///mybatis?useUnicode=true&characterEncoding=UTF8
username=root
password=123456
drivername=com.mysql.cj.jdbc.Driver

log4j.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
    <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern"
                   value="%-5p %d{MM-dd HH:mm:ss,SSS} %m  (%F:%L) \n" />
        </layout>
    </appender>
    <logger name="java.sql">
        <level value="debug" />
    </logger>
    <logger name="org.apache.ibatis">
        <level value="debug" />
    </logger>
    <root>
        <level value="debug" />
        <appender-ref ref="STDOUT" />
    </root>
</log4j:configuration>

2. 配置代码

pom.xml

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.fxc</groupId>
  <artifactId>JavaWebTest01</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>war</packaging>

  <name>JavaWebTest01 Maven Webapp</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
  </properties>

  <dependencies>

    <!-- https://mvnrepository.com/artifact/junit/junit -->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
      <scope>test</scope>
    </dependency>


    <!--<dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>-->

    <!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>4.0.1</version>
      <scope>provided</scope>
    </dependency>

    <!-- https://mvnrepository.com/artifact/javax.servlet/jstl -->
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>jstl</artifactId>
      <version>1.2</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/taglibs/standard -->
    <dependency>
      <groupId>taglibs</groupId>
      <artifactId>standard</artifactId>
      <version>1.1.2</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.6</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.15</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/log4j/log4j -->
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
    </dependency>



  </dependencies>



  <build>
    <!--指定maven的资源路径-->
      <resources>
          <resource>
              <directory>src/main/java</directory>
            <includes>
              <include>**/*.xml</include>
              <include>**/*.*</include>
            </includes>
          </resource>

        <resource>
          <directory>src/resources</directory>
          <includes>
            <include>**/*.xml</include>
            <include>**/*.properties</include>
          </includes>
        </resource>
      </resources>


    <finalName>JavaWebTest01</finalName>
    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
      <plugins>
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.1.0</version>
        </plugin>
        <!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->
        <plugin>
          <artifactId>maven-resources-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.8.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-surefire-plugin</artifactId>
          <version>2.22.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-war-plugin</artifactId>
          <version>3.2.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-install-plugin</artifactId>
          <version>2.5.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-deploy-plugin</artifactId>
          <version>2.8.2</version>
        </plugin>

        <plugin>
          <groupId>org.apache.tomcat.maven</groupId>
          <artifactId>tomcat7-maven-plugin</artifactId>
          <version>2.2</version>
          <configuration>
            <!--端口控制-->
            <port>8080</port>
            <!--项目路径控制意味着http://localhost:8080/-->
            <path>/web01</path>
            <!--编码-->
            <uriEncoding>UTF-8</uriEncoding>
          </configuration>
        </plugin>
        <!-- maven插件控制 -->
        <plugin>
          <groupId>org.apache.maven.plugins</groupId>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.1</version>
          <configuration>
            <source>1.8</source>
            <target>1.8</target>
            <encoding>utf-8</encoding>
          </configuration>
        </plugin>


      </plugins>
    </pluginManagement>
  </build>
</project>

mybatisConfig.xml

<?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>
    <!--  外部配置文件  -->
    <properties resource="db.properties" />

    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>

    <!--添加实体类的别名，注意位置，在settings下，environments上-->
    <typeAliases>
        <package name="model"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${drivername}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mapping/test.xml" />
        <mapper resource="mapping/usermapper.xml" />
        <mapper resource="mapping/scholarshipmapper.xml" />
    </mappers>

</configuration>

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee
          http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
         version="4.0">
  <servlet>
    <servlet-name>userServlet</servlet-name>
    <servlet-class>Controller.UserServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>userServlet</servlet-name>
    <url-pattern>/user1</url-pattern>
  </servlet-mapping>
  <servlet>
    <servlet-name>servletTest</servlet-name>
    <servlet-class>Controller.ServletTest01</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>servletTest</servlet-name>
    <url-pattern>/user2</url-pattern>
  </servlet-mapping>
</web-app>

3. 后台代码

model.Scholarship

package model;

public class Scholarship {
    private int id;
    private String stu_id;
    private String stu_name;
    private String major;
    private String classes;
    private String type;

    public Scholarship() {
    }

    public Scholarship(int id, String stu_id, String stu_name, String major, String classes, String type) {
        this.id = id;
        this.stu_id = stu_id;
        this.stu_name = stu_name;
        this.major = major;
        this.classes = classes;
        this.type = type;
    }

    public int getId() {
        return id;
    }

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

    public String getStu_id() {
        return stu_id;
    }

    public void setStu_id(String stu_id) {
        this.stu_id = stu_id;
    }

    public String getStu_name() {
        return stu_name;
    }

    public void setStu_name(String stu_name) {
        this.stu_name = stu_name;
    }

    public String getMajor() {
        return major;
    }

    public void setMajor(String major) {
        this.major = major;
    }

    public String getClasses() {
        return classes;
    }

    public void setClasses(String classes) {
        this.classes = classes;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    @Override
    public String toString() {
        return "Scholarship{" +
                "id=" + id +
                ", stu_id='" + stu_id + '\'' +
                ", stu_name='" + stu_name + '\'' +
                ", major='" + major + '\'' +
                ", classes='" + classes + '\'' +
                ", type='" + type + '\'' +
                '}';
    }
}

model.Page

/**
 * 使查询结果分页
 */

package model;

import java.util.ArrayList;

public class Page {
    private int pageNum;  //总页数
    private int pageSize;  //每页大小(显示的条数)
    private ArrayList<Scholarship> content;  //存储数据

    public Page() {
    }

    public Page(int pageNum, int pageSize, ArrayList<Scholarship> content) {
        this.pageNum = pageNum;
        this.pageSize = pageSize;
        this.content = content;
    }

    public int getPageNum() {
        return pageNum;
    }

    public void setPageNum(int pageNum) {
        this.pageNum = pageNum;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public ArrayList<Scholarship> getContent() {
        return content;
    }

    public void setContent(ArrayList<Scholarship> content) {
        this.content = content;
    }

    @Override
    public String toString() {
        return "Page{" +
                "pageNum=" + pageNum +
                ", pageSize=" + pageSize +
                ", content=" + content +
                '}';
    }
}

Dao.scholarshipDao

package Dao;

import model.Scholarship;

import java.util.List;

public interface scholarshipDao {
    public List<Scholarship> selectAllSS();
}

mapping/scholarshipmapper.xml

<?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="Dao.scholarshipDao">
    <select id="selectAllSS" resultType="Scholarship">
        select * from scholarship;
    </select>
</mapper>

Service.ScholarshipService

package Service;

import model.Page;
import model.Scholarship;

import java.util.ArrayList;
import java.util.List;

public interface ScholarshipService {
    public ArrayList<Scholarship> selectAllSS();
    public Page getPage();
}

Service.Impl.ScholarshipServiceImpl

package Service.Impl;

import Dao.scholarshipDao;
import Service.ScholarshipService;
import model.Page;
import model.Scholarship;
import org.apache.ibatis.session.SqlSession;
import utils.MyBatisHelper;

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

public class ScholarshipServiceImpl implements ScholarshipService {
    /**
     * 从数据库获取全部数据
     * @return 返回所有结果
     */
    @Override
    public ArrayList<Scholarship> selectAllSS() {
        try {
            SqlSession session = MyBatisHelper.getSession();
            scholarshipDao scholarshipDao = session.getMapper(scholarshipDao.class);
            ArrayList<Scholarship> scholarships = (ArrayList<Scholarship>) scholarshipDao.selectAllSS();
            return scholarships;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    public Page getPage(){
        ArrayList<Scholarship> scholarships = selectAllSS();
        Page page = new Page();
        page.setPageSize(10);
        page.setPageNum((int) Math.ceil((double)scholarships.size()/(double)page.getPageSize()));
        page.setContent(scholarships);
        return page;
    }
}

Controller.ScholarshipServlet

package Controller;

import Service.Impl.ScholarshipServiceImpl;
import Service.ScholarshipService;
import model.Page;

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;

@WebServlet(name = "ScholarshipServlet",urlPatterns = "/scholarship")
public class ScholarshipServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        ScholarshipService ssi = new ScholarshipServiceImpl();
        Page page = ssi.getPage();
        request.setAttribute("myPage",page);
        //System.out.println(page);
        request.getRequestDispatcher("/WEB-INF/scholarship.jsp").forward(request,response);
    }

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

utils.MyBatisHelper

package utils;

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.Reader;

public class MyBatisHelper {
    private static SqlSession session;

    public static SqlSession getSession() throws IOException {
        String resource = "MyBatisConfig.xml";
        Reader reader = Resources.getResourceAsReader(resource);
        SqlSessionFactory sessionFactory =new SqlSessionFactoryBuilder().build(reader);
        session = sessionFactory.openSession();
        return session;
    }
}

WEB-INF/scholarship.jsp

<%@ page import="model.Page" %>
<%@ page import="model.Scholarship" %>
<%@ page import="java.util.ArrayList" %><%--
  Created by IntelliJ IDEA.
  User: 清风慕竹
  Date: 2019/9/30
  Time: 18:43
  To change this template use File | Settings | File Templates.
  思路：用JS的Array来存储查询出的数据对象，其中JS对象和后台对象的传值用JSP来实现
  之后创建表格【JS对表格的操作】等操作就简单了
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>OK</title>

    <style type="text/css">
        td{
            text-align: center;
        }
    </style>

    <script>
        var a =1;
        function Scholarship(id, stu_id, stu_name, major, classes, type) {
            this.id = id;
            this.stu_id = stu_id;
            this.stu_name = stu_name;
            this.major = major;
            this.classes = classes;
            this.type = type;
        }

        //创建数组来存储数据
        var myList = new Array();

        <%
            Page myPage = (Page) request.getAttribute("myPage");
            //获取总共的数据量
            int totalsize = myPage.getContent().size();
            //包含的数据
            ArrayList<Scholarship> myList = myPage.getContent();
            //获取页数
            int pageNum = myPage.getPageNum();
            //获取页大小
            int pageSize = myPage.getPageSize();
        %>
        //存储总页数
        var PAGENUM =
        <%=pageNum%>
        var PAGESIZE =
        <%=pageSize%>
        var TOTALSIZE = <%=totalsize%>
            <%
                for(int i = 0; i < totalsize; i++) {
             %>
            myList.push(new Scholarship('<%=myList.get(i).getId()%>', '<%=myList.get(i).getStu_id()%>', '<%=myList.get(i).getStu_name()%>', '<%=myList.get(i).getMajor()%>', '<%=myList.get(i).getClasses()%>', '<%=myList.get(i).getType()%>'));

        <%
           }
       %>

        //当前页数
        var correctPageNum = 1;

        /**
         * 封装创建表格行的函数
         * 主要使用JS对表格的操作
         */
        function createtablerow() {
            var rows;
            //若是当前页码与总页码不同则显示PAGESIZE行
            if (correctPageNum != PAGENUM)
                rows = PAGESIZE;
            else
            //若是当前页码为最后一页则显示剩余行
                rows = PAGESIZE * (PAGENUM) - TOTALSIZE;
            //创建行函数
            for (var i = 0; i < rows; i++) {
                var table = document.getElementById("tables");
                var newRow = table.insertRow(i+1);
                newRow.setAttribute("class","mytr");

                var cell_id = newRow.insertCell();
                cell_id.innerText = myList[(correctPageNum - 1) * PAGESIZE + i].id;

                var cell_stu_id = newRow.insertCell();
                cell_stu_id.innerText = myList[(correctPageNum - 1) * PAGESIZE + i].stu_id;

                var cell_stu_name = newRow.insertCell();
                cell_stu_name.innerText = myList[(correctPageNum - 1) * PAGESIZE + i].stu_name;

                var cell_major = newRow.insertCell();
                cell_major.innerText = myList[(correctPageNum - 1) * PAGESIZE + i].major;

                var cell_classes = newRow.insertCell();
                cell_classes.innerText = myList[(correctPageNum - 1) * PAGESIZE + i].classes;

                var cell_type = newRow.insertCell();
                cell_type.innerText = myList[(correctPageNum - 1) * PAGESIZE + i].type;
            }
        }

        function test() {
            var context = document.getElementById("tables");
            //context.appendChild(maincontext);
            var newRow = context.insertRow(0);
            var cell1 = newRow.insertCell();
            cell1.innerText="aaa";
        }


        function firstpage() {
            correctPageNum = 1;
            var trs = document.getElementsByClassName("mytr");
            if (trs != null) {
                for (var i = 0; i < trs.length; i++) {
                    trs[i].parentNode.removeChild(trs[i]);
                    i--;
                }
            }

            createtablerow();
        }

        function pageup() {
            if (correctPageNum > 1)
                correctPageNum--;

            var trs = document.getElementsByClassName("mytr");
            if (trs != null) {
                for (var i = 0; i < trs.length; i++) {
                    trs[i].parentNode.removeChild(trs[i]);
                    i--;
                }
            }

            createtablerow();

            //console.log(correctPageNum);
        }

        function pagedown() {
            if (correctPageNum < PAGENUM)
                correctPageNum++;

            var trs = document.getElementsByClassName("mytr");
            if (trs != null) {
                for (var i = 0; i < trs.length; i++) {
                    trs[i].parentNode.removeChild(trs[i]);
                    i--;
                }
            }

            createtablerow();

            //console.log(correctPageNum);
        }


        function lastpage() {
            correctPageNum = PAGENUM;

            var trs = document.getElementsByClassName("mytr");
            if (trs != null) {
                for (var i = 0; i < trs.length; i++) {
                    trs[i].parentNode.removeChild(trs[i]);
                    i--;
                }
            }

            createtablerow();
        }
    </script>
</head>
<body>
<table id="tables" border="1px black solid" cellspacing="0px" style="margin:0 auto;">
    <tr id="tablehead">
        <td style="width: 50px">序号</td>
        <td style="width: 150px">学号</td>
        <td style="width: 100px">姓名</td>
        <td style="width: 100px">专业</td>
        <td style="width: 100px">班级</td>
        <td style="width: 200px">奖学金类别</td>
    </tr>

    <script>firstpage()</script>


    <form>
        <tr>
            <td colspan="3" style="text-align: left;border-right: none">
                <button type="button" onclick="firstpage()">首页</button>
                <button type="button" onclick="pageup()">上一页</button>
            </td>
            <td colspan="3" style="text-align: right;border-left: none;">
                <button type="button" onclick="pagedown()">下一页</button>
                <button type="button" onclick="lastpage()">尾页</button>
            </td>
        </tr>
    </form>
</table>
<script>

</script>
</body>
</html>