练习点设计：模糊查询、删除、新增


一、语言和环境

1.实现语言：JAVA语言。

2.环境要求：MyEclipse/Eclipse + Tomcat + MySql。

3.使用技术：​​SpringMVC​​ + ​​Spring​​ + ​​Mybatis​​。

二、实现功能

随着校内图书馆的发展，现需要制作图书信息管理系统，主要功能如下：

1.首页默认显示所有图书信息

基于Spring MVC + Spring + MyBatis的【图书信息管理系统（一）】_mvc

2.鼠标悬停某行数据时，以线性过渡动画显示光棒效果

基于Spring MVC + Spring + MyBatis的【图书信息管理系统（一）】_java_02

3.用户输入图书名称，点击查询，则完成模糊查询，显示查询结果

基于Spring MVC + Spring + MyBatis的【图书信息管理系统（一）】_后端_03

4.用户点击删除，则弹出提示框，用户点击确定后，删除选中数据并显示最新数据

基于Spring MVC + Spring + MyBatis的【图书信息管理系统（一）】_spring_04基于Spring MVC + Spring + MyBatis的【图书信息管理系统（一）】_原力计划_05

5.用户点击“新增”按钮,则打开新增页面,填写完相关信息后点击新增按钮，增加图书信息数据到数据库,且页面跳转到列表页面展示最新数据

基于Spring MVC + Spring + MyBatis的【图书信息管理系统（一）】_java_06

基于Spring MVC + Spring + MyBatis的【图书信息管理系统（一）】_mvc_07

三、数据库设计

1.创建数据库（​​book_db​​）。

2.创建数据表（​​book​​），结构如下。

字段名

说明

字段类型

长度

备注

id

序号

int

主键，自增

name

图书名称

varchar

50

不能为空

type

图书类别

varchar

50

不能为空

price

图书价格

decimal

10，2

不能为空

create_date

出版时间

datetime

不能为空

四、推荐实现步骤

1.SSM版本的实现步骤如下：

（1）创建数据库和数据表，添加测试数据（至少添加4条测试数据）。

（2）创建Web工程并创建各个包，导入工程所需的jar文件。

（3）添加相关SSM框架支持。

（4）配置项目所需要的各种配置文件（mybatis配置文件、spring配置文件、springMVC配置文件）。

（5）创建实体类。

（6）创建MyBatis操作数据库所需的Mapper接口及其Xml映射数据库操作语句文件。

（7）创建业务逻辑相应的接口及其实现类，实现相应的业务，并在类中加入对DAO/Mapper的引用和注入。

（8）创建Controller控制器类，在Controller中添加对业务逻辑类的引用和注入，并配置springMVC配置文件。

（9）创建相关的操作页面，并使用CSS对页面进行美化。

（10）实现页面的各项操作功能，并在相关地方进行验证，操作要人性化。

（11）调试运行成功后导出相关的数据库文件并提交。

五、实现代码

1、MySQL数据库

book_db

基于Spring MVC + Spring + MyBatis的【图书信息管理系统（一）】_java_08

/*
 Date: 25/07/2021 22:09:28
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `price` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `create_date` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES (1, '龙珠', '漫画', '56.00', '2021-07-14 14:07:42');
INSERT INTO `book` VALUES (2, '火影忍者', '漫画', '48.00', '2021-07-24 14:08:25');
INSERT INTO `book` VALUES (3, '三体', '科幻小说', '128.00', '2021-07-22 14:09:08');
INSERT INTO `book` VALUES (4, '西游记', '文学', '98.00', '2021-07-20 14:09:55');
INSERT INTO `book` VALUES (5, '进击的巨人', '动漫', '68.00', '2021-07-06 14:10:22');

SET FOREIGN_KEY_CHECKS = 1;

2、项目Java代码

目录结构

Books

基于Spring MVC + Spring + MyBatis的【图书信息管理系统（一）】_mvc_09

JAR包：

基于Spring MVC + Spring + MyBatis的【图书信息管理系统（一）】_spring_10基于Spring MVC + Spring + MyBatis的【图书信息管理系统（一）】_java_11

src

com.controller【控制层】

BookController.java
package com.controller;

import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;

import com.entity.Book;
import com.service.BookService;

@Controller
public class BookController {

  @Resource
  BookService bookService;

  @RequestMapping("/booksList")
  public String getBooks(Model model, String name) {
    List<Book> bookList = bookService.selectAll(name);
    model.addAttribute("bookList", bookList);
    return "/book";
  }

  @RequestMapping("/deleteBook")
  public String deleteBook(int id) {
    int delBook = bookService.delBook(id);
    return "redirect:/booksList.do";
  }

  // 跳转添加页面的方法
  @RequestMapping("/insertInto")
  public String insertInto() {
    return "addBook";
  }

  // 跳转添加页面的方法
  @RequestMapping("/insertBook")
  public String insertBook(Book book) {
    int addBook = bookService.insertBook(book);
    return "redirect:/booksList.do";
  }

}

com.dao【数据库访问层】

BookMapper.java
package com.dao;

import com.entity.Book;
import java.util.List;

import org.apache.ibatis.annotations.Param;

public interface BookMapper {
    int deleteByPrimaryKey(Integer id);

    int insert(Book record);

    List<Book> selectAll(@Param("name")String name);

}
BookMapper.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="com.dao.BookMapper">
  <resultMap id="BaseResultMap" type="com.entity.Book">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="type" jdbcType="VARCHAR" property="type" />
    <result column="price" jdbcType="VARCHAR" property="price" />
    <result column="create_date" jdbcType="VARCHAR" property="createDate" />
  </resultMap>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from book
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.entity.Book">
    insert into book (id, name, type,
    price, create_date)
    values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR},
    #{type,jdbcType=VARCHAR},
    #{price,jdbcType=VARCHAR}, #{createDate,jdbcType=VARCHAR})
  </insert>


  <select id="selectAll" resultMap="BaseResultMap">
    select id, name, type, price, create_date
    from book

    <where>
      <if test="name!= null">name like "%"#{name}"%"   </if>
    </where>
  </select>


</mapper>

com.entity【实体的包】

Book.java
package com.entity;

public class Book {
    private Integer id;

    private String name;

    private String type;

    private String price;

    private String createDate;

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name == null ? null : name.trim();
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type == null ? null : type.trim();
    }

    public String getPrice() {
        return price;
    }

    public void setPrice(String price) {
        this.price = price == null ? null : price.trim();
    }

    public String getCreateDate() {
        return createDate;
    }

    public void setCreateDate(String createDate) {
        this.createDate = createDate == null ? null : createDate.trim();
    }
}

com.service【义务处理层接口】

BookService.java
package com.service;

import java.util.List;

import com.entity.Book;

public interface BookService {

  //查询
  List<Book> selectAll(String name);
  //删除
  int delBook(int id);
  //添加
  int insertBook(Book book);

}

com.serviceImpl【实现层】

BookServiceImpl.java
package com.serviceImpl;

import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.dao.BookMapper;
import com.entity.Book;
import com.service.BookService;
@Service
public class BookServiceImpl implements BookService {

  @Resource 
  BookMapper mapper;

  @Override
  public List<Book> selectAll(String name) {
    List<Book> selectAll=mapper.selectAll(name);
    return selectAll;
  }

  @Override
  public int delBook(int id) {
    int deleteBook=mapper.deleteByPrimaryKey(id);
    return deleteBook;
  }

  @Override
  public int insertBook(Book book) {
    int insert=mapper.insert(book);
    return insert;
  }


}

mybatis

sqlMapConfig.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>
  <!-- 别名 -->
  <typeAliases>
    <package name="com.entity" />
  </typeAliases>
</configuration>

spring

applicationContext-dao.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  xmlns="http://www.springframework.org/schema/beans" 
  xmlns:p="http://www.springframework.org/schema/p" 
  xmlns:context="http://www.springframework.org/schema/context" 
  xmlns:aop="http://www.springframework.org/schema/aop" 
  xmlns:tx="http://www.springframework.org/schema/tx" 
  xsi:schemaLocation="http://www.springframework.org/schema/beans 
  http://www.springframework.org/schema/beans/spring-beans-4.2.xsd 
  http://www.springframework.org/schema/context 
  http://www.springframework.org/schema/context/spring-context-4.2.xsd 
  http://www.springframework.org/schema/aop 
  http://www.springframework.org/schema/aop/spring-aop-4.2.xsd 
  http://www.springframework.org/schema/tx
  http://www.springframework.org/schema/tx/spring-tx.xsd
  http://www.springframework.org/schema/mvc 
    http://www.springframework.org/schema/mvc/spring-mvc-4.2.xsd ">

  <!-- 指定spring容器读取db.properties文件 -->
  <context:property-placeholder location="classpath:jdbc.properties"></context:property-placeholder>
  <!-- 将连接池注册到bean容器中 -->
  <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
    <property name="driverClassName" value="${jdbc.driver}"></property>
    <property name="Url" value="${jdbc.url}"></property>
    <property name="username" value="${jdbc.username}"></property>
    <property name="password" value="${jdbc.password}"></property>
  </bean>
  <!-- 配置SqlSessionFactory -->
  <bean class="org.mybatis.spring.SqlSessionFactoryBean">
    <!-- 设置MyBatis核心配置文件 -->
    <property name="configLocation" value="classpath:mybatis/sqlMapConfig.xml" />
    <!-- 设置数据源 -->
    <property name="dataSource" ref="dataSource" />
  </bean>
  <!-- 配置Mapper扫描 -->
  <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <!-- 设置Mapper扫描包 -->
    <property name="basePackage"  value="com.dao" />
  </bean>
  <!-- 配置事务管理器 -->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
      <property name="dataSource" ref="dataSource"></property>
    </bean>
    <!-- 开启注解方式管理AOP事务 -->
    <tx:annotation-driven transaction-manager="transactionManager" />
</beans>
applicationContext-service.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns="http://www.springframework.org/schema/beans" 
    xmlns:context="http://www.springframework.org/schema/context" 
    xmlns:aop="http://www.springframework.org/schema/aop" 
    xmlns:tx="http://www.springframework.org/schema/tx" 
    xmlns:mvc="http://www.springframework.org/schema/mvc" 
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
    http://www.springframework.org/schema/beans/spring-beans-4.2.xsd 
    http://www.springframework.org/schema/context 
    http://www.springframework.org/schema/context/spring-context-4.2.xsd 
    http://www.springframework.org/schema/aop 
    http://www.springframework.org/schema/aop/spring-aop-4.2.xsd 
    http://www.springframework.org/schema/tx 
    http://www.springframework.org/schema/tx/spring-tx-4.2.xsd 
    http://www.springframework.org/schema/mvc 
    http://www.springframework.org/schema/mvc/spring-mvc-4.2.xsd ">
    <!-- 配置Service扫描 -->
    <context:component-scan base-package="com" />

</beans>
spring-mvc.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns="http://www.springframework.org/schema/beans" xmlns:context="http://www.springframework.org/schema/context"
  xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
  xmlns:mvc="http://www.springframework.org/schema/mvc"
  xsi:schemaLocation="http://www.springframework.org/schema/beans 
    http://www.springframework.org/schema/beans/spring-beans-4.2.xsd 
    http://www.springframework.org/schema/context 
    http://www.springframework.org/schema/context/spring-context-4.2.xsd 
    http://www.springframework.org/schema/aop 
    http://www.springframework.org/schema/aop/spring-aop-4.2.xsd 
    http://www.springframework.org/schema/tx 
    http://www.springframework.org/schema/tx/spring-tx-4.2.xsd 
    http://www.springframework.org/schema/mvc 
    http://www.springframework.org/schema/mvc/spring-mvc-4.2.xsd ">

  <!-- 配置Controller扫描 -->
  <context:component-scan base-package="com.controller" />
  <!-- 配置注解驱动 -->
  <mvc:annotation-driven />
  <!-- 配置视图解析器 -->
  <bean
    class="org.springframework.web.servlet.view.InternalResourceViewResolver">
    <!-- 前缀 -->
    <property name="prefix" value="/WEB-INF/jsp/" />
    <!-- 后缀 -->
    <property name="suffix" value=".jsp" />
  </bean>
</beans>

jdbc.properties

jdbc.url=jdbc:mysql://localhost:3306/book_db?useUnicode=true&characterEncoding=UTF-8&useSSL=false
jdbc.username=root
jdbc.password=123456
jdbc.driver=com.mysql.jdbc.Driver

WebContent

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">
  <display-name>Books</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
  <!--spring容器  -->
  <context-param>
    <param-name>contextConfigLocation</param-name>
    <param-value>classpath:spring/applicationContext-*.xml</param-value>
  </context-param>
  <!-- 监听器，加载spring配置 -->
  <listener>
    <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
  </listener>
  <!-- 前端控制器 -->
  <servlet>
    <servlet-name>springmvc</servlet-name>
    <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
    <init-param>
      <param-name>contextConfigLocation</param-name>
      <param-value>classpath:spring/spring-mvc.xml</param-value>
    </init-param>
    <load-on-startup>1</load-on-startup>
  </servlet>
  <servlet-mapping>
    <servlet-name>springmvc</servlet-name>
    <url-pattern>*.do</url-pattern>
  </servlet-mapping>
  <!-- 设置post请求的字符编码过滤器 -->
  <filter>
        <filter-name>CharacterEncodingFilter</filter-name>
        <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
        <init-param>
            <param-name>encoding</param-name>
            <param-value>UTF-8</param-value>
        </init-param>
    </filter>
    <filter-mapping>
        <filter-name>CharacterEncodingFilter</filter-name>
        <url-pattern>/*</url-pattern>
    </filter-mapping>
</web-app>

jsp

index.jsp

<%@ page language="java" contentType="text/html; charset=utf-8"
  pageEncoding="utf-8"%>
<!DOCTYPE html>
<%
  String path = request.getContextPath();
  String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort(
      + path;
%>
<html>
<head>
<meta charset="utf-8">
<title>登录</title>
</head>
<body>
  <script type="text/javascript">
  window.location.href="<%=basePath%>/booksList.do";
  </script>
</body>
</html>

addBook.jsp

<%@ page language="java" contentType="text/html; charset=utf-8"
  pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<head>
<meta charset="utf-8">
<title>录入信息</title>
<style type="text/css">
table {
  margin: auto;
}

.button {
  margin: auto;
}
</style>
</head>
<body>
  <form action="insertBook.do">
    <table border="0" cellspacing="" cellpadding="">
      <tr>
        <td>&nbsp;&nbsp;&nbsp;
          <h1 style="text-align:;">新增图书信息</h1>
        </td>
      </tr>
      <tr>
        <td><input type="hidden" name="id" value="${book.id}">
          图书名称:&nbsp;&nbsp;&nbsp;&nbsp; <input type="text" name="name"
          value="${book.name}"></td>
      </tr>
      <tr>
        <td>图书类别:&nbsp;&nbsp;&nbsp;&nbsp; <input type="text"
          name="type" value="${book.type}">
        </td>
      </tr>
      <tr>
        <td>图书价格:&nbsp;&nbsp;&nbsp;&nbsp; <input type="text"
          name="price" value="${book.price}">
        </td>
      </tr>
      <tr>
        <td>出版时间:&nbsp;&nbsp;&nbsp;&nbsp; <input type="text"
          name="createDate" value="${book.createDate}">
        </td>
      </tr>
      <tr>
        <td>
          &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <input
          style="width: 100px;" type="submit" value="新增" />
        </td>
      </tr>
    </table>
  </form>
</body>
</html>

book.jsp

<%@ page language="java" contentType="text/html; charset=utf-8"
  pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<head>
<meta charset="utf-8">
<title>图书信息管理系统</title>
<style type="text/css">
h2 {
  position: relative;
  left: 40%;
}

table {
  text-align: center;
}

.foot {
  margin-right: 100px;
  float: right;
}

tr:hover {
  background: #DEB887;
}

a {
  text-decoration: none;
}
</style>
</head>
<body>
  <h2>图书信息管理系统</h2>
  <fieldset>
    <legend>搜索</legend>
    <form action="booksList.do" method="post">
      图书名称 <input type="text" name="name"> <input type="submit"
        value="搜索" /> <a href="insertInto.do"> <input type="button"
        value="添加" />
      </a>
    </form>
  </fieldset>
  <table width="100%" border="1px" cellpadding="5" cellspacing="3">
    <tr>
      <th width="80px">编号</th>
      <th width="150px">图书名称</th>
      <th width="150px">图书类别</th>
      <th width="150px">图书价格</th>
      <th width="150px">出版时间</th>
      <th width="150px">操作</th>
    </tr>
    <c:forEach var="book" items="${bookList }" varStatus="item">
      <tr>
        <td width="80px">${book.id}</td>
        <td width="150px">${book.name}</td>
        <td width="150px">${book.type}</td>
        <td width="150px">${book.price}</td>
        <td width="150px">${book.createDate}</td>
        <td width="150px">
          <%-- <a href="deleteBook.do?id=${book.id}">删除</a> --%> <a
          href="javascript:if(confirm('确实要删除吗?'))location='deleteBook.do?id=${book.id}'">删除</a>
        </td>
      </tr>
    </c:forEach>
  </table>
  <p>共${bookList.size()}条数据</p>
</body>
</html>