准备工作:
商品entity:
import lombok.Data;
import java.math.BigDecimal;
@Data
public class Goods {
private Long id;
private String name;
private BigDecimal price;
private Long uid;
private User user;
}
客户entity:
import lombok.Data;
import java.util.List;
@Data
public class User {
private Long id;
private Integer age;
private String name;
private List<Goods> goods;
}
一、标签“sql”
需求:
查询全部用户
mapper:
List<User> getUserList();
xml:
<sql id="userList_include">id,age,`name`</sql>
<select id="getUserList" resultType="com.mybatis.entity.User">
select <include refid="userList_include"/> from user
</select>
测试:
@Test
public void userList(){
userMapper.getUserList().stream().forEach(System.out::println);
}
控制台:
二、标签“if”
需求:
根据条件查询用户
mapper:
List<User> getUserListByConditions(Map<String,Object> user);
xml:
<select id="getUserListByConditions" resultType="com.mybatis.entity.User">
select id,age,`name` from user where 1=1
<if test="id != null">
and id =#{id}
</if>
<if test="age != null">
and age =#{age}
</if>
<if test="name != null">
and `name` =#{name}
</if>
</select>
测试:
@Test
public void userListByConditions(){
//查询的条件
HashMap<String,Object> map = new HashMap();
map.put("age",20);
//输出结果
userMapper.getUserListByConditions(map)
.stream().forEach(System.out::println);
}
控制台:
三、标签“where”
需求:
根据条件查询用户
mapper:
List<User> getUserListByConditions2(Map<String,Object> user);
xml:
<select id="getUserListByConditions2" resultType="com.mybatis.entity.User">
select id,age,`name` from user
<where>
<if test="id != null">
and id =#{id}
</if>
<if test="age != null">
and age =#{age}
</if>
<if test="name != null">
and `name` =#{name}
</if>
</where>
</select>
测试:
@Test
public void userListByConditions2(){
//查询的条件
HashMap<String,Object> map = new HashMap();
map.put("id",22);
//输出结果
userMapper.getUserListByConditions2(map).stream().forEach(System.out::println);
}
控制台:
四、标签“set”
需求:
修改商品信息
mapper:
int updateGoods(Map<String,Object> goods);
xml:
<update id="updateGoods">
update goods
<set>
<if test="name != null">
`name` = #{name},
</if>
<if test="price != null">
price = #{price},
</if>
</set>
where id = #{id}
</update>
测试:
@Test
public void updateGoods(){
//修改的条件
HashMap<String,Object> map = new HashMap();
map.put("id",10);
map.put("price",100);
//执行修改
goodsMapper.updateGoods(map);
}
控制台:
五、标签“choose、when、otherwise”
需求:
根据条件查询用户
mapper:
List<User> getUserListByConditions4(Map<String,Object> user);
xml:
<select id="getUserListByConditions4" resultType="com.mybatis.entity.User">
select id,age,`name` from user
<where>
<choose>
<when test="age != null">
and age =#{age}
</when>
<when test="name != null">
and `name` =#{name}
</when>
<otherwise>
and id = 10
</otherwise>
</choose>
</where>
</select>
测试:
@Test
public void userListByConditions4(){
//查询的条件
HashMap<String,Object> map = new HashMap();
map.put("age",20);
//输出结果
userMapper.getUserListByConditions4(map)
.stream().forEach(System.out::println);
}
控制台:
六、标签“trim”
需求:
1、根据条件查询用户
2、修改商品信息
mapper:
List<User> getUserListByConditions3(Map<String,Object> user);
int updateGoods2(Map<String,Object> goods);
xml:
<select id="getUserListByConditions3" resultType="com.mybatis.entity.User">
select id,age,`name` from user
<trim prefix="where" prefixOverrides="and | or">
<if test="id != null">
and id =#{id}
</if>
<if test="age != null">
and age =#{age}
</if>
<if test="name != null">
and `name` =#{name}
</if>
</trim>
</select>
<update id="updateGoods2">
update goods
<trim prefix="set" suffixOverrides=",">
<if test="name != null">
`name` = #{name},
</if>
<if test="price != null">
price = #{price},
</if>
</trim>
where id = #{id}
</update>
测试:
@Test
public void userListByConditions3(){
//查询的条件
HashMap<String,Object> map = new HashMap();
map.put("id",22);
//输出结果
userMapper.getUserListByConditions3(map)
.stream().forEach(System.out::println);
}
@Test
public void updateGoods2(){
//修改的条件
HashMap<String,Object> map = new HashMap();
map.put("id",10);
map.put("price",80);
//执行修改
goodsMapper.updateGoods2(map);
}
控制台:
七、标签“resultMap”
相关网址:
解决实体类字段与数据库字段不一致的问题_沈幼楚221215的博客-CSDN博客_实体类和数据库字段不对应
八、标签“foreach”
需求:
根据ids查询商品信息
mapper:
List<Goods> getGoodsByIds(Map<String,Object> goods);
xml:
<select id="getGoodsByIds" resultType="com.mybatis.entity.Goods">
select `id` ,`name`,`price` ,`uid` from goods
<where>
<foreach collection="ids" item="id" open="(" close=")" separator="or">
id = #{id}
</foreach>
</where>
</select>
测试:
@Test
public void getGoodsByIds(){
//id集合ids
List<Long> ids = new ArrayList<>();
ids.add(10L);
ids.add(11L);
ids.add(12L);
//查询的条件
HashMap<String,Object> map = new HashMap();
map.put("ids",ids);
//输出结果
goodsMapper.getGoodsByIds(map).stream().forEach(System.out::println);
}
控制台:
九、标签“association”
需求:
查询商品详细信息( 多个商品(Goods)对应一个客户(User))
mapper:
List<Goods> getGoods();
xml:
<select id="getGoods" resultMap="GoodsUser">
select g.id as goods_id,g.`name` as goods_name,
price as goods_price,u.`name` as user_name,
u.id as user_id from goods g inner join `user` u
on g.uid = u.id order by user_id
</select>
<resultMap id="GoodsUser" type="com.mybatis.entity.Goods">
<result property="id" column="goods_id"/>
<result property="name" column="goods_name"/>
<result property="price" column="goods_price"/>
<association property="user" javaType="com.mybatis.entity.User">
<result property="name" column="user_name"/>
<result property="id" column="user_id"/>
</association>
</resultMap>
测试:
@Test
public void association(){
goodsMapper.getGoods().stream().forEach(System.out::println);
}
控制台:
十、标签“collection”
需求:
查询用户详细信息(一个客户(User)对应多个商品(Goods))
mapper:
List<User> getUsers();
xml:
<select id="getUsers" resultMap="UserGoods">
select g.id as goods_id,g.`name` as goods_name,
price as goods_price,u.`name` as user_name,
u.id as user_id from goods g inner join `user` u
on g.uid = u.id
</select>
<resultMap id="UserGoods" type="com.mybatis.entity.User">
<result property="name" column="user_name"/>
<result property="id" column="user_id"/>
<collection property="goods" ofType="com.mybatis.entity.Goods">
<result property="id" column="goods_id"/>
<result property="name" column="goods_name"/>
<result property="price" column="goods_price"/>
</collection>
</resultMap>
测试:
@Test
public void collection(){
userMapper.getUsers().stream().forEach(System.out::println);
}
控制台: