目录
特殊SQL的执行
模糊查询
List<User> testMohu(@Param("mohu") String mohu); <select id="mohu" resultType="User">
-- select * from t_user where username like '%${mohu}%'
select * from t_user where username like concat('%',#{mohu},'%')
-- select * from t_user where username like "%"#{mohu}"%"
</select>@Test
public void selectAllUserMohu(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> users = mapper.mohu("李");
users.forEach(user -> System.out.println(user));
}批量删除
int delMore(@Param("ids") String ids);<delete id="delMore">
#{}会自动加单引号，这对这条sql来讲是不正确的
delete from t_user where id in (${ids})
</delete>@Test
public void delMore(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int i = mapper.delMore("2,3,4");
System.out.println(i);
}动态设置表名
List<User> getAllUser(@Param("tableName") String tableName);<!--List<User> getAllUser(@Param("tableName") String tableName);-->
<select id="getAllUser" resultType="User">
select * from ${tableName}
</select>
添加功能获取自增的主键
t_clazz(clazz_id,clazz_name)
t_student(student_id,student_name,clazz_id)
功能需求：
1、添加班级信息
2、获取新添加的班级的id
3、为班级分配学生，即将某学的班级id修改为新添加的班级的id
useGeneratedKeys：设置使用自增的主键
keyProperty：因为增删改有统一的返回值，是受影响的行数，因此只能将获取的自增的主键放在传输的参数user对象的某个属性中
int insertUser(User user);
<!--int insertUser(User user);-->
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
insert into t_user values(null,#{username},#{password},#{age},#{sex})
</insert>@Test
public void InsertUserId(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User(null, "王qi", "123123", 23, "男", "123@163.com");
mapper.InsertUserId(user);
System.out.println(user);
}ResultMap结果映射
resultMap处理字段和属性的映射关系
若数据库表中的字段名和实体类中的属性名不一致，则可以通过resultMap设置自定义映射。
resultMap：设置自定义映射
属性：
id：表示自定义映射的唯一标识
type：查询的数据要映射的实体类的类型
子标签：
id：设置主键的映射关系
result：设置普通字段的映射关系
association：设置多对一的映射关系
collection：设置一对多的映射关系 属性：
property：设置映射关系中实体类中的属性名
column：设置映射关系中表中的字段名
一个朴素的例子：
<resultMap id="userMap" type="User">
<id property="id" column="id"></id>
<result property="userName" column="user_name"></result>
<result property="password" column="password"></result>
<result property="age" column="age"></result>
<result property="sex" column="sex"></result>
</resultMap>
<!--List<User> testMohu(@Param("mohu") String mohu);-->
<!--select标签里的resultMap属性值要和resultMap标签id的属性值相同-->
<select id="testMohu" resultMap="userMap">
<!--select * from t_user where username like '%${mohu}%'-->
select id,user_name,password,age,sex from t_user where user_name like concat('%',#{mohu},'%')
</select>
若字段名和实体类中的属性名不一致，但是字段名符合数据库的规则（使用_），实体类中的属性名符合Java的规则（使用驼峰） 此时也可通过以下两种方式处理字段名和实体类中的属性的映射关系。
a>可以通过为字段起别名的方式，保证和实体类中的属性名保持一致
b>可以在MyBatis的核心配置文件中设置一个全局配置信息mapUnderscoreToCamelCase，可 以在查询表中数据时，自动将_类型的字段名转换为驼峰
例如：字段名user_name，设置了mapUnderscoreToCamelCase，此时字段名就会转换为 userName。
多对一映射处理
此类查询出来的结果中1号框的五个字段对应emp实体类的五个属性，2号框对应dept实体类的属性，其中emp和dept是多对一的关系，故在Emp（多）实体类中增加Dept（一）类型的属性dept。
级联方式处理映射关系
<resultMap id="empDeptMap" type="Emp">
<id column="eid" property="eid"></id>
<result column="ename" property="ename"></result>
<result column="age" property="age"></result>
<result column="sex" property="sex"></result>
<result column="did" property="dept.did"></result>
<result column="dname" property="dept.dname"></result>
</resultMap>
<!--Emp getEmpAndDeptByEid(@Param("eid") int eid);-->
<select id="getEmpAndDeptByEid" resultMap="empDeptMap">
select emp.*,dept.* from t_emp emp left join t_dept dept on emp.did = dept.did where emp.eid = #{eid}
</select>其中dept是emp实体类中的一个属性，列名为did，属性为dept，令property值为dept.did让其产生映射关系。
<result column="did" property="dept.did"></result>
<result column="dname" property="dept.dname"></result>
使用association处理映射关系
association这个标签专门来处理多对一的关系，property依然要写emp当中的属性，把查询出来的did以及deptName跟dept实体类当中的属性相对应，映射完之后获得Dept对象，再将此对象赋值给Emp实体类中的dept属性。
- association:处理多对一的映射关系
- property:需要处理多对的映射关系的属性名
- javaType:该属性的类型
<resultMap id="empDeptMap" type="Emp">
<id column="eid" property="eid"></id>
<result column="ename" property="ename"></result>
<result column="age" property="age"></result>
<result column="sex" property="sex"></result>
<association property="dept" javaType="Dept">
<id column="did" property="did"></id>
<result column="dname" property="dname"></result>
</association>
</resultMap>
<!--Emp getEmpAndDeptByEid(@Param("eid") int eid);-->
<select id="getEmpAndDeptByEid" resultMap="empDeptMap">
select emp.*,dept.* from t_emp emp left join t_dept dept on emp.did = dept.did where emp.eid = #{eid}
</select>使用分步查询处理映射关系（暂未明白，后续补）
- select：设置分步查询，查询某个属性的值的sql的标识（namespace.sqlId）
- column：将sql以及查询结果中的某个字段设置为分步查询的条件
- fetchType:当开启了全局的延迟加裁之后，可通过此属性手动控制延迟加载的效果
- fetchType= "lazyleager":lazy表示延迟加载，eager 表示立即加载
一对多映射处理
即在Dept（一）实体类中添加private List<Emp> emps ;
案例
Goods：
package com.imooc.mybatis.entity;
import java.util.List;
public class Goods {
private Integer goodsId;//商品编号
private String title;//标题
private String subTitle;//子标题
private Float originalCost;//原始价格
private Float currentPrice;//当前价格
private Float discount;//折扣率
private Integer isFreeDelivery;//是否包邮 ,1-包邮 0-不包邮
private Integer categoryId;//分类编号
private List<GoodsDetail> goodsDetails;
public Integer getGoodsId() {
return goodsId;
}
public void setGoodsId(Integer goodsId) {
this.goodsId = goodsId;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getSubTitle() {
return subTitle;
}
public void setSubTitle(String subTitle) {
this.subTitle = subTitle;
}
public Float getOriginalCost() {
return originalCost;
}
public void setOriginalCost(Float originalCost) {
this.originalCost = originalCost;
}
public Float getCurrentPrice() {
return currentPrice;
}
public void setCurrentPrice(Float currentPrice) {
this.currentPrice = currentPrice;
}
public Float getDiscount() {
return discount;
}
public void setDiscount(Float discount) {
this.discount = discount;
}
public Integer getIsFreeDelivery() {
return isFreeDelivery;
}
public void setIsFreeDelivery(Integer isFreeDelivery) {
this.isFreeDelivery = isFreeDelivery;
}
public Integer getCategoryId() {
return categoryId;
}
public void setCategoryId(Integer categoryId) {
this.categoryId = categoryId;
}
public List<GoodsDetail> getGoodsDetails() {
return goodsDetails;
}
public void setGoodsDetails(List<GoodsDetail> goodsDetails) {
this.goodsDetails = goodsDetails;
}
}
Category：
package com.imooc.mybatis.entity;
public class Category {
private Integer categoryId;
private String categoryName;
private Integer parentId;
private Integer categoryLevel;
private Integer categoryOrder;
public Integer getCategoryId() {
return categoryId;
}
public void setCategoryId(Integer categoryId) {
this.categoryId = categoryId;
}
public String getCategoryName() {
return categoryName;
}
public void setCategoryName(String categoryName) {
this.categoryName = categoryName;
}
public Integer getParentId() {
return parentId;
}
public void setParentId(Integer parentId) {
this.parentId = parentId;
}
public Integer getCategoryLevel() {
return categoryLevel;
}
public void setCategoryLevel(Integer categoryLevel) {
this.categoryLevel = categoryLevel;
}
public Integer getCategoryOrder() {
return categoryOrder;
}
public void setCategoryOrder(Integer categoryOrder) {
this.categoryOrder = categoryOrder;
}
}
dto:
import com.imooc.mybatis.entity.Category;
import com.imooc.mybatis.entity.Goods;
//Data Transfer Object--数据传输对象
public class GoodsDTO {
private Goods goods = new Goods();
private Category category = new Category();
private String test;
public Goods getGoods() {
return goods;
}
public void setGoods(Goods goods) {
this.goods = goods;
}
public Category getCategory() {
return category;
}
public void setCategory(Category category) {
this.category = category;
}
public String getTest() {
return test;
}
public void setTest(String test) {
this.test = test;
}
} <!--结果映射-->
<resultMap id="rmGoods" type="com.imooc.mybatis.dto.GoodsDTO">
<!--设置主键字段与属性映射-->
<id property="goods.goodsId" column="goods_id"></id>
<!--设置非主键字段与属性映射-->
<result property="goods.title" column="title"></result>
<result property="goods.originalCost" column="original_cost"></result>
<result property="goods.currentPrice" column="current_price"></result>
<result property="goods.discount" column="discount"></result>
<result property="goods.isFreeDelivery" column="is_free_delivery"></result>
<result property="goods.categoryId" column="category_id"></result>
<result property="category.categoryId" column="category_id"></result>
<result property="category.categoryName" column="category_name"></result>
<result property="category.parentId" column="parent_id"></result>
<result property="category.categoryLevel" column="category_level"></result>
<result property="category.categoryOrder" column="category_order"></result>
<result property="test" column="test"/>
</resultMap>
<select id="selectGoodsDTO" resultMap="rmGoods">
select g.* , c.*,'1' as test from t_goods g , t_category c
where g.category_id = c.category_id
</select>测试类：
/**
* 利用ResultMap进行结果映射
* @throws Exception
*/
@Test
public void testSelectGoodsDTO() throws Exception {
SqlSession session = null;
try{
session = MyBatisUtils.openSession();
List<GoodsDTO> list = session.selectList("goods.selectGoodsDTO");
for (GoodsDTO g : list) {
System.out.println(g.getGoods().getTitle());
}
}catch (Exception e){
throw e;
}finally {
MyBatisUtils.closeSession(session);
}
}MyBatis数据插入操作
resultType：sql返回值类型 order：AFTER | BEFORE 代表在sql之后|之前执行 last_insert_id() 用来获取当前连接中最后产生的id keyProperty： 主键属性
执行完sql之后，会自动的执行last_insert_id()，将得到的值回添到Goods的goodsId
<insert id="insert" parameterType="com.imooc.mybatis.entity.Goods" flushCache="true">
INSERT INTO t_goods(title, sub_title, original_cost, current_price, discount, is_free_delivery, category_id)
VALUES (#{title} , #{subTitle} , #{originalCost}, #{currentPrice}, #{discount}, #{isFreeDelivery}, #{categoryId})
<selectKey resultType="Integer" keyProperty="goodsId" order="AFTER">
select last_insert_id()
</selectKey>
</insert>/**
* 新增数据
* @throws Exception
*/
@Test
public void testInsert() throws Exception {
SqlSession session = null;
try{
session = MyBatisUtils.openSession();
Goods goods = new Goods();
goods.setTitle("测试商品");
goods.setSubTitle("测试子标题");
goods.setOriginalCost(200f);
goods.setCurrentPrice(100f);
goods.setDiscount(0.5f);
goods.setIsFreeDelivery(1);
goods.setCategoryId(43);
//insert()方法返回值代表本次成功插入的记录总数
int num = session.insert("goods.insert", goods);
session.commit();//提交事务数据
System.out.println(goods.getGoodsId());
}catch (Exception e){
if(session != null){
session.rollback();//回滚事务
}
throw e;
}finally {
MyBatisUtils.closeSession(session);
}
}selectKey与useGeneratedKeys的区别
- selectKey标签需要明确编写获取最新主键的SQL语句
- selectKey适用于所有的关系型数据库
- useGeneratedKeys只支持"自增主键'类型的数据库
- useGeneratedKeys属性会自动根据驱动生成对应SQL语句
更新与删除操作
<update id="update" parameterType="com.imooc.mybatis.entity.Goods">
UPDATE t_goods
SET
title = #{title} ,
sub_title = #{subTitle} ,
original_cost = #{originalCost} ,
current_price = #{currentPrice} ,
discount = #{discount} ,
is_free_delivery = #{isFreeDelivery} ,
category_id = #{categoryId}
WHERE
goods_id = #{goodsId}
</update>
<!--delete from t_goods where goods_id in (1920,1921)-->
<delete id="delete" parameterType="Integer">
delete from t_goods where goods_id = #{value}
</delete> /**
* 更新数据
* @throws Exception
*/
@Test
public void testUpdate() throws Exception {
SqlSession session = null;
try{
session = MyBatisUtils.openSession();
Goods goods = session.selectOne("goods.selectById", 739);
goods.setTitle("更新测试商品");
int num = session.update("goods.update" , goods);
session.commit();//提交事务数据
}catch (Exception e){
if(session != null){
session.rollback();//回滚事务
}
throw e;
}finally {
MyBatisUtils.closeSession(session);
}
}
/**
* 删除数据
* @throws Exception
*/
@Test
public void testDelete() throws Exception {
SqlSession session = null;
try{
session = MyBatisUtils.openSession();
int num = session.delete("goods.delete" , 739);
session.commit();//提交事务数据
}catch (Exception e){
if(session != null){
session.rollback();//回滚事务
}
throw e;
}finally {
MyBatisUtils.closeSession(session);
}
}MyBatis工作流程
1714
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
