Java版图书管理系统完整开发流程

最新推荐文章于 2024-05-12 03:05:11 发布

A_K_L

最新推荐文章于 2024-05-12 03:05:11 发布

阅读量4.3k

点赞数 13

分类专栏：笔记文章标签：数据库 mysql servlet

本文链接：https://blog.csdn.net/A_K_L/article/details/108064672

版权

笔记专栏收录该内容

29 篇文章 0 订阅

订阅专栏

开发环境与技术栈

Windows/Mac/Linux
Maven
Servlet
MySQL
Jackson

项目功能

主要业务：管理学校图书信息，主要是用来管理学生借阅图书的信息。

用户注册（打开主页，可以看到注册按钮）；
用户登录（打开主页，可以看到登录界面）；
新增图书借阅信息；
修改图书借阅信息；
删除图书借阅信息。

项目演示

1、注册和登录页面

在这里插入图片描述

2、借阅信息管理页面

在这里插入图片描述

3、新增借阅信息

在这里插入图片描述

4、修改借阅信息

在这里插入图片描述

5、删除借阅信息

在这里插入图片描述

开发准备

1.IDEA编码配置：
在这里插入图片描述

2.自动编码设置：
在这里插入图片描述

3.配置好pom.xml文件，指定打包方式，引入相关依赖等等：

<!-- 比如引入MySQL数据库JDBC驱动包 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>

4.生成项目的web.xml文件：
在这里插入图片描述

5.在IDEA中设置好tomcat部署项目自动发布功能：

在这里插入图片描述

6.准备好前端资源

数据库设计

1、数据库表关系说明

在这里插入图片描述
从上面的关系图可以很清楚的看出数据库中主要的几个表之间的关系：

学生表和班级表为多对一关系；
学生表和图书借阅信息表为一对多关系；
图书表和图书借阅信息表为一对多关系；
由2和3可知，图书表和学生表在借阅的场景下，表现出多对多关系。

2、创建数据库及数据库表

drop database if exists book;
create database book character set utf8mb4;

use book;

-- 创建用户表
drop table if exists user;
create table user(
    id int primary key auto_increment,
    username varchar(20) not null unique comment '用户账号',
    password varchar(20) not null comment '密码',
    nickname varchar(20) comment '用户昵称',
    email varchar(50) comment '邮箱',
    create_time timestamp default NOW() comment '创建时间'
) comment '用户表';

-- 创建学生表
drop table if exists student;
create table student(
    id int primary key auto_increment,
    student_name varchar(20) not null comment '姓名',
    student_no varchar(20) comment '学号',
    id_card varchar(20) comment '身份证号',
    student_email varchar(50) comment '邮箱',
    classes_id int comment '班级id',
    create_time timestamp default NOW() comment '创建时间',
    foreign key (classes_id) references classes(id)
) comment '学生表';

-- 创建班级表
drop table if exists classes;
create table classes(
    id int primary key auto_increment,
    classes_name varchar(20) not null comment '班级名称',
    classes_graduate_year varchar(20) comment '毕业年份，数据字典000001',
    classes_major varchar(20) comment '专业，数据字典000002',
    classes_desc varchar(50) comment '备注',
    create_time timestamp default NOW() comment '创建时间'
) comment '班级表';

-- 创建图书表
drop table if exists book;
create table book(
   id int primary key auto_increment,
   book_name varchar(50) not null comment '图书名称',
   author varchar(20) comment '作者',
   price decimal(10,2) comment '价格',
   create_time timestamp default NOW() comment '创建时间'
) comment '图书信息';

--创建借阅信息表
drop table if exists borrow_record;
create table borrow_record(
   id int primary key auto_increment,
   book_id int comment '图书id',
   student_id int comment '学生id',
   start_time timestamp not null comment '借阅日期',
   end_time timestamp null default null comment '归还日期',
   create_time timestamp default NOW() comment '创建时间',
   foreign key (book_id) references book(id),
   foreign key (student_id) references student(id)
) comment '图书借阅信息';

-- 为了后期程序良好的拓展性，增加两个表，一个是数据字典表，一个是数据字典标签表
drop table if exists dictionary;
create table dictionary(
    id int primary key auto_increment,
    dictionary_key varchar(20) not null unique comment '键',
    dictionary_value varchar(20) not null comment '值',
    dictionary_desc varchar(20) comment '备注',
    create_time timestamp default NOW() comment '创建时间'
) comment '数据字典';

drop table if exists dictionary_tag;
create table dictionary_tag(
    id int primary key auto_increment,
    dictionary_tag_key varchar(20) not null comment '键',
    dictionary_tag_value varchar(20) not null comment '值',
    dictionary_tag_desc varchar(20) comment '备注',
    dictionary_id int comment '数据字典id',
    create_time timestamp default NOW() comment '创建时间',
    foreign key (dictionary_id) references dictionary(id)
) comment '数据字典标签';

前后端接口约定

要实现功能，需要先明确前后端需要约定好的接口。接口的定义一般是前后端约定好的，所以也和前端代码息息相关，前端需要什么数据，后端需要什么数据，数据的格式等等，也都会在接口中体现。

1、查询专业字典

（1）请求

GET dict/tag/query?dictionaryKey=000002    //请求方法和路径以及请求参数

（2）响应

//响应格式样例
{
  "success" : true,
  "code" : "200",
  "message" : "操作成功",
  "data" : [ {
    "dictionaryTagKey" : "000002001",
    "dictionaryTagValue" : "中文系"
  }, {
    "dictionaryTagKey" : "000002002",
    "dictionaryTagValue" : "英语系"
  }, {
    "dictionaryTagKey" : "000002003",
    "dictionaryTagValue" : "计算机科学与技术"
  } ]
}

2、查询毕业年份字典

（1）请求

GET dict/tag/query?dictionaryKey=000001   //请求方法和路径以及请求参数

（2）响应

//响应格式样例
{
  "success" : true,
  "code" : "200",
  "message" : "操作成功",
  "data" : [ {
    "dictionaryTagKey" : "000001001",
    "dictionaryTagValue" : "2020届"
  }, {
    "dictionaryTagKey" : "000001002",
    "dictionaryTagValue" : "2021届"
  }, {
    "dictionaryTagKey" : "000001003",
    "dictionaryTagValue" : "2022届"
  }, {
    "dictionaryTagKey" : "000001004",
    "dictionaryTagValue" : "2023届"
  } ]
}

3、查询图书借阅信息

（1）请求

GET borrowRecord/query?searchText=&sortOrder=asc&pageSize=7&pageNumber=1

（2）响应

{
  "success" : true,
  "code" : "200",
  "message" : "操作成功",
  "total" : 9,
  "data" : [ {
    "id" : 1,
    "startTime" : "2020-01-01 14:20:00",
    "endTime" : "2020-02-01 14:20:00",
    "createTime" : "2020-06-17 15:52:33",
    "book" : {
      "id" : 1,
      "bookName" : "高等数学",
      "author" : "邱伯驺",
      "price" : 115.20
    },
    "classes" : {
      "id" : 1,
      "classesName" : "计科三班",
      "classesGraduateYear" : "000001001",
      "classesMajor" : "000002002",
      "classesDesc" : "计算机科学与技术三班"
    },
    "student" : {
      "id" : 1,
      "studentName" : "亚索",
      "studentNo" : "s00001",
      "idCard" : "222222222222222222",
      "studentEmail" : "123@qq.com"
    }
  }, {
    "id" : 7,
    "startTime" : "2020-02-01 14:20:00",
    "endTime" : "2020-02-21 14:20:00",
    "createTime" : "2020-06-17 15:52:33",
    "book" : {
      "id" : 3,
      "bookName" : "数据结构",
      "author" : "严蔚敏",
      "price" : 33.00
    },
    "classes" : {
      "id" : 1,
      "classesName" : "计科三班",
      "classesGraduateYear" : "000001001",
      "classesMajor" : "000002002",
      "classesDesc" : "计算机科学与技术三班"
    },
    "student" : {
      "id" : 3,
      "studentName" : "阿卡丽",
      "studentNo" : "s00003",
      "idCard" : "222222222222222224",
      "studentEmail" : "123@qq.com"
    }
  } ]
}

4、查询班级（数据字典）

使用在下拉菜单中，要求按照数据字典响应格式返回。
（1）请求

GET http://localhost:8080/classes/queryAsDict

（2）响应

{
  "success" : true,
  "code" : "200",
  "message" : "操作成功",
  "data" : [ {
    "dictionaryTagKey" : "1",
    "dictionaryTagValue" : "计科一班",
    "classesGraduateYear" : "000001001",
    "classesMajor" : "000002003"
  }, {
    "dictionaryTagKey" : "2",
    "dictionaryTagValue" : "计科二班",
    "classesGraduateYear" : "000001002",
    "classesMajor" : "000002003"
  }, {
    "dictionaryTagKey" : "3",
    "dictionaryTagValue" : "计科一班",
    "classesGraduateYear" : "000001003",
    "classesMajor" : "000002003"
  } ]
}

5、查询图书（数据字典）

（1）请求

GET book/queryAsDict

（2）响应

{
  "success" : true,
  "code" : "200",
  "message" : "操作成功",
  "data" : [ {
    "dictionaryTagKey" : "1",
    "dictionaryTagValue" : "高等数学",
    "author" : "	邱伯驺",
    "price" : 115.20
  }, {
    "dictionaryTagKey" : "2",
    "dictionaryTagValue" : "数据结构",
    "author" : "严蔚敏",
    "price" : 61.50
  }, {
    "dictionaryTagKey" : "3",
    "dictionaryTagValue" : "深入理解Java虚拟机",
    "author" : "周志明",
    "price" : 33.00
  }, {
    "dictionaryTagKey" : "4",
    "dictionaryTagValue" : "MySQL必知必会",
    "author" : "Ben Forta",
    "price" : 33.00
  } ]
}

6、查询学生（数据字典）

级联下拉菜单：选择班级下拉选项之后，根据选择的班级id查询所有学生。要求按照数据字典响应格式返回。
（1）请求

GET student/queryAsDict?dictionaryKey=2

（2）响应

{
  "success" : true,
  "code" : "200",
  "message" : "操作成功",
  "data" : [ {
    "dictionaryTagKey" : "6",
    "dictionaryTagValue" : "剑姬",
    "studentNo" : "s00006",
    "idCard" : "222222222222222227"
  }, {
    "dictionaryTagKey" : "7",
    "dictionaryTagValue" : "锐雯",
    "studentNo" : "s00007",
    "idCard" : "222222222222222228"
  } ]
}

7、新增图书借阅信息

（1）请求

POST borrowRecord/add
Content-Type: application/json

{"studentId":"7","bookId":"3","startTime":"2020-06-10 19:40:56","endTime":""}

（2）响应

{
  "success" : true,
  "code" : "200",
  "message" : "操作成功"
}

8、删除图书借阅信息

（1）请求

GET borrowRecord/delete?ids=2&ids=3

（2）响应

{
  "success" : true,
  "code" : "200",
  "message" : "操作成功"
}

9、查询图书借阅详情

（1）请求

GET borrowRecord/queryById?id=1

（2）响应

{
  "success" : true,
  "code" : "200",
  "message" : "操作成功",
  "data" : {
    "id" : 1,
    "startTime" : "2020-01-01 14:20:00",
    "endTime" : "2020-02-01 14:20:00",
    "createTime" : "2020-06-17 15:52:33",
    "book" : {
      "id" : 1,
      "bookName" : "高等数学",
      "author" : "邱伯驺",
      "price" : 115.20
    },
    "classes" : {
      "id" : 1,
      "classesName" : "计科三班",
      "classesGraduateYear" : "000001001",
      "classesMajor" : "000002002",
      "classesDesc" : "计算机科学与技术三班"
    },
    "student" : {
      "id" : 1,
      "studentName" : "亚索",
      "studentNo" : "s00001",
      "idCard" : "222222222222222222",
      "studentEmail" : "123@qq.com"
    }
  }
}

10、修改图书借阅信息

（1）请求

POST borrowRecord/update
Content-Type: application/json

{"id":"10","studentId":"3","bookId":"4","startTime":"2020-06-11 19:24:46","endTime":"2020-06-18 19:54:49"}

（2）响应

{
  "success" : true,
  "code" : "200",
  "message" : "操作成功"
}

有了上述的约定，后面不管是后端代码，还是前端代码都需要严格遵守上述约定。否则，就无法完成交互。

代码设计

1、设计数据库实体类

（1）User实体类

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

import java.util.Date;

/**
 * 用户表
 */
@Getter
@Setter
@ToString
public class User {
    //用户ID
    private Integer id;
	//用户名
    private String username;
	//用户密码
    private String password;
	//用户昵称
    private String nickname;
	//用户邮箱
	private String email;
	//创建时间
    private Date createTime;
}

（2）Student实体类

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

import java.util.Date;

/**
 * 学生表
 */
@Getter
@Setter
@ToString
public class Student extends DictionaryTag{
    
    private Integer id;
	//学生姓名
    private String studentName;
	//学号
    private String studentNo;
	//身份证号
    private String idCard;
	//邮箱
    private String studentEmail;
	//班级ID
    private Integer classesId;
	//创建时间
    private Date createTime;
}

（3）Class实体类

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

import java.util.Date;

/**
 * 班级表
 */
@Getter
@Setter
@ToString
public class Classes extends DictionaryTag{
    
    private Integer id;
	//班级名称
    private String classesName;
	//毕业年份，数据字典000001
    private String classesGraduateYear;
    //专业，数据字典000002
    private String classesMajor;
	//备注
    private String classesDesc;
	//创建时间
    private Date createTime;
}

（4）Book实体类

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

import java.math.BigDecimal;
import java.util.Date;

@Getter
@Setter
@ToString
public class Book extends DictionaryTag{
    //如果用int类型的话，不赋值的话默认为0。因为数据库中又很多0，直接使用int有可能会达不到预期查询结果
    private Integer id;
    private String bookName;
    //作者
    private String author; 
    //对应数据库中的decimal类型。跟金钱相关的一般都用这个。
    private BigDecimal price;
    //创建时间
    private Date createTime;
}

（5）BorrowRecord实体类

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

import java.util.Date;

/**
 * 图书借阅信息
 */
@Getter
@Setter
@ToString
public class BorrowRecord {
    
    private Integer id;
	//图书ID
    private Integer bookId;
	//学生ID
    private Integer studentId;
	//借阅日期
    private Date startTime;
	//归还日期
    private Date endTime;
	//创建时间
    private Date createTime;
	//书
    private Book book;
	//班级
    private Classes classes;
	//学生 
    private Student student;
}

（6）ResponseResult实体类

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

@Getter
@Setter
@ToString
//http请求返回响应的统一格式，具体需要的字段，是前端后端一起约定好的
public class ResponseResult {
    private boolean success;  //前端响应状态码为200，但是success可以为false
    private String code;      //自定义的消息编码
    private String message;   //自定义的消息内容
    private Integer total;    //分页需要的字段，查询总的行数(不是分页的数量)
    private Object data;      //业务数据
    private String stackTrace; //出现异常时的堆栈信息
}

2、设计工具类

（1）DBUtil工具类(对JDBC操作进行简单的封装)

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import liufc.exception.SystemException;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBUtil {
    private static final String URL = "jdbc:mysql://localhost:3306/book";
    private static final String USERNAME = "用户名";
    private static final String PASSWORD = "密码";

    //双重校验锁写单例模式
    private static volatile DataSource DATA_SOURCE;
    private DBUtil() {

    }
    private static DataSource getDataSource() {
        if(DATA_SOURCE == null) {
            synchronized (DBUtil.class) {
                if(DATA_SOURCE == null) {
                    DATA_SOURCE = new MysqlDataSource();
                    ((MysqlDataSource) DATA_SOURCE).setUrl(URL);
                    ((MysqlDataSource) DATA_SOURCE).setUser(USERNAME);
                    ((MysqlDataSource) DATA_SOURCE).setPassword(PASSWORD);
                }
            }
        }
        return  DATA_SOURCE;
    }

    public static Connection getConnection() {
        try {
            return getDataSource().getConnection();
        } catch (SQLException e) {
            throw new SystemException("000001","获取数据库连接失败",e);
        }
    }

    public static void close(Connection c, Statement s) {
        close(c,s,null);   //因为修改和添加不返回结果集，所以，不用释放。可以直接重载下面写好的函数
    }

    //释放资源
    public static void close(Connection c, Statement s, ResultSet r) {
        try {
            if(r != null) {
                r.close();
            }
            if(s != null) {
                s.close();
            }
            if(c != null) {
                c.close();
            }
        } catch (SQLException e) {
            throw new SystemException("000002","释放数据库资源出错",e);
        }
    }
}

（2）JSONUtil类

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import liufc.exception.SystemException;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;

public class JSONUtil {
    private static final ObjectMapper MAPPER;

    static {
        MAPPER = new ObjectMapper();
        //时间格式
        MAPPER.setDateFormat(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"));
    }

    //读取输入流的json数据，反序列化对象。
    // 泛型操作：<T>方法上定义泛型类，返回值和传入参数都可以使用泛型。
    public static <T> T read(InputStream is,Class<T> clazz) {
        try {
            return MAPPER.readValue(is,clazz);
        } catch (IOException e) {
            throw new SystemException("000003","http请求，解析json数据出错",e);
        }
    }

    public static String write(Object o) {
        try {
            return MAPPER.writerWithDefaultPrettyPrinter().writeValueAsString(o);
        } catch (JsonProcessingException e) {
            throw new SystemException("000004","json序列化对象出错"+o,e);
        }
    }
}

3、自定义异常类

（1）基本异常类

import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class BaseException extends RuntimeException{
    protected String code;

    public BaseException(String code, String message) {
        super(message);
        this.code = code;
    }

    public BaseException(String code, String message, Throwable cause) {
        super(message, cause);
        this.code = code;
    }
}

（2）业务异常类

//业务异常类
public class BusinessException extends BaseException{

    public BusinessException(String code, String message) {
        super("BUS" + code, "业务异常：" + message);
    }

    public BusinessException(String code, String message, Throwable cause) {
        super("BUS" + code, "业务异常：" + message, cause);
    }
}

（3）客户端异常类

//客户端异常类
public class ClientException extends BaseException{

    public ClientException(String code, String message) {
        super("CLI" + code, "客户端错误" + message);
    }

    public ClientException(String code, String message, Throwable cause) {
        super("CLI" + code, "客户端错误" + message, cause);
    }
}

（4）系统异常类

//系统异常类
public class SystemException extends BaseException{

    public SystemException(String code, String message) {
        super("SYS" + code, "系统异常：" + message);
    }

    public SystemException(String code, String message, Throwable cause) {
        super("SYS" + code, "系统异常：" + message, cause);
    }
}

4、设计dao层

dao层是数据访问层，这里设计几个dao类，来封装对数据库表进行增删查改等基础操作的功能。

（1）UserDao（封装针对user表的基本操作）

import liufc.exception.SystemException;
import liufc.model.User;
import liufc.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class UserDAO {
    public static User query(User user) {
    	//查询结果直接通过一个User实体类返回
        User queryUser = null;
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            c = DBUtil.getConnection();
            String sql = "select id,username,password,nickname from user where username=? and password=?";
            ps = c.prepareStatement(sql);
            ps.setString(1,user.getUsername());
            ps.setString(2,user.getPassword());
            rs = ps.executeQuery();
            while (rs.next()) {
                queryUser = user;
                queryUser.setId(rs.getInt("id"));
                queryUser.setNickname(rs.getString("nickname"));
            }
        }catch (Exception e) {
            throw new SystemException("00007","用户登录校验数据库查询出错",e);
        }finally {
            DBUtil.close(c,ps,rs);
        }

        return queryUser;
    }
}

（2）StudentDao

import liufc.exception.SystemException;
import liufc.model.Student;
import liufc.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class StudentDAO {
    public static List<Student> queryAsDict(int id) {
        List<Student> students = new ArrayList<>();
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            c = DBUtil.getConnection();
            String sql = "select id,student_name,id_card,student_no from student where classes_id=?";
            ps = c.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setDictionaryTagKey(rs.getString("id"));
                student.setDictionaryTagValue(rs.getString("student_name"));
                student.setStudentNo(rs.getString("student_no"));
                student.setIdCard(rs.getString("id_card"));
                students.add(student);
            }
        }catch (Exception e) {
            throw new SystemException("00005","查询学生数据字典出错",e);
        }finally {
            DBUtil.close(c,ps,rs);
        }
        return students;
    }
}

（3）ClassDao

import liufc.exception.SystemException;
import liufc.model.Classes;
import liufc.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ClassesDAO {

    public static List<Classes> queryAsDict() {
        List<Classes> classesList = new ArrayList<>();
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            c = DBUtil.getConnection();
            String sql = "select id, classes_name, classes_graduate_year, classes_major from classes";
            ps = c.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Classes classes = new Classes();
                classes.setDictionaryTagKey(String.valueOf(rs.getInt("id")));
                classes.setDictionaryTagValue(rs.getString("classes_name"));
                classes.setClassesGraduateYear(rs.getString("classes_graduate_year"));
                classes.setClassesMajor(rs.getString("classes_major"));
                classesList.add(classes);
            }
        }catch (Exception e) {
            throw new SystemException("00003","查询班级数据字典出错",e);
        }finally {
            DBUtil.close(c,ps,rs);
        }

        return classesList;
    }
}

（4）BookDao

import liufc.exception.SystemException;
import liufc.model.Book;
import liufc.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class BookDAO {

    public static List<Book> queryAsDict() {
        List<Book> books = new ArrayList<>();
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            c = DBUtil.getConnection();
            String sql = "select id,book_name,author,price from book";
            ps = c.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Book book = new Book();
                //下面两个字段是从id和name来的
                book.setDictionaryTagKey(String.valueOf(rs.getInt("id")));
                book.setDictionaryTagValue(rs.getString("book_name"));
                book.setAuthor(rs.getString("author"));
                book.setPrice(rs.getBigDecimal("price"));
                books.add(book);
            }
        }catch (Exception e) {
            throw new SystemException("00004","查询图书数据字典出错",e);
        }finally {
            DBUtil.close(c,ps,rs);
        }
        return books;
    }
}

（5）BorrowRecordDAO

import liufc.exception.SystemException;
import liufc.model.*;
import liufc.util.CountHolder;
import liufc.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class BorrowRecordDAO {

    public static List<BorrowRecord> query(Page p) {
        List<BorrowRecord> records = new ArrayList<>();
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            c = DBUtil.getConnection();
            //StringBuilder是因为要根据条件来判断是否选择拼接字符串
            StringBuilder sql = new StringBuilder("select br.id," +
                    "       br.book_id," +
                    "       br.student_id," +
                    "       br.start_time," +
                    "       br.end_time," +
                    "       br.create_time," +
                    "       b.book_name," +
                    "       b.author," +
                    "       b.price," +
                    "       s.student_name," +
                    "       s.student_no," +
                    "       s.id_card," +
                    "       s.student_email," +
                    "       s.classes_id," +
                    "       c.classes_name," +
                    "       c.classes_graduate_year," +
                    "       c.classes_major," +
                    "       c.classes_desc" +
                    " from borrow_record br" +
                    "         join book b on br.book_id = b.id" +
                    "         join student s on br.student_id = s.id" +
                    "         join classes c on s.classes_id = c.id");
            //搜索内容不为空字符串,根据学生姓名和图书名称包含搜索内容，就查询出
            if(p.getSearchText() != null && p.getSearchText().trim().length() > 0){
                sql.append(" WHERE s.student_name like ? or  b.book_name like ?");
            }
            //升序或降序.如果使用占位符替换时，字符串替换会自动添加' '，而这里的desc不能加单引号
            if(p.getSortOrder() != null && p.getSortOrder().trim().length() > 0) {
                sql.append("  order by create_time "+p.getSortOrder());
            }

            StringBuilder countSQL = new StringBuilder("select count(0) count from (");
            countSQL.append(sql);
            countSQL.append(") tmp");
            //获取查询结果集的数量
            ps = c.prepareStatement(countSQL.toString());
            if(p.getSearchText() != null && p.getSearchText().trim().length() > 0){
                ps.setString(1,"%"+p.getSearchText()+"%");
                ps.setString(2,"%"+p.getSearchText()+"%");
            }
            rs = ps.executeQuery();
            while (rs.next()) {
                int count = rs.getInt("count");  //获取查询总数量
                //需要在返回的数据中，设置到total字段（data业务数据字段同级，这里设置不进去）
                //保存变量在自己的线程中：ThreadLocal
                CountHolder.set(count);
            }

            //分页查询
            sql.append(" limit ?,?");
            ps = c.prepareStatement(sql.toString());
            int i = 1;
            if(p.getSearchText() != null && p.getSearchText().trim().length() > 0){
                ps.setString(i++,"%"+p.getSearchText()+"%");
                ps.setString(i++,"%"+p.getSearchText()+"%");
            }
            //页码转索引：前一页的页码*每页的数量,索引从0开始
            ps.setInt(i++,(p.getPageNumber()-1)*p.getPageSize());
            ps.setInt(i++,p.getPageSize());
            rs = ps.executeQuery();
            while (rs.next()) {
                //设置图书借阅信息
                BorrowRecord br = new BorrowRecord();
                br.setId(rs.getInt("id"));
                //得把数据库时间强转为Java时间
                br.setStartTime(new Date(rs.getTimestamp("start_time").getTime()));
                br.setEndTime(new Date(rs.getTimestamp("end_time").getTime()));
                br.setCreateTime(new Date(rs.getTimestamp("create_time").getTime()));
                //设置图书信息
                Book book = new Book();
                book.setId(rs.getInt("book_id"));
                book.setBookName(rs.getString("book_name"));
                book.setAuthor(rs.getString("author"));
                book.setPrice(rs.getBigDecimal("price"));
                br.setBook(book);
                //设置学生信息
                Student s = new Student();
                s.setId(rs.getInt("student_id"));
                s.setStudentName(rs.getString("student_name"));
                s.setStudentNo(rs.getString("student_no")); //学号
                s.setIdCard(rs.getString("id_card"));
                s.setStudentEmail(rs.getString("student_email"));
                br.setStudent(s);
                //设置班级信息
                Classes classes = new Classes();
                classes.setId(rs.getInt("classes_id"));
                classes.setClassesName(rs.getString("classes_name"));
                classes.setClassesGraduateYear(rs.getString("classes_graduate_year"));//毕业年份
                classes.setClassesMajor(rs.getString("classes_major")); //专业
                classes.setClassesDesc(rs.getString("classes_desc")); //班级描述
                br.setClasses(classes);
                records.add(br);
            }
        }catch (Exception e) {
            throw new SystemException("00001","查询图书借阅信息出错",e);
        }finally {
            DBUtil.close(c,ps,rs);
        }

        return records;
    }

    public static BorrowRecord queryById(int id) {
        //图书借阅信息
        BorrowRecord br = new BorrowRecord();
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            c = DBUtil.getConnection();
            String sql = "select br.id," +
                    "       br.book_id," +
                    "       br.student_id," +
                    "       br.start_time," +
                    "       br.end_time," +
                    "       br.create_time," +
                    "       b.book_name," +
                    "       b.author," +
                    "       b.price," +
                    "       s.student_name," +
                    "       s.student_no," +
                    "       s.id_card," +
                    "       s.student_email," +
                    "       s.classes_id," +
                    "       c.classes_name," +
                    "       c.classes_graduate_year," +
                    "       c.classes_major," +
                    "       c.classes_desc" +
                    " from borrow_record br" +
                    "         join book b on br.book_id = b.id" +
                    "         join student s on br.student_id = s.id" +
                    "         join classes c on s.classes_id = c.id" +
                    " where br.id=?";
            ps = c.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while (rs.next()) {

                br.setId(rs.getInt("id"));
                //得把数据库时间强转为Java时间
                br.setStartTime(new Date(rs.getTimestamp("start_time").getTime()));
                br.setEndTime(new Date(rs.getTimestamp("end_time").getTime()));
                br.setCreateTime(new Date(rs.getTimestamp("create_time").getTime()));
                //设置图书信息
                Book book = new Book();
                book.setId(rs.getInt("book_id"));
                book.setBookName(rs.getString("book_name"));
                book.setAuthor(rs.getString("author"));
                book.setPrice(rs.getBigDecimal("price"));
                br.setBook(book);
                //设置学生信息
                Student s = new Student();
                s.setId(rs.getInt("student_id"));
                s.setStudentName(rs.getString("student_name"));
                s.setStudentNo(rs.getString("student_no")); //学号
                s.setIdCard(rs.getString("id_card"));
                s.setStudentEmail(rs.getString("student_email"));
                br.setStudent(s);
                //设置班级信息
                Classes classes = new Classes();
                classes.setId(rs.getInt("classes_id"));
                classes.setClassesName(rs.getString("classes_name"));
                classes.setClassesGraduateYear(rs.getString("classes_graduate_year"));//毕业年份
                classes.setClassesMajor(rs.getString("classes_major")); //专业
                classes.setClassesDesc(rs.getString("classes_desc")); //班级描述
                br.setClasses(classes);
            }
        }catch (Exception e) {
            throw new SystemException("00006","查询图书借阅信息详情出错",e);
        }finally {
            DBUtil.close(c,ps,rs);
        }

        return br;
    }

    public static int insert(BorrowRecord record) {
        Connection c = null;
        PreparedStatement ps = null;
        try {
            c = DBUtil.getConnection();
            String sql = "insert borrow_record(book_id,student_id,start_time,end_time) values (?,?,?,?)";
            ps = c.prepareStatement(sql);
            ps.setInt(1,record.getBookId());
            ps.setInt(2,record.getStudentId());
            ps.setTimestamp(3,new Timestamp(record.getStartTime().getTime()));
            ps.setTimestamp(4,new Timestamp(record.getEndTime().getTime()));
            return ps.executeUpdate();
        }catch (Exception e) {
            throw new SystemException("000010","插入图书借阅信息出错",e);
        }finally {
            DBUtil.close(c,ps);
        }
    }

    public static int update(BorrowRecord record) {
        Connection c = null;
        PreparedStatement ps = null;
        try {
            c = DBUtil.getConnection();
            String sql = "update borrow_record set book_id=?,student_id=?,start_time=?,end_time=? where id=?";
            ps = c.prepareStatement(sql);
            ps.setInt(1,record.getBookId());
            ps.setInt(2,record.getStudentId());
            ps.setTimestamp(3,new Timestamp(record.getStartTime().getTime()));
            ps.setTimestamp(4,new Timestamp(record.getEndTime().getTime()));
            ps.setInt(5,record.getId());
            return ps.executeUpdate();
        }catch (Exception e) {
            throw new SystemException("000011","修改图书借阅信息出错",e);
        }finally {
            DBUtil.close(c,ps);
        }
    }

    public static int delete(String[] ids) {
        Connection c = null;
        PreparedStatement ps = null;
        try {
            c = DBUtil.getConnection();
            //因为要修改的行数是一个动态数组，不固定，所以要使用拼接字符串的办法
            StringBuilder sql = new StringBuilder("delete from borrow_record where id in (");
            for (int i = 0; i < ids.length; i++) {
                if(i != 0) {
                    sql.append(",");
                }
                sql.append("?");
            }
            sql.append(")");
            ps = c.prepareStatement(sql.toString());
            for (int i = 0; i < ids.length; i++) {  //填占位符
                ps.setInt(i+1,Integer.parseInt(ids[i]));
            }
            return ps.executeUpdate();
        }catch (Exception e) {
            throw new SystemException("000012","删除图书借阅信息出错",e);
        }finally {
            DBUtil.close(c,ps);
        }
    }
}

（6）DictionaryTagDAO

import liufc.exception.SystemException;
import liufc.model.DictionaryTag;
import liufc.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

//每个dao都是对应数据库表的jdbc操作
//这里就是对数据字典标签的数据访问操作
public class DictionaryTagDAO {
    public static List<DictionaryTag> query(String key) {
        List<DictionaryTag> tags = new ArrayList<>();
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            c = DBUtil.getConnection();
            String sql = "select concat(d.dictionary_key, dt.dictionary_tag_key) dictionary_tag_key," +
                    "       dt.dictionary_tag_value" +
                    " from dictionary d" +
                    "         join dictionary_tag dt on d.id = dt.dictionary_id" +
                    " where d.dictionary_key = ?";
            ps = c.prepareStatement(sql);
            ps.setString(1,key);
            rs = ps.executeQuery();
            while (rs.next()) {
                DictionaryTag tag = new DictionaryTag();
                tag.setDictionaryTagKey(rs.getString("dictionary_tag_key"));
                tag.setDictionaryTagValue(rs.getString("dictionary_tag_value"));
                tags.add(tag);
            }
        }catch (Exception e) {
            throw new SystemException("00002","查询数据字典信息出错",e);
        }finally {
            DBUtil.close(c,ps,rs);
        }

        return tags;
    }
}

5、设计API接口

1、AbstractBaseServlet设置一个统一的HTTP响应类
这是一个继承自HttpServlet的抽象类，设置了一个统一的HTTP响应格式。后面所有的Servlet类都继承自它，增加代码的复用性。

import liufc.exception.BaseException;
import liufc.model.ResponseResult;
import liufc.util.CountHolder;
import liufc.util.JSONUtil;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.io.StringWriter;

public abstract class AbstractBaseServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doPost(req, resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //对请求体进行编码，注意：对url中的请求数据无效
        req.setCharacterEncoding("UTF-8");
        //针对响应体设置编码
        resp.setCharacterEncoding("UTF-8");
        //设置响应的数据格式：响应头Content-Type告诉浏览器怎么解析
        resp.setContentType("text/html");

        //响应结果
        ResponseResult r = new ResponseResult();
        try {
            //父类的service调用doGet/doPost方法，执行到这里，调用子类的process方法
            //只要process没有抛出异常，就返回成功的数据
            Object data = process(req,resp);
            r.setSuccess(true);
            r.setCode("200");
            r.setMessage("操作成功");
            r.setTotal(CountHolder.get());  //可能是分页的接口，get可以获取到值；也可能不是分页接口，返回null
            r.setData(data);
        } catch (Exception e) {   //process抛异常的处理逻辑
            e.printStackTrace();
            if(e instanceof BaseException) {
                BaseException be = (BaseException) e;
                r.setCode(be.getCode());
                r.setMessage(be.getMessage());
            }else {
                r.setCode("500");
                r.setMessage("未知的错误");
            }
            //设置堆栈信息
            StringWriter sw = new StringWriter();
            PrintWriter pw = new PrintWriter(sw);
            e.printStackTrace(pw);
            r.setStackTrace(sw.toString());
        }finally {
            CountHolder.remove();   //ThreadLocal规范做法，线程结束前，一定要remove删除，否则就可能出现内存泄漏
        }
        PrintWriter pw = resp.getWriter();
        pw.println(JSONUtil.write(r));
        pw.flush();
    }

    //抽象方法：子类重写
    public abstract Object process (HttpServletRequest req, HttpServletResponse resp) throws Exception;
}

（2）UserLoginServlet类来校验用户名密码是否正确

import liufc.dao.UserDAO;
import liufc.exception.BusinessException;
import liufc.model.User;
import liufc.util.JSONUtil;

import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

@WebServlet("/user/login")
public class UserLoginServlet extends AbstractBaseServlet {

    @Override
    public Object process(HttpServletRequest req, HttpServletResponse resp) throws Exception {
        //req.getParameter("")这个方法只能获取url和请求体，k=v形式的数据
        User user = JSONUtil.read(req.getInputStream(),User.class);   //http请求解析的用户数据
        User queryUser = UserDAO.query(user);  //通过请求的用户密码在数据库查询，获取用户
        if(queryUser == null) {
            throw new BusinessException("00001","用户名密码校验失败");
        }
        HttpSession session = req.getSession();
        session.setAttribute("user",queryUser);
        return null;
    }
}

（3）StudentQueryAsDictServlet类通过学生id查询学生信息

import liufc.dao.StudentDAO;
import liufc.model.Student;

import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;

@WebServlet("/student/queryAsDict")
public class StudentQueryAsDictServlet extends AbstractBaseServlet{

    @Override
    public Object process(HttpServletRequest req, HttpServletResponse resp) throws Exception {
        //获取班级id
        int id = Integer.parseInt(req.getParameter("dictionaryKey"));
        List<Student> students = StudentDAO.queryAsDict(id);
        return students;
    }
}

（4）ClassesQueryAsDicServlet类查询班级表

import liufc.dao.ClassesDAO;
import liufc.model.Classes;

import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;

@WebServlet("/classes/queryAsDict")
public class ClassesQueryAsDicServlet extends AbstractBaseServlet{

    @Override
    public Object process(HttpServletRequest req, HttpServletResponse resp) throws Exception {
        List<Classes> classesList = ClassesDAO.queryAsDict();
        return classesList;
    }
}

（5）BookQueryAsDictServlet类查询图书列表

import liufc.dao.BookDAO;
import liufc.model.Book;

import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;

@WebServlet("/book/queryAsDict")
public class BookQueryAsDictServlet extends AbstractBaseServlet{

    @Override
    public Object process(HttpServletRequest req, HttpServletResponse resp) throws Exception {
        List<Book> books = BookDAO.queryAsDict();
        return books;
    }
}

（6）BorrowRecordAddServlet类添加图书借阅信息

import liufc.dao.BorrowRecordDAO;
import liufc.exception.BusinessException;
import liufc.model.BorrowRecord;
import liufc.util.JSONUtil;

import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/borrowRecord/add")
public class BorrowRecordAddServlet extends AbstractBaseServlet{

    @Override
    public Object process(HttpServletRequest req, HttpServletResponse resp) throws Exception {
        BorrowRecord record = JSONUtil.read(req.getInputStream(),BorrowRecord.class);
        int num = BorrowRecordDAO.insert(record);  //数据库的插入操作，返回值是int
        if(num != 1) {
            throw new BusinessException("00008","插入图书借阅信息数量异常");
        }
        return null;
    }
}

（7）BorrowRecordQueryServlet类查询图书借阅信息

import liufc.dao.BorrowRecordDAO;
import liufc.model.BorrowRecord;
import liufc.model.Page;
import liufc.util.Util;

import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;

@WebServlet("/borrowRecord/query")
public class BorrowRecordQueryServlet extends AbstractBaseServlet{

    @Override
    public Object process(HttpServletRequest req, HttpServletResponse resp) throws Exception {
        //url中的请求数据通过getParameter获取
        Page p = Util.parse(req);
        List<BorrowRecord> records = BorrowRecordDAO.query(p);
        return records;
    }
}

（8）BorrowRecordQueryByIdServlet类通过id查询图书借阅信息

import liufc.dao.BorrowRecordDAO;
import liufc.model.BorrowRecord;

import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/borrowRecord/queryById")
public class BorrowRecordQueryByIdServlet extends AbstractBaseServlet{

    @Override
    public Object process(HttpServletRequest req, HttpServletResponse resp) throws Exception {
        //获取图书借阅记录的id
        int id = Integer.parseInt(req.getParameter("id"));
        BorrowRecord record = BorrowRecordDAO.queryById(id);
        return record;
    }
}

（9）BorrowRecordUpdateServlet类更新图书借阅信息

import liufc.dao.BorrowRecordDAO;
import liufc.exception.BusinessException;
import liufc.model.BorrowRecord;
import liufc.util.JSONUtil;

import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/borrowRecord/update")
public class BorrowRecordUpdateServlet extends AbstractBaseServlet{

    @Override
    public Object process(HttpServletRequest req, HttpServletResponse resp) throws Exception {
        BorrowRecord record = JSONUtil.read(req.getInputStream(),BorrowRecord.class);
        int num = BorrowRecordDAO.update(record);  //数据库的插入操作，返回值是int
        if(num != 1) {
            throw new BusinessException("00009","修改图书借阅信息数量异常");
        }
        return null;
    }
}

（10）BorrowRecordDeleteServlet类删除图书借阅信息

import liufc.dao.BorrowRecordDAO;
import liufc.exception.BusinessException;
import liufc.model.BorrowRecord;
import liufc.util.JSONUtil;

import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/borrowRecord/delete")
public class BorrowRecordDeleteServlet extends AbstractBaseServlet{

    @Override
    public Object process(HttpServletRequest req, HttpServletResponse resp) throws Exception {
        String[] ids = req.getParameterValues("ids");//ids=4,ids=5...
        int num = BorrowRecordDAO.delete(ids);  //数据库的插入操作，返回值是int
        if(num != ids.length) {
            throw new BusinessException("00009","删除图书借阅信息数量异常");
        }
        return null;
    }
}

项目代码结构

在这里插入图片描述

A_K_L

关注

13
点赞
踩
61

收藏

觉得还不错? 一键收藏
2
评论
Java版图书管理系统完整开发流程

图书管理系统开发环境与技术栈项目功能项目演示开发环境与技术栈Windows/Mac/LinuxMavenServletMySQLJackson项目功能管理学校图书信息，主要是用来管理学生借阅图书的信息。项目演示
复制链接

扫一扫