基于MyBatis的MavenCRUD

结构

说明:domain用于存放实体类,qo存放查询条件的对象,util存放工具类

创建db.properties

说明:此配置文件主要用于配置数据库连接四要素,driver代表所需的数据库连接驱动,url代表数据库的连接地址,username代表数据库的用户名,password代表数据库的密码
创建mybatis-config.xml配置文件

说明:通过properties标签引入db.properties并通过${}依靠键名读取db.properties的值,为数据库连接池配置四要素.利用扫描该软件包下所有的mapper文件.
引入项目所需的依赖

点击查看代码

<dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql-jdbc-version}</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.19</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.34</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>2.0.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-api</artifactId>
            <version>2.25.2</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>2.0.17</version>
        </dependency>
    </dependencies>

4. 创建log4j.properties ![图片3](https://img2024.cnblogs.com/blog/3764592/202601/3764592-20260127211648581-2061529256.png) 说明:配置日志文件,用于在控制台输出sql语句的日志,日志级别设置为TRACE.

5.封装实体类

点击查看代码

```
  @Data
  @AllArgsConstructor
  @NoArgsConstructor
  public class Employee {
      /*雇员的编号，由四位数字所组成*/
      private Long empno;
      /*雇员的姓名，由10位字符所组成*/
      private String ename;
      /*雇员的职位*/
      private String job;
      /*雇员对应的领导编号，领导也是雇员*/
      private Long mgr;
      /*雇员的雇佣日期*/
      private Date hiredate;
      /*基本工资，其中有两位小数，五位整数，一共是七位*/
      private Double sal;
      /*奖金，佣金(销售才有)*/
      private Double comm;
      /*雇员所在的部门编号*/
      private Long deptno;
  }
```
</details>

6.创建EmployeeMapper接口,并添加save方法用于增加

点击查看代码

public interface EmployeeMapper {
    int save(Employee employee);
 
}

7.在resources资源根目录下,创建和EmployeeMapper相同位置的EmployeeMapper.xml文件,用于撰写sql语句

点击查看代码

  ```
  <insert id="save" keyColumn="empno" keyProperty="empno" useGeneratedKeys="true">
          insert into emp (ename, job, mgr, hiredate, sal, comm, deptno)
          values ( #{ename}, #{job}, #{mgr}, #{hiredate}, #{sal}, #{comm}, #{deptno} );
      </insert>
  ```
  </details>

说明:可以通过id的值来调取相应的方法进行操作,keyColumn用于映射表中的列,keyProperty用于映射java实体类的字段,useGeneratedKeys表示自动生成主键,执行添加后,可以自动获取数据库中自增长的id,并映射到当前实体类的字段中.

8.编写工具类MyBatisUtils

点击查看代码

  ```
  public class MybatisUtils {
      private static SqlSessionFactory ssf;
      static{
          try (InputStream is= Resources.getResourceAsStream("mybatis-config.xml");){
              ssf=new SqlSessionFactoryBuilder().build(is);
          } catch (IOException e) {
              throw new RuntimeException(e);
          }
      }
      public static SqlSession getSqlSession(){
          return ssf.openSession();
      }

      public static void extracted(SqlSession ss) {
          ss.commit();
          ss.close();
      }
  }
  ```
  </details>

说明:获取mybatis-config.xml配置文件的输入流, 利用SqlSessionFactory对象,通过SqlSessionFactoryBuilder(),将配置信息进行构建,返回并打开一个sqlSession对象,创建extracted方法,用于提交事务并关闭资源.

9.创建EmployeeService接口

点击查看代码

  ```
  public interface EmployeeService {
      int save(Employee employee);
  }
  ```
  </details>

实现EmployeeService接口

点击查看代码

public class EmployeeServiceImpl implements EmployeeService {

    @Override
    public int save(Employee employee) {
        SqlSession ss= MybatisUtils.getSqlSession();
        EmployeeMapper employeeMapper=ss.getMapper(EmployeeMapper.class);
        int save = employeeMapper.save(employee);
        MybatisUtils.extracted(ss);
        return save;
    }
 }

说明: 利用MybatisUtils工具类获取 SqlSession对象,利用该对象的getMapper方法,通过EmployeeMapper的字节码文件获取到EmployeeMapper的对象,再利用EmployeeMapper的对象调用具体方法完成需求,最将受影响的行数返回.

11.创建测试类进行验证

点击查看代码

      public class EmployeeTest {
          private EmployeeService employeeService=new EmployeeServiceImpl();
          @Test
          void save() {
              Employee employee= new Employee();
              employee.setEname("bbb");
              employee.setJob("CLERK");
              employee.setMgr(7782L);
              employee.setHiredate(new Date());
              employee.setSal(2000.0D);
              employee.setComm(0.0D);
              employee.setDeptno(10L);
              int save = employeeService.save(employee);
              if(save>0){
                  System.out.println("保存成功");
              }else{
                  System.out.println("保存失败");
              }
          }
      }
      ```
      </details>

说明:利用多态,创建一个EmployeeServiceImpl的对象,创建一个Employee对象,并未其设置想要添加的属性值,利用
employeeServiceImpl对象,调用save添加方法,在save方法中会利用employeeMapper对象调用save方法,调用sql
语句,完成添加操作,最后将受影响的行数返回到employeeServiceImpl中,emplServiceImpl对象再返回给测试类,
最后对该值进行逻辑处理,如果大于0,说明至少有一行受影响,则保存成功,否则保存失败.

12.继续在EmployeeMapper中添加 通过id删除,修改,通过id查询,查询所有的抽象方法

      <details>
      <summary>点击查看代码</summary>

      ```
       int delById(Long id);
          int update(Dept dept);
          Dept selById(Long id);
          List<Dept> selAll();
      ```
      </details>

13.在mapper.xml文件中,通过ResultMap,手动映射,将需要查询的字段封装到sql标签中,编写删除,修改,查询所需的sql语句

<details>
      <summary>点击查看代码</summary>

      ```
<update id="update">
            update emp
            set ename = #{ename},
                job = #{job},
                mgr = #{mgr},
                hiredate = #{hiredate},
                sal = #{sal},
                comm = #{comm},
                deptno = #{deptno}
            where empno = #{empno}
    </update>
<delete id="delById">
            delete from emp where empno = #{empno}
    </delete>
<select id="selById" resultMap="EmployeeResultMap">
            select <include refid="emp_columns" /> from emp where empno = #{empno}
    </select>
    <select id="selAll" resultMap="EmployeeResultMap">
            select <include refid="emp_columns" /> from emp
    </select>
      ```
      </details>

14.在service接口中,添加对应的抽象方法

      <details>
      <summary>点击查看代码</summary>

      ```
         int delById(Long id);
         int update(Employee employee);
         Employee selById(Long id);
         List<Employee> selAll();
      ```
      </details>

15.实现这几个方法

      <details>
      <summary>点击查看代码</summary>

      ```
      @Override
          public int delById(Long id) {
              SqlSession ss= MybatisUtils.getSqlSession();
              EmployeeMapper employeeMapper=ss.getMapper(EmployeeMapper.class);
              int del = employeeMapper.delById(id);
              MybatisUtils.extracted(ss);
              return del;
          }

          @Override
          public int update(Employee employee) {
              SqlSession ss= MybatisUtils.getSqlSession();
              EmployeeMapper employeeMapper=ss.getMapper(EmployeeMapper.class);
              int update = employeeMapper.update(employee);
              MybatisUtils.extracted(ss);
              return update;
          }

          @Override
          public Employee selById(Long id) {
              SqlSession ss= MybatisUtils.getSqlSession();
              EmployeeMapper employeeMapper=ss.getMapper(EmployeeMapper.class);
              Employee employee = employeeMapper.selById(id);
              ss.close();
              return employee;
          }

          @Override
          public List<Employee> selAll() {
              SqlSession ss= MybatisUtils.getSqlSession();
              EmployeeMapper employeeMapper=ss.getMapper(EmployeeMapper.class);
              List<Employee> list = employeeMapper.selAll();
              ss.close();
              return list;
          }
      ```
      </details>

16.创建测试类,测试几个方法

      <details>
      <summary>点击查看代码</summary>

      ```
        @Test
    void delById() {
        int del = employeeService.delById(7935L);
        if(del>0){
            System.out.println("删除成功");
        }else{
            System.out.println("删除失败");
        }
    }

    @Test
    void update() {
        Employee employee= new Employee();
        employee.setEmpno(7935L);
        employee.setEname("ccc");
        employee.setJob("ccc");
        employee.setMgr(1L);
        employee.setHiredate(new Date());
        employee.setSal(3000.0D);
        employee.setComm(0.0D);
        employee.setDeptno(20L);
        int update = employeeService.update(employee);
        if(update>0){
            System.out.println("修改成功");
        }else{
            System.out.println("修改失败");
        }
    }

    @Test
    void selById() {
        Employee employee = employeeService.selById(7935L);
        System.out.println(employee);
    }

    @Test
    void selAll() {
        employeeService.selAll().forEach(System.out::println);
    }
      ```
      </details>
说明:将查询到的对象输出,修改和删除对返回值进行逻辑处理,如果受影响行数大于1,则删除/修改成功.

17.批量添加    首先在mapper中创建抽象方法,然后在mapper.xml中添加对应的sql语句

      <details>
      <summary>点击查看代码</summary>

      ```
            int batchSave(List<Employee> list);

 <insert id="batchSave">
        insert into emp (ename, job, mgr, hiredate, sal, comm, deptno)
        values
            <foreach collection="list" item="emp" separator=",">
                ( #{emp.ename}, #{emp.job}, #{emp.mgr}, #{emp.hiredate}, #{emp.sal}, #{emp.comm}, #{emp.deptno} )
            </foreach>
    </insert>

      ```
      </details>
说明: <foreach>标签可以对字段进行遍历,collection表示数据源,item表示当前项,临时变量,separator表示循环中每个变量以什么符号进行分隔

18.在service中创建抽象方法,并在service的实现类中实现该方法

      <details>
      <summary>点击查看代码</summary>

      ```
      @Override
    public int batchSave(List<Employee> list) {
        SqlSession ss= MybatisUtils.getSqlSession();
        EmployeeMapper employeeMapper=ss.getMapper(EmployeeMapper.class);
        int save = employeeMapper.batchSave(list);
        MybatisUtils.extracted(ss);
        return save;
    }
      ```
      </details>

19.在测试类中进行测试,并根据返回值判断是否批量添加成功

<details>
<summary>点击查看代码</summary>

@Test
void batchSave() {
List employees = new ArrayList<>(
List.of(new Employee(1L,"eee","ccc",1L,new Date(),3000.0D,0.0D,20L),
new Employee(2L,"ddd","ccc",1L,new Date(),3000.0D,0.0D,20L)
));
int batchSave = employeeService.batchSave(employees);
if(batchSave>0){
System.out.println("批量保存成功");
}else{
System.out.println("批量保存失败");
}
}

</details>

20.批量删除 原理同上,首先在mapper接口中创建抽象方法,在mapper.xml中实现,在service接口创建方法,在serviceImpl实现类中实现该方法,最后在测试类中测试该方法,依据返回值判断是否批量删除成功

      <details>
      <summary>点击查看代码</summary>

      ```
   <delete id="batchDelete">
            delete from emp where empno in
            <foreach collection="array" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
    </delete>
      
    @Override
    public int batchDelete(Long[] ids) {
        SqlSession ss= MybatisUtils.getSqlSession();
        EmployeeMapper employeeMapper=ss.getMapper(EmployeeMapper.class);
        int batchDelete = employeeMapper.batchDelete(ids);
        MybatisUtils.extracted(ss);
        return batchDelete;
    }

        @Test
    void batchDelete() {
        Long[] ids = {7935L,7936L,7937L};
        int batchDelete = employeeService.batchDelete(ids);
        if(batchDelete>0){
            System.out.println("批量删除成功");
        }else{
            System.out.println("批量删除失败");
        }

    }
      ```
      </details>
说明:open里的内容表示sql语句初始化之前的内容,close表示关闭

21.动态修改  原始的修改可能会伴随一些问题,直接将整个对象作为参数传递进行修改时,如果只给其中少数字段赋值,其他字段则会采取默认值,最终可能导致有些字段被修改为空值,此时动态sql就发挥了作用,可以非常人性化的进行修改,如果该对象的某个属性为空,则不进行操作,保留原来的值
首先还是在mapper接口中,创建抽象方法

      <details>
      <summary>点击查看代码</summary>

      ```
            int updatePartten(Employee employee);

      ```
      </details>


在mapper.xml中,写动态sql语句


      <details>
      <summary>点击查看代码</summary>

      ```
        <update id="updatePartten">
            update emp
            <set>
                <if test="ename != null and ename != ''">
                    ename = #{ename},
                </if>
                <if test="job != null and job != ''">
                        job = #{job},
                </if>
                <if test="mgr != null">
                        mgr = #{mgr},
                </if>
                <if test="hiredate != null">
                        hiredate = #{hiredate},
                </if>
                <if test="sal != null">
                        sal = #{sal},
                </if>
                <if test="comm != null">
                        comm = #{comm},
                </if>
                <if test="deptno != null">
                        deptno = #{deptno}
                </if>
            </set>
            where empno = #{empno}
    </update>
      ```
      </details>

说明:利用<set><if>标签,如果if标签里面的值为true,则执行.

接下来在service接口中创建抽象方法,并在serviceImpl实现类中实现
      <details>
      <summary>点击查看代码</summary>

      ```
            int updatePartten(Employee employee);

        @Override
        public int updatePartten(Employee employee) {
            SqlSession ss= MybatisUtils.getSqlSession();
            EmployeeMapper employeeMapper=ss.getMapper(EmployeeMapper.class);
            int updatePartten = employeeMapper.updatePartten(employee);
            MybatisUtils.extracted(ss);
            return updatePartten;
        }

      ```
      </details>

21.条件查询-模糊查询
  相比查询所有记录,显然条件查询更能满足生活和项目中的需求,例如要查询薪资在1000-2000之间的员工,则可以通过该方式进行查询
22.首先创建查询对象

      <details>
      <summary>点击查看代码</summary>

      ```
      @Data
@AllArgsConstructor
@NoArgsConstructor
public class EmployeeQO {
    private String keyword;
    private Double salStart;
    private Double salEnd;
}

      ```
      </details>

说明:后面会通过该对象的值,进行查询

23. 在mapper中创建该抽象方法,将查询对象作为实参,并在mapper.xml中写sql语句

<details>
<summary>点击查看代码</summary>

List<Employee> selByCondition(EmployeeQO qo);

</details>
说明:通过where,if标签,进行空值判断,通过like子句完成模糊查询,利用concat()函数,将关键字和%拼接,需要注意的是这里面<=要用&lt;

24.在service和serviceImpl中创建该方法

      <details>
      <summary>点击查看代码</summary>

      ```
        @Override
    public List<Employee> selByCondition(EmployeeQO qo) {
        SqlSession ss= MybatisUtils.getSqlSession();
        EmployeeMapper employeeMapper=ss.getMapper(EmployeeMapper.class);
        List<Employee> list = employeeMapper.selByCondition(qo);
        ss.close();
        return list;
    }
      ```
      </details>


25.在测试类中,测试该方法,将查询到的结果输出

      <details>
      <summary>点击查看代码</summary>

      ```
      @Test
          void selByCondition() {
              EmployeeQO qo=new EmployeeQO();
              qo.setKeyword("sale");
              qo.setSalStart(1000.0D);
              qo.setSalEnd(1300.0D);
              employeeService.selByCondition(qo).forEach(System.out::println);
          }
      ```
      </details>

至此,完成CRUD

posted @ 2026-01-27 22:03 theCabbage 阅读(4) 评论(0) 收藏举报

刷新页面返回顶部

theCabbage

基于MyBatis的MavenCRUD

公告