<?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.powernode.mybatis.mappers.CarMapper">
<!-- if标签的属性是必须的,if标签中test标签必须为true,if标签内的sql语句就会自动拼接,反过来则不会-->
<!-- test标签的值应该是一个表达式,结果为true或者false即可-->
<!-- 如果使用了param注解,那么test标签写param指定的参数名-->
<!-- 当没有使用使用这个param注解,则test中出现的是param1,param2....-->
<!-- 当使用的是一个POJO对象,则填入的则为POJO的属性名字-->
<!-- mybatis的动态SQL中不能使用&&只能是and-->
<select id="selectByMultiCondition" resultType="Car">
select * from t_car where 1 = 1
<if test="brand != null and brand != ''">
and brand like '%${brand}%'
</if>
<if test="guidePrice != null and guidePrice != ''">
and guide_price > #{guidePrice}
</if>
<if test="carType != null and carType != ''">
and car_type = #{carType}
</if>
</select>
<!-- where标签是负责实现where子句的,如果我们没有where的条件,就不会再生成了-->
<!-- 可以自动的去掉前面的and或者or,然后自动拼接出sql语句-->
<!-- 在子句的后面的and和or不能去掉-->
<select id="selectByMultiConditionWithWhere" resultType="Car">
select * from t_car
<where>
<if test="brand != null and brand != ''">
brand like '%${brand}%'
</if>
<if test="guidePrice != null and guidePrice != ''">
and guide_price > #{guidePrice}
</if>
<if test="carType != null and carType != ''">
and car_type = #{carType}
</if>
</where>
</select>
<!-- prefixOverrides去掉前缀 suffix=加后缀 suffixOverrides去掉后缀 prefix加前缀-->
<!-- suffixOverrides去掉后缀:就是把SQL子句中多余的and或or去掉,这里用|就代表或者-->
<!-- 没有任何sql子句的话,就不会添加前缀了(sql子句前缀)-->
<select id="selectByMultiConditionWithTrim" resultType="Car">
select * from t_car
<trim suffixOverrides="and|or" prefix="where">
<if test="brand != null and brand != ''">
brand like '%${brand}%' and
</if>
<if test="guidePrice != null and guidePrice != ''">
guide_price > #{guidePrice} and
</if>
<if test="carType != null and carType != ''">
car_type = #{carType} and
</if>
</trim>
</select>
<!-- 局部更新,如果传递的值不为空,我们才实现更新数据-->
<update id="updateById">
update t_car set
car_num = #{carNum},
brand = #{brand},
guide_price = #{guidePrice},
produce_time = #{produceTime},
car_type = #{carType}
where
id = #{id};
</update>
<update id="updateBySet">
update t_car
<set>
<if test="carNum != null and carNum != ''">car_num = #{carNum},</if>
<if test="brand != null and brand != ''">brand = #{brand},</if>
<if test="guidePrice != null and guidePrice != ''">guide_price = #{guidePrice},</if>
<if test="produceTime != null and produceTime != ''">produce_time = #{produceTime},</if>
<if test="carType != null and carType != ''">car_type = #{carType}</if>
</set>
where
id = #{id}
</update>
<!-- 先遇到就先进去,全部为空就走最后一个,和if-else语句类似,传进去一个null值-->
<select id="selectWith" resultType="Car">
select * from t_car
<where>
<choose>
<when test="brand != null and brand != ''">
brand like '%${brand}%'
</when>
<when test="guidePrice != null and guidePrice != ''">
guide_price = #{guidePrice}
</when>
<otherwise>
car_type = #{carType}
</otherwise>
</choose>
</where>
</select>
<!-- foreach标签的属性collection=用于指定数组或者集合 item=代表数组或集合的元素 separator=循环之间的分隔符-->
<delete id="deleteByIds">
delete from t_car where id in
<foreach collection="array" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
<!-- 批量插入信息,一次插入多条语句-->
<insert id="insertBatch">
insert into t_car values
<foreach collection="cars" item="car" separator=",">
(null,#{car.carNum},#{car.brand},#{car.guidePrice},#{car.produceTime},#{car.carType})
</foreach>
</insert>
<delete id="deleteBatchById">
delete from t_car where
<foreach collection="ids" separator="or" item="id">
id = #{id}
</foreach>
</delete>
</mapper>
<?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.powernode.mybatis.mappers.CarMapper">
<!-- if标签的属性是必须的,if标签中test标签必须为true,if标签内的sql语句就会自动拼接,反过来则不会-->
<!-- test标签的值应该是一个表达式,结果为true或者false即可-->
<!-- 如果使用了param注解,那么test标签写param指定的参数名-->
<!-- 当没有使用使用这个param注解,则test中出现的是param1,param2....-->
<!-- 当使用的是一个POJO对象,则填入的则为POJO的属性名字-->
<!-- mybatis的动态SQL中不能使用&&只能是and-->
<select id="selectByMultiCondition" resultType="Car">
select * from t_car where 1 = 1
<if test="brand != null and brand != ''">
and brand like '%${brand}%'
</if>
<if test="guidePrice != null and guidePrice != ''">
and guide_price > #{guidePrice}
</if>
<if test="carType != null and carType != ''">
and car_type = #{carType}
</if>
</select>
<!-- where标签是负责实现where子句的,如果我们没有where的条件,就不会再生成了-->
<!-- 可以自动的去掉前面的and或者or,然后自动拼接出sql语句-->
<!-- 在子句的后面的and和or不能去掉-->
<select id="selectByMultiConditionWithWhere" resultType="Car">
select * from t_car
<where>
<if test="brand != null and brand != ''">
brand like '%${brand}%'
</if>
<if test="guidePrice != null and guidePrice != ''">
and guide_price > #{guidePrice}
</if>
<if test="carType != null and carType != ''">
and car_type = #{carType}
</if>
</where>
</select>
<!-- prefixOverrides去掉前缀 suffix=加后缀 suffixOverrides去掉后缀 prefix加前缀-->
<!-- suffixOverrides去掉后缀:就是把SQL子句中多余的and或or去掉,这里用|就代表或者-->
<!-- 没有任何sql子句的话,就不会添加前缀了(sql子句前缀)-->
<select id="selectByMultiConditionWithTrim" resultType="Car">
select * from t_car
<trim suffixOverrides="and|or" prefix="where">
<if test="brand != null and brand != ''">
brand like '%${brand}%' and
</if>
<if test="guidePrice != null and guidePrice != ''">
guide_price > #{guidePrice} and
</if>
<if test="carType != null and carType != ''">
car_type = #{carType} and
</if>
</trim>
</select>
<!-- 局部更新,如果传递的值不为空,我们才实现更新数据-->
<update id="updateById">
update t_car set
car_num = #{carNum},
brand = #{brand},
guide_price = #{guidePrice},
produce_time = #{produceTime},
car_type = #{carType}
where
id = #{id};
</update>
<update id="updateBySet">
update t_car
<set>
<if test="carNum != null and carNum != ''">car_num = #{carNum},</if>
<if test="brand != null and brand != ''">brand = #{brand},</if>
<if test="guidePrice != null and guidePrice != ''">guide_price = #{guidePrice},</if>
<if test="produceTime != null and produceTime != ''">produce_time = #{produceTime},</if>
<if test="carType != null and carType != ''">car_type = #{carType}</if>
</set>
where
id = #{id}
</update>
<!-- 先遇到就先进去,全部为空就走最后一个,和if-else语句类似,传进去一个null值-->
<select id="selectWith" resultType="Car">
select * from t_car
<where>
<choose>
<when test="brand != null and brand != ''">
brand like '%${brand}%'
</when>
<when test="guidePrice != null and guidePrice != ''">
guide_price = #{guidePrice}
</when>
<otherwise>
car_type = #{carType}
</otherwise>
</choose>
</where>
</select>
<!-- foreach标签的属性collection=用于指定数组或者集合 item=代表数组或集合的元素 separator=循环之间的分隔符-->
<delete id="deleteByIds">
delete from t_car where id in
<foreach collection="array" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
<!-- 批量插入信息,一次插入多条语句-->
<insert id="insertBatch">
insert into t_car values
<foreach collection="cars" item="car" separator=",">
(null,#{car.carNum},#{car.brand},#{car.guidePrice},#{car.produceTime},#{car.carType})
</foreach>
</insert>
<delete id="deleteBatchById">
delete from t_car where
<foreach collection="ids" separator="or" item="id">
id = #{id}
</foreach>
</delete>
</mapper>
package com.powernode.mybatis.mappers;
import com.powernode.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface CarMapper
{
int deleteBatchById(@Param("ids")Long[] ids);
//批量插入,一次插入多条car信息
int insertBatch(@Param("cars") List<Car> cars);
//使用foreach标签批量删除
int deleteByIds(@Param("ids") Long[] ids);
List<Car> selectWith(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);
int updateBySet(Car car);
int updateById(Car car);
List<Car> selectByMultiConditionWithTrim(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);
//使用where标签让SQL子句更加智能
List<Car> selectByMultiConditionWithWhere(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);
//多条件查询,根据汽车品牌指导价和汽车类型实现查询
List<Car> selectByMultiCondition(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);
}
package com.powernode.mybatis.mappers;
import com.powernode.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface CarMapper
{
int deleteBatchById(@Param("ids")Long[] ids);
//批量插入,一次插入多条car信息
int insertBatch(@Param("cars") List<Car> cars);
//使用foreach标签批量删除
int deleteByIds(@Param("ids") Long[] ids);
List<Car> selectWith(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);
int updateBySet(Car car);
int updateById(Car car);
List<Car> selectByMultiConditionWithTrim(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);
//使用where标签让SQL子句更加智能
List<Car> selectByMultiConditionWithWhere(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);
//多条件查询,根据汽车品牌指导价和汽车类型实现查询
List<Car> selectByMultiCondition(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);
}
package com.powernode.mybatis.Test;
import com.powernode.mybatis.mappers.CarMapper;
import com.powernode.mybatis.pojo.Car;
import com.powernode.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;
public class TestCarMapper
{
private static final Logger logger = LoggerFactory.getLogger(TestCarMapper.class);
@Test
public void TestSelectByMultiCondition()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> carList = mapper.selectByMultiCondition("比亚迪",3.0,"燃油车");
carList.forEach(car -> {
logger.info(car.toString());
});
SqlSessionUtil.close(sqlSession);
}
@Test
public void TestNull()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> carList = mapper.selectByMultiCondition(null,null,null);
carList.forEach(car -> {
logger.info(car.toString());
});
}
@Test
public void TestNum()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> carList = mapper.selectByMultiCondition("长安",null,null);
carList.forEach(car -> {
logger.info(car.toString());
});
}
@Test
public void TestSelectByMultiConditionWithWhere()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> carList = mapper.selectByMultiConditionWithWhere("比亚迪",3.0,"燃油车");
carList.forEach(car -> {
logger.info(car.toString());
});
}
@Test
public void TestNullFor()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> carList = mapper.selectByMultiConditionWithWhere(null,null,null);
carList.forEach(car -> {
logger.info(car.toString());
});
}
@Test
public void TestNullOne()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> carList = mapper.selectByMultiConditionWithWhere(null,3.0,null);
carList.forEach(car -> {
logger.info(car.toString());
});
}
@Test
public void TestSelectByMultiConditionWithTrim()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> carList = mapper.selectByMultiConditionWithTrim("比亚迪",3.0,"燃油车");
carList.forEach(car -> {
logger.info(car.toString());
});
}
@Test
public void TestSelectByMultiNull()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> carList = mapper.selectByMultiConditionWithTrim(null,null,null);
carList.forEach(car -> {
logger.info(car.toString());
});
}
@Test
public void TestupdateById()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Car car = new Car(5L,null,null,null,null,"新能源");
int count = mapper.updateById(car);
sqlSession.commit();
SqlSessionUtil.close(sqlSession);
}
@Test
public void TestupdateBySet()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Car car = new Car(49L,null,null,null,null,"新能源");
int count = mapper.updateBySet(car);
logger.info("" + count);
sqlSession.commit();
SqlSessionUtil.close(sqlSession);
}
@Test
public void TestselectWith()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> carList = mapper.selectWith(null,null,null);
carList.forEach(car -> {
logger.info(car.toString());
});
SqlSessionUtil.close(sqlSession);
}
@Test
public void TestdeleteByIds()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Long[] ids = {48L,16L,17L};
int count = mapper.deleteByIds(ids);
logger.info("条数" + count);
sqlSession.commit();
SqlSessionUtil.close(sqlSession);
}
@Test
public void TestinsertBatch()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> cars = new ArrayList<>();
cars.add(new Car(null,"1200","长城",15.0,"2020-10-11","燃油车"));
cars.add(new Car(null,"1200","奇瑞",15.0,"2020-10-11","燃油车"));
cars.add(new Car(null,"1200","江淮",15.0,"2020-10-11","燃油车"));
int count = mapper.insertBatch(cars);
System.out.println(count);
sqlSession.commit();
SqlSessionUtil.close(sqlSession);
}
@Test
public void TestdeleteBatchById()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Long[] ids = {4L};
mapper.deleteBatchById(ids);
sqlSession.commit();
SqlSessionUtil.close(sqlSession);
}
}
package com.powernode.mybatis.Test;
import com.powernode.mybatis.mappers.CarMapper;
import com.powernode.mybatis.pojo.Car;
import com.powernode.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;
public class TestCarMapper
{
private static final Logger logger = LoggerFactory.getLogger(TestCarMapper.class);
@Test
public void TestSelectByMultiCondition()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> carList = mapper.selectByMultiCondition("比亚迪",3.0,"燃油车");
carList.forEach(car -> {
logger.info(car.toString());
});
SqlSessionUtil.close(sqlSession);
}
@Test
public void TestNull()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> carList = mapper.selectByMultiCondition(null,null,null);
carList.forEach(car -> {
logger.info(car.toString());
});
}
@Test
public void TestNum()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> carList = mapper.selectByMultiCondition("长安",null,null);
carList.forEach(car -> {
logger.info(car.toString());
});
}
@Test
public void TestSelectByMultiConditionWithWhere()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> carList = mapper.selectByMultiConditionWithWhere("比亚迪",3.0,"燃油车");
carList.forEach(car -> {
logger.info(car.toString());
});
}
@Test
public void TestNullFor()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> carList = mapper.selectByMultiConditionWithWhere(null,null,null);
carList.forEach(car -> {
logger.info(car.toString());
});
}
@Test
public void TestNullOne()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> carList = mapper.selectByMultiConditionWithWhere(null,3.0,null);
carList.forEach(car -> {
logger.info(car.toString());
});
}
@Test
public void TestSelectByMultiConditionWithTrim()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> carList = mapper.selectByMultiConditionWithTrim("比亚迪",3.0,"燃油车");
carList.forEach(car -> {
logger.info(car.toString());
});
}
@Test
public void TestSelectByMultiNull()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> carList = mapper.selectByMultiConditionWithTrim(null,null,null);
carList.forEach(car -> {
logger.info(car.toString());
});
}
@Test
public void TestupdateById()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Car car = new Car(5L,null,null,null,null,"新能源");
int count = mapper.updateById(car);
sqlSession.commit();
SqlSessionUtil.close(sqlSession);
}
@Test
public void TestupdateBySet()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Car car = new Car(49L,null,null,null,null,"新能源");
int count = mapper.updateBySet(car);
logger.info("" + count);
sqlSession.commit();
SqlSessionUtil.close(sqlSession);
}
@Test
public void TestselectWith()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> carList = mapper.selectWith(null,null,null);
carList.forEach(car -> {
logger.info(car.toString());
});
SqlSessionUtil.close(sqlSession);
}
@Test
public void TestdeleteByIds()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Long[] ids = {48L,16L,17L};
int count = mapper.deleteByIds(ids);
logger.info("条数" + count);
sqlSession.commit();
SqlSessionUtil.close(sqlSession);
}
@Test
public void TestinsertBatch()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> cars = new ArrayList<>();
cars.add(new Car(null,"1200","长城",15.0,"2020-10-11","燃油车"));
cars.add(new Car(null,"1200","奇瑞",15.0,"2020-10-11","燃油车"));
cars.add(new Car(null,"1200","江淮",15.0,"2020-10-11","燃油车"));
int count = mapper.insertBatch(cars);
System.out.println(count);
sqlSession.commit();
SqlSessionUtil.close(sqlSession);
}
@Test
public void TestdeleteBatchById()
{
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Long[] ids = {4L};
mapper.deleteBatchById(ids);
sqlSession.commit();
SqlSessionUtil.close(sqlSession);
}
}