项目--mapper层数据访问

mapper层-》数据存储对象，相当于dao层

mapper层直接与数据库打交道（执行sql语句）接口直接提供给service层。

where 1=1 动态sql拼接 

%表示0个或多个字符

1.实体类 entities

package com.cc.springboot.entities;

import java.util.Date;

/**
 * 供应商实体类
 * @Title: Provider
 * @Description: com.mengxuegu.springboot.entities
 * @Auther: www.mengxuegu.com
 * @Version: 1.0
 */

public class Provider {
    private Integer pid;
    //供应商编码
    private String providerCode;
    //供应商名称
    private String providerName;
    //联系人
    private String people;
    //联系电话
    private String phone;
    //联系地址
    private String address;
    //传真
    private String fax;
    //描述
    private String describe;
    // 创建时间
    private Date createDate;

    public Provider() {
    }

    public Provider(Integer pid, String providerCode, String providerName, String people, String phone, String address, String fax, String describe) {
        this.pid = pid;
        this.providerCode = providerCode;
        this.providerName = providerName;
        this.people = people;
        this.phone = phone;
        this.address = address;
        this.fax = fax;
        this.describe = describe;
        this.createDate = new Date();
    }

    @Override
    public String toString() {
        return "Provider{" +
                "pid=" + pid +
                ", providerCode='" + providerCode + '\'' +
                ", providerName='" + providerName + '\'' +
                ", people='" + people + '\'' +
                ", phone='" + phone + '\'' +
                ", address='" + address + '\'' +
                ", fax='" + fax + '\'' +
                ", describe='" + describe + '\'' +
                ", createDate=" + createDate +
                '}';
    }

    public Integer getPid() {
        return pid;
    }

    public void setPid(Integer pid) {
        this.pid = pid;
    }

    public String getProviderCode() {
        return providerCode;
    }

    public void setProviderCode(String providerCode) {
        this.providerCode = providerCode;
    }

    public String getProviderName() {
        return providerName;
    }

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

    public String getPeople() {
        return people;
    }

    public void setPeople(String people) {
        this.people = people;
    }

    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 String getFax() {
        return fax;
    }

    public void setFax(String fax) {
        this.fax = fax;
    }

    public String getDescribe() {
        return describe;
    }

    public void setDescribe(String describe) {
        this.describe = describe;
    }

    public Date getCreateDate() {
        return createDate;
    }

    public void setCreateDate(Date date) {
        this.createDate = date;
    }
}

知识点：getter和setter以及构造方法等不要忘记写，

2.创建接口 mapper/providerMapper

package com.cc.springboot.mapper;

import com.cc.springboot.entities.Provider;

import java.util.List;

//@Mapper 或 @MapperScan("package com.cc.springboot.mapper")
public interface ProviderMapper {

    List<Provider> getProviders(Provider provider);

    Provider getProviderByPid(Integer pid);

    int addProvider(Provider provider);

    int deleteProvider(Integer pid);

    int updateProvider(Provider provider);
}

 知识点：@Mapper 或在运行类添加@MapperScan()*建议

3.使用SQL语句 核心配置文件（resources/mybatis/mybatis-config.xml）和类配置文件（resources/mybatis/mapper/ProviderMapper.xml）

  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>
     <!--核心配置文件-->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
</configuration>

 知识点：setting 解决驼峰命名

ProviderMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cc.springboot.mapper.ProviderMapper">
      <!--模糊查询 搜索-->
      <select id="getProviders" resultType="com.cc.springboot.entities.Provider">
          select * from provider where 1=1
          <if test='providerName !="null" and providerName!=" "'>
              and providerName like '%${providerName}%'
          </if>
      </select>

     <!--通过id查询-->
    <select id="getProviderByPid"  resultType="com.cc.springboot.entities.Provider">
        select * from provider where pid=#{pid}
    </select>

      <!--插入数据-->
    <insert id="addProvider">
        insert into provider (providerName,people) values(#{providerName},#{people})
    </insert>

     <!--更新数据-->
    <update id="updateProvider">
        update `bill`.`provider`
        set `provider_code`=#{providerCode}, `providerName`=#{providerName}, `people`=#{people}, `phone`=#{phone}, `address`=#{address}, `fax`=#{fax}, `describe`=#{describe}
        where (`pid`=#{pid});
    </update>

    <!--删除数据-->
    <delete id="deleteProvider">
        delete from provider where pid=#{pid}
    </delete>
</mapper>

 知识点：

namespace ->复制mapper路径; 

id-》mapper接口定义的方法名；

resultType不要写错resultMap;

模糊查询中注意test-》数据类型，建议外单引号，内双引号

${}->动态拼接

3.测试中运行，测试mapper层是否正确

package com.cc.springboot;

import com.cc.springboot.entities.Provider;
import com.cc.springboot.mapper.ProviderMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;


import java.util.List;


@SpringBootTest(classes = {SpringBootBillApplication.class})
public class SpringBootBillApplicationTests {
   @Autowired
    ProviderMapper providerMapper;
    @Test
    void contextLoads() {
        Provider p=new Provider();
        p.setProviderName("A货云服务供应商11");
        List<Provider> providers = providerMapper.getProviders(p);
        System.out.println(providers.get(0));

        Provider provider = providerMapper.getProviderByPid((2));
        System.out.println(provider);

        provider.setProviderName("yyyyy呵呵嘿嘿");
        int i = providerMapper.updateProvider(provider);
        System.out.println(i);

        providerMapper.addProvider(new Provider(2001, "PR-AA", "梦学谷供应商111", "小张", "18888666981", "深圳软件园", "0911-0123456", "品质A"));

        providerMapper.deleteProvider(11);
    }

}

知识点：要注入mapper，若测试中打印sql语句，在yml配置文件中添加

logging:
  level:
    com.cc.springboot.mapper: debug

controller/ProviderController 修改进行页面跳转

package com.cc.springweb.controller;

import com.cc.springweb.bean.Provider;

import com.cc.springweb.mapper.ProviderMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;


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

@Controller
public class ProviderController {
    //    日志
    Logger logger= LoggerFactory.getLogger(getClass());
	@Autowired(required = false)
	ProviderMapper providerMapper;

    @GetMapping("/providers")
    public String list(Map<String,Object> map, Provider provider){
        List<Provider> providers = providerMapper.getProviders(provider);
        map.put("providers",providers);
        map.put("providerName",provider.getProviderName());
        return "provider/list";
    }


    @GetMapping("/provider/{pid}")
    public String view(@PathVariable("pid") Integer pid, @RequestParam(value = "type",defaultValue = "view") String type,Map<String,Object> map){
        logger.info("查询"+pid+"的供应商详细信息");
        Provider provider = providerMapper.getProviderByPid(pid);
        map.put("provider",provider);
        return "provider/"+type;

    }

    //    修改供应商信息
    @PutMapping("/provider")
    public String update(Provider provider){
        logger.info("更新供应商信息");
        	providerMapper.updateProvider(provider);
//        providerDao.save(provider);
        return "redirect:/providers";
    }

    //    打开添加供应商页面
    @GetMapping("/provider")
    public String toAdd(){
        return "provider/add";
    }
    //    添加供应商
    @PostMapping("/provider")
    public String add(Provider provider){
        logger.info("添加供应商信息",provider);
        providerMapper.addProvider(provider);
        return "redirect:/providers";
    }

    //    删除供应商
    @DeleteMapping("/provider/{pid}")
    public String delete(@PathVariable("pid") Integer pid){
        logger.info("删除供应商信息");
        	providerMapper.deleteProvider(pid);
        return "redirect:/providers";
    }
}