从JDBC连接池到Mybatis（Mybatis进阶篇二——Mapper映射文件）

六：Mybatis的映射文件进阶：

动态SQL标签：

JDBC配置文件：

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jtdb-small
user=root
password=123456

mybatis核心配置文件：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--数据源环境-->
<configuration>
    <!-- 通过properties标签，加载外部properties标签 -->
    <properties resource="jdbc.properties"></properties>
    <!-- 自定义别名 -->
    <typeAliases>
        <typeAlias type="com.mybatisss.pojo.Item" alias="Item"></typeAlias>
    </typeAliases>
    <!-- default="developement"：指定默认的环境名称-->
    <environments default="developement">
        <!-- id="developement"：指定当前环境的名称-->
        <environment id="developement">
            <!-- type="JDBC"：指事务管理类型是JDBC-->
            <transactionManager type="JDBC"></transactionManager>
            <!-- type="POOLED"：指定当前数据源类型是连接池 -->
            <dataSource type="POOLED">
                <!-- 数据源配置的基本参数 -->
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${user}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--加载映射文件-->
    <mappers>
        <mapper resource="mapper/ItemMapper.xml"></mapper>
    </mappers>
</configuration>

mapper映射文件：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatisss.dao.ItemMapper">
    <select id="findByCondition" resultType="Item" parameterType="Item">
        SELECT * FROM tb_item
        <!-- where标签可以避免 and 语句报错 -->
        <where>
            <!-- if标签用于判断，可以动态拼接SQL语句 ；
                test属性中 参数为实体类属性 -->
           <if test="id != null and id != ''" >
              AND id = #{id}
            </if>
            <if test="title != null and title != ''" >
                AND title = #{title}
            </if>
            <if test="sellPoint != null and sellPoint != ''" >
                AND sell_point = #{sellPoint}
            </if>
        </where>
    </select>

    <select id="findByIds" resultType="Item" parameterType="list">
        SELECT * FROM tb_item
        <where>
            <!-- foreach循环：
                collection：映射的接口入参类型，若为list集合则为list，若为数组则为array
                open：表示循环拼接语句的开始内容
                close：表示循环拼接语句的结束内容
                separator：表示拼循环接语句中间的分割符
                item：表示参数别名，当前映射的接口入参为list，这里别名为ids，
                        故在foreach标签中的参数应为ids,否则报出：
                        Parameter 'XXX' not found. Available parameters are [collection, list]
                        的异常
             -->
                <foreach collection="list" open="id in (" close=")" separator="," item="ids">
                    #{ids}
                </foreach>
            </where>
    </select>

    <!-- SQL片段抽取
        抽取重复使用的SQL片段，进行SQL语句拼接
        SQL片段中id为唯一标识，对应使用拼接的include标签中的refid
        可以理解为：sql id = include refid
     -->
    <sql id="condition">
        <if test="id != null and id != ''" >
            AND id = #{id}
        </if>
    </sql>
    <select id="findById" resultType="Item" parameterType="Long">
        SELECT * FROM tb_item 
        <where>
            <include refid="condition"></include>
        </where>
    </select>

    <!-- 补充：
        #{}：表示占位符参数，可以理解为 PreparedStatement
        ${}：表示SQL拼接，可以理解为：Statement
    -->
</mapper>

Item实体类：

package com.mybatisss.pojo;

import java.math.BigDecimal;
import java.util.Date;

public class Item {

    private Long id;			//商品编号
    private String title;		//标题
    private String sellPoint;	//买点
    private BigDecimal price;	//价格
    private Integer num;		//数量
    private String barcode;		//条形码
    private String image;		//图片
    private Long cid;			//外键，详情ID
    private Integer status;		//状态 1:正常，2下架
    private Date created;		//创建时间
    private Date updated;		//修改时间

    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    public String getSellPoint() {
        return sellPoint;
    }
    public void setSellPoint(String sellPoint) {
        this.sellPoint = sellPoint;
    }
    public BigDecimal getPrice() {
        return price;
    }
    public void setPrice(BigDecimal price) {
        this.price = price;
    }
    public Integer getNum() {
        return num;
    }
    public void setNum(Integer num) {
        this.num = num;
    }
    public String getBarcode() {
        return barcode;
    }
    public void setBarcode(String barcode) {
        this.barcode = barcode;
    }
    public String getImage() {
        return image;
    }
    public void setImage(String image) {
        this.image = image;
    }
    public Long getCid() {
        return cid;
    }
    public void setCid(Long cid) {
        this.cid = cid;
    }
    public Integer getStatus() {
        return status;
    }
    public void setStatus(Integer status) {
        this.status = status;
    }
    public Date getCreated() {
        return created;
    }
    public void setCreated(Date created) {
        this.created = created;
    }
    public Date getUpdated() {
        return updated;
    }
    public void setUpdated(Date updated) {
        this.updated = updated;
    }

    @Override
    public String toString() {
        return "Item [id=" + id + ", title=" + title + ", sell_point=" + sellPoint
                + ", price=" + price + ", num=" + num + ", barcode=" + barcode
                + ", image=" + image + ", cid=" + cid + ", status=" + status
                + ", created=" + created + ", updated=" + updated + "]";
    }
}

mapper接口：

package com.mybatisss.dao;

import com.mybatisss.pojo.Item;

import java.util.List;

public interface ItemMapper {

    public List<Item> findByCondition(Item item);

    public List<Item> findByIds(List<Long> list);

    public List<Item> findById(Long id);

}

测试Demo1：

 

package com.mybatisss.test;

import com.mybatisss.dao.ItemMapper;
import com.mybatisss.pojo.Item;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class ServiceDemo02 {
    public static void main(String[] args) throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        ItemMapper mapper = sqlSession.getMapper(ItemMapper.class);
        //虚拟Item实体
        Item item = new Item();
        item.setId(875724L);
        item.setTitle("诺基亚(NOKIA) 1050 (RM-908) 蓝色 移动联通2G手机");
        //调用对应实现方法
        List<Item> list = mapper.findByCondition(item);
        //打印结果
        for (Item i : list) {
            System.out.println(i);
        }
        sqlSession.close();
    }
}

测试Demo2：

package com.mybatisss.test;

import com.mybatisss.dao.ItemMapper;
import com.mybatisss.pojo.Item;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class ServiceDemo03 {
    public static void main(String[] args) throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        ItemMapper mapper = sqlSession.getMapper(ItemMapper.class);
        //虚拟List
        List<Long> l = new ArrayList<Long>();
        l.add(875724L);
        l.add(983253L);
        //调用对应实现方法
        List<Item> list = mapper.findByIds(l);
        //打印结果
        for (Item i : list) {
            System.out.println(i);
        }
        sqlSession.close();
    }
}

测试Demo3：

package com.mybatisss.test;

import com.mybatisss.dao.ItemMapper;
import com.mybatisss.pojo.Item;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class ServiceDemo04 {
    public static void main(String[] args) throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        ItemMapper mapper = sqlSession.getMapper(ItemMapper.class);
        //调用对应实现方法
        List<Item> list = mapper.findById(875724L);
        //打印结果
        for (Item i : list) {
            System.out.println(i);
        }
        sqlSession.close();
    }
}

本文只是个人学习内容整理的笔记，如有侵权，即联系870013269@qq.com删除