Mybatis练习（CRUD）

---

一、练习一

数据库环境准备：

create table store (
  id int primary key auto_increment,
  shop_owner varchar(32) comment "店主姓名",
  id_number varchar(18) comment "身份证号",
  name varchar(100) comment "店铺名称",
  industry varchar(100) comment "行业分类",
  area varchar(200) comment "店铺区域",
  phone varchar(11) comment "手机号码",
  status int default 0 comment "审核状态。 0：待审核  1：审核通过  2：审核失败 3：重新审核 ",
  audit_time datetime comment "审核时间"
);

insert into store values (null,"张三丰","441322199309273014","张三丰包子铺","美食","北京市海淀区","18933283299","0","2017-12-08 12:35:30");
insert into store values (null,"令狐冲","441322199009102104","华冲手机维修","电子维修","北京市昌平区","18933283299","1","2019-01-020 20:20:00");
insert into store values (null,"赵敏","441322199610205317","托尼美容美发","美容美发","北京市朝阳区","18933283299","2","2020-08-08 10:00:30");

pojo类：

@Data
public class Store {
    private Integer id;
    private String shopOwner;
    private String idNumber;
    private String name;
    private  String industry;
    private String area;
    private String phone;
    private Integer status;
    private String auditTime;
}

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.yyy</groupId>
    <artifactId>store-demo</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>
        <!--自动生成pojo的方法-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
        </dependency>
        <!--mybatis 依赖-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.5</version>
        </dependency>

        <!--mysql 驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>

        <!--junit 单元测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
            <scope>test</scope>
        </dependency>

        <!-- 添加slf4j日志api -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.20</version>
        </dependency>
        <!-- 添加logback-classic依赖 -->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>1.2.3</version>
        </dependency>
        <!-- 添加logback-core依赖 -->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-core</artifactId>
            <version>1.2.3</version>
        </dependency>
    </dependencies>

日志配置文件：

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <!--
        CONSOLE ：表示当前的日志信息是可以输出到控制台的。
    -->
    <appender name="Console" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <pattern>[%level] %blue(%d{HH:mm:ss.SSS}) %cyan([%thread]) %boldGreen(%logger{15}) - %msg %n</pattern>
        </encoder>
    </appender>

    <logger name="com.yyy" level="DEBUG" additivity="false">
        <appender-ref ref="Console"/>
    </logger>
    <!--
      level:用来设置打印级别，大小写无关：TRACE, DEBUG, INFO, WARN, ERROR, ALL 和 OFF
     ， 默认debug
      <root>可以包含零个或多个<appender-ref>元素，标识这个输出位置将会被本日志级别控制。
      -->
    <root level="DEBUG">
        <appender-ref ref="Console"/>
    </root>
</configuration>
</project>

mybatis配置文件：

<?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>
    <!--起别名-->
    <typeAliases>
        <package name="com.yyy.pojo"></package>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql:///mybatis?useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!--扫描mapper包-->
        <package name="com.yyy.mapper"/>
    </mappers>
</configuration>

需求描述
现有如下表所示是store【店铺】表中的数据列表，请求使用代理方式完成如下需求

• 问题1、向表中添加如下数据

"黄蓉","441322199511114212","蓉儿叫花鸡","美食","北京市朝阳区","18933283299",1,"2020-08-08 10:00:30"

代码实现：
xml开发方式，sql语句

   <insert id="insertStore" useGeneratedKeys="true" keyProperty="id">
        insert into store(`shop_owner`, `id_number`, `name`, `industry`, `area`, `phone`, `status`, `audit_time`)
        values (#{shopOwner},#{idNumber},#{name},#{industry},#{area},#{phone},#{status},#{auditTime})
    </insert>

mapper接口类

   /**
     * 新增
     * @param store
     */
    void insertStore(Store store);

编写测试类进行测试：（使用了JUnit单元测试，@Before 、 @After、@Test）

 SqlSessionFactory sqlSessionFactory;
    SqlSession sqlSession;
    StoreMapper mapper;
    @Before
    public  void textBefore() throws IOException {
        InputStream resource = Resources.getResourceAsStream("mybatis-config.xml");
         sqlSessionFactory = new SqlSessionFactoryBuilder().build(resource);
         sqlSession = sqlSessionFactory.openSession();
         mapper = sqlSession.getMapper(StoreMapper.class);
    }
    @After
    public void textAfter(){
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void textInster(){
        Store store = new Store();
        store.setName("蓉儿叫花鸡");
        store.setArea("北京市朝阳区");
        store.setAuditTime("2020-08-08 10:00");
        store.setIdNumber("441322199511114212");
        store.setPhone("18933283299");
        store.setShopOwner("黄蓉");
        store.setIndustry("美食");
        store.setStatus(1);
        mapper.insertStore(store);

        System.out.println( store.getId());
    }  

• 问题2、查询 id=1 的数据，并将数据封装到实体类对象中
  xml sql语句：

 <update id="update">
        update store
        <set>
            <if test="area !=null and area !=''">
                area = #{area},
            </if>
            <if test="phone != null and phone != ''">
                phone = #{phone},
            </if>
            <if test="status != null">
              status = #{status},
            </if>
            <if test="shopOwner != null and shopOwner!= ''">
                shop_owner = #{shopOwner}
            </if>
        </set>
      <where>
          id = #{id}
    </where>
    </update>

• 问题3、查询 表中所有的数据，并封装到集合中
  简单的sql可以使用注释的方式进行开发，xml文件和注释是可以同时存在的

   /**
     * 查询全部
     * @return
     */
    @Select("select * from store ")
    List<Store> findAll();

• 问题4、动态 的根据 id 删除 数据
  xml sql语句

  <delete id="deleteByIds">
        delete from store
        <where>
            id in
         <choose>
           <when test="ids.size>0 and ids != null">
               <foreach collection="ids" separator="," open="(" close=")" item="item">
                   #{item}
               </foreach>
           </when>
          <otherwise>
              (0)
          </otherwise>
          </choose>
        </where>
    </delete>

• 问题5、动态sql修改部分字段（使用 set标签和if标签）

 <update id="update">
        update store
        <set>
            <if test="area !=null and area !=''">
                area = #{area},
            </if>
            <if test="phone != null and phone != ''">
                phone = #{phone},
            </if>
            <if test="status != null">
              status = #{status},
            </if>
            <if test="shopOwner != null and shopOwner!= ''">
                shop_owner = #{shopOwner}
            </if>
        </set>
      <where>
          id = #{id}
    </where>
    </update>

完整题目代码链接：

https://download.csdn.net/download/javaren001/85335436

二、练习二

在项目中我们常遇到的多条件动态查询，如下图：

表结构是题目一的 Store 表，完成如下需要

1. 在 StoreMapper 接口中声明 void deleteByIds(@Param("ids") List ids) 方法来删除 id=1和id=3 的数据

xml sql语句：
foreach 标签：遍历集合
choose 标签：类似java中的switch
when 标签：类似java中switch 的case

   <delete id="deleteByIds">
        delete from store
        <where>
            id in
         <choose>
           <when test="ids.size>0 and ids != null">
               <foreach collection="ids" separator="," open="(" close=")" item="item">
                   #{item}
               </foreach>
           </when>
          <otherwise>
              (0)
          </otherwise>
          </choose>
        </where>
    </delete>

2. 在 StoreMapper 接口中声明 List<Store> findCondition(Store store) 方法来动态的根据不同条件查询数据
   xml sql语句：

 <select id="findCondition" resultMap="resultMap">
        select * from store
        <where>
            <if test="phone != null and phone != ''">
                and phone = #{phone}
            </if>
            <if test="idNumber != null and idNumber != ''">
                and id_number = #{idNumber}
            </if>
            <if test="auditTime != null and auditTime != ''">
                and audit_time = #{auditTime}
            </if>
            <if test="status != null ">
                and status = #{status}
            </if>
        </where>
    </select>

三、练习三（注释方式）

1、数据库表准备：

create table userInfo(
	id int PRIMARY key auto_increment,
	uname varchar(20) UNIQUE,
	uped varchar(20) not null,
	realname varchar(20) not null
);

insert into userinfo(uname,uped,realname) values 
('ZS','123','张三'),('LS','456','李四'),('WW','789','王五'),											
('猴子','123','孙悟空'),('猪','456','猪八戒'),('舍利子','789','唐僧');

2、pojo

@Data
public class User {
    private Integer id;
    private String username;
    private String password;
    private String realName;
}

3、使用注释实现查询全部

  /**
     * 查询全部
     * @return
     */
    @Results(id="ResultMap",value={
            @Result(column = "uname" ,property = "username"),
            @Result(column = "uped" ,property = "password"),
            @Result(column = "realname" ,property = "realName")
    })
    @Select("select * from userinfo")
    List<User> findAll();

就是把在xml配置文件中的标签形式写成注释方式
如果需要返回值时，要在加上一个@ResultMap标签

@ResultMap（“里面放的是@Results 的id”），
 如果在xml中配置了ResultMap，就可以直接用xml文件中的ReslutMap的ID
 注解和xml中配置的ResultMap是通用的