【笔记】多条件操作数据库

测试类

import demo2.BrandMapper;
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 pojo.Brand;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Array;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class BrandMapperTest {
    @Test
    public void SelectAll() throws Exception {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = build.openSession(true);
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
//        查询表中所有数据，并且封装到一个集合当中
        List<Brand> all = mapper.findAll();

        for (Brand brand : all) {
            System.out.println(brand);
        }
    }

    @Test
    public void SelectByid() throws Exception {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = build.openSession(true);
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
//         单条件查询，返回一个实体类对象
       Brand brand = mapper.selectByid(3);
        System.out.println(brand);
    }

    @Test
    public void FindByCondition() throws Exception {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = build.openSession(true);
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
//      多条件查询
        String brandName="华为";
        String companyName="华为技术有限公司";
        int status=1;
        Brand byCondition = mapper.findByCondition(brandName, companyName, status);
        System.out.println(byCondition);
    }


    @Test
    public void FindByCond() throws Exception {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = build.openSession(true);
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
//      多条件查询，将数据转入实体类中进行查询
        String brandName="华为";
        String companyName="华为技术有限公司";
        int status=1;
        Brand brand = new Brand();
        brand.setStatus(status);
        brand.setCompanyName(companyName);
        brand.setBrandName(brandName);
        List<Brand> byCondition = mapper.findByCond(brand);
        System.out.println(byCondition);
    }

    @Test
    public void ADD() throws Exception {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = build.openSession(true);
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
//      添加数据，添加一整个实体类对象数据
        Brand brand = new Brand();
        brand.setBrandName("波导");
        brand.setCompanyName("波导手机");
        brand.setDescription("牛逼");
        brand.setOrdered(20);
        brand.setStatus(0);
        mapper.add(brand);
    }

    @Test
    public void UpDate() throws Exception {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = build.openSession(true);
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
//     修改数据
        Brand brand = new Brand();
        brand.setId(4);
        brand.setBrandName("苹果");
        brand.setCompanyName("苹果手机");

        mapper.update(brand);
    }

    @Test
    public void DeleteByids() throws Exception {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = build.openSession(true);
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
//      传入集合，一次性删除多条数据
        List<Integer> ids = Arrays.asList(1, 2, 3);

        mapper.deleteByids(ids);
    }




}

接口

package demo2;

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

import java.util.List;

public interface BrandMapper {
//    注解开发，主要用在一些简单的sql语句
    @Select("select * from tb_brand")
    @ResultMap("rm")
    List<Brand> findAll();

    Brand selectByid(int id);
//     注解定义的是传过来的参数的名称，是后面sql语句#{}里面要写的名称
    Brand findByCondition(@Param("brandName")String brandName , @Param("companyName")String companyName,@Param("status")int status);

    List<Brand> findByCond(Brand brand);

    void add(Brand brand);

    void deleteByid(Brand brand);

    void update(Brand brand);

    void deleteByids(@Param("ids") List<Integer> 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="demo2.BrandMapper">
    <!--定义名字，因为在查询的时候，需要获取值，当数据库的列名和实体类中的成员变量的名称不一致时-->
    <!--就会导致输出是封装不了，导致数据为null-->
    <resultMap id="rm" type="pojo.Brand">
        <result column="brand_name" property="brandName"/>
        <result column="company_name" property="companyName"/>
    </resultMap>
    <!--配置的方法，通过接口和映射可以使用这个sql语句操作数据库-->
    <!--resultMap指定结构输出的类型-->
    <select id="findAll" resultMap="rm">
        select * from tb_brand ;
    </select>
    <select id="selectByid" resultMap="rm">

        select * from tb_brand where id = #{id};
    </select>

    <select id="findByCondition" resultMap="rm">
        select * from  tb_brand where  status = #{status} and brand_name like concat("%",#{brandName},"%") and  company_name like concat("%",#{companyName},"%")
    </select>
    <select id="findByCond" resultMap="rm">
        select * from  tb_brand
        <!--where标记可以判断and是否要出现，保证sql语句没有问题-->
        <where>
            <!--test就是判断语句-->
        <if test="status != null"> status = #{status}</if>
        <if test="brandName != null and brandName != '' "> and brand_name like concat("%",#{brandName},"%")</if>
        <if test="companyName != null and companyName != '' "> and  company_name like concat("%",#{companyName},"%")</if>
         </where>
    </select>
    <insert id="add" useGeneratedKeys="true" keyProperty="id">
        insert into tb_brand() values (null,#{brandName},#{companyName},#{ordered},#{description},#{status});
    </insert>

    <delete id="deleteByid">
        delete from  tb_brand where id=#{id}
    </delete>

    <update id="update">
        update tb_brand
        <!--使用set标签，就可以在里面用上if标签做判断-->
        <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="deleteByids">
        delete from tb_brand where id in
        <!--foreach就是变量传过来的集合-->
        <!--collection：如果在接口中有注解定义了参数名称，那么collection后面的值就应该和定义的参数名称一致-->
        <!--如果没有定义名称那么传来的是数组就写Array，如果是集合就写list-->
        <!--item：定义传来的集合或者数组中数据的名称，应该和sql语句中判断所用到的列名一致-->
        <!--separator：分隔符-->
        <!--open：开始插入-->
        <!--close：结束插入-->
        <foreach collection="ids" item="id" separator="," open="(" close=")" >
            #{id}
        </foreach>
    </delete>
</mapper>

核心配置文件

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

    <typeAliases>
        <package name="com.itheima.pojo"/>
    </typeAliases>
    
    <!--
    environments：配置数据库连接环境信息。可以配置多个environment，通过default属性切换不同的environment
    -->
    <environments default="test2">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <!--数据库连接信息-->
                <!--固定写法-->
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <!--指定数据库-->
                <property name="url" value="jdbc:mysql:///db1?useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="1234"/>
            </dataSource>
        </environment>

        <environment id="test">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <!--数据库连接信息-->
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql:///db4?useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="1234"/>
            </dataSource>
        </environment>
        <environment id="test2">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <!--数据库连接信息-->
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql:///mybatis?useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="1234"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!--加载sql映射文件-->
       <!-- <mapper resource="com/itheima/mapper/UserMapper.xml"/>-->
        <!--<mapper resource="demo2/StuMapper.xml"/>-->
        <!--Mapper代理方式-->
        <!--指定文件夹下所有配置文件都可以读取-->
        <package name="demo2"/>

    </mappers>



</configuration>

实体类

package pojo;

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

    public Brand() {
    }

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