Mybatis简单上手

作者: yaco | 来源:发表于2020-08-24 11:57 被阅读0次

一、搭建MySQL环境

创建maven项目
导入maven依赖

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

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

    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
</dependencies>

配置xml和properties文件的扫描方式

    <build>
        <resources>
            <!--配置资源扫描-resources包下面的xml文件和properties文件都可以被扫描到-->
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>

            <!--配置资源扫描-java包下面的xml文件和properties文件都可以被扫描到-->
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>

完整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>com.yaco</groupId>
    <artifactId>mybatis</artifactId>
    <packaging>pom</packaging>
    <version>1.0-SNAPSHOT</version>
    <modules>
        <module>mybatis01</module>
        <module>mybatis02</module>
        <module>mybatis03</module>
    </modules>

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

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

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>
    
    <build>
        <resources>
            <!--配置资源扫描-resources包下面的xml文件和properties文件都可以被扫描到-->
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>

            <!--配置资源扫描-java包下面的xml文件和properties文件都可以被扫描到-->
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>

</project>

二、实现简单的增删改查

1、创建数据库

创建一个简单的用户表，表中含有三个属性，用户id，用户名和用户密码

CREATE DATABASE mybatis;
USE mybatis;

# 创建表
CREATE TABLE `user`(
    `id` INT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(30) DEFAULT NULL,
    `pwd` VARCHAR(30) DEFAULT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;

# 插入数据
INSERT INTO `user`(`id`,`name`,`pwd`) VALUES
(1,'张三','123456'),
(2,'李四','123456'),
(3,'狂神','123456')

2、创建`mybatis-config.xml`配置文件

主要配置数据连接的相关信息，包括驱动器、url、连接数据库的用户名和密码、是否启动数据库连接池等信息

使用mapper配置userMapper.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>

    <!--配置别名-->
    <typeAliases>
        <typeAlias alias="User" type="com.yaco.pojo.User"></typeAlias>
    </typeAliases>

    <!--配置环境-->
    <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=false&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;autoReconnect=true&amp;failOverReadOnly=false"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
        <mapper resource="com/yaco/dao/UserMapper.xml"/>
    </mappers>

</configuration>

3、创建对应的数据库中的实体类

实体类对应数据库中User表中的字符名，此处与表中字段名完全一致，也可以不一致

public class User {

    private int id;
    private String name;
    private String pwd;

    public User() {
    }

    public User(int id, String name, String pwd) {
        this.id = id;
        this.name = name;
        this.pwd = pwd;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                '}';
    }
}

创建UserMapper的持久层接口

public interface UserMapper {

    // 查
    List<User> getUserList();

    // 增
    int addUser(User user);

    // 删
    void deleteById(int id);

    // 该
    int update(User user);

    // 查找一个
    User findById(int id);

    // 根据用户名和密码查询，使用map
    User findByNameAnsPwd(Map<String, Object> map);

    // 模糊查询
    List<User> findUserLike(String value);
}

4、创建mapper映射文件

<?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.yaco.dao.UserMapper">

    <update id="update" parameterType="User">
        update user set name=#{name}, pwd=#{pwd}  where id = #{id}
    </update>

    <delete id="deleteById">
        delete from user where id = #{id}
    </delete>

    <select id="getUserList" resultType="User">
        select * from user
    </select>

    <select id="findById" resultType="User" parameterType="int">
        select * from user where id = #{id}
    </select>

    <select id="findByNameAnsPwd" resultType="User" parameterType="map">
        select * from user where name=#{name} and pwd=#{pwd}
    </select>

    <select id="findUserLike" resultType="User">
        select * from user where name like #{value}
    </select>

    <insert id="addUser" parameterType="User">
        insert into user (id, name, pwd) values (#{id},#{name},#{pwd})
    </insert>

</mapper>

5、创建一个MybatisUtil工具类

MybatisUtil工具类用于获取执行映射文件中的sql语句的SqlSession

public class MybatisUtil {

    private static SqlSessionFactory sqlSessionFactory;

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

    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession();
    }
}

6、测试类

在test文件下做测试，保证包名与所测试的类包结构一致

public class UserDaoTest {

    @Test
    public void testList() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserMapper userDao = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userDao.getUserList();
        for (User user : userList) {
            System.out.println(user);
        }
        sqlSession.close();
    }

    @Test
    public void testFindOne() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserMapper userDao = sqlSession.getMapper(UserMapper.class);
        User user = userDao.findById(1);
        System.out.println(user);
        sqlSession.close();
    }

    @Test
    public void testInsert() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserMapper userDao = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setId(5);
        user.setName("hj");
        user.setPwd("123456");
        int res = userDao.addUser(user);
        if(res > 0) {
            System.out.println("插入成工");
        }
        System.out.println(user);
        // 插入要提交事务
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void testUpdate() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserMapper userDao = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setId(5);
        user.setName("yaco");
        user.setPwd("123456");
        int res = userDao.update(user);
        if(res > 0) {
            System.out.println("更新成功");
        }
        System.out.println(user);
        // 插入要提交事务
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void testDelete() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserMapper userDao = sqlSession.getMapper(UserMapper.class);
        userDao.deleteById(5);
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void testMap() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserMapper userDao = sqlSession.getMapper(UserMapper.class);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("name", "张三");
        map.put("pwd","123456");
        User user = userDao.findByNameAnsPwd(map);
        System.out.println(user);
        sqlSession.close();
    }

    @Test
    public void testLike() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserMapper userDao = sqlSession.getMapper(UserMapper.class);
        List<User> users = userDao.findUserLike("%李%");
        for (User user : users) {
            System.out.println(user);
        }
        sqlSession.close();
    }
}

三、使用Mybatis实现多对一查询

1、搭建数据表结构

创建两张表，分别式学生表和老师表，一个学生对应一个老师，一个老师可以对应多名学生。

CREATE TABLE student(
    id INT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(30) DEFAULT NULL
    tid INT(20) DEFAULT NULL,
    CONSTRAINT FOREIGN KEY(tid) REFERENCES teacher(id)
)ENGINE = INNODB DEFAULT CHARSET utf8

CREATE TABLE teacher(
    id INT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(20),
)ENGINE = INNODB DEFAULT CHARSET utf8

添加一些数据

2、搭建实体类及映射环境

实体类

public class Student {

    private int id;
    private String name;
    private Teacher teacher;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Teacher getTeacher() {
        return teacher;
    }

    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", teacher=" + teacher +
                '}';
    }
}

public class Teacher {

    private int id;
    private String name;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

StudentMapper持久层接口，查询学生，实现多对一的功能

public interface StudentMapper {

    List<Student> selectStudentList();

    List<Student> selectStudentList2();

    Teacher selectTeacherById(int id);
}

3、使用嵌套查询完成多对一查询

    <!--多对一的第一种方式： 使用嵌套查询-->
    <resultMap id="studentTeacher" type="Student">
        <result property="id" column="sid" />
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>

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

4、使用子查询完成多对一查询

    <!--多对一的第二种方式，使用子查询-->
    <resultMap id="studentTeacher2" type="Student">
        <result property="id" column="id" />
        <result property="name" column="name"/>
        <association property="teacher" javaType="Teacher" select="selectTeacherById" column="tid"></association>
    </resultMap>

    <select id="selectStudentList2" resultMap="studentTeacher2">
        select * from student
    </select>

    <select id="selectTeacherById" resultType="Teacher">
        select * from teacher where id = #{id};
    </select>

5、测试

public class MybatisTest {

    @Test
    public void test() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> students = mapper.selectStudentList();
        for (Student student : students) {
            System.out.println(student);
        }
        sqlSession.close();
    }

    @Test
    public void test2() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> students = mapper.selectStudentList2();
        for (Student student : students) {
            System.out.println(student);
        }
        sqlSession.close();
    }
}

测试输出

// 嵌套查询结果
Student{id=1, name='小明', teacher=Teacher{id=1, name='数学老师'}}
Student{id=2, name='小红', teacher=Teacher{id=2, name='语文老师'}}
Student{id=3, name='小兰', teacher=Teacher{id=3, name='英语老师'}}
Student{id=4, name='小宋', teacher=Teacher{id=1, name='数学老师'}}
Student{id=5, name='小丽', teacher=Teacher{id=2, name='语文老师'}}
Student{id=6, name='小叮', teacher=Teacher{id=3, name='英语老师'}}

// 子查询结果
Student{id=1, name='小明', teacher=Teacher{id=1, name='数学老师'}}
Student{id=2, name='小红', teacher=Teacher{id=2, name='语文老师'}}
Student{id=3, name='小兰', teacher=Teacher{id=3, name='英语老师'}}
Student{id=4, name='小宋', teacher=Teacher{id=1, name='数学老师'}}
Student{id=5, name='小丽', teacher=Teacher{id=2, name='语文老师'}}
Student{id=6, name='小叮', teacher=Teacher{id=3, name='英语老师'}}

四、使用Mybatis实现一对多查询

1、搭建实体类及映射环境

在上面多对一的情况下，学生实体类中有一个对应得老师实体引用，一对多得情况，老师实体类也要有一个学生得引用，因为是一对多得关系，这里用List存放学生集合

public class Student {

    private int id;
    private String name;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

public class Teacher {

    private int id;
    private String name;
    List<Student> students;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<Student> getStudents() {
        return students;
    }

    public void setStudents(List<Student> students) {
        this.students = students;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", students=" + students +
                '}';
    }
}

创建持久层TeacherMapper接口

public interface TeacherMapper {

    List<Teacher> selectTeacherById(@Param("tid") int tid);
}

2、使用嵌套查询完成一对多查询

<?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.yaco.dao.TeacherMapper">

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

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

</mapper>

3、测试

public class MybatisTest {

    @Test
    public void test() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        List<Teacher> teachers = mapper.selectTeacherById(1);
        for (Teacher teacher : teachers) {
            System.out.println(teacher);
        }
    }
}

用例输出

Teacher{id=1, name='数学老师', students=[Student{id=1, name='小明'}, Student{id=4, name='小宋'}]}

Mybatis简单上手

一、搭建MySQL环境

二、实现简单的增删改查

1、创建数据库

2、创建`mybatis-config.xml`配置文件

3、创建对应的数据库中的实体类

4、创建mapper映射文件

5、创建一个MybatisUtil工具类

6、测试类

三、使用Mybatis实现多对一查询

1、搭建数据表结构

2、搭建实体类及映射环境

3、使用嵌套查询完成多对一查询

4、使用子查询完成多对一查询

5、测试

四、使用Mybatis实现一对多查询

1、搭建实体类及映射环境

2、使用嵌套查询完成一对多查询

3、测试

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

Mybatis简单上手

一、搭建MySQL环境

二、实现简单的增删改查

1、创建数据库

2、创建mybatis-config.xml配置文件

3、创建对应的数据库中的实体类

4、创建mapper映射文件

5、创建一个MybatisUtil工具类

6、测试类

三、使用Mybatis实现多对一查询

1、搭建数据表结构

2、搭建实体类及映射环境

3、使用嵌套查询完成多对一查询

4、使用子查询完成多对一查询

5、测试

四、使用Mybatis实现一对多查询

1、搭建实体类及映射环境

2、使用嵌套查询完成一对多查询

3、测试

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

2、创建`mybatis-config.xml`配置文件