Java MyBatis 学习记录

本文最后更新于 2022年2月14日 晚上

MyBatis

使用JDBC回顾

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
public void findStudent() {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
        //注册 mysql 驱动
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/springdb";
        String username = "root";
        String password = "root";
        //创建连接对象
        conn = DriverManager.getConnection(url, username, password);
        //保存查询结果
        List<Student> stuList = new ArrayList<>();
        //创建 Statement, 用来执行 sql 语句
        stmt = conn.createStatement();
        //执行查询，创建记录集，
        rs = stmt.executeQuery("select * from student");
        while (rs.next()) {
            Student stu = new Student();
            stu.setId(rs.getInt("id"));
            stu.setName(rs.getString("name"));
            stu.setAge(rs.getInt("age"));
            //从数据库取出数据转为 Student 对象，封装到 List 集合
            stuList.add(stu);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            //关闭资源
            if (rs != null) ;
            {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

代码多，复杂，业务和数据库操作在一起

MyBatis使用

配置文件

  • 依赖添加
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<dependencies>
  <dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.11</version>
    <scope>test</scope>
  </dependency>

  <dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.9</version>
  </dependency>

  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.25</version>
  </dependency>
</dependencies>

注意mysql-connector-java需要与数据库版本一致或者更高

  • build选项
1
2
3
4
5
6
7
8
9
10
11
12
<build>
  <resources>
    <resource>
      <directory>src/main/java</directory>
      <includes>
        <include>**/*.properties</include>
        <include>**/*.xml</include>
      </includes>
      <filtering>false</filtering>
    </resource>
  </resources>
</build>

resource目录下配置文件不过滤

  • mybatis.xml配置
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--开启日志-->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

    <!--定义别名-->
    <typeAliases>
        <typeAlias type="org.example.entity.Student" alias="stu"/>

        <!--使用包名下面的类名作为别名-->
        <package name="org.example.entity"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/springdb"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    
    <mappers> <!-- Mapper路径 -->
        <!-- <mapper resource="org/example/dao/StudentDao.xml"/> -->
        <package name="org.example.dao"/>
    </mappers>

</configuration>

DAO层编写

对应DAO接口需要与Mapper文件名字相同，接口并不需要实现

1
2
3
4
5
public interface StudentDao {
    public List<Student> selectAllStudent();

    public int insertStudent(Student student);
}

对应Mapper

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?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="org.example.dao.StudentDao">

    <!--    resultType指定返回参数，与函数类型对应-->
    <select id="selectAllStudent" resultType="org.example.entity.Student">
        select id, name, email, age
        from student
        order by id
    </select>

    <insert id="insertStudent">
        insert into student
        values (#{id}, #{name}, #{email}, #{age})
    </insert>
</mapper>

工具类编写

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
public class MyBatisUtils {


    private static SqlSessionFactory sqlSessionFactory = null;
    static {
        String config = "mybatis.xml";
        try{
            InputStream inputStream = Resources.getResourceAsStream(config);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        }catch (IOException e){
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession() {
        SqlSession sqlSession = null;
        if(sqlSessionFactory != null){
            sqlSession = sqlSessionFactory.openSession(); //非自动提交
        }
        return sqlSession;
    }
}

所有对数据库的操作都使用sqlSession操作

SqlSessionFactory接口线程安全，

测试

使用jdk动态代理获取DAO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
@Test
public void testSelectStudent() {
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    StudentDao dao = sqlSession.getMapper(StudentDao.class); //动态代理(jdk)
    System.out.println(StudentDao.class);
    List<Student> studentList = dao.selectAllStudent();
    sqlSession.close();
    for (Student stu : studentList) {
        System.out.println(stu);
    }
}

@Test
public void testInsertStudent() {
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    StudentDao dao = sqlSession.getMapper(StudentDao.class);

    Student student = new Student();
    student.setId(1151);
    student.setName("LiHua");
    student.setAge(25);
    student.setEmail("shizurin233@gmail.com");

    int res = dao.insertStudent(student);
    sqlSession.commit();
    sqlSession.close();
    System.out.println(res);
}

不使用动态代理获取DAO

  • 代码重复多，用不到工具类，Mapper
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
@Test
public void TestInsert() throws IOException {
    String config = "mybatis.xml";
    InputStream inputStream = Resources.getResourceAsStream(config);

    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession(true); //自动提交事务
    String sqlId = "org.example.dao.StudentDao.insertStudent";

    Student student = new Student();
    student.setId(2010);
    student.setName("LiHua");
    student.setEmail("shizurin233@gmail.com");
    student.setAge(22);

    int nums = sqlSession.insert(sqlId, student);
    //sqlSession.commit(); //MyBatis 默认不提交事务，需要手动
    System.out.println(nums);

    sqlSession.close();
}

传参

当想要给Mapper文件的SQL语句传参，可以用以下的几个方式

  • 只传一个参数

Mapper：

1
2
3
4
5
<select id="selectStudentById" parameterType="integer" resultType="Student">
    select id, name, email, age
    from student
    where id = #{id}
</select>

接口：

1
public Student selectStudentById(Integer id);


  • 传多个参数（使用注解）

接口：

1
2
public List<Student> selectMultiParam(@Param("StuName") String name,
                                         @Param("StuAge") Integer age);

Mapper：

1
2
3
4
5
6
<select id="selectMultiParam" resultType="Student">
    select id, name, email, age
    from student
    where name = #{StuName}
       or age = #{StuAge}
</select>


  • 传多个参数（使用对象）

新建一个包含参数的类：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
public class QueryParam {

    private String paramName;
    private Integer paramAge;

    public String getParamString() {
        return paramName;
    }

    public void setParamString(String paramString) {
        this.paramName = paramString;
    }

    public Integer getParamAge() {
        return paramAge;
    }

    public void setParamAge(Integer paramAge) {
        this.paramAge = paramAge;
    }
}

接口：

1
public List<Student> selectMultiObject(QueryParam param);

Mapper：

1
2
3
4
5
6
7
8
9
<!--    使用对象传递参数
        详细形式：name = #{paramName, javaType=java.lang.String, jdbcType=VARCHAR}
-->
<select id="selectMultiObject" resultType="Student">
    select id, name, email, age
    from student
    where name = #{paramName}
       or age = #{paramAge}
</select>

测试方法：

1
2
3
4
QueryParam param = new QueryParam();
param.setParamString("LiMing");
param.setParamAge(22);
List<Student> studentList = dao.selectMultiObject(param);


  • 传多个参数（按照参数位置）

接口：

1
public List<Student> selectViaPosition(String name, Integer age);

Mapper：

1
2
3
4
5
6
<select id="selectViaPosition" resultType="Student">
    select id, name, email, age
    from student
    where name = #{arg0}
       or age = #{arg1}
</select>


  • 传多个参数（使用Map）

接口：

1
List<Student> selectMultiMap(Map<String,Object> map);

Mapper：

1
2
3
<select id="selectMultiMap" resultType="Student">
	select id,name,email,age from student where name=#{name} or age =#{age}
</select>

resultMap参数

当数据库的列名和Java类的属性名不同时，可用resultMap来映射这种关系，resultMap和resultType不能一起用

或者还可以使用as别名：

1
2
3
4
<select id="selectTest2" resultMap="studentMap">
    select id as stuId, name as stuName, email as stuMail, age as stuAge
	from student
</select>

动态SQL

If

当 test 的值为 true 时，会将其包含的 SQL 片断拼接到其所在的 SQL 语句中

接口：

1
List<Student> selectIf(Student student);

Mapper：

1
2
3
4
5
6
7
8
9
10
11
<select id="selectIf" resultType="Student">
    select id, name, email, age
    from student
    where id > 0
    <if test="name != null and name != '' ">
        and name = #{name}
    </if>
    <if test="age > 0">
        and age = #{age}
    </if>
</select>

Where

前面的if比较麻烦，是拼接而成，如果第一个if不成立，那么sql语句就不能用，要加1=1这种

where标签则会自动识别，加上where，并且自动判断and要不要加

1
2
3
4
5
6
7
8
9
10
11
12
<select id="selectWhere" resultType="Student">
    select id, name, email, age
    from student
    <where>
        <if test="name != null and name != '' ">
            name = #{name}
        </if>
        <if test="age > 0">
            and age = #{age}
        </if>
    </where>
</select>

ForEach

用来实现where id in (xx,yy,zz)这种操作

  • 传Integer

接口：

1
public List<Student> selectForeach1(List<Integer> list); //传Integer

Mapper：

1
2
3
4
5
6
7
8
<select id="selectForeach1" resultType="Student">
    select id, name, email, age
    from student
    where id in
    <foreach collection="list" item="stuId" open="(" close=")" separator=",">
        #{stuId}
    </foreach>
</select>


  • 传Student对象

接口：

1
public List<Student> selectForeach2(List<Student> students); //传对象

Mapper：

1
2
3
4
5
6
7
8
<select id="selectForeach2" resultType="Student">
    select id, name, email, age
    from student
    where id in
    <foreach collection="list" item="stu" open="(" close=")" separator=",">
        #{stu.id}
    </foreach>
</select>

分页PageHelper

依赖：

1
2
3
4
5
6
7
8
9
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.1.10</version>
</dependency>

<plugins>
    <plugin interceptor="com.github.pagehelper.PageInterceptor" />
</plugins>

测试方法：

1
2
3
4
5
6
7
@Test
public void testSelect() throws IOException {
    //获取第 1 页， 3 条内容
    PageHelper.startPage(1,3);
    List<Student> studentList = studentDao.selectStudents();
    studentList.forEach( stu -> System.out.println(stu));
}
学习总结
#Java #Framework #MyBatis
Java MyBatis 学习记录
https://nanami.run/2022/02/14/java-mybatis/
作者
Nanami
发布于
2022年2月14日
许可协议