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

allway2

已于 2022-05-01 20:11:50 修改

阅读量840

点赞数

文章标签： mysql 数据库 spring boot

于 2022-05-01 19:49:37 首次发布

本文链接：https://blog.csdn.net/allway2/article/details/124531365

版权

项目目录结构

数据库

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

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.7</version>
        <relativePath /> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.cos</groupId>
    <artifactId>crud</artifactId>
    <version>0.1</version>
    <name>crud</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>

        <dependency>
            <groupId>org.apache.tomcat</groupId>
            <artifactId>tomcat-jasper</artifactId>
            <version>9.0.62</version>
        </dependency>


        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

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

<?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.cos.crud.repository.PostRepository">
	<select id="findAll" resultType="com.cos.crud.model.Post">
		SELECT * FROM post ORDER BY id DESC
	</select>
	<select id="findById" resultType="com.cos.crud.model.Post">
		SELECT * FROM post WHERE id=#{id}
	</select>
	<insert id="save">
		INSERT INTO post(title,content,userId,createDate) 
		VALUES(#{title},#{content},#{userId},now())
	</insert>
	<update id="update">
		UPDATE post SET title=#{title}, content=#{content}
		WHERE id=#{id}
	</update>
	<delete id="delete">
		DELETE FROM post WHERE id=#{id}
	</delete>
</mapper>

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<Post> 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<Post> 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"%>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Insert title here</title>
    </head>
    <body>


        <table border="1">
            <tr>
                <th>ID</th>
                <th>Title</th>
                <th>CreateDate</th>
            </tr>
            <c:forEach var="post" items="${posts}">
                <tr>
                    <td>${post.id}</td>
                    <td><a href="/post/${post.id}">${post.title}</a></td>
                    <td>${post.createDate}</td>
                </tr>
            </c:forEach>

        </table>

        <a href="/post/writeForm">Add</a>

    </body>
</html>

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"%>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Insert title here</title>
    </head>
    <body>
        Title: ${post.title} <br/>
        UserId: ${post.userId} <br/>
        Content: ${post.content} <br/>
        CreateDate: ${post.createDate}
        <br/>
        <a href="/post/updateForm/${post.id}">Update</a><br/>
        <a href="/post/delete/${post.id}">Delete</a>
    </body>
</html>

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"%>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Insert title here</title>
    </head>
    <body>

        <form action="/post/save" method="POST">
            <input type="text" name="title" placeholder="title"/><br/>
            <textarea rows="5" cols="20" name="content"></textarea><br/>
            <input type="submit" value="Save"/>
        </form>

    </body>
</html>

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"%>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Insert title here</title>
    </head>
    <body>

        <form action="/post/update" method="POST">
            <input type="hidden" name="id" value="${post.id}">
            <input type="text" value="${post.title}" name="title" placeholder="title"/><br/>
            <textarea rows="5" cols="20" name="content">${post.content}</textarea><br/>
            <input type="submit" value="Update"/>
        </form>

    </body>
</html>

运行日志



  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: 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-master\SpringBoot-MyBatis-MySql-CRUD-master\target\classes started by Administrator in F:\SpringBoot-MyBatis-MySql-CRUD-master\SpringBoot-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