Mybatis（三、综合练习

一、项目准备工作

1，创建数据库

drop table if exists tb_brand;
create table tb_brand(
    id int primary key auto_increment,
    brand_name varchar(20),
    company_name varchar(20),
    ordered int comment '排序',
    description varchar(100),
    status int
);

insert into tb_brand(brand_name, company_name, ordered, description, status)
values ('三只松鼠','三只松鼠有限公司',5,'好吃不上火',0),
       ('华为','我是华为华为华为',100,'买我不如买一加',1),
       ('腾讯','充钱充钱请充钱',50,'天游不是天美',0);
select * from tb_brand;

2，创建实体类

package com.brrbaii.pojo;

public class Brand {
    private Integer id;
    private String brandName;
    private String companyName;
    private Integer ordered;
    private String description;
    private Integer status;

    public Brand(Integer id, String brandName, String companyName, Integer ordered, String description, Integer status) {
        this.id = id;
        this.brandName = brandName;
        this.companyName = companyName;
        this.ordered = ordered;
        this.description = description;
        this.status = status;
    }

    public Brand(){}

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getBrandName() {
        return brandName;
    }

    public void setBrandName(String brandName) {
        this.brandName = brandName;
    }

    public String getCompanyName() {
        return companyName;
    }

    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    public Integer getOrdered() {
        return ordered;
    }

    public void setOrdered(Integer ordered) {
        this.ordered = ordered;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }

    @Override
    public String toString() {
        return "Brand{" +
                "id=" + id +
                ", brandName='" + brandName + '\'' +
                ", companyName='" + companyName + '\'' +
                ", ordered=" + ordered +
                ", description='" + description + '\'' +
                ", status=" + status +
                '}';
    }
}

3，创建测试类

4，创建SqlsessionUtils工具栏

package com.brrbaii.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.InputStream;

public class SqlSessionUtils {
    private static SqlSessionFactory sqlSessionFactory;
    static String resource = "mybatis-config.xml";
    //初始化静态代码块
    static {
        try{
            //1.加载mybatis的核心配置文件,获取SqlSessionFactory
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
    //获取SqlSession对象，用它执行sql
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }

}

5，创建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="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
           <!--数据库连接信息-->
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/你要使用的数据库"/>
                <property name="username" value="你的数据库账号"/>
                <property name="password" value="你的数据库密码"/>
            </dataSource>
        </environment>
    </environments>
    <!--加载SQL映射文件-->
    <mappers>
        //这里放你后续的映射文件
        <mapper resource="com/brrbaii/mapper/UserMapper.xml"/>
    </mappers>
</configuration>

6，安装MybatisX插件

MybatisX 是一款基于 IDEA 的快速开发插件，为效率而生。

安装方法：打开 IDEA，进入 File -> Settings -> Plugins -> Browse Repositories，输mybatis搜索并安装。

二、工作流程

1），建立BrandMapper接口，添加方法，添加如下代码

注意：

1，方法名和返回值要对应BrandMapper.xml的ID和resultType

2，brand是通过在Mybatis-config.xml通过package配置了别名

2），建立BrandMapper.xml，添加sql语句 

注意：namespace的路径为BrandMapper接口的位置

此时我们发现，数据库的字段名和实体类的属性名不一致时，数据不会自动封装

我们可以采取上述图片的方法为不一样的列名添加别名，或者采取SQL片段

SQl片段：

再或者使用resultMap：

 3），在测试类中进行测试

package com.brrbaii.test;

import com.brrbaii.mapper.BrandMapper;
import com.brrbaii.pojo.Brand;
import com.brrbaii.utils.SqlSessionUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class MybatisTest {
    @Test
    public void selectAllTest(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        final List<Brand> brands = brandMapper.selectAll();
        for (Brand brand:brands){
            System.out.println(brand);
        }
    }

}

  测试成功！

三、完善后续所有方法

1，修改

2，插入

 2.2，插入并自动封装新生成的ID 

3、删除

 注意：增删改方法需要提交事务，觉得手动提交麻烦可以在SqlsessionFactory里设置自动

 

附上完整代码

xml文件：brandMapper.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">
<!--
    namespace：名称空间
    resulType：返回类型
    id       ：当前SQL的唯一标识
-->
<mapper namespace="com.brrbaii.mapper.BrandMapper">


    <!--
        id:唯一标识
        type:映射的类型，支持别名
    -->
    <resultMap id="brandResultMap" type="brand">
        <result column="brand_name" property="brandName" />
        <result column="company_name" property="companyName" />
    </resultMap>
    <!--
        插入并封装ID
    -->
    <insert id="addBrandAndGetId" useGeneratedKeys="true" keyColumn="id">
        insert into tb_brand
            (brand_name, company_name, ordered, description, status)
        values (#{brandName},#{companyName},#{ordered},#{description},#{status})
    </insert>

    <!--
        动态修改查询
    -->
    <update id="updateDynamic">
        update tb_brand
        <set>
            <if test="brandName!=null">
                brand_name = #{brandName},
            </if>
            <if test="companyName!=null">
                company_name = #{companyName},
            </if>
            <if test="ordered!=null">
                ordered = #{ordered},
            </if>
            <if test="description!=null">
                description = #{description},
            </if>
            <if test="status!=null">
                status = #{status},
            </if>
        </set>
        where id = #{id}
    </update>

    <!--
        id:主键字段映射
            column:表的列名
            property:实体类的别名
        result:一般字段属性
            column:表的列名
            property:实体类的别名
    -->

    <!--
        多条件查询
    -->
    <select id="selectByCondition" resultMap="brandResultMap">
        select *
        from tb_brand
        <where>
            <if test="status!=null">
                and status = #{status}
            </if>
            <if test="companyName != null and companyName != ''">
                and company_name like concat('%',#{companyName},'%')
            </if>
            <if test="brandName != null and brandName != ''">
                and brand_name like concat('%',#{brandName},'%')
            </if>
        </where>
    </select>

    <!--
        单条件查询
    -->
    <select id="selectBySingleCondition" resultMap="brandResultMap">
        select *
        from tb_brand
        <where>
            <choose>
                <when test="status!=null">
                    status = #{status}
                </when>
                <when test="companyName != null and companyName != ''">
                    company_name like concat('%',#{companyName},'%')
                </when>
                <when test="brandName != null and brandName != ''">
                    brand_name like concat('%',#{brandName},'%')
                </when>
                <otherwise>
                    1 = 1
                </otherwise>
            </choose>
        </where>
    </select>

    <delete id="deleteByArr">
        delete from tb_brand
        where id in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>

</mapper>

java文件：brandMapper.java（包含动态sql方法和使用注解实现方法）

package com.brrbaii.mapper;

import com.brrbaii.pojo.Brand;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

public interface BrandMapper {
    /**
     * 删除多个
     */
    void deleteByArr(@Param("ids") int[] ids);

    /**
     * 通过ID删除
     */
    @Delete("delete from tb_brand where id = #{id}")
    void deleteById(int id);
    /**
     * 查询全部
     * @return：Brand对象集合
     */
    @Select("select *,brand_name as BrandName," +
            "company_name as companyName from tb_brand")
    List<Brand> selectAll();
    /**
     * 根据ID查询对象
     */
    @Select("select *,brand_name as BrandName," +
            "company_name as companyName from tb_brand " +
            "where id = #{id}"
    )
    Brand selectById(int id);
    /**
     * 根据条件查询数据，这里练习三个方法
     *  1，散装参数查询（若有多个参数，需要用@Param指明SQL占位符#{}内对应的参数）
     *  2，类对象参数查询
     *  3，Map集合参数查询
     */
    List<Brand> selectByCondition(@Param("status")int status,
                                  @Param("companyName")String companyName,
                                  @Param("brandName") String brandName);

    List<Brand> selectByCondition(Brand brand);
    List<Brand> selectByCondition(Map brandMap);
    /**
     * 动态单条件查询对象
     */
    List<Brand> selectBySingleCondition(Map brandMap);
    /**
     * 插入
     */
    @Insert("insert into tb_brand(brand_name, company_name, ordered, description, status) " +
            "values(#{brandName},#{companyName},#{ordered},#{description},#{status})")
    void addBrand(Map brandMap);

    /**
     * 插入并返回ID
     */
    void addBrandAndGetId(Map brandMap);

    /**
     * 修改
     */
    @Update("update tb_brand set brand_name = #{brandName}, company_name = #{companyName}, " +
            "ordered = #{ordered}, description = #{description}, status = #{status}" +
            " where id = #{id}")
    void update(Brand brand);

    /**
     * 动态修改
     */
    void updateDynamic(Brand brand);



}