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

Java 多表联查分页显示

操作步骤：

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

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();
    }

}

原文链接：https://blog.csdn.net/qq_16559905/article/details/51169324

Java 多表联查分页显示

你可能也喜欢