投稿
  1. 极客之音首页
  2. 技术随笔

MyBatis进阶（二）

小半 • • 技术随笔 • 阅读 223

导读：本篇文章讲解 MyBatis进阶（二），希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com

目录

OneToMany对象关联查询

ManyToOne对象关联查询

PageHelper分页插件

MyBatis整合C3P0连接池

MyBatis批处理

Mybatis注解开发方式

Goods：

package com.imooc.mybatis.entity;

import java.util.List;

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 void setCategoryId(Integer categoryId) {
        this.categoryId = categoryId;
    }

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

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

GoodsDetail：

package com.imooc.mybatis.entity;

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;
    }
}

OneToMany对象关联查询

Goods（一） –> GoodsDetail（多） 

在Goods（一）中增加属性：    private List<GoodsDetail> goodsDetails;

  • resultMap可用于说明一对多或者多对一的映射逻辑
  • id 是resultMap属性引用的标志
  • type 指向One的实体(Goods) 

goods.xml

     <resultMap id="rmGoods1" type="com.imooc.mybatis.entity.Goods">
        <!-- 映射goods对象的主键到goods_id字段 -->
        <id column="goods_id" property="goodsId"></id>
        <!--
            collection专门用来描述Goods对象中的List集合的数据从哪来
            property指向Goods中List的属性名
            select指向命名空间为“goodsDetail”中的selectByGoodsId
            column代表查询结果的关联列
        -->
        <collection property="goodsDetails"
                    select="goodsDetail.selectByGoodsId"
                    column="goods_id"/>
    </resultMap>
    <select id="selectOneToMany" resultMap="rmGoods1">
        select * from t_goods limit 0,10
    </select>

        collection的含义是,在 “select * from t_goods limit 0,1 ”得到结果后,对所有Goods对象遍历得到goods_id字段值,并代入到goodsDetail命名空间的findByGoodsId的SQL中执行查询,将得到的”商品详情”集合赋值给goodsDetails List对象。

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>

</mapper>
    /**
     * 一对多对象关联查询
     * @throws Exception
     */
    @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对象关联查询

GoodsDetail（多） –>  Goods（一）

在GoodsDetail（多）中增加属性：    private Goods goods;

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">

    <resultMap id="rmGoodsDetail" type="com.imooc.mybatis.entity.GoodsDetail">
        <id column="gd_id" property="gdId"/>
        <!--
            goods_id被使用到goods关联上会导致原有goods_detail中的属性goodsId没有赋上值
            解决方法是添加手动映射
        -->
        <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 limit 0,20
    </select>

</mapper>

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="selectById" parameterType="Integer" resultType="com.imooc.mybatis.entity.Goods">
        select * from t_goods where  goods_id = #{value}
    </select>

</mapper>
 /**
     * 测试多对一对象关联映射
     */
    @Test
    public void testManyToOne() throws Exception {
        SqlSession session = null;
        try {
            session = MyBatisUtils.openSession();
            List<GoodsDetail> list = session.selectList("goodsDetail.selectManyToOne");
            for(GoodsDetail gd:list) {
                // gd.getGoods()为goods对象
                System.out.println(gd.getGdPicUrl() + ":" + gd.getGoods().getTitle());
            }
        } catch (Exception e) {
            throw e;
        } finally {
            MyBatisUtils.closeSession(session);
        }
    }

PageHelper分页插件

使用流程

maven引入PageHelper与jsqlparser

        <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>

mybatis-config.xml增加Plugin配置

<plugins>
<!--设置分页插件-->
    <plugin interceptor="com.github.pagehelper.PageInterceptor">
        <property name="helperDialect" value="mysql"/>
        <property name="reasonable" value="true"/>
    </plugin>
</plugins>

代码中使用PageHelper.startPage()自动分页

goods.xml

    <select id="selectPage" resultType="com.imooc.mybatis.entity.Goods">
        select * from t_goods where current_price &lt; 1000
    </select>
   /**
     * PageHelper分页查询
     */
    @Test
    public void testSelectPage() throws Exception {
        SqlSession session = null;
        try {
            session = MyBatisUtils.openSession();
            /*startPage方法会自动将下一次查询进行分页*/
            PageHelper.startPage(2,10);  //参数1：第几页  参数2：每页几行
            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);
        }
    }

详细使用：如何使用分页插件 (pagehelper.github.io)

MyBatis整合C3P0连接池

引入依赖

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

 数据源工厂类：

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();
    }
}

        C3P0作为连接池，假如要让MyBatis对他进行支持的话，需要额外的扩展一个类，需要创建一个全新的包，名为DataSource，用于保存数据源，在这里创建一个新的类，类名为C3P0DataSourceFactory，创建完成之后必须要继承一个父类,名为UnpooledDataSourceFactory，并且增加一个构造函数，含义是在这个类初始化的时候。让数据员源由c3p0进行创建。

         在mybatis-config.xml当中，将<dataSource type=”POOLED”>更改为<dataSource type=”com.imooc.mybatis.datasource.C3P0DataSourceFactory”>即可，其中不同连接池中的属性名是不一样的。

<!--采用连接池方式管理数据库连接-->
<!--<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>

MyBatis批处理

当传进来的参数为list时，需用到foreach标签

批量增加和删除：

   <!--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>
    </insert>
    <!--in (1901,1902)-->
    <delete id="batchDelete" parameterType="java.util.List">
        DELETE FROM t_goods WHERE goods_id in
        <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </delete>

Mybatis注解开发方式

MyBatis进阶（二）

        采用注解开发是不用编写mapper.xml文件的，取而代之需要创建一个名为dao的包，在这个包中创建一系列的接口，利用接口加注解替代原有的xml文件。

配置文件中进行包扫描：

 <mappers>
        <!--<mapper class="com.imooc.mybatis.dao.GoodsDAO"/>-->
        <package name="com.imooc.mybatis.dao"/>
</mappers>

 查询：

public interface GoodsDAO {

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

}

        session会根据GoodsDAO里的配置信息来动态地生成GoodsDAO的实现类，也就是说这里不用额外的开发直接利用goodsDAO里的方法进行相应的增删改查即可。

    @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);
        }
    }

添加：

public interface GoodsDAO {

    @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>  获取新插入的自增主键的编号
    @SelectKey(statement = "select last_insert_id()" , before = false , keyProperty = "goodsId" , resultType = Integer.class)
    public int insert(Goods goods);

}

测试类：

/**
     * 新增数据
     * @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());
        }catch (Exception e){
            if(session != null){
                session.rollback();//回滚事务
            }
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }
    }

实现结果映射：

public interface GoodsDAO {

    @Select("select * from t_goods")
    //<resultMap>
    @Results({
            //<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();
}

测试类：

    @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);

        }
    }

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/99475.html

赞 (0)
小半的头像小半
0 0

相关推荐

极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！