MyBatis入门学习

1.MyBaits简介

​ MyBatis 是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO（Plain Old Java Objects，普通老式 Java 对象）为数据库中的记录。

来自官网 -https://mybatis.org/mybatis-3/zh/index.html

2.传统的JDBC操作数据库

导入依赖

    <!-- mybatis 依赖 -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.5.6</version>
    </dependency>
    
    <!-- mysql 的依赖 -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
    </dependency>

2.1 创建实体类

/**
 * @author xxs
 * @create 2021/5/18 11:07 下午
 */
@Data
@ToString
@AllArgsConstructor
@NoArgsConstructor
public class User {
    
    private int id;
    private String username;
    private String password;
}

2.2创建连接工具JdbcUtils

package com.example.util;

import com.sun.org.apache.regexp.internal.RE;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @author xxs
 * @create 2021/5/18 11:08 下午
 * jdbc 工具类
 */
public class JdbcUtils {

    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password;

    /**
     * 初始化 配置参数
     */
    static {
        try {
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties prop = new Properties();
            prop.load(in);

            driver = prop.getProperty("driver");
            url = prop.getProperty("url");
            username = prop.getProperty("username");
            password = prop.getProperty("password");

            Class.forName(driver);
        }catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    /**
     * 获取连接
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }

    /**
     * 释放资源
     * @param connection
     * @param st
     * @param rs
     */
    public static void release(Connection connection, Statement st, ResultSet rs) {
        if (rs!=null) {
            try {
                rs.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if (st!=null) {
            try {
                st.close();;
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (connection!=null) {
            try {
                connection.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

2.3编写配置文件db.properties

driver=com.mysql.jdbc.Driver
password=123
url=jdbc:mysql://localhost:3306/jdbc
username=root 

2.4编写测试类来测试CRUD

package com.example.test;

import com.example.util.JdbcUtils;
import com.sun.deploy.uitoolkit.impl.fx.AppletStageManager;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * @author xxs
 * @create 2021/5/18 11:20 下午
 * crud
 */
public class Demo {

    public static void main(String[] args) {
        //insert();
        //delete();
        //update();
        find();
    }

    /**
     * 查找数据
     */
    private static void find() {
        Connection connection = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            connection = JdbcUtils.getConnection();
            st = connection.createStatement();
            String sql = "select * from user where id = 1";
            rs = st.executeQuery(sql);
            if (rs.next()) {
                System.out.println(rs.getString("username"));
                System.out.println(rs.getString("password"));
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,st,rs);
        }
    }

    /**
     * 更新数据
     */
    private static void update() {
        Connection connection = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            connection = JdbcUtils.getConnection();
            st = connection.createStatement();
            String sql = "update user set username = '李四' where id = 1";
            int i = st.executeUpdate(sql);
            if (i>0) {
                System.out.println("更新成功!");
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,st,rs);
        }
    }

    /**
     * 删除数据
     */
    private static void delete() {
        Connection connection = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            connection = JdbcUtils.getConnection();
            st = connection.createStatement();
            String sql = "delete from user where id = 2";
            int i = st.executeUpdate(sql);
            if (i>0) {
                System.out.println("删除成功!");
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,st,rs);
        }
    }

    /**
     * 插入数据
     */
    private static void insert() {
        Connection connection = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            connection = JdbcUtils.getConnection();
            st = connection.createStatement();
            String sql = "insert into user values (2,'李四','123456')";
            int num = st.executeUpdate(sql);
            if (num>0) {
                System.out.println("插入成功");
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,st,rs);
        }
    }
}

2.5存在的问题

• 数据库创建连接，释放资源造成系统资源浪费影响系统性能，可以使用数据库连接池来解决
• Sql语句存在硬编码，不利于后期的维护
• 对结果集的解析也存在硬编码，sql变化导致代码变化，系统不容易维护

2.6使用连接池

​ 连接池:连接池就是将应用所需的连接对象放在池中，每次访问时从池中获取，使用完毕再放回池中，以达到连接复用的目的。连接池和线程池很像，都是为了减少连接对象在创建、销毁连接过程中不必要消耗的资源

线程池的常用种类

• c3p0
• druid
• HikariCP

c3p0

导入依赖

<dependency>
      <groupId>com.mchange</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.5.5</version>
 </dependency>

package com.example.connpool;


import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author xxs
 * @create 2021/5/19 8:41 上午
 * c3p0连接池
 */
public class JdbcUtils_C3P0 {
    private static ComboPooledDataSource ds = null;
    static {
        try {
            ds = new ComboPooledDataSource();
            ds.setDriverClass("com.mysql.jdbc.Driver");
            ds.setJdbcUrl("jdbc://localhost:3306/jdbc");
            ds.setUser("root");
            ds.setPassword("123");

            ds.setInitialPoolSize(10);
            ds.setMinPoolSize(5);
            ds.setMaxPoolSize(20);

        }catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    /**
     * 获取连接
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    /**
     * 关闭资源
     * @param connection
     * @param st
     * @param rs
     */
    public static void release(Connection connection, Statement st, ResultSet rs) {
        if (rs!=null) {
            try {
                rs.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        rs = null;
        if (st!=null) {
            try {
                st.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (connection!=null) {
            try {
                connection.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

}

也可以使用xml文件来配置连接的相关信息

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>

    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc</property>
        <property name="user">root</property>
        <property name="password">123</property>

        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>

    <named-config name="mysql">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc</property>
        <property name="user">root</property>
        <property name="password">123</property>

        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </named-config>
</c3p0-config>

druid

导入依赖

 <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.2.5</version>
 </dependency>

package com.example.connpool;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.example.util.JdbcUtils;
import com.sun.org.apache.regexp.internal.RE;
import sun.tools.asm.CatchData;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.ResourceBundle;

/**
 * @author xxs
 * @create 2021/5/19 9:31 上午
 * druid 连接池
 */
public class JdbcUtils_Druid {

    private static DataSource ds = null;

    /**
     * 初始化配置
     */
    static {
        try {
            Properties properties = new Properties();
            InputStream is = JdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties");
            properties.load(is);
            ds = DruidDataSourceFactory.createDataSource(properties);
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    /**
     * 关闭资源
     * @param connection
     * @param st
     * @param rs
     */
    public static void release(Connection connection, Statement st, ResultSet rs) {
        if (rs!=null) {
            try {
                rs.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        rs = null;
        if (st!=null) {
            try {
                st.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (connection!=null) {
            try {
                connection.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

}

driver=com.mysql.jdbc.Driver
##初始连接数，默认0
initialSize=10
#最大连接数，默认8
maxActive=30
#缓存PreparedStatement的最大数量，默认-1（不缓存）。大于0时会自动开启缓存PreparedStatement，所以可以省略上一句设置
maxOpenPreparedStatements=20
#获取连接的最大等待时间，单位毫秒
maxWait=2000
#最小闲置数
minIdle=10
password=123
#缓存PreparedStatement，默认false
poolPreparedStatements=true
url=jdbc:mysql://localhost:3306/jdbc
username=root

HikariCP

导入依赖

  <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>2.6.1</version>
  </dependency>

package com.example.demo.connpool;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.HikariJNDIFactory;
import com.zaxxer.hikari.pool.HikariPool;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @author xxs
 * @create 2021/5/19 1:56 下午
 * HikariCP 连接池
 */
public class JdbcUtils_HikariCP {

    private static HikariDataSource dataSource;

    /**
     * 初始化配置
     */
    static {
        Properties properties = new Properties();
        try {
            InputStream is = JdbcUtils_HikariCP.class.getClassLoader().getResourceAsStream("hikari.properties");
            properties.load(is);
        }catch (Exception e) {
            e.printStackTrace();
        }
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(properties.getProperty("url"));
        config.setUsername(properties.getProperty("username"));
        config.setPassword(properties.getProperty("password"));
        config.setDriverClassName(properties.getProperty("driver"));

        dataSource = new HikariDataSource(config);
    }

    /**
     * 获取连接
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    /**
     * 关闭资源
     * @param connection
     * @param st
     * @param rs
     */
    public static void release(Connection connection, Statement st, ResultSet rs) {
        if (rs!=null) {
            try {
                rs.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        rs = null;
        if (st!=null) {
            try {
                st.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (connection!=null) {
            try {
                connection.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

driver=com.mysql.jdbc.Driver
password=123
url=jdbc:mysql://localhost:3306/jdbc
username=root

3.MyBatis入门

3.1创建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/jdbc"/>
                <property name="username" value="root"/>
                <property name="password" value="123"/>
            </dataSource>
        </environment>
    </environments>
    
    <!-- dao 和 xml 一一对应 -->
    <mappers>
        <mapper resource="mapper/UserDao.xml"/>
    </mappers>

</configuration>

这个配置文件是用来创建SqlSessionFactory对象

3.2获取SqlSession

package com.example.demo.test;

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;

/**
 * @author xxs
 * @create 2021/5/19 2:22 下午
 */
public class TestMyBatis {
    public static void main(String[] args) throws IOException {

     // 读取mybatis-config 配置文件
     InputStream is =  Resources.getResourceAsStream("mybatis-config.xml");
     // 创建mybatis核心对象 sqlSessionFactory
     SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
     // 获取SqlSession 对象
     SqlSession sqlSession = sqlSessionFactory.openSession();
     System.out.println(sqlSession);
    }
}

3.3编写dao接口

package com.example.demo.dao;

import com.example.demo.pojo.User;

/**
 * @author xxs
 * @create 2021/5/19 2:31 下午
 * user的持久层
 */
public interface UserDao {

    /**
     * 保存用户
     * @param user
     * @return
     */
    int save(User user);
}

3.4编写对应的配置文件

<?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.example.demo.dao.UserDao">

    <insert id="save" parameterType="com.example.demo.pojo.User">
        insert into user values (#{id},#{username},#{password})
    </insert>

</mapper>

3.5测试类

package com.example.demo.test;

import com.example.demo.dao.UserDao;
import com.example.demo.pojo.User;
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;

/**
 * @author xxs
 * @create 2021/5/19 2:22 下午
 */
public class TestMyBatis {
    public static void main(String[] args) throws IOException {

     // 读取mybatis-config 配置文件
     InputStream is =  Resources.getResourceAsStream("mybatis-config.xml");
     // 创建mybatis核心对象 sqlSessionFactory
     SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
     // 获取SqlSession 对象
     SqlSession sqlSession = sqlSessionFactory.openSession();

      try {
          UserDao mapper = sqlSession.getMapper(UserDao.class);
          int i = mapper.save(new User(3, "王五", "12345678"));
          System.out.println(i);
          System.out.println(sqlSession);
          sqlSession.commit();
      }catch (Exception e) {
          e.printStackTrace();
          sqlSession.rollback();
      }finally {
          sqlSession.close();
      }
    }
}

其它类似的增删改查都是相同的

3.6关系映射

数据库的关联关系

• 一对一
• 一对多
• 多对多

一对一的关联关系

我们有两张表 一张是用户表，一张是用户的信息表

person表里面有 id、name、age、cardno

info表里面有 id、cardno、address

create table person(
 id int(6) PRIMARY KEY  auto_increment,
 name VARCHAR(40),
 age int(3),
 cardno VARCHAR(18) REFERENCES info(cradno)
);

create table info(
   id int(6) PRIMARY KEY auto_increment,
	 cradno VARCHAR(18),
	 address VARCHAR(100)
)

编写实体类

package com.example.demo.pojo;

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

/**
 * @author xxs
 * @create 2021/5/19 8:06 下午
 * 用户的实体类
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Person {

    private int id;
    private String name;
    private String age;
    private String cardNo;
    private Info info;

}

package com.example.demo.pojo;

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

/**
 * @author xxs
 * @create 2021/5/19 8:07 下午
 * 用户的信息类
 */
@ToString
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Info {
    private int id;
    private String cardNo;
    private String address;
}

package com.example.demo.dao;

import com.example.demo.pojo.Person;

import java.util.List;

/**
 * @author xxs
 * @create 2021/5/19 9:15 下午
 */
public interface PersonDao {

    /**
     * 保存用户
     * @param person
     * @return
     */
    int save(Person person);

    /**
     * 查询用户的信息
     * @return
     */
    List<Person> findAll();
}

package com.example.demo.dao;

import com.example.demo.pojo.Info;

/**
 * @author xxs
 * @create 2021/5/19 8:12 下午
 */
public interface InfoDao {

    /**
     * 保存用户信息
     */
    int save(Info info);
}

<?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.example.demo.dao.PersonDao">

    <resultMap id="findAll" type="com.example.demo.pojo.Person">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="cardNo" column="cardno"/>

        <!-- 处理一对一关系的标签 -->
        <association property="info" javaType="com.example.demo.pojo.Info">
            <id column="iid" property="id"/>
            <result column="icardno" property="cardNo"/>
            <result column="address" property="address"/>
        </association>

    </resultMap>

    <insert id="save" parameterType="com.example.demo.pojo.Person" useGeneratedKeys="true" keyProperty="id">
         insert into person values (#{id},#{name},#{age},#{cardNo})
    </insert>

    <select id="findAll" resultMap="findAll">
       SELECT
		p.id,p.name,p.age,p.cardno,i.id as iid,i.cardno as icardno,i.address
		FROM person as p
		left JOIN info as i
		on p.cardno = i.cardno
    </select>

</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.example.demo.dao.InfoDao">
    <resultMap id="saveResult" type="com.example.demo.pojo.Info">
        <id property="id" column="id"/>
        <result property="cardNo" column="cardno"/>
        <result property="address" column="address"/>
    </resultMap>

    <insert id="save" parameterType="com.example.demo.pojo.Info">
         insert into info values (#{id},#{cardNo},#{address})
    </insert>
</mapper>

package com.example.demo.test;

import com.example.demo.dao.InfoDao;
import com.example.demo.dao.PersonDao;
import com.example.demo.pojo.Info;
import com.example.demo.pojo.Person;
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;
import java.util.List;

/**
 * @author xxs
 * @create 2021/5/19 9:19 下午
 */
public class TestMyBatis6 {
    public static void main(String[] args) throws IOException {

        // 读取mybatis-config 配置文件
        InputStream is =  Resources.getResourceAsStream("mybatis-config.xml");
        // 创建mybatis核心对象 sqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        // 获取SqlSession 对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            PersonDao personDao = sqlSession.getMapper(PersonDao.class);
            List<Person> all = personDao.findAll();
            all.forEach(person -> System.out.println(person));

            sqlSession.commit();
        }catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        }finally {
            sqlSession.close();
        }
    }
}

一对多的映射关系

我们现在有两张表 一张是部门表 一张是员工表

部门表 dept 有 id name

员工表有 id username age bir deptid

package com.example.demo.pojo;

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

import java.util.List;

/**
 * @author xxs
 * @create 2021/5/19 10:08 下午
 * 部门实体类
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Dept {

    private int id;
    private String name;
    private List<Emp> emp;
}

package com.example.demo.pojo;

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

import java.util.Date;

/**
 * @author xxs
 * @create 2021/5/19 10:08 下午
 * 员工表
 */
@AllArgsConstructor
@NoArgsConstructor
@Data
@ToString
public class Emp {

    private int id;
    private String username;
    private int age;
    private Date bir;
    private int deptId;
}

package com.example.demo.dao;

import com.example.demo.pojo.Dept;

import java.util.List;

/**
 * @author xxs
 * @create 2021/5/19 10:10 下午
 */
public interface DeptDao {

    /**
     * 查询部门 并且所有的员工
     * @return
     */
    List<Dept> findAll();
}

<?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.example.demo.dao.DeptDao">
   <resultMap id="deptAll" type="com.example.demo.pojo.Dept">
       <id property="id" column="id"/>
       <result property="name" column="name"/>
       <!-- collection 是用来映射一对多的 -->
       <collection property="emp" javaType="java.util.List" ofType="com.example.demo.pojo.Emp">
           <id column="eid" property="id"/>
           <result column="username" property="username"/>
           <result column="age" property="age"/>
           <result column="bir" property="bir"/>
           <result column="deptid" property="deptId"/>
       </collection>
   </resultMap>
   <select id="findAll" resultMap="deptAll">
       SELECT
               d.id,d.name,e.id as eid,e.username,e.age,e.bir,e.deptid
               from dept as d
               left JOIN emp as e
               on d.id = e.deptid
   </select>
</mapper>

package com.example.demo.test;

import com.example.demo.dao.DeptDao;
import com.example.demo.dao.PersonDao;
import com.example.demo.pojo.Dept;
import com.example.demo.pojo.Person;
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;
import java.util.List;

/**
 * @author xxs
 * @create 2021/5/19 10:13 下午
 */
public class TestMyBatis7 {
    public static void main(String[] args) throws IOException {

        // 读取mybatis-config 配置文件
        InputStream is =  Resources.getResourceAsStream("mybatis-config.xml");
        // 创建mybatis核心对象 sqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        // 获取SqlSession 对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            DeptDao deptDao = sqlSession.getMapper(DeptDao.class);
            List<Dept> depts = deptDao.findAll();
            depts.forEach(dept -> System.out.println(dept));
            sqlSession.commit();
        }catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        }finally {
            sqlSession.close();
        }
    }
}

多对多映射的关系

两张表 一张是学生表 一张是老师表

学生表 id name

老师表 id name

中间表 id cid sid

package com.example.demo.pojo;

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

/**
 * @author xxs
 * @create 2021/5/19 10:45 下午
 */
@AllArgsConstructor
@NoArgsConstructor
@Data
@ToString
public class Course {

    private int id;
    private String name;
}

package com.example.demo.pojo;

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

import java.util.List;

/**
 * @author xxs
 * @create 2021/5/19 10:44 下午
 */
@AllArgsConstructor
@NoArgsConstructor
@Data
@ToString
public class Student {
    private int id;
    private String name;
    private List<Course> course;
}

package com.example.demo.dao;

import com.example.demo.pojo.Student;

/**
 * @author xxs
 * @create 2021/5/19 10:45 下午
 */
public interface StudentDao {

    /**
     * 根据id 查询学生
     * @param id
     * @return
     */
    Student findStudentById(int id);
}

<?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.example.demo.dao.StudentDao">
    <resultMap id="studentMap" type="com.example.demo.pojo.Student">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <collection property="course" javaType="java.util.List" ofType="com.example.demo.pojo.Course">
            <id column="cid" property="id"/>
            <result column="cname" property="name"/>
        </collection>
    </resultMap>
    <select id="findStudentById" parameterType="java.lang.Integer" resultMap="studentMap">
        SELECT s.id,s.name,c.id as cid,c.name as cname
                 FROM student as s
                 left join student_course as sc
                           on s.id = sc.sid
                 left join course as c
                           on sc.cid = c.id
                 where s.id = #{id}
    </select>
</mapper>

3.7动态sql

• if
• choose when otherwise
• trim where set
• foreach
• script
• bind