MyBatis、Maven框架（一整套完整练习＜1＞）

环境和要求说明：

IDEA编译器、MySQL数据库、MyBatis、Maven框架，MyBatisX插件。
采用模拟接收数据，不涉及Web前端。

数据库：

-- 创建数据库
create database mybatis_demo;

use mybatis_demo;

-- 删除smbms_address表
drop table if exists smbms_address;

-- 创建smbms_address表
CREATE TABLE `smbms_address` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `contact` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '联系人姓名',
  `addressDesc` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '收货地址明细',
  `postCode` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '邮编',
  `tel` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '联系人电话',
  `createdBy` bigint(20) DEFAULT NULL COMMENT '创建者',
  `creationDate` datetime DEFAULT NULL COMMENT '创建时间',
  `modifyBy` bigint(20) DEFAULT NULL COMMENT '修改者',
  `modifyDate` datetime DEFAULT NULL COMMENT '修改时间',
  `userId` bigint(20) DEFAULT NULL COMMENT '用户ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- 删除smbms_address表
drop table if exists smbms_bill;

-- 创建smbms_bill表
CREATE TABLE `smbms_bill` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `billCode` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '账单编码',
  `productName` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '商品名称',
  `productDesc` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '商品描述',
  `productUnit` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '商品单位',
  `productCount` decimal(20,2) DEFAULT NULL COMMENT '商品数量',
  `totalPrice` decimal(20,2) DEFAULT NULL COMMENT '商品总额',
  `isPayment` int(10) DEFAULT NULL COMMENT '是否支付（1：未支付 2：已支付）',
  `createdBy` bigint(20) DEFAULT NULL COMMENT '创建者（userId）',
  `creationDate` datetime DEFAULT NULL COMMENT '创建时间',
  `modifyBy` bigint(20) DEFAULT NULL COMMENT '更新者（userId）',
  `modifyDate` datetime DEFAULT NULL COMMENT '更新时间',
  `providerId` int(20) DEFAULT NULL COMMENT '供应商ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- 删除smbms_provider表
drop table if exists smbms_provider;

-- 创建smbms_provider表
CREATE TABLE `smbms_provider` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `proCode` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '供应商编码',
  `proName` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '供应商名称',
  `proDesc` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '供应商详细描述',
  `proContact` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '供应商联系人',
  `proPhone` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '联系电话',
  `proAddress` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '地址',
  `proFax` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '传真',
  `createdBy` bigint(20) DEFAULT NULL COMMENT '创建者（userId）',
  `creationDate` datetime DEFAULT NULL COMMENT '创建时间',
  `modifyDate` datetime DEFAULT NULL COMMENT '更新时间',
  `modifyBy` bigint(20) DEFAULT NULL COMMENT '更新者（userId）',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- 删除smbms_role表
drop table if exists smbms_role;

-- 创建smbms_role表
CREATE TABLE `smbms_role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `roleCode` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '角色编码',
  `roleName` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '角色名称',
  `createdBy` bigint(20) DEFAULT NULL COMMENT '创建者',
  `creationDate` datetime DEFAULT NULL COMMENT '创建时间',
  `modifyBy` bigint(20) DEFAULT NULL COMMENT '修改者',
  `modifyDate` datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- 删除smbms_user表
drop table if exists smbms_user;

-- 创建smbms_user表
CREATE TABLE `smbms_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `userCode` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '用户编码',
  `userName` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '用户名称',
  `userPassword` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '用户密码',
  `gender` int(10) DEFAULT NULL COMMENT '性别（1:女、 2:男）',
  `birthday` date DEFAULT NULL COMMENT '出生日期',
  `phone` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '手机',
  `address` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '地址',
  `userRole` int(10) DEFAULT NULL COMMENT '用户角色（取自角色表-角色id）',
  `createdBy` bigint(20) DEFAULT NULL COMMENT '创建者（userId）',
  `creationDate` datetime DEFAULT NULL COMMENT '创建时间',
  `modifyBy` bigint(20) DEFAULT NULL COMMENT '更新者（userId）',
  `modifyDate` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

整体结构：

准备配置文件：

（1）依赖文件《pom.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>
        <!--导入lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.4</version>
            <scope>provided</scope>
        </dependency>
        <!--mysql依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.5</version>
        </dependency>
        <!--junit-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
            <scope>test</scope>
        </dependency>
        <!--log4j-->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-webmvc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>5.2.8.RELEASE</version>
        </dependency>
    </dependencies>
</project>

（2）数据库配置文件和映射：
db.properties：存放数据库的信息《方便以后处理》

driver:com.mysql.jdbc.Driver
url:jdbc:mysql:///mybatis_demo?useSSL=false
username:root
password:LXY5201314LYH

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核心配置文件-->
<configuration>
    <!--引入外部配置文件-->
    <properties resource="db.properties"/>
    <!--配置日志-->
    <settings>
        <!--标准日志实现-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <!--驼峰命名打开-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <!--别名-->
    <typeAliases>
        <package name="com.zp.pojo"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <!--事务管理-->
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <!-- 数据库连接信息 -->
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!-- 加载sql映射文件 -->
        <!--<mapper resource="UserMapper.xml"/>--><!--用于单个加入-->
        <package name="com.zp.Mapper"/>
    </mappers>
</configuration>

实体类

（1）Bill

package com.zp.pojo;
import lombok.Data;

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


@Data
public class Bill {
    private Integer id;   //id
    private String billCode; //账单编码
    private String productName; //商品名称
    private String productDesc; //商品描述
    private String productUnit; //商品单位
    private BigDecimal productCount; //商品数量
    private BigDecimal totalPrice; //总金额
    private Integer isPayment; //是否支付
    private Integer providerId; //供应商ID
    private Integer createdBy; //创建者
    private Date creationDate; //创建时间
    private Integer modifyBy; //更新者
    private Date modifyDate;//更新时间
    private String providerName;//供应商名称

    public Integer getId() {
        return id;
    }

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

    public String getBillCode() {
        return billCode;
    }

    public void setBillCode(String billCode) {
        this.billCode = billCode;
    }

    public String getProductName() {
        return productName;
    }

    public void setProductName(String productName) {
        this.productName = productName;
    }

    public String getProductDesc() {
        return productDesc;
    }

    public void setProductDesc(String productDesc) {
        this.productDesc = productDesc;
    }

    public String getProductUnit() {
        return productUnit;
    }

    public void setProductUnit(String productUnit) {
        this.productUnit = productUnit;
    }

    public BigDecimal getProductCount() {
        return productCount;
    }

    public void setProductCount(BigDecimal productCount) {
        this.productCount = productCount;
    }

    public BigDecimal getTotalPrice() {
        return totalPrice;
    }

    public void setTotalPrice(BigDecimal totalPrice) {
        this.totalPrice = totalPrice;
    }

    public Integer getIsPayment() {
        return isPayment;
    }

    public void setIsPayment(Integer isPayment) {
        this.isPayment = isPayment;
    }

    public Integer getProviderId() {
        return providerId;
    }

    public void setProviderId(Integer providerId) {
        this.providerId = providerId;
    }

    public Integer getCreatedBy() {
        return createdBy;
    }

    public void setCreatedBy(Integer createdBy) {
        this.createdBy = createdBy;
    }

    public Date getCreationDate() {
        return creationDate;
    }

    public void setCreationDate(Date creationDate) {
        this.creationDate = creationDate;
    }

    public Integer getModifyBy() {
        return modifyBy;
    }

    public void setModifyBy(Integer modifyBy) {
        this.modifyBy = modifyBy;
    }

    public Date getModifyDate() {
        return modifyDate;
    }

    public void setModifyDate(Date modifyDate) {
        this.modifyDate = modifyDate;
    }

    public String getProviderName() {
        return providerName;
    }

    public void setProviderName(String providerName) {
        this.providerName = providerName;
    }

    @Override
    public String toString() {
        return "Bill{" +
                "id=" + id +
                ", billCode='" + billCode + '\'' +
                ", productName='" + productName + '\'' +
                ", productDesc='" + productDesc + '\'' +
                ", productUnit='" + productUnit + '\'' +
                ", productCount=" + productCount +
                ", totalPrice=" + totalPrice +
                ", isPayment=" + isPayment +
                ", providerId=" + providerId +
                ", createdBy=" + createdBy +
                ", creationDate=" + creationDate +
                ", modifyBy=" + modifyBy +
                ", modifyDate=" + modifyDate +
                ", providerName='" + providerName + '\'' +
                '}';
    }
}

（2）Provider

package com.zp.pojo;

import lombok.Data;

import java.util.Date;

@Data
public class Provider {
    private Integer id;   //id
    private String proCode; //供应商编码
    private String proName; //供应商名称
    private String proDesc; //供应商描述
    private String proContact; //供应商联系人
    private String proPhone; //供应商电话
    private String proAddress; //供应商地址
    private String proFax; //供应商传真
    private Integer createdBy; //创建者
    private Date creationDate; //创建时间
    private Integer modifyBy; //更新者
    private Date modifyDate;//更新时间

    public Integer getId() {
        return id;
    }

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

    public String getProCode() {
        return proCode;
    }

    public void setProCode(String proCode) {
        this.proCode = proCode;
    }

    public String getProName() {
        return proName;
    }

    public void setProName(String proName) {
        this.proName = proName;
    }

    public String getProDesc() {
        return proDesc;
    }

    public void setProDesc(String proDesc) {
        this.proDesc = proDesc;
    }

    public String getProContact() {
        return proContact;
    }

    public void setProContact(String proContact) {
        this.proContact = proContact;
    }

    public String getProPhone() {
        return proPhone;
    }

    public void setProPhone(String proPhone) {
        this.proPhone = proPhone;
    }

    public String getProAddress() {
        return proAddress;
    }

    public void setProAddress(String proAddress) {
        this.proAddress = proAddress;
    }

    public String getProFax() {
        return proFax;
    }

    public void setProFax(String proFax) {
        this.proFax = proFax;
    }

    public Integer getCreatedBy() {
        return createdBy;
    }

    public void setCreatedBy(Integer createdBy) {
        this.createdBy = createdBy;
    }

    public Date getCreationDate() {
        return creationDate;
    }

    public void setCreationDate(Date creationDate) {
        this.creationDate = creationDate;
    }

    public Integer getModifyBy() {
        return modifyBy;
    }

    public void setModifyBy(Integer modifyBy) {
        this.modifyBy = modifyBy;
    }

    public Date getModifyDate() {
        return modifyDate;
    }

    public void setModifyDate(Date modifyDate) {
        this.modifyDate = modifyDate;
    }

    @Override
    public String toString() {
        return "Provider{" +
                "id=" + id +
                ", proCode='" + proCode + '\'' +
                ", proName='" + proName + '\'' +
                ", proDesc='" + proDesc + '\'' +
                ", proContact='" + proContact + '\'' +
                ", proPhone='" + proPhone + '\'' +
                ", proAddress='" + proAddress + '\'' +
                ", proFax='" + proFax + '\'' +
                ", createdBy=" + createdBy +
                ", creationDate=" + creationDate +
                ", modifyBy=" + modifyBy +
                ", modifyDate=" + modifyDate +
                '}';
    }
}

(3)Role

package com.zp.pojo;

import lombok.Data;

import java.util.Date;

@Data
public class Role {
    private Integer id;   //id
    private String roleCode; //角色编码
    private String roleName; //角色名称
    private Integer createdBy; //创建者
    private Date creationDate; //创建时间
    private Integer modifyBy; //更新者
    private Date modifyDate;//更新时间

    public Integer getId() {
        return id;
    }

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

    public String getRoleCode() {
        return roleCode;
    }

    public void setRoleCode(String roleCode) {
        this.roleCode = roleCode;
    }

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    public Integer getCreatedBy() {
        return createdBy;
    }

    public void setCreatedBy(Integer createdBy) {
        this.createdBy = createdBy;
    }

    public Date getCreationDate() {
        return creationDate;
    }

    public void setCreationDate(Date creationDate) {
        this.creationDate = creationDate;
    }

    public Integer getModifyBy() {
        return modifyBy;
    }

    public void setModifyBy(Integer modifyBy) {
        this.modifyBy = modifyBy;
    }

    public Date getModifyDate() {
        return modifyDate;
    }

    public void setModifyDate(Date modifyDate) {
        this.modifyDate = modifyDate;
    }

    @Override
    public String toString() {
        return "Role{" +
                "id=" + id +
                ", roleCode='" + roleCode + '\'' +
                ", roleName='" + roleName + '\'' +
                ", createdBy=" + createdBy +
                ", creationDate=" + creationDate +
                ", modifyBy=" + modifyBy +
                ", modifyDate=" + modifyDate +
                '}';
    }
}

(4)User

package com.zp.pojo;

import lombok.Data;

import java.util.Date;

@Data
public class User {
    private Integer id; //id
    private String userCode; //用户编码
    private String userName; //用户名称
    private String userPassword; //用户密码
    private Integer gender;  //性别
    private Date birthday;  //出生日期
    private String phone;   //电话
    private String address; //地址
    private Integer userRole;    //用户角色
    private Integer createdBy;   //创建者
    private Date creationDate; //创建时间
    private Integer modifyBy;     //更新者
    private Date modifyDate;   //更新时间
    private Integer age;//年龄
    private String userRoleName;    //用户角色名称

    public Integer getId() {
        return id;
    }

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

    public String getUserCode() {
        return userCode;
    }

    public void setUserCode(String userCode) {
        this.userCode = userCode;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getUserPassword() {
        return userPassword;
    }

    public void setUserPassword(String userPassword) {
        this.userPassword = userPassword;
    }

    public Integer getGender() {
        return gender;
    }

    public void setGender(Integer gender) {
        this.gender = gender;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Integer getUserRole() {
        return userRole;
    }

    public void setUserRole(Integer userRole) {
        this.userRole = userRole;
    }

    public Integer getCreatedBy() {
        return createdBy;
    }

    public void setCreatedBy(Integer createdBy) {
        this.createdBy = createdBy;
    }

    public Date getCreationDate() {
        return creationDate;
    }

    public void setCreationDate(Date creationDate) {
        this.creationDate = creationDate;
    }

    public Integer getModifyBy() {
        return modifyBy;
    }

    public void setModifyBy(Integer modifyBy) {
        this.modifyBy = modifyBy;
    }

    public Date getModifyDate() {
        return modifyDate;
    }

    public void setModifyDate(Date modifyDate) {
        this.modifyDate = modifyDate;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getUserRoleName() {
        return userRoleName;
    }

    public void setUserRoleName(String userRoleName) {
        this.userRoleName = userRoleName;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", userCode='" + userCode + '\'' +
                ", userName='" + userName + '\'' +
                ", userPassword='" + userPassword + '\'' +
                ", gender=" + gender +
                ", birthday=" + birthday +
                ", phone='" + phone + '\'' +
                ", address='" + address + '\'' +
                ", userRole=" + userRole +
                ", createdBy=" + createdBy +
                ", creationDate=" + creationDate +
                ", modifyBy=" + modifyBy +
                ", modifyDate=" + modifyDate +
                ", age=" + age +
                ", userRoleName='" + userRoleName + '\'' +
                '}';
    }
}

准备工作搞完，接下来就是重头戏：
练习流程：主目录Mapper(接口) ----》对应Mapper的配置文件 ----》测试文件中Mapper去测试

开干：先把数据库的连接、配置文件加载：
mybatisUtils：放在测试目录里面，因为是在测试中练习

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

/**
 * mybatis工具类
 * SqlSessionFactory--> SqlSession
 */
public class mybatisUtils {

    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            //使用mybatis第一步获取 ：SqlSessionFactory对象
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    /**
     * 既然有了SqlSessionFactory，顾名思义，我们可以从中获得SqlSession的实例。SqlSession
     * 提供了在数据库执行 SQL 命令所属的所有方法。你可以通过Session 实例来直接执行已映射的SQL
     * 语句。例如：opensession=true ->自动提交事务
     */
    public static SqlSession getSqlSession() {
        //获取SqlSession对象，用来执行sql
        return sqlSessionFactory.openSession(/*true*/);
    }
}

BillMapper：

package com.zp.Mapper;

import com.zp.pojo.Bill;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * 抽象类
 */
public interface BillMapper {
    /**
     * 根据供应商Id查询订单数量
     * @param providerId
     * @return
     */
    public int getBillCountByProviderId(@Param("providerId") Integer providerId);

    /**
     * 增加订单
     * @param bill
     * @return
     */
    public int add(Bill bill);

    /**
     * 通过查询条件获取供应商列表-getBillList
     * @param productName
     * @param providerId
     * @param isPayment
     * @param startIndex
     * @param pageSize
     * @return
     * @throws Exception
     */
    public List<Bill> getBillList(
            @Param("productName") String productName,
            @Param("providerId") String providerId,
            @Param("isPayment") String isPayment,
            @Param("startIndex") Integer startIndex,
            @Param("pageSize") Integer pageSize)throws Exception;

    /**
     * 通过delId，删除bill
     */
    public void deleteBillById(@Param("id") Integer id)throws Exception;

    /**
     * 通过billId获取Bill
     */
    public Bill getBillById(@Param("id") Integer id)throws Exception;

    /**
     * 修改订单信息
     * @param bill
     * @return
     * @throws Exception
     */
    public int modify(Bill bill)throws Exception;
}

BillMapper.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：命名空间
-->
<mapper namespace="com.zp.Mapper.BillMapper">

    <!--根据供应商Id查询订单数量-->
    <!--
        id：是select语句的唯一标识
        resultType：返回结果的类型
    -->
    <select id="getBillCountByProviderId" resultType="int">
        select count(*)
        from smbms_bill
        where providerId = #{providerId}
    </select>

    <!--增加订单-->
    <!--
        id：是insert语句的唯一标识
        parameterType：传入Bill类型
    -->
    <insert id="add" parameterType="Bill">
        insert into smbms_bill ( id, billCode, productName, productDesc, productUnit, productCount
                               , totalPrice, isPayment, providerId, createdBy, creationDate)
        values (#{id}, #{billCode}, #{productName}, #{productDesc}, #{productUnit}, #{productCount},
                #{totalPrice}, #{isPayment}, #{providerId}, #{createdBy}, #{creationDate})
    </insert>

    <!--通过条件查询，查询供货商数量,模糊查询-->
    <select id="getBillList" resultType="Bill">
        select * from smbms_bill
        <where>
            <if test="productName != null">
                and productName like concat ('%',#{productName},'%')
            </if>
            <if test="providerId != null">
                and providerId like concat ('%',#{providerId},'%')
            </if>
            <if test="isPayment != null">
                and isPayment like concat ('%',#{isPayment},'%')
            </if>
        </where>
        limit #{startIndex},#{pageSize}
    </select>

    <!--通过delId删除Bill-->
    <delete id="deleteBillById" parameterType="int">
        delete from smbms_bill
        <where>
            id=#{id}
        </where>

    </delete>

    <!--通过billId获取Bill-->
    <select id="getBillById" resultType="Bill">
        select * from smbms_bill
        <where>
            id=#{id}
        </where>
    </select>

    <!--修改订单信息-->
    <update id="modify">
        update smbms_bill
        <set>
            <if test="productName != null">
                productName=#{productName},
            </if>
            <if test="productDesc != null">
                productDesc#{productDesc},
            </if>
            <if test="productCount != null">
                productCount=#{productCount},
            </if>
        </set>
        <where>
            id=#{id}
        </where>
    </update>
</mapper>

BillTest：

package com.zp.utils;

import com.zp.Mapper.BillMapper;
import com.zp.pojo.Bill;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class BillTest {

    //根据供应商Id查询订单数量
    @Test
    public void getBillCountByProviderId() {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        BillMapper billMapper = sqlSession.getMapper(BillMapper.class);//获取对应的接口的对象
        int count = billMapper.getBillCountByProviderId(3);
        System.out.println("供应商Id为：3的供应" + count + "种商品");
        sqlSession.close();
    }

    //增加订单
    @Test
    public void add() {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        BillMapper billMapper = sqlSession.getMapper(BillMapper.class);//获取对应的接口的对象
        Bill bill = new Bill();
        bill.setId(19);
        bill.setBillCode("BILL2016_019");
        billMapper.add(bill);
        sqlSession.commit();//提交事务
        sqlSession.close();//释放资源
    }

    //通过条件查询，查询供货商数量,模糊查询
    @Test
    public void getBillList() throws Exception {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        BillMapper billMapper = sqlSession.getMapper(BillMapper.class);
        List<Bill> billList = billMapper.getBillList("油", "1", "1", 0, 2);
        for (Bill bill : billList) {
            System.out.println(bill);
        }
        sqlSession.close();
    }

    //通过Id，删除
    @Test
    public void deleteBillById() throws Exception {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        BillMapper billMapper = sqlSession.getMapper(BillMapper.class);
        billMapper.deleteBillById(19);
        sqlSession.commit();
        sqlSession.close();
    }

    //通过billId获取Bill
    @Test
    public void getBillById() throws Exception {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        BillMapper billMapper = sqlSession.getMapper(BillMapper.class);
        Bill bill = billMapper.getBillById(2);
        System.out.println(bill);
        sqlSession.close();
    }

    //修改订单信息
    @Test
    public void modify() throws Exception {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        BillMapper billMapper = sqlSession.getMapper(BillMapper.class);
        Bill bill = new Bill();
        bill.setProductName("爽歪歪");
        bill.setId(18);
        billMapper.modify(bill);
        sqlSession.commit();
        sqlSession.close();
    }
}

ProviderMapper：

package com.zp.Mapper;

import com.zp.pojo.Provider;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface ProviderMapper {
    /**
     * 增加供应商
     * @param provider
     * @return
     * @throws Exception
     */
    public int add(Provider provider)throws Exception;

    /**
     * 通过供应商名称、编码获取供应商列表-模糊查询-providerList
     * @param proName
     * @return
     * @throws Exception
     */
    public List<Provider> getProviderList(@Param("proName") String proName,
                                          @Param("proCode")String proCode)throws Exception;

    /**
     * 通过proId删除Provider
     * @param id
     * @return
     * @throws Exception
     */
    public int deleteProviderById( int id)throws Exception;

    /**
     * 通过proId获取Provider
     * @param id
     * @return
     * @throws Exception
     */
    public Provider getProviderById(String id)throws Exception;

    /**
     * 修改用户信息
     * @param
     * @return
     * @throws Exception
     */
    public int modify(Provider provider)throws Exception;

}

ProviderMapper.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：命名空间
-->
<mapper namespace="com.zp.Mapper.ProviderMapper">

    <!--增加供应商-->
    <insert id="add">
        insert into smbms_provider (id,proCode,proName,proDesc)
        values (#{id},#{proCode},#{proName},#{proDesc});
    </insert>
    <!--修改用户信息-->
    <update id="modify">
        update smbms_provider
        <set>
            <if test="proCode != null">
                proCode=#{proCode},
            </if>
            <if test="proName">
                proName=#{proName},
            </if>
        </set>
        <where>
            id=#{id}
        </where>
    </update>
    <!--通过proId删除Provider-->
    <delete id="deleteProviderById">
        delete from smbms_provider
        <where>
            id=#{id}
        </where>
    </delete>
    <!--通过供应商名称、编码获取供应商列表-模糊查询-providerList-->
    <select id="getProviderList" resultType="Provider">
        select * from smbms_provider
        <where>
            <if test="proName != null">
                and proName like concat('%',#{proName},'%')
            </if>
            <if test="proCode != null">
                and proCode like concat('%',#{proCode},'%')
            </if>
        </where>
    </select>
    <!--通过proId获取Provider-->
    <select id="getProviderById" resultType="Provider">
        select * from smbms_provider
        <where>
            id=#{id}
        </where>
    </select>
</mapper>

providerTest：

package com.zp.utils;

import com.zp.Mapper.ProviderMapper;
import com.zp.pojo.Provider;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class providerTest {
    /**
     * 增加供应商
     *
     * @throws Exception
     */
    @Test
    public void add() throws Exception {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        ProviderMapper providerMapper = sqlSession.getMapper(ProviderMapper.class);
        Provider provider = new Provider();
        provider.setId(16);
        provider.setProCode("新添加的");
        provider.setProName("张三");
        provider.setProDesc("主营app");
        providerMapper.add(provider);
        sqlSession.commit();
        sqlSession.close();
    }

    /**
     * 通过供应商名称、编码获取供应商列表-模糊查询-providerList
     *
     * @throws Exception
     */
    @Test
    public void getProviderList() throws Exception {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        ProviderMapper providerMapper = sqlSession.getMapper(ProviderMapper.class);
        List<Provider> providerList = providerMapper.getProviderList("张", "新");
        for (Provider provider : providerList) {
            System.out.println(provider);
        }
        sqlSession.close();
    }

    /**
     * 通过proId删除Provider
     *
     * @throws Exception
     */
    @Test
    public void deleteProviderById() throws Exception {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        ProviderMapper providerMapper = sqlSession.getMapper(ProviderMapper.class);
        providerMapper.deleteProviderById(16);
        sqlSession.commit();
        sqlSession.close();
    }

    /**
     * 通过proId获取Provider
     *
     * @throws Exception
     */
    @Test
    public void getProviderById() throws Exception {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        ProviderMapper providerMapper = sqlSession.getMapper(ProviderMapper.class);
        Provider provider = providerMapper.getProviderById("16");
        System.out.println(provider);
        sqlSession.close();
    }

    /**
     * 修改用户信息
     *
     * @throws Exception
     */
    @Test
    public void modify() throws Exception {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        ProviderMapper providerMapper = sqlSession.getMapper(ProviderMapper.class);
        Provider provider = new Provider();
        provider.setProCode("修改proCode");
        provider.setProName("修改proName");
        provider.setId(16);
        providerMapper.modify(provider);
        sqlSession.commit();
        sqlSession.close();
    }
}

RoleMapper：

package com.zp.Mapper;

import com.zp.pojo.Role;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface RoleMapper {

    //获取角色列表
    public List<Role> getRoleList()throws Exception;

    //增加角色信息
    public int add(Role role)throws Exception;

    //通过Id删除Role
    public int deleteRoleById(@Param("id") String Id)throws Exception;

    //修改角色信息
    public int modify(Role role)throws Exception;

    //通过Id获取role
    public Role getRoleById(@Param("id") Integer id)throws Exception;

    //根据roleCode，进行角色编码的唯一性验证(统计count)
    public int roleCodeIsExist(@Param("roleCode") String roleCode)throws Exception;
}

RoleMapper.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：命名空间
-->
<mapper namespace="com.zp.Mapper.RoleMapper">
    <!--增加角色信息-->
    <insert id="add">
        insert into smbms_role (id,roleCode,roleName)
        values (#{id},#{roleCode},#{roleName});
    </insert>
    <!--修改角色信息-->
    <update id="modify">
        update smbms_role
        <set>
            <if test="roleCode != null">
                roleCode=#{roleCode},
            </if>
            <if test="roleName != null">
                roleName=#{roleName},
            </if>
        </set>
        <where>
            id=#{id}
        </where>
    </update>


    <!--通过Id删除Role-->
    <delete id="deleteRoleById">
        delete from smbms_role
        <where>
            id=#{id}
        </where>
    </delete>

    <!--获取角色列表-->
    <select id="getRoleList" resultType="Role">
        select * from smbms_role
    </select>

    <!--通过Id获取role-->
    <select id="getRoleById" resultType="Role">
        select * from smbms_role
        <where>
            id=#{id}
        </where>
    </select>
    <!--根据roleCode，进行角色编码的唯一性验证(统计count)-->
    <select id="roleCodeIsExist" resultType="Integer">
        select count(*) from smbms_role
        <where>
            roleCode=#{roleCode}
        </where>
    </select>
</mapper>

RoleTest：

package com.zp.utils;


import com.zp.Mapper.RoleMapper;
import com.zp.pojo.Role;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class RoleTest {

    /**
     * 获取角色列表
     *
     * @throws Exception
     */
    @Test
    public void getRoleList() throws Exception {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
        List<Role> roleList = roleMapper.getRoleList();
        System.out.println(roleList);
        sqlSession.close();
    }

    /**
     * 增加角色信息
     *
     * @throws Exception
     */
    @Test
    public void add() throws Exception {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
        Role role = new Role();
        role.setId(4);
        role.setRoleCode("zp");
        role.setRoleName("张四");
        roleMapper.add(role);
        sqlSession.commit();
        sqlSession.close();
    }

    /**
     * 通过Id删除Role
     *
     * @throws Exception
     */
    @Test
    public void deleteRoleById() throws Exception {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
        roleMapper.deleteRoleById("4");
        sqlSession.commit();
        sqlSession.close();
    }

    /**
     * 修改角色信息
     *
     * @throws Exception
     */
    @Test
    public void modify() throws Exception {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
        Role role = new Role();
        role.setRoleName("李四");
        role.setRoleCode("abc");
        role.setId(4);
        roleMapper.modify(role);
        sqlSession.commit();
        sqlSession.close();
        ;
    }

    /**
     * 通过Id获取role
     *
     * @throws Exception
     */
    @Test
    public void getRoleById() throws Exception {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
        Role role = roleMapper.getRoleById(4);
        System.out.println(role);
        sqlSession.close();
    }

    /**
     * 根据roleCode，进行角色编码的唯一性验证(统计count)
     * @throws Exception
     */
    @Test
    public void roleCodeIsExist() throws Exception {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
        int count=roleMapper.roleCodeIsExist("abc");
        System.out.println(count);
        sqlSession.close();
    }
}

UserMapper：

package com.zp.Mapper;

import com.zp.pojo.User;
import org.apache.ibatis.annotations.Param;

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

public interface UserMapper {
    /**
     * @param
     * @param map
     * @return
     * @throws Exception
     */
    //测试数据库是否链接成功
    List<User> UserTest(Map<String,Integer> map) throws Exception;

    /**
     * 通过userCode获取User
     *
     * @param userCode
     * @return
     * @throws Exception
     */
    //通过userCode获取User
    public User getLoginUser(@Param("userCode") String userCode) throws Exception;

    /**
     * 增加用户信息
     * insert into
     * @param user
     * @return
     * @throws Exception
     */
    //增加用户信息
    public int add(User user) throws Exception;

    /**
     * 通过条件查询userList,limit分页
     *
     * @param userName
     * @param userRole
     * @return
     * @throws Exception
     */
    //通过条件查询userList,limit分页
    public List<User> getUserList(@Param("userName") String userName,
                                  @Param("userRole") Integer userRole,
                                  @Param("currentPageNO") Integer currentPageNO,
                                  @Param("pageSize") Integer pageSize)throws Exception;

    /**
     *通过条件查询-用户记录数
     * @param userName
     * @param userRole
     * @return
     * @throws Exception
     */
    //通过条件查询-用户记录数
    public int getUserCount(@Param("userName") String userName,
                            @Param("userRole") Integer userRole)throws Exception;

    /**
     * 通过userId删除user
     * delect from
     * @param id
     * @return
     */
    //通过userId删除user
    public int deleteUserById(@Param("id") Integer id);

    /**
     * 通过useId获取user
     * select from
     * @param id
     * @return
     */
    //通过useId获取user
    public User getUserById(@Param("id") Integer id);

    /**
     * 修改用户信息
     * update set
     * @param user
     * @return
     */
    //修改用户信息
    public int modify(User user);

    /**
     * update set
     * @param id
     * @param userPassword
     * @return
     */
    //修改当前用户密码
    public int updatePwd(@Param("id") Integer id, @Param("userPassword") String userPassword);
}

UserMapper.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：命名空间
-->
<mapper namespace="com.zp.Mapper.UserMapper">
    <!--增加用户信息-->
    <insert id="add" >
        insert into smbms_user (userCode,userName,userPassword,gender,birthday,phone,
                                      address,userRole,createdBy,creationDate)
        values (#{userCode},#{userName},#{userPassword},#{gender},#{birthday},
                #{phone},#{address},#{userRole},#{createdBy},#{creationDate});
    </insert>
    <!--修改用户信息-->
    <update id="modify">
        update smbms_user
        <set>
            <if test="userCode != null">
                userCode=#{userCode},
            </if>
            <if test="userName != null">
                userName=#{userName},
            </if>
            <if test="phone != null">
                phone=#{phone}
            </if>
        </set>
        <where>
            id=#{id}
        </where>
    </update>
    <!--修改当前用户密码-->
    <update id="updatePwd">
        update smbms_user
        <set>
            <if test="userPassword != null">
                userPassword=#{userPassword},
            </if>
        </set>
        <where>
            id=#{id}
        </where>
    </update>
    <!--通过userId删除user-->
    <delete id="deleteUserById">
        delete
        from smbms_user
        <where>
            id=#{id}
        </where>
    </delete>

    <!--
        测试数据库是否连接成功
        limit是分页，startindex是从什么时候开始
        pagesize是一页多少条数据
    -->
    <select id="UserTest" resultType="user">
        select *
        from smbms_user limit #{startindex},#{pagesize};
    </select>
    <!--通过条件查询userList,limit分页-->
    <select id="getUserList" resultType="User">
        select * from smbms_user
        <where>
            <if test="userName != null">
                and userName=#{userName}
            </if>
            <if test="userRole != null">
                and userRole=#{userRole}
            </if>
        </where>
        limit #{currentPageNO},#{pageSize}
    </select>
    <!--通过条件查询-用户记录数-->
    <select id="getUserCount" resultType="Integer">
        select count(*)
        from smbms_user
        <where>
            userName=#{userName} and userRole=#{userRole}
        </where>
    </select>
    <!--通过useId获取user-->
    <select id="getUserById" resultType="User">
        select *
        from smbms_user
        <where>
            id=#{id}
        </where>
    </select>
</mapper>

UserTest：

package com.zp.utils;

import com.zp.Mapper.UserMapper;
import com.zp.pojo.User;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.Date;
import java.util.HashMap;
import java.util.List;

public class UserTest {

    /**
     * 测试数据库是否链接成功
     *
     * @throws Exception
     */
    @Test
    public void GetUser() throws Exception {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        HashMap<String, Integer> hashMap = new HashMap<String, Integer>();
        hashMap.put("startndex", 0);
        hashMap.put("pagesize", 5);
        List<User> userList = userMapper.UserTest(hashMap);
        for (User user : userList) {
            System.out.println(user);
        }
        sqlSession.close();
    }

    /**
     * 增加用户信息
     *
     * @throws Exception
     */
    @Test
    public void add() throws Exception {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setId(16);
        user.setUserCode("zp");
        user.setAddress("上海");
        user.setUserName("张三");
        user.setUserPassword("123");
        user.setGender(1);
        user.setBirthday(new Date(10));
        user.setPhone("18888888888");
        user.setUserRole(1);
        user.setCreatedBy(1);
        user.setCreationDate(new Date(20));
        user.setModifyBy(0);
        user.setModifyDate(new Date(30));
        userMapper.add(user);
        sqlSession.commit();
        sqlSession.close();
    }

    /**
     * 通过条件查询userList,limit分页
     *
     * @throws Exception
     */
    @Test
    public void getUserList() throws Exception {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.getUserList("张三", 1, 0, 1);
        for (User user : userList) {
            System.out.println(user);
        }
        sqlSession.close();
    }

    /**
     * 通过条件查询-用户记录数
     *
     * @throws Exception
     */
    @Test
    public void getUserCount() throws Exception {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        int userCount = userMapper.getUserCount("张三", 1);
        System.out.println(userCount);
        sqlSession.close();
    }

    /**
     * 通过useId获取user
     *
     * @throws Exception
     */
    @Test
    public void deleteUserById() throws Exception {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        userMapper.deleteUserById(16);
        sqlSession.commit();
        sqlSession.close();
    }

    /**
     * 通过useId获取user
     *
     * @throws Exception
     */
    @Test
    public void getUserById() throws Exception {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = userMapper.getUserById(1);
        System.out.println(user);
        sqlSession.close();
    }

    /**
     * 修改用户信息
     *
     * @throws Exception
     */
    @Test
    public void modify() throws Exception {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setUserName("王五");
        user.setId(1);
        user.setUserCode("One");
        userMapper.modify(user);
        sqlSession.commit();
        sqlSession.close();
    }

    /**
     * 修改当前用户密码
     * @throws Exception
     */
    @Test
    public void updatePwd()throws Exception{
        SqlSession sqlSession=mybatisUtils.getSqlSession();
        UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
        userMapper.updatePwd(1,"123456");
        sqlSession.commit();
        sqlSession.close();
    }
}