resultMap使用:
1.定义<resultMap>标签
2.在<select>标签,使用resultMap属性替换resultType属性
mapper的xml文件中,数据库的某些字段名替代
<resultMap id="brandResultMap" type="com.itheima.pojo.Brand">
<result column="brand_name" property="brandName" />
<result column="company_name" property="companyName" />
</resultMap>
<select id="selectAll" resultMap="brandResultMap">
select * from tb_brand;
</select>
小蓝鸟起别名报错问题就不用别名
参数占位符
1.#{} :执行SQL时,会将#{}占位符替换为?,将来自动设置参数值(参数传递)
2.${} :拼SQL。存在SQL注入问题(对表名或列名进行动态设置)
特殊字符处理
1.转义字符 2.CDATA区
例如 <
处理为: < 或
<![CDATA[
<
]]>
条件查询
参数传递
1.散装参数:有多个参数,需使用@Param(“SQL参数占位符”)
2.对象参数:对象的属性名称和参数占位符名称一致
3.map集合参数
散装参数
@Test
public void testSelectByCondition() throws Exception {
// 接受参数
int status=1;
String companyName="华为";
String brandName="华为";
companyName="%"+companyName+"%";
brandName="%"+brandName+"%";
// 获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取sqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获取Mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
// 执行方法
List<Brand> brands = brandMapper.selectByCondition(status, brandName, companyName);
System.out.println(brands);
// 释放资源
sqlSession.close();
}
2.对象参数
@Test
public void testSelectByCondition() throws Exception {
// 接受参数
int status=1;
String companyName="华为";
String brandName="华为";
companyName="%"+companyName+"%";
brandName="%"+brandName+"%";
// 封装对象
Brand brand = new Brand();
brand.setStatus(status);
brand.setBrandName(brandName);
brand.setCompanyName(companyName);
// 获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取sqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获取Mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
// 执行方法
List<Brand> brands = brandMapper.selectByCondition(brand);
System.out.println(brands);
// 释放资源
sqlSession.close();
}
3.map集合
@Test
public void testSelectByCondition() throws Exception {
// 接受参数
int status=1;
String companyName="华为";
String brandName="华为";
companyName="%"+companyName+"%";
brandName="%"+brandName+"%";
// 封装对象
Map map = new HashMap();
map.put("status",status);
map.put("companyName",companyName);
map.put("brandName",brandName);
// 获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取sqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获取Mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
// 执行方法
List<Brand> brands = brandMapper.selectByCondition(map);
System.out.println(brands);
// 释放资源
sqlSession.close();
}
动态sql
if条件判断 test进行逻辑表达式
问题 解决1.恒等式 2.<where>替换where关键字
<select id="selectByCondition" resultMap="brandResultMap">
select *
from tb_brand
<where>
<if test="status!=null">
status =#{status}
</if>
<if test="companyName!=null and companyName!=''">
and company_name like #{companyName}
</if>
<if test="brandName!=null and brandName!=''">
and brand_name like #{brandName}
</if>
</where>
</select>
choose(when,otherwise) 选择 类似 switch(case,default)
<!-- 单条件动态查询-->
<select id="selectByConditionSingle" resultMap="brandResultMap">
select *
from tb_brand
<where>
<choose>
<when test="status!=null">
status=#{status}
</when>
<when test="companyName!=null and conpanyName!=''">
company_name like #{companyName}
</when>
<when test="brandName!=null and brandName!=''">
brandName like #{brandName}
</when>
<otherwise>
1=1
</otherwise>
</choose>
</where>
</select>
添加
idea未将数据添加进数据库,数据回滚
1.手动提交 sqlSession.commit();
2.自动提交 SqlSession sqlSession = sqlSessionFactory.openSession(true);
问题:需获取增加数据的id useGeneratedKeys="true" keyProperty="id"
<!--keyProperty是实体类的属性,数据库的自增主键跟实体类的属性进行绑定-->
<!--useGeneratedKeys="true" 返回数据库自动生成的记录主键id-->
<insert id="add" useGeneratedKeys="true" keyProperty="id">
insert into tb_brand (brand_name,company_name,ordered,description,status)
values (#{brandName},#{companyName},#{ordered},#{description},#{status})
</insert>
动态修改
<set>标签:只修改部分数据,规避逗号
<update id="update">
update tb_brand
<set>
<if test="brandName!=null and brandName!=''">
brandName=#{brandName},
</if>
<if test="companyName!=null and companyName!=''">
company_name=#{companyName},
</if>
<if test="ordered!=null">
ordered=#{ordered},
</if>
<if test="description!=null and description!=''">
description=#{description},
</if>
<if test="status!=null">
status=#{status}
</if>
</set>
where id=#{id};
</update>
删除
删除多条问题:删除数量不确定,sql语句不好写
mybatis会将数组参数封装为一个map集合(键,值)
默认:array=数组
<foreach collection="array" item="id" separator=","> #{id} </foreach>
使用@Param注解改变map集合的默认key的名称
brandMapper.java中
void deleteByIds(@Param("ids") int[] ids);