如何使用mybatis连接数据库及曾删改查的操作(基于maven开发)

配置文件

pox.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>mybatis-demo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>


    <dependencies>

        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.5</version>
        </dependency>
        <!-- mysql 驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
            <scope>test</scope>
        </dependency>

        <!-- 添加slf4j日志api -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.20</version>
        </dependency>
        <!-- 添加logback-classic依赖 -->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>1.2.3</version>
        </dependency>
        <!-- 添加logback-core依赖 -->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-core</artifactId>
            <version>1.2.3</version>
        </dependency>

    </dependencies>

</project>

//核心配置文件

<?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.mlt.pojo"/>
    </typeAliases>
    <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://127.0.0.1:3306/mybatis?useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="1234"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <package name= "com.mlt.mapper"/>
    </mappers>
</configuration>

//Mapper 配置文件(注意要和Brandmapper接口在同一目录下)

<?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.mlt.mapper.BrandMapper">

<!--数据库字段名称，和实体类的属性名称不一样，则不能自动封装-->
<!--    <select id="selectAll" resultType="brand">-->
<!--    select *from tb_brand;-->
<!--    </select>-->

<!--  resultMap  -->
    <resultMap id="brandresultMap" type="brand">
        <result column="brand_name" property="brandName"/>
        <result column="company_name" property="companyName"/>
    </resultMap>



    <!-- sql片段-->
<!--    <sql id="brand_column">-->
<!--        id ,brand_name as brandName,company_name as companyName,ordered,description,status-->
<!--    </sql>-->
    <select id="selectAll" resultMap="brandresultMap">
        select
          *
            from tb_brand;
    </select>
    <select id="selectByid" resultMap="brandresultMap">
        select * from tb_brand where CD 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 2=2
        <where>
        <if test="status !=null">
         and  status = #{status}
        </if>
        <if test="companyName !=null and companyName !=''">
          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>  <!-- 相当于switch-->
                <when test="status !=null"><!-- case-->
                    status = #{status}
                </when>
                <when test="companyName !=null and companyName !=''"><!-- case-->
                    company_name like #{companyName}
                </when>
                <when test="brandName !=null and brandName !=''"><!-- case-->
                     brand_name like #{brandName}
                </when>
            </choose>
        </where>
    </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="delete">
        delete from  tb_brand where id=#{id}
    </delete>
<!--    批量删除
    *array=数组
    * 使用注解
-->
    <delete id="deleteBylds">
        delete from  tb_brand where id
        in
         <foreach collection="array" item="id" separator="," open="(" close=")">
                #{id}
            </foreach>
    </delete>
</mapper>

//brand类

package com.mlt.pojo;

/**
 * 品牌
 *
 * alt + 鼠标左键：整列编辑
 *
 * 在实体类中，基本数据类型建议使用其对应的包装类型
 */

public class Brand {
    // id 主键
    private Integer id;
    // 品牌名称
    private String brandName;
    // 企业名称
    private String companyName;
    // 排序字段
    private Integer ordered;
    // 描述信息
    private String description;
    // 状态：0：禁用  1：启用
    private Integer 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 +
                '}';
    }
}

//BrandMapper代理的接口(以后实现方法，利于扩展)

package com.mlt.mapper;

import com.mlt.pojo.Brand;
import org.apache.ibatis.annotations.Param;


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


public interface BrandMapper {

    List<Brand> selectAll();
    /**
     * 查询
     */
    Brand selectByid(int id);

    /**
     * 条件查询
     * 1.散装参数：如果方法有多个参数，需要使用@param包装
     * @param status
     * @param companyName
     * @param brandName
     * @return
     */
    List<Brand> selectByCondition(@Param("status") int status,
                                  @Param("companyName") String companyName,
                                  @Param("brandName") String brandName);


    List<Brand> selectByCondition(Brand brand);

    List<Brand> selectByCondition(Map map);

    List<Brand> selectByConditionSingle(Brand brand);


    /**
     * 添加
     *
     */
    void add(Brand brand);
    /**
     *
     * 修改
     */
    int update(Brand brand);
    /**
     * 删除
     */
    int delete(int id);

    /**
     * 批量删除
     */
    void deleteBylds( int[] ids);
}

//测试代码()

package com.mlt.test;

import com.mlt.mapper.BrandMapper;
import com.mlt.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);
        SqlSession sqlSession =sqlSessionFactory.openSession();

        //2.获取mapper接口代理对象
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

        List<Brand> barns = mapper.selectAll();

        System.out.println(barns);

        //3.关闭

        sqlSession.close();
    }



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

        //2.获取mapper接口代理对象
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

        Brand brand = mapper.selectByid(id);
        System.out.println(brand);

        //3.关闭

        sqlSession.close();
    }


    @Test
    public void testSelectByCondition() throws IOException {
        //1.获取sqlSessionFactory
      //  int status=1;
        String companyName = "华为";
    //    String brandName = "华为";

        //处理参数

        companyName = "%"+companyName+"%";
     //   brandName = "%"+brandName+"%";

        Map<String,Object> map =new HashMap<>();
     //   map.put("status",status);
        map.put("companyName",companyName);
   //     map.put("brandName",brandName);
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession =sqlSessionFactory.openSession();

        //2.获取mapper接口代理对象
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

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

        //3.关闭

        sqlSession.close();
    }



    @Test
    public void testSelectByConditionSinggo() throws IOException {
        //1.获取sqlSessionFactory
        int status=1;
        String companyName = "华为";
         String brandName = "华为";

        //处理参数

        companyName = "%"+companyName+"%";
       brandName = "%"+brandName+"%";

        Brand brand = new Brand();
       // brand.setStatus(status);
      //  brand.setCompanyName(companyName);
      //  brand.setBrandName(brandName);
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession =sqlSessionFactory.openSession();

        //2.获取mapper接口代理对象
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

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

        //3.关闭

        sqlSession.close();
    }

    /**
     * 添加数据
     *
     */
    @Test
    public void testAdd() throws IOException {
        //1.获取sqlSessionFactory
        int status=1;
        String companyName = "华为shouji";
        String brandName = "华为";
        String description="手机中的战斗机";
        int ordered=100;

        Brand brand = new Brand();
         brand.setStatus(status);
          brand.setCompanyName(companyName);
          brand.setBrandName(brandName);
          brand.setDescription(description);
          brand.setOrdered(ordered);
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession =sqlSessionFactory.openSession(true);

        //2.获取mapper接口代理对象
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

         mapper.add(brand);

        //提交事务
        sqlSession.commit();
               //3.关闭

        sqlSession.close();
    }

    @Test
    public void testAdd2() throws IOException {
        //1.获取sqlSessionFactory
        int status=1;
        String companyName = "华为shouji";
        String brandName = "华为";
        String description="手机中的战斗机";
        int ordered=100;

        Brand brand = new Brand();
        brand.setStatus(status);
        brand.setCompanyName(companyName);
        brand.setBrandName(brandName);
        brand.setDescription(description);
        brand.setOrdered(ordered);
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession =sqlSessionFactory.openSession(true);

        //2.获取mapper接口代理对象
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

        mapper.add(brand);

        //提交事务
        sqlSession.commit();
        //3.关闭
        System.out.println(brand.getId());
        sqlSession.close();
    }

    /**
     * 修改
     * @throws IOException
     */
    @Test
    public void testUpdate() throws IOException {
        //1.获取sqlSessionFactory
        int status=1;
        String companyName = "sadadsa";
        String brandName = "华dasdasdasdada为";
        String description="手dsadasdasda机中的战斗机";
        int ordered=100;
        int id =8;

        Brand brand = new Brand();
        brand.setStatus(status);
        brand.setCompanyName(companyName);
        brand.setBrandName(brandName);
        brand.setDescription(description);
        brand.setOrdered(ordered);
        brand.setId(id);
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession =sqlSessionFactory.openSession(true);

        //2.获取mapper接口代理对象
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

        int update = mapper.update(brand);
        System.out.println(update);
        //提交事务
        sqlSession.commit();
        //3.关闭
        System.out.println(brand.getId());
        sqlSession.close();
    }

    /**
     * 修改
     * @throws IOException
     */
    @Test
    public void testUpdate2() throws IOException {
        //1.获取sqlSessionFactory
        int status=0;
        String companyName = "sadadsa";
        String brandName = "华dasdasdasdada为";
        String description="手dsadasdasda机中的战斗机";
        int ordered=10000000;
        int id =6;

        Brand brand = new Brand();
  //     brand.setStatus(status);
//        brand.setCompanyName(companyName);
//        brand.setBrandName(brandName);
        brand.setDescription(description);
  //      brand.setOrdered(ordered);
        brand.setId(id);
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession =sqlSessionFactory.openSession(true);

        //2.获取mapper接口代理对象
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

        int update = mapper.update(brand);
        System.out.println(update);
        //提交事务
        sqlSession.commit();
        //3.关闭
        System.out.println(brand.getId());
        sqlSession.close();
    }


    /**
     * 根据id删除
     * @throws IOException
     */
    @Test
    public void testdeleteByid() throws IOException {


        int id =6;


        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession =sqlSessionFactory.openSession(true);

        //2.获取mapper接口代理对象
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

        int update = mapper.delete(id);
        System.out.println(update);
        //提交事务
        sqlSession.commit();
        //3.关闭

        sqlSession.close();
    }


    /**
     * 删除
     * @throws IOException
     */
    @Test
    public void testdeleteByids() throws IOException {


        int[] ids ={5,6,7,8};


        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession =sqlSessionFactory.openSession(true);

        //2.获取mapper接口代理对象
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

        mapper.deleteBylds(ids);

        //提交事务
        sqlSession.commit();
        //3.关闭

        sqlSession.close();
    }
}