MyBatis基础与进阶

2023/10/30

后端

MyBatis[ORM]框架基础

软件开发中框架

框架式可被应用开发者定制的应用骨架
框架是一种规则，保证开发者遵循相同的方式开发程序
框架提倡”不要重复造轮子”,对基础功能进行封装

框架的优点

极大提高了开发效率
统一的编码规则，利于团队管理
灵活配置的应用，拥有更好的维护性

SSM开发框架

Spring对象容器框架(框架的框架) [提供底层对象的管理]
SpringMVC替代servlet更有效的开发 [提供Web界面的交互]
MyBatis简化数据库的开发 [数据库增删改查便捷操作]

MyBatis

MyBatis是优秀的持久层框架(将内存中的数据保存到数据库中以防止重启后数据丢失)
MyBatis使用XML将SQL与程序解耦，便于维护[改代码改xml更方便]
MyBatis是JDBC的延伸

MyBatis开发流程【推荐使用Maven】

引入MyBatis依赖
创建核心配置文件
创建实体(Entity)类
创建Mapper映射文件
初始化SessionFactory(绘画工厂读取配置文件加载Mapper映射)
利用SqlSession对象操作数据

单元测试与JUnit4

单元测试(用于测试方法的方法)

单元测试是指对软件中的最小可测试单元进行检查和验证
测试用例是指写一段代码对已有功能(方法)进行校验
JUnit4是Java中最著名的单元测试工具，主流IDE内置支持

JUnit4使用方法

引入JUnit Jar包或增加Maven依赖
编写测试用例验证目标方法是否正确运行
在测试用例上增加**@Test**注解开始单元测试

如果插件plug报错点settings 找到 Build,Execution,Deployment → Build Tools → Maven 修改下面的三个地址到Maven

Maven home path: D:/apache-maven-3.9.5
User settings files: D:\apache-maven-3.9.5\conf\settings.xml
Local repository: D:\apache-maven-3.9.5\repository

Maven工程有专门测试用例的 test包
方法命名：在原方法前增加test前缀 Class命名：在原有命名后增加Test

快速生成类的测试用例 => 选中类 → Code → Generate → Test
按住Ctrl可以多选执行多个测试用例类
如果想运行所有测试用例类工程上点右键 → Run ‘All Test’

Calculator.java
package org.example;

public class Calculator {
    public int add(int a, int b){
        return a + b;
    }
    public int substract(int a, int b){
        return a - b;
    }
    public int multiply(int a, int b){
        return a * b;
    }
    public float divide(int a, int b){
        if (b==0){
            throw new ArithmeticException("除数不能为0");
        }
        return (a*1f) / b;
    }
}

CalculatorTest.java
import org.example.Calculator;
import org.junit.Test;

public class CalculatorTest {
    private Calculator cal = new Calculator();
    @Test
    public void testAdd(){
        int result = cal.add(1,2);
        System.out.println(result);
    }
    @Test
    public void testSubstract(){
        int result = cal.substract(1,2);
        System.out.println(result);
    }
    @Test
    public void testMultiply(){
        int result = cal.multiply(1,2);
        System.out.println(result);
    }
    @Test
    public void testDivide(){
        float result = cal.divide(1,2);
        System.out.println(result);
    }
    @Test
    public void testDivide1(){
        float result = cal.divide(1,0);
        System.out.println(result);
    }
}

MyBatis环境配置

mybatis-config.xml

MyBatis采用XML格式配置数据库环境信息
MyBatis环境配置标签**< environment >**
environment包含数据库驱动、URL、用户名与密码

mybatis-config.xml
<!--配置环境,不同的环境不同的id名字-->
<enviroment id="dev">
    <!--采用JDBC方式对数据库事务进行commit/rollback-->
    <transactionManager type="JDBC"></transactionManager>
    <!--采用连接池方式管理数据库连接-->
    <dataSource type="POOLED">
        <property name="driver" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/db"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </dataSource>
</enviroment>

<groupId>org.example</groupId>
    <artifactId>JUnit4_Maven</artifactId>
    <version>1.0-SNAPSHOT</version>
    <repositories>
        <repository>
            <id>aliyun</id>
            <name>aliyun</name>
            <url>http://maven.aliyun.com/repository/public</url>
        </repository>
    </repositories>

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.1</version>
        </dependency>
    </dependencies>

可以在右侧加载内置Database数据库

<configuration>
    <environments default=""> 
        <environment id="">
            <transactionManager type="JDBC"></transactionManager> 
            <dataSource type=""></dataSource>
        </environment>
    </environments>
</configuration>

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="dev"> <!--当环境默认数据源为dev时候使用运行的代码[不同id来切换]-->
        <environment id="dev">  <!--唯一标识-->
            <!--采用JDBC方式对数据库事务进行管理-->
            <transactionManager type=""></transactionManager>
            <!--采用连接池方式管理数据库连接-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/babytun?useUnicode=true&amp;characterEncoding=UTFF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
</configuration>

SqlSessionFactory

SqlSessionFactory是MyBatis的核心对象
用于初始化MyBatis, 创建SqlSession对象
SqlSession对象提供了数据表CRUD对应方法
保证SqlSessionFactory在应用中全局唯一

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="prd"> <!--当环境默认数据源为dev时候使用运行的代码[不同id来切换]-->
        <environment id="dev">  <!--唯一标识-->
            <!--采用JDBC方式对数据库事务进行管理-->
            <transactionManager type="JDBC"></transactionManager>
            <!--采用连接池方式管理数据库连接-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/babytun?useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
        <environment id="prd">
            <!-- 采用JDBC方式对数据库事务进行commit/rollback -->
            <transactionManager type="JDBC"></transactionManager>
            <!--采用连接池方式管理数据库连接-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://192.168.1.155:3306/babytun?useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
</configuration>

test-MyBatisTestor.java
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 org.junit.Test;

import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;

//JUNIT单元测试类
public class MyBatisTestor {
    @Test
    public void testSqlSessionFactory() throws IOException {
        //利用Reader加载classpath下的mybatis-config.xml核心配置文件
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");//按照字符流读取
        //初始化SqlSessionFactory对象,同时解析mybatis-config.xml文件
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);//read解析上面的对象
        System.out.println("SessionFactory加载成功");
        SqlSession sqlSession = null;
        try{
            //创建SqlSession对象,SqlSession是JDBC的扩展类,用于数据库交互
            sqlSession = sqlSessionFactory.openSession();
            //创建数据库连接(测试用)
            Connection connection = sqlSession.getConnection();
            System.out.println(connection);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (sqlSession != null){
                //如果type="POOLED",代表使用连接池,close则是将连接回收到连接池中
                //如果type="UNPOOLED",代表直连,close则会调用Connection.close()方法关闭连接
                sqlSession.close();
            }
        }
    }
}

保证SqlSessionFactory在应用中全局唯一 [创建一个工具类]

static块用于初始化静态对象

MyBatisUtils.java
package com.imooc.mybatis.utils;

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

import java.io.IOException;
import java.io.Reader;
//MaBatisUtils工具类,创建全局唯一的SqlSessionFactory对象
public class MyBatisUtils {
    //利用static(静态)属于类不属于对象,且全局唯一
    private static SqlSessionFactory sqlSessionFactory = null;
    static{ //利用静态块在初始化类时实例化sqlSessionFactory
        //利用Reader加载classpath下的mybatis-config.xml核心配置文件
        Reader reader = null;//按照字符流读取
        try {
            reader = Resources.getResourceAsReader("mybatis-config.xml");
            //初始化SqlSessionFactory对象,同时解析mybatis-config.xml文件
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);//read解析上面的对象
        } catch (IOException e) {
            e.printStackTrace();
            //初始化错误时,通过抛出异常ExceptionInInitializerError通知调用者
            throw new ExceptionInInitializerError(e);
        }
    }
    public static SqlSession openSession(){
        return sqlSessionFactory.openSession();
    }

    public static void closeSession(SqlSession session){
        if (session != null){
            session.close();
        }
    }
}

MyBatisTestor.java
import com.imooc.mybatis.utils.MyBatisUtils;
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 org.junit.Test;

import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;

//JUNIT单元测试类
public class MyBatisTestor {
    @Test
    public void testSqlSessionFactory() throws IOException {
        //利用Reader加载classpath下的mybatis-config.xml核心配置文件
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");//按照字符流读取
        //初始化SqlSessionFactory对象,同时解析mybatis-config.xml文件
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);//read解析上面的对象
        System.out.println("SessionFactory加载成功");
        SqlSession sqlSession = null;
        try{
            //创建SqlSession对象,SqlSession是JDBC的扩展类,用于数据库交互
            sqlSession = sqlSessionFactory.openSession();
            //创建数据库连接(测试用)
            Connection connection = sqlSession.getConnection();
            System.out.println(connection);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (sqlSession != null){
                //如果type="POOLED",代表使用连接池,close则是将连接回收到连接池中
                //如果type="UNPOOLED",代表直连,close则会调用Connection.close()方法关闭连接
                sqlSession.close();
            }
        }
    }
    @Test
    public void testMyBatisUtils() throws Exception{
        SqlSession sqlSession = null;
        try{
            sqlSession = MyBatisUtils.openSession();
            Connection connection = sqlSession.getConnection();
            System.out.println(connection);
        }catch (Exception e){
            throw e;
        }finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }
}

MyBatis数据查询

用测试类去测试代码

test-java-MyBatisTestor.java
import com.imooc.mybatis.MyBatisUtils;
import com.imooc.mybatis.entity.Goods;
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 org.junit.Test;

import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.util.List;

//JUNIT单元测试类
public class MyBatisTestor {
    @Test
    public void testSqlSessionFactory() throws IOException {
        //利用Reader加载classpath下的mybatis-config.xml核心配置文件
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");//按照字符流读取
        //初始化SqlSessionFactory对象,同时解析mybatis-config.xml文件
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);//read解析上面的对象
        System.out.println("SessionFactory加载成功");
        SqlSession sqlSession = null;
        try{
            //创建SqlSession对象,SqlSession是JDBC的扩展类,用于数据库交互
            sqlSession = sqlSessionFactory.openSession();
            //创建数据库连接(测试用)
            Connection connection = sqlSession.getConnection();
            System.out.println(connection);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (sqlSession != null){
                //如果type="POOLED",代表使用连接池,close则是将连接回收到连接池中
                //如果type="UNPOOLED",代表直连,close则会调用Connection.close()方法关闭连接
                sqlSession.close();
            }
        }
    }
    /**
     * MyBatisUtils使用指南
     * @throws Exception
     */
    @Test
    public void testMyBatisUtils() throws Exception {
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.openSession();
            Connection connection = sqlSession.getConnection();
            System.out.println(connection);
        }catch (Exception e){
            throw e;
        } finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }

    /**
     * select查询语句执行
     * @throws Exception
     */
    @Test
    public void testSelectAll() throws Exception {
        SqlSession session = null;
        try{
            session = MyBatisUtils.openSession();
            List<Goods> list = session.selectList("goods.selectAll");
            for(Goods g : list){
                System.out.println(g.getTitle());
            }
        }catch (Exception e){
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }
    }
}

MyBatis数据查询步骤

创建实体类(Entity) /工具类(Utils)

Goods.java
private Integer goodsId;//商品编号
    private String title;//标题
    private String subTitle;//子标题
    private Float originalCost;//原始价格
    private Float currentPrice;//当前价格
    private Float discount;//折扣率
    private Integer isFreeDelivery;//是否包邮 ,1-包邮 0-不包邮
    private Integer categoryId;//分类编号
    Getter+Setter...

MyBatisUtils.java
package com.imooc.mybatis;

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

import java.io.IOException;
import java.io.Reader;
//MaBatisUtils工具类,创建全局唯一的SqlSessionFactory对象
public class MyBatisUtils {
    //利用static(静态)属于类不属于对象,且全局唯一
    private static SqlSessionFactory sqlSessionFactory = null;
    static{ //利用静态块在初始化类时实例化sqlSessionFactory
        //利用Reader加载classpath下的mybatis-config.xml核心配置文件
        Reader reader = null;//按照字符流读取
        try {
            reader = Resources.getResourceAsReader("mybatis-config.xml");
            //初始化SqlSessionFactory对象,同时解析mybatis-config.xml文件
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);//read解析上面的对象
        } catch (IOException e) {
            e.printStackTrace();
            //初始化错误时,通过抛出异常ExceptionInInitializerError通知调用者
            throw new ExceptionInInitializerError(e);
        }
    }

    /**
     * openSession 创建一个新的SqlSession对象
     * @return SqlSession对象
     */
    public static SqlSession openSession(){
        return sqlSessionFactory.openSession();
    }

    /**
     * 释放一个有效的SqlSession对象
     * @param session 准备释放SqlSession对象
     */
    public static void closeSession(SqlSession session){
        if(session != null){
            session.close();
        }
    }
}

创建Mapper XML (在resources下创建) 表属性和字段一一对应

若要让mybatis认识xml 要在mybatis-config.xml里面声明增加< mappers >

goods.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="goods"> <!--区分不同的工作空间-->
    <select id="selectAll" resultType="com.imooc.mybatis.entity.Goods">
        select * from t_goods order by goods_id desc limit 10<!--将每一条记录包装成↑的goods对象-->
    </select>
</mapper>

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="prd"> <!--当环境默认数据源为dev时候使用运行的代码[不同id来切换]-->
        <environment id="dev">  <!--唯一标识-->
            <!--采用JDBC方式对数据库事务进行管理-->
            <transactionManager type="JDBC"></transactionManager>
            <!--采用连接池方式管理数据库连接-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/babytun?useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
        <environment id="prd">
            <!-- 采用JDBC方式对数据库事务进行commit/rollback -->
            <transactionManager type="JDBC"></transactionManager>
            <!--采用连接池方式管理数据库连接-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://192.168.1.155:3306/babytun?useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mappers/goods.xml"/>
    </mappers>
</configuration>

编写< select >SQL标签

goods.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="goods"> <!--区分不同的工作空间-->
    <select id="selectAll" resultType="com.imooc.mybatis.entity.Goods">
        select * from t_goods order by goods_id desc limit 10<!--将每一条记录包装成↑的goods对象-->
    </select>
</mapper>

开启驼峰命名映射

<settings><!--开启驼峰命名转换 goods_id → goodsId-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

新增< mapper >

mybatis-config.xml
<mappers>
        <mapper resource="mappers/goods.xml"/>
    </mappers>

SqlSession执行select语句

SQL传参[动态传入数据]

goods.xml
<select id="selectById" resultType="com.imooc.mybatis.entity.Goods">
    select * from t_goods where goods_id = #{value}
</select>

MyBatisTestor.java
@Test
    public void testSelectById() throws Exception{
        SqlSession session = null;
        try{
            session = MyBatisUtils.openSession();
            Goods goods = session.selectOne("goods.selectById", 1602);
            System.out.println(goods.getTitle());
        }catch (Exception e){
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }
    }

goods.xml
<select id="selectByPriceRange" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods">
            select * from t_goods
            where
                current_price between #{min} and #{max}
            order by current_price
            limit 0,#{limit}
    </select>
        
MyBatisTestor.java (多参数传递要指定Map接口)
@Test
    public void selectByPriceRange() throws Exception{
        SqlSession session = null;
        try{
            session = MyBatisUtils.openSession();
            Map param = new HashMap();
            param.put("min", 100);
            param.put("max", 500);
            param.put("limit", 10);
            List<Goods> list = session.selectList("goods.selectByPriceRange",param);
            for (Goods g:list){
                System.out.println(g.getTitle() + ":" + g.getCurrentPrice());
            }
        }catch (Exception e){
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }
    }

获取多表关联查询结果[Map]

LinkedHashMap链表形式的HashMap不会出现乱序

利用LinkedHashMap保存多表关联结果
MyBatis会将每一条记录包装为LinkedHashMap对象
key是字段名，value是字段对应的值，字段类型根据表结构进行自动判断
优点：易于扩展，易于使用
缺点：太过灵活，无法进行编译时检查

goods.xml
<select id="selectGoodsMap" resultType="java.util.LinkedHashMap">
        select g.* , c.category_name from t_goods g , t_category c
        where g.category_id = c.category_id
    </select>
</mapper>

MyBatisTestor.java [map类 或 实体类]
@Test
    public void selectGoodsMap() throws Exception{
        SqlSession session = null;
        try{
            session = MyBatisUtils.openSession();
            List<Map> list = session.selectList("goods.selectGoodsMap");
            for (Map map : list){
                System.out.println(map);
            }
        }catch (Exception e){
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }
}

ResultMap结果映射[多表查询结果多人协作首选]

把复杂的查询结果映射成DTO对象来进行保存调用的时候轻松获得属性 [书写大量的映射规则]

ResultMap可以将查询结果映射为复杂类型的Java对象
ResultMap适用于Java对象保存多表关联结果
ResultMap支持对象关联查询等高级特性

结果用java对象进行保存 dto包[GoodsDTO]是数据传输对象包[扩展包] 为了扩展的需要可以扩展

结果映射规则进行赋值 转换为哪个dto
查询出来后每次都会給goods中的goodsId进行赋值
用GoodsDTO.java 来承载多表关联查询的结果

GoodsDTO.java
public class GoodsDTO {
    private Goods goods = new Goods();
    private String categoryName;
    private String test;
    Getter+Setter
}

Goods.java
public class Goods {
    private Integer goodsId;//商品编号
    private String title;//标题
    private String subTitle;//子标题
    private Float originalCost;//原始价格
    private Float currentPrice;//当前价格
    private Float discount;//折扣率
    private Integer isFreeDelivery;//是否包邮 ,1-包邮 0-不包邮
    private Integer categoryId;//分类编号
}

MyBatisTestor.java
@Test
    public void selectGoodsDTO() throws Exception{
        SqlSession session = null;
        try{
            session = MyBatisUtils.openSession();
            List<GoodsDTO> list = session.selectList("goods.selectGoodsDTO");
            for (GoodsDTO g : list){
                System.out.println(g.getGoods().getTitle());
            }
        }catch (Exception e){
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }
    }

Goods.xml
<resultMap id="rmGoods" type="com.imooc.mybatis.dto.GoodsDTO">
        <id property="goods.goodsId" column="goods_id"></id>
        <result property="goods.title" column="title"></result>
        <result property="goods.originalCost" column="original_cost"></result>
        <result property="goods.currentPrice" column="current_price"></result>
        <result property="goods.discount" column="discount"></result>
        <result property="goods.isFreeDelivery" column="is_free_delivery"></result>
        <result property="goods.categoryId" column="category_id"></result>
        <result property="categoryName" column="category_name"/>
        <result property="test" column="test"/>
    </resultMap>
    <select id="selectGoodsDTO" resultMap="rmGoods">
        select g.* , c.category_name from t_goods g , t_category c
        where g.category_id = c.category_id
    </select>

GoodsDTO.java
public class GoodsDTO {
    private Goods goods = new Goods();
    private Category category = new Category();
    private String test;
    Getter+Setter...
}

Category.java
public class Category { //标准的Java Bean 换DTO的  private Category category = new Category();
    private Integer categoryId;
    private String categoryName;
    private Integer parentId;
    private Integer categoryLevel;
    private Integer categoryOrder;
    Getter+Setter...
}

Goods.xml
<resultMap id="rmGoods" type="com.imooc.mybatis.dto.GoodsDTO">
        <id property="goods.goodsId" column="goods_id"></id>
        <result property="goods.title" column="title"></result>
        <result property="goods.originalCost" column="original_cost"></result>
        <result property="goods.currentPrice" column="current_price"></result>
        <result property="goods.discount" column="discount"></result>
        <result property="goods.isFreeDelivery" column="is_free_delivery"></result>
        <result property="goods.categoryId" column="category_id"></result>
        <result property="category.categoryId" column="category_id"/>
        <result property="category.categoryName" column="category_name"/>
        <result property="category.parentId" column="parent_id"/>
        <result property="category.categoryLevel" column="category_level"/>
        <result property="category.categoryOrder" column="category_order"/>
        <result property="test" column="test"/>
    </resultMap>
    <select id="selectGoodsDTO" resultMap="rmGoods">
        select g.* , c.*,'1' from t_goods g , t_category c
        where g.category_id = c.category_id
    </select>

MyBatisTestor.java
@Test
    public void selectGoodsDTO() throws Exception{
        SqlSession session = null;
        try{
            session = MyBatisUtils.openSession();
            List<GoodsDTO> list = session.selectList("goods.selectGoodsDTO");
            for (GoodsDTO g : list){
                System.out.println(g.getGoods().getTitle());
            }
        }catch (Exception e){
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }
    }

MyBatis数据插入操作

数据库事务

数据库事务是保证数据操作完整性的基础

客户端[Java] → 事务日志[增删改查数据] → 向MySQL写入commit → 数据表作为更新数据【若执行roll back后事务日志和数据表的数据都会被清除】

MyBatis写操作包含三种

插入< insert >

goods.xml 新增操作
 <!--flushCache="true"在sql执行后强制清空缓存-->
<insert id="insert" parameterType="com.imooc.mybatis.entity.Goods"flushCache="true">
 INSERT INTO t_goods(title, sub_title, original_cost, current_price, discount, is_free_delivery, category_id)
 VALUES (#{title} , #{subTitle} , #{originalCost}, #{currentPrice}, #{discount}, #{isFreeDelivery}, #{categoryId})
  <selectKey resultType="Integer" keyProperty="goodsId" order="AFTER"> <!--主键自动生成-->
    select last_insert_id()
  </selectKey>
</insert>

MyBatisTestor.java
@Test
    public void selectInsert() throws Exception{
        SqlSession session = null;
        try{
            session = MyBatisUtils.openSession();
            Goods goods = new Goods();
            goods.setTitle("测试商品");
            goods.setSubTitle("测试子标题");
            goods.setOriginalCost(200f);
            goods.setCurrentPrice(100f);
            goods.setDiscount(0.5f);
            goods.setIsFreeDelivery(1);
            goods.setCategoryId(43);
            //insert()方法返回值代表本次成功插入的记录总数
            int num = session.insert("goods.insert", goods);
            session.commit();
            System.out.println(goods.getGoodsId());
        }catch (Exception e){
            if (session != null){
                session.rollback();
            }
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }
    }

更新 < update>
不推荐goods.set…来插入，而是推荐使用获取到原始的商品信息Goods goods = session.selectOne("goods.selectById", 739);再在原始信息上做出调整和更新。对数据影响最小

goods.xml
 <update id="update" parameterType="com.imooc.mybatis.entity.Goods">
        UPDATE t_goods
        SET
          title = #{title},
          sub_title = #{subTitle},
          original_cost = #{originalCost},
          current_price = #{currentPrice},
          discount = #{discount},
          is_free_delivery = #{isFreeDelivery},
          category_id = #{categoryId}
        WHERE
          good_id = #{goodsId} <!--对主键进行筛选-->
    </update>

MyBatisTestor.java
 @Test
    public void testUpdate() throws Exception{
        SqlSession session = null;
        try{
            session = MyBatisUtils.openSession();
            Goods goods = session.selectOne("goods.selectById", 739);//得到指定编号的对象
            goods.setTitle("更新测试商品");
            int num = session.update("goods.update", goods);
            session.commit();
        }catch (Exception e){
            if (session!=null)
                session.rollback();
        }
    }

删除 < delete >
大多数删除操作都是根据主键来运行的

good.xml
<delete id="delete" parameterType="integer">
    delete from t_goods where goods_id = #{value}
</delete>

MyBatisTestor.java
 @Test
    public void Delete(){
        SqlSession session = null;
        try{
            session = MyBatisUtils.openSession();
            int num = session.delete("goods.delete", 739);
            session.commit();
        }catch (Exception e){
            if (session!=null){
                session.rollback();
            }
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }
    }

SelectKey与UserGeneratedKeys的区别

SelectKey属性用法
<insert id="insert" parameterType="com.itlaoqi.mybatis.entity.Goods">
  INSERT INTO SQL语句
============================获取主键值===============================(↑案例)
 <selectKey resultType="Integer" keyProperty="goodsId" order="AFTER">
   select last_insert_id()
 </selectKey>
</insert>

★ selectKey标签需要明确编写获取最新主键的SQL语句  获取主键 ★
★ selectKey适用于所有的关系型数据库 ★
★ selectKey标签是通用方案,适用于所有数据库,但编写麻烦 ★

UserGeneratedKeys属性用法
<insert id="insert" parameterType="com.imooc.mybatis.entity.Goods">
   userGeneratedKeys="true"
   keyProperty="goodsId" <!--主键值-->
   keyColumn="goods_id"> <!--字段名-->
  INSERT INTO SQL语句
</insert>
★ useGeneratedKeys属性会自动根据驱动生成对应SQL语句 自动获取主键 ★
★ useGeneratedKeys只支持"自增主键"类型的数据库 ★
★ useGeneratedKeys属性只支持"自增主键"数据库,使用简单

在Oracle中selectKey的用法

<insert id="insert" parameterType="com.itlaoqi.mybatis.entity.Goods">
  InSERT INTO SQL语句
  <selectKey resultType="Integer" order=“BEFORE” keyProperty="goodsId">
      SELECT seq_goods.nextval as id from dual  
  </selectKey>
</insert>

SQL注入攻击[缺少转义操作]

SQL注入是指攻击者利用SQL漏洞，绕过系统约束，越权获取数据的攻击方式

SQLd代码:
"select * from a where name ='" + name + " '";

正常情况下:
name:张三 → select * from a where name = '张三';

SQL注入攻击:
name:' or 1=1 or 1='
select * from a whrer name = " or 1=1 or 1="

MyBatis两种传值方式

${}文本替换(原文传值)(产生的sql语句绝不能是外界输入的), 未经过任何处理对SQL文本替换 {根据前台输入的条件不同来选择不同的字段排序没有对输入的数据进行预编译处理导致输入的内容变成了sql的一部分}

${order}
param.put("order"," order by title desc");

用${}原传值：select * from t_goods where title = ‘ ‘ or 1 =1 or title = ‘【德国】爱他美婴幼儿配方奶粉1段800g*2罐铂金版’”); 成为sql的一部分
#{}预编译传值, 使用预编译传值可以预防SQL注入

用#{}预编译：select * from t_goods where title = “ ‘ ‘ or 1 =1 or title = ‘【德国】爱他美婴幼儿配方奶粉1段800g*2罐铂金版’”); #输入的会变成字符串

goods.xml
<select id="selectByTitle" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods">
  select * from t_goods where title = ${title}
</select>

MyBatisTestor.java
 @Test
    public void testSelectByTitle(){
        SqlSession session = null;
        try{
            session = MyBatisUtils.openSession();
            Map param = new HashMap();
            pparam.put("title","'【德国】爱他美婴幼儿配方奶粉1段800g*2罐 铂金版'");
            param.put("order", " order by title desc");
//param.put("titile","'' or 1=1 or title='【德国】爱他美婴幼儿配方奶粉1段800g*2罐 铂金版'");
//用${}原传值：select * from t_goods where title = '' or 1 =1 or title = '【德国】爱他美婴幼儿配方奶粉1段800g*2罐 铂金版'"); 成为sql的一部分
//用#{}预编译：select * from t_goods where title = "'' or 1 =1 or title = '【德国】爱他美婴幼儿配方奶粉1段800g*2罐 铂金版'");
            List<Goods> list = session.selectList("goods.selectByTitle", param);
            for(Goods g:list){
                System.out.println(g.getTitle() + ":" + g.getCurrentPrice());
            }
        }catch (Exception e){
            if (session!=null){
                session.rollback();
            }
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }
    }

使用上传照片

MyBatis进阶教程

MyBatis日志管理

什么是日志

日志文件是用于记录系统操作事件的记录文件或文件集合
日志保存历史数据, 是诊断问题以及理解系列活动的重要依据

ch.qos.logback logback-classic 1.2.3

上传照片

goods.xml
<select id="selectByTitle" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods">
        select * from t_goods where title = #{title}
            ${order}
    </select>

MyBatisTestor.java
@Test
    public void testSelectByTitle(){
        SqlSession session = null;
        try{
            session = MyBatisUtils.openSession();
            Map param = new HashMap();
            param.put("title","'【德国】爱他美婴幼儿配方奶粉1段800g*2罐 铂金版'");
            param.put("order", " order by title desc");
//param.put("titile","'' or 1=1 or title='【德国】爱他美婴幼儿配方奶粉1段800g*2罐 铂金版'");
//用${}原传值：select * from t_goods where title = '' or 1 =1 or title = '【德国】爱他美婴幼儿配方奶粉1段800g*2罐 铂金版'"); 成为sql的一部分
//用#{}预编译：select * from t_goods where title = "'' or 1 =1 or title = '【德国】爱他美婴幼儿配方奶粉1段800g*2罐 铂金版'");
            List<Goods> list = session.selectList("goods.selectByTitle", param);
            for(Goods g:list){
                System.out.println(g.getTitle() + ":" + g.getCurrentPrice());
            }
        }catch (Exception e){
            if (session!=null){
                session.rollback();
            }
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }
   }

logback.xml
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <appender name="console" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <pattern>[%thread] %d{HH:mm:ss.SSS} %-5level %logger{36} - %msg%n</pattern>
<!--            23:39:16.761 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.-->
        </encoder>
    </appender>
    <!--
        日志输出级别(优先级高到低):
        error: 错误 - 系统的故障日志
        warn: 警告 - 存在风险或使用不当的日志
        info: 一般性消息
        debug: 程序内部用于调试信息
        trace: 程序运行的跟踪信息
     -->
<!--生产环境最低级别设置info以上 开发环境最低级别设置debug以上方便调试-->
    <root level="debug">
        <appender-ref ref="console"/>
    </root>
</configuration>

MyBatis动态SQL

动态SQL

动态SQL是指根据参数数据动态组织SQL的技术

<select id="dynamicSQL" parameterType="java.util.Map" resultType="com...Goods">
    select * from t_goods
    where     <!--动态增加SQL的数据-->
        <if test="categoryId != null">
            and category_id=#{categoryId}
        </if>
        <if test="currentPrice != null">
            and current_price &lt; #{currentPrice}
        </if>
</select>

goods.xml
<select id="dynamicSQL" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods">
        select * from t_goods
        <where>    <!--根据查询条件动态增加SQL的数据-->
        <if test="categoryId != null">
            and category_id=#{categoryId}
        </if>
        <if test="currentPrice != null">
            and current_price &lt; #{currentPrice}
        </if>
        </where>
    </select>
<where></where>  where标签会动态的对子sql进行判断

MyBatis二级缓存 (可共享对象)

sql语句第一次查询 sql存储在硬盘上
优化：第一次查询的时候放在某个内存再次访问就很快 (缓存)

一级缓存默认开启，缓存范围SqlSession会话
二级缓存手动开启，属于范围Mapper Namespace

二级缓存运行规则

二级开启后默认所有查询操作均使用缓存
写操作commit提交时对该namespace缓存强制清空
配置useCache=false可以不用缓存
配置flushCache=true代表强制清空缓存

@Test
public void testLv1Cache(){
        SqlSession session = null;
        try{
            session = MyBatisUtils.openSession();
            Goods goods = session.selectOne("goods.selectById", 1603);
            session.commit();//commit提交时对该namespace缓存强制清空
            Goods goods1 = session.selectOne("goods.selectById", 1603);
            System.out.println(goods.hashCode() + ":" + goods1.hashCode());
        }catch (Exception e){
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }
    }

Goods goods = session.selectOne(“goods.selectById”, 1603);
session.commit();//commit提交时对该namespace缓存强制清空
缓存和语句距离太短资源浪费使用率不高哦

<cache eviction="LRU" flushInterval="600000" size="512" readOnly="true"/>

运行结果只出现一条sql语句结果的goods.hasCode的结果来自同一块内存区域

MyBatisTestor.java
 @Test
    public void testLv2Cache(){
        SqlSession session = null;
        try{
            session = MyBatisUtils.openSession();
            Goods goods = session.selectOne("goods.selectById", 1603);
            System.out.println(goods.hashCode());
        }catch (Exception e){
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }

        try{
            session = MyBatisUtils.openSession();
            Goods goods = session.selectOne("goods.selectById", 1603);
            System.out.println(goods.hashCode());
        }catch (Exception e){
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }
    }

08:59:50.304 [main] DEBUG goods - Cache Hit Ratio [goods]: 0.5
缓存命中率越高 证明优化越好

goods.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="goods"> <!--区分不同的工作空间-->
    <!--开启了二级缓存
        eviction是缓存的清除策略，当缓存对象数量达到上限后，自动触发对应算法对缓存对象清除
        1. LRU - 最近最久未使用：移除最长随时间不被使用的对象{893}
           LFU - 最近时间内方位最少的移除{1}
        【LRU + LFU 增强缓存速度】
        01 02 03 04 .. 0512
        14 99 83 1        893
        2. FIFO - 先进先出：按对象进入缓存的顺序来移除它们
        3. SOFT - 软引用：移除基于垃圾收集器状态和软引用规则的对象
        4. WEAK - 弱引用：更积极的移除基于垃圾收集器状态和弱引用规则的对象
        flushInterval 代表间隔多长时间自动清空缓存,单位毫秒,600000毫秒=10分钟
        size 缓存存储上限,用于保存对象或集合(1个结婚算1个对象)的数据上限
        readOnly 设置为true,代表返回只读缓存,每次从缓存取出的是缓存对象本身,这种执行效率较高
                 设置为false,代表每次取出的是缓存对象的"副本",每一次取出的对象都是不同的，这种安全性较高
    -->
    <cache eviction="LRU" flushInterval="600000" size="512" readOnly="true"/>
    <!--useCache="false"代表查询结果不放入缓存-->
    <select id="selectAll" resultType="com.imooc.mybatis.entity.Goods" useCache="false">
        select * from t_goods order by goods_id desc limit 10<!--将每一条记录包装成↑的goods对象-->
    </select>
    <select id="selectById" resultType="com.imooc.mybatis.entity.Goods">
        select * from t_goods where goods_id = #{value}
    </select>

    <select id="selectByPriceRange" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods">
            select * from t_goods
            where
                current_price between #{min} and #{max}
            order by current_price
            limit 0,#{limit}
    </select>
<!--flushCache="true"在sql执行后强制清空缓存 效果和commit相同--> 
    <select id="selectGoodsMap" resultType="java.util.LinkedHashMap" flushCache="true">
        select g.* , c.category_name from t_goods g , t_category c
        where g.category_id = c.category_id
    </select>

    <resultMap id="rmGoods" type="com.imooc.mybatis.dto.GoodsDTO">
        <id property="goods.goodsId" column="goods_id"></id>
        <result property="goods.title" column="title"></result>
        <result property="goods.originalCost" column="original_cost"></result>
        <result property="goods.currentPrice" column="current_price"></result>
        <result property="goods.discount" column="discount"></result>
        <result property="goods.isFreeDelivery" column="is_free_delivery"></result>
        <result property="goods.categoryId" column="category_id"></result>
        <result property="category.categoryId" column="category_id"/>
        <result property="category.categoryName" column="category_name"/>
        <result property="category.parentId" column="parent_id"/>
        <result property="category.categoryLevel" column="category_level"/>
        <result property="category.categoryOrder" column="category_order"/>
        <result property="test" column="test"/>
    </resultMap>
    <select id="selectGoodsDTO" resultMap="rmGoods">
        select g.* , c.*,'1' from t_goods g , t_category c
        where g.category_id = c.category_id
    </select>

    <!--flushCache="true"在sql执行后强制清空缓存 效果和commit相同--> 
    <insert id="insert" parameterType="com.imooc.mybatis.entity.Goods" flushCache="true">
        INSERT INTO t_goods(title, sub_title, original_cost, current_price, discount, is_free_delivery, category_id)
        VALUES (#{title} , #{subTitle} , #{originalCost}, #{currentPrice}, #{discount}, #{isFreeDelivery}, #{categoryId})
        <selectKey resultType="Integer" keyProperty="goodsId" order="AFTER"> <!--主键自动生成-->
        select last_insert_id()
        </selectKey>
    </insert>

    <update id="update" parameterType="com.imooc.mybatis.entity.Goods">
        UPDATE t_goods
        SET
          title = #{title},
          sub_title = #{subTitle},
          original_cost = #{originalCost},
          current_price = #{currentPrice},
          discount = #{discount},
          is_free_delivery = #{isFreeDelivery},
          category_id = #{categoryId}
        WHERE
          good_id = #{goodsId} <!--对主键进行筛选-->
    </update>

    <delete id="delete" parameterType="integer">
        delete from t_goods where goods_id = #{value}
    </delete>

    <select id="selectByTitle" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods">
        select * from t_goods where title = #{title}
            ${order}
    </select>

    <select id="dynamicSQL" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods">
        select * from t_goods
        <where>    <!--根据查询条件动态增加SQL的数据-->
        <if test="categoryId != null">
            and category_id=#{categoryId}
        </if>
        <if test="currentPrice != null">
            and current_price &lt; #{currentPrice}
        </if>
        </where>
    </select>
</mapper>

一级缓存被默认开启的随着sql开随着sql关

复习<cache eviction="LRU" flushInterval="600000" size="512" readOnly="true"/> 当中的各种

<insert id="insert" parameterType="com.imooc.mybatis.entity.Goods" flushCache="true">

MyBatis多表级联查询 (通过一个对象获得另外一个对象)

一个比较有多个学生而一个学生在同一时间只能隶属于一个班级

实体关系分析

商品和详情对象关联查询 [商品是1 详情是多详情那方要持有商品的主键]

ManyToOne对象关联查询 [collection]

mybatis-config.xml 最底下加一层
<mappers>
   <mapper resource="mappers/goods.xml"/>
   <mapper resource="mappers/goods_detail.xml"/>
</mappers>


good.xml
 <!--
        resultMap可以用于说明一对多或者多对一的映射逻辑
        id 是 resultMap属性引用标志
        type 指向One的实体(Goods)
    -->
    <resultMap id="rmGoods1" type="com.imooc.mybatis.entity.Goods">
        <!-- 映射goods对象的主键到goods_id字段 -->
        <id column="goods_id" property="goodsId"></id>
        <!--
            collection的含义是,在
            select * from t_goods limit 0,1 得到结果后,对所有Goods对象遍历得到goods_id字段值,
            并代入到goodsDetail命名空间的findByGoodsId的SQL中执行查询,
            将得到的"商品详情"集合赋值给goodsDetails List对象.
        -->
        <collection property="goodsDetails" select="goodsDetail.selectByGoodsId"
                    column="goods_id"/>
    </resultMap>
    <select id="selectOneToMany" resultMap="rmGoods1">
        select * from t_goods limit 0,10
    </select>


goods_details.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="goodsDetail">
    <select id="selectByGoodsId" parameterType="Integer"
            resultType="com.imooc.mybatis.entity.GoodsDetail">
        select * from t_goods_detail where goods_id = #{value}
    </select>
</mapper>

Goods.java 再加一个
public class Goods {
    private Integer goodsId;//商品编号
    private String title;//标题
    private String subTitle;//子标题
    private Float originalCost;//原始价格
    private Float currentPrice;//当前价格
    private Float discount;//折扣率
    private Integer isFreeDelivery;//是否包邮 ,1-包邮 0-不包邮
    private Integer categoryId;//分类编号
    private List<GoodsDetail> goodsDetails;
    Getter+Setter
}

MaBatisTestor.java 
/**
     * 测试多对一对象关联映射
     */
    @Test
    public void testOneToMany() throws Exception {
        SqlSession session = null;
        try {
            session = MyBatisUtils.openSession();
            List<Goods> list = session.selectList("goods.selectOneToMany");
            for(Goods goods:list) {
                System.out.println(goods.getTitle() + ":" + goods.getGoodsDetails().size());
            }
        } catch (Exception e) {
            throw e;
        } finally {
            MyBatisUtils.closeSession(session);
        }
    }

ManyToOne对象关联查询 [association]

商品和详情对象关联查询

goods_detail.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="goodsDetail">
    <select id="selectByGoodsId" parameterType="Integer"
            resultType="com.imooc.mybatis.entity.GoodsDetail">
        select * from t_goods_detail where goods_id = #{value}
    </select>

    <resultMap id="rmGoodsDetail" type="com.imooc.mybatis.entity.GoodsDetail">
        <id column="gd_id" property="gdId"/>
        <!--字段映射-->
        <result column="goods_id" property="goodsId"/>
        <!--从多的一方关联单的一方     在goods.xml中的goods空间 根据查询结果id带入到这个语句赋值到goods语句-->
        <association property="goods" select="goods.selectById" column="goods_id"></association>
    </resultMap>
    <select id="selectManyToOne" resultMap="rmGoodsDetail">
        select * from t_goods_detail limit 0,20
    </select>
</mapper>

MyBatisTestor.java
 @Test
    public void testManyToOne() throws Exception {
        SqlSession session = null;
        try {
            session = MyBatisUtils.openSession();
            List<GoodsDetail> list = session.selectList("goodsDetail.selectManyToOne");
            for(GoodsDetail gd:list) {
                System.out.println(gd.getGdPicUrl() + ":" + gd.getGoods().getTitle());
            }
        } catch (Exception e) {
            throw e;
        } finally {
            MyBatisUtils.closeSession(session);
        }
    }

GoodsDetail.java
public class GoodsDetail {
    private Integer gdId;
    private Integer goodsId;
    private String gdPicUrl;
    private Integer gdOrder;
    private Goods goods;
    Getter + Setter
}

分页查询的麻烦事

当前页数据查询 - select * from tab limit 0,10
总记录数查询 - select count(*) from tab
程序计算总页数、上一页页码、下一页页码

分页插件PageHelper

PageHelper使用流程

maven引入PageHelper与jsqlparser
mybatis-config.xml增加Plugin配置
代码中使用PageHelper.startPage()自动分页

pom.xml
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.1.10</version>
</dependency>
<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId> <!--最核心处理的sql语句-->
    <version>2.0</version>
</dependency>


goods.xml
<select id="selectPage" resultType="com.imooc.mybatis.entity.Goods">
        select * from t_goods where current_price &lt; 1000
</select>

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>   <!--环境配置标签-->
    <settings><!--开启驼峰命名转换 goods_id → goodsId-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <!--启动Pagehelper分页插件-->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <!--设置数据库类型-->
            <property name="helperDialect" value="mysql"/>
            <!--分页合理化-->
            <property name="reasonable" value="true"/>
        </plugin>
    </plugins>
    <environments default="dev"> <!--当环境默认数据源为dev时候使用运行的代码[不同id来切换]-->
        <environment id="dev">  <!--唯一标识-->
            <!--采用JDBC方式对数据库事务进行管理-->
            <transactionManager type="JDBC"></transactionManager>
            <!--采用连接池方式管理数据库连接-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/babytun?useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
        <environment id="prd">
            <!-- 采用JDBC方式对数据库事务进行commit/rollback -->
            <transactionManager type="JDBC"></transactionManager>
            <!--采用连接池方式管理数据库连接-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://192.168.1.155:3306/babytun?useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mappers/goods.xml"/>
        <mapper resource="mappers/goods_detail.xml"/>
    </mappers>
</configuration>

MyBatisTestor.java
@Test
    public void testSelectPage() throws Exception {
        SqlSession session = null;
        try {
            session = MyBatisUtils.openSession();
            /*startPage方法自动将下一次查询进行分页*/
            PageHelper.startPage(2,10);
            Page<Goods> page = (Page)session.selectList("goods.selectPage");
            System.out.println("总页数:" + page.getPages());
            System.out.println("总记录数:" + page.getTotal());
            System.out.println("开始行号:" + page.getStartRow());
            System.out.println("结束行号:" + page.getEndRow());
            System.out.println("当前页码:" + page.getPageNum());
            List<Goods> data = page.getResult();//当前页数据
            for (Goods g : data) {
                System.out.println(g.getTitle());
            }
            System.out.println("");
        } catch (Exception e) {
            throw e;
        } finally {
            MyBatisUtils.closeSession(session);
        }
    }

不同数据库分页的实现原理 [面试/笔试]

MySQL分页 select * from table limit 10,20; 起始行号向后取多少值
Oracle分页(三层嵌套)

Oracle分页(三层嵌套)
select t3.* from(
    select t2.*,rownum as row_num from(
        select * from table order by id asc
    ) t2 where rownum <= 20
)t3
where t2.row_num>11

SQL Server 2000

select top 3 * from table
where
  id not in
  (select top 15 id from table)

SQL Server 2012+

select * from table order by id 
   offset 4 rows fetch next 5 rows only

MyBatis配置C3P0连接池

pom.xml
<dependency>
     <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.5.4</version>
</dependency>

mybatis-config.xml
<environment id="dev">  <!--唯一标识-->
            <!--采用JDBC方式对数据库事务进行管理-->
            <transactionManager type="JDBC"></transactionManager>
            <!--采用连接池方式管理数据库连接-->
<!--            <dataSource type="POOLED">-->
            <dataSource type="com.imooc.mybatis.datasource.C3P0DataSourceFactory">
                <property name="driverClass" value="com.mysql.jdbc.Driver"/>
                <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/babytun?useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="user" value="root"/>
                <property name="password" value="root"/>
                <property name="initialPoolSize" value="5"/>
                <property name="maxPoolSize" value="20"/>
                <property name="minPoolSize" value="5"/>
                <!--...-->
            </dataSource>
        </environment>

C3P0DataSourceFactory.java
package com.imooc.mybatis.datasource;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.ibatis.datasource.unpooled.UnpooledDataSourceFactory;

/**
 * C3P0与MyBatis兼容使用的数据源工厂类
 */
public class C3P0DataSourceFactory extends UnpooledDataSourceFactory{
    public C3P0DataSourceFactory(){
        this.dataSource = new ComboPooledDataSource();
    }
}

MyBatis批处理 [利用集合保存批处理数据] [海量数据导入]

goods.xml
<!--    INSERT INTO table-->
<!--    VALUES("a","a1","a2"),("b","b1","b2"),(...)-->
<insert id="batchInsert" parameterType="java.util.List">
     INSERT INTO t_goods(title, sub_title, original_cost, current_price, discount, is_free_delivery, category_id)
     VALUES
    <foreach collection="list" item="item" index="index" separator=",">
      (#{item.title},#{item.subTitle}, #{item.originalCost}, #{item.currentPrice}, #{item.discount}, #{item.isFreeDelivery}, #{item.categoryId})
    </foreach>
<!--批量插入数据的局限：1.无法获得插入数据的id  2.批量生成的SQL太长，可能会被服务器拒绝(可以分段2次for循环)-->
</insert>

MyBatisTestor.java
/**
     * 批量插入测试
     * @throws Exception
     */
    @Test
    public void testBatchInsert() throws Exception {
        SqlSession session = null;
        try {
            long st = new Date().getTime();
            session = MyBatisUtils.openSession();
            List list = new ArrayList();
            for (int i = 0; i < 10000; i++) {
                Goods goods = new Goods();
                goods.setTitle("测试商品");
                goods.setSubTitle("测试子标题");
                goods.setOriginalCost(200f);
                goods.setCurrentPrice(100f);
                goods.setDiscount(0.5f);
                goods.setIsFreeDelivery(1);
                goods.setCategoryId(43);
                //insert()方法返回值代表本次成功插入的记录总数

                list.add(goods);
            }
            session.insert("goods.batchInsert", list);
            session.commit();//提交事务数据
            long et = new Date().getTime();
            System.out.println("执行时间:" + (et - st) + "毫秒");
//            System.out.println(goods.getGoodsId());
        } catch (Exception e) {
            if (session != null) {
                session.rollback();//回滚事务
            }
            throw e;
        } finally {
            MyBatisUtils.closeSession(session);
        }
    }

====================================================================
 /**
     * 10000次数据插入对比测试用例
     * @throws Exception
     */
    @Test
    public void testInsert1() throws Exception {
        SqlSession session = null;
        try{
            long st = new Date().getTime();
            session = MyBatisUtils.openSession();
            List list = new ArrayList();
            for(int i = 0 ; i < 10000 ; i++) {
                Goods goods = new Goods();
                goods.setTitle("测试商品");
                goods.setSubTitle("测试子标题");
                goods.setOriginalCost(200f);
                goods.setCurrentPrice(100f);
                goods.setDiscount(0.5f);
                goods.setIsFreeDelivery(1);
                goods.setCategoryId(43);
                //insert()方法返回值代表本次成功插入的记录总数
                //每循环一次 插入一次
                session.insert("goods.insert" , goods);
            }

            session.commit();//提交事务数据
            long et = new Date().getTime();
            System.out.println("执行时间:" + (et-st) + "毫秒");
//            System.out.println(goods.getGoodsId());
        }catch (Exception e){
            if(session != null){
                session.rollback();//回滚事务
            }
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }
    }

MyBatis常用注解 (注解适合小型敏捷项目 XML适合大型协作项目)

注解	对应XML	说明
@Insert	< insert >	更新SQL
@Update	< update >	更新SQL
@Delete	< delete >	删除SQL
@Select	< select >	查询SQL
@Param	–	参数映射
@Results	< resultMap >	结果映射
@Result	< id > < result >	字段映射

把XML中的写入代码中

dao.GoodsDAO.java
package com.imooc.mybatis.dao;

import com.imooc.mybatis.dto.GoodsDTO;
import com.imooc.mybatis.entity.Goods;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface GoodsDAO {
    @Select("select * from t_goods where current_price between #{min} and #{max} order by current_price limit 0,#{limit}")
    public List<Goods> selectByPriceRange(@Param("min") Float min, @Param("max") Float max, @Param("limit") Integer limit);

    @Insert("INSERT INTO t_goods(title, sub_title, original_cost, current_price, discount, is_free_delivery, category_id) VALUES (#{title} , #{subTitle} , #{originalCost}, #{currentPrice}, #{discount}, #{isFreeDelivery}, #{categoryId})")
    //<selectKey> befor是true则在这之前执行 keyProperty主键 resultType返回主键类型
    @SelectKey(statement = "select last_insert_id()" , before = false , keyProperty = "goodsId" , resultType = Integer.class)
    public int insert(Goods goods);

    @Select({"select * from t_goods"})
    //<resultMap>
    @Results({ //select实行完以下结果 按照以下规则并赋值給GoodsDTO对象
            //<id>
            @Result(column = "goods_id", property = "goodsId", id = true),
            //<result>
            @Result(column = "title", property = "title"),
            @Result(column = "current_price", property = "currentPrice"),
    })
    public List<GoodsDTO> selectAll();

}

dto.GoodsDTO
package com.imooc.mybatis.dto;

public class GoodsDTO {
    private Integer goodsId;//商品编号
    private String title;//标题
    private Float currentPrice;//当前价格

    public Integer getGoodsId() {
        return goodsId;
    }

    public void setGoodsId(Integer goodsId) {
        this.goodsId = goodsId;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public Float getCurrentPrice() {
        return currentPrice;
    }

    public void setCurrentPrice(Float currentPrice) {
        this.currentPrice = currentPrice;
    }
}

entity.Goods.java
public class Goods {
    private Integer goodsId;//商品编号
    private String title;//标题
    private String subTitle;//子标题
    private Float originalCost;//原始价格
    private Float currentPrice;//当前价格
    private Float discount;//折扣率
    private Integer isFreeDelivery;//是否包邮 ,1-包邮 0-不包邮
    private Integer categoryId;//分类编号
    Getter + Setter
}

resources.logback.xml
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
   <appender name="console" class="ch.qos.logback.core.ConsoleAppender">
       <encoder>
           <pattern>[%thread] %d{HH:mm:ss.SSS} %-5level %logger{36} - %msg%n</pattern>
       </encoder>
   </appender>

    <!--
        日志输出级别(优先级高到低):
        error: 错误 - 系统的故障日志
        warn: 警告 - 存在风险或使用不当的日志
        info: 一般性消息
        debug: 程序内部用于调试信息
        trace: 程序运行的跟踪信息
     -->
    <root level="debug">
        <appender-ref ref="console"/>
    </root>
</configuration>

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>
    <settings>
        <!-- goods_id ==> goodsId 驼峰命名转换 -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <!--设置默认指向的数据库-->
    <environments default="dev">
        <!--配置环境，不同的环境不同的id名字-->
        <environment id="dev">
            <!-- 采用JDBC方式对数据库事务进行commit/rollback -->
            <transactionManager type="JDBC"></transactionManager>
            <!--采用连接池方式管理数据库连接-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/babytun?useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
<!--        <mapper class="com.imooc.mybatis.dao.GoodsDAO"/>-->
        <package name="com.imooc.mybatis.dao"/>
    </mappers>
</configuration>

test.java.com.imooc.mybatis.MyBatisTestor
package com.imooc.mybatis;

import com.imooc.mybatis.dao.GoodsDAO;
import com.imooc.mybatis.dto.GoodsDTO;
import com.imooc.mybatis.entity.Goods;
import com.imooc.mybatis.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

//JUNIT单元测试类
public class MyBatisTestor {

    @Test
    public void testSelectByPriceRange() throws Exception {
        SqlSession session = null;
        try{
            session = MyBatisUtils.openSession();
            GoodsDAO goodsDAO = session.getMapper(GoodsDAO.class); //包含注解的接口
            List<Goods> list = goodsDAO.selectByPriceRange(100f, 500f, 20);
            System.out.println(list.size());
        }catch (Exception e){
            throw e;
        } finally {
            MyBatisUtils.closeSession(session);
        }
    }

    /**
     * 新增数据
     * @throws Exception
     */
    @Test
    public void testInsert() throws Exception {
        SqlSession session = null;
        try{
            session = MyBatisUtils.openSession();
            Goods goods = new Goods();
            goods.setTitle("测试商品");
            goods.setSubTitle("测试子标题");
            goods.setOriginalCost(200f);
            goods.setCurrentPrice(100f);
            goods.setDiscount(0.5f);
            goods.setIsFreeDelivery(1);
            goods.setCategoryId(43);
            GoodsDAO goodsDAO = session.getMapper(GoodsDAO.class);
            //insert()方法返回值代表本次成功插入的记录总数
            int num = goodsDAO.insert(goods);
            session.commit();//提交事务数据
            System.out.println(goods.getGoodsId()); //最新的数据回填給goodsId
        }catch (Exception e){
            if(session != null){
                session.rollback();//回滚事务
            }
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }
    }

    @Test
    public void testSelectAll() throws Exception {
        SqlSession session = null;
        try{
            session = MyBatisUtils.openSession();
            GoodsDAO goodsDAO = session.getMapper(GoodsDAO.class);
            List<GoodsDTO> list = goodsDAO.selectAll();
            System.out.println(list.size());
        }catch (Exception e){
            throw e;
        } finally {
            MyBatisUtils.closeSession(session);
        }
    }
}

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.imooc</groupId>
    <artifactId>mybatis-annotation</artifactId>
    <version>1.0-SNAPSHOT</version>
    <repositories>
        <repository>
            <id>aliyun</id>
            <name>aliyun</name>
            <url>https://maven.aliyun.com/repository/public</url>
        </repository>
    </repositories>
    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>1.2.3</version>
        </dependency>
    </dependencies>
</project>

LOADING...