MyBatis开发实践

一、mybatis的两种开发方式

  1. 使用原生接口
  2. Mapper代理实现自定义接口

二、使用原生接口开发

  1. 在数据库Test下新建表

    SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_account
-- ----------------------------
DROP TABLE IF EXISTS `t_account`;
CREATE TABLE `t_account`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `username` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `password` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

  2. 在Idea中新建普通maven工程，pom.xml添加如下依赖

    <?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.simon</groupId>
    <artifactId>app</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <simon.version>1.0.0</simon.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>

        <mybatis.version>3.5.7</mybatis.version>
        <mysql-jdbc.verison>8.0.26</mysql-jdbc.verison>
        <lombok.version>1.18.20</lombok.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>${mybatis.version}</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql-jdbc.verison}</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok.version}</version>
            <scope>provided</scope>
        </dependency>
    </dependencies>
    <!--通常情况下，mybatis只能读取resources下的xml文件，为了能读取java下的xml文件，做如下配置-->
    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
        </resources>
    </build>


</project>

  3. 新建实体类Account

    package com.simon.app.entity;
import lombok.AllArgsConstructor;
import lombok.Data;

@Data
@AllArgsConstructor
public class Account {
    private int id;
    private String username;
    private String password;
    private int age;
}

​ @AllArgsConstructor注解自动生成以所有属性作为参数的构造函数

  1. 在resources目录下建立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>
    <environments default="development">
        <environment id="development">
            <!--配置jdbc事务管理-->
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
              <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
              <property name="url" value="jdbc:mysql://localhost:3306/test?useUnicode=true&amp;characterEncoding=UTF-8"/>
              <property name="username" value="root"/>
              <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/simon/app/mapper/AccountMapper.xml"></mapper>
    </mappers>
</configuration>

  2. 建立mapper映射文件AccountMapper.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.simon.app.mapper.AccountMapper">
    <insert id="save" parameterType="com.simon.app.entity.Account">
        insert into t_account(username,password,age) values (#{username},#{password},#{age})
    </insert>
</mapper>
    • namespace的值为映射文件的全路径名去掉xml扩展名
    • insert中的id就是对外提供调用的方法名

  3. 测试代码

    package com.simon.app.test;

import com.simon.app.entity.Account;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;

public class Test {
    public static void main(String[] args) {
        InputStream resource = Test.class.getClassLoader().getResourceAsStream("config.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sessionFactory = sqlSessionFactoryBuilder.build(resource);
        SqlSession sqlSession = sessionFactory.openSession();
        String statement="com.simon.app.mapper.AccountMapper.save";

        Account account=new Account(1,"simon","111111",20);
        sqlSession.insert(statement,account);
        sqlSession.commit();
        sqlSession.close();
    }
}

三、通过mapper代理实现自定义接口

只需要做两件事

  • 自定义接口，定义相关业务方法。

  • 编写与方法相对应的Mapper.xml

    1. 自定义接口

      package com.simon.app.repository;

import com.simon.app.entity.Account;

import java.util.List;

public interface AccountRepository {
    public int save(Account obj);
    public int update(Account obj);
    public int delete(int id);
    public List<Account> findAll();
    public Account findById(int id);
}

    2. 不需要提供实现类，直接提供mapper.xml即可，定义接口方法对应的SQL语句，statement 标签可根据SQL执行的业务选择insert、update、delete、select。mybatis会根据规则自动创建接口对应的实现类。

      规则：

      • Mapper.xml中的namespace为接口的全类名

      • Mapper.xml中statement中的id为接口中对应的方法名

      • Mapper.xml中的statement的parameterType和接口中对应的方法的参数类型一致

      • Mapper.xml中的statement的resultType和接口中对应的方法的返回值类型一致

        <?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.simon.app.repository.AccountRepository">
  <insert id="save" parameterType="com.simon.app.entity.Account">
      insert into t_account(username,password,age) values (#{username},#{password},#{age})
  </insert>
  <update id="update" parameterType="com.simon.app.entity.Account">
      update t_account set username=#{username},password=#{password},age=#{age} where id=#{id}
  </update>
    <delete id="delete" parameterType="java.lang.Long">
        delete from t_account where id=#{id}
    </delete>
    <select id="findAll" resultType="com.simon.app.entity.Account">
        select * from t_account
    </select>
    <select id="findById" parameterType="int" resultType="com.simon.app.entity.Account">
        select * from t_account where id=#{id}
    </select>
</mapper>

    3. 在config.xml中注册AccountRepository.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>
    <environments default="development">
        <environment id="development">
            <!--配置jdbc事务管理-->
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/test?useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/simon/app/mapper/AccountMapper.xml"></mapper>
        <mapper resource="com/simon/app/repository/AccountRepository.xml"></mapper>
    </mappers>
</configuration>

    4. 测试

      package com.simon.app.test;

import com.simon.app.entity.Account;
import com.simon.app.repository.AccountRepository;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;
import java.util.List;

public class Test2 {
    public static void main(String[] args) {
        InputStream resource = Test2.class.getClassLoader().getResourceAsStream("config.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = sqlSessionFactoryBuilder.build(resource);
        SqlSession sqlSession = factory.openSession();
        //获取接口的代理对象
        AccountRepository mapper = sqlSession.getMapper(AccountRepository.class);

//        Account obj=new Account(2,"linda","111111",46);
//        mapper.save(obj);
//        sqlSession.commit();
//        sqlSession.close();

//        List<Account> all = mapper.findAll();
//        for(Account obj:all){
//            System.out.println(obj);
//        }

        Account account = mapper.findById(3);
        System.out.println(account);
        account.setPassword("123456");
        account.setUsername("simon");
        account.setAge(56);
        mapper.update(account);
        sqlSession.commit();
        sqlSession.close();
    }
}

四、多条件查询

    • 接口中定义一个方法

      public Account findByNameAndAge(String username,int age);

    • 在mapper.xml增加如下statement

          <select id="findByNameAndAge" resultType="com.simon.app.entity.Account">
        select * from t_account where username =#{param1} and age=#{param2}
    </select>

      由于接口方法中有多个参数传入，而select中不能设置多个parameterType，索性就不写了，取而代之的是在SQL语句中用占位符param1和param2代替，从而达到多条件查询的目的，param名称也可用arg替换，如：arg1, arg0, param1, param2

五、一对多和多对一级联查询

5.1、建表脚本

  • SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for classes
-- ----------------------------
DROP TABLE IF EXISTS `classes`;
CREATE TABLE `classes`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of classes
-- ----------------------------
INSERT INTO `classes` VALUES (1, '一班');
INSERT INTO `classes` VALUES (2, '二班');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `cid` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `class_student`(`cid`) USING BTREE,
  CONSTRAINT `class_student` FOREIGN KEY (`cid`) REFERENCES `classes` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '小黄', 1);
INSERT INTO `student` VALUES (2, '校长', 1);
INSERT INTO `student` VALUES (3, '厨子', 2);
INSERT INTO `student` VALUES (4, '胖子', 2);
INSERT INTO `student` VALUES (5, '诸葛亮', 2);

SET FOREIGN_KEY_CHECKS = 1;

5.2 、从多方级联查询

一个班级对应多个学生，如果查询学生并级联查询出对应的班级就是从多方进行查询。

  1. pojo定义
package com.simon.app.entity;

import lombok.Data;
import java.util.List;

@Data
public class Classes {
    private long id;
    private String name;
    private List<Student> students;
}


package com.simon.app.entity;

import lombok.Data;

@Data
public class Student {
    private long id;
    private String name;
    private Classes classes;
}
  • 定义pojo时不能使用@AllArgsConstructor注解
  • 此时学生pojo里定义一个班级属性classes

  1. 定义SQL

    select s.id,s.name,c.id as cid,c.`name` as cname from student s,classes c where s.id=#{id} and s.cid=c.id

    该语句通过学生id号查询，并关联查询对应的班级。

  2. 定义resultMap

    默认情况下，mybatis的select元素中resultType属性对应的是一个独立的javabean，sql语句中的字段名称和javabean中的属性通过名称一一对应，但是关联查询时，无法从sql查询语句中的字段名称对应到javabean中的对象属性上，为此需要重新定义resultType，而这个resultType就是resultMap，通过上图可知，id和name和Student类的id和name属性一一对应，但是cid和cname如何对应到Student类的classes属性上呢，此时就需要使用association标签了，代码如下：

        <resultMap id="studentMap" type="com.simon.app.entity.Student">
       <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <association property="classes" javaType="com.simon.app.entity.Classes">
            <id column="cid" property="id"></id>
            <result column="cname" property="name"></result>
        </association>
    </resultMap>

  3. 将select标签的resultType更换为resultMap

    <select id="findById" parameterType="long" resultMap="studentMap">
   select s.id,s.name,c.id as cid,c.`name` as cname from student s,classes c where s.id=#{id} and s.id=c.id
</select>

    完整的AccountRepository.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.simon.app.repository.StudentRepository">
    <resultMap id="studentMap" type="com.simon.app.entity.Student">
       <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <association property="classes" javaType="com.simon.app.entity.Classes">
            <id column="cid" property="id"></id>
            <result column="cname" property="name"></result>
        </association>
    </resultMap>
    <select id="findById" parameterType="long" resultMap="studentMap">
        select s.id,s.name,c.id as cid,c.`name` as cname from student s,classes c where s.id=#{id} and s.id=c.id
    </select>
</mapper>

    记住将xml加入到config.xml中

  4. 定义接口StudentRepository.java

    package com.simon.app.repository;

import com.simon.app.entity.Student;

public interface StudentRepository {
    public Student findById(long id);
}

  5. 定义测试类

    package com.simon.app.test;

import com.simon.app.entity.Student;
import com.simon.app.repository.StudentRepository;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;

public class Test4 {
    public static void main(String[] args) {
        InputStream resource = Test2.class.getClassLoader().getResourceAsStream("config.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = sqlSessionFactoryBuilder.build(resource);
        SqlSession sqlSession = factory.openSession();
        //获取接口的代理对象
        StudentRepository mapper = sqlSession.getMapper(StudentRepository.class);

        Student student = mapper.findById(1L);
        System.out.println(student);
        sqlSession.close();
    }
}

  6. 运行结果

5.3、从一方级联查询

Student和Classes两个javabean不变。

  1. 定义测试SQL

    select s.id,s.name,c.id as cid,c.`name` as cname from student s,classes c where c.id=1 and s.cid=c.id

    结果如下：

可以看到，通过班级id可以查询到对应多条学生数据

  1. 定义接口ClassesRepository.java

    package com.simon.app.repository;

import com.simon.app.entity.Classes;

public interface ClassesRepository {
    public Classes findById(long id);
}

  2. 定义ClassesRepository.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.simon.app.repository.ClassesRepository">
    <resultMap id="classesMap" type="com.simon.app.entity.Classes">
       <id column="cid" property="id"></id>
        <result column="cname" property="name"></result>
        <collection property="students" ofType="com.simon.app.entity.Student">
            <id column="id" property="id"></id>
            <result column="name" property="name"></result>
        </collection>
    </resultMap>
    <select id="findById" parameterType="long" resultMap="classesMap">
        select s.id,s.name,c.id as cid,c.`name` as cname from student s,classes c where c.id=#{id} and s.cid=c.id
    </select>
</mapper>

    可以看到集合用collection替换，javaType换成ofType，ofType里面的值实际上是泛型对应的类型，即Student类
    sql字段和标签属性的对应关系如下：

  3. 定义测试类

    package com.simon.app.test;

import com.simon.app.entity.Classes;
import com.simon.app.repository.ClassesRepository;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;

public class Test5 {
    public static void main(String[] args) {
        InputStream resource = Test2.class.getClassLoader().getResourceAsStream("config.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = sqlSessionFactoryBuilder.build(resource);
        SqlSession sqlSession = factory.openSession();
        //获取接口的代理对象
        ClassesRepository mapper = sqlSession.getMapper(ClassesRepository.class);

        Classes classes = mapper.findById(1L);
        System.out.println(classes);
        sqlSession.close();
    }
}

  4. 结果

posted @ 2021-09-29 18:26  疯狗强尼  阅读(44)  评论(0)  编辑  收藏  举报