Demand analysis – > function design – > database design

Environmental requirements

environment

• IDEA
• MySQL 5.7.19
• Tomcat 9
• Maven 3.6
  

requirement

• Proficient in MySQL database
• Familiar with spring, Java Web and mybatis
• Simple front-end knowledge
  

Database environment

CREATE DATABASE `ssm`CHARACTER SET utf8 COLLATE utf8_general_ci; 

USE `ssm`; 
 
CREATE TABLE `ssm`.` books` ( `book_id` INT(10) NOT NULL AUTO_ Increment comment 'Book ID', ` book_ Name ` varchar (100) not null comment 'book name', ` book_ Count ` int (10) not null comment 'quantity', ` book_ Detail ` varchar (1000) not null comment 'description', primary key (` book_id ') engine = InnoDB charset = utf8 collate = utf8_ general_ ci; 

INSERT INTO `ssm`.` Books ` (` book_name `, ` book_count `, ` book_detail `) values ('java ',' 1 ',' from getting started to giving up '); 
INSERT INTO `ssm`.` Books ` (` book_name `, ` book_count `, ` book_detail `) values ('mysql ',' 11 ',' from deleting the library to running '); 
INSERT INTO `ssm`.`books` (`book_name`, `book_count`, `book_detail`) VALUES ('Linux', '5', 'j'); 

SELECT * FROM books LIMIT 0, 1000;

Basic environment construction

Create Maven project

pom. XML add dependency, add resource export

4.0.0

    com.qing
    ssm
    1.0-SNAPSHOT

    
        
        
            junit
            junit
            4.13.2
        
        
        
            org.projectlombok
            lombok
            1.18.20
        
        
        
            mysql
            mysql-connector-java
            5.1.49
        
        
        
            com.mchange
            c3p0
            0.9.5.5
        
        
        
            javax.servlet
            servlet-api
            2.5
        
        
        
            javax.servlet.jsp
            jsp-api
            2.2
        
        
        
            javax.servlet
            jstl
            1.2
        
        
        
            org.mybatis
            mybatis
            3.5.7
        
        
        
            org.mybatis
            mybatis-spring
            2.0.6
        
        
        
            org.springframework
            spring-webmvc
            5.3.9
        
        
        
            org.springframework
            spring-jdbc
            5.3.9
        
        
        
            org.aspectj
            aspectjweaver
            1.9.7
            runtime
        

    

    
    
        
            
                src/main/resources
                
                    **/*.properties
                    **/*.xml
                
                true
            
            
                src/main/java
                
                    **/*.properties
                    **/*.xml
                
                true

Idea connection database

Submit project to Git

[email protected]:wl3pbzhyq/ssm.git

Create base package

create profile

db.properties

driver=com.mysql.jdbc.Driver
#If you use MySQL 8 0 +, add a time zone configuration: & servertimezone = Asia / Shanghai
url=jdbc:mysql://localhost:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=UTF-8
username=root
password=123456

mybatis-config.xml

applicationContext.xml

Create entity class

package com.qing.pojo;

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

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Books {
    private int bookId;
    private String bookName;
    private int bookCount;
    private String bookDetail;
}

Create mapper interface and mapper xml

package com.qing.dao;

import com.qing.pojo.Books;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface BooksMapper {

    /**
     *New book
     * @param books
     * @return
     */
    int add(Books books);

    /**
     *Delete book by ID
     * @param bookId
     * @return
     */
    int deleteById(@Param("bookId") int bookId);

    /**
     *Amendment
     * @param books
     * @return
     */
    int update(Books books);

    /**
     *Get book by ID
     * @param bookId
     * @return
     */
    Books getBooksById(@Param("bookId") int bookId);

    /**
     *Get all books
     * @return
     */
    List listBooks();
}

insert into ssm.books(book_name, book_count, book_detail)
        values (#{bookName},#{bookCount},#{bookDetail})
    
    
    
        delete from ssm.books where book_id = #{bookId}
    
    
    
        update ssm.books
        set book_name=#{bookName},book_count=#{bookCount},book_detail=#{bookDetail}
        where book_id=#{bookId}
    
    
    
        select * from ssm.books
        where book_id=#{bookId}
    
    
    
        select * from ssm.books

Register mapper to mybatis config xml

Create service interface and implementation class

package com.qing.service;

import com.qing.pojo.Books;

import java.util.List;

public interface BooksService {
    /**
     *New book
     * @param books
     * @return
     */
    int add(Books books);

    /**
     *Delete book by ID
     * @param bookId
     * @return
     */
    int deleteById(int bookId);

    /**
     *Amendment
     * @param books
     * @return
     */
    int update(Books books);

    /**
     *Get book by ID
     * @param bookId
     * @return
     */
    Books getBooksById(int bookId);

    /**
     *Get all books
     * @return
     */
    List listBooks();
}

package com.qing.service;

import com.qing.dao.BooksMapper;
import com.qing.pojo.Books;

import java.util.List;

public class BooksServiceImpl implements BooksService {

    //Service layer adjustment, combination Dao
    private BooksMapper booksMapper;

    public void setBooksMapper(BooksMapper booksMapper) {
        this.booksMapper = booksMapper;
    }

    public int add(Books books) {
        return booksMapper.add(books);
    }

    public int deleteById(int bookId) {
        return booksMapper.deleteById(bookId);
    }

    public int update(Books books) {
        return booksMapper.update(books);
    }

    public Books getBooksById(int bookId) {
        return booksMapper.getBooksById(bookId);
    }

    public List listBooks() {
        return booksMapper.listBooks();
    }
}

Spring configuration file

Create the configuration file spring Dao xml

Create the configuration file spring service xml

Create the configuration file spring MVC xml

applicationContext. Import spring-dao.xml xml spring-service. xml spring-mvc. xml

Project add Web Framework Support

Configure web xml

spingmvc
        org.springframework.web.servlet.DispatcherServlet
        
            contextConfigLocation
            classpath:applicationContext.xml
        
        1
    
    
        spingmvc
        /
    
    
    
    
        encodingFilter
        org.springframework.web.filter.CharacterEncodingFilter
        
            encoding
            utf-8
        
    
    
        encodingFilter
        /*
    

    
        15

Create controller

package com.qing.controller;

import com.qing.pojo.Books;
import com.qing.service.BooksService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;

import java.util.List;

@Controller
@RequestMapping("/books")
public class BooksController {

    @Autowired
    @Qualifier("BookServiceImpl")
    private BooksService booksService;

    @RequestMapping("/listBooks")
    public String listBooks(Model model) {
        List list = booksService.listBooks();
        model.addAttribute("list",list);
        return "listBooks";
    }
}

Create home and library pages

home page
  
  
  Enter the library

Library


Library

    
        
            
                Book number
                Book name
                Number of books
                Book description
            
        
        
            
                
                    ${book.bookId}
                    ${book.bookName}
                    ${book.bookCount}
                    ${book.bookDetail}

Create lib under WEB-INF in artifacts and select jar

Configure Tomcat and test