Mybatis-分页、注解、log4j的使用、ResultMap多对一和一对多

2024-01-21 21:51:46

Log4j的使用

1.在pom文件中导入依赖

<!--导入log4j-->
        <!-- https://mvnrepository.com/artifact/log4j/log4j -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

2.配置log4j.properties文件(配置在类路径下，resource目录下)

#将等级为DEBUG的日志信息输出到console和file这两个目的地
，console和file的定义在下面的代码
log4j.rootLogger=DEBUG,console,file

#控制台输出的相关设置
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.Target=System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n

#输出到日志文件
log4j.appender.file=org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/Mybatis_logs.log
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yyyy-MM-dd}][%c]%m%n

#日志输出级别
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

3.成果展示

Limit分页

分页的作用：
- 进行友好的显示
- 减少数据的处理量，分开进行显示（例子：淘宝商城的商品分页显示）
操作
1.UserMapper.class

/*实现分页查询*/
    List<UserPojo> getLimitUser(HashMap<String, Integer> map);

2.UserMapper.xml

<!--ResultMap 结果集,解决数据库列名与属性名不同的问题-->
    <resultMap id="UserMap" type="UserAliasesType">
        <!--这里的ID和Name都可以省略-->
        <id  column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="password" property="pwd"/>
    </resultMap>
<!--分页查询-->
    <select id="getLimitUser" parameterType="Map" resultMap="UserMap" >
        select * from `user` limit #{indexPage},#{countPage}
    </select>

3.UserMapperTest.class

//第一种方式
/*分页查询*/
    @Test
    public void getLimitUserTest() throws IOException {
        SqlSession sqlsession = MybatisUtil.getSQLSession();
        UserMapper mapper = sqlsession.getMapper(UserMapper.class);
        HashMap<String,Integer> map = new HashMap<String, Integer>();
        map.put("indexPage",0);
        map.put("countPage",5);
        List<UserPojo> limitUser = mapper.getLimitUser(map);
        for (UserPojo user:limitUser) {
            System.out.println(user);
        }
//第二种方式：使用RowBounds
    @Test
    public void getLimiterRoeBounds(){
        RowBounds rowBounds = new RowBounds(5, 5);
        List<UserPojo> listuser = sqlsession.selectList("com.test.mapper.UserMapper.getListUser", null, rowBounds);
        for (UserPojo user:listuser) {
            System.out.println(user);
        }
    }

使用注解开发

1.面向接口编程
作用：解耦、可扩展、提高复用性、分层开发（上层不用管下层的具体实现）、使得共同协作变得更加容易代码更具有规范性
2.操作(中间会用到MybatisUtil.class,mybatis-config.xml,log4j.properties,UserPojo.class)
需要在mybatis-config.xml文件中绑定接口(注册Mapper)
mybatis-config.xml

    <!--注册Mapper-->
    <mappers>
        <!--第一种配置方式-->
        <!--<mapper resource="com/test/mapper/UserMapper.xml"/>-->
        <!--第二种配置方式(需要UserMapper.class文件与UserMapper.xml文件在同一个包下，且文件名必须相同)-->
        <!--<mapper class="com.test.mapper.StudentMapper"/>-->
        <mapper class="test.mapper.UserMapper"/>
        <!--第三种配置方式(需要UserMapper.class文件与UserMapper.xml文件在同一个包下，且文件名必须相同)-->
        <!--<package name="com.test.mapper"/>-->
    </mappers>

UserMapper.class

package test.mapper;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import test.pojo.UserPojo;

import java.util.HashMap;
import java.util.List;

public interface UserMapper {
    //查询所有用户
    @Select("select id,name,password pwd from user")
    List<UserPojo> getListUser();

    //添加用户
    @Insert("insert into user values(#{id},#{name},#{pwd})")
    int addUser(UserPojo userPojo);

    //更新用户
    @Update("update user set name=#{name},password=#{pwd} where id=#{id}")
    int updateUser(UserPojo userPojo);
}

UserMapperTest.class

package com.test.mapper;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import test.mapper.UserMapper;
import test.pojo.UserPojo;
import test.util.MybatisUtil;

import java.io.IOException;
import java.util.List;

public class UserMapperTest {
   SqlSession sqlSession;
   UserMapper mapper;
   public UserMapperTest() throws IOException {
      this.sqlSession = MybatisUtil.getSQLSession();
      this.mapper = sqlSession.getMapper(UserMapper.class);
   }

   //使用注解查询用户
   @Test
   public void getListUser(){
      List<UserPojo> listUser = mapper.getListUser();
      for (UserPojo user:listUser) {
         System.out.println(user);
      }
   }

   //使用注解添加用户
   @Test
   public void addUser(){
      UserPojo userpojo = new UserPojo(3000, "2wubai", "2000");
      int i = mapper.addUser(userpojo);
      if(i==1) {
         System.out.println("成功添加" + i + "行，数据！");
      }else {
         System.out.println("添加用户失败！");
      }
   }

   //更新用户Byid
   @Test
   public void updateUser(){
      UserPojo userpojo = new UserPojo(2000, "2000", "2000duo");
      int i = mapper.updateUser(userpojo);
      if(i==1) {
         System.out.println("成功更新" + i + "行，数据！");
      }else {
         System.out.println("更新用户失败！");
      }
   }
}

搭建（多对一，一对多）的测试环境

学生表，老师表
实体类：
StudentMapper.class

package test.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data //get，set方法
@AllArgsConstructor
@NoArgsConstructor
public class StudentPojo {
    private int id;
    private String name;
    private TeacherPojo teacherPojo;
}

TeacherMapper.class

package test.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class TeacherPojo {
    private int id;
    private String name;
}

ResultMap多对一

1.方法一：使用子查询
目标：查询有老师的学生
StudentMapper.class

public interface StudentMapper {
    //ResultMap-多对一
    List<StudentPojo> getUserAndTeacher();
}

StudentMapper.xml

<?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">
<!--namespace相当于(UserMapperImpl)实现dao层接口-->
<mapper namespace="test.mapper.StudentMapper">
    <!--创建ResultMap-->
    <!--association:应用于对象，一般用于多对一
        collection：应用于集合，一般用于一对多
    -->
    <resultMap id="studentMap" type="StudentAliasesType">
        <association property="teacherPojo" column="tid" javaType="TeacherAliasesType" select="getTeacher"/>
        <!--<collection property=""/>-->
    </resultMap>
    <select id="getUserAndTeacher" resultMap="studentMap">
        select * from student;
    </select>
    <select id="getTeacher" resultType="TeacherAliasesType">
        select * from teacher where id=#{tid}
    </select>
</mapper>

StudentMapper.class

package com.test.mapper;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import test.mapper.StudentMapper;
import test.mapper.UserMapper;
import test.pojo.StudentPojo;
import test.util.MybatisUtil;

import java.io.IOException;
import java.util.List;

public class StudentMapperTest {
    SqlSession sqlSession;
    StudentMapper studentMapper;
    public StudentMapperTest() throws IOException {
        this.sqlSession = MybatisUtil.getSQLSession();
        this.studentMapper = sqlSession.getMapper(StudentMapper.class);
    }
    //ResultMap多对一(子查询)
    @Test
    public void getUserAndTeacher(){
        List<StudentPojo> userAndTeacher = studentMapper.getUserAndTeacher();
        for (StudentPojo student:userAndTeacher) {
            System.out.println(student);
        }
        sqlSession.close();
    }
}

运行结果：

2.方法二：使用联表查询（与方法一的区别，主要是xml中的sql语句和resultMap配置的区别）

<!--使用联表查询-->
    <resultMap id="Student2" type="StudentAliasesType">
        <id property="id" column="id"/>
        <id property="name" column="name"/>
        <association property="teacherPojo" javaType="TeacherAliasesType">
            <result property="name" column="tname"/>
        </association>
    </resultMap>
    <select id="getStudentAndTeacher2" resultMap="Student2">
        SELECT s.id,s.name,t.name tname FROM student s,teacher t where s.tid=t.id
    </select>

ResultMap一对多

1.测试环境：查询一个老师的所有学生
2.TeacherPojo.class中包含一个学生集合（List studentPojoList;）
3.与多对一基本一样，只有StudentPojo.class、TeacherPojo.class和TeacherMapper.xml不一样
StudentPojo.class

public class StudentPojo {
    private int id;
    private String name;
    //private TeacherPojo teacherPojo;
    private int tid;
}

TeacherPojo.class

public class TeacherPojo {
    private int id;
    private String name;
    private List<StudentPojo> studentPojoList;
}

TeacherMapper.xml

<?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">
<!--namespace相当于(UserMapperImpl)实现dao层接口-->
<mapper namespace="test.mapper.TeacherMapper">
    <!--创建ResultMap-->
    <!--association:应用于对象，一般用于多对一
        collection：应用于集合，一般用于一对多
    -->
    <resultMap id="teacherMap" type="TeacherAliasesType">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <collection property="studentPojoList" ofType="StudentAliasesType">
            <result property="id" column="id"/>
            <result property="name" column="sname"/>
        </collection>
        <!--<collection property=""/>-->
    </resultMap>
    <select id="getTeacherAndStudentById" resultMap="teacherMap">
        select t.id,t.name,s.name sname from teacher t,student s where t.id=s.tid and t.id=#{id}
    </select>
    
    <!--使用子查询的模式-->
    <resultMap id="teacherMap2" type="TeacherAliasesType">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <collection property="studentPojoList" javaType="ArrayList" ofType="StudentAliasesType" select="getStudent" column="tid">
        </collection>
    </resultMap>
    <select id="getTeacherAndStudentById2" resultMap="teacherMap2">
        select id,name from teacher where id=#{id}
    </select>
    <select id="getStudent" resultType="StudentAliasesType">
        select id,name from student where tid=#{tid}
    </select>
</mapper>

TeacherMapperTest.class

public class TeacherMapperTest {
    SqlSession sqlSession;
    StudentMapper studentMapper;
    TeacherMapper teacherMapper;

    public TeacherMapperTest() throws IOException {
        this.sqlSession = MybatisUtil.getSQLSession();
        this.studentMapper = sqlSession.getMapper(StudentMapper.class);
        this.teacherMapper = sqlSession.getMapper(TeacherMapper.class);
    }

    //一对多
    @Test
    public void getTeacherAndStudentById() {
        List<TeacherPojo> teacherAndStudentById = teacherMapper.getTeacherAndStudentById(1);
        for (TeacherPojo teacherandstudent : teacherAndStudentById) {
            System.out.println(teacherandstudent);
        }
    }

    @Test
    public void getTeacherAndStudentById2() {
        List<TeacherPojo> teacherAndStudentById = teacherMapper.getTeacherAndStudentById(1);
        for (TeacherPojo teacherandstudent : teacherAndStudentById) {
            System.out.println(teacherandstudent);
        }
    }
}

一般情况下不使用子查询的模式，不便于发现问题（实际使用视情况而定）

搬运【bilibili狂神说Java】