查询:
问题:数据库表的字段名称 和 实体类的属性名称 不一样,则不能自动封装数据。
方案一:起别名(使用 as 起一个与实体类属性名相同的别名)
缺点:每次查询都要定义一次别名。
public interface BrandMapper {
// 查询所有
public List<Brand> selectAll();
}
<!-- BrandMapper.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.it.mapper.BrandMapper">
<select id="selectAll" resultType="brand">
select id, brand_name as brandName, company_name as conmanyName,
ordered, description
from db_brand;
</select>
</mapper>
方案二:定义sql片段(使用<sql></sql>标签定义sql片段,<include/>标签导入sql片段)
缺点:不灵活(每次查询的是固定的字段)
<!-- BrandMapper.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.it.mapper.BrandMapper">
<!--
sql片段
-->
<sql id="brand_column">
id, brand_name as brandName, company_name as conmanyName, ordered, description
</sql>
<select id="selectAll" resultType="brand">
select
<include refid="brand_column"/>
from db_brand;
</select>
</mapper>
方案三:定义<resultMap>标签,在<select>标签中使用resultMap属性替换 resultType属性
<!-- BrandMapper.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.it.mapper.BrandMapper">
<!--
id:唯一属性
type:映射的类型,支持别名
-->
<resultMap id="brandResultMap" type="brand">
<!--
result:完成一般字段的映射
column:表的列名
property:实体类的属性名
-->
<result column="brand_name" property="brandName" />
<result column="company_name" property="companyName" />
</resultMap>
<!--
使用resultMap属性替换resultType属性
-->
<select id="selectAll" resultMap="brandResultMap">
select
*
from db_brand;
</select>
</mapper>
参数占位符:
#{ } :会将其替换为 ? ,为了防止SQL注入
${ } :拼sql,会存在SQL注入问题(表名或者字段名不固定的情况下可以使用)
public interface BrandMapper {
// 按id查询
Brand selectById(int id);
}
<!-- BrandMapper.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.it.mapper.BrandMapper">
<resultMap id="brandResultMap" type="brand">
<result column="brand_name" property="brandName" />
<result column="company_name" property="companyName" />
</resultMap>
<select id="selectById" resultMap="brandResultMap">
select *
from db_brand
where id = #{id};
</select>
</mapper>
特殊字符处理:
- 使用转义字符:
- CDATA区:
<!-- BrandMapper.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.it.mapper.BrandMapper">
<resultMap id="brandResultMap" type="brand">
<result column="brand_name" property="brandName" />
<result column="company_name" property="companyName" />
</resultMap>
<!--
转义字符:< 使用 <
> 使用 >
...
-->
<select id="selectById" resultMap="brandResultMap">
select *
from db_brand
where id < #{id};
</select>
<!--
CDATA区格式:
<![CDATA[
文本格式
]]>
-->
<select id="selectById" resultMap="brandResultMap">
select *
from db_brand where id
<![CDATA[
<
]]>
#{id};
</select>
</mapper>
条件查询:
-
多条件查询参数接收方式:
- 散装参数:如果方法中有多个参数,使用 @Param("SQL中的参数占位符名称")
- 实体类封装参数:只需保证SQL中的参数名 和 实体类属性名 对应上即可
- map集合参数:只需保证SQL中参数名 和 map集合的键的名称 对应上即可
public interface BrandMapper {
// 方式一:散装参数
List<Brand> selectByCondition(@Param("status") int status,
@Param("companyName") String companyName,
@Param("brandName") String brandName);
// 方式二:实体类封装参数
List<Brand> selectByCondition(Brand brand);
// 方式三:map集合参数
List<Brand> selectByCondition(Map map);
}
<!-- BrandMapper.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.it.mapper.BrandMapper">
<resultMap id="brandResultMap" type="brand">
<result column="brand_name" property="brandName" />
<result column="company_name" property="companyName" />
</resultMap>
<select id="selectByCondition" resultMap="brandResultMap">
select *
from db_brand
where status = #{status}
and company_name like #{companyName}
and brand_name like #{brandName}
</select>
</mapper>
动态条件查询:
动态SQL:SQL语句会随着用户的输入或外部条件的变化而变化。
<!-- BrandMapper.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.it.mapper.BrandMapper">
<resultMap id="brandResultMap" type="brand">
<result column="brand_name" property="brandName" />
<result column="company_name" property="companyName" />
</resultMap>
<!--
动态条件查询
-->
<select id="selectByCondition" resultMap="brandResultMap">
select *
from db_brand
<where>
<if test="status != null">
and 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>
<select id="selectByConditionSingle" resultMap="brandResultMap">
select *
from db_brand
where
<choose> <!-- 相当于switch -->
<when test="status != null"> <!-- 相当于case -->
status = #{status}
</when>
<when test="companyName != null and companyName != '' ">
company_name like #{companyName}
</when>
<when test="brandName != null and brandName != '' ">
brand_name like #{brandName}
</when>
<otherwise> <!-- 相当于default -->
1 = 1
</otherwise>
</choose>
</select>
</mapper>
添加:
MyBatis事务:
- openSession():默认开启事务,进行增删改操作后需要使用 sqlSession.commit(); 手动提交事务。
- openSession(true):可以设置为自动提交事务(关闭事务)。
主键返回:useGeneratedKeys 属性和 keyProperty 属性
在数据添加成功后,需要获取插入数据库的主键的值
<!-- BrandMapper.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.it.mapper.BrandMapper">
<resultMap id="brandResultMap" type="brand">
<result column="brand_name" property="brandName" />
<result column="company_name" property="companyName" />
</resultMap>
<!--
使用
useGeneratedKeys :设置为true
和
keyproperty属性 :数据库对应主键名
-->
<insert id="add" useGeneratedKeys="true" keyProperty="id">
insert into db_brand (brand_name, company_name, ordered, description, status)
values (#{brandName}, #{companyName}, #{orderd}, #{description}, #{status});
</insert>
</mapper>
修改:
- 修改全部字段:使用 <update id=" "></update> 标签,与上述查询、插入操作类似。
- 修改动态字段: 使用 <set></set> 标签,规避潜在风险(未被修改的字段返回null的问题,以及或因 set 逗号等引发sql报错问题)
<!-- BrandMapper.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.it.mapper.BrandMapper">
<resultMap id="brandResultMap" type="brand">
<result column="brand_name" property="brandName" />
<result column="company_name" property="companyName" />
</resultMap>
<update id="update">
update db_brand
<set>
<if test="brandName != null and brandName != '' ">
brand_name = #{brandName};
</if>
<if test="companyName != null and companyName != '' ">
company_name = #{companyName};
</if>
<if test="status != null">
status = #{status};
</if>
<if test="ordered != null">
ordered = #{ordered};
</if>
</set>
where id = #{id};
</update>
</mapper>
删除:
- 删除一个:使用 <delete id="deleteById"></delete> 标签,与上述查询、插入、修改一致
- 批量删除:实际上是删除一个数组,使用动态sql中 <foreach>标签动态生成占位符
public interface BrandMapper {
void deleteById(@Param("ids") int[] ids);
}
<!-- BrandMapper.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.it.mapper.BrandMapper">
<resultMap id="brandResultMap" type="brand">
<result column="brand_name" property="brandName" />
<result column="company_name" property="companyName" />
</resultMap>
<!--
mybatis会将数组参数封装为一个map集合
1、默认:array = 数组 (默认:collection属性名应为 array)
2、或者使用 @Param 注解改变map集合的默认key的名称(collection属性名自定义为:ids)
separator属性:分隔符
open close属性:可省略,其代表 foreach开始时拼接一个...,结尾时拼接一个...
-->
<delete id="deleteById">
delete from db_brand
where id
in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
;
</delete>
</mapper>
注解开发:
使用注解开发会比配置文件更加方便
- 查询:@Select
- 添加:@Insert
- 修改:@Update
- 删除:@Delete
package org.mybatis.example;
public interface BlogMapper {
@Select("SELECT * FROM blog WHERE id = #{id}")
Blog selectBlog(int id);
}
通常较为简单的功能(简单的sql语句),使用注解完成;较为复杂的功能使用配置文件的形式完成。