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

Mybatis XML配置文件操作数据库

news 来源：原创 2024/9/27 6:09:35

Mybaits在操作数据库时，可以有两种方式；第一种是使用注解的方式操作，另一种是使用XML配置文件的方式：一般而言，若没有特别的要求，则编写一些简单的SQL语句，可以直接使用注解的方式；编写一些复杂的SQL语句则需要使用XML注解的方式

用XML配置文件操作数据库，完成增删改查。

MybatisMapper

package com.wzb.MybatisExercise20240926;import com.wzb.Pojo20240926.Emp;
import org.apache.ibatis.annotations.Mapper;import java.time.LocalDate;
import java.util.List;@Mapper
public interface MybatisMapper {// 增public void insertEmp(Emp emp);// 删public void deleteEmp(List<Integer> ids);// 改public void updateEmp(Emp emp);// 查public List<Emp> selectEmp(String name, short gender, LocalDate begin, LocalDate end);}

Pojo

package com.wzb.Pojo20240926;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;import java.time.LocalDate;
import java.time.LocalDateTime;@Data
@AllArgsConstructor
@NoArgsConstructor
public class Emp {private Integer id;private String username;private String password;private String name;private Short gender;private String image;private Short job;private LocalDate entrydate; //LocalDate类型对应数据表中的date类型private Integer deptId;private LocalDateTime createTime; //LocalDateTime类型对应数据表中的datetime类型private LocalDateTime updateTime;
}

XML配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wzb.MybatisExercise20240926.MybatisMapper"><!--全类名就是全部包名 + 类名--><!--XML映射文件中的SQL语句的id必须和Mapper接口中的方法名完全一致，并且保持返回值一致--><!--完成条件查询--><!--    <select id = "selectEmp" resultType="com.wzb.Pojo20240926.Emp">--><!--        select * from emp--><!--        where name like concat('%', #{name}, '%')--><!--        and gender = #{gender}--><!--        and entrydate between #{begin} and #{end}--><!--        order by update_time desc--><!--    </select>--><!-- 但是有一个弊端，可见中间都是and连接的，那么当后面的参数（如gender）都是null的时候，即使传递了前面的参数（如name）也不会有查询结果，因为不管传递参数没有，都会拼接进SQL语句，这不符合业务逻辑————传递了参数，才进行拼接；若没有参数，则不拼接 --><!-- 增 --><insert id="insertEmp">insert into emp (username, name, gender, image, job, entrydate, dept_id, create_time, update_time)values<trim prefix="(" suffix=")" suffixOverrides=","><if test="username != null">#{username},</if><if test="name != null">#{name},</if><if test="gender != -1">#{gender},</if><if test="image != null">#{image},</if><if test="job != null">#{job},</if><if test="entrydate != null">#{entrydate},</if><if test="deptId != -1">#{deptId},</if>#{createTime}, #{updateTime}</trim></insert><!-- 删 --><delete id="deleteEmp">delete from emp where id in<foreach collection="ids" item="id" separator="," open="(" close=")">#{id}</foreach></delete><!-- 改 --><update id="updateEmp">update emp<set><if test="username != null">username=#{username},</if><if test="name != null">name=#{name},</if><if test="gender != -1">gender=#{gender},</if><if test="image != null">image=#{image},</if><if test="job != null">job=#{job},</if><if test="entrydate != null">entrydate=#{entrydate},</if><if test="deptId != null">dept_id=#{deptId},</if><if test="updateTime != null">update_time=#{updateTime}</if></set>where id=#{id}</update><select id="selectEmp" resultType="com.wzb.Pojo20240926.Emp">select * from emp<where><if test="name != null">name like concat('%', #{name}, '%')</if><if test="gender!=-1">and gender=#{gender}</if><if test="begin!=null and end!=null">and entrydate between begin and end</if></where>order by entrydate desc</select></mapper>

SpringbootTest

package com.wzb;import com.wzb.MybatisExercise20240926.MybatisMapper;
import com.wzb.Pojo20240926.Emp;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;@SpringBootTest
class SpringbootExercise20240926ApplicationTests {@Autowiredprivate MybatisMapper mybatisMapper;//增@Testpublic void insertEmp() {Emp emp = new Emp(30, "leiyi", "123456", "雷伊", (short) 1, "3.jpg",(short) 1, LocalDate.of(2000, 1, 1),2, LocalDateTime.now(), LocalDateTime.now());mybatisMapper.insertEmp(emp);}// 删@Testpublic void deleteEmp() {List<Integer> ids = new ArrayList<>();Collections.addAll(ids, 30);mybatisMapper.deleteEmp(ids);}// 改@Testpublic void updateEmp() {Emp emp = new Emp();emp.setUsername("gaiya");emp.setName("盖亚");emp.setGender((short)1);emp.setUpdateTime(LocalDateTime.now());emp.setId(30);mybatisMapper.updateEmp(emp);}// 查@Testpublic void selectEmp() {List<Emp> empList = mybatisMapper.selectEmp(null, (short)-1, null, null);for (Emp emp : empList) {System.out.println(emp);}}}