使用mybatis完成图书馆管理小项目

mybatis完成图书馆管管理系统小项目
本篇博客旨在演示mybatis、servlet、数据库和前端页面的交互，项目的业务逻辑可能有所欠缺，大家重点关注技术使用，逻辑层面不需深究

一、要求
按照要求在数据库建立图书表、借出表、购买记录表，用户表，图书类型表，使用mybatis完成用户登录、图书信息查询、图书进货、借书、还书、购书等操作

技术使用

1. mybatis框架；
2. servlet；
   3.jsp等；

数据库建表

CREATE DATABASE bookmrg

#图书类型表
CREATE TABLE IF NOT EXISTS book_type(
	type_id INT PRIMARY KEY AUTO_INCREMENT,
	type_name VARCHAR(50),
	default_date DATE,
	delay_money_per_day DOUBLE(11,2)
);

#图书表
CREATE TABLE IF NOT EXISTS book_info(
	book_id INT PRIMARY KEY AUTO_INCREMENT,
	book_name VARCHAR(50),
	price DOUBLE(11,2),
	store INT,
	book_des VARCHAR(500),
	book_type INT
);

#借出表
CREATE TABLE IF NOT EXISTS borrow_info(
	borrow_id INT PRIMARY KEY AUTO_INCREMENT,
	user_id INT,
	book_id INT,
	borrow_date DATE,
	back_date DATE,
	delay_money DOUBLE(11,2)
); 

#购买记录表
CREATE TABLE IF NOT EXISTS buy_history(
	buy_id INT PRIMARY KEY AUTO_INCREMENT,
	user_id INT,
	book_id INT,
	buy_date DATE,
	buy_num INT,
	total_price DOUBLE(11,2)
);


#用户表
CREATE TABLE IF NOT EXISTS user_info(
	user_id INT PRIMARY KEY AUTO_INCREMENT,
	user_name VARCHAR(50),
	user_grade VARCHAR(50),
	user_phone VARCHAR(20),
	user_type VARCHAR(50)
);

按照字段属性要求及表之间的关联关系插入数据，用于项目测试
user表中有且只有一个管理员admin

在idea中搭建mybatis环境

1、引入mybatis相关jar包

2、按照图示工程目录结构创建相关包、类、接口和配置文件

db.properties为数据库连接属性文件

log4j.properties为日志输出属性文件

mybatis-config.xml为mybatis配置文件

BookMapper.xml为BookMapper接口的映射文件

功能模块一：根据用户名实现用户登录

分析：
（1）由于数据库没有建立密码字段，本项目用户登录仅匹配用户名即可登录成功
（2）前端页面通过表单提交用户名信息
（3）servlet接收前端用户名数据，将其作为参数调用dao层对应接口到数据库的用户表中查询相关信息，并返回用户对象（若不存在该用户则返回null）
（4）servlet根据返回的对象用户对象是否为null分别设置对应的session属性，并通过重定向跳转到前端页面进行进行信息展示
（5）若返回对象为空，则跳转到登录界面提示登录失败，并让用户重新登录，否则跳转到登录成功页面，并通过判断传来的用户名是否为admin而给出不同的操作
（6）若为管理员，则有添加图书等权限
（7）若不为管理员，则有查看所有图书，以及按照用户名，图书名和图书类型查询借书等相关记录（模糊查询）的权限

功能模块二：用户查看所有图书信息

分析：
（1）前端用户通过表单提交请求，并跳转到servlet进行处理
（2）servlet调用dao层相关接口进行查询，返回一个图书实体类集合
（3）servlet对集合进行判断，若为null则向前端提交查询失败会话，否则将集合通过session传递到前端
（4）通过jsp的标签对集合进行遍历，显示所有图书信息
（5）展示图书信息的同时给用户提供查询借书信息选项，提交的信息包括用户姓名，书名，图书类），并通过模糊查询到后端进行数据查询

功能模块三：根据用户名、图书名、图书类型模糊查询相关借书记录

分析：
（1）servlet接收前端传入的三个字符属性
（2）由于接收的三个属性都为String类型，可能会存在空值这时需要使用mybatis的动态SQL技术从而避免SQL异常
（3）由于参数为多个，所以采用map集合的方式进行参数传递
（4）查询后返回一个借书信息对象，在servlet对此对象进行判断是否为空
（5）若为空，则提示用户未查询到相关信息，否则展示相关借书记录

废话不多说直接上代码

dao层接口：

package cn.kgc.book.dao;

import cn.kgc.book.entity.Book;
import cn.kgc.book.entity.User;

import java.util.List;
import java.util.Map;

/**
 * @author ZhangALong
 * @date 2020/6/15
 * @description
 */
public interface BookMapper {
    User queryUserByName(String username);
    List<Book> queryAllBooks();
    List<Book> queryBooksByName(Map<String,String> map);
}

接口对应的映射文件：

<?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="cn.kgc.book.dao.BookMapper">
<select id="queryUserByName" parameterType="string" resultType="user">
    select * from user_info where user_name=#{username};
</select>
    <select id="queryAllBooks" resultType="Book">
        select * from book_info join book_type on type_id=book_type;
    </select>
    <select id="queryBooksByName" parameterType="map" resultType="book">
        select * from book_info join borrow_info on book_info.book_id=borrow_id
        join book_type on book_info.book_type=book_type.type_id
        join user_info on borrow_info.user_id=user_info.user_id
        <where>
            <if test="bookname!=null and bookname!=''">
                book_name like concat("%",#{bookname},"%");
            </if>
            <if test="username!=null and username!=''">
                and user_name like concat("%",#{username},"%");
            </if>
            <!--<if test="type!=null and type!=''">-->
                <!--and type_name like concat("%",#{type},"%");-->
            <!--</if>-->
        </where>
    </select>
</mapper>

Book实现类：

package cn.kgc.book.entity;

import java.util.Date;

/**
 * @author ZhangALong
 * @date 2020/6/15
 * @description
 */
public class Book {
    private int book_id;
    private String book_name;
    private double price;
    private int store;
    private String book_des;
    private String type_name;
    private int default_date;
    private double delay_money_per_day;
    private String user_name;
    private Date borrow_date;
    private Date back_date;
    private double delay_money;

    public Date getBorrow_date() {
        return borrow_date;
    }

    public void setBorrow_date(Date borrow_date) {
        this.borrow_date = borrow_date;
    }

    public Date getBack_date() {
        return back_date;
    }

    public void setBack_date(Date back_date) {
        this.back_date = back_date;
    }

    public double getDelay_money() {
        return delay_money;
    }

    public void setDelay_money(double delay_money) {
        this.delay_money = delay_money;
    }

    public String getUser_name() {
        return user_name;
    }

    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }

    public int getBook_id() {
        return book_id;
    }

    public void setBook_id(int book_id) {
        this.book_id = book_id;
    }

    public String getBook_name() {
        return book_name;
    }

    public void setBook_name(String book_name) {
        this.book_name = book_name;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public int getStore() {
        return store;
    }

    public void setStore(int store) {
        this.store = store;
    }

    public String getBook_des() {
        return book_des;
    }

    public void setBook_des(String book_des) {
        this.book_des = book_des;
    }

    public String getType_name() {
        return type_name;
    }

    public void setType_name(String type_name) {
        this.type_name = type_name;
    }

    public int getDefault_date() {
        return default_date;
    }

    public void setDefault_date(int default_date) {
        this.default_date = default_date;
    }

    public double getDelay_money_per_day() {
        return delay_money_per_day;
    }

    public void setDelay_money_per_day(double delay_money_per_day) {
        this.delay_money_per_day = delay_money_per_day;
    }
}

User实现类：

package cn.kgc.book.entity;

import java.util.Date;

/**
 * @author ZhangALong
 * @date 2020/6/15
 * @description
 */
public class Book {
    private int book_id;
    private String book_name;
    private double price;
    private int store;
    private String book_des;
    private String type_name;
    private int default_date;
    private double delay_money_per_day;
    private String user_name;
    private Date borrow_date;
    private Date back_date;
    private double delay_money;

    public Date getBorrow_date() {
        return borrow_date;
    }

    public void setBorrow_date(Date borrow_date) {
        this.borrow_date = borrow_date;
    }

    public Date getBack_date() {
        return back_date;
    }

    public void setBack_date(Date back_date) {
        this.back_date = back_date;
    }

    public double getDelay_money() {
        return delay_money;
    }

    public void setDelay_money(double delay_money) {
        this.delay_money = delay_money;
    }

    public String getUser_name() {
        return user_name;
    }

    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }

    public int getBook_id() {
        return book_id;
    }

    public void setBook_id(int book_id) {
        this.book_id = book_id;
    }

    public String getBook_name() {
        return book_name;
    }

    public void setBook_name(String book_name) {
        this.book_name = book_name;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public int getStore() {
        return store;
    }

    public void setStore(int store) {
        this.store = store;
    }

    public String getBook_des() {
        return book_des;
    }

    public void setBook_des(String book_des) {
        this.book_des = book_des;
    }

    public String getType_name() {
        return type_name;
    }

    public void setType_name(String type_name) {
        this.type_name = type_name;
    }

    public int getDefault_date() {
        return default_date;
    }

    public void setDefault_date(int default_date) {
        this.default_date = default_date;
    }

    public double getDelay_money_per_day() {
        return delay_money_per_day;
    }

    public void setDelay_money_per_day(double delay_money_per_day) {
        this.delay_money_per_day = delay_money_per_day;
    }
}

登录servlet：

package cn.kgc.book.servlet;

import cn.kgc.book.dao.BookMapper;
import cn.kgc.book.entity.User;
import cn.kgc.book.util.MapperConfig;
import org.apache.ibatis.session.SqlSession;

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

/**
 * @author ZhangALong
 * @date 2020/6/15
 * @description
 */
public class LoginServlet extends HttpServlet {
    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        HttpSession session=req.getSession();
        String username=req.getParameter("username");
        User user=null;
        //判空
        if (username==null || username.equals("")){
            session.setAttribute("msg","用户不存在");
            resp.sendRedirect("index.jsp");
        }else {
            SqlSession sqlSession= MapperConfig.getSession();
            user=sqlSession.getMapper(BookMapper.class).queryUserByName(username);
            if (user!=null){
                session.setAttribute("msg","登录成功");
                session.setAttribute("user",user);
                resp.sendRedirect("loginSuccess.jsp");
            }else {
                session.setAttribute("msg","用户不存在");
                resp.sendRedirect("index.jsp");
            }
        }
    }
}

查询所有图书信息servlet：

package cn.kgc.book.servlet;

import cn.kgc.book.dao.BookMapper;
import cn.kgc.book.entity.Book;
import cn.kgc.book.util.MapperConfig;
import com.sun.org.apache.regexp.internal.RE;
import org.apache.ibatis.session.SqlSession;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.util.List;

/**
 * @author ZhangALong
 * @date 2020/6/15
 * @description
 */
public class GetAllBooks extends HttpServlet {
    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        HttpSession session= req.getSession();
        SqlSession sqlSession= MapperConfig.getSession();
        List<Book> list=sqlSession.getMapper(BookMapper.class).queryAllBooks();
        if (list!=null){
            session.setAttribute("bookList",list);
            resp.sendRedirect("showBooks.jsp");
        }
    }
}

根据用户名、书名、图书类型进行模糊查询借书记录：

package cn.kgc.book.servlet;

import cn.kgc.book.dao.BookMapper;
import cn.kgc.book.entity.Book;
import cn.kgc.book.util.MapperConfig;
import org.apache.ibatis.session.SqlSession;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author ZhangALong
 * @date 2020/6/15
 * @description
 */
public class GetBookByNameServlet extends HttpServlet {
    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        HttpSession session=req.getSession();
        String bookname=req.getParameter("bookname");
        String username=req.getParameter("username");
        String type=req.getParameter("type");

        Map<String,String> map=new HashMap();
        map.put("bookname",bookname);
        map.put("username",username);
        map.put("type",type);
        SqlSession sqlSession= MapperConfig.getSession();
        System.out.println(bookname);
        List<Book> list=sqlSession.getMapper(BookMapper.class).queryBooksByName(map);
        System.out.println(list.get(0).getBook_name());
        if (list==null){
            session.setAttribute("msg","信息查询失败");
        }else {
            session.setAttribute("msg","信息查询成功");
            session.setAttribute("list",list);
            resp.sendRedirect("showBorrowList.jsp");
        }
    }
}

servlet的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>LoginServlet</servlet-name>
        <servlet-class>cn.kgc.book.servlet.LoginServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>LoginServlet</servlet-name>
        <url-pattern>/login.do</url-pattern>
    </servlet-mapping>
    <!--查看所有书籍-->
    <servlet>
        <servlet-name>GetAllBooks</servlet-name>
        <servlet-class>cn.kgc.book.servlet.GetAllBooks</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>GetAllBooks</servlet-name>
        <url-pattern>/getAllBooks.do</url-pattern>
    </servlet-mapping>
    <!--按条件查找信息-->
    <servlet>
        <servlet-name>GetBookByNameServlet</servlet-name>
        <servlet-class>cn.kgc.book.servlet.GetBookByNameServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>GetBookByNameServlet</servlet-name>
        <url-pattern>/getBookByName.do</url-pattern>
    </servlet-mapping>
</web-app>

前端登录界面：

<%--
  Created by IntelliJ IDEA.
  User: user
  Date: 2020/6/15
  Time: 11:00
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
  <head>
    <title>首页</title>
  </head>
  <body>
  <%
  Object msg=session.getAttribute("msg");
  if (msg==null || msg.equals("")){
  %>
  <form action="login.do" method="get">
    <p>用户名：<input type="text" name="username"></p>
    <p><input type="submit" value="点我登录"></p>
  </form>
  <%
  }else {
      out.print(msg);
  %>
  <form action="login.do" method="get">
    <p>用户名：<input type="text" name="username"></p>
    <p><input type="submit" value="点我登录"></p>
  </form>
  <%
  }
  session.removeAttribute("msg");
  %>
  </body>
</html>

前端登陆成功页面：

<%@ page import="cn.kgc.book.entity.User" %><%--
  Created by IntelliJ IDEA.
  User: user
  Date: 2020/6/15
  Time: 11:54
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>登录成功页面</title>
</head>
<body>
<%
    User user= (User) session.getAttribute("user");
    if (user!=null){
        out.print("恭喜"+user.getUser_type()+"用户"+user.getUser_name()+"登陆成功");
//        学生查看图书模块
        if (user.getUser_type().equals("学生")){
            %>
    <form action="getAllBooks.do" method="get">
        <p><input type="submit" value="点我查看所有书籍"></p>
    </form>
<%
        }else {
    %>
    <form action="" method="">
        <p><input type="submit" value="点我添加数据"></p>
    </form>
    <%
        }
    }
%>
</body>
</html>

前端展示所有图书界面：

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page import="java.util.List" %>
<%@ page import="cn.kgc.book.entity.Book" %><%--
  Created by IntelliJ IDEA.
  User: user
  Date: 2020/6/15
  Time: 13:25
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>书籍页面</title>
</head>
<body>
<form action="getBookByName.do">
    <p>图书名称：<input type="text" name="bookname">
       借书人姓名：<input type="text" name="username">
       图书类型：
        <select name="type">
            <option value="杂志">杂志</option>
            <option value="小说">小说</option>
            <option value="地理">地理</option>
            <option value="计算机">计算机</option>
        </select>
    </p>
    <p><input type="submit" value="点我查询"></p>
</form>
<table border="1">
    <tr>
        <th>图书ID</th>
        <th>书名</th>
        <th>单价</th>
        <th>库存</th>
        <th>详情</th>
        <th>类型</th>
        <th>默认还书时间（天）</th>
        <th>延时缴费（/天）</th>
    </tr>
    <c:forEach items="${bookList}" var="book">
        <tr>
            <td>${book.book_id}</td>
            <td>${book.book_name}</td>
            <td>${book.price}</td>
            <td>${book.store}</td>
            <td>${book.book_des}</td>
            <td>${book.type_name}</td>
            <td>${book.default_date}</td>
            <td>${book.delay_money_per_day}</td>
        </tr>
    </c:forEach>
</table>
</body>
</html>

前端查询借书记录界面：

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%--
  Created by IntelliJ IDEA.
  User: user
  Date: 2020/6/15
  Time: 20:18
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>借书信息展示</title>
</head>
<body>
<c:if test="${list!=null}">
<table border="1">
    <tr>
        <th>图书ID</th>
        <th>图书名称</th>
        <th>单价</th>
        <th>图书类型</th>
        <th>借书人姓名</th>
        <th>借书起始日期</th>
        <th>还书日期</th>
        <th>延迟应缴费</th>
    </tr>
    <c:forEach items="${list}" var="info">
        <tr>
            <td>${info.book_id}</td>
            <td>${info.book_name}</td>
            <td>${info.price}</td>
            <td>${info.type_name}</td>
            <td>${info.user_name}</td>
            <td>${info.borrow_date}</td>
            <td>${info.back_date}</td>
            <td>${info.delay_money}</td>
        </tr>
    </c:forEach>
</table>
</c:if>
</body>
</html>