Mybatis学习笔记

package Test;

import Test.pojo.User;
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;

/**
 * @author 杨世博
 */
public class MybatisDemo {
    public static void main(String[] args) throws IOException {
//        1、加载Mybatis的核心配置文件,获取SqlSessionFactory;
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

//        2、获取SqlSession对象,用它来执行sql;
        SqlSession sqlSession = sqlSessionFactory.openSession();

//        3、执行Sql语句
        List<User> users = sqlSession.selectList("test.selectAll");

        System.out.println(users);

//        4、释放资源
        sqlSession.close();
    }
}

package Test;

import Test.mapper.AccountMapper;
import Test.pojo.User;
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;

/**
 * @author 杨世博
 *
 * Mapper代理开发
 */
public class MybatisDemo2 {
    public static void main(String[] args) throws IOException {
//        1、加载Mybatis的核心配置文件,获取SqlSessionFactory;
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

//        2、获取SqlSession对象,用它来执行sql;
        SqlSession sqlSession = sqlSessionFactory.openSession();

//        3、执行Sql语句
//        List<User> users = sqlSession.selectList("test.selectAll");

//        3、1 获取 UserMapper 接口的代理对象
        AccountMapper accountMapper = sqlSession.getMapper(AccountMapper.class);
        List<User> users = accountMapper.selectAll();

        System.out.println(users);

//        4、释放资源
        sqlSession.close();
    }
}

 

<?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:名称空间
-->


<mapper namespace="test.mapper.BrandMapper">

<!--
    数据库表中的字段名称  和   实体类的属性名称 不一样,则不能自动封装数据
    * 起别名:对不一样的列名起别名,让别名和实体类的属性名一样
        * 缺点:每次查询都要定义一次别名
            * sql片段
                * 缺点:不灵活
    * resultMap;
        1、定义<resultMap>标签
        2、在<select>标签中,使用resultMap属性替换 resultType属性
-->


<!--
        id:唯一标识
        type:映射类型,支持别名
-->
    <resultMap id="brandResultMap" type="brand">
<!--
            id:完成主键字段的映射
                column:表的列名
                property:实体类的属性名
            result:完成一般字段的映射
                column:表的列名
                property:实体类的属性名
-->
        <result column="brand_name" property="brandName" />
        <result column="company_name" property="companyName" />
    </resultMap>
    
    <select id="selectAll" resultMap="brandResultMap">
        select *
        from tb_brand;
    </select>


<!--
        * 参数占位符
            1、#{}:会将其替换为?,为了防止SQL注入
            2、${}:拼sql。会存在sql注入问题
            3、使用时机:
                * 参数传递时:#{}
                * 表名或者列名不固定的情况下:${}
        * 参数类型:
            parameterType:可以省略
        * 特殊字符的处理:
            1、转义字符:
            2、CDATA区:
-->
    <select id="selectById" resultMap="brandResultMap">
        select *
        from tb_brand where id = #{id};
    </select>


    <!--    起别名-->
<!--    <select id="selectAll" resultType="Brand">-->
<!--        select id, brand_name as brandName, company_name as companyName, ordered, description, status-->
<!--        from tb_brand;-->
<!--    </select>-->

<!--    普通-->
<!--    <select id="selectAll" resultType="Brand">-->
<!--        select * from tb_brand;-->
<!--    </select>-->
</mapper>

package test.mapper;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import test.pojo.Brand;

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

/**
 * @author 杨世博
 */
public interface BrandMapper {

    /**
     * 查询Brand对象
     * @return 返回查到的对象
     */
    List<Brand> selectAll();

    /**
     * 查看详细:根据Id查询 得到Brand对象
     * @param id 信息
     * @return 返回查询到的对象
     */
    @Select(value = "SELECT * FROM tb_brand WHERE id = #{id}")
    Brand selectById(@Param("id") int id);

    /**
     * 条件查询
     * * 参数接收
     *      散装参数:如果方法中有多个参数,需要使用@Param("SQL参数占位符名称")
     * @param status
     * @param companyName
     * @param brandName
     * @return
     */
    List<Brand> selectByCondition(@Param("status") int status, @Param("companyName") String companyName, @Param("brandName") String brandName);

    /**
     * 条件查询
     * * 参数接收
     *      对象参数:对象的属性名称要和参数占位符名称一致
     * @param brand
     * @return
     */
    List<Brand> selectByCondition(Brand brand);

    /**
     * 条件查询
     * * 参数接收
     *      map集合参数
     * @param map
     * @return
     */
    List<Brand> selectByCondition(Map map);

    /**
     * 单条件动态查询Brand对象
     * @param brand 传入随机参数
     * @return 返回多个 Brand 对象
     */
    List<Brand> selectByConditionSingle(Brand brand);

    /**
     * 添加 Brand 对象
     * @param brand 对象的信息
     */
    void add(Brand brand);

    /**
     * 修改 Brand对象
     * @param brand
     * @return
     */
    int update(Brand brand);

    /**
     * 通过Id删除数据库中的Brand
     * @param id
     */
    void deleteById(Integer id);

    /**
     * 批量删除
     * @param ids 要删除的id
     */
    void deleteByIds(@Param("ids")int[] ids);
}

<?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:名称空间
-->


<mapper namespace="test.mapper.BrandMapper">

<!--
    数据库表中的字段名称  和   实体类的属性名称 不一样,则不能自动封装数据
    * 起别名:对不一样的列名起别名,让别名和实体类的属性名一样
        * 缺点:每次查询都要定义一次别名
            * sql片段
                * 缺点:不灵活
    * resultMap;
        1、定义<resultMap>标签
        2、在<select>标签中,使用resultMap属性替换 resultType属性
-->


<!--
        id:唯一标识
        type:映射类型,支持别名
-->
    <resultMap id="brandResultMap" type="brand">
<!--
            id:完成主键字段的映射
                column:表的列名
                property:实体类的属性名
            result:完成一般字段的映射
                column:表的列名
                property:实体类的属性名
-->
        <result column="brand_name" property="brandName" />
        <result column="company_name" property="companyName" />
    </resultMap>


    <select id="selectAll" resultMap="brandResultMap">
        select *
        from tb_brand;
    </select>


<!--
        * 参数占位符
            1、#{}:会将其替换为?,为了防止SQL注入
            2、${}:拼sql。会存在sql注入问题
            3、使用时机:
                * 参数传递时:#{}
                * 表名或者列名不固定的情况下:${}
        * 参数类型:
            parameterType:可以省略
        * 特殊字符的处理:
            1、转义字符:
            2、CDATA区:
-->
<!--    <select id="selectById" resultMap="brandResultMap">-->
<!--        select *-->
<!--        from tb_brand where id = #{id};-->
<!--    </select>-->

<!--    条件查询-->
<!--    <select id="selectByCondition" resultMap="brandResultMap">-->
<!--        select *-->
<!--        from tb_brand-->
<!--        where status = #{status}-->
<!--            and company_name like #{companyName}-->
<!--            and brand_name like #{brandName}-->
<!--    </select>-->

<!--
        动态条件查询
            *if:条件判断
                *test:逻辑表达式
            *问题:
                * 恒等式
                * <where> 替换 where 关键字
-->
    <select id="selectByCondition" resultMap="brandResultMap">
        select *
        from tb_brand
        <where>
            <if test="status != null">
                and status = #{status}
            </if>
            <if test="companyName != null and company != '' ">
                and company_name like #{companyName}
            </if>
            <if test="brandName != null and brandName != '' ">
                and brand_name like #{brandName}
            </if>
        </where>
    </select>

<!--
        单条件的动态条件查询
-->
<!--    <select id="selectByConditionSingle" resultMap="brandResultMap">-->
<!--        select *-->
<!--        from tb_brand-->
<!--        where-->
<!--        <choose>&lt;!&ndash; 相当于 switch &ndash;&gt;-->
<!--            <when test="status != null">-->
<!--                status = #{status}-->
<!--            </when>&lt;!&ndash; 相当于 case &ndash;&gt;-->
<!--            <when test="companyName != null and company != '' ">-->
<!--                company_name like #{companyName}-->
<!--            </when>-->
<!--            <when test="brandName != null and brandName != '' ">-->
<!--                brand_name like #{brandName}-->
<!--            </when>-->
<!--            <otherwise>-->
<!--                1 = 1-->
<!--            </otherwise>-->
<!--        </choose>-->
<!--    </select>-->

    <select id="selectByConditionSingle" resultMap="brandResultMap">
        select *
        from tb_brand
        <where>
            <choose><!-- 相当于 switch -->
                <when test="status != null">
                    status = #{status}
                </when><!-- 相当于 case -->
                <when test="companyName != null and company != '' ">
                    company_name like #{companyName}
                </when>
                <when test="brandName != null and brandName != '' ">
                    brand_name like #{brandName}
                </when>
            </choose>
        </where>
    </select>

    <!--    起别名-->
<!--    <select id="selectAll" resultType="Brand">-->
<!--        select id, brand_name as brandName, company_name as companyName, ordered, description, status-->
<!--        from tb_brand;-->
<!--    </select>-->

<!--    普通-->
<!--    <select id="selectAll" resultType="Brand">-->
<!--        select * from tb_brand;-->
<!--    </select>-->


<!--    返回添加的主键-->
    <insert id="add" useGeneratedKeys="true" keyProperty="id">
        insert into tb_brand (brand_name, company_name, ordered, description, status)
        values (#{brandName}, #{companyName}, #{ordered}, #{description}, #{status});
    </insert>


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

<!--    删除一个数据-->
    <delete id="deleteById">
        delete from tb_brand where id = #{id}
    </delete>

<!--    动态删除多个数据-->
<!--
        mybatis 会将数组参数,封装为一个Map集合。
            * 默认:array = 数组
            * 使用@Param注解改变map集合的默认key的名称
-->
    <delete id="deleteByIds">
        delete from tb_brand where id in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>
</mapper>
package test;

import test.mapper.BrandMapper;
import test.pojo.Brand;
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.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MybatisTest {

    @Test
    public void testSelectAll() throws IOException {
//        1、获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

//        2、获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

//        3、获取Mapper接口的代理对象
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

//        4、执行方法
        List<Brand> brands = mapper.selectAll();
        System.out.println(brands);

//        5、释放资源
        sqlSession.close();
    }


    @Test
    public void testSelectById() throws IOException {
//        就收参数
        int id = 1;


//        1、获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

//        2、获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

//        3、获取Mapper接口的代理对象
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

//        4、执行方法
        Brand brand = mapper.selectById(id);
        System.out.println(brand);

//        5、释放资源
        sqlSession.close();
    }


    @Test
    public void testSelectByCondition() throws IOException {
//        就收参数
        int status = 1;
        String companyName = "华为";
        String brandName = "华为";

//        处理参数
        companyName = "%" + companyName + "%";
        brandName = "%" + brandName + "%";

//        封装对象
//        Brand brand = new Brand();
//        brand.setStatus(status);
//        brand.setBrandName(brandName);
//        brand.setCompanyName(companyName);

        Map map = new HashMap();
        map.put("status",status);
        map.put("brandName",brandName);
        map.put("companyName",companyName);

//        1、获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

//        2、获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

//        3、获取Mapper接口的代理对象
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

//        4、执行方法

//        散装参数查询
//        List<Brand> brands = mapper.selectByCondition(status, companyName, brandName);

//        对象参数查询
//        List<Brand> brands = mapper.selectByCondition(brand);

//        map集合查询
        List<Brand> brands = mapper.selectByCondition(map);
        System.out.println(brands);

//        5、释放资源
        sqlSession.close();
    }


    @Test
    public void testSelectByConditionSingle() throws IOException {
//        就收参数
        int status = 1;
        String companyName = "华为";
        String brandName = "华为";

//        处理参数
        companyName = "%" + companyName + "%";
        brandName = "%" + brandName + "%";

//        封装对象
        Brand brand = new Brand();
//        brand.setStatus(status);
//        brand.setBrandName(brandName);
//        brand.setCompanyName(companyName);

//        1、获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

//        2、获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

//        3、获取Mapper接口的代理对象
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

//        4、执行方法
        List<Brand> brands = mapper.selectByConditionSingle(brand);
        System.out.println(brands);

//        5、释放资源
        sqlSession.close();
    }


    @Test
    public void testAdd() throws IOException {
//        就收参数
        int status = 1;
        String companyName = "鸭梨";
        String brandName = "鸭梨牌山寨手机";
        String description = "美国有苹果,中国有鸭梨";
        int ordered = 2;

//        封装对象
        Brand brand = new Brand();
        brand.setStatus(status);
        brand.setBrandName(brandName);
        brand.setCompanyName(companyName);
        brand.setDescription(description);
        brand.setOrdered(ordered);

//        1、获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

//        2、获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

//        3、获取Mapper接口的代理对象
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

//        4、执行方法
        mapper.add(brand);

//        提交事务
//        sqlSession.commit();

        List<Brand> brands = mapper.selectAll();
        System.out.println(brands);

//        5、释放资源
        sqlSession.close();
    }


    @Test
    public void testAdd2() throws IOException {
//        就收参数
        int status = 1;
        String companyName = "鸭梨";
        String brandName = "鸭梨牌山寨手机";
        String description = "美国有苹果,中国有鸭梨";
        int ordered = 2;

//        封装对象
        Brand brand = new Brand();
        brand.setStatus(status);
        brand.setBrandName(brandName);
        brand.setCompanyName(companyName);
        brand.setDescription(description);
        brand.setOrdered(ordered);

//        1、获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

//        2、获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

//        3、获取Mapper接口的代理对象
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

//        4、执行方法
        mapper.add(brand);
        Integer id = brand.getId();
        System.out.println(id);


//        5、释放资源
        sqlSession.close();
    }


    @Test
    public void testUpdate() throws IOException {
//        就收参数
        int status = 1;
        String companyName = "菠萝";
        String brandName = "菠萝牌山寨手机";
        String description = "美国有苹果,中国有菠萝";
        int ordered = 2;
        int id = 8;

//        封装对象
        Brand brand = new Brand();
        brand.setStatus(status);
        brand.setBrandName(brandName);
        brand.setCompanyName(companyName);
//        brand.setDescription(description);
        brand.setOrdered(ordered);
        brand.setId(id);

//        1、获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

//        2、获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

//        3、获取Mapper接口的代理对象
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

//        4、执行方法
        int count = mapper.update(brand);
        System.out.println(count);


//        5、释放资源
        sqlSession.close();
    }


    @Test
    public void testDeleteById() throws IOException {
//        就收参数

        int id = 8;

//        1、获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

//        2、获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

//        3、获取Mapper接口的代理对象
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

//        4、执行方法
        mapper.deleteById(id);

//        5、释放资源
        sqlSession.close();
    }


    @Test
    public void testDeleteByIds() throws IOException {
//        就收参数

        int[] ids = {4,9,10};

//        1、获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

//        2、获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

//        3、获取Mapper接口的代理对象
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

//        4、执行方法
        mapper.deleteByIds(ids);

//        5、释放资源
        sqlSession.close();
    }
}


  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

kukudeYSB

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值