Springboot+MySQL实现数据库的增删改查

<?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.3.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.leyou.demo</groupId>
    <artifactId>user-service-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>user-service-demo</name>
    <description>Demo project for Spring Boot</description>

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

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</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>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </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>

        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper-spring-boot-starter</artifactId>
            <version>2.1.5</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
        </dependency>
    </dependencies>

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

</project>

3、model实体类：

package com.leyou.userservice.model;

import lombok.Data;

import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;
import java.util.Date;

/**
 * @ClassName: User
 * @Description:
 * @Author: q'j'j
 * @Create: 2020-09-01 11:03
 */
@Table(name = "tb_user")
@Data
public class User implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String username;
    private String password;
    private  Long phone;
    private Date created;
}

4、Dao层，也叫mapper层

package com.leyou.userservice.Dao;

import com.leyou.userservice.model.User;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface UserMapper extends tk.mybatis.mapper.common.Mapper<User>{
}

5、service层

package com.leyou.userservice.Service;

import com.leyou.userservice.Dao.UserMapper;
import com.leyou.userservice.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * @ClassName: UserService
 * @Description:
 * @Author: q'j'j
 * @Create: 2020-09-01 11:33
 */
@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    public User queryById(Long id){
        return this.userMapper.selectByPrimaryKey(id);
    }
}

6、controller层

package com.leyou.userservice.Controller;

import com.leyou.userservice.Service.UserService;
import com.leyou.userservice.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * @ClassName: UserController
 * @Description:
 * @Author: q'j'j
 * @Create: 2020-09-01 11:33
 */
@RestController
@RequestMapping("user")
public class UserController {
    @Autowired
    private UserService userService;

    @GetMapping("/{id}")
    public User queryById(@PathVariable("id") Long id){
        return this.userService.queryById(id);
    }

}

7、配置文件：

server:
  port: 8081

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/yun6?characterEncoding=utf-8&serverTimezone=UTC
    username: root
    password: 1234
    hikari:
      maximum-pool-size: 20
      minimum-idle: 10
mybatis:
  type-aliases-package: com.leyou.userservice.model

8、MySQL数据库
（1）建表

`DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` varchar(32) NOT NULL COMMENT '密码，加密存储',
  `phone` varchar(20) DEFAULT NULL COMMENT '注册手机号',
  `created` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8 COMMENT='用户表';

`
（2）插入数据``

INSERT INTO `tb_user` VALUES ('16', 'huge', '1f8fe7059d1a86060f3a82bfcf2ea06e', '13688666688', '2018-04-29 16:31:35');
INSERT INTO `tb_user` VALUES ('17', 'leyou', '9ff12f364c1e1d576a6c031af17c6a2c', '13800880088', '2018-05-01 09:31:33');
INSERT INTO `tb_user` VALUES ('18', 'hehe', 'ec597888142eb7ae821a6bf3555ffc4f', '16888668866', '2018-05-01 09:35:29');
INSERT INTO `tb_user` VALUES ('19', 'haha', 'b1e2d0f363b8937b72056d39b933eed9', '18999999999', '2018-05-01 09:38:22');
INSERT INTO `tb_user` VALUES ('20', 'heihei', 'bffbff3726148ca20b8e1edbb96e7d02', '13888888888', '2018-05-01 09:38:39');
INSERT INTO `tb_user` VALUES ('21', 'hugege', '0760bf52d18804f9b1ba9ec2526f74db', '13600527634', '2018-05-01 18:23:46');
INSERT INTO `tb_user` VALUES ('27', 'liuyan', 'ee15b6016cd78661056c5701d6f343e7', '17623672016', '2018-05-01 18:25:30');`

9、启动查询
localhost:8081/user/17

Tags 标签

加个好友，技术交流