mybatis之高级语法练习一

一、题目

练习：

1.在 resource 表中增加如下的查询条件，并使用一条查询语句完成，参数统一使用 map。

一、用户传入 page 与 size 参数时分页查询。

二、用户传入 beginTime 用于查询创建时间在 beginTime 的之后创建的资源。

三、传入 url 后可以根据 url 模糊查询。

四、传入 name 后可以根据 name 模糊查询。

练习：

2.在 resource 表中增加修改方法，使其能完成只修改不为空的属性，参数统一使用 map。

练习：

3.在修改时经常使用到批量修改操作，创建如下方法完成 resource 批量修改。

int updateAll(int[]ids, Resource resource);

ids 为修改的数据id集合

resource 包含修改的属性，不为空的属性才修改。

二、代码展示

①主体框架

②Resource实体类代码

package com.allen.entity;


import java.util.Date;

public class Resource {

  private Integer id;
  private String name;
  private String url;
  private Integer pid;
  private Date createTime;
  private Date updateTime;
  private Integer orderNumber;


  public Integer getId() {
    return id;
  }

  public void setId(Integer id) {
    this.id = id;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public String getUrl() {
    return url;
  }

  public void setUrl(String url) {
    this.url = url;
  }

  public Integer getPid() {
    return pid;
  }

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

  public Date getCreateTime() {
    return createTime;
  }

  public void setCreateTime(Date createTime) {
    this.createTime = createTime;
  }

  public Date getUpdateTime() {
    return updateTime;
  }

  public void setUpdateTime(Date updateTime) {
    this.updateTime = updateTime;
  }

  public Integer getOrderNumber() {
    return orderNumber;
  }

  public void setOrderNumber(Integer orderNumber) {
    this.orderNumber = orderNumber;
  }

  @Override
  public String toString() {
    return "\nResource{" +
            "id=" + id +
            ", name='" + name + '\'' +
            ", url='" + url + '\'' +
            ", pid=" + pid +
            ", createTime=" + createTime +
            ", updateTime=" + updateTime +
            ", orderNumber=" + orderNumber +
            '}';
  }
}

③ResourceMapper接口代码

package com.allen.mapper;

import com.allen.entity.Resource;
import org.apache.ibatis.annotations.Param;

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

public interface ResourceMapper {
	//1
    List<Resource> selectByPage(Map<String, Object> resMap);
	//2
    int update(Map<String, Object> map);
	//3
    int updateAll(@Param("ids") int[] ids, @Param("resource") Resource resource);
}

④resources文件夹下mapper文件里的ResourceMapper.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.allen.mapper.ResourceMapper">
    <resultMap id="ResourceMap" type="com.allen.entity.Resource">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="url" property="url" jdbcType="VARCHAR"/>
        <result column="pid" property="pid" jdbcType="INTEGER"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
        <result column="order_number" property="orderNumber" jdbcType="INTEGER"/>
    </resultMap>
    <sql id="Resource_List">
        id, name, url, pid, create_time, update_time, order_number
    </sql>
    <update id="update" parameterType="map">
        update resource
        <set>
            <if test="name!=null">name = #{name},</if>
            <if test="url!=null">url = #{url},</if>
            <if test="pid!=null">pid = #{pid},</if>
            <if test="orderNumber!=null">order_number = #{orderNumber},</if>
            update_time = now()
        </set>
        where id=#{id}
    </update>
    <update id="updateAll">
        update resource
        <set>
            <if test="resource.name!=null">`name`=#{resource.name},</if>
            <if test="resource.url!=null">url=#{resource.url},</if>
            <if test="resource.pid!=null">pid=#{resource.pid},</if>
            <if test="resource.orderNumber!=null">order_number=#{resource.orderNumber},</if>
            update_time=now()
        </set>
        where id
        <foreach collection="ids" item="id" open="in(" close=")" separator=",">
            #{id}
        </foreach>
    </update>
    <select id="selectByPage" parameterType="map" resultMap="ResourceMap">
        select
        <include refid="Resource_List"/>
        from resource
        <where>
            <if test="name!=null and name!=''">
               and name like #{name}
            </if>
            <if test="url!=null and url!=''">
                and url like #{url}
            </if>
            <if test="pid!=null">
                and pid like #{pid}
            </if>
            <if test="beginTime!=null">
                and create_time >=#{beginTime}
            </if>
        </where>
        <if test="page!=null and size!=null">
            limit #{page},#{size}
        </if>
    </select>
</mapper>

⑤mybatis12-conf.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>
    <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://localhost:3306/mybatis"/>
                <property name="username" value="root" />
                <property name="password" value="123456" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/ResourceMapper.xml"></mapper>
    </mappers>
</configuration>

⑥AppTest代码

package com.allen;

import static org.junit.Assert.assertTrue;

import com.allen.entity.Resource;
import com.allen.mapper.ResourceMapper;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.Reader;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class AppTest {
    SqlSession session;
    @Before
    public void init() throws IOException {
        Reader reader = Resources.getResourceAsReader("mybatis12-conf.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        session = sessionFactory.openSession(true);
    }
    @After
    public void close(){
        session.close();
    }

    /**
     * 1
     */
    @Test
    public void select(){
        ResourceMapper mapper = session.getMapper(ResourceMapper.class);
        Map<String,Object> map=new HashMap<>();
//        map.put("name","%管理%");
//        map.put("url","%dit%");
//        map.put("page",0);
//        map.put("size",8);
        map.put("beginTime","2020-07-07 16:25:15");
        List<Resource> resources = mapper.selectByPage(map);
        System.out.println(resources);
    }

    /**
     * 2
     */
    @Test
    public void update(){
        ResourceMapper mapper = session.getMapper(ResourceMapper.class);
        Map<String,Object> map=new HashMap<>();
        map.put("id",1);
        map.put("orderNumber",22);
        int update = mapper.update(map);
        System.out.println(update);
    }

    /**
     * 3
     */
    @Test
    public void updateAll(){
        ResourceMapper mapper = session.getMapper(ResourceMapper.class);
        int[] ints = new int[3];
        ints[0]=1;
        ints[1]=6;
        ints[2]=15;
        Resource resource=new Resource();
        resource.setOrderNumber(777);
        int i = mapper.updateAll(ints, resource);
        System.out.println(i);
    }
}

⑦数据库表内容

⑧SQL语句代码

CREATE TABLE `resource` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(50) NOT NULL COMMENT '名字',
  `url` varchar(50) NOT NULL COMMENT '地址',
  `pid` int(11) DEFAULT NULL COMMENT '父级id',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `order_number` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `resource`
--

LOCK TABLES `resource` WRITE;
/*!40000 ALTER TABLE `resource` DISABLE KEYS */;
INSERT INTO `resource` VALUES (1,'用户管理','/user',0,'2020-07-07 09:50:33','2022-01-03 21:23:28',777),(2,'用户添加','/userEdit',1,'2020-07-07 14:38:18','2020-07-07 14:38:41',NULL),(3,'用户修改','/userEdit',1,'2020-07-07 14:38:20','2020-07-07 14:38:43',NULL),(4,'用户删除','/user',1,'2020-07-07 09:50:38','2020-07-07 14:38:45',NULL),(5,'用户查询','/user',1,'2020-07-07 14:38:25','2020-07-07 14:38:47',NULL),(6,'角色管理','/role',0,'2020-07-07 14:38:27','2022-01-03 21:23:28',777),(7,'角色添加','/roleEdit',6,'2020-07-07 09:50:39','2020-07-07 14:38:48',NULL),(8,'角色修改','/roleEdit',6,'2020-07-07 14:38:39','2020-07-07 14:38:50',NULL),(12,'角色查询','/role',6,'2020-07-07 16:23:28','2020-07-09 10:17:27',NULL),(13,'角色删除','/roleDelete',6,'2020-07-07 16:23:46','2020-07-07 16:27:44',NULL),(14,'角色授权','/editRoleOfResource',6,'2020-07-07 16:24:11','2020-07-09 10:17:29',NULL),(15,'资源管理','/resource',0,'2020-07-07 16:24:41','2022-01-03 21:23:28',777),(16,'资源添加','/resourceEdit',15,'2020-07-07 16:25:15','2020-07-12 17:33:40',4),(17,'资源删除','/resource',15,'2020-07-07 16:25:37','2020-07-12 17:33:34',3),(19,'资源修改','/resourceEdit',15,'2020-07-07 16:26:09','2020-07-12 17:33:27',2),(20,'资源查询','/resource',15,'2020-07-07 16:27:05','2020-07-12 17:32:59',1),(21,'首页','/index',0,'2020-07-07 16:32:35','2020-07-09 10:17:39',10),(22,'资源授权','/authorize',6,'2020-07-13 15:45:22',NULL,1);
/*!40000 ALTER TABLE `resource` ENABLE KEYS */;
UNLOCK TABLES;

⑩测试结果如图所示

练习1

练习2

练习3