SSM Library Management System: Back-end Implementation (PART 1)

Small knowledge, big challenge! This paper is participating in theEssentials for programmers”Creative activities

preface

The previous two articles covered how to prepare the project framework and how to integrate SSM. Today, let’s see how to do that.

Attached below are the portals of the first two articles:

SSM Library Management system: project framework construction

SSM Library management system: integrated SSM

Preparing the database

Create a new database, BookManager, and create two tables: Book and appointment;

Create database
CREATE DATABASE `bookmanager`;
Copy the code

Create a book table
CREATE TABLE `book` (
    `book_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'the book ID',
    `name` varchar(100) NOT NULL COMMENT 'Book Name',
    `number` int(11) NOT NULL COMMENT 'Collection Quantity'.PRIMARY KEY (`book_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Book list';

Insert data
INSERT INTO `book`(`book_id`, `name`, `number`) VALUES (1, "Effective Java", 10), (2, "algorithm ",10), (3, "MySQL must know must know ",10);
Copy the code

-- Create a book reservation table
CREATE TABLE `appointment` (
    `book_id` int(11) NOT NULL COMMENT 'the book ID',
    `student_id` int(11) NOT NULL COMMENT 'student id',
    `appoint_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Appointment time' ,
    PRIMARY KEY (`book_id`, `student_id`),
    INDEX `idx_appoint_time` (`appoint_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Book Reservation List';
Copy the code

Entity class writing

Once the database is ready, you can create entity classes for the corresponding tables. Before creating entity classes, you can reduce code by introducing Lombok dependencies in Pom.xml.

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.12</version>
</dependency>
Copy the code

Book.java

package com.cunyu.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/ * * *@author : cunyu
 * @version : 1.0
 * @className : Book
 * @date: 2020/7/23 for *@description: Book entity class */

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Book {
    private int bookId;
    private String name;
    private int number;
}
Copy the code

Appointment.java

package com.cunyu.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

/ * * *@author : cunyu
 * @version : 1.0
 * @className : Appointment
 * @date : 2020/7/23 15:57
 * @description: Appointment entity class */

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Appointment {
    private int bookId;
    private int studentId;
    private Date appointTime;
    private Book book;
}
Copy the code

Dao interface class writing

BookDao.java

package com.cunyu.dao;

import com.cunyu.pojo.Book;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/ * * *@InterfaceName : BookDao
 * @Author : cunyu
 * @Date : 2020/7/23 16:02
 * @Version : 1.0
 * @Description: Book interface **/

public interface BookDao {

    / * * *@paramBookId bookId *@returnThe book with the corresponding ID *@descriptionFind the corresponding book * based on the book ID@date 2020/7/23 16:04
     * @author cunyu1943
     * @version1.0 * /
    Book queryById(@Param("bookId") int bookId);

    / * * *@paramOffset Indicates the start position *@paramLimit Number of queries *@returnList of all books *@descriptionQuery all books *@date 2020/7/23 16:08
     * @author cunyu1943
     * @version1.0 * /
    List<Book> queryAll(@Param("offset") int offset, @Param("limit") int limit);

    / * * *@paramBookId bookId *@returnNumber of updated record rows *@descriptionUpdate collection after borrowing *@date 2020/7/23 16:09
     * @author cunyu1943
     * @version1.0 * /
    int reduceNumber(@Param("bookId") int bookId);
}

Copy the code

AppointmentDao.java

package com.cunyu.dao;

import com.cunyu.pojo.Appointment;
import org.apache.ibatis.annotations.Param;

/ * * *@InterfaceName : AppointmentDao
 * @Author : cunyu
 * @Date : 2020/7/23 16:03
 * @Version : 1.0
 * @Description: Appointment interface **/

public interface AppointmentDao {

    / * * *@paramBookId bookId *@paramStudentId studentId *@returnNumber of inserted rows *@descriptionInsert reservation book record *@date2020/7/23 not *@author cunyu1943
     * @version1.0 * /
    int insertAppointment(@Param("bookId") int bookId, @Param("studentId") int studentId);

    / * * *@paramBookId bookId *@paramStudentId studentId *@return
     * @descriptionThrough the primary key query reservation book records, and carry the book entity *@date2020/7/23 company *@author cunyu1943
     * @version1.0 * /
    Appointment queryByKeyWithBook(@Param("bookId") int bookId, @Param("studentId") int studentId);
}
Copy the code

Mapper written

After writing dao interface, we do not need to implement it ourselves, MyBatis will give us dynamic implementation, but we need to configure the corresponding Mapper. In the SRC/main/resources/mapper under new BookDao. XML and AppointmentDao. XML, used in the dao interface corresponding to the above;

BookDao.xml

      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cunyu.dao.BookDao">
    <select id="queryById" resultType="Book" parameterType="int">
        SELECT book_id, name, number
        FROM book
        WHERE book_id = #{bookId}
    </select>

    <select id="queryAll" resultType="Book">
        SELECT *
        FROM book
        ORDER BY book_id
        LIMIT #{offset},#{limit}
    </select>

    <update id="reduceNumber">
        UPDATE book
        SET number = number - 1
        WHERE book_id = #{bookId}
          AND number > 0
    </update>
</mapper>

Copy the code

AppointmentDao.xml

      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cunyu.dao.AppointmentDao">
    <insert id="insertAppointment">
        <! -- ignore primary key conflict, error -->
        INSERT ignore INTO appointment (book_id, student_id) VALUES (#{bookId}, #{studentId})
    </insert>

    <select id="queryByKeyWithBook" resultType="Appointment">
        <! Tell MyBatis to map the result to Appointment and also map the Book property.
        SELECT
        appointment.book_id,
        appointment.student_id,
        appointment.appoint_time,
        book.book_id "book.book_id",
        book.`name` "book.name",
        book.number "book.number"
        FROM
        appointment
        INNER JOIN book ON appointment.book_id = book.book_id
        WHERE
        appointment.book_id = #{bookId}
        AND appointment.student_id = #{studentId}
    </select>
</mapper>
Copy the code

test

After going through the process of preparing the database -> entity class writing -> interface class writing -> Mapper configuration, we were ready to test the modularity to see if our interface was successfully implemented.

BookDaoTest.java

package com.cunyu.dao;

import com.cunyu.pojo.Book;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.List;

/ * * *@author : cunyu
 * @version : 1.0
 * @className : BookDaoTest
 * @date : 2020/7/23 18:02
 * @description: BookDao test class */

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:spring/spring-*.xml")
public class BookDaoTest {
    // Automatic injection
    @Autowired
    private BookDao bookDao;

    @Test
    public void testQueryById(a) {
        int bookId = 1;
        Book book = bookDao.queryById(bookId);
        System.out.println("ID corresponds to the book information:" + book);
    }

    @Test
    public void testQueryAll(a) {
        List<Book> bookList = bookDao.queryAll(0.3);
        System.out.println("All book information:");
        for(Book book : bookList ) { System.out.println(book); }}@Test
    public void testReduceNumber(a) {
        int bookId = 3;
        int update = bookDao.reduceNumber(bookId);
        System.out.println("update = "+ update); }}Copy the code

After running two tests, the database looks like this:

AppointmentDaoTest.java

package com.cunyu.dao;

import com.cunyu.pojo.Appointment;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

/ * * *@author : cunyu
 * @version : 1.0
 * @className : AppointmentDaoTest
 * @date: 2020/7/23 thou *@descriptionAppointmentDao Test */

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:spring/spring-*.xml")
public class AppointmentDaoTest {

    @Autowired
    AppointmentDao appointmentDao;

    @Test
    public void testInsertAppointment(a) {
        int bookId = 2;
        int studentId = 18301333;
        int insert = appointmentDao.insertAppointment(bookId, studentId);
        System.out.println("Insert = " + insert);
    }

    @Test
    public void testQueryByKeyWithBook(a){
        int bookId = 2;
        int studentId = 18301333; Appointment appointment=appointmentDao.queryByKeyWithBook(bookId,studentId); System.out.println(appointment); System.out.println(appointment.getBook()); }}Copy the code

After the appointment, insert records into the Appointment table;

conclusion

So far, the work we have done is summarized as follows:

1. Design database
2. Creating an entity Class
3. Write dao interface classes
4. Write dao interface corresponding mapper and hand it to MyBatis for dynamic implementation
5. Test the DAO interface method implementation

Well, that’s the end of the first phase of the library system, and the next step is to optimize it and code the Service and Controller layers.