【SpringBoot】使用Spring Boot、MyBatis-Plus和MySQL来实现增删改查操作,并添加自定义SQL查询。
使用Spring Boot、MyBatis-Plus和MySQL来实现增删改查操作,并添加自定义SQL查询。
1. 创建Spring Boot项目
你可以使用Spring Initializr来创建一个新的Spring Boot项目。在选择依赖项时,确保选择以下内容:
- Spring Web
- MyBatis-Plus Boot Starter
- MySQL Driver
- Lombok(用于简化Java代码)
2. 添加依赖
如果你使用的是Maven,确保在pom.xml
中添加以下依赖:
<dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.4.3.4</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency>
</dependencies>
3. 配置数据库连接
在src/main/resources/application.properties
中配置数据库连接信息。
spring.datasource.url=jdbc:mysql://localhost:3306/your_database?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
spring.datasource.username=your_username
spring.datasource.password=your_password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Drivermybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
mybatis.mapper-locations=classpath:mapper/*.xml
4. 创建实体类
在src/main/java/com/example/demo/entity
目录下创建一个实体类,例如User
。
package com.example.demo.entity;import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;@Data
@TableName("user")
public class User {@TableIdprivate Long id;private String name;private Integer age;private String email;
}
5. 创建Mapper接口
在src/main/java/com/example/demo/mapper
目录下创建一个Mapper接口,继承BaseMapper
。这里同时给出使用注解和XML两种方式,二选一。
使用注解的方式
package com.example.demo.mapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demo.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;import java.util.List;@Mapper
public interface UserMapper extends BaseMapper<User> {@Select("SELECT * FROM user WHERE age > #{age}")List<User> selectUsersOlderThan(@Param("age") int age);
}
使用XML文件的方式
首先,在src/main/resources/mapper
目录下创建一个XML文件,例如UserMapper.xml
。
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.example.demo.mapper.UserMapper"><select id="selectUsersOlderThan" resultType="com.example.demo.entity.User">SELECT * FROM user WHERE age > #{age}</select>
</mapper>
然后,在UserMapper
接口中添加与XML文件中定义的方法一致的方法签名。
package com.example.demo.mapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demo.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;import java.util.List;@Mapper
public interface UserMapper extends BaseMapper<User> {List<User> selectUsersOlderThan(@Param("age") int age);
}
6. 创建Service类
在src/main/java/com/example/demo/service
目录下创建一个Service接口。
package com.example.demo.service;import com.baomidou.mybatisplus.extension.service.IService;
import com.example.demo.entity.User;import java.util.List;public interface UserService extends IService<User> {List<User> getUsersOlderThan(int age);
}
在src/main/java/com/example/demo/service/impl
目录下创建Service实现类。
package com.example.demo.service.impl;import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.demo.entity.User;
import com.example.demo.mapper.UserMapper;
import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.util.List;@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {@Autowiredprivate UserMapper userMapper;@Overridepublic List<User> getUsersOlderThan(int age) {return userMapper.selectUsersOlderThan(age);}
}
7. 创建Controller类
在src/main/java/com/example/demo/controller
目录下创建一个Controller类,用于处理HTTP请求。
package com.example.demo.controller;import com.example.demo.entity.User;
import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;import java.util.List;@RestController
@RequestMapping("/users")
public class UserController {@Autowiredprivate UserService userService;@GetMappingpublic List<User> getAllUsers() {return userService.list();}@GetMapping("/{id}")public User getUserById(@PathVariable Long id) {return userService.getById(id);}@PostMappingpublic boolean createUser(@RequestBody User user) {return userService.save(user);}@PutMapping("/{id}")public boolean updateUser(@PathVariable Long id, @RequestBody User user) {user.setId(id);return userService.updateById(user);}@DeleteMapping("/{id}")public boolean deleteUser(@PathVariable Long id) {return userService.removeById(id);}@GetMapping("/older-than/{age}")public List<User> getUsersOlderThan(@PathVariable int age) {return userService.getUsersOlderThan(age);}
}
8. 创建数据库表
确保在MySQL数据库中创建对应的表。例如:
CREATE TABLE `user` (`id` BIGINT NOT NULL AUTO_INCREMENT,`name` VARCHAR(50) NOT NULL,`age` INT NOT NULL,`email` VARCHAR(50),PRIMARY KEY (`id`)
);
9. 启动应用
确保所有配置和代码都正确,然后运行Spring Boot应用。你可以通过以下命令来启动应用:
mvn spring-boot:run
10. 测试API
你现在可以通过HTTP请求来访问和操作用户数据。以下是一些示例请求:
获取所有用户
curl -X GET http://localhost:8080/users
获取特定用户
curl -X GET http://localhost:8080/users/1
创建新用户
curl -X POST http://localhost:8080/users -H "Content-Type: application/json" -d '{"name":"John Doe","age":30,"email":"john.doe@example.com"}'
更新用户
curl -X PUT http://localhost:8080/users/1 -H "Content-Type: application/json" -d '{"name":"Jane Doe","age":25,"email":"jane.doe@example.com"}'
删除用户
curl -X DELETE http://localhost:8080/users/1
获取年龄大于特定值的用户
curl -X GET http://localhost:8080/users/older-than/30
通过这些步骤,你可以在Spring Boot项目中使用MyBatis-Plus和MySQL实现增删改查操作,并添加自定义SQL查询。这样,你不仅可以利用MyBatis-Plus的简洁性,还能灵活地支持复杂的SQL查询。