当前位置：首页 > news >正文

springboot+mybatis连接数据库实现增删改查功能

news 2025/7/2 8:21:43

springboot+mybatis连接数据库实现增删改查功能

创建表
创建项目
实体类
DAO接口
写sql的XML文件
Service层
Controller
启动类
结果
目录结构
参考博客

创建表

create table user(id int ,name varchar(30),pwd varchar(40)
)

insert into `user` values(2,'hxf','789101'),(3,'hlm','789102'),(4,'hzh','789103'),(1,'hy','789110')

现在的表内容：
在这里插入图片描述

目标的内容：

在这里插入图片描述

创建项目

在这里插入图片描述

我的POM文件依赖

<dependencies><!--mysql--><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.18</version></dependency><!--druid连接池--><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.1.22</version></dependency><!--mybatis--><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.1.4</version></dependency><dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId><version>3.5.6</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.16.18</version><scope>provided</scope></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></dependencies>

yml文件

server:port: 8080
spring:datasource:username: rootpassword: 5****6url: jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8driver-class-name: com.mysql.cj.jdbc.Drivertype: com.alibaba.druid.pool.DruidDataSource
mybatis:#  mapper文件mapper-locations: dao/*Dao.xml

实体类

package com.example.demo.vo;import lombok.AllArgsConstructor;
import lombok.Data;@Data
@AllArgsConstructor
public class User {private int id;private String name;private String pwd;
}

DAO接口

package com.example.demo.dao;
import com.example.demo.vo.User;
import java.util.List;
public interface UserDao {public List<User> selectUser();int insertUser(User user);int deleteUser(String name);int updateUser(String name);
}

写sql的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.example.demo.dao.UserDao"><insert id="insertUser" parameterType="com.example.demo.vo.User">insert into user values(#{id},#{name},#{pwd})</insert><update id="updateUser" parameterType="string">update `user` set pwd = '789100' where NAME = 'hy'</update><delete id="deleteUser" parameterType="string">delete from user where name = #{name}</delete><select id="selectUser" resultType="com.example.demo.vo.User">SELECT * FROM user order by pwd</select>
</mapper>

Service层

package com.example.demo.service;
import com.example.demo.dao.UserDao;
import com.example.demo.vo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.util.List;@Service
public class UserService {@Autowiredprivate UserDao userDao;public List<User> selectUser() {return userDao.selectUser();}public void addUser(User user){userDao.insertUser(user);}public void deleteUser(String name){userDao.deleteUser(name);}public void updateUser(String name){userDao.updateUser(name);}
}

Controller

package com.example.demo.controller;import com.example.demo.service.UserService;
import com.example.demo.vo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;import java.util.Iterator;
import java.util.List;@RestController
@RequestMapping("/test")
public class UserController {@Autowired(required = false)private UserService userService;@GetMapping("/query")public  String query(){String result="";List<User> userList = userService.selectUser();Iterator<User> iterator = userList.iterator();while(iterator.hasNext()){User next = iterator.next();result += next.toString();}return result;//        for (User user : usersList) {
//            result +=user.toString();
//        }
//        return result;}@GetMapping("/add")public String addUser(){userService.addUser(new User(5,"hss","789104"));return "add ok";}@GetMapping("/delete")public String deleteUser(){userService.deleteUser("hzb");return "delete ok";}@GetMapping("/update")public String updateUser(){userService.updateUser("hy");return "update ok";}
}

启动类

package com.example.demo;import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;@SpringBootApplication
@MapperScan("com.example.demo.dao")
public class DemoApplication {public static void main(String[] args) {SpringApplication.run(DemoApplication.class, args);}
}

结果

用postman查询：localhost:8080/test/query
在这里插入图片描述
添加：localhost:8080/test/add

删除:localhost:8080/test/delete

修改:localhost:8080/test/update

最终结果：localhost:8080/test/query