Mi información de contacto
Correo[email protected]
2024-07-12
한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina
En el desarrollo de Java, MyBatis, como excelente marco de capa de persistencia, se utiliza ampliamente en varios proyectos con su configuración simple y potentes funciones. Este artículo presentará en detalle los cuatro métodos para escribir SQL en la capa del asignador en MyBatis y los explicará en detalle a través de códigos de implementación específicos, tomando la tabla de usuarios como ejemplo.
MyBatis es un marco semi-ORM que asigna SQL a archivos XML o anotaciones, simplificando las operaciones de la base de datos. Admite SQL personalizado, procedimientos almacenados y mapeo avanzado.
El siguiente es un ejemplo de una estructura de proyecto simple basada en Spring Boot y MyBatis:
springboot-template-mybatis
│ pom.xml
│
├─src
│ └─main
│ ├─java
│ │ └─com
│ │ └─zcs
│ │ │ Application.java
│ │ │
│ │ ├─common
│ │ │ Result.java
│ │ │
│ │ ├─controller
│ │ │ UserControllerByAnnotation.java
│ │ │ UserControllerByMap.java
│ │ │ UserControllerByProvider.java
│ │ │ UserControllerByXml.java
│ │ │
│ │ ├─entity
│ │ │ UserEntity.java
│ │ │
│ │ ├─mapper
│ │ │ │ UserMapperByAnnotation.java
│ │ │ │ UserMapperByMap.java
│ │ │ │ UserMapperByProvider.java
│ │ │ │ UserMapperByXml.java
│ │ │ │
│ │ │ └─provider
│ │ │ UserProvider.java
│ │ │
│ │ └─service
│ │ UserService.java
│ │ UserServiceByMap.java
│ │
│ └─resources
│ │ application.yml
│ │ log4j.properties
│ │
│ └─com
│ └─zcs
│ └─mapper
│ UserMapperByXml.xml
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
import lombok.Data;
@Data
public class UserEntity {
private Integer id;
private String name;
private Integer age;
}
import com.zcs.entity.UserEntity;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface UserMapperByXml {
List<UserEntity> selectAllUsersByXml();
boolean insertUserByXml(UserEntity user);
boolean updateUserByXml(UserEntity user);
boolean deleteUserByXml(Integer id);
}
<?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.zcs.mapper.UserMapperByXml">
<!-- 查询所有用户 -->
<select id="selectAllUsersByXml">
SELECT *
FROM user
</select>
<!-- 插入用户 -->
<insert id="insertUserByXml">
INSERT INTO user (id, name, age)
VALUES (#{id}, #{name}, #{age})
</insert>
<!-- 更新用户 -->
<update id="updateUserByXml">
UPDATE user
SET name = #{name},
age = #{age}
WHERE id = #{id}
</update>
<!-- 删除用户 -->
<delete id="deleteUserByXml">
DELETE
FROM user
WHERE id = #{id}
</delete>
</mapper>
import com.zcs.common.Result;
import com.zcs.entity.UserEntity;
import com.zcs.mapper.UserMapperByXml;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import java.util.List;
@Slf4j
@RestController
@RequestMapping("/usersByXml")
public class UserControllerByXml {
@Resource
private UserMapperByXml userMapperByXml;
@GetMapping
public Result<List<UserEntity>> selectAllUsersByAnnotation() {
List<UserEntity> userEntities = userMapperByXml.selectAllUsersByXml();
return Result.success("successfully", userEntities);
}
@PostMapping
public Result<String> insertUserByAnnotation(@RequestBody UserEntity user) {
boolean b = userMapperByXml.insertUserByXml(user);
if (b) {
return Result.success("successfully", "数据插入成功");
}
return Result.error(500, "数据插入失败");
}
@PutMapping
public Result<String> updateUserByAnnotation(@RequestBody UserEntity user) {
boolean b = userMapperByXml.updateUserByXml(user);
if (b) {
return Result.success("successfully", "数据更新成功");
}
return Result.error(500, "数据更新失败");
}
@DeleteMapping
public Result<String> deleteUserByAnnotation(@RequestParam Integer id) {
boolean b = userMapperByXml.deleteUserByXml(id);
if (b) {
return Result.success("successfully", "数据删除成功");
}
return Result.error(500, "数据删除失败");
}
}
import com.zcs.entity.UserEntity;
import org.apache.ibatis.annotations.*;
import java.util.List;
@Mapper
public interface UserMapperByAnnotation {
@Select("SELECT * FROM user")
List<UserEntity> getAllUser();
@Insert("INSERT INTO user (id, name, age) VALUES (#{id}, #{name}, #{age})")
@Options(useGeneratedKeys = true, keyProperty = "id")
boolean insertUser(UserEntity user);
@Update("UPDATE user SET name = #{name}, age = #{age} WHERE id = #{id}")
boolean updateUser(UserEntity user);
@Delete("DELETE FROM user WHERE id = #{id}")
boolean deleteUserById(Integer id);
}
import com.zcs.entity.UserEntity;
import com.zcs.mapper.UserMapperByAnnotation;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
@Service
public class UserServiceByAnnotation {
@Resource
private UserMapperByAnnotation userMapperByAnnotation;
public List<UserEntity> getAllUser() {
return userMapperByAnnotation.getAllUser();
}
public boolean insertUser(UserEntity user) {
return userMapperByAnnotation.insertUser(user);
}
public boolean updateUser(UserEntity user) {
return userMapperByAnnotation.updateUser(user);
}
public boolean deleteUserById(Integer id) {
return userMapperByAnnotation.deleteUserById(id);
}
}
import com.zcs.common.Result;
import com.zcs.entity.UserEntity;
import com.zcs.service.UserServiceByAnnotation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import java.util.List;
@Slf4j
@RestController
@RequestMapping("/usersByAnnotation")
public class UserControllerByAnnotation {
@Resource
private UserServiceByAnnotation userServiceByAnnotation;
@GetMapping
public Result<List<UserEntity>> getAllUser() {
List<UserEntity> allUserM = userServiceByAnnotation.getAllUser();
return Result.success("success", allUserM);
}
@PostMapping
public Result<String> insertUser(@RequestBody UserEntity user) {
boolean b = userServiceByAnnotation.insertUser(user);
if (b) {
return Result.success("success", "数据插入成功");
}
return Result.error(500, "数据插入失败");
}
@PutMapping
public Result<String> updateUser(@RequestBody UserEntity user) {
boolean b = userServiceByAnnotation.updateUser(user);
if (b) {
return Result.success("success", "数据更新成功");
}
return Result.error(500, "数据更新失败");
}
@DeleteMapping
public Result<String> deleteUserById(@RequestParam Integer id) {
boolean b = userServiceByAnnotation.deleteUserById(id);
if (b) {
return Result.success("success", "数据删除成功");
}
return Result.error(500, "数据删除失败");
}
}
import com.zcs.entity.UserEntity;
import org.apache.ibatis.jdbc.SQL;
public class UserProvider {
public String selectAllUser() {
SQL sql = new SQL();
sql.SELECT("*");
sql.FROM("user");
return sql.toString();
}
public String insertUser(UserEntity userEntity) {
SQL sql = new SQL();
sql.INSERT_INTO("user");
if (userEntity.getId() != null) {
sql.VALUES("id", "#{id}");
}
if (userEntity.getName() != null) {
sql.VALUES("name", "#{name}");
}
if (userEntity.getAge() != null) {
sql.VALUES("age", "#{age}");
}
return sql.toString();
}
public String updateUser(UserEntity userEntity) {
SQL sql = new SQL();
sql.UPDATE("user");
if (userEntity.getName() != null) {
sql.SET("name = #{name}");
}
if (userEntity.getAge() != null) {
sql.SET("age = #{age}");
}
sql.WHERE("id = #{id}");
return sql.toString();
}
public String deleteUserById(Integer id) {
SQL sql = new SQL();
sql.DELETE_FROM("user");
sql.WHERE("id = #{id}");
return sql.toString();
}
}
import com.zcs.entity.UserEntity;
import com.zcs.mapper.provider.UserProvider;
import org.apache.ibatis.annotations.*;
import java.util.List;
@Mapper
public interface UserMapperByProvider {
@SelectProvider(type = UserProvider.class, method = "selectAllUser")
List<UserEntity> selectAllUser();
@InsertProvider(type = UserProvider.class, method = "insertUser")
@Options(useGeneratedKeys = true, keyProperty = "id")
boolean insertUser(UserEntity userEntity);
@UpdateProvider(type = UserProvider.class, method = "updateUser")
boolean updateUser(UserEntity userEntity);
@DeleteProvider(type = UserProvider.class, method = "deleteUserById")
boolean deleteUserById(Integer id);
}
import com.zcs.common.Result;
import com.zcs.entity.UserEntity;
import com.zcs.mapper.UserMapperByProvider;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import java.util.List;
@Slf4j
@RestController
@RequestMapping("/usersByProvider")
public class UserControllerByProvider {
@Resource
private UserMapperByProvider userMapper;
@GetMapping
public Result<List<UserEntity>> selectAllUsersByAnnotation() {
List<UserEntity> userEntities = userMapper.selectAllUser();
return Result.success("successfully", userEntities);
}
@PostMapping
public Result<String> insertUserByAnnotation(@RequestBody UserEntity user) {
boolean b = userMapper.insertUser(user);
if (b) {
return Result.success("success", "数据插入成功");
}
return Result.error(500, "数据插入失败");
}
@PutMapping
public Result<String> updateUserByAnnotation(@RequestBody UserEntity user) {
boolean b = userMapper.updateUser(user);
if (b) {
return Result.success("success", "数据更新成功");
}
return Result.error(500, "数据更新失败");
}
@DeleteMapping
public Result<String> deleteUserByAnnotation(@RequestParam Integer id) {
boolean b = userMapper.deleteUserById(id);
if (b) {
return Result.success("success", "数据删除成功");
}
return Result.error(500, "数据删除失败");
}
}
import org.apache.ibatis.annotations.*;
import java.util.List;
import java.util.Map;
@Mapper
public interface UserMapperByMap {
@Select("${sql}")
List<Map<String, Object>> getAllUser(Map<String, Object> map);
@Insert("${sql}")
@Options(useGeneratedKeys = true, keyProperty = "id")
boolean insertUser(Map<String, Object> map);
@Update("${sql}")
boolean updateUser(Map<String, Object> map);
@Delete("${sql}")
boolean deleteUserById(Map<String, Object> map);
}
import com.zcs.mapper.UserMapperByMap;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
import java.util.Map;
@Service
public class UserServiceByMap {
@Resource
private UserMapperByMap userMapperByMap;
public List<Map<String, Object>> getAllUser(Map<String, Object> map) {
String sql = "SELECT * FROM user";
map.put("sql", sql);
return userMapperByMap.getAllUser(map);
}
public boolean insertUser(Map<String, Object> map) {
String sql = "INSERT INTO user (id, name, age) VALUES (#{id}, #{name}, #{age})";
map.put("sql", sql);
return userMapperByMap.insertUser(map);
}
public boolean updateUser(Map<String, Object> map) {
String sql = "UPDATE user SET name = #{name}, age = #{age} WHERE id = #{id}";
map.put("sql", sql);
return userMapperByMap.updateUser(map);
}
public boolean deleteUserById(Map<String, Object> map) {
String sql = "DELETE FROM user WHERE id = #{id}";
map.put("sql", sql);
return userMapperByMap.deleteUserById(map);
}
}
import com.zcs.common.Result;
import com.zcs.service.UserServiceByMap;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Slf4j
@RestController
@RequestMapping("/usersByMap")
public class UserControllerByMap {
@Resource
private UserServiceByMap userServiceByMap;
@GetMapping
public Result<List<Map<String, Object>>> getAllUser() {
HashMap<String, Object> map = new HashMap<>();
List<Map<String, Object>> allUser = userServiceByMap.getAllUser(map);
return Result.success("success", allUser);
}
@PostMapping
public Result<String> insertUser(@RequestBody Map<String, Object> map) {
boolean b = userServiceByMap.insertUser(map);
if (b) {
return Result.success("success", "数据插入成功");
}
return Result.error(500, "数据插入失败");
}
@PutMapping
public Result<String> updateUserM(@RequestBody Map<String, Object> map) {
boolean b = userServiceByMap.updateUser(map);
if (b) {
return Result.success("success", "数据更新成功");
}
return Result.error(500, "数据更新失败");
}
@DeleteMapping
public Result<String> deleteUserByIdM(@RequestParam Map<String, Object> map) {
boolean b = userServiceByMap.deleteUserById(map);
if (b) {
return Result.success("success", "数据删除成功");
}
return Result.error(500, "数据删除失败");
}
}
Este artículo presenta en detalle los cuatro métodos para escribir SQL en la capa asignadora de MyBatis, incluido el uso de archivos XML, anotaciones, proporcionar clases y pasar SQL como parámetros. A través del código de implementación específico, los lectores pueden comprender mejor las ventajas y desventajas de cada método y elegir el método apropiado según las necesidades reales.