Spring Boot JSP视图和 MyBatis 对 MySQL 数据库进行 CRUD 操作

项目目录结构

 数据库

create table post
 
(
 
    id int not null primary key auto_increment,
 
    title varchar(500)  null,
 
    content varchar(500)  null,
 
    userId int null,
 
    createDate Timestamp null
 
)

pom.xml

    4.0.0
    
        org.springframework.boot
        spring-boot-starter-parent
        2.6.7
         
    
    com.cos
    crud
    0.1
    crud
    Demo project for Spring Boot

    
        1.8
    

    

        
            org.apache.tomcat
            tomcat-jasper
            9.0.62
        


        
            javax.servlet
            jstl
        
        
            org.springframework.boot
            spring-boot-starter-web
        
        
            org.mybatis.spring.boot
            mybatis-spring-boot-starter
            2.2.2
        

        
            org.springframework.boot
            spring-boot-devtools
            runtime
            true
        
        
            mysql
            mysql-connector-java
            runtime
        
        
            org.projectlombok
            lombok
            true
        
        
            org.springframework.boot
            spring-boot-starter-test
            test
            
                
                    org.junit.vintage
                    junit-vintage-engine
                
            
        
    

    
        
            
                org.springframework.boot
                spring-boot-maven-plugin
            
        
    

application.properties

server.port = 8080
server.servlet.context-path = /

spring.mvc.view.prefix = /WEB-INF/views/
spring.mvc.view.suffix = .jsp

spring.datasource.url = jdbc:mysql://localhost:3306/db?autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
spring.datasource.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.username = root
spring.datasource.password = root
logging.level.com.cos.crud=debug

resources/mapper/post.xml

	
		SELECT * FROM post ORDER BY id DESC
	
	
		SELECT * FROM post WHERe id=#{id}
	
	
		INSERT INTO post(title,content,userId,createDate) 
		VALUES(#{title},#{content},#{userId},now())
	
	
		UPDATE post SET title=#{title}, content=#{content}
		WHERe id=#{id}
	
	
		DELETE FROM post WHERe id=#{id}
	
 
Post.java
 
package com.cos.crud.model;

import java.sql.Timestamp;

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

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Post {
	private int id;
	private String title;
	private String content;
	private int userId; //FK
	private Timestamp createDate;
}
 
PostRepository.java
 
package com.cos.crud.repository;

import java.util.List;

import com.cos.crud.model.Post;

public interface PostRepository {

    List findAll();

    void save(Post post);

    void update(Post post);

    Post findById(int id);

    void delete(int id);
}
 
PostController.java
 
package com.cos.crud.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import com.cos.crud.model.Post;
import com.cos.crud.repository.PostRepository;

@Controller
@RequestMapping("/post")
public class PostController {

    @Autowired
    private PostRepository postReposityry;

    // GET => http://localhost:8080/post
    // GET => http://localhost:8080/post/
    @GetMapping("")
    public String postList(Model model) {
        List posts = postReposityry.findAll();
        model.addAttribute("posts", posts);
        // webapp/WEB-INF/views/post/list.jsp
        return "post/list";
    }

    // POST => http://localhost:8080/post/update
    @PostMapping("/update")
    public String update(Post post) { // param, form
        try {
            postReposityry.update(post);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "redirect:/post";
    }

    // @RequestParam("id")
    // @ResponseBody => JSON
    // POST => http://localhost:8080/post/delete/1
    @GetMapping("/delete/{id}")
    public String delete(@PathVariable int id) {
        try {
            postReposityry.delete(id);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "redirect:/post";
    }

    // POST => http://localhost:8080/post/save
    @PostMapping("/save")
    public String save(Post post) {
        try {
            postReposityry.save(post);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "redirect:/post";
    }

    // POST => http://localhost:8080/post/1
    @GetMapping("/{id}")
    public String post(@PathVariable int id, Model model) {
        try {
            Post post = postReposityry.findById(id);
            model.addAttribute(post);
            return "post/detail";
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "redirect:/post";
    }

    // GET => http://localhost:8080/post/writeForm
    @GetMapping("/writeForm")
    public String writeForm() {
        return "post/writeForm";
    }

    // GET => http://localhost:8080/post/updateForm
    @GetMapping("/updateForm/{id}")
    public String updateForm(@PathVariable int id, Model model) {
        Post post = postReposityry.findById(id);
        model.addAttribute("post", post);
        return "post/updateForm";
    }

}
 
DataSourceConfig.java
 
package com.cos.crud.config;

import javax.sql.DataSource;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Configuration;

@Configuration
public class DataSourceConfig {

	@ConfigurationProperties(prefix="spring.datasource")
	public DataSource dataSource(){
    	return DataSourceBuilder.create().build();
	}
}
 
DataAccessConfig.java
 
package com.cos.crud.config;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

@Configuration
@MapperScan(basePackages = "com.cos.crud.repository")
public class DataAccessConfig {

    @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {

        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();

        sessionFactory.setDataSource(dataSource);
        sessionFactory.setMapperLocations(
        		new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        return sessionFactory.getObject();
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}
 
CrudApplication.java
 
package com.cos.crud;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class CrudApplication {

	public static void main(String[] args) {
		SpringApplication.run(CrudApplication.class, args);
	}

}
 
list.jsp
 
<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>


    
        
        
    
    


        
                

            

                
ID
                
Title
                
CreateDate
            
            

                    
${post.id}
                    
${post.title}
                    
${post.createDate}
                
            

        

        Add

    
 
detail.jsp
 
<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>


    
        
        
    
    
        Title: ${post.title} 

        UserId: ${post.userId} 

        Content: ${post.content} 

        CreateDate: ${post.createDate}
        

        Update

        Delete
    
 
writeForm.jsp
 
<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>


    
        
        
    
    

        

            

            

            
        

    

updateForm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>


    
        
        
    
    

        

            
            

            ${post.content}

            
        

    

运行日志

  .   ____          _            __ _ _
 /\ / ___'_ __ _ _(_)_ __  __ _    
( ( )___ | '_ | '_| | '_ / _` |    
 \/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |___, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::                (v2.6.7)

2022-05-01 20:05:47.571  INFO 10344 --- [  restartedMain] com.cos.crud.CrudApplication             : Starting CrudApplication using Java 1.8.0_202 on x220win10 with PID 10344 (F:SpringBoot-MyBatis-MySql-CRUD-masterSpringBoot-MyBatis-MySql-CRUD-mastertargetclasses started by Administrator in F:SpringBoot-MyBatis-MySql-CRUD-masterSpringBoot-MyBatis-MySql-CRUD-master)
2022-05-01 20:05:47.571 DEBUG 10344 --- [  restartedMain] com.cos.crud.CrudApplication             : Running with Spring Boot v2.6.7, Spring v5.3.19
2022-05-01 20:05:47.571  INFO 10344 --- [  restartedMain] com.cos.crud.CrudApplication             : No active profile set, falling back to 1 default profile: "default"
2022-05-01 20:05:47.938  INFO 10344 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)
2022-05-01 20:05:47.939  INFO 10344 --- [  restartedMain] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2022-05-01 20:05:47.940  INFO 10344 --- [  restartedMain] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.62]
2022-05-01 20:05:48.066  INFO 10344 --- [  restartedMain] org.apache.jasper.servlet.TldScanner     : At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
2022-05-01 20:05:48.069  INFO 10344 --- [  restartedMain] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2022-05-01 20:05:48.070  INFO 10344 --- [  restartedMain] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 497 ms
2022-05-01 20:05:48.243  INFO 10344 --- [  restartedMain] o.s.b.d.a.OptionalLiveReloadServer       : LiveReload server is running on port 35729
2022-05-01 20:05:48.254  INFO 10344 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2022-05-01 20:05:48.259  INFO 10344 --- [  restartedMain] com.cos.crud.CrudApplication             : Started CrudApplication in 0.732 seconds (JVM running for 1709.904)
2022-05-01 20:05:48.261  INFO 10344 --- [  restartedMain] .ConditionEvaluationDeltaLoggingListener : Condition evaluation unchanged
2022-05-01 20:05:53.865  INFO 10344 --- [nio-8080-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring DispatcherServlet 'dispatcherServlet'
2022-05-01 20:05:53.866  INFO 10344 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : Initializing Servlet 'dispatcherServlet'
2022-05-01 20:05:53.867  INFO 10344 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : Completed initialization in 1 ms
2022-05-01 20:05:53.871  INFO 10344 --- [nio-8080-exec-1] com.zaxxer.hikari.HikariDataSource       : HikariPool-3 - Starting...
2022-05-01 20:05:53.879  INFO 10344 --- [nio-8080-exec-1] com.zaxxer.hikari.HikariDataSource       : HikariPool-3 - Start completed.
2022-05-01 20:05:53.884 DEBUG 10344 --- [nio-8080-exec-1] c.c.c.repository.PostRepository.findAll  : ==>  Preparing: SELECT * FROM post ORDER BY id DESC
2022-05-01 20:05:53.899 DEBUG 10344 --- [nio-8080-exec-1] c.c.c.repository.PostRepository.findAll  : ==> Parameters: 
2022-05-01 20:05:53.915 DEBUG 10344 --- [nio-8080-exec-1] c.c.c.repository.PostRepository.findAll  : <==      Total: 1
2022-05-01 20:06:14.389 DEBUG 10344 --- [nio-8080-exec-3] c.c.crud.repository.PostRepository.save  : ==>  Preparing: INSERT INTO post(title,content,userId,createDate) VALUES(?,?,?,now())
2022-05-01 20:06:14.392 DEBUG 10344 --- [nio-8080-exec-3] c.c.crud.repository.PostRepository.save  : ==> Parameters: 测试(String), 测试(String), 0(Integer)
2022-05-01 20:06:14.409 DEBUG 10344 --- [nio-8080-exec-3] c.c.crud.repository.PostRepository.save  : <==    Updates: 1
2022-05-01 20:06:14.416 DEBUG 10344 --- [nio-8080-exec-4] c.c.c.repository.PostRepository.findAll  : ==>  Preparing: SELECT * FROM post ORDER BY id DESC
2022-05-01 20:06:14.417 DEBUG 10344 --- [nio-8080-exec-4] c.c.c.repository.PostRepository.findAll  : ==> Parameters: 
2022-05-01 20:06:14.418 DEBUG 10344 --- [nio-8080-exec-4] c.c.c.repository.PostRepository.findAll  : <==      Total: 2
2022-05-01 20:08:01.636 DEBUG 10344 --- [nio-8080-exec-6] c.c.c.r.PostRepository.findById          : ==>  Preparing: SELECt * FROM post WHERe id=?
2022-05-01 20:08:01.636 DEBUG 10344 --- [nio-8080-exec-6] c.c.c.r.PostRepository.findById          : ==> Parameters: 7(Integer)
2022-05-01 20:08:01.638 DEBUG 10344 --- [nio-8080-exec-6] c.c.c.r.PostRepository.findById          : <==      Total: 1
2022-05-01 20:08:10.831 DEBUG 10344 --- [nio-8080-exec-7] c.c.c.r.PostRepository.findById          : ==>  Preparing: SELECt * FROM post WHERe id=?
2022-05-01 20:08:10.831 DEBUG 10344 --- [nio-8080-exec-7] c.c.c.r.PostRepository.findById          : ==> Parameters: 7(Integer)
2022-05-01 20:08:10.832 DEBUG 10344 --- [nio-8080-exec-7] c.c.c.r.PostRepository.findById          : <==      Total: 1
2022-05-01 20:08:26.843 DEBUG 10344 --- [nio-8080-exec-8] c.c.c.repository.PostRepository.update   : ==>  Preparing: UPDATE post SET title=?, content=? WHERe id=?
2022-05-01 20:08:26.844 DEBUG 10344 --- [nio-8080-exec-8] c.c.c.repository.PostRepository.update   : ==> Parameters: 测试777(String), 测试777(String), 7(Integer)
2022-05-01 20:08:26.863 DEBUG 10344 --- [nio-8080-exec-8] c.c.c.repository.PostRepository.update   : <==    Updates: 1
2022-05-01 20:08:26.870 DEBUG 10344 --- [nio-8080-exec-8] c.c.c.repository.PostRepository.findAll  : ==>  Preparing: SELECT * FROM post ORDER BY id DESC
2022-05-01 20:08:26.870 DEBUG 10344 --- [nio-8080-exec-8] c.c.c.repository.PostRepository.findAll  : ==> Parameters: 
2022-05-01 20:08:26.872 DEBUG 10344 --- [nio-8080-exec-8] c.c.c.repository.PostRepository.findAll  : <==      Total: 2
2022-05-01 20:08:41.819 DEBUG 10344 --- [nio-8080-exec-9] c.c.c.r.PostRepository.findById          : ==>  Preparing: SELECt * FROM post WHERe id=?
2022-05-01 20:08:41.820 DEBUG 10344 --- [nio-8080-exec-9] c.c.c.r.PostRepository.findById          : ==> Parameters: 6(Integer)
2022-05-01 20:08:41.821 DEBUG 10344 --- [nio-8080-exec-9] c.c.c.r.PostRepository.findById          : <==      Total: 1
2022-05-01 20:08:43.610 DEBUG 10344 --- [io-8080-exec-10] c.c.c.repository.PostRepository.delete   : ==>  Preparing: DELETE FROM post WHERe id=?
2022-05-01 20:08:43.610 DEBUG 10344 --- [io-8080-exec-10] c.c.c.repository.PostRepository.delete   : ==> Parameters: 6(Integer)
2022-05-01 20:08:43.629 DEBUG 10344 --- [io-8080-exec-10] c.c.c.repository.PostRepository.delete   : <==    Updates: 1
2022-05-01 20:08:43.636 DEBUG 10344 --- [nio-8080-exec-1] c.c.c.repository.PostRepository.findAll  : ==>  Preparing: SELECT * FROM post ORDER BY id DESC
2022-05-01 20:08:43.636 DEBUG 10344 --- [nio-8080-exec-1] c.c.c.repository.PostRepository.findAll  : ==> Parameters: 
2022-05-01 20:08:43.639 DEBUG 10344 --- [nio-8080-exec-1] c.c.c.repository.PostRepository.findAll  : <==      Total: 1