狂神说Mybatis3 29道习题_狂神mybatis29-优快云博客

CREATE DATABASE smbms;
USE smbms;


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` BIGINT(20) DEFAULT NULL COMMENT '供应商ID',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT  INTO `smbms_bill`(`id`,`billCode`,`productName`,`productDesc`,`productUnit`,`productCount`,`totalPrice`,`isPayment`,`createdBy`,`creationDate`,`modifyBy`,`modifyDate`,`providerId`) 
VALUES (2,'BILL2016_002','香皂、肥皂、药皂','日用品-皂类','块','1000.00','10000.00',2,1,'2016-03-23 04:20:40',NULL,NULL,13),
(3,'BILL2016_003','大豆油','食品-食用油','斤','300.00','5890.00',2,1,'2014-12-14 13:02:03',NULL,NULL,6),
(4,'BILL2016_004','橄榄油','食品-进口食用油','斤','200.00','9800.00',2,1,'2013-10-10 03:12:13',NULL,NULL,7),
(5,'BILL2016_005','洗洁精','日用品-厨房清洁','瓶','500.00','7000.00',2,1,'2014-12-14 13:02:03',NULL,NULL,9),
(6,'BILL2016_006','美国大杏仁','食品-坚果','袋','300.00','5000.00',2,1,'2016-04-14 06:08:09',NULL,NULL,4),
(7,'BILL2016_007','沐浴液、精油','日用品-沐浴类','瓶','500.00','23000.00',1,1,'2016-07-22 10:10:22',NULL,NULL,14),
(8,'BILL2016_008','不锈钢盘碗','日用品-厨房用具','个','600.00','6000.00',2,1,'2016-04-14 05:12:13',NULL,NULL,14),
(9,'BILL2016_009','塑料杯','日用品-杯子','个','350.00','1750.00',2,1,'2016-02-04 11:40:20',NULL,NULL,14),
(10,'BILL2016_010','豆瓣酱','食品-调料','瓶','200.00','2000.00',2,1,'2013-10-29 05:07:03',NULL,NULL,8),
(11,'BILL2016_011','海之蓝','饮料-国酒','瓶','50.00','10000.00',1,1,'2016-04-14 16:16:00',NULL,NULL,1),
(12,'BILL2016_012','芝华士','饮料-洋酒','瓶','20.00','6000.00',1,1,'2016-09-09 17:00:00',NULL,NULL,1),
(13,'BILL2016_013','长城红葡萄酒','饮料-红酒','瓶','60.00','800.00',2,1,'2016-11-14 15:23:00',NULL,NULL,1),
(14,'BILL2016_014','泰国香米','食品-大米','斤','400.00','5000.00',2,1,'2016-10-09 15:20:00',NULL,NULL,3),
(15,'BILL2016_015','东北大米','食品-大米','斤','600.00','4000.00',2,1,'2016-11-14 14:00:00',NULL,NULL,3),
(16,'BILL2016_016','可口可乐','饮料','瓶','2000.00','6000.00',2,1,'2012-03-27 13:03:01',NULL,NULL,2),
(17,'BILL2016_017','脉动','饮料','瓶','1500.00','4500.00',2,1,'2016-05-10 12:00:00',NULL,NULL,2),
(18,'BILL2016_018','哇哈哈','饮料','瓶','2000.00','4000.00',2,1,'2015-11-24 15:12:03',NULL,NULL,2);


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=16 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT  INTO `smbms_provider`(`id`,`proCode`,`proName`,`proDesc`,`proContact`,`proPhone`,`proAddress`,`proFax`,`createdBy`,`creationDate`,`modifyDate`,`modifyBy`) 
VALUES (1,'BJ_GYS001','北京三木堂商贸有限公司','长期合作伙伴，主营产品:茅台、五粮液、郎酒、酒鬼酒、泸州老窖、赖茅酒、法国红酒等','张国强','13566667777','北京市丰台区育芳园北路','010-58858787',1,'2013-03-21 16:52:07',NULL,NULL),
(2,'HB_GYS001','石家庄帅益食品贸易有限公司','长期合作伙伴，主营产品:饮料、水饮料、植物蛋白饮料、休闲食品、果汁饮料、功能饮料等','王军','13309094212','河北省石家庄新华区','0311-67738876',1,'2016-04-13 04:20:40',NULL,NULL),
(3,'GZ_GYS001','深圳市泰香米业有限公司','初次合作伙伴，主营产品：良记金轮米,龙轮香米等','郑程瀚','13402013312','广东省深圳市福田区深南大道6006华丰大厦','0755-67776212',1,'2014-03-21 16:56:07',NULL,NULL),
(4,'GZ_GYS002','深圳市喜来客商贸有限公司','长期合作伙伴，主营产品：坚果炒货.果脯蜜饯.天然花茶.营养豆豆.特色美食.进口食品.海味零食.肉脯肉','林妮','18599897645','广东省深圳市福龙工业区B2栋3楼西','0755-67772341',1,'2013-03-22 16:52:07',NULL,NULL),
(5,'JS_GYS001','兴化佳美调味品厂','长期合作伙伴，主营产品：天然香辛料、鸡精、复合调味料','徐国洋','13754444221','江苏省兴化市林湖工业区','0523-21299098',1,'2015-11-22 16:52:07',NULL,NULL),
(6,'BJ_GYS002','北京纳福尔食用油有限公司','长期合作伙伴，主营产品：山茶油、大豆油、花生油、橄榄油等','马莺','13422235678','北京市朝阳区珠江帝景1号楼','010-588634233',1,'2012-03-21 17:52:07',NULL,NULL),
(7,'BJ_GYS003','北京国粮食用油有限公司','初次合作伙伴，主营产品：花生油、大豆油、小磨油等','王驰','13344441135','北京大兴青云店开发区','010-588134111',1,'2016-04-13 00:00:00',NULL,NULL),
(8,'ZJ_GYS001','慈溪市广和绿色食品厂','长期合作伙伴，主营产品：豆瓣酱、黄豆酱、甜面酱，辣椒，大蒜等农产品','薛圣丹','18099953223','浙江省宁波市慈溪周巷小安村','0574-34449090',1,'2013-11-21 06:02:07',NULL,NULL),
(9,'GX_GYS001','优百商贸有限公司','长期合作伙伴，主营产品：日化产品','李立国','13323566543','广西南宁市秀厢大道42-1号','0771-98861134',1,'2013-03-21 19:52:07',NULL,NULL),
(10,'JS_GYS002','南京火头军信息技术有限公司','长期合作伙伴，主营产品：不锈钢厨具等','陈女士','13098992113','江苏省南京市浦口区浦口大道1号新城总部大厦A座903室','025-86223345',1,'2013-03-25 16:52:07',NULL,NULL),
(11,'GZ_GYS003','广州市白云区美星五金制品厂','长期合作伙伴，主营产品：海绵床垫、坐垫、靠垫、海绵枕头、头枕等','梁天','13562276775','广州市白云区钟落潭镇福龙路20号','020-85542231',1,'2016-12-21 06:12:17',NULL,NULL),
(12,'BJ_GYS004','北京隆盛日化科技','长期合作伙伴，主营产品：日化环保清洗剂，家居洗涤专卖、洗涤用品网、墙体除霉剂、墙面霉菌清除剂等','孙欣','13689865678','北京市大兴区旧宫','010-35576786',1,'2014-11-21 12:51:11',NULL,NULL),
(13,'SD_GYS001','山东豪克华光联合发展有限公司','长期合作伙伴，主营产品：洗衣皂、洗衣粉、洗衣液、洗洁精、消杀类、香皂等','吴洪转','13245468787','山东济阳济北工业区仁和街21号','0531-53362445',1,'2015-01-28 10:52:07',NULL,NULL),
(14,'JS_GYS003','无锡喜源坤商行','长期合作伙伴，主营产品：日化品批销','周一清','18567674532','江苏无锡盛岸西路','0510-32274422',1,'2016-04-23 11:11:11',NULL,NULL),
(15,'ZJ_GYS002','乐摆日用品厂','长期合作伙伴，主营产品：各种中、高档塑料杯，塑料乐扣水杯（密封杯）、保鲜杯（保鲜盒）、广告杯、礼品杯','王世杰','13212331567','浙江省金华市义乌市义东路','0579-34452321',1,'2016-08-22 10:01:30',NULL,NULL);


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=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

insert  into `smbms_role`(`id`,`roleCode`,`roleName`,`createdBy`,`creationDate`,`modifyBy`,`modifyDate`) 
values (1,'SMBMS_ADMIN','系统管理员',1,'2016-04-13 00:00:00',NULL,NULL),
(2,'SMBMS_MANAGER','经理',1,'2016-04-13 00:00:00',NULL,NULL),
(3,'SMBMS_EMPLOYEE','普通员工',1,'2016-04-13 00:00:00',NULL,NULL);


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` bigint(20) 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;

insert  into `smbms_user`(`id`,`userCode`,`userName`,`userPassword`,`gender`,`birthday`,`phone`,`address`,`userRole`,`createdBy`,`creationDate`,`modifyBy`,`modifyDate`) 
values (1,'admin','系统管理员','1234567',1,'1983-10-10','13688889999','北京市海淀区成府路207号',1,1,'2013-03-21 16:52:07',NULL,NULL),
(2,'liming','李明','0000000',2,'1983-12-10','13688884457','北京市东城区前门东大街9号',2,1,'2014-12-31 19:52:09',NULL,NULL),
(5,'hanlubiao','韩路彪','0000000',2,'1984-06-05','18567542321','北京市朝阳区北辰中心12号',2,1,'2014-12-31 19:52:09',NULL,NULL),
(6,'zhanghua','张华','0000000',1,'1983-06-15','13544561111','北京市海淀区学院路61号',3,1,'2013-02-11 10:51:17',NULL,NULL),
(7,'wangyang','王洋','0000000',2,'1982-12-31','13444561124','北京市海淀区西二旗辉煌国际16层',3,1,'2014-06-11 19:09:07',NULL,NULL),
(8,'zhaoyan','赵燕','0000000',1,'1986-03-07','18098764545','北京市海淀区回龙观小区10号楼',3,1,'2016-04-21 13:54:07',NULL,NULL),
(10,'sunlei','孙磊','0000000',2,'1981-01-04','13387676765','北京市朝阳区管庄新月小区12楼',3,1,'2015-05-06 10:52:07',NULL,NULL),
(11,'sunxing','孙兴','0000000',2,'1978-03-12','13367890900','北京市朝阳区建国门南大街10号',3,1,'2016-11-09 16:51:17',NULL,NULL),
(12,'zhangchen','张晨','0000000',1,'1986-03-28','18098765434','朝阳区管庄路口北柏林爱乐三期13号楼',3,1,'2016-08-09 05:52:37',1,'2016-04-14 14:15:36'),
(13,'dengchao','邓超','0000000',2,'1981-11-04','13689674534','北京市海淀区北航家属院10号楼',3,1,'2016-07-11 08:02:47',NULL,NULL),
(14,'yangguo','杨过','0000000',2,'1980-01-01','13388886623','北京市朝阳区北苑家园茉莉园20号楼',3,1,'2015-02-01 03:52:07',NULL,NULL),
(15,'zhaomin','赵敏','0000000',1,'1987-12-04','18099897657','北京市昌平区天通苑3区12号楼',2,1,'2015-09-12 12:02:12',NULL,NULL);

POJO实体类

Bill

package com.duxuan.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;

import java.math.BigDecimal;
import java.util.Date;
@Data
@AllArgsConstructor
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 createdBy; //创建者
    private Date creationDate; //创建时间
    private Integer modifyBy; //更新者
    private Date modifyDate;//更新时间
    private Integer providerId; //供应商ID
    private String providerName;//供应商名称
}

Provider

package com.duxuan.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;

import java.util.Date;

@Data
@AllArgsConstructor
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 Date modifyDate;//更新时间
    private Integer modifyBy; //更新者



    public Provider(String proCode, String proName) {
        this.proCode = proCode;
        this.proName = proName;
    }
}

Role

package com.duxuan.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 Role(Integer id, String roleCode, String roleName, Integer createdBy, Date creationDate, Integer modifyBy, Date modifyDate) {
        this.id = id;
        this.roleCode = roleCode;
        this.roleName = roleName;
        this.createdBy = createdBy;
        this.creationDate = creationDate;
        this.modifyBy = modifyBy;
        this.modifyDate = modifyDate;
    }
}

User

package com.duxuan.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.ibatis.annotations.ConstructorArgs;

import java.util.Date;

@Data
public class User {
    //id
    private Integer 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 User(Integer id, String userCode, String userName, String userPassword, Integer gender, Date birthday, String phone, String address, Integer userRole, Integer createdBy, Date creationDate, Integer modifyBy, Date modifyDate, Integer age) {
        this.id = id;
        this.userCode = userCode;
        this.userName = userName;
        this.userPassword = userPassword;
        this.gender = gender;
        this.birthday = birthday;
        this.phone = phone;
        this.address = address;
        this.userRole = userRole;
        this.createdBy = createdBy;
        this.creationDate = creationDate;
        this.modifyBy = modifyBy;
        this.modifyDate = modifyDate;
        this.age = age;
    }
}

dao接口文件

RoleMapper.java

package com.duxuan.dao;

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

import java.util.List;

public interface RoleMapper {
    //练习1：获取角色列表
    public List<Role> getRoleList();
    //练习2：添加角色信息
    public int add(Role role);
    //练习3：通过Id删除Role RoleMapper主要看@Param里的参数名
    public int deleteRoleById(@Param("id")Integer delId);
    //练习4：修改角色信息
    public int modify(Role role);
    //练习5：通过Id获取role
    public Role getRoleById(@Param("id")Integer id);
    //练习6：根据roleCode，进行角色编码的唯一性验证,使用计数count
    public int roleCodeIsExist(@Param("roleCode") String roleCode);
}

UserMapper.java

package com.duxuan.dao;

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

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

public interface UserMapper {
    //练习7：通过userCode获取User
    public User getLoginUser(@Param("userCode") String userCode);

    //练习8：增加用户信息
    public int add(Map map);  //用Map不用User

    //练习9：通过条件查询userList 注意: 这里是User表与Role联合查询 userRole表示的Role的id,from代表起始
    public List<User> getUserList(@Param("userName") String userName,
                                  @Param("userRole") Integer userRole,
                                  @Param("from") Integer from,
                                  @Param("pageSize") Integer pageSize);

    //练习10：通过条件查询-用户记录数
    public int getUserCount(@Param("userName") String userName,
                            @Param("userRole") Integer userRole);

    //练习11：通过userId删除user
    public int deleteUserById(@Param("id") Integer id);

    //练习12：通过useId获取user
    public User getUserById(@Param("id") Integer id);

    //练习13：修改用户信息
    public int modify(User user);

    //练习14：修改当前用户密码
    public int updatePwd(@Param("id") Integer id,@Param("pwd") String pwd);

}

ProviderMapper.java

package com.duxuan.dao;

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

import java.util.List;

public interface ProviderMapper {
    //练习15：增加用户信息 这里类取别名的话一定要映射 resultMap 否则它会报找不到元素
    public int add(/*@Param("Provider") */Provider provider);

    //练习16：通过条件查询providerList
    public List<Provider> getProviderList(@Param("proName") String proName,
                                          @Param("proCode") String proCode,
                                          @Param("from") Integer currentPageNo,
                                          @Param("pageSize") Integer pageSize);

    //练习17：获取供应商列表
    public List<Provider> getProList();

    //练习18：通过条件查询供应商记录数
    public int getProviderCount(@Param("proName") String proName,
                                @Param("proCode") String proCode);

    //练习19：通过供应商Id删除供应商信息
    public int deleteProviderById(@Param("id") Integer delId);

    //练习20：根据供应商Id获取供应商信息
    public Provider getProviderById(@Param("id") Integer id);

    //练习21：修改供应商
    public int modify(Provider provider);

}

BillMapper.java

package com.duxuan.dao;
import com.duxuan.pojo.Bill;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface BillMapper {
    //练习22：根据供应商Id查询订单数量
    public int getBillCountByProviderId(@Param("providerId") Integer providerId);

    //练习23：增加订单
    public int add(Bill bill);

    //练习24：通过查询条件获取供应商列表-getBillList
    public List<Bill> getBillList(@Param("productName") String productName,
                                  @Param("providerId") String providerId,
                                  @Param("isPayment") String isPayment,
                                  @Param("from") Integer from,
                                  @Param("pageSize") Integer pageSize);

    //练习25：通过条件查询，查询供货商数量
    public int getBillCount(@Param("productName") String productName,
                            @Param("providerId") String providerId,
                            @Param("isPayment") String isPayment);

    //练习26：通过delId删除Bill
    public int deleteBillById(@Param("id") Integer id);

    //练习27：通过billId获取Bill
    public Bill getBillById(@Param("id") Integer id);

    //练习28：修改订单信息
    public int modify(Bill bill);

    //练习29：根据供应商Id删除订单信息
    public int deleteBillByProviderId(@Param("providerId") Integer providerId);

}

接口对应的XML文件

RoleMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.duxuan.dao.RoleMapper">

    <select id="getRoleList" resultType="Role">
        select * from smbms_role;
    </select>

    <insert id="add" parameterType="Role">
        insert into smbms_role values (#{id},#{roleCode},#{roleName},#{createdBy},#{creationDate},
        #{modifyBy},#{modifyDate});
    </insert>

    <delete id="deleteRoleById" parameterType="Integer">
        delete from smbms_role where id = #{id};
    </delete>

    <update id="modify" parameterType="Role">
        update smbms_role
        <set>
        <if test="roleCode != null">
            roleCode=#{roleCode},
        </if>
        <if test="roleName != null">
            roleName=#{roleName},
        </if>
        <if test="createdBy != null">
            createdBy=#{createdBy},
        </if>
        <if test="creationDate != null">
            creationDate=#{creationDate},
        </if>
        <if test="modifyBy != null">
            modifyBy=#{modifyBy},
        </if>
        <if test="modifyDate != null">
            modifyDate=#{modifyDate}
        </if>
        </set>
        where id = #{id}
    </update>

    <select id="getRoleById" resultType="Role">
        select * from smbms_role where id = #{id};
    </select>
    <!--计数使用count(*)-->
    <select id="roleCodeIsExist" parameterType="String" resultType="Integer">
        select count(*) from smbms_role where roleCode=#{roleCode}
    </select>
</mapper>

UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.duxuan.dao.UserMapper">

    <select id="getLoginUser" parameterType="String" resultType="User">
        select * from smbms_user
        <trim prefix="where">
            <if test="userCode != null">
                userCode = #{userCode}
            </if>
        </trim>
    </select>

    <insert id="add" parameterType="map">
        insert into smbms_user values (#{id},#{userCode},#{userName},#{userPassword},#{gender},#{birthday},
        #{phone},#{address},#{userRole},#{createdBy},#{creationDate},#{modifyBy}
        ,#{modifyDate});
    </insert>

    <!--多对一 按照结果嵌套查询-->
    <select id="getUserList" resultMap="UserRole">
        select u.*,r.roleName from smbms_user u,smbms_role r where  u.userRole=r.id
        <if test="userRole!= null">and u.userRole = #{userRole} </if>
        <if test="userName!= null and userName!=''">and u.userName like CONCAT('%',#{userName},'%')</if>
        order by creationDate DESC limit #{from},#{pageSize}
</select>
     <!--返回的Map要对应姓名，roleName-->
    <resultMap id="UserRole" type="user">
        <!--column数据库中的字段,property实体类中的属性,相同名称字段不用映射-->
        <result property="userRoleName" column="roleName"/> <!--如果实在不想写可以在sql中将userRoleName as 成别名roleName-->
    </resultMap>

    <!--计数使用count(*)-->
    <select id="getUserCount" resultType="Integer">
        select count(1) as count from smbms_user u,smbms_role r where u.userRole=r.id
        <if test="userRole!=null">and userRole=#{userRole}</if>
        <if test="userName!=null and userName!=''">and userName like CONCAT('%',#{userName},'%')</if>
    </select>

    <delete id="deleteUserById" parameterType="Integer">
        delete from smbms_user where id = #{id};
    </delete>

    <select id="getUserById" resultType="User">
        select * from smbms_user where id = #{id};
    </select>

    <update id="modify" parameterType="Role">
        update smbms_user
        <trim prefix="set" suffixOverrides="," suffix="where id = #{id}">
            <if test="userCode != null">
                userCode=#{userCode},
            </if>
            <if test="userName != null">
                userName=#{userName},
            </if>
            <if test="userPassword != null">
                userPassword=#{userPassword},
            </if>
            <if test="gender != null">
                gender=#{gender},
            </if>
            <if test="birthday != null">
                birthday=#{birthday},
            </if>
            <if test="phone != null">
                phone=#{phone},
            </if>
            <if test="address != null">
                address=#{address},
            </if>
            <if test="userRole != null">
                userRole=#{userRole},
            </if>
            <if test="createdBy != null">
                createdBy=#{createdBy},
            </if>
            <if test="creationDate != null">
                creationDate=#{creationDate},
            </if>
            <if test="modifyBy != null">
                modifyBy=#{modifyBy},
            </if>
            <if test="modifyDate != null">
                modifyDate=#{modifyDate}
            </if>
        </trim>
   

    <update id="updatePwd">
        update smbms_user set userPassword = #{pwd} where id = #{id}
    </update>
</mapper>

ProviderMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.duxuan.dao.ProviderMapper">

    <insert id="add" parameterType="Provider">
        insert into smbms_provider(proCode,proName)
		values(#{proCode},#{proName})
    </insert>


    <select id="getProviderList" resultType="Provider">
        select * from smbms_provider
        <trim prefix="where" prefixOverrides="and | or" >
            <if test="proName!=null and proName!=''">
                and proName like CONCAT('%',#{proName},'%')
            </if>
            <if test="proCode!=null and proCode!=''">
                and proCode like CONCAT('%',#{proCode},'%')
            </if>
        </trim>
        order by creationDate DESC
        limit #{from},#{pageSize}
    </select>

    <select id="getProList" resultType="Provider">
        select * from smbms_provider;
    </select>

    <!--计数使用count(*)-->
    <select id="getProviderCount" resultType="Integer">
        select count(1) from smbms_provider
        <where>
            <if test="proName != null">proName like CONCAT('%',#{proName},'%')</if>
            <if test="proCode != null">and proCode like CONCAT('%',#{proCode},'%')</if>
        </where>
    </select>

    <delete id="deleteProviderById" parameterType="Integer">
        delete from smbms_provider where id = #{id};
    </delete>

    <select id="getProviderById" resultType="Provider">
        select * from smbms_provider where id = #{id}
    </select>


    <update id="modify" parameterType="Provider">
        update smbms_role
            <trim prefix="set" suffixOverrides="," suffix="where id = #{id}">
                <if test="proCode!=null">proCode=#{proCode},</if>
                <if test="proName!=null">proName=#{proName},</if>
                <if test="proDesc!=null">proDesc=#{proDesc},</if>
                <if test="proContact!=null">proContact=#{proContact},</if>
                <if test="proPhone!=null">proPhone=#{proPhone},</if>
                <if test="proAddress!=null">proAddress=#{proAddress},</if>
                <if test="proFax!=null">proFax=#{proFax},</if>
                <if test="createdBy!=null">createdBy=#{createdBy},</if>
                <if test="creationDate!=null">creationDate=#{creationDate},</if>
                <if test="modifyDate!=null">modifyDate=#{modifyDate},</if>
                <if test="modifyBy!=null">modifyBy=#{modifyBy}</if>   /*最后一个不要加逗号*/
            </trim>

    </update>



</mapper>

BillMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.duxuan.dao.BillMapper">

    <select id="getBillCountByProviderId" resultType="Integer">
        select count(1) from smbms_bill where providerId = #{providerId}
    </select>

    <insert id="add" parameterType="Bill">
        insert into smbms_bill(billCode,productName)
		values(#{billCode},#{productName})
    </insert>

    <select id="getBillList" resultType="Bill">
        select b.*,p.proName as providerName from smbms_bill b, smbms_provider p
        <trim prefix="where b.providerId=p.id" prefixOverrides="and | or">
            <if test="productName!=null and productName!=''">
                and productName like CONCAT('%',#{productName},'%')
            </if>
            <if test="providerId!=null and providerId!=''">
                and providerId=#{providerId}
            </if>
            <if test="isPayment!=null and isPayment!=''">
                and isPayment=#{isPayment}
            </if>
        </trim>
            limit #{from},#{pageSize}


    </select>

    <!--计数使用count(*)-->
    <select id="getBillCount" resultType="Integer">
        select count(1) from smbms_bill
        <where>
            <if test="productName != null">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>
    </select>


    <delete id="deleteBillById" parameterType="Integer">
        delete from smbms_bill where id = #{id};
    </delete>



    <select id="getBillById" resultType="Bill">
        select * from smbms_bill where id = #{id};
    </select>
    
    <select id="getProviderById" resultType="Provider">
        select * from smbms_bill where id = #{id}
    </select>
    
    <update id="modify" parameterType="Bill">
        update smbms_bill
        <trim prefix="set" suffixOverrides="," suffix="where id = #{id}">
            <if test="billCode!=null">billCode=#{billCode},</if>
            <if test="productName!=null">productName=#{productName},</if>
            <if test="productDesc!=null">productDesc=#{productDesc},</if>
            <if test="productUnit!=null">proContact=#{productUnit},</if>
            <if test="productCount!=null">productCount=#{productCount},</if>
            <if test="totalPrice!=null">totalPrice=#{totalPrice},</if>
            <if test="isPayment!=null">isPayment=#{isPayment},</if>
            <if test="providerId!=null">providerId=#{providerId},</if>
            <if test="createdBy!=null">createdBy=#{createdBy},</if>
            <if test="creationDate!=null">creationDate=#{creationDate},</if>
            <if test="modifyBy!=null">modifyBy=#{modifyBy},</if>
            <if test="modifyDate!=null">modifyDate=#{modifyDate},</if>
            <if test="providerName!=null">providerName=#{providerName}</if>
        </trim>

    </update>

    <delete id="deleteBillByProviderId" parameterType="Integer">
        delete from smbms_bill where providerId = #{providerId};
    </delete>

</mapper>

测试代码

RoleTest

package com.duxuan;

import com.duxuan.dao.RoleMapper;
import com.duxuan.pojo.Role;
import com.duxuan.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;


import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;


public class RoleTest {


    //封装获取当前时间的函数
    public static Date getCurrentDate() throws ParseException {
        Date date=new Date();//获取当前的时间
        SimpleDateFormat sd=new SimpleDateFormat("yyyy:MM:dd HH:mm:ss");//用这种格式去格式化，与数据库里的格式保持一致
        /*System.out.println(sd.format(date));*/
        String format = sd.format(date); //转成String
        Date parse = sd.parse(format);//转回Date
        return parse;
    }

    /*测试一*/
    @Test
    public void getRoleList(){
        SqlSession session = MybatisUtils.getSession();
        RoleMapper mapper = session.getMapper(RoleMapper.class);
        List<Role> roleList = mapper.getRoleList();
        for (Role role : roleList) {
            System.out.println(role);
        }
        session.close();
    }

    @Test
    public void add() throws ParseException {
        SqlSession session = MybatisUtils.getSession();
        RoleMapper mapper = session.getMapper(RoleMapper.class);
        mapper.add(new Role(4,"SMBMS_Boss","老板",1,getCurrentDate(),null,null));
        session.close();
    }

    @Test
    public void deleteRoleById(){
        SqlSession session = MybatisUtils.getSession();
        RoleMapper mapper = session.getMapper(RoleMapper.class);
        mapper.deleteRoleById(4);
        session.close();
    }

    @Test
    public void modify() throws ParseException {
        SqlSession session = MybatisUtils.getSession();
        RoleMapper mapper = session.getMapper(RoleMapper.class);
        mapper.modify(new Role(1,null,null,null,null,2,getCurrentDate()));
        session.close();
    }

    @Test
    public void getRoleById(){
        SqlSession session = MybatisUtils.getSession();
        RoleMapper mapper = session.getMapper(RoleMapper.class);
        Role role = mapper.getRoleById(2);
        System.out.println(role);
        session.close();
    }

    @Test
    public void roleCodeIsExist(){
        SqlSession session = MybatisUtils.getSession();
        RoleMapper mapper = session.getMapper(RoleMapper.class);
        int i = mapper.roleCodeIsExist("SMBMS_EMPLOYEE1");
        System.out.println(i);
        session.close();
    }
}

UserTest

package com.duxuan;

import com.duxuan.dao.UserMapper;
import com.duxuan.pojo.User;
import com.duxuan.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;


import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

public class UserTest {

    //封装获取当前时间的函数
    public static Date getCurrentDate() throws ParseException {
        Date date=new Date();//获取当前的时间
        SimpleDateFormat sd=new SimpleDateFormat("yyyy:MM:dd HH:mm:ss");//用这种格式去格式化，与数据库里的格式保持一致
        /*System.out.println(sd.format(date));*/
        String format = sd.format(date); //转成String
        Date parse = sd.parse(format);//转回Date
        return parse;
    }

    @Test
    public void getLoginUser(){
        SqlSession session = MybatisUtils.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = mapper.getLoginUser("wangyang");
        System.out.println(user);
        session.close();
    }

    @Test
    public void add() throws ParseException {
        SqlSession session = MybatisUtils.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        HashMap map = new HashMap();
        map.put("id",16);
        map.put("userCode","duxuan");
        map.put("userName","杜轩");
        map.put("userPassword","root");
        map.put("gender",1);
        map.put("birthday",null);
        map.put("phone","110");
        map.put("address","上海");
        map.put("userRole",3);
        map.put("createdBy",1);
        map.put("creationDate",getCurrentDate());
        mapper.add(map);
        session.close();
    }

    @Test
    public void getUserList(){
        SqlSession session = MybatisUtils.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        List<User> users = mapper.getUserList("孙", 3, 0, 30);
        for (User user : users) {
            System.out.println(user.toString());
        }
        session.close();
    }

    @Test
    public void getUserCount(){
        SqlSession session = MybatisUtils.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        int times = mapper.getUserCount("邓超", 3);
        System.out.println(times);
        session.close();
    }

    @Test
    public void deleteUserById(){
        SqlSession session = MybatisUtils.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        mapper.deleteUserById(16);
        session.close();
    }

    @Test
    public void getUserById(){
        SqlSession session = MybatisUtils.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = mapper.getUserById(15);
        System.out.println(user);
        session.close();
    }

    @Test
    public void modify(){
        SqlSession session = MybatisUtils.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        mapper.modify(new User(16,null,"杜轩1",null,null,null,null,null,null,null,null,null,null,null));
        session.close();
    }

    @Test
    public void updatePwd(){
        SqlSession session = MybatisUtils.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        mapper.updatePwd(16,"root");
        session.close();
    }
}

ProviderTest

package com.duxuan;

import com.duxuan.dao.ProviderMapper;
import com.duxuan.pojo.Provider;
import com.duxuan.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.text.ParseException;
import java.util.List;

public class ProviderTest {

    @Test
    public void add() throws ParseException {
        SqlSession session = MybatisUtils.getSession();
        ProviderMapper mapper = session.getMapper(ProviderMapper.class);


        mapper.add( new Provider("12312", "温州皮革厂"));
        session.close();

    }

    /*测试一*/
    @Test
    public void getProviderList(){
        SqlSession session = MybatisUtils.getSession();
        ProviderMapper mapper = session.getMapper(ProviderMapper.class);
        //模糊查询
        List<Provider> providerList = mapper.getProviderList("北京", "BJ", 0, 17);
        for (Provider provider : providerList) {
            System.out.println(provider.toString());
        }
        session.close();
    }

    /*测试一*/
    @Test
    public void getProList(){
        SqlSession session = MybatisUtils.getSession();
        ProviderMapper mapper = session.getMapper(ProviderMapper.class);
        List<Provider> proList = mapper.getProList();
        for (Provider provider : proList) {
            System.out.println(provider.toString());
        }
        session.close();
    }

    @Test
    public void getProviderCount(){
        SqlSession session = MybatisUtils.getSession();
        ProviderMapper mapper = session.getMapper(ProviderMapper.class);
        int providerCount = mapper.getProviderCount("深圳", "GZ");
        System.out.println(providerCount);
        session.close();
    }

    @Test
    public void deleteProviderById(){
        SqlSession session = MybatisUtils.getSession();
        ProviderMapper mapper = session.getMapper(ProviderMapper.class);
        mapper.deleteProviderById(16);
        session.close();
    }

    @Test
    public void getProviderById(){
        SqlSession session = MybatisUtils.getSession();
        ProviderMapper mapper = session.getMapper(ProviderMapper.class);
        Provider provider = mapper.getProviderById(1); //利用的是反射 实体类里必须要有全参的构造函数
        System.out.println(provider);
        session.close();
    }

    @Test
    public void modify() {
        SqlSession session = MybatisUtils.getSession();
        ProviderMapper mapper = session.getMapper(ProviderMapper.class);
        mapper.modify(new Provider(18,"AH_GYS003","安徽皮革厂",null,null,null,null,null,null,null,null,null));
        session.close();
    }




}

BillTest

package com.duxuan;

import com.duxuan.dao.BillMapper;
import com.duxuan.pojo.Bill;
import com.duxuan.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class BillTest {


    @Test
    public void getBillCountByProviderId(){
        SqlSession session = MybatisUtils.getSession();
        BillMapper mapper = session.getMapper(BillMapper.class);
        int times = mapper.getBillCountByProviderId(13);
        System.out.println(times);
        session.close();
    }

    @Test
    public void add(){
        SqlSession session = MybatisUtils.getSession();
        BillMapper mapper = session.getMapper(BillMapper.class);
       mapper.add(new Bill(19,null,"杜1",null,null,null,null,null,null,null,null,null,null,null));
        session.close();
    }

    @Test
    public void getBillList(){
        SqlSession session = MybatisUtils.getSession();
        BillMapper mapper = session.getMapper(BillMapper.class);
        mapper.getBillList("橄榄油","7","2",0,10);
        session.close();
    }

    @Test
    public void getBillCount(){
        SqlSession session = MybatisUtils.getSession();
        BillMapper mapper = session.getMapper(BillMapper.class);
        int count = mapper.getBillCount("橄榄油", "7", "2");
        System.out.println(count);
        session.close();
    }

    @Test
    public void deleteBillById(){
        SqlSession session = MybatisUtils.getSession();
        BillMapper mapper = session.getMapper(BillMapper.class);
        mapper.deleteBillById(19);
        session.close();
    }

    @Test
    public void getBillById(){
        SqlSession session = MybatisUtils.getSession();
        BillMapper mapper = session.getMapper(BillMapper.class);
        Bill billById = mapper.getBillById(1);
        System.out.println(billById.toString());
        session.close();
    }

    @Test
    public void modify(){
        SqlSession session = MybatisUtils.getSession();
        BillMapper mapper = session.getMapper(BillMapper.class);
        mapper.modify(new Bill(20,null,"杜轩22",null,null,null,null,null,null,null,null,null,null,null));
        session.close();
    }

    @Test
    public void deleteBillByProviderId(){
        SqlSession session = MybatisUtils.getSession();
        BillMapper mapper = session.getMapper(BillMapper.class);
        int i = mapper.deleteBillByProviderId(20);
        System.out.println(i);
        session.close();
    }
}

配置文件

MybatisUtils

package com.duxuan.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;
    static {
        String resource = "mybatis-config.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSession(){
       return sqlSessionFactory.openSession(true);
    }
}

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>
    <!--引入外部配置文件 优先使用外部配置文件而不是property-->
    <properties resource="dp.properties"/>

    
    <settings>
        <!--标准地日志工厂实现-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <setting name="mapUnderscoreToCamelCase" value="true"/> <!--开启驼峰命名转换-->
        <!--显示的开启缓存-->
        <setting name="cacheEnabled" value="true"></setting>
    </settings>
    
    <typeAliases>
        <package name="com.duxuan.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>
        <mapper class="com.duxuan.dao.UserMapper"/>
        <mapper class="com.duxuan.dao.BillMapper"/>
        <mapper class="com.duxuan.dao.RoleMapper"/>
        <mapper class="com.duxuan.dao.ProviderMapper"/>
    </mappers>
</configuration>

dp.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/smbms?useSSL=true&useUnicode=true&characterEncoding=UTF-8
username=root
password=root

log4j.properties

### set log levels ###
log4j.rootLogger = debug , console, file

### 输出到控制台 ###
log4j.appender.console =  org.apache.log4j.ConsoleAppender
log4j.appender.console.Target =  System.out
log4j.appender.console.Threshold =  DEBUG
log4j.appender.console.layout =  org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern =  [%c]-%m%n

### 输出到日志文件相关设置 ###
log4j.appender.file =  org.apache.log4j.RollingFileAppender
log4j.appender.file.File =  ./logs/duxuan.log
log4j.appender.file.MaxFileSize =  10mb
log4j.appender.file.Threshold =  DEBUG ## 输出DEBUG级别以上的日志
log4j.appender.file.layout =  org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern = [%p][%d{yy-MM-dd}][%c]%m%n

### 日志输出级别 ###
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG