SpringBoot 增删改查项目

1 pom.xml中导入依赖

 <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--		<dependency>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-devtools</artifactId>
                    <optional>true</optional>
                </dependency>-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <dependency>
            <groupId>org.webjars</groupId>
            <artifactId>jquery</artifactId>
            <version>3.3.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.47</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
            <version>8.0.30</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.9</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.15</version>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>RELEASE</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>RELEASE</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <dependency>
            <groupId>org.webjars</groupId>
            <artifactId>jquery</artifactId>
            <version>3.3.1</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.1</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.9</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.15</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

配置数据源(在application.yaml)

spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/boot_demo
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

创建数据的配置类

@Configuration
public class DruidConfig {
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource dataSource(){
        return new DruidDataSource();
    }

2 建立数据库

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for my_user
-- ----------------------------
DROP TABLE IF EXISTS `my_user`;
CREATE TABLE `my_user`  (
  `pid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `addr` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  PRIMARY KEY (`pid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

3 在templates文件夹下，建立首页,增加,更新网页

(1)首页 index

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Title</title>
</head>
<body>
<frameset cols="*" rows="136, *" id="frame_main"  border="0">
    <frame src="header" noresize="noresize" name="header">

    <frameset cols="240, *">
        <frame th:src="@{/menu}" name="menu" />
        <frame th:src="@{/main}" name="main">
    </frameset>
</frameset>
<a th:href="@{/add}">添加</a>
<table class="mt" align="center">
    <tr >
        <td >用户名</td>
        <td >密码</td>
        <td >地址</td>
        <td >生日</td>
        <td >操作</td>
    </tr>
    <form th:action="@{/find}" method="post">
        用户名: <input type="text" name="username"></br>
        密码: <input type="text" name="password"></br>
        <input type="submit" value="查询">
    </form>
    <tbody>
    <tr th:each="user : ${users}">
        <td th:text="${user.username}"></td>
        <td th:text="${user.password}"></td>
        <td th:text="${user.addr}"></td>
        <td th:text="${#calendars.format(user.birth,'yyyy-MM-dd')}"></td>
        <td>
            <a th:href="@{/getUser(pid=${user.pid})}">修改</a>
            <a th:href="@{/delete(pid=${user.pid})}">删除</a>
        </td>
    </tr>
    </tbody>
</table>

</body>
</html>

(2)添加

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<form th:action="@{/save}" method="post">
    <table class="mt" align="center">
        <tr >
            <td >用户名</td>
            <td><input name="username" type="text"/></td>
        </tr>
        <tr >
            <td >密码</td>
            <td><input name="password" type="password"/></td>

        </tr>
        <tr >
            <td >地址</td>
            <td><input name="addr" type="text"/></td>
        </tr>
        <tr >
            <td >生日</td>
            <td><input name="birth" type="text"/></td>

        </tr>
        <tr >
            <td >性别</td>
            <td><input name="gender" type="text"/></td>
        </tr>
        <tr >
            <td ></td>
            <td><input  type="submit"/></td>
        </tr>
    </table>
</form>

</body>
</html>

(3) 更新

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<form th:action="@{/update}" method="post">
    <input type="hidden" name="pid" th:value="${user.pid}"/>
    <table class="mt" align="center">
        <tr >
            <td >用户名</td>
            <td><input name="username" type="text" th:value="${user.username}"/></td>
        </tr><tr >
        <td >密码</td>
        <td><input name="password" type="password" th:value="${user.password}"/></td>
    </tr><tr >
        <td >地址</td>
        <td><input name="addr" type="text" th:value="${user.addr}"/></td>
    </tr>
        <tr >
            <td >性别</td>
            <td><input name="gender" type="text" th:value="${user.gender}"/></td>
        </tr>
        <tr >
            <td >生日</td>
            <td><input name="birth" type="text" th:value="${#calendars.format(user.birth, 'yyyy-MM-dd')}"/></td>
        </tr><tr >
        <td ></td>
        <td><input  type="submit"/></td>
    </tr>
    </table>
</form>

</body>
</html>

3 建立实体类 MyUser

import java.util.Date;

public class MyUser {
    private int pid;
    private String username;
    private String password;
    private String addr;
    private int gender;
    private Date birth;

    public MyUser() {
    }

    public MyUser(int pid, String username, String password, String addr, int gender, Date birth) {
        this.pid = pid;
        this.username = username;
        this.password = password;
        this.addr = addr;
        this.gender = gender;
        this.birth = birth;
    }

    public int getPid() {
        return pid;
    }

    public void setPid(int pid) {
        this.pid = pid;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getAddr() {
        return addr;
    }

    public void setAddr(String addr) {
        this.addr = addr;
    }

    public int getGender() {
        return gender;
    }

    public void setGender(int gender) {
        this.gender = gender;
    }

    public Date getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }

    @Override
    public String toString() {
        return "MyUser{" +
                "pid=" + pid +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", addr='" + addr + '\'' +
                ", gender=" + gender +
                ", birth=" + birth +
                '}';
    }
}

4 建立Service以及Impl接口,Mapper方法

Service层

package com.qcby.Service;

import com.qcby.model.MyUser;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;


public interface MyUserService {

    public  void insert(MyUser user) ;

    public  void update(MyUser user) ;

    public List<MyUser> selectUsers();

    public MyUser selectUsersById(Map<String, String> map);

    public MyUser selectUsersById1(int userId);

    public  void delete(int pid) ;


}

Impl层

package com.qcby.Service.Impl;

import com.qcby.Mapper.MyUserMapper;
import com.qcby.Service.MyUserService;
import com.qcby.model.MyUser;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

@Service
public class MyUserServiceImpl implements MyUserService {

    @Autowired
    private MyUserMapper myUserMapper;

    @Override
    public void insert(MyUser user) {
        myUserMapper.insert(user);
    }

    @Override
    public void update(MyUser user) {
        myUserMapper.update(user);
    }

    @Override
    public List<MyUser> selectUsers() {
        return myUserMapper.selectUsers();
    }

    @Override
    public MyUser selectUsersById(Map<String, String> map) {
        return myUserMapper.selectUsersById(map);
    }

    @Override
    public MyUser selectUsersById1(int userId) {
        return myUserMapper.selectUsersById1(userId);
    }

    @Override
    public void delete(int pid) {
        myUserMapper.delete(pid);
    }
}

Mapper实现层

package com.qcby.Mapper;

import com.qcby.model.MyUser;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface MyUserMapper {


    @Options(useGeneratedKeys = true, keyProperty = "pid")
    @Insert("insert into my_user(pid, username, password, addr, gender, birth)values(" +
            "#{pid}, #{username}, #{password}, #{addr}, #{gender}, #{birth})")
    public  void insert(MyUser user) ;

    @Select("select * from my_user")
    public List<MyUser> selectUsers();

    @Select("select * from my_user t where t.username = #{username} and t.password = #{password}")
    public MyUser selectUsersById(Map<String, String> map);


    @Select("select * from my_user t where t.pid = #{pid}")
    public MyUser selectUsersById1(int userId);

    @Update("update  my_user set " +
            "username = #{username}," +
            "password=#{password}, " +
            "addr= #{addr}, " +
            "gender=#{gender}, " +
            "birth=#{birth} " +
            "where pid = #{pid}")
    public  void update(MyUser user) ;

    @Delete("delete from my_user  where pid = #{pid}")
    public  void delete(int pid) ;

}

5 在Controller层创建控制器

(1) 首页展示信息

 @Autowired
    private MyUserService myUserService;

    @GetMapping("/index")
    public String index(Model model){
        List<MyUser> myUserList=myUserService.selectUsers();
        model.addAttribute("users",myUserList);
        return "index";
    }

(2)添加功能

 @PostMapping("/save")
    public String add(Model model, String username, String password, String addr, int gender, Date birth){
        MyUser myUser=new MyUser();
        myUser.setUsername(username);
        myUser.setBirth(birth);
        myUser.setGender(gender);
        myUser.setAddr(addr);
        myUser.setPassword(password);
        myUserService.insert(myUser);
        return "redirect:/index";

    }

(3) 更新功能

 @GetMapping("getUser")
    public String getUser(int pid,Model model){
        MyUser myUser=myUserService.selectUsersById1(pid);
        model.addAttribute("user",myUser);
        return "update";
    }
    @PostMapping("update")
    public String update(Model model ,MyUser myUser){
        myUserService.update(myUser);
        model.addAttribute("user",myUser);
        return "redirect:/index";
    }

(4)删除

@GetMapping("delete")
    public String delete(Model model,int pid){
        myUserService.delete(pid);
        List<MyUser> myUserList=myUserService.selectUsers();
        model.addAttribute("users",myUserList);
        return "redirect:/index";
    }

(5)查询

 @ResponseBody
    @PostMapping("find")
    public MyUser find(String username,String password){
        Map<String,String> map=new HashMap<>();
        map.put("username",username);
        map.put("password",password);
        MyUser myUser=myUserService.selectUsersById(map);
        return myUser;
    }

PS：记得在application.properties中写上端口号