Mybatis动态SQL（狂神说学习笔记）29道练习题

以下代码分为工具类、几个配置文件（mybatis-config.xml）、实体类、持久层（mapper映射文件）、测试类，代码我自己写完全部运行一遍，没有BUG。

一、工具类
MybatisUtils

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;/**获得 SqlSession 的实例*/
public class MybatisUtils {//既然有了 SqlSessionFactory，顾名思义，我们可以从中获得 SqlSession 的实例.// SqlSession 提供了在数据库执行 SQL 命令所需的所有方法。public static SqlSession getSqlSession(){InputStream inputStream = null;try {String resource = "mybatis-config.xml";inputStream = Resources.getResourceAsStream(resource);} catch (IOException e) {e.printStackTrace();}SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);return sqlSessionFactory.openSession(true);//默认提交事务，这样可以省略sqlSession.commit()该提交事务的代码}
}

二、配置文件：
配置文件和Mapper映射文件需要注意一点：如果出现中文注释引起程序出现异常的问题，我是把文件中的第一行的代码改成了“encoding=“utf8””，原本代码是设置UTF-8。
mybatis-config.xml

<?xml version="1.0" encoding="utf8" ?>
<!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration><properties resource="db.properties"/><settings><setting name="logImpl" value="STDOUT_LOGGING"/><setting name="mapUnderscoreToCamelCase" value="true"/></settings><typeAliases><!--     这里需要根据每个人创建的包名去修改   --><package name="com.hwx.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.hwx.dao.user.UserMapper"/><mapper class="com.hwx.dao.role.RoleMapper"/><mapper class="com.hwx.dao.provider.ProviderMapper"/><mapper class="com.hwx.dao.bill.BillMapper"/></mappers></configuration>

db.properties:这里根据自己设置的账号密码数据库名字去修改

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

log4j.properties:

#将等级为DEBUG的日志信息输出到console和file这两个目的地，console和file的定义在下面的代码
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=./log/rzp.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=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.sq1.PreparedStatement=DEBUG

三、实体类：
User类

package com.hwx.pojo;import java.io.Serializable;
import java.util.Date;
/**用户*/
public class User implements Serializable {//idprivate 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() {}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, String userRoleName) {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;this.userRoleName = 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) {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;}public Integer getAge() {/*Long time = System.currentTimeMillis( ) -birthday. getTime();Integer age = Long.valueof(time/365/24/60/60/1ee0).IntegerVaLue();*/Date date = new Date();Integer age = date.getYear()-birthday.getYear();return age;}public void setAge(Integer age) {this.age = age;}public String getUserRoleName() {return userRoleName;}public void setUserRoleName(String userRoleName) {this.userRoleName = 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;}
}

Role类

package com.hwx.pojo;
import java.util.Date;
/**角色*/
public class Role {//idprivate Integer 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;}
}

Provider类

package com.hwx.pojo;import java.util.Date;
/**供应商*/
public class Provider {private Integer id;   //idprivate 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;}
}

Bill类

package com.hwx.pojo;
import java.math.BigDecimal;
import java.util.Date;
/**订单*/
public class Bill {private Integer id;   //idprivate String billCode; //账单编码private String productName; //商品名称private String productDesc; //商品描述private String productUnit; //商品单位private BigDecimal productCount; //商品数量private BigDecimal totalPrice; //总金额private Integer isPayment; //是否支付private Integer providerId; //供应商IDprivate Integer createdBy; //创建者private Date creationDate; //创建时间private Integer modifyBy; //更新者private Date modifyDate;//更新时间private String providerName;//供应商名称public String getProviderName() {return providerName;}public void setProviderName(String providerName) {this.providerName = 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;}
}

四、持久层：
UserMapper.java：

package com.hwx.dao.user;import com.hwx.pojo.User;
import org.apache.ibatis.annotations.Param;import java.util.List;
import java.util.Map;/**
* 用户接口
*/
public interface UserMapper {//得到要登录的用户信息User getLoginUser(@Param("userCode") String userCode);//根据条件 查询 获取用户列表List<User> getUserList(@Param("userName") String userName,@Param("userRole") int userRole,@Param("currentPageNo") int currentPageNo,@Param("pageSize") int pageSize);//修改密码int updatePassword(@Param("id") int id,@Param("userPassword") String password);//根据用户名 或 角色 查询用户总数int getUserCounts(@Param("userName") String userName,@Param("userRole") int userRole);//用户管理模块中的  添加用户int addUser(User user);//用户管理模块中的  添加用户int addUserMap(Map<String, Object> params);//用户管理模块中的 删除用户int deleteUser(@Param("id") int delId);//根据用户id 查询用户信息User getUserById(@Param("id") int id);//用户管理模块中的 修改用户信息int modifyUser(User user);//用户管理模块中的  修改用户信息int modifyUserMap(Map<String, Object> params);}

UserMapper.xml：

<?xml version="1.0" encoding="utf8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hwx.dao.user.UserMapper"><select id="getLoginUser" resultType="user">select * from smbms_user <where><if test="userCode !=null">userCode=#{userCode}</if></where></select><resultMap id="userList" type="user"><result property="id" column="id"/><result property="userCode" column="userCode"/><result property="userName" column="userName"/><result property="phone" column="phone"/><result property="birthday" column="birthday"/><result property="gender" column="gender"/><result property="userRole" column="userRole"/><result property="userRoleName" column="roleName"/></resultMap><select id="getUserById" resultType="user">select * from smbms_user<where><if test="id != null">id = #{id}</if></where></select><select id="getUserCounts" resultType="int">select count(1)  from smbms_user u,smbms_role r where u.userRole = r.id<if test="userRole !=null">and u.userRole like CONCAT('%',#{userRole},'%')</if><if test="userName !=null">and u.userName like CONCAT('%',#{userName},'%')</if></select><update id="updatePassword" parameterType="String">update smbms_user<set>userPassword=#{userPassword} where id=#{id}</set></update><select id="getUserList"  resultType="user">select u.*,r.roleName as userRoleName from smbms_user u,smbms_role rwhere u.userRole = r.id<if test="userName != null">and u.userName like concat('%',#{userName},'%')</if><if test="userRole !=null">and u.userRole like concat('%',#{userRole},'%')</if>order by u.creationDate DESC limit #{currentPageNo},#{pageSize}</select><insert id="addUser" parameterType="user">insert into smbms_user (id,userCode,userName,userPassword,gender,birthday,phone,address,userRole,createdBy,creationDate)values (#{id},#{userCode},#{userName},#{userPassword},#{gender},#{birthday},#{phone},#{address},#{userRole},#{createdBy},#{creationDate,jdbcType=TIMESTAMP})</insert><insert id="addUserMap" parameterType="map" >insert into smbms_user (id,userCode,userName,userPassword,gender,birthday,phone,address,userRole,createdBy,creationDate)values (#{id},#{userCode},#{userName},#{userPassword},#{gender},#{birthday},#{phone},#{address},#{userRole},#{createdBy},#{creationDate,jdbcType=TIMESTAMP})</insert><delete id="deleteUser" parameterType="int">delete from smbms_user where id = #{id}</delete><update id="modifyUser" parameterType="user">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="modifyBy!=null">modifyBy=#{modifyBy},</if><if test="modifyDate!=null">modifyDate=#{modifyDate}</if></trim></update><update id="modifyUserMap" parameterType="map">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="modifyBy!=null">modifyBy=#{modifyBy},</if><if test="modifyDate!=null">modifyDate=#{modifyDate}</if></trim></update></mapper>

RoleMapper.java：

package com.hwx.dao.role;import com.hwx.pojo.Role;
import org.apache.ibatis.annotations.Param;import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;/**
* 角色接口
*/
public interface RoleMapper {//获取角色列表List<Role> getRoleList();//增加角色信息int addRole(Role role);//增加角色信息  使用Map作为参数传入int addRoleMap(Map<String, Object> params);//通过Id删除角色int deleteRoleById(@Param("id") int delId);//修改角色信息int modifyRole(Role role);//修改角色信息 使用Map作为参数传入int modifyRoleMap(Map<String, Object> params);//通过Id获取roleRole getRoleById(@Param("id") int id);//根据roleCode，进行角色编码的唯一性验证int roleCodeIsExist(@Param("roleCode") String roleCode);}

`RoleMapper.xml`：
```

<?xml version="1.0" encoding="utf8" ?>

<select id="getRoleList" resultType="role">select * from smbms_role
</select><insert id="addRole" parameterType="role">insert into smbms_role (id,roleCode,roleName,createdBy,creationDate)values (#{id},#{roleCode},#{roleName},#{createdBy},#{creationDate,jdbcType=TIMESTAMP})
</insert><insert id="addRoleMap" parameterType="map" >insert into smbms_role (id,roleCode,roleName,createdBy,creationDate)values (#{id},#{roleCode},#{roleName},#{createdBy},#{creationDate,jdbcType=TIMESTAMP})
</insert><delete id="deleteRoleById" parameterType="int">delete from smbms_role where id = #{id}
</delete><select id="getRoleById" resultType="role">select * from smbms_role<where><if test="id != null">id = #{id}</if></where>
</select><update id="modifyRole" parameterType="role">update smbms_role<trim prefix="set" suffixOverrides="," suffix="where id=#{id}"><if test="roleCode!=null">roleCode=#{roleCode},</if><if test="roleName!=null">roleName=#{roleName},</if><if test="modifyBy!=null">modifyBy=#{modifyBy},</if><if test="modifyDate!=null">modifyDate=#{modifyDate}</if></trim>
</update><update id="modifyRoleMap" parameterType="map">update smbms_role<trim prefix="set" suffixOverrides="," suffix="where id=#{id}"><if test="roleCode!=null">roleCode=#{roleCode},</if><if test="roleName!=null">roleName=#{roleName},</if><if test="modifyBy!=null">modifyBy=#{modifyBy},</if><if test="modifyDate!=null">modifyDate=#{modifyDate}</if></trim>
</update><select id="roleCodeIsExist" resultType="int">select count(1) as count from smbms_role where roleCode=#{roleCode}
</select>

```

ProviderMapper.java：

package com.hwx.dao.provider;import com.hwx.pojo.Provider;
import org.apache.ibatis.annotations.Param;import java.util.List;
import java.util.Map;/**
* 供应商接口
*/
public interface ProviderMapper {//通过条件查询providerListList<Provider> getProviderList(@Param("proName") String proName,@Param("proCode") String proCode,@Param("currentPageNo") int currentPageNo,@Param("pageSize") int pageSize);//获取供应商列表List<Provider> getProList();//增加供应商信息int addProvider(Provider provider);//增加供应商信息Mapint addProviderMap(Map<String, Object> params);//通过条件查询供应商记录数int getProviderCounts(@Param("proName") String proName,@Param("proCode") String proCode);//通过供应商Id删除供应商信息int deleteProviderById(@Param("id") int delId);//根据供应商Id获取供应商信息Provider getProviderById(@Param("id") int id);//修改供应商int modifyProvider(Provider provider);//修改供应商int modifyProviderMap(Map<String, Object> params);;}

ProviderMapper.xml：

<?xml version="1.0" encoding="utf8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hwx.dao.provider.ProviderMapper"><select id="getProList" resultType="provider">select * from smbms_provider</select><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 DESClimit #{currentPageNo},#{pageSize}</select><select id="getProviderById" resultType="provider">select * from smbms_provider<where><if test="id != null">id = #{id}</if></where></select><select id="getProviderCounts" resultType="int">select count(1) as count from smbms_provider<where><if test="proCode!=null and proCOde!=''">and proCode like CONCAT('%',#{proCode},'%')</if><if test="proName!=null and proName!=''">and proName like CONCAT('%',#{proName},'%')</if></where></select><insert id="addProvider" parameterType="provider">insert into smbms_provider(id,proCode,proName,proDesc,proContact,proPhone,proAddress,proFax,createdBy,creationDate)values(#{id},#{proCode},#{proName},#{proDesc},#{proContact},#{proPhone},#{proAddress},#{proFax},#{createdBy},#{creationDate,jdbcType=TIMESTAMP})</insert><insert id="addProviderMap" parameterType="map" >insert into smbms_provider(id,proCode,proName,proDesc,proContact,proPhone,proAddress,proFax,createdBy,creationDate)values(#{id},#{proCode},#{proName},#{proDesc},#{proContact},#{proPhone},#{proAddress},#{proFax},#{createdBy},#{creationDate,jdbcType=TIMESTAMP})</insert><delete id="deleteProviderById" parameterType="int">delete from smbms_provider where id = #{id}</delete><update id="modifyProvider" parameterType="provider">update smbms_provider<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="modifyBy!=null">modifyBy=#{modifyBy},</if><if test="modifyDate!=null">modifyDate=#{modifyDate}</if></trim></update><update id="modifyProviderMap" parameterType="map">update smbms_provider<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="modifyBy!=null">modifyBy=#{modifyBy},</if><if test="modifyDate!=null">modifyDate=#{modifyDate}</if></trim></update></mapper>

BillMapper.java：

package com.hwx.dao.bill;import com.hwx.pojo.Bill;
import org.apache.ibatis.annotations.Param;import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;/**
* 订单
*/
public interface BillMapper {//根据 商品名称、供应商id、是否付款 查询订单列表List<Bill> getBillList(@Param("productName") String productName,@Param("providerId") int providerId,@Param("isPayment") int isPayment,@Param("currentPageNo") int currentPageNo,@Param("pageSize") int pageSize);//根据订单id 获取订单信息Bill getBillById(@Param("id") int billId);//根据 商品名称、供应商id、是否付款 查询订单总数int getBillCounts(@Param("productName") String productName,@Param("providerId") int providerId,@Param("isPayment") int isPayment);//根据供应商Id查询订单数量int getBillCountByProviderId(@Param("providerId") int providerId);//添加订单int addBill(Bill bill);//添加订单 使用Map作为参数传入int addBillMap(Map<String,Object> params);//删除订单int deleteBill(@Param("id") int billId);//根据供应商Id删除订单信息int deleteBillByProviderId(@Param("providerId") int providerId);//修改订单信息int modifyBill(Bill bill);//修改订单信息 使用Map作为参数传入int modifyBillMap(Map<String,Object> params);}

BillMapper.xml：

<?xml version="1.0" encoding="utf8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hwx.dao.bill.BillMapper"><select id="getBillList" resultType="bill">select b.*,p.proName as providerName from smbms_bill b,smbms_provider pwhere b.providerId=p.id<if test="productName != null and productName != ''">and productName like CONCAT('%',#{productName},'%')</if><if test="providerId != null">and providerId=#{providerId}</if><if test="isPayment != null">and isPayment = #{isPayment}</if>order by creationDate DESClimit #{currentPageNo},#{pageSize}</select><select id="getBillById" resultType="bill">select * from smbms_bill<where><if test="id != null">id = #{id}</if></where></select><select id="getBillCountByProviderId" resultType="int">select count(1) as count from smbms_bill<where><if test=" providerId != null">providerId = #{providerId}</if></where></select><select id="getBillCounts" resultType="int">select count(1) as count 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 = #{providerId}</if><if test="isPayment != null">and isPayment = #{isPayment}</if></trim></select><insert id="addBill" parameterType="bill">insert into smbms_bill(id,billCode,productName,productDesc,productUnit,productCount,totalPrice,isPayment,createdBy,creationDate,providerId)values(#{id},#{billCode},#{productName},#{productDesc},#{productUnit},#{productCount},#{totalPrice},#{isPayment},#{createdBy},#{creationDate,jdbcType=TIMESTAMP},#{providerId})</insert><insert id="addBillMap" parameterType="map">insert into smbms_bill(id,billCode,productName,productDesc,productUnit,productCount,totalPrice,isPayment,createdBy,creationDate,providerId)values(#{id},#{billCode},#{productName},#{productDesc},#{productUnit},#{productCount},#{totalPrice},#{isPayment},#{createdBy},#{creationDate,jdbcType=TIMESTAMP},#{providerId})</insert><delete id="deleteBill" parameterType="int">delete from smbms_bill where id = #{id}</delete><delete id="deleteBillByProviderId" parameterType="int">delete from smbms_bill where providerId = #{providerId}</delete><update id="modifyBill" 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">productUnit = #{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="modifyBy != null">modifyBy =#{modifyBy},</if><if test="modifyDate != null">modifyDate = #{modifyDate},</if><if test="providerId != null">providerId = #{providerId}</if></trim></update><update id="modifyBillMap" parameterType="map">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">productUnit = #{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="modifyBy != null">modifyBy =#{modifyBy},</if><if test="modifyDate != null">modifyDate = #{modifyDate},</if><if test="providerId != null">providerId = #{providerId}</if></trim></update>
</mapper>

五、测试类：
UserTest:

package com.hwx.test;import com.hwx.dao.user.UserMapper;
import com.hwx.pojo.User;
import com.hwx.util.MybatisUtils;import org.apache.ibatis.session.SqlSession;
import org.junit.Test;import java.util.*;/**
用户测试
*/
public class UserTest {@Testpublic void getLoginUser(){//根据用户编码查询用户//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();UserMapper userMapper = sqlSession.getMapper(UserMapper.class);User user = userMapper.getLoginUser("test");System.out.println(user);//关闭SqlSessionsqlSession.close();}@Testpublic void getUserById(){//根据ID查询用户//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();UserMapper userMapper = sqlSession.getMapper(UserMapper.class);User user = userMapper.getUserById(8);System.out.println(user);//关闭SqlSessionsqlSession.close();}@Testpublic void getUserCounts(){//根据用户编码查询用户数量//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();UserMapper userMapper = sqlSession.getMapper(UserMapper.class);int number = userMapper.getUserCounts("测试",1);System.out.println(number);//关闭SqlSessionsqlSession.close();}@Testpublic void updatePassword(){//根据ID修改用户密码//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();UserMapper userMapper = sqlSession.getMapper(UserMapper.class);int number = userMapper.updatePassword(8,"123456");System.out.println(number);//提交：因为我在 sqlSessionFactory.openSession(true);//默认提交事务，所有这里不用再提交了//关闭SqlSessionsqlSession.close();}@Testpublic void getUserList(){//获取用户//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();List<User> userList = new ArrayList<User>();UserMapper userMapper = sqlSession.getMapper(UserMapper.class);userList= userMapper.getUserList("管理员",1,1,5);for (User user : userList) {System.out.println(user);}//关闭SqlSessionsqlSession.close();}@Testpublic void addUser(){//新增用户//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//        User user =new User(28, "hhh", "哈哈哈", "123456",
//                1, new Date(), "17779674555", "中国",
//                2, 1, new Date());
//        int number = userMapper.addUser(user);//另外一种：Map<String, Object> params = new HashMap<String, Object>();Date date = new Date();params.put("id", "26");params.put("userCode", "hhh");params.put("userName", "哈哈哈");params.put("userPassword", "123456");params.put("gender", "1");params.put("birthday", "2016-09-30");params.put("phone", "17779674555");params.put("address", "中国");params.put("userRole", "2");params.put("createdBy", "1");params.put("creationDate", date.toLocaleString());int number = userMapper.addUserMap(params);
//        System.out.println(number);//关闭SqlSessionsqlSession.close();}@Testpublic void modifyUserMap(){//修改用户modifyUserMap//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();UserMapper userMapper = sqlSession.getMapper(UserMapper.class);Map<String, Object> params = new HashMap<String, Object>();Date date = new Date();params.put("id", "26");params.put("userCode", "hhh");params.put("userName", "哈");params.put("userPassword", "111111");params.put("gender", "1");params.put("birthday", "2016-09-30");params.put("phone", "13689674534");params.put("address", "中国");params.put("userRole", "2");params.put("modifyBy", "1");params.put("modifyDate", date.toLocaleString());int number = userMapper.modifyUserMap(params);System.out.println(number);//关闭SqlSessionsqlSession.close();}@Testpublic void deleteUser() {//删除用户//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();UserMapper userMapper = sqlSession.getMapper(UserMapper.class);int number = userMapper.deleteUser(4);System.out.println(number);//关闭SqlSessionsqlSession.close();}}
;

RoleTest：

package com.hwx.test;import com.hwx.dao.role.RoleMapper;
import com.hwx.pojo.Role;
import com.hwx.util.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;import java.util.*;/**
* 角色测试
*/
public class RoleTest {@Testpublic void getRoleList() {//获取角色//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();List<Role> roleList = new ArrayList<Role>();RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);roleList = roleMapper.getRoleList();for (Role role : roleList) {System.out.println(role);}//关闭SqlSessionsqlSession.close();}@Testpublic void addRole() {//新增角色//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);Map<String, Object> params = new HashMap<String, Object>();Date date = new Date();params.put("id", "4");params.put("roleCode", "SMBMS_DELIVERY");params.put("roleName", "配送员");params.put("createdBy", "1");params.put("creationDate", date.toLocaleString());int number = roleMapper.addRoleMap(params);System.out.println(number);//关闭SqlSessionsqlSession.close();}@Testpublic void deleteRoleById() {//删除角色//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);int number = roleMapper.deleteRoleById(4);System.out.println(number);//关闭SqlSessionsqlSession.close();}@Testpublic void getRoleById(){//根据ID查询角色//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);Role role = roleMapper.getRoleById(1);System.out.println(role);//关闭SqlSessionsqlSession.close();}@Testpublic void modifyRoleMap(){//修改角色信息//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);Map<String, Object> params = new HashMap<String, Object>();Date date = new Date();params.put("id", "4");params.put("roleCode", "SMBMS_");params.put("roleName", "SMBMS_DELIVERY");params.put("modifyBy", "1");params.put("modifyDate", date.toLocaleString());int number = roleMapper.modifyRoleMap(params);System.out.println(number);//关闭SqlSessionsqlSession.close();}@Testpublic void roleCodeIsExist(){//验证角色//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);int number = roleMapper.roleCodeIsExist("SMBMS_ADMIN");System.out.println(number);//关闭SqlSessionsqlSession.close();}}

ProviderTest：

package com.hwx.test;import com.hwx.dao.provider.ProviderMapper;
import com.hwx.pojo.Provider;
import com.hwx.util.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;import java.util.*;/**
*供应商测试
*/
public class ProviderTest {@Testpublic void getProList(){//查询所有供应商信息//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();ProviderMapper providerMapper = sqlSession.getMapper(ProviderMapper.class);List<Provider> providerList = new ArrayList<Provider>();providerList= providerMapper.getProList();for (Provider provider : providerList) {System.out.println(provider);}//关闭SqlSessionsqlSession.close();}@Testpublic void getProviderList(){//根据条件查询供应商信息//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();ProviderMapper providerMapper = sqlSession.getMapper(ProviderMapper.class);List<Provider> providerList = new ArrayList<Provider>();providerList= providerMapper.getProviderList("兴化佳美调味品厂","JS_GYS001",1,2);for (Provider provider : providerList) {System.out.println(provider);}//关闭SqlSessionsqlSession.close();}@Testpublic void addProviderMap(){//新增供应商//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();ProviderMapper providerMapper = sqlSession.getMapper(ProviderMapper.class);Map<String, Object> params = new HashMap<String, Object>();Date date = new Date();params.put("id", "16");params.put("proCode", "GZ_GYS156");params.put("proName", "广州市白云胡华五金制品厂");params.put("proDesc", "长期合作伙伴，主营产品：海绵床垫、坐垫、靠垫、海绵枕头、头枕等");params.put("proContact", "孙欣弈");params.put("proPhone", "17779674555");params.put("proAddress", "中国北京市大兴区旧宫");params.put("proFax", "010-35576785");params.put("createdBy", "1");params.put("creationDate", date.toLocaleString());int number = providerMapper.addProviderMap(params);System.out.println(number);//关闭SqlSessionsqlSession.close();}@Testpublic void getProviderCounts(){//查询供应商信息的数量//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();ProviderMapper providerMapper = sqlSession.getMapper(ProviderMapper.class);int number = providerMapper.getProviderCounts("深圳市喜来客商贸有限公司","GZ_GYS002");System.out.println(number);//关闭SqlSessionsqlSession.close();}@Testpublic void deleteProviderById(){//删除供应商信息//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();ProviderMapper providerMapper = sqlSession.getMapper(ProviderMapper.class);int number = providerMapper.deleteProviderById(16);System.out.println(number);//关闭SqlSessionsqlSession.close();}@Testpublic void getProviderById(){//根据ID查询供应商信息//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();ProviderMapper providerMapper = sqlSession.getMapper(ProviderMapper.class);Provider provider = providerMapper.getProviderById(2);System.out.println(provider.toString());//关闭SqlSessionsqlSession.close();}@Testpublic void modifyProviderMap(){//修改供应商信息//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();ProviderMapper providerMapper = sqlSession.getMapper(ProviderMapper.class);Map<String, Object> params = new HashMap<String, Object>();Date date = new Date();params.put("id", "16");params.put("proCode", "GZ_GYS155");params.put("proName", "广州市白云胡华五金制品厂");params.put("proDesc", "长期合作伙伴，主营产品：海绵床垫、坐垫、靠垫、海绵枕头、头枕等");params.put("proContact", "孙欣弈");params.put("proPhone", "17779674555");params.put("proAddress", "中国北京市大兴区旧宫");params.put("proFax", "010-35576785");params.put("modifyBy", "1");params.put("modifyDate", date.toLocaleString());int number = providerMapper.modifyProviderMap(params);System.out.println(number);//关闭SqlSessionsqlSession.close();}
}

BillTest：

package com.hwx.test;import com.hwx.dao.bill.BillMapper;
import com.hwx.pojo.Bill;
import com.hwx.util.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;import java.util.*;/**
* 订单测试
*/
public class BillTest {@Testpublic void getBillList() {//根据条件获取订单//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();List<Bill> billList = new ArrayList<Bill>();BillMapper billMapper = sqlSession.getMapper(BillMapper.class);/*** select b.*,p.proName as providerName from smbms_bill b,smbms_provider p* where b.providerId = p.id   and productName like CONCAT('%','大','%')*   and providerId=6 and isPayment=2* order by creationDate DESC* limit 0,1*/billList= billMapper.getBillList("大",6,2,0,1);for (Bill Bill : billList) {System.out.println(Bill);}//关闭SqlSessionsqlSession.close();}@Testpublic void getBillCounts() {//根据条件获取订单//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();BillMapper billMapper = sqlSession.getMapper(BillMapper.class);/*** select b.*,p.proName as providerName from smbms_bill b,smbms_provider p* where b.providerId = p.id   and productName like CONCAT('%','大','%')*   and providerId=6 and isPayment=2* order by creationDate DESC* limit 0,1*/int number = billMapper.getBillCounts("大",6,2);System.out.println(number);//关闭SqlSessionsqlSession.close();}@Testpublic void addBillMap(){//新增订单//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();BillMapper billMapper = sqlSession.getMapper(BillMapper.class);Map<String, Object> params = new HashMap<String, Object>();Date date = new Date();params.put("id", "26");params.put("billCode", "BILL2021_015");params.put("productName", "芦荟汁");params.put("productDesc", "干净又卫生");params.put("productUnit", "杯");params.put("productCount", "10");params.put("totalPrice", "19.9");params.put("isPayment", "2");params.put("createdBy", "1");params.put("creationDate", date.toLocaleString());params.put("providerId", "6");int number = billMapper.addBillMap(params);System.out.println(number);//关闭SqlSessionsqlSession.close();}@Testpublic void deleteBill(){//删除订单//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();BillMapper billMapper = sqlSession.getMapper(BillMapper.class);int number = billMapper.deleteBill(26);System.out.println(number);//关闭SqlSessionsqlSession.close();}@Testpublic void deleteBillByProviderId(){//根据ProviderId删除订单//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();BillMapper billMapper = sqlSession.getMapper(BillMapper.class);int number = billMapper.deleteBillByProviderId(11);System.out.println(number);//关闭SqlSessionsqlSession.close();}@Testpublic void modifyBillMap(){//修改订单//第一步：获得SqlSession对象SqlSession sqlSession = MybatisUtils.getSqlSession();BillMapper billMapper = sqlSession.getMapper(BillMapper.class);Map<String, Object> params = new HashMap<String, Object>();Date date = new Date();params.put("id", "25");params.put("billCode", "BILL2021_015");params.put("productName", "芦荟汁");params.put("productDesc", "干净又卫生");params.put("productUnit", "杯");params.put("productCount", "10");params.put("totalPrice", "9.9");params.put("isPayment", "2");params.put("modifyBy", "1");params.put("modifyDate", date.toLocaleString());params.put("providerId", "6");int number = billMapper.modifyBillMap(params);System.out.println(number);//关闭SqlSessionsqlSession.close();}
}

项目结构如下：
在这里插入图片描述