1.ORM框架mybatis的基本介绍（包括配置文件）：
ORM可以解决数据库与程序间的异构性，比如在Java中我们使用String表示字符串，而Oracle中可使用varchar2，MySQL中可使用varchar，SQLServer可使用nvarchar。
ORM是通过使用描述对象和数据库之间映射的元数据，将程序中的对象与关系数据库相互映射。
没有ORM时我们是这样完成对象与关系数据库之间的映射的：

  1.   //将执行的sql
  2.             String sql = "SELECT name, id, age, password FROM users";
  3.             //创建命令对象
  4.             preparedStatement = connection.prepareStatement(sql);
  5.             //执行并获得结果集
  6.             resultSet = preparedStatement.executeQuery();
  7.             //遍历结果集，将数据库中的数据转换成Java中的对象
  8.             while(resultSet.next()){
  9.                 String name = resultSet.getString("name");
  10.                 int id = resultSet.getInt("id");
  11.                 int age = resultSet.getInt("age");
  12.                 String password = resultSet.getString("password");
  13.                 User entity= new User(name,id,age,password);
  14.                 Users.add(entity);
  15.             }

这种写法的不足：

  • 原生的jdbc会频繁的创建链接和释放资源，会增加数据库的链接压力
    解决方法：使用连接池，用连接池来管理数据库的链接和释放资源。
  • 原生的jdbc的sql语句是写在java代码中的，这种方式属于硬编码，不利于sql代码的维护。
    解决方法：mybatis框架将sql语句提取到xml配置文件中，每一次修改sql语句，只需要修改配置文件中的语句即可，和代码无关
  • 原生的jdbc预编译对象中的?也属于硬编码
    解决方法：mybatis框架将视线关系对象映射，mybatis存在输入对象映射和输出对象映射。

原生的jdbc：

  1. public class Demo{
  2.     private static String driver = "com.mysql.jdbc.Driver";
  3.     private static String url = "jdbc:mysql://localhost:3306/db_shop";
  4.     private static String username = "root";
  5.     private static String password = "";
  7.     static{
  8.         //加载驱动
  9.         Class.forName(driver);
  10.     }
  12.     public static void main(String[] args){
  13.         try{
  14.             Connection conn = ManagerDriver.getConnection(url,username,password);
  15.             String sql = "select * from student where id = ?";
  16.             PreparedStatement ps = conn.prepareStatement(sql);
  17.             ps.setInt(1,user.getInt());
  18.             ResultSet rs = ps.executeQuery();
  19.             while(rs.next()){
  21.             }
  22.             rs.close();
  23.             ps.close();
  24.             conn.close();
  25.         }catch(Exception e){
  27.         }
  28.     }
  29. }

mybatis概念：

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

案例代码：查询

这是mybatis基本的框架
image.png

编写sqlMapConfig.xml

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE configuration
  3.  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  4.  "http://mybatis.org/dtd/mybatis-3-config.dtd">
  5. <configuration>
  6.      <!-- 使用mybatis需要的数据源和事务配置，后续如果整合spring之后，将不再需要 -->
  7.      <environments default="development">
  8.          <!-- 配置数据源和事务 -->
  9.          <environment id="development">
  10.              <!-- 配置事务管理,将事务管理交给mybatis管理 -->
  11.              <transactionManager type="JDBC" />
  12.              <!-- 配置数据源 -->
  13.              <dataSource type="POOLED">
  14.                  <property name = "driver" value = "com.mysql.jdbc.Driver" />
  15.                  <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&amp;characterEncoding=utf-8"/>
  16.                  <property name="username" value="root"/>
  17.                  <property name="password" value=""/>
  18.              </dataSource>
  19.          </environment>
  20.      </environments>
  21.      <!-- 加载**.xml配置文件 -->
  22.      <mappers>
  23.          <mapper resource="Food.xml"/>
  24.      </mappers>
  25.  </configuration>

编写log4j.properties标签

  1. # Global logging configuration 
  2. log4j.rootLogger=DEBUG, stdout
  3. # MyBatis logging configuration...
  4. log4j.logger.org.mybatis.example.BlogMapper=TRACE
  5. # Console output... 
  6. log4j.appender.stdout=org.apache.log4j.ConsoleAppender 
  7. log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
  8. log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

编写失血模型对象：food模型

  1. package model;
  3. /**
  4.  * @ClassName: food.java
  5.  * @Description: 该类的功能描述
  6.  * @version: v1.0.0
  7.  * @author: 169986432
  8.  * @date: 2020年10月11日 下午2:38:05 
  9.  */
  10. public class Food {
  11.     private  int  id;
  12.     private  String  last_name;
  13.     private  String  gender;
  14.     private  String  email;
  15.     public Food() {
  16.         super();
  17.     }
  18.     public Food(int id, String last_name, String gender, String email) {
  19.         super();
  20.         this.id = id;
  21.         this.last_name = last_name;
  22.         this.gender = gender;
  23.         this.email = email;
  24.     }
  25.     /**
  26.      * @return the id
  27.      */
  28.     public int getId() {
  29.         return id;
  30.     }
  31.     /**
  32.      * @param id the id to set
  33.      */
  34.     public void setId(int id) {
  35.         this.id = id;
  36.     }
  37.     /**
  38.      * @return the last_name
  39.      */
  40.     public String getLast_name() {
  41.         return last_name;
  42.     }
  43.     /**
  44.      * @param last_name the last_name to set
  45.      */
  46.     public void setLast_name(String last_name) {
  47.         this.last_name = last_name;
  48.     }
  49.     /**
  50.      * @return the gender
  51.      */
  52.     public String getGender() {
  53.         return gender;
  54.     }
  55.     /**
  56.      * @param gender the gender to set
  57.      */
  58.     public void setGender(String gender) {
  59.         this.gender = gender;
  60.     }
  61.     /**
  62.      * @return the email
  63.      */
  64.     public String getEmail() {
  65.         return email;
  66.     }
  67.     /**
  68.      * @param email the email to set
  69.      */
  70.     public void setEmail(String email) {
  71.         this.email = email;
  72.     }
  73.     @Override
  74.     public String toString() {
  75.         return "Food [id=" + id + ", last_name=" + last_name + ", gender=" + gender + ", email=" + email + "]";
  76.     }
  78. }

编写单个映射关系的sql.xml:Food.xml

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <!DOCTYPE mapper
  3.  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5.   <mapper namespace="test">
  6.       <select id="foodAll
  7.       " parameterType="java.lang.Integer" resultType="model.Food">
  8.               SELECT * FROM food WHERE last_anme= #{value}
  9.      </select>
  10.  </mapper>

编写dao接口：查询的接口

  1. public interface FoodDao {
  2.     public Food queryProductById(String last_name) throws Exception;
  3. }

编写dao接口的实现类：FoodImpl实现类

  1. public class FoodDaoImpl   implements  FoodDao {
  2.     //声明一个会话工厂
  3.         private SqlSessionFactory factory;
  5.         //由构造方法进行创建对象赋值
  6.         public  FoodDaoImpl(SqlSessionFactory factory) {
  7.             this.factory = factory;
  8.         }
  10.     @Override
  11.     public Food queryProductById(String last_name) throws Exception {
  12.         //开启会话
  13.         SqlSession sqlSession = factory.openSession();
  14.         Food prod = sqlSession.selectOne("test.foodAll", last_name);
  16.         //关闭会话
  17.         sqlSession.close();
  18.         return prod;
  19.     }
  21. }

测试类：测试

  1. public class Test {
  2.         //创建dao的实现类
  4.             public static void main(String[] args) throws Exception{
  5.                 String path = "SqlMapConfig.xml";
  6.                 InputStream config = Resources.getResourceAsStream(path);
  7.                 //创建一个会话工厂
  8.                 SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(config);
  10.                 testQuery1(factory);
  11.             }
  13.             public static void testQuery1(SqlSessionFactory factory) throws Exception {
  14.                 FoodDao dao = new FoodDaoImpl(factory);
  15.                 int id = 1;
  16.                 Food  food=dao.queryProductById("张三");
  17.                 System.out.println(food);
  18.             }
  20.     }

mybatis的最简单的增，删，改，查

思路：
1.导入jr包，创建xml里面配置增，删，改。查的sql语句
2.创建xml文件在SqlMapConfig.xml文件中进行配置加载
3.创建一个失血模型要跟数据库的字段一致
4.创建一个接口，接口当中包含5个方法，两个查询、增、删、改的方法。只有定义。
5.创建实现类，实现定义的接口，按照规定进行重写方法。声明工厂类对象，该对象由该类的构造方法进行赋值。
6.编写测试类，在测试类中，创建工厂对象，传递给dao的实现类。
代码：
image.png
编写xml：food.xml

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <!DOCTYPE mapper
  3.  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="first">
  6.     <!-- 根据id查询 -->
  7.     <select id="queryId" parameterType="java.lang.Integer" resultType="model.Food">
  8.         SELECT * FROM t_product WHERE p_id = #{p_id}
  9.     </select>
  10.     <!-- 根据名称模糊查询，返回多条结果集 -->
  11.     <select id="queryName" parameterType="java.lang.String" resultType="model.Food">
  12.         SELECT * FROM t_product WHERE name like "%${value}%"
  13.     </select>
  14.     <!-- 新增一条数据 -->
  15.     <insert id="insert" parameterType="model.Food">
  16.         INSERT INTO t_product(name,p_number,price) value(#{name},#{p_number},#{price})
  17.     </insert>
  18.     <!-- 根据id值更新 -->
  19.     <update id="update" parameterType="model.Food">
  20.         UPDATE t_product SET name = #{name} WHERE p_id = #{p_id}
  21.     </update>
  22.     <!-- 根据id删除 -->
  23.     <delete id="delete" parameterType="java.lang.Integer">
  24.         DELETE FROM t_product WHERE p_id = #{p_id}
  25.     </delete>
  26. </mapper>

编写xml：加载food。xml
编写接口增，删，改，查

  1. public interface FoodDao {
  3.     public  Food queryId(int id) throws Exception;
  5.     public List<Food> queryName(String name) throws Exception;
  7.     public Food insert(Food product) throws Exception;
  9.     public int update(Food product) throws Exception;
  11.     public int delete(int id) throws Exception;
  12. }

编写接口的实现类

  1. public class FoodDaoImpl   implements  FoodDao {
  2.     //声明一个会话工厂
  3.         private SqlSessionFactory factory;
  5.         //由构造方法进行创建对象赋值
  6.         public  FoodDaoImpl(SqlSessionFactory factory) {
  7.             this.factory = factory;
  8.         }
  10.         @Override
  11.         public Food queryId(int id) throws Exception {
  12.             SqlSession sqlSession = factory.openSession();
  13.             Food  food= sqlSession.selectOne("first.queryId", id);
  14.             sqlSession.close();
  15.             return food;
  16.         }
  18.         @Override
  19.         public List<Food> queryName(String name) throws Exception {
  20.             SqlSession sqlSession = factory.openSession();
  21.             List<Food> list = sqlSession.selectList("first.queryName", name);
  22.             sqlSession.close();
  23.             return list;
  24.         }
  26.         @Override
  27.         public Food insert(Food food) throws Exception {
  28.             SqlSession sqlSession = factory.openSession();
  29.             int row = sqlSession.insert("first.insert", food);
  30.             sqlSession.commit();
  31.             sqlSession.close();
  32.             return  food;
  33.         }
  35.         @Override
  36.         public int update(Food food) throws Exception {
  37.             SqlSession sqlSession = factory.openSession();
  38.             int row = sqlSession.update("first.update", food);
  39.             sqlSession.commit();
  40.             sqlSession.close();
  41.             return row;
  42.         }
  44.         @Override
  45.         public int delete(int id) throws Exception {
  46.             SqlSession sqlSession = factory.openSession();
  47.             int row = sqlSession.delete("first.delete", id);
  48.             sqlSession.commit();
  49.             sqlSession.close();
  50.             return row;
  51.         }
  52. }

测试类：测试

  1. public class Test {
  2.         //创建dao的实现类
  4.             public static void main(String[] args) throws Exception{
  5.                 String path = "SqlMapConfig.xml";
  6.                 InputStream config = Resources.getResourceAsStream(path);
  7.                 //创建一个会话工厂
  8.                 SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(config);
  10. //                testQuery1(factory);
  11. //                testQuery2(factory);
  12.                 testQuery3(factory);
  13.             }
  15.             public static void testQuery1(SqlSessionFactory factory) throws Exception {
  16.                 FoodDao dao = new FoodDaoImpl(factory);
  17.                 int id = 1;
  18.                 Food  food = dao.queryId(id);
  19.                 System.out.println(food);
  20.             }
  22.             public static void testQuery2(SqlSessionFactory factory) throws Exception {
  23.                 FoodDao dao = new FoodDaoImpl(factory);
  24.                 String name = "牛奶";
  25.                 List<Food> list = dao.queryName(name);
  26.                 System.out.println(list);
  27.             }
  28.            public  static  void testQuery3(SqlSessionFactory factory) throws Exception {
  29.                SqlSession session = factory.openSession();
  30.                Food  food=session.getMapper(Food.class);
  31.                Food  food1=new  Food();
  32.                food1.setP_id(7);
  33.                food1.setName("阿萨姆奶茶");
  34.                food1.setP_number(788);
  35.                food1.setPrice(90);
  36.                food1.setAdd_time("2020-09-23 17:59:46.0");
  37.            } 
  38.     }

效果：
image.png
image.png

mapper代理的方式进行讲解增、删、改、查(重点)

配置xml文件

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE configuration
  3.   PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  4.   "http://mybatis.org/dtd/mybatis-3-config.dtd">
  5. <configuration>
  6.   <environments default="development">
  7.     <environment id="development">
  8.       <transactionManager type="JDBC"/>
  9.       <!-- 修改数据库的四个连接属性  -->
  10.       <dataSource type="POOLED">
  11.         <property name="driver" value="com.mysql.jdbc.Driver"/>
  12.         <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
  13.         <property name="username" value="root"/>
  14.         <property name="password" value=""/>
  15.       </dataSource>
  16.     </environment>
  17.   </environments>
  18.   <!-- 配置sql语句 的那个mapper配置文件 -->
  19.   <mappers>
  20.     <mapper resource="com/dao/UserMapper.xml"/>
  21.   </mappers>
  22. </configuration>

Mapper代码：

  1. public interface UserMapper {    
  2.         // 保存
  3.         public int save(Food Food);    
  4.         // 更新
  5.         public intupdate(Food Food);    
  6.         // 根据id删除
  7.         public int deleteId(int id);    
  8.         // 根据id搜索
  9.         public User findId(int id);    
  10.         // 搜索全部
  11.         public List<User> findFood();
  12. }

配置FoodMapper.xml代码：

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.   PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.   "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.dao.UserMapper">
  7. <!--     public Integer save(Food FoodUser user); -->
  8.     <insert id="saveUser" parameterType="com.pojo.Food">
  9.         insert into t_user(`last_name`,`sex`) values(#{lastName},#{sex})
  10.     </insert>
  11. <!--     public Integer updateUser(Food Food); -->
  12.     <update id="updateUser" parameterType="com.pojo.Food">
  13.         update t_user set last_name = #{lastName} , sex = #{sex} where id = #{id}
  14.     </update>
  15. <!--     public Integer deleteUserById(Integer id); -->
  16.     <delete id="deleteUserById">
  17.         delete from t_user where id = #{id}
  18.     </delete>    
  19. <!--     public User findUserById(Integer id); -->
  20.     <select id="findUserById" resultType="com.pojo.Food">
  21.         select id,last_name lastName,sex from t_user where id = #{id}
  22.     </select>
  23. <!--     public List<User> findUsers(); -->
  24.     <select id="findUsers" resultType="com.pojo.Food">
  25.         select id,last_name lastName,sex from t_user
  26.     </select>
  27. </mapper>

测试类：

  1. public class FoodMapperTest {
  3.     static SqlSessionFactory sqlSessionFactory;
  5.     @BeforeClass
  6.     public static void setUpBeforeClass() throws Exception {
  7.         String url = "mybatis-config.xml";
  8.         // 读取配置文件
  9.         InputStream inputStream = Resources.getResourceAsStream(url);
  10.         // 创建SqlSessionFactory对象
  11.         sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
  12.     }
  14.     @Test
  15.     public void testSave() {
  16.         SqlSession session = sqlSessionFactory.openSession();
  17.         try {
  18.             UserMapper userMapper = session.getMapper(UserMapper.class);
  19.             User user = new User(0, "ddddd", 1);
  20.             userMapper.saveUser(user);
  21.             session.commit();
  22.             System.out.println(user);
  23.         } finally {
  24.             session.close();
  25.         }
  26.     }
  28.     @Test
  29.     public void testUpdateUser() {
  30.         SqlSession session = sqlSessionFactory.openSession();
  31.         try {
  32.             UserMapper userMapper = session.getMapper(UserMapper.class);
  33.             User user = new User(4, "eeeee", 1);
  34.             userMapper.updateUser(user);
  35.             session.commit();
  36.         } finally {
  37.             session.close();
  38.         }
  39.     }
  41.     @Test
  42.     public void testDeleteById() {
  43.         SqlSession session = sqlSessionFactory.openSession();
  44.         try {
  45.             UserMapper userMapper = session.getMapper(UserMapper.class);
  46.             userMapper.deleteUserById(4);
  47.             session.commit();
  48.         } finally {
  49.             session.close();
  50.         }
  51.     }
  53.     @Test
  54.     public void testFindById() {
  55.         SqlSession session = sqlSessionFactory.openSession();
  56.         try {
  57.             UserMapper userMapper = session.getMapper(UserMapper.class);
  58.             System.out.println(userMapper.findUserById(1));
  59.         } finally {
  60.             session.close();
  61.         }
  62.     }
  64.     @Test
  65.     public void testFind() {
  66.         SqlSession session = sqlSessionFactory.openSession();
  67.         try {
  68.             UserMapper userMapper = session.getMapper(UserMapper.class);
  69.             System.out.println(userMapper.findUsers());
  70.         } finally {
  71.             session.close();
  72.         }
  73.     }

效果：
image.png

别名和mapper映射详解

设置别名：

1.设置别名位置是在SqlMapConfig.xml配置文件中

  1. <!-- 设置一个别名 -->
  2. <typeAliases>
  3.     <!-- 
  4.         type: 要设置的model全路径名
  5.         alias:取的别名
  6.     -->
  7.     <typeAlias type="com.woniuxy.mall.model.User" alias="user"/>
  8. </typeAliases>

2.推荐使用批量设置别名，扫描model包

  1. <typeAliases>
  2.     <!-- 
  3.         name:包名
  4.     -->
  5.     <package name="com.woniuxy.mall.model"/>
  6. </typeAliases>

Mapper映射关系配置

以mapper.xml配置文件的方式
image.png
以配置Mapper接口的方式进行配置

  1. <!-- 加载mapper.xml文件 -->
  2.  <mappers>
  3.      <!-- 
  4.         class:对应接口的全路径名
  5.         要求：
  6.             对应的mapper.java接口文件和mapper.xml配置文件必须放在同一个包下面。
  7.             mapper.java接口文件和mapper.xml配置文件的名字必须要一致。
  8.     -->
  9.      <mapper class="mapper.FoodMapper"/>
  10.  </mappers>

以扫描包的形式进行配置

  1. <!-- 加载mapper.xml文件 -->
  2.  <mappers>
  3.      <!-- 
  4.             name:mapper接口的包名
  5.             规则同上。
  6.     -->
  7.      <package name="mapper"/>
  8.  </mappers>

自定义的POJO类

自定义POJO类，一般指的是高级查询，在想要的数据和传入的条件不再一张表中，通过表所创建的失血模型model已经不能够满足传入参数的需求了，这个时候，就需要使用自定义的POJO类
因为一个失血模型已经不能够满足查询输入参数的要求了，所以进行扩展，创建一个UserCustom类,里面包含user对象声明和userInfo对象声明
Customer 实体类：

  1. public class Customer {
  2.     private List<Orders> orders;    // 关联多个订单
  3.     private Integer id;
  4.     private String name;
  6.     public Integer getId() {
  7.         return id;
  8.     }
  10.     public void setId(Integer id) {
  11.         this.id = id;
  12.     }
  14.     public String getName() {
  15.         return name;
  16.     }
  18.     public void setName(String name) {
  19.         this.name = name;
  20.     }
  22.     public List<Orders> getOrders() {
  23.         return orders;
  24.     }
  26.     public void setOrders(List<Orders> orders) {
  27.         this.orders = orders;
  28.     }
  29. }

Orders 实体类：

  1. public class Orders {
  2.     private Integer id;
  3.     private String sn;       // 订单编号
  4.     private String remark;   // 订单描述
  6.     public Integer getId() {
  7.         return id;
  8.     }
  10.     public void setId(Integer id) {
  11.         this.id = id;
  12.     }
  14.     public String getSn() {
  15.         return sn;
  16.     }
  18.     public void setSn(String sn) {
  19.         this.sn = sn;
  20.     }
  22.     public String getRemark() {
  23.         return remark;
  24.     }
  26.     public void setRemark(String remark) {
  27.         this.remark = remark;
  28.     }

创建 CustomerMapper.xml 映射文件

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  6. <mapper namespace="yeepay.payplus.mapper.CustomerMapper">
  7.     <resultMap type="yeepay.payplus.domain.Customer" id="customerRM">
  8.         <id property="id" column="ID"/>
  9.         <result property="name" column="NAME"/>
  10.     </resultMap>
  12.     <!-- 配置关联关系 1:N -->
  13.     <resultMap type="yeepay.payplus.domain.Customer" id="customerOrdersRM" extends="customerRM">
  14.         <!-- 配置多的（N），property 属性就是实体中的 List 对象属性名称，ofType 属性就是集合元素的类型 -->
  15.         <collection property="orders" ofType="yeepay.payplus.domain.Orders">
  16.             <id property="id" column="ID"/>
  17.             <result property="sn" column="SN"/>
  18.             <result property="remark" column="REMARK"/>
  19.         </collection>
  20.     </resultMap>
  22.     <!-- 查询，关联关系 Map 作为查询条件 -->
  23.     <select id="find" parameterType="map" resultMap="customerOrdersRM">
  24.         SELECT
  25.             c.name,o.sn,o.remark
  26.         FROM
  27.             (SELECT id,name FROM customer) c
  28.         LEFT JOIN
  29.             (SELECT id,sn,remark,customer_id FROM orders) o
  30.         ON c.id = o.customer_id
  31.         WHERE c.name = #{customerName}
  32.     </select>
  33. </mapper>

sqlMapConfig.xml 配置文件

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE configuration
  3.         PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-config.dtd">
  6. <configuration>
  8.     <!-- 赋别名 -->
  9.     <typeAliases>
  10.         <typeAlias type="yeepay.payplus.domain.Person" alias="Person"/>
  11.     </typeAliases>
  13.     <!-- 配置开发环境，可以配置多个，在具体用时再做切换 -->
  14.     <environments default="">
  15.         <environment id="test">
  16.             <transactionManager type="JDBC"></transactionManager>    <!-- 事务管理类型：JDBC、MANAGED -->
  17.             <dataSource type="POOLED">    <!-- 数据源类型：POOLED、UNPOOLED、JNDI -->
  18.                 <property name="driver" value="com.mysql.jdbc.Driver" />
  19.                 <property name="url" value="jdbc:mysql://localhost:3306/test?characterEncoding=utf-8" />
  20.                 <property name="username" value="root" />
  21.                 <property name="password" value="root" />
  22.             </dataSource>
  23.         </environment>
  24.     </environments>
  26.     <!-- 加载映射文件 mapper -->
  27.     <mappers>
  28.         <!-- 路径用 斜线（/） 分割，而不是用 点(.) -->
  29.         <mapper resource="yeepay/payplus/mapper/UserMapper.xml"/>
  30.         <mapper resource="yeepay/payplus/mapper/CustomerMapper.xml"/>
  31.     </mappers>
  32. </configuration>

创建 CeshiCustomer 测试类

  1. public class CeshiCustomer {
  2.     @Test
  3.     public void testFind() throws IOException {
  4.         /**
  5.          *  1、获得 SqlSessionFactory
  6.          *  2、获得 SqlSession
  7.          *  3、调用在 mapper 文件中配置的 SQL 语句
  8.          */
  9.         String resource = "sqlMapConfig.xml";           // 定位核心配置文件
  10.         InputStream inputStream = Resources.getResourceAsStream(resource);
  11.         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);    // 创建 SqlSessionFactory
  13.         SqlSession sqlSession = sqlSessionFactory.openSession();    // 获取到 SqlSession
  15.         Map map = new HashMap();
  16.         map.put("customerName", "charies");
  18.         // 调用 mapper 中的方法：命名空间 + id
  19.         List<Customer> customerList = sqlSession.selectList("yeepay.payplus.mapper.CustomerMapper.find", map);
  21.         for (Customer c : customerList) {
  22.             System.out.println(c);
  23.         }
  24.     }


输入映射：目前在mybatis用到的都是parameterType类型，其中可以是简单数据类型，也可以是包装类型（POJO）
parameterType:java.lang.Integer\java.lang.String\POJO模型类。
输出映射

  • 输出的类型为POJO包装类（model失血模型），用的是resultType
  • 输出的类型为简单数据类型,resultType,要求:查询的结果集必须是一行一列的数据
  • 输出类型为resultMap方式，当前的作用，当java中失血模型对象中的字段和数据库中的字段名称不一致的时候，可以使用resultMap方式进行自定义映射关系。

代码：

  1. <resultMap type="User" id="userResult">
  2.         <!-- id: 用来关联在查询结果集中可以唯一识别一个实体的字段，一般都是主键
  3.             column:在查询结果集中，能够唯一识别一个实体的字段名
  4.             property:结果集中的数据和失血模型中对应的属性名。
  5.         -->
  6.         <id column="id" property="user_id"/>
  7.         <!-- 
  8.             result:除了能唯一识别实体字段以外的其他普通字段
  9.             column:在查询结果集中，数据库的字段名
  10.             property:结果集中的数据和失血模型中对应的属性名。
  11.          -->
  12.         <result column="userName" property="user_name"/>
  13.         <result column="pass" property="password"/>
  14.     </resultMap>
  16. <!-- 查询所有用户 -->
  17.     <select id="queryAllUser" resultMap="userResult">
  18.         SELECT user_id id,user_name userName,`password` pass FROM t_user
  19.     </select>

动态sql

  1. <!-- 编写一个sql片段，供以后重复使用sql片段 -->
  2.     <sql id="query_user_info">
  3.         <if test="user.user_name != null">
  4.                 AND s1.user_name like "%${user.user_name}%" 
  5.         </if>
  6.         <if test="userInfo.gender != null">
  7.                 AND s2.gender = #{userInfo.gender}
  8.         </if>
  9.     </sql>
  10. <!-- 使用sql片段 -->
  11. <select id="queryUserAndInfo" parameterType="com.woniuxy.mall.model.UserCustom" resultType="User">
  12.         SELECT * FROM t_user as s1 JOIN t_userinfo as s2 on s1.user_id = s2.user_id  
  13.         <where>
  14.             <include refid="query_user_info"></include>
  15.         </where>
  16.     </select>

一对一、一对多、多对多查询

一对一的关系：人与身份证

Idcard 的代码：

  1. public class Idcard  {
  3.     private Integer id;
  4.     private String code;
  5.     public day(Integer id, String code) {
  6.         super();
  7.         this.id = id;
  8.         this.code = code;
  9.     }
  10.     public day() {
  11.         super();
  12.     }
  13.     /**
  14.      * @return the id
  15.      */
  16.     public Integer getId() {
  17.         return id;
  18.     }
  19.     /**
  20.      * @param id the id to set
  21.      */
  22.     public void setId(Integer id) {
  23.         this.id = id;
  24.     }
  25.     /**
  26.      * @return the code
  27.      */
  28.     public String getCode() {
  29.         return code;
  30.     }
  31.     /**
  32.      * @param code the code to set
  33.      */
  34.     public void setCode(String code) {
  35.         this.code = code;
  36.     }
  37.     @Override
  38.     public String toString() {
  39.         return "day [id=" + id + ", code=" + code + "]";
  40.     }

Person 的代码：

  1. public class Person {
  2.     private Integer id;
  3.     private String name;
  4.     private Integer age;
  6.     // 个人身份证关联
  7.     private Idcard card;
  9.     // 省略setter和getter方法
  10.     @Override
  11.     public String toString() {
  12.         return "Person[id=" + id + ",name=" + name + ",age=" + age + ",card="
  13.                 + card + "]";
  14.     }
  15. }

IdCardMapper.xml 的代码：

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3. PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4. "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.dao.IdCardDao">
  6.     <select id="selectCodeById" parameterType="Integer" resultType= "com.po.Idcard">
  7.         select * from idcard where id=#{id}
  8.     </select>
  9. </mapper>

PersonMapper.xml 的代码：

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3. PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4. "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.dao.PersonDao">
  6.     <!-- 一对一根据id查询个人信息：级联查询的第一种方法（嵌套查询，执行两个SQL语句）-->
  7.     <resultMap type="com.po.Person" id="cardAndPerson1">
  8.         <id property="id" column="id"/>
  9.         <result property="name" column="name"/>
  10.         <result property="age" column="age"/>
  11.         <!-- 一对一级联查询-->
  12.         <association property="card" column="idcard_id" javaType="com.po.Idcard"
  13.         select="com.dao.IdCardDao.selectCodeByld"/>
  14.     </resultMap>
  15.     <select id="selectPersonById1" parameterType="Integer" resultMap=
  16.     "cardAndPerson1">
  17.         select * from person where id=#{id}
  18.     </select>
  19.     <!--对一根据id查询个人信息：级联查询的第二种方法（嵌套结果，执行一个SQL语句）-->
  20.     <resultMap type="com.po.Person" id="cardAndPerson2">
  21.         <id property="id" column="id"/>
  22.         <result property="name" column="name"/>
  23.         <result property="age" column="age"/>
  24.         <!-- 一对一级联查询-->
  25.         <association property="card" javaType="com.po.Idcard">
  26.             <id property="id" column="idcard_id"/>
  27.             <result property="code" column="code"/>
  28.         </association>
  29.     </resultMap>
  31.     <select id="selectPersonById2" parameterType="Integer" resultMap= "cardAndPerson2">
  32.         select p.*,ic.code
  33.         from person p, idcard ic
  34.         where p.idcard_id=ic.id and p.id=#{id}
  35.     </select>
  37.     <!-- 一对一根据id查询个人信息：连接查询（使用POJO存储结果）-->
  38.     <select id="selectPersonById3" parameterType="Integer" resultType= "com.pojo.SelectPersonById">
  39.         select p.*,ic.code
  40.         from person p, idcard ic
  41.         where p.idcard_id = ic.id and p.id=#{id}
  42.     </select>
  43. </mapper>

创建 POJO 类
SelectPersonById 的代码

  1. public class SelectPersonById {
  2.     private Integer id;
  3.     private String name;
  4.     private Integer age;
  5.     private String code;
  7.     public day() {
  8.         super();
  9.     }
  11.     public day(Integer id, String name, Integer age, String code) {
  12.         super();
  13.         this.id = id;
  14.         this.name = name;
  15.         this.age = age;
  16.         this.code = code;
  17.     }
  19.     /**
  20.      * @return the id
  21.      */
  22.     public Integer getId() {
  23.         return id;
  24.     }
  26.     /**
  27.      * @param id the id to set
  28.      */
  29.     public void setId(Integer id) {
  30.         this.id = id;
  31.     }
  33.     /**
  34.      * @return the name
  35.      */
  36.     public String getName() {
  37.         return name;
  38.     }
  40.     /**
  41.      * @param name the name to set
  42.      */
  43.     public void setName(String name) {
  44.         this.name = name;
  45.     }
  47.     /**
  48.      * @return the age
  49.      */
  50.     public Integer getAge() {
  51.         return age;
  52.     }
  54.     /**
  55.      * @param age the age to set
  56.      */
  57.     public void setAge(Integer age) {
  58.         this.age = age;
  59.     }
  61.     /**
  62.      * @return the code
  63.      */
  64.     public String getCode() {
  65.         return code;
  66.     }
  68.     /**
  69.      * @param code the code to set
  70.      */
  71.     public void setCode(String code) {
  72.         this.code = code;
  73.     }
  75.     @Override
  76.     public String toString() {
  77.         return "day [id=" + id + ", name=" + name + ", age=" + age + ", code=" + code + "]";
  78.     }

创建数据操作接口：
IdCardDao 的代码

  1. public interface IdCardDao {
  2.     public Idcard selectCodeById(Integer i);
  3. }

PersonDao 的代码

  1. public interface PersonDao {
  2.     public Person selectPersonById1(Integer id);
  3.     public Person selectPersonById2(Integer id);
  4.     public SelectPersonById selectPersonById3(Integer id);
  5. }

调用

  1. public class OneToOneController {
  2.     @Autowired
  3.     private PersonDao personDao;
  4.     public void test(){
  5.         Person p1 = personDao.selectPersonById1(1);
  6.         System.out.println(p1);
  7.         System.out.println("=============================");
  8.         Person p2 = personDao.selectPersonById2(1);
  9.         System.out.println(p2);
  10.         System.out.println("=============================");
  11.         selectPersonById p3 = personDao.selectPersonById3(1);
  12.         System.out.println(p3);
  13.     }
  14. }

测试：

  1. public class TestOneToOne {
  2.     public static void main(String[] args) {
  3.         ApplicationContext appcon = new ClassPathXmlApplicationContext("applicationContext.xml");
  4.         OneToOneController oto = (OneToOneController)appcon.getBean("oneToOne-Controller");
  5.         oto.test();
  6.     }
  7. }

一对多关系：一对多级联查询，用户关联的订单

Orders 类的代码：

  1. public class Orders {
  2.     private Integer id;
  3.     private String ordersn;
  5.     public Integer getId() {
  6.         return id;
  7.     }
  9.     public void setId(Integer id) {
  10.         this.id = id;
  11.     }
  13.     public String getOrdersn() {
  14.         return ordersn;
  15.     }
  17.     public void setOrdersn(String ordersn) {
  18.         this.ordersn = ordersn;
  19.     }
  21.     @Override
  22.     public String toString() {
  23.         return "Orders[id=" + id + ",ordersn=" + ordersn + "]";
  24.     }
  25. }

创建UserMapper.xml

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <!DOCTYPE mapper
  3. PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4. "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.mybatis.mapper.UserMapper">
  6.     <!-- 一对多 根据uid查询用户及其关联的订单信息：级联查询的第一种方法（嵌套查询） -->
  7.     <resultMap type="com.po.MyUser" id="userAndOrders1">
  8.         <id property="uid" column="uid" />
  9.         <result property="uname" column="uname" />
  10.         <result property="usex" column="usex" />
  11.         <!-- 一对多级联查询，ofType表示集合中的元素类型，将uid传递给selectOrdersByld -->
  12.         <collection property="ordersList" ofType="com.po.Orders"
  13.             column="uid" select="com.dao.OrdersDao.selectOrdersByld" />
  14.     </resultMap>
  15.     <select id="selectUserOrdersById1" parameterType="Integer"
  16.         resultMap="userAndOrders1">
  17.         select * from user where uid = #{id}
  18.     </select>
  19.     <!--对多根据uid查询用户及其关联的订单信息：级联查询的第二种方法（嵌套结果） -->
  20.     <resultMap type="com.po.MyUser" id="userAndOrders2">
  21.         <id property="uid" column="uid" />
  22.         <result property="uname" column="uname" />
  23.         <result property="usex" column="usex" />
  24.         <!-- 对多级联查询，ofType表示集合中的元素类型 -->
  25.         <collection property="ordersList" ofType="com.po.Orders">
  26.             <id property="id" column="id" />
  27.             <result property="ordersn" column="ordersn" />
  28.         </collection>
  29.     </resultMap>
  30.     <select id="selectUserOrdersById2" parameterType="Integer"
  31.         resultMap="userAndOrders2">
  32.         select u.*,o.id, o.ordersn from user u, orders o where u.uid
  33.         = o.user_id and
  34.         u.uid=#{id}
  35.     </select>
  36.     <!-- 一对多 根据uid查询用户及其关联的订单信息：连接查询（使用POJO存储结果） -->
  37.     <select id="selectUserOrdersById3" parameterType="Integer"
  38.         resultType="com.pojo.SelectUserOrdersById">
  39.         select u.*, o.id, o.ordersn from user u, orders o where
  40.         u.uid = o.user_id
  41.         and u.uid=#{id}
  42.     </select>
  43. </mapper>

创建：OrdersMapper.xml

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <!DOCTYPE mapper
  3. PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4. "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.dao.OrdersDao">
  6.     <!-- 根据用户uid查询订单信息 -->
  7.     <select id="selectOrdersById" resultType="com.po.Orders"
  8.         parameterType="Integer">
  9.         select * from orders where user_id=#{id}
  10.     </select>
  11. </mapper>

创建 POJO 类
SelectUserOrdersById代码：

  1. public class SelectUserOrdersById {
  2.     private Integer uid;
  3.     private String uname;
  4.     private String usex;
  5.     private Integer id;
  6.     private String ordersn;
  8.     // 省略setter和getter方法
  9.     @Override
  10.     public String toString() { // 为了方便查看结果，重写了toString方法
  11.         return "User[uid=" + uid + ",uname=" + uname + ",usex=" + usex
  12.                 + ",oid=" + id + ",ordersn=" + ordersn + "]";
  13.     }
  14. }

创建数据操作接口

  1. public interface OrdersDao {
  2.     public List<Orders> selectOrdersById(Integer uid);
  3. }
  1. public interface UserDao {
  3.     public MyUser selectOrdersById1(Integer uid);
  5.     public MyUser selectOrdersById2(Integer uid);
  7.     public List<SelectUserOrdersById> selectOrdersById3(Integer uid);
  8. }

调用接口方法

  1.  public void test(){
  2.         //查询一个用户及订单信息
  3.         MyUser auser1 = userDao.selectUserOrderById1(1);
  4.         System.out.println(auser1);
  5.         System.out.println("=============================");
  6.         MyUser auser2 = userDao.selectUserOrderById2(1);
  7.         System.out.println(auser2);
  8.         System.out.println("=============================");
  9.         List<SelectUserOrdersById> auser3 = userDao.selectUserOrdersById3(1);
  10.         System.out.println(auser3);
  11.         System.out.println("=============================");
  12.     }

测试：

  1. public class TestOneToMore {
  2.     public static void main(String[] args) {
  3.         ApplicationContext appcon = new ClassPathXmlApplicationContext("applicationContext.xml");
  4.         OneToMoreController otm = (OneToMoreController)appcon.getBean("oneToMoreController");
  5.         otm.test();
  6.     }
  7. }

多对多的关系：订单和商品

Product 的代码：

  1. public class Product {
  3.     private Integer id;
  4.     private String name;
  5.     private Double price;
  7.     public day() {
  8.         super();
  9.     }
  11.     public day(Integer id, String name, Double price) {
  12.         super();
  13.         this.id = id;
  14.         this.name = name;
  15.         this.price = price;
  16.     }
  18.     /**
  19.      * @return the id
  20.      */
  21.     public Integer getId() {
  22.         return id;
  23.     }
  25.     /**
  26.      * @param id the id to set
  27.      */
  28.     public void setId(Integer id) {
  29.         this.id = id;
  30.     }
  32.     /**
  33.      * @return the name
  34.      */
  35.     public String getName() {
  36.         return name;
  37.     }
  39.     /**
  40.      * @param name the name to set
  41.      */
  42.     public void setName(String name) {
  43.         this.name = name;
  44.     }
  46.     /**
  47.      * @return the price
  48.      */
  49.     public Double getPrice() {
  50.         return price;
  51.     }
  53.     /**
  54.      * @param price the price to set
  55.      */
  56.     public void setPrice(Double price) {
  57.         this.price = price;
  58.     }
  60.     @Override
  61.     public String toString() {
  62.         return "day [id=" + id + ", name=" + name + ", price=" + price + "]";
  63.     }

Orders 的代码：

  2. public class Orders {
  3.     private Integer id;
  4.     private String ordersn;
  5.     public day() {
  6.         super();
  7.     }
  8.     public day(Integer id, String ordersn) {
  9.         super();
  10.         this.id = id;
  11.         this.ordersn = ordersn;
  12.     }
  13.     /**
  14.      * @return the id
  15.      */
  16.     public Integer getId() {
  17.         return id;
  18.     }
  19.     /**
  20.      * @param id the id to set
  21.      */
  22.     public void setId(Integer id) {
  23.         this.id = id;
  24.     }
  25.     /**
  26.      * @return the ordersn
  27.      */
  28.     public String getOrdersn() {
  29.         return ordersn;
  30.     }
  31.     /**
  32.      * @param ordersn the ordersn to set
  33.      */
  34.     public void setOrdersn(String ordersn) {
  35.         this.ordersn = ordersn;
  36.     }
  37.     @Override
  38.     public String toString() {
  39.         return "day [id=" + id + ", ordersn=" + ordersn + ", getId()=" + getId() + ", getOrdersn()=" + getOrdersn()
  40.                 + ", getClass()=" + getClass() + ", hashCode()=" + hashCode() + ", toString()=" + super.toString()
  41.                 + "]";
  42.     }

在 Orders 接口中添加以下接口方法：

public List selectallOrdersAndProducts();
调用接口方法

  1. public class MoreToMoreController {
  2.     @Autowired
  3.     private OrdersDao ordersDao;
  5.     public void test() {
  6.         List<Orders> os = ordersDao.selectallOrdersAndProducts();
  7.         for (Orders orders : os) {
  8.             System.out.println(orders);
  9.         }
  10.     }
  11. }

测试：

  1. public class TestMoreToMore {
  2.     public static void main(String[] args) {
  3.         ApplicationContext appcon = new ClassPathXmlApplicationContext(
  4.                 "applicationContext.xml");
  5.         MoreToMoreController otm = (MoreToMoreController) appcon
  6.                 .getBean("moreToMoreController");
  7.         otm.test();
  8.     }
  9. }

延迟加载、一级缓存和二级缓存

什么是延迟加载
resultMap中的association和collection标签具有延迟加载的功能。
延迟加载的意思是说，在关联查询时，利用延迟加载，先加载主信息。使用关联信息时再去加载关联信息
设置延迟加载
需要在SqlMapConfig.xml文件中，在标签中设置下延迟加载。
lazyLoadingEnabled、aggressiveLazyLoading

  1. <!-- 开启延迟加载 -->
  2.     <settings>
  3.         <!-- lazyLoadingEnabled:延迟加载启动，默认是false -->
  4.         <setting name="lazyLoadingEnabled" value="true"/>
  5.         <!-- aggressiveLazyLoading：积极的懒加载，false的话按需加载，默认是true -->
  6.         <setting name="aggressiveLazyLoading" value="false"/>
  8.         <!-- 开启二级缓存，默认是false -->
  9.         <setting name="cacheEnabled" value="true"/>
  10.     </settings>

Mybatis的一级缓存是指SqlSession。一级缓存的作用域是一个SqlSession。Mybatis默认开启一级缓存。
在同一个SqlSession中，执行相同的查询SQL，第一次会去查询数据库，并写到缓存中；第二次直接从缓存中取。当执行SQL时两次查询中间发生了增删改操作，则SqlSession的缓存清空。
Mybatis的二级缓存是指mapper映射文件。二级缓存的作用域是同一个namespace下的mapper映射文件内容，多个SqlSession共享。Mybatis需要手动设置启动二级缓存。
在同一个namespace下的mapper文件中，执行相同的查询SQL，第一次会去查询数据库，并写到缓存中；第二次直接从缓存中取。当执行SQL时两次查询中间发生了增删改操作，则二级缓存清空。
一级缓存：

image.png
二级缓存：
image.png