简单Java 多表联查分页显示（初）

Love The Way You Lie 2022-07-29 11:45 368阅读 0赞

##             Java 多表联查分页显示 ##

**操作步骤：**

1.  建立数据库
2.  使用Servlet 创建Java项目
3.  编写代码

--------------------

**1、数据库 选用 Oracle**

代码附上：

drop table Score;
    drop table Student;
    drop table Subject;
    drop sequence seq_Student;
    drop sequence seq_Score;
    drop sequence seq_Subject;
    
    
    create table Student ( id int primary key, name nvarchar2(50) not null, age int not null, hobby nvarchar2(50) not null );
    
    
    
    create table subject ( id int primary key, name nvarchar2(20) not null );
    
    create table Score ( id int primary key, score number(11,2) not null, stuId int references Student(id) not null, subId int references Subject not null );
    
    
    create sequence seq_Student;
    create sequence seq_Score;
    create sequence seq_Subject;
    
    
    insert into Student values(seq_student.nextval,'张三',20,'篮球');
    insert into Student values(seq_student.nextval,'李四',30,'篮球');
    insert into Student values(seq_student.nextval,'王五',40,'篮球');
    insert into Student values(seq_student.nextval,'赵六',50,'篮球');
    commit;
    
    insert into Subject values(seq_Subject.nextval,'语文');
    insert into Subject values(seq_Subject.nextval,'数学');
    insert into Subject values(seq_Subject.nextval,'英语');
    insert into Subject values(seq_Subject.nextval,'java');
    commit;
    
    
    insert into Score values(seq_score.nextval,96.5,1,1);
    insert into Score values(seq_score.nextval,96.5,2,1);
    insert into Score values(seq_score.nextval,96.5,3,4);
    insert into Score values(seq_score.nextval,96.5,4,4);
    commit;
    
    
    
    
    
    
    select * from Student;
    select * from Score;
    select * from Subject;
    
    
    
    
    
    select stu.*,sco.score,sub.name from (select rownum as rn,s.* from (select * from Student order by id desc) s ) stu left join Score sco on stu.id = sco.id left join Subject sub on sub.id = sco.subid where stu.rn>0 and stu.rn<=10

**2、页面展示：index.jsp页面**

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
    <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
    <html>
      <head>
        <title>My JSP 'index.jsp' starting page</title>
      </head>
    
      <body>
        <form action="">
            <table align="center" border="1" style="width: 400px; border-collapse: collapse; text-align: center;">
                <tr>
                    <th>编号</th>
                    <th>姓名</th>
                    <th>年龄</th>
                    <th>爱好</th>
                    <th>成绩</th>
                    <th>科目</th>
                </tr>
                <c:if test="${empty list}">
                    <c:redirect url="StudentInfoServlet.do"></c:redirect>
                </c:if>
                <c:forEach var="list" items="${sessionScope.list}">
                    <tr>
                        <td>${list.id }</td>
                        <td>${list.name }</td>
                        <td>${list.age }</td>
                        <td>${list.hobby }</td>
                        <td>${list.score }</td>
                        <td>${list.subject }</td>
                    </tr>
                </c:forEach>
                <tr>
                    <td colspan="6">
                        <a href="StudentInfoServlet.do?page=1">首页</a>
                        <a href="StudentInfoServlet.do?page=${sessionScope.top }">上一页</a>
                        <a href="StudentInfoServlet.do?page=${sessionScope.bottom }">下一页</a>
                        <a href="StudentInfoServlet.do?page=${sessionScope.count }">末页</a>
                    </td>
                </tr>
            </table>
    
        </form>
      </body>
    </html>

**3、建立Servlet    StudentInfoServlet.java类**

package servlet;
    
    import java.io.IOException;
    import java.util.List;
    
    import javax.servlet.ServletException;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import dao.StudentInfoDao;
    import dao.impl.StudentInfoDaoImpl;
    import entity.StudentInfo;
    
    public class StudentInfoServlet extends HttpServlet { 
    
        private static final long serialVersionUID = 1L;
    
        public void doGet(HttpServletRequest request, HttpServletResponse response)
                throws ServletException, IOException {
            this.doPost(request, response);
        }
    
        public void doPost(HttpServletRequest request, HttpServletResponse response)
                throws ServletException, IOException {
    
            response.setContentType("text/html;charset= utf-8");
            request.setCharacterEncoding("utf-8");
    
            StudentInfoDao stu = new StudentInfoDaoImpl();
            int count = stu.count_ye();
    
            Integer dang_ye = null;
            if (request.getParameter("page") != null) {
                dang_ye = Integer.parseInt(request.getParameter("page"));
            } else {
                dang_ye = 1;
            }
            List<StudentInfo> list = stu.getAll(dang_ye);
    
            int top = 1;
            int bottom = count;
            if (dang_ye != 1) {
                top = dang_ye - 1;
            }
            if (dang_ye != count) {
                bottom = dang_ye + 1;
            }
            request.getSession().setAttribute("top", top);
            request.getSession().setAttribute("bottom", bottom);
            request.getSession().setAttribute("count", count);
            request.getSession().setAttribute("list", list);
            response.sendRedirect("index.jsp");
        }
    
    }

**4、建立Dao层区**

**StudentInfoDao.java接口**

package dao;
    
    import java.util.List;
    
    import entity.StudentInfo;
    
    public interface StudentInfoDao { 
        public List<StudentInfo> getAll(int page);
    
        public int count_ye();
    }

**实现类 StudentInfoDaoImpl.java接口实现类**

package dao.impl;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    
    import dao.BaseDao;
    import dao.StudentInfoDao;
    import entity.StudentInfo;
    
    public class StudentInfoDaoImpl extends BaseDao implements StudentInfoDao { 
    
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
    
        public int count_ye() {
            int count = 0;
            int ye = 0;
            String sql = "select count(*) from Student";
            try {
                conn = this.getConnection();
                ps = conn.prepareStatement(sql);
                rs = ps.executeQuery();
                if (rs.next()) {
                    count = rs.getInt(1);
                }
                ye = count / 10;
                if (count % 10 != 0) {
                    ye++;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return ye;
        }
    
        public List<StudentInfo> getAll(int page) {
            List<StudentInfo> list = new ArrayList<StudentInfo>();
            String sql = "select stu.*,sco.score as scoid,sub.name as subid from (select rownum as rn,s.* from (select * from Student order by id desc) s ) stu left join Score sco on stu.id = sco.id left join Subject sub on sub.id = sco.subid where stu.rn>? and stu.rn<=?";
            int top = (page - 1) * 10;
            int bottom = page * 10;
            try {
                conn = this.getConnection();
                ps = conn.prepareStatement(sql);
                ps.setInt(1, top);
                ps.setInt(2, bottom);
                rs = ps.executeQuery();
                while (rs.next()) {
                    StudentInfo stu = new StudentInfo(rs.getInt("id"),
                            rs.getString("name"), rs.getInt("age"), rs.getString("hobby"), rs
                                    .getInt("scoid"), rs.getString("subid"));
                    list.add(stu);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                this.getClose(conn, ps, rs);
            }
            return list;
        }
    
    }

**BaseDao.java**

package dao;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    public class BaseDao { 
        private static final String driver = "oracle.jdbc.driver.OracleDriver";
        private static final String url = "jdbc:oracle:thin:@localhost:1521:accp11g";
        private static final String name = "system";
        private static final String pwd = "baiyu";
    
        public Connection getConnection() {
            Connection conn = null;
    
            try {
                Class.forName(driver);
                conn = DriverManager.getConnection(url, name, pwd);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return conn;
        }
    
        public void getClose(Connection conn, Statement ps, ResultSet rs) {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (ps != null) {
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

**实体类 StudentInfo.java**

package entity;
    
    import java.io.Serializable;
    
    public class StudentInfo implements Serializable { 
        private static final long serialVersionUID = 1L;
        private int id;
        private String name;
        private int age;
        private String hobby;
        private int score;
        private String subject;
    
        public int getId() {
            return id;
        }
    
        public void setId(int id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public int getAge() {
            return age;
        }
    
        public void setAge(int age) {
            this.age = age;
        }
    
        public String getHobby() {
            return hobby;
        }
    
        public void setHobby(String hobby) {
            this.hobby = hobby;
        }
    
        public int getScore() {
            return score;
        }
    
        public void setScore(int score) {
            this.score = score;
        }
    
        public String getSubject() {
            return subject;
        }
    
        public void setSubject(String subject) {
            this.subject = subject;
        }
    
        public StudentInfo(int id, String name, int age, String hobby, int score,
                String subject) {
            super();
            this.id = id;
            this.name = name;
            this.age = age;
            this.hobby = hobby;
            this.score = score;
            this.subject = subject;
        }
    
        public StudentInfo(String name, int age, String hobby, int score,
                String subject) {
            super();
            this.name = name;
            this.age = age;
            this.hobby = hobby;
            this.score = score;
            this.subject = subject;
        }
    
        public StudentInfo() {
            super();
        }
    
    }