sprint boot 使用XML方式实现操作数据库

前面我们已经学习使用MyBatis-Plus依赖实现操作数据库MyBatis-Plus还支持XML文件来操作数据库一般适合于复杂的SQL操作spring boot使用MyBatis-Plus依赖实现操作数据库-CSDN博客spring boot 实现数据库分页操作-CSDN博客前期的基础配置信息参考上面的文档操作一yml配置XML地址spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:3306/test_db?useUnicodetruecharacterEncodingutf-8serverTimezoneAsia/ShanghaiuseSSLfalseallowPublicKeyRetrievalTrue username: root password: xxxx mybatis-plus: # mapper xml文件位置如果自定义xml才需要 mapper-locations: classpath:/mapper/**/*.xml global-config: db-config: # 主键策略自增 id-type: auto configuration: ### 开启打印sql配置 log-impl: org.apache.ibatis.logging.stdout.StdOutImpl auto-mapping-behavior: partial map-underscore-to-camel-case: true二启动类开启Mapper扫描在启动类增加MapperScan(com.example.demo.mapper)com.example.demo.mapper对应Mapper接口地址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.mapper) public class DemoApplication { public static void main(String[] args) { SpringApplication.run(DemoApplication.class, args); } }三编写XML根据上面配置的xml文件配置classpath:/mapper/**/*.xml在src/main/resources/mapper/user目录下创建UserMapper.xml文件1查询?xml version1.0 encodingUTF-8? !DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtd mapper namespacecom.example.demo.mapper.UserMapper !-- 查询所有 -- select idselectAllUser resultTypecom.example.demo.model.User select id,username,age from user /select !-- 根据id查询 -- select idselectByUserId resultTypecom.example.demo.model.User select id,username,age from user where id #{id} /select !--指定条件查询-- select idselectUserByCondition resultTypecom.example.demo.model.User select id,username,age from user where if testusername ! null and username ! and username like concat(%,#{username},%) /if if testage ! null and age #{age} /if /where /select /mapper2新增?xml version1.0 encodingUTF-8? !DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtd mapper namespacecom.example.demo.mapper.UserMapper !-- 新增 -- insert idinsertUser insert into user(uuid,username,age) values(#{uuid},#{username},#{age}) /insert !-- 4. foreach批量插入多条数据 -- insert idinsertBatch insert into user(uuid,username,age) values foreach collectionlist itemu separator, (#{u.uuid},#{u.username},#{u.age}) /foreach /insert /mapper3编辑?xml version1.0 encodingUTF-8? !DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtd mapper namespacecom.example.demo.mapper.UserMapper !-- 更新 -- update idupdateUser update user set username#{username}, age#{age} where id#{id} /update !--动态更新-- update idupdateUserDynamic update user set if testusername ! null and username ! username #{username}, /if if testage ! null age #{age}, /if /set where id #{id} /update /mapper4删除?xml version1.0 encodingUTF-8? !DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtd mapper namespacecom.example.demo.mapper.UserMapper !-- 删除 -- delete iddeleteById delete from java_user where id#{id} /delete !-- 3. foreach批量删除in语句 collection集合参数名item循环项open、close、separator拼接符号 -- delete iddeleteBatchIds delete from java_user where id in foreach collectionidList itemid open( close) separator, #{id} /foreach /delete /mapper四编写Mapper接口根据上面的xml文件中配置的namespace属性 找到对应的Mapper接口文件根据上面配置的Mapper接口文件地址为/src/main/java/com/example/demo/mapper/UserMapper.java1查询package com.example.demo.mapper; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.example.demo.model.User; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; import java.util.List; public interface UserMapper extends BaseMapperUser { //查询所有 ListUser selectAllUser(); // 根据id查询 ListUser selectByUserId(Param(id) Long id); //指定条件查询 ListUser selectUserByCondition(Param(username) String username, Param(age) Integer age); }2新增package com.example.demo.mapper; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.example.demo.model.User; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; import java.util.List; public interface UserMapper extends BaseMapperUser { //新增 int insertUser(User user); // 批量新增 int insertBatch(Param(list) ListUser list); }3编辑package com.example.demo.mapper; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.example.demo.model.User; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; import java.util.List; public interface UserMapper extends BaseMapperUser { // 更新 int updateUser(User user); //动态更新 int updateUserDynamic(User user); }4删除package com.example.demo.mapper; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.example.demo.model.User; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; import java.util.List; public interface UserMapper extends BaseMapperUser { // 删除 int deleteById(Param(id) Long id); // 批量删除 根据id集合 int deleteBatchIds(Param(idList) ListLong idList); }五调用示例1查询package com.example.demo.controller; import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.example.demo.mapper.UserMapper; import com.example.demo.model.User; import jakarta.annotation.Resource; import org.springframework.web.bind.annotation.*; import java.util.*; RestController RequestMapping(/user) public class UserController { //注入mapper Resource private UserMapper userMapper; GetMapping(/selectAllUser) public ListUser selectAllUser() { //查询所有 return userMapper.selectAllUser(); } GetMapping(/selectByUserId) public ListUser selectByUserId() { //根据指定ID查询 Long id 2L; return userMapper.selectByUserId(id); } GetMapping(/selectUserByCondition) public ListUser selectUserByCondition() { //指定条件查询 return userMapper.selectUserByCondition(张, null); } }2新增package com.example.demo.controller; import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.example.demo.mapper.UserMapper; import com.example.demo.model.User; import jakarta.annotation.Resource; import org.springframework.web.bind.annotation.*; import java.util.*; RestController RequestMapping(/user) public class UserController { //注入mapper Resource private UserMapper userMapper; GetMapping(/insertUser) public String insertUser() { //单条新增 User user new User(); user.setUsername(李四); user.setAge(25); user.setUuid(UUID.randomUUID().toString().replace(-, )); userMapper.insertUser(user); return ok; } GetMapping(/insertBatch) public String insertBatch() { //批量新增 ListUser userList new ArrayList(); User u1 new User(); u1.setUsername(张三); u1.setAge(22); u1.setUuid(UUID.randomUUID().toString().replace(-, )); User u2 new User(); u2.setUsername(李四); u2.setAge(23); u2.setUuid(UUID.randomUUID().toString().replace(-, )); User u3 new User(); u3.setUsername(王五); u3.setAge(24); u3.setUuid(UUID.randomUUID().toString().replace(-, )); userList.add(u1); userList.add(u2); userList.add(u3); //执行批量插入 int rows userMapper.insertBatch(userList); return 成功插入行数 rows; } }3编辑package com.example.demo.controller; import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.example.demo.mapper.UserMapper; import com.example.demo.model.User; import jakarta.annotation.Resource; import org.springframework.web.bind.annotation.*; import java.util.*; RestController RequestMapping(/user) public class UserController { //注入mapper Resource private UserMapper userMapper; GetMapping(/updateUser) public String updateUser() { //更新 User user new User(); user.setUsername(李四); user.setAge(25); user.setId(16L); userMapper.updateUser(user); return ok; } GetMapping(/updateUserDynamic) public String updateUserDynamic() { //动态更新 User user new User(); user.setId(12L); user.setUsername(张三修改); userMapper.updateUserDynamic(user); return ok; } }4删除package com.example.demo.controller; import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.example.demo.mapper.UserMapper; import com.example.demo.model.User; import jakarta.annotation.Resource; import org.springframework.web.bind.annotation.*; import java.util.*; RestController RequestMapping(/user) public class UserController { //注入mapper Resource private UserMapper userMapper; GetMapping(/deleteById) public String deleteById() { //删除 userMapper.deleteById(10L); return ok; } GetMapping(/deleteBatchIds) public String deleteBatchIds() { //批量删除 ListLong ids Arrays.asList(1L,2L,3L); userMapper.deleteBatchIds(ids); return ok; } }六XML常用标签1顶层标签mapper #根标签namespace 必须等于 Mapper 接口全类名mapper namespacecom.example.demo.mapper.UserMapper /mapper2增删改查标签select#查询示例select id方法名 resultType resultMap parameterType sql /select属性说明id对应 Mapper 接口方法名必须一致resultType返回值类型简单对象 / 集合泛型resultMap自定义映射二选一不能和 resultType 同时用parameterType传入参数类型可省略insert#新增示例insert idadd # insert idadd useGeneratedKeystrue keyPropertyid 返回自增主键 insert into user(name) values(#{name}) /insert属性说明useGeneratedKeystrue开启回填主键keyProperty实体接收主键的属性名update#修改示例update idupdate update user set name#{name} where id#{id} /updatedelete#删除示例delete iddelete delete from user where id#{id} /delete3动态SQL标签if #条件判断示例if testname ! null and name ! # test 里面是 **OGNL 表达式**不用 #{} and name like concat(%,#{name},%) /ifwhere #where条件标签示例#自动处理多余and没有条件时不会生成 where 关键字 where if testname ! nulland name#{name}/if /whereset #更新专用示例#自动去掉最后一个多余逗号 set if testname ! nullname#{name},/if /setchoose when otherwise#多选一类似 switch‑case示例# 只会执行一个分支 choose when testage ! null and age #{age} /when otherwise and age 18 /otherwise /chooseforeach#循环批量、in 查询示例foreach collectionlist itemu open( close) separator, #{u.id} /foreach属性说明collection集合参数名和Param(xxx)一致item循环中单个元素别名open循环开始前缀close循环结束后缀separator元素之间分隔符trim#万能字符串截取where、set 底层就是 trim示例trim prefixWHERE prefixOverridesAND|OR if testname ! nulland name#{name}/if /trim属性说明prefix头部添加字符串prefixOverrides去掉开头多余字符suffix尾部添加字符串suffixOverrides去掉末尾多余逗号4映射标签resultMap#自定义字段映射示例resultMap idUserMap typecom.example.demo.model.User id columnuser_id propertyid/ result columnuser_name propertyname/ /resultMap属性说明id主键映射column 数据库列名propertyJava 属性名result普通字段映射association#多对一、单个嵌套对象示例association propertydept javaTypecom.example.demo.model.Dept id columndept_id propertydeptId/ /association属性说明property实体里面嵌套对象属性名javaType嵌套对象类型collection#一对多、集合示例collection propertyorderList ofTypecom.example.demo.model.Order id columnorder_id propertyorderId/ /collection属性说明ofType集合里面元素的 Java 类型extends#resultMap 继承示例resultMap idbaseMap typeUser.../resultMap resultMap idfullMap extendsbaseMap typeUser.../resultMap5SQL复用标签sqlinclude提取公共 SQL 片段示例!--定义公共字段-- sql iduserColumnsid,name,age,email/sql select idselectAll resultTypeUser select include refiduserColumns/ from user /select七XML标签速查清单标签用途select/insert/update/delete增删改查if条件判断where动态 where 条件去除多余 andset动态更新去除末尾逗号choose‑when‑otherwise多选一foreach循环、批量操作、in 查询trim自定义字符串裁剪resultMap自定义字段映射association多对一嵌套对象collection一对多集合映射sql include复用 SQL 片段