Mybatis使用与配置

最新推荐文章于 2022-10-26 20:20:15 发布

攻城老湿

最新推荐文章于 2022-10-26 20:20:15 发布

阅读量153

点赞数 1

文章标签：数据库 mybatis mysql java maven

本文链接：https://blog.csdn.net/yorickjun/article/details/116645757

版权

本测试项目涵盖了Mybatis框架的入门快速构建、基于xml的CRUD操作、配置信息说明、基于注解的CRUD操作、多对一和一对多的映射操作以及动态SQL相关内容。望可以帮助到有相关需求的伙伴。
测试代码Github地址：https://github.com/yaokuku123/mybatis-demo

一. 搭建Mybatis首个程序

创建数据库

CREATE DATABASE `mybatis`;

USE `mybatis`;

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
`id` int(20) NOT NULL,
`name` varchar(30) DEFAULT NULL,
`pwd` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `user`(`id`,`name`,`pwd`) values (1,'alice','123456'),(2,'bob','abcdef'),(3,'yorick','987654');

导入Maven依赖

<dependency>
   <groupId>org.mybatis</groupId>
   <artifactId>mybatis</artifactId>
   <version>3.5.4</version>
</dependency>
<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.6</version>
</dependency>
 <dependency>
     <groupId>org.projectlombok</groupId>
     <artifactId>lombok</artifactId>
     <version>1.18.12</version>
</dependency>
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
</dependency>

编写Mybatis核心配置文件

<?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>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&amp;useUnicode=true&amp;characterEncoding=utf8"/>
                <property name="username" value="root"/>
                <property name="password" value="199748"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/yqj/dao/userMapper.xml"/>
    </mappers>
</configuration>

编写MyBatis工具类

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;

public class MybatisUtils {

   private static SqlSessionFactory sqlSessionFactory;

   static {
       try {
           String resource = "mybatis-config.xml";
           InputStream inputStream = Resources.getResourceAsStream(resource);
           sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
      } catch (IOException e) {
           e.printStackTrace();
      }
  }

   //获取SqlSession连接
   public static SqlSession getSession(){
       return sqlSessionFactory.openSession();
  }

}

创建实体类

package com.yqj.pojo;

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

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private int id;  //id
    private String name;   //姓名
    private String pwd;   //密码
}

编写Mapper接口类

package com.yqj.dao;

import com.yqj.pojo.User;

import java.util.List;

public interface UserMapper {
    List<User> selectUser();
}

编写Mapper.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">
<mapper namespace="com.yqj.dao.UserMapper">
    <select id="selectUser" resultType="com.yqj.pojo.User">
        select * from user
    </select>
</mapper>

编写测试类

package com.yqj.test;

import com.yqj.dao.UserMapper;
import com.yqj.pojo.User;
import com.yqj.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class MyTest {

    @Test
    public void test(){
        SqlSession session = MybatisUtils.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        List<User> users = mapper.selectUser();
        for (User user : users) {
            System.out.println(user);
        }
        session.close();
    }
}

二. Mybatis基于xml的CRUD操作

在Mapper接口中定义相关方法

package com.yqj.dao;

import com.yqj.pojo.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface UserMapper {
    //查询所有
    List<User> selectUser();

    //根据id查询用户
    User selectById(int id);

    //添加用户
    int addUser(User user);

    //修改用户
    int updateUser(User user);

    //删除用户
    int deleteUser(int id);

    //根据用户名和密码查询(传递两个参数)
    User selectByNameAndPwd(@Param("name") String name,@Param("pwd") String pwd);

    //根据用户名和密码查询(万能Map)
    User selectByMap(Map<String,Object> map);
    
    //分页查询
    List<User> selectUserByPage(Map<String,Integer> map);
}

在对应接口的xml文件中编写相应的sql语句

<?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="com.yqj.dao.UserMapper">
    <!--查询所有-->
    <select id="selectUser" resultType="com.yqj.pojo.User">
        select * from user
    </select>

    <!--根据id查询用户-->
    <select id="selectById" resultType="com.yqj.pojo.User">
        select * from user where id=#{id}
    </select>

    <!--添加用户-->
    <insert id="addUser" parameterType="com.yqj.pojo.User">
        insert into user(id,name,pwd) values (#{id},#{name},#{pwd})
    </insert>

    <!--修改用户-->
    <update id="updateUser" parameterType="com.yqj.pojo.User">
        update user set name=#{name},pwd=#{pwd} where id=#{id}
    </update>

    <!--删除用户-->
    <delete id="deleteUser" >
        delete from user where id=#{id}
    </delete>

    <!--根据用户名和密码查询(传递两个参数)-->
    <select id="selectByNameAndPwd" resultType="com.yqj.pojo.User">
        select * from user where name=#{name} and pwd=#{pwd}
    </select>

    <!--根据用户名和密码查询(万能Map)-->
    <select id="selectByMap" resultType="com.yqj.pojo.User">
        select * from user where name=#{username} and pwd=#{password}
    </select>

    <!--分页查询-->
    <select id="selectUserByPage" resultType="com.yqj.pojo.User">
        select * from user limit #{startIndex},#{pageSize}
    </select>
</mapper>

编写测试类测试CRUD操作

package com.yqj.test;

import com.yqj.dao.UserMapper;
import com.yqj.pojo.User;
import com.yqj.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

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

/**
 * Copyright(C),2019-2020,XXX公司
 * FileName: MyTest
 * Author: yaoqijun
 * Date: 2020/8/18 20:10
 */
public class MyTest {

    //查询所有
    @Test
    public void testSelectAll(){
        SqlSession session = MybatisUtils.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        List<User> users = mapper.selectUser();
        for (User user : users) {
            System.out.println(user);
        }
        session.close();
    }

    //根据id查询用户
    @Test
    public void testSelectById(){
        SqlSession session = MybatisUtils.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = mapper.selectById(1);
        System.out.println(user);
        session.close();
    }

    //添加用户
    @Test
    public void testAddUser(){
        SqlSession session = MybatisUtils.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        mapper.addUser(new User(4,"tom","123"));
        session.commit();
        session.close();
    }

    //修改用户
    @Test
    public void testUpdateUser(){
        SqlSession session = MybatisUtils.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = mapper.selectById(1);
        user.setName("aliceUpdate");
        mapper.updateUser(user);
        session.commit();
        session.close();
    }

    //删除用户
    @Test
    public void testDeleteUser(){
        SqlSession session = MybatisUtils.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        mapper.deleteUser(4);
        session.commit();
        session.close();
    }

    //根据用户名和密码查询(传递两个参数)
    @Test
    public void testSelectByNameAndPwd(){
        SqlSession session = MybatisUtils.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = mapper.selectByNameAndPwd("alice", "123456");
        System.out.println(user);
        session.close();
    }

    //根据用户名和密码查询(万能Map)
    @Test
    public void testSelectByMap(){
        SqlSession session = MybatisUtils.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        Map<String,Object> map = new HashMap<>();
        map.put("username","alice");
        map.put("password","123456");
        User user = mapper.selectByMap(map);
        System.out.println(user);
        session.close();
    }
    
    //分页查询
    @Test
    public void testSelectUserByPage(){
        SqlSession session = MybatisUtils.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        Map<String,Integer> map = new HashMap<>();
        map.put("startIndex",1);
        map.put("pageSize",2);
        List<User> users = mapper.selectUserByPage(map);
        for (User user : users) {
            System.out.println(user);
        }
        session.close();
    }
}

三. 核心配置文件

mybatis-config.xml包含了mybatis的核心配置

可以配置如下内容：

configuration(配置)
properties(属性)
settings(设置)
typeAliases(类型别名)
typeHandlers(类型处理器)
objectFactory(对象工厂)
plugins(插件)
environments(环境配置)
environment(环境变量)
transactionManager(事务管理器)
dataSource(数据源)
databaseIdProvider(数据库厂商标识)
mappers(映射器)

environments

说明：

environments：可以配置mybatis运行的多套环境，每个环境使用一个子元素environment指定。但必须指定其中的一个为默认运行环境(default)。
environment：指定其中的一个运行环境。
transactionManager：JDBC | MANAGED两种事务管理器
dataSource：UNPOOLED|POOLED|JNDI 使用标准的 JDBC 数据源接口来配置 JDBC 连接对象的资源。

<environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&amp;useUnicode=true&amp;characterEncoding=utf8"/>
                <property name="username" value="root"/>
                <property name="password" value="199748"/>
            </dataSource>
        </environment>
    </environments>

mappers

说明：

mappers：定义映射SQL语句文件。需要告诉 MyBatis 到哪里去找到SQL语句。
引入资源的方式：
- 使用相对于类路径的资源引用

* 使用完全限定资源定位符

```xml
<mappers>
 <mapper url="file:D:\study\code\test\mybatis\mybatis-config\src\main\resources\com\yqj\dao\UserMapper.xml"/>
</mappers>

使用映射器接口实现类的完全限定类名，需要配置文件名称和接口名称一致，并且位于同一目录下

<mappers>
 <mapper class="com.yqj.dao.UserMapper"/>
</mappers>

将包内的映射器接口实现全部注册为映射器，但是需要配置文件名称和接口名称一致，并且位于同一目录下

<mappers>
 <package name="com.yqj.dao"/>
</mappers>

properties

说明：

properties：数据库这些属性都是可外部配置且可动态替换的，既可以在典型的 Java 属性文件中配置，亦可通过 properties 元素的子元素来传递。

使用：

在资源目录下新建一个db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=utf8
username=root
password=199748

将文件导入properties 配置文件

<configuration>
   <!--导入properties文件-->
   <properties resource="db.properties"/>

   <environments default="development">
       <environment id="development">
           <transactionManager type="JDBC"/>
           <dataSource type="POOLED">
               <property name="driver" value="${driver}"/>
               <property name="url" value="${url}"/>
               <property name="username" value="${username}"/>
               <property name="password" value="${password}"/>
           </dataSource>
       </environment>
   </environments>
   <mappers>
       <mapper resource="mapper/UserMapper.xml"/>
   </mappers>
</configuration>

typeAliases

说明：

typeAliases：类型别名是为 Java 类型设置一个短的名字。存在的意义仅在于用来减少类完全限定名的冗余。
使用方式：
- 将某个类配置别名.当这样配置时，User可以用在任何使用com.yqj.pojo.User的地方。(大小写不区分)
```
<typeAliases>
   <typeAlias type="com.yqj.pojo.User" alias="User"/>
</typeAliases>
```
- 将某个包下的全部POJO对象起别名。每一个在包 com.kuang.pojo 中的 Java Bean，在没有注解的情况下，会使用 Bean 的首字母小写的非限定类名来作为它的别名。(大小写不区分)
```
<typeAliases>
   <package name="com.yqj.pojo"/>
</typeAliases>
```

settings

说明：

settings：用于进行额外的mybatis使用配置
常用配置

<settings>
 <setting name="cacheEnabled" value="true"/>
 <setting name="lazyLoadingEnabled" value="true"/>
 <setting name="multipleResultSetsEnabled" value="true"/>
 <setting name="useColumnLabel" value="true"/>
 <setting name="useGeneratedKeys" value="false"/>
 <setting name="autoMappingBehavior" value="PARTIAL"/>
 <setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>
 <setting name="defaultExecutorType" value="SIMPLE"/>
 <setting name="defaultStatementTimeout" value="25"/>
 <setting name="defaultFetchSize" value="100"/>
 <setting name="safeRowBoundsEnabled" value="false"/>
 <setting name="mapUnderscoreToCamelCase" value="false"/>
 <setting name="localCacheScope" value="SESSION"/>
 <setting name="jdbcTypeForNull" value="OTHER"/>
 <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
 <setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>

四. Mybatis基于注解的CRUD操作

按照xml的配置搭建环境，区别在于根据注解的CRUD无需创建UserMapper.xml配置文件。并且在核心配置文件中配置mapper绑定到接口

<mappers>
    <mapper class="com.yqj.dao.UserMapper"/>
</mappers>

在接口处编写基于注解的CRUD

package com.yqj.dao;

import com.yqj.pojo.User;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface UserMapper {

    //查询所有
    @Select("select * from user")
    List<User> selectUsers();

    //按id查询
    @Select("select * from user where id=#{id}")
    User selectById(@Param("id") int id);

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

    //修改
    @Update("update user set name=#{name},pwd=#{pwd} where id=#{id}")
    int updateUser(User user);

    //删除
    @Delete("delete from user where id=#{id}")
    int deleteUser(@Param("id") int id);
}

五. 多对一和一对多操作

环境搭建

实例：学生和老师建立多对一的关系，老师和学生建立一对多的关系。

创建多对一的数据表

CREATE TABLE `teacher` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO teacher(`id`, `name`) VALUES (1, '姚老师');

CREATE TABLE `student` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
`tid` INT(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fktid` (`tid`),
CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8


INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');

引入依赖

<dependencies>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.4</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.6</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.12</version>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
</dependencies>

编写核心配置文件

数据库相关的配置信息文件 db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=utf8
username=root
password=199748

核心配置文件 mybatis-config.xml

<?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>
    <properties resource="db.properties"/>

    <typeAliases>
        <package name="com.yqj.pojo"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <package name="com.yqj.dao"/>
    </mappers>
</configuration>

工具类

package com.yqj.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

public class MybatisUtils {
    private static SqlSessionFactory sessionFactory;

    static {
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSession(){
        return sessionFactory.openSession();
    }
}

实体类对象

Student对象

package com.yqj.pojo;

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

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

Teacher对象

package com.yqj.pojo;

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

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

Mapper接口

StudentMapper接口

package com.yqj.dao;

public interface StudentMapper {
}

TeacherMapper接口

package com.yqj.dao;

public interface TeacherMapper {
}

Mapper接口对应的配置文件

StudentMapper.xml配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yqj.dao.StudentMapper">

</mapper>

TeacherMapper.xml配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yqj.dao.TeacherMapper">

</mapper>

至此，测试环境搭建完毕

多对一的相关操作

在Student对象中添加Techer字段

package com.yqj.pojo;

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

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private int id;
    private String name;
    //多个学生可以是同一个老师，即多对一
    private Teacher teacher;
}

在Student对应的Mapper接口中添加查询所有学生及对应老师的信息方法

package com.yqj.dao;

import com.yqj.pojo.Student;

import java.util.List;

public interface StudentMapper {
    List<Student> getStudents();
}

在Mapper对应的配置文件中编写多对一的相关SQL( 直接查询出结果，进行结果集的映射)

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yqj.dao.StudentMapper">

    <resultMap id="manyToOne" type="Student">
        <id property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <id property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>

    <select id="getStudents" resultMap="manyToOne">
        select s.id sid, s.name sname ,t.id tid,t.name tname
        from student s,teacher t
        where s.tid = t.id
    </select>

</mapper>

测试

package com.yqj.test;

import com.yqj.dao.StudentMapper;
import com.yqj.pojo.Student;
import com.yqj.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class MyTest {
    @Test
    public void testGetStudents(){
        SqlSession session = MybatisUtils.getSession();
        StudentMapper mapper = session.getMapper(StudentMapper.class);
        List<Student> students = mapper.getStudents();
        for (Student student : students) {
            System.out.println(student);
        }
        session.close();
    }
}

成功得到查询结果，可以发现teacher对象已经成功封装数据

Student(id=1, name=小明, teacher=Teacher(id=1, name=姚老师))
Student(id=2, name=小红, teacher=Teacher(id=1, name=姚老师))
Student(id=3, name=小张, teacher=Teacher(id=1, name=姚老师))
Student(id=4, name=小李, teacher=Teacher(id=1, name=姚老师))
Student(id=5, name=小王, teacher=Teacher(id=1, name=姚老师))

一对多的相关操作

在Teacher对象中增加List<Student>字段，表示一个老师对应多个学生

package com.yqj.pojo;

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

import java.util.List;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
    private int id;
    private String name;
    //一个老师多个学生
    private List<Student> students;
}

在Teacher的Mapper接口中添加按照id查找老师的接口方法

package com.yqj.dao;

import com.yqj.pojo.Teacher;

public interface TeacherMapper {

    Teacher getTeacher(int id);
}

在Mapper对应的配置文件中编写一对多的SQL语句

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yqj.dao.TeacherMapper">

    <resultMap id="OneToMany" type="Teacher">
        <id property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="students" ofType="Student">
            <id property="id" column="sid"/>
            <result property="name" column="sname"/>
        </collection>
    </resultMap>

    <select id="getTeacher" resultMap="OneToMany">
        select t.id tid,t.name tname,s.id sid,s.name sname
        from teacher t,student s
        where s.tid = t.id  and t.id = #{id}
    </select>

</mapper>

测试

package com.yqj.test;


import com.yqj.dao.TeacherMapper;
import com.yqj.pojo.Teacher;
import com.yqj.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class MyTest {
   

    @Test
    public void testGetTeacher(){
        SqlSession session = MybatisUtils.getSession();
        TeacherMapper mapper = session.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);
        session.close();
    }
}

得到结果，从结果可以发现已经成功将Student数据封装进Teacher对象中

Teacher(id=1, name=姚老师, students=[Student(id=1, name=小明, teacher=null), Student(id=2, name=小红, teacher=null), Student(id=3, name=小张, teacher=null), Student(id=4, name=小李, teacher=null), Student(id=5, name=小王, teacher=null)])

小结

1、关联-association
2、集合-collection
3、所以association 是用于一对一和多对一，而collection是用于一对多的关系
4、JavaType和ofType 都是用来指定对象类型的
    * JavaType 是用来指定pojo中属性的类型
    * ofType 指定的是映射到list集合属性中pojo的类型。

六. 动态SQL

环境搭建
在Mapper中定义相关操作的接口

package com.yqj.dao;

import com.yqj.pojo.User;

import java.util.List;
import java.util.Map;

public interface UserMapper {

    //根据姓名和密码动态筛选用户
    List<User> selectUserByNameAndPwd(Map map);

    //更新用户，动态更改姓名和密码
    int updateUser(User user);

    //查找多个指定id的用户
    List<User> selectUsers(Map map);
}

在Mapper的配置文件中实现动态SQL

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yqj.dao.UserMapper">

    <!--根据姓名和密码动态筛选用户-->
    <select id="selectUserByNameAndPwd" resultType="User">
        select * from user
        <where>
            <if test="name!=null">
                name=#{name}
            </if>
            <if test="pwd!=null">
                and pwd=#{pwd}
            </if>
        </where>
    </select>

    <!--更新用户，动态更改姓名和密码-->
    <update id="updateUser">
        update user
        <set>
            <if test="name!=null">
                name=#{name},
            </if>
            <if test="pwd!=null">
                pwd=#{pwd}
            </if>
        </set>
        where id=#{id}
    </update>

    <!--查找多个指定id的用户-->
    <select id="selectUsers" resultType="User">
        select * from user
        <where>
            <foreach collection="ids" item="id" open="(" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>
</mapper>

测试

package com.yqj.test;

import com.yqj.dao.UserMapper;
import com.yqj.pojo.User;
import com.yqj.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MyTest {

    //根据姓名和密码动态筛选用户
    @Test
    public void testSelectUserByNameAndPwd(){
        SqlSession session = MybatisUtils.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        Map<String,Object> map = new HashMap<>();
        map.put("name","jerry");
        map.put("pwd","123");
        List<User> users = mapper.selectUserByNameAndPwd(map);
        for (User user : users) {
            System.out.println(user);
        }
        session.close();
    }

    //更新用户，动态更改姓名和密码
    @Test
    public void testUpdateUser(){
        SqlSession session = MybatisUtils.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = new User();
        user.setId(2);
        user.setName("ketty");
        user.setPwd("233");
        mapper.updateUser(user);
        session.commit();
        session.close();
    }

    //查找多个指定id的用户
    @Test
    public void testSelectUsers(){
        SqlSession session = MybatisUtils.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        Map<String,Object> map = new HashMap<>();
        List<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(3);
        map.put("ids",ids);
        List<User> users = mapper.selectUsers(map);
        for (User user : users) {
            System.out.println(user);
        }
        session.close();
    }
}

攻城老湿

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
Mybatis使用与配置

本测试项目涵盖了Mybatis框架的入门快速构建、基于xml的CRUD操作、配置信息说明、基于注解的CRUD操作、多对一和一对多的映射操作以及动态SQL相关内容。望可以帮助到有相关需求的伙伴。测试代码Github地址：https://github.com/yaokuku123/mybatis-demo一. 搭建Mybatis首个程序创建数据库CREATE DATABASE `myb...
复制链接

扫一扫