Mybatis08:练习29道练习题实战！

测试环境搭建

搭建实验数据库

创建bill表

CREATE TABLE `smbms_bill` (
              `id` int(10) NOT NULL, 
              `productName` varchar(50) NOT NULL,
              `isPayment` int(1) DEFAULT NULL,                                                                         `providerId` int(20) NOT NULL,                                                                           `providerName` varchar(40) NOT NULL,                                                                     PRIMARY KEY (`id`)                                                                                     ) ENGINE=InnoDB DEFAULT CHARSET=utf8

再pom文件中导入MyBatis相关 jar 包

mysql
mybatis
junit
lombok(可导可不导)

    <!--    导入maven依赖-->
    <dependencies>
<!--        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.6</version>
        </dependency>
<!--        junit-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
        </dependency>

    </dependencies>

编写MyBatis核心配置文件 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">
        <property name="pwd" value="root"/>
    </properties>
    
    <settings>
<!--        标准的日志工厂实现-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
<!--        LOG4J 实现-->
<!--        <setting name="logImpl" value="LOG4J"/>-->
<!--        是否开启驼峰命名自动映射-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

<!--    可以给实体类取别名-->
    <typeAliases>
        <package name="com.sen.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="${pwd}"/>
            </dataSource>
        </environment>

        <environment id="test">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=GMT%2B8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/sen/dao/BillMapper.xml"/>
    </mappers>
</configuration>

environment下的dataSource放在db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
username=root

编写MyBatis工具类

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 命令所需的所有方法。你可以通过 SqlSession 实例来直接执行已映射的 SQL 语句
//  openSession带参数    SqlSession openSession(boolean autoCommit);
//  设置自动提交参数
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession(true);
    }
}

新建实体类bill，provider,role,user

bill实体类

package com.sen.pojo;

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

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

建立Mapper接口

billMapper

package com.sen.dao;


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

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

public interface BillMapper {

	//增加订单
	public int add(Bill bill)throws Exception;

	//修改订单信息
	public int modify( Bill bill)throws Exception;

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

	//通过delId删除Bill
	public int deleteBillById(@Param("id") String delId)throws Exception;

	//通过查询条件获取供应商列表-模糊查询-getBillList
	public List<Bill> getBillList(Bill bill)throws Exception;

	//根据供应商id查询订单数量
	public int getBillCountByProviderId(@Param("providerId")Integer providerId)throws Exception;

	//通过条件查询-订单表记录数
//	public int getBillCount(@Param("productName")String productName,@Param("providerId")Integer providerId)throws Exception;
	public int getBillCount(Map<String,Object> map)throws Exception;

}

建立Mapper.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.sen.dao.BillMapper">
    <insert id="add" parameterType="bill">
        insert into mybatis.smbms_bill (id, billCode, productName, productDesc, productUnit, productCount, totalPrice, isPayment, createdBy, creationDate, modifyBy, modifyDate, providerId)
         values (#{id},
                 #{billCode},
                 #{productName},
                 #{productDesc},
                 #{productUnit},
                 #{productCount},
                 #{totalPrice},
                 #{isPayment},
                 #{createdBy},
                 #{creationDate},
                 #{modifyBy},
                 #{modifyDate},
                 #{providerId});
    </insert>

    <update id="modify" parameterType="bill">
        update mybatis.smbms_bill
        set 
            billCode = #{billCode},
            productName =#{productName},
            productDesc=#{productDesc},
            productUnit=#{productUnit},
            productCount=#{productCount},
            totalPrice=#{totalPrice},
            isPayment=#{isPayment},
            createdBy=#{createdBy},
            creationDate=#{creationDate},
            modifyBy=#{modifyBy},
            modifyDate=#{modifyDate},
            providerId=#{providerId}
        where id = #{id};
    </update>

    <delete id="deleteBillById"  >
        delete from mybatis.smbms_bill where id = #{id}
    </delete>

    <select id="getBillById" resultType="bill">
        select *
        from  mybatis.smbms_bill
        where id=#{id};
    </select>

    <select id="getBillCountByProviderId" parameterType="int" resultType="int">
        select sum(productCount) as billCount from mybatis.smbms_bill where providerId=#{providerId}
    </select>

    <select id="getBillCount" parameterType="map" resultType="int">
        select count(1) as billCount from mybatis.smbms_bill where providerId = #{providerId} and productName = #{productName}
    </select>

    <select id="getBillList" resultType="bill">

    </select>

</mapper>

在核心配置文件中绑定我们的Mapper接口或者文件！

<mappers>
    <mapper resource="com/sen/dao/BillMapper.xml"/>
</mappers>

测试查询是否能够成功！

posted @ 2021-02-06 19:59 森小森阅读(83) 评论(0) 收藏举报

刷新页面返回顶部

Mybatis08:练习29道练习题实战！

公告