Springboot数据库增删改查

 application.properties文件

spring.datasource.url=jdbc:mysql://localhost:3306/dbname
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.database=mysql
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update

相关依赖

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

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.32</version>
        </dependency>

目录结构

 数据库结构

 

1.在com.example目录下创建一个pojo包并创建User实体类 

package com.example.db2demo.pojo;

import jakarta.persistence.*;

@Entity
@Table(name = "user")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;
    @Column(name = "username",nullable = false)
    private String username;
    @Column(name = "password", nullable = false)
    private String password;
    @Transient
    private String remark;

    public User(String username, String password) {
        this.username = username;
        this.password = password;
    }

    public User() {
        return;
    }

    public User(int id, String username, String password) {
        this.id = id;
        this.username = username;
        this.password = password;
    }


    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    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 getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }
    @Override
    public String toString() {
        return "User{"+"id="+id + ", username='"+username + '\''+", password='"+password + '\''+", remark='"+remark + '\''+'}';
    }
}

2.在com.example目录下创建一个repository包并创建一个UserReposity接口

package com.example.db2demo.repository;

import com.example.db2demo.pojo.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

public interface UserReposity extends JpaRepository<User,Integer> {
    @Query(value = "SELECT * FROM user u where u.id = ?1",nativeQuery = true)
    //查询用户id
    public User findUserById(int id);
    //根据用户名查询
    @Query(value = "SELECT * FROM user where user.username=?1",nativeQuery = true)
    public User findUserByName(String username);
    //查找id最大的用户
    @Query(value = "SELECT * FROM user WHERE id = (SELECT MAX(id) FROM user )",nativeQuery = true)
    public User findMaxIdUser();
}

3.在com.example目录下创建一个controller包并创建实体类

package com.example.db2demo.controller;

import com.example.db2demo.pojo.User;
import com.example.db2demo.repository.UserReposity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.SQLOutput;
import java.util.List;
import java.util.Optional;

@RestController
public class Usercontroller {
    @Autowired
    UserReposity reposity;
    @GetMapping("/saveUser")
    public String saveUser() {
        reposity.save(new User("ljn","123456"));
        return "保存成功";
    }
    @GetMapping("/addUser")
    public String addUser(String username,String password) {
        reposity.save(new User(username,password));
        return "添加成功";
    }
    @GetMapping("/UpdateUser")
    public String UpdateUser(int id,String username,String password) {
        User users = new User(id,username,password);
        reposity.save(users);
        return "更新成功";
    }
    @GetMapping("/deleteUser")
    public String deleteUser(int id) {
        reposity.deleteById(id);
        return "删除成功";
    }

    @GetMapping("/getUserById")
    public Optional<User> getUserById(int id) {
        Optional<User> users = reposity.findById(id);
        System.out.println("查询成功");
        System.out.println(users);
        return users;
    }
    @GetMapping("/getAllUser")
    public List<User> getAllUser() {
        List<User> users = reposity.findAll();
        if (users!= null) {
            System.out.println("所有User对象列表为");
            for (User user:users) {
                System.out.println(user);
            }
        }
        return users;
    }
    @GetMapping("/findUserById")
    public User findUserById(int id) {
        User user = reposity.findUserById(id);
        System.out.println("查询成功");
        System.out.println(user);
        return user;
    }
    @GetMapping("/findUserByName")
    public User findUserByName(String username) {
        User user = reposity.findUserByName(username);
        System.out.println("查询成功");
        System.out.println(user);
        return user;
    }
    @GetMapping("/findMaxIdUser")
    public User findMaxIdUser() {
        User user = reposity.findMaxIdUser();
        System.out.println("查询成功");
        System.out.println(user);
        return user;
    }
}

最后就可以实现增删改查了。