Java框架MyBatis学习历程（6）——对象关联查询，分页插件PageHelper，配置C3P0连接池

1、实体关系分析

2、OneToMany对象关联查询

Goods实体类：

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;//商品对应详情页数据
    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 String getSubTitle() {
        return subTitle;
    }

    public void setSubTitle(String subTitle) {
        this.subTitle = subTitle;
    }

    public Float getOriginalCost() {
        return originalCost;
    }

    public void setOriginalCost(Float originalCost) {
        this.originalCost = originalCost;
    }

    public Float getCurrentPrice() {
        return currentPrice;
    }

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

    public Float getDiscount() {
        return discount;
    }

    public void setDiscount(Float discount) {
        this.discount = discount;
    }

    public Integer getIsFreeDelivery() {
        return isFreeDelivery;
    }

    public void setIsFreeDelivery(Integer isFreeDelivery) {
        this.isFreeDelivery = isFreeDelivery;
    }

    public Integer getCategoryId() {
        return categoryId;
    }

    public List<GoodsDetail> getGoodsDetails() {
        return goodsDetails;
    }

    public void setGoodsDetails(List<GoodsDetail> goodsDetails) {
        this.goodsDetails = goodsDetails;
    }

    public void setCategoryId(Integer categoryId) {
        this.categoryId = categoryId;
    }
}

goods_detail.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 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.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命名空间的selectByGoodsId的SQL中执行查询，
            将得到的"商品详情"集合赋值给goodsDetails List对象。
        -->
        <collection property="goodsDetails" select="goodsDetail.selectByGoodsId" column="goods_id"/>
    </resultMap>
    <select id="selectOneToMany" resultMap="rmGoods1" >
        select * from t_goods limit 0,1;
    </select>
</mapper>

mybatis-config.xml:

    <mappers>
        <mapper resource="mappers/goods.xml"/>
        <mapper resource="mappers/goods_detail.xml"/>
    </mappers>
</configuration>

测试代码：

    @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) {
            e.printStackTrace();
        } finally {
            MyBatisUtils.closeSession(session);
        }
    }

3、ManyToOne对象关联查询

GoodsDetail对象实体：

public class GoodsDetail {
    private Integer gdId;
    private Integer goodsId;
    private String gdPicUrl;
    private Integer gdOrder;
    private Goods goods;
    public Integer getGdId() {
        return gdId;
    }

    public void setGdId(Integer gdId) {
        this.gdId = gdId;
    }

    public Integer getGoodsId() {
        return goodsId;
    }

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

    public String getGdPicUrl() {
        return gdPicUrl;
    }

    public void setGdPicUrl(String gdPicUrl) {
        this.gdPicUrl = gdPicUrl;
    }

    public Integer getGdOrder() {
        return gdOrder;
    }

    public void setGdOrder(Integer gdOrder) {
        this.gdOrder = gdOrder;
    }

    public Goods getGoods() {
        return goods;
    }

    public void setGoods(Goods goods) {
        this.goods = goods;
    }
}

goods_detail.xml:

    <resultMap id="rmGoodsDetail" type="com.imooc.mybatis.entity.GoodsDetail">
        <id column="gd_id" property="gdId"/>
        <result column="goods_id" property="goodsId"/>
        <association property="goods" select="goods.selectById" column="goods_id"></association>
    </resultMap>
    <select id="selectManyToOne" resultMap="rmGoodsDetail">
        select * from t_goods_detail where goods_id = 741 limit 0,10
    </select>

测试代码：

    /**
     * 测试多对一对象关联映射
     */
    @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) {
            e.printStackTrace();
        } finally {
            MyBatisUtils.closeSession(session);
        }
    }

4、分页查询

使用案例：
（1）导入pagehelper和jsqlparser依赖（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>
            <version>2.0</version>
        </dependency>

(2)进行属性配置（mybatis-config.xml）：

    </settings>
    <!-- 启用Pagehelper分页插件 -->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <!-- 设置数据库类型 -->
            <property name="helperDialect" value="mysql"/>
            <!-- 分页合理化 -->
            <property name="reasonable" value="true"></property>
        </plugin>
    </plugins>

    <!-- 设置默认指向的数据库-->
    <environments default="dev">

(3)goods.xml

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

(4)测试代码：

    @Test
    /**
     * PageHelper分页查询
     */
    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);
        }
    }

（5）结果：

5、不同数据库分页的实现原理

6、配置C3P0连接池

（1）导入依赖（pom.xml）：

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

（2）编写数据源工厂类：

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

/**
 * @author: Mr.Du
 * @description: C3P0连接池
 * @date: 2021/04/25 17:20
 */
public class C3P0DataSourceFactory extends UnpooledDataSourceFactory {
    public C3P0DataSourceFactory() {
        this.dataSource = new ComboPooledDataSource();
    }
}

（3）更改配置信息（mybatis-config,xml）:

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

(4)完成配置，随便用测试用例进行测试确定是否配置成功。