编码题
需求描述
现有如下表所示是store【店铺】表中的数据列表,请使用代理方式完成如下需求
-
向表中添加如下数据
"黄蓉","441322199511114212","蓉儿叫花鸡","美食","北京市朝阳区","18933283299",1,"2020-08-08 10:00:30"
-
查询
id=1
的数据,并将数据封装到实体类对象中 -
查询表中所有的数据,并封装到集合中
-
删除
id=2
的数据 -
将
id=1
的数据area
字段值修改为北京市昌平区
,phone
字段值修改为13629224217
-
在
StoreMapper
接口中声明void deleteByIds(@Param("ids") List ids)
方法来删除id=1和id=3
的数据 -
在
StoreMapper
接口中声明List<Store> findCondition(Map<String,Object> condition)
方法来动态的根据不同条件查询数据。如下图所示,请在测试类中模拟不同的查询条件查询数据
素材
store表的创建语句如下:
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");
提示:
需求五不是修改所有的数据,所以需要使用 动态sql修改部分字段。这里需要使用 set标签和if标签
需求六需要使用 foreach
标签来遍历集合进行动态sql拼接
需求七需要使用 if
标签判断是否包含指定的条件
注意:
-
审核状态的取值是 0、1、2、3
-
findCondition()
方法参数map集合的key是属性名称,value是属性值。如下是所有的查询条件封装的map集合SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); String startTime = "2019-01-02"; String endTime = "2020-08-10"; //0,准备数据 Map<String,Object> condition = new HashMap<>(); condition.put("phone","%18933283299%"); condition.put("startTime",sdf.parse(startTime)); condition.put("endTime",sdf.parse(endTime)); condition.put("status",0); condition.put("idNumber","%441322199309273014%");
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.mybatisdemo</groupId>
<artifactId>mybatis01</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.10</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>
</project>
jdbc.properties配置文件
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/heima
jdbc.username=root
jdbc.password=root
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>
<properties resource="jdbc.properties"/>
<settings>
<!-- 将数据库表中的字段名里的下划线自动映射到Java属性驼峰式命名,默认是false-->
<!-- <setting name="mapUnderscoreToCamelCase" value="true"/>-->
<!--开启延迟加载 ,如果不想让某个数据延迟加载,可在映射文件的association里面加个fetchType属性设为eager就不会延迟加载了-->
<!-- <setting name="lzayLoadingEnabled" value="true"/>-->
<!--在控制台显示sql语句-->
<setting name="logImpl" value="STDOUT_LOGGING"/>
<!--当属性名和数据库字段名不一致时,开启驼峰命名,全局设置,这里先关掉,用resultMap做映射-->
<!-- <setting name="mapUnderscoreToCamelCase" value="true"/>-->
</settings>
<typeAliases>
<!-- 给实体类取别名,统一为类名-->
<package name="com.mybatisdemo.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--<mapper resource="UserMapper.xml"/>-->
<!--当Mapper接口和映射文件一致时,且在同一目录下,就可用包扫描,注意resources下面建是用directory目录用/分包名-->
<package name="com.mybatisdemo.mapper"/>
</mappers>
</configuration>
Store实体类
package com.mybatisdemo.pojo;
import java.util.Date;
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 Date auditTime;
public Store() {
}
public Store(Integer id, String shopOwner, String idNumber, String name, String industry, String area,
String phone, Integer status, Date auditTime) {
this.id = id;
this.shopOwner = shopOwner;
this.idNumber = idNumber;
this.name = name;
this.industry = industry;
this.area = area;
this.phone = phone;
this.status = status;
this.auditTime = auditTime;
}
/**
* 获取
* @return id
*/
public Integer getId() {
return id;
}
/**
* 设置
* @param id
*/
public void setId(Integer id) {
this.id = id;
}
/**
* 获取
* @return shopOwner
*/
public String getShopOwner() {
return shopOwner;
}
/**
* 设置
* @param shopOwner
*/
public void setShopOwner(String shopOwner) {
this.shopOwner = shopOwner;
}
/**
* 获取
* @return idNumber
*/
public String getIdNumber() {
return idNumber;
}
/**
* 设置
* @param idNumber
*/
public void setIdNumber(String idNumber) {
this.idNumber = idNumber;
}
/**
* 获取
* @return name
*/
public String getName() {
return name;
}
/**
* 设置
* @param name
*/
public void setName(String name) {
this.name = name;
}
/**
* 获取
* @return industry
*/
public String getIndustry() {
return industry;
}
/**
* 设置
* @param industry
*/
public void setIndustry(String industry) {
this.industry = industry;
}
/**
* 获取
* @return area
*/
public String getArea() {
return area;
}
/**
* 设置
* @param area
*/
public void setArea(String area) {
this.area = area;
}
/**
* 获取
* @return phone
*/
public String getPhone() {
return phone;
}
/**
* 设置
* @param phone
*/
public void setPhone(String phone) {
this.phone = phone;
}
/**
* 获取
* @return status
*/
public Integer getStatus() {
return status;
}
/**
* 设置
* @param status
*/
public void setStatus(Integer status) {
this.status = status;
}
/**
* 获取
* @return auditTime
*/
public Date getAuditTime() {
return auditTime;
}
/**
* 设置
* @param auditTime
*/
public void setAuditTime(Date auditTime) {
this.auditTime = auditTime;
}
public String toString() {
return "Store{id = " + id + ", shopOwner = " + shopOwner + ", idNumber = " + idNumber + ", name = " + name + ", industry = " + industry + ", area = " + area + ", phone = " + phone + ", status = " + status + ", auditTime = " + auditTime + "}";
}
}
StoreMapper接口
package com.mybatisdemo.mapper;
import com.mybatisdemo.pojo.Store;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface StoreMapper {
// 向表中添加如下数据
void add(Store store);
//查询 `id=1` 的数据,并将数据封装到实体类对象中
Store getByID(Integer id);
//查询 表中所有的数据,并封装到集合中
List<Store> getAll();
//删除 `id=2` 的数据
void deleteById(Integer id);
//修改部分数据 将 `id=1` 的数据 `area` 字段值修改为 `北京市昌平区`, `phone` 字段值修改为 `13629224217`
// 修改部分数据方一
void updateById(Store store);
//修改部分数据方二 用动态sql语句
void updateById2(Store store);
//批量删除2和3
void deleteByIds(@Param("ids") List ids);
//据不同条件查询数据 用动态sql语句
List<Store> findCondition(Map<String,Object> condition);
}
StoreMapper.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.mybatisdemo.mapper.StoreMapper">
<!--添加-->
<insert id="add" >
insert into store values (null,#{shopOwner},#{idNumber},#{name},#{industry},#{area},#{phone},#{status},#{auditTime});
</insert>
<select id="getByID" resultType="Store">
select * from store where id = #{id};
</select>
<select id="getAll" resultMap="StoreMap">
select * from store
</select>
<resultMap id="StoreMap" type="Store">
<id column="id" property="id"/>
<result column="shop_owner" property="shopOwner"/>
<result column="id_number" property="idNumber"/>
<result column="name" property="name"/>
<result column="industry" property="industry"/>
<result column="area" property="area"/>
<result column="phone" property="phone"/>
<result column="status" property="status"/>
<result column="audit_time" property="auditTime"/>
</resultMap>
<!--删除一个-->
<delete id="deleteById" >
delete from store where id =#{id};
</delete>
<!--修改部分数据方法一-->
<update id="updateById" >
update store set area = #{area},phone = #{phone} where id =#{id};
</update>
<!--修改部分数据方法二-->
<update id="updateById2">
update store
<set>
<if test="area != null and area !=''">
area = #{area},
</if>
<if test="phone != null and phone !=''">
phone = #{phone}
</if>
</set>
where id = #{id};
</update>
<!--删除多个-->
<delete id="deleteByIds">
delete from store
where id in
<foreach collection="ids" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</delete>
<!--where 标签可以自动补全where这个关键字,它还可以去掉多余的and和or关键字-->
<select id="findCondition" resultMap="StoreMap">
select * from store
<where>
<if test="phone != null and phone !=''">
phone like #{phone}
</if>
<if test="idNumber != null and idNumber != ''">
and id_number like #{idNumber}
</if>
<if test="status != null">
and status = #{status}
</if>
<if test="startTime != null and endTime != null">
and audit_time between #{startTime} and #{endTime}
</if>
</where>
</select>
<!--set标签可以去掉多余的逗号-->
</mapper>
SqlSessionUtils工具类
package com.mybatisdemo.utils;
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;
public class SqlSessionUtils {
public static SqlSession getSqlSession(){
SqlSession sqlSession = null;
try {
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
sqlSession = sqlSessionFactory.openSession(true);
} catch (IOException e) {
e.printStackTrace();
}
return sqlSession;
}
}
测试类
import com.mybatisdemo.mapper.StoreMapper;
import com.mybatisdemo.pojo.Store;
import com.mybatisdemo.utils.SqlSessionUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
/*
* String转换为Date类型
* String strDate = "字符串日期";
* SimpleDateFormat sdf = new SimpleDateFormat("要转换的模板");
* Date date = sdf.parse(strDate);
*
* */
public class StoreTest {
@Test
public void testAdd() throws ParseException {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
StoreMapper storeMapper = sqlSession.getMapper(StoreMapper.class);
Store store = new Store();
store.setShopOwner("黄蓉");
store.setIdNumber("441322199511114212");
store.setName("蓉儿叫花鸡");
store.setIndustry("美食");
store.setArea("北京市朝阳区");
store.setPhone("18933283299");
store.setStatus(1);
storeMapper.add(store);
String strDate ="2020-08-08 10:00:30";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date = sdf.parse(strDate);
store.setAuditTime(date);
storeMapper.add(store);
}
@Test
public void testGetByID(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
StoreMapper storeMapper = sqlSession.getMapper(StoreMapper.class);
Store byID = storeMapper.getByID(1);
System.out.println("byID = " + byID);
}
@Test
public void testGetAll(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
StoreMapper storeMapper = sqlSession.getMapper(StoreMapper.class);
List<Store> storeList = storeMapper.getAll();
storeList.forEach(System.out::println);
}
@Test
public void testDeleteById(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
StoreMapper storeMapper = sqlSession.getMapper(StoreMapper.class);
storeMapper.deleteById(4);
}
@Test
public void testUpdateById(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
StoreMapper storeMapper = sqlSession.getMapper(StoreMapper.class);
Store store = new Store();
store.setArea("北京市昌平区");
store.setPhone("13629224217");
store.setId(1);
storeMapper.updateById(store);
}
@Test
public void testUpdateById2(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
StoreMapper storeMapper = sqlSession.getMapper(StoreMapper.class);
Store store = new Store();
store.setArea("五岳");
store.setPhone("13655556217");
store.setId(1);
storeMapper.updateById2(store);
}
@Test
public void testDeleteByIds(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
StoreMapper storeMapper = sqlSession.getMapper(StoreMapper.class);
List<Integer> ids = new ArrayList();
ids.add(2);
ids.add(3);
storeMapper.deleteByIds(ids);
}
@Test
public void test() throws ParseException {
//这里有个审核时间,弄个时间范围,让审核时间在这个范围内的都包含进来,更贴近实际需求
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//申请开始时间
String startTime = "2016-01-02";
//申请结束时间
String endTime = "2020-08-10";
//由于要根据身份证号、手机号、申请时间和审核状态来查询,将他们装进map中
Map<String,Object> condition = new HashMap<>();
condition.put("phone","%13655556217%");
condition.put("startTime",sdf.parse(startTime));
condition.put("endTime",sdf.parse(endTime));
condition.put("status",0);
condition.put("idNumber","%44132219%");
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
StoreMapper storeMapper = sqlSession.getMapper(StoreMapper.class);
List<Store> stores = storeMapper.findCondition(condition);
stores.forEach(System.out::println);
}
}