常用数据类型
JdbcType Mysql
VARCHAR VARCHAR
FLOAT FLOAT
DOUBLE DOUBLE
DECIMAL DECIMAL
INTEGER INTEGER
BIGINT BIGINT
SMALLINT SMALLINT
DATE DATE
TIMESTAMP TIMESTAMP/DATETIME
TINYINT TINYINT
BLOB BLOB
CLOB TEXT
常用小技巧
字段模糊查询
<if test="operatorLeader != null">
and operator_leader like concat('%', #{operatorLeader,jdbcType=VARCHAR}, '%')
</if>
DAO接口声明
Mapper接口声明和xml文件中的方法一一对应,mapper中声明方法的名称以及参数,xml是mapper的对应实现。
public interface XXXMapper{
//参数可以是类的类型,成员参数成对出现student_name=#{studentName}
int insert(StudentDO entity);
//map类型,key为数据库字段,value为#{}的value
List<StudentDO> selectByMap(Map<String,Object> queryMap);
//必须按照顺序来,数字代表参数位置#{0},#{1}...
int getStudentByCondition(String studentName,Interge studentAge);
//@Param可以不按照顺序,@Param中有值按照值来,没值的话按照变量名称
int getStudentByCondition(@Param("name") String studentName,@Param("age") Interge studentAge);
//返回Map类型单条数据
Map<String,Object> getUserById(Interger id);
//返回Map类型多条数据
List<Map<String,Object>> getUserByName(String name);
//返回自定义Map类型的数据
@MapKey("userName")
Map<String,User> getUserByName(String name);
}
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.yupaopao.mcn.infrastructure.dal.mapper.McnStudyChallengeVideoMapper">
<!--告诉mybatis,实体类的属性名和表的字段名之间的对应关系-->
<resultMap id="BaseResultMap" type="com.flx.study.entity.StudentDO">
<id column="id" jdbcType="BIGINT" property="id"/>
<result column="key_word" jdbcType="VARCHAR" property="KeyWord"/>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime">
</resultMap>
<sql>...</sql>
<select>...</select>
<insert>...</insert>
<update>...</update>
<delete>...</delete>
</mapper>
重点名次解释:
namespace代表的是Mapper的全类名,指向的是Dao接口
转义关键字
parameterType关键字(定义传入参数类型)
基本数据类型(int,string,long,date)
McnStudyChallengeKey selectByPrimaryKey(Long id);
parameterType="java.lang.Long"
McnStudyChallengeKey selectByName(String name);
parameterType="java.lang.String"
McnStudyChallengeKey selectByDate(Date date);
parameterType="java.util.Date"
复杂数据类型(类和Map)
Map类型
也称为map封装,将参数直接放入HashMap中,key作为引用参数,value作为值
List selectStudentByMap(Map queryMap);
queryMap:
{
"id":99,
"name":"wangming",
"className":"chunfeng"
}
parameterType="map"
select * from t_student where id=#{id} and name=#{name} and class_name=#{className}
List类型
也称为list封装,可以同时放入多个参数
List ids);
parameterType="list"
select * from t_student where id in
<foreach item="item" index="index" collection="list" open="(" close=")" separator=",">
#{item}
</foreach>
类类型
StudentDO selectByCondition(StudentDO entity);
parameterType="com.flx.study.StudentDO"
resultMap关键字(定义返回实体类型映射)
告诉mybatis,实体类的属性名和表的字段名之间的对应关系,其中id是标识,type是映射的实体类全类名
自定义映射实体类型
id可以被其他地方引用的标识,比如:
<resultMap id="BaseResultMap" type="com.flx.study.entity.StudentDO">
...
</resultMap>
<select resultMap="BaseResultMap" parameterType="java.util.List">
resultType关键字(定义返回类型,严格对应)
返回基本类型
返回普通Map类型
Map selectUserById(Integer id);//查出一条数据
List> selectUserByName(String name);//查出多条数据
<select id="selectUserById" parameterType="integer" resultType="map">
select * from t_user where id=#{name}
</select>
返回自定义Map类型
@MapKey("userName")
Map selectAllUser();
<select id="selectAllUser" resultType="com.flx.study.User">
select * from t_user;
</select>
返回Pojo类型
User selectUserByName(String name);
<select id="selectUserByName" parameterType="string" resultType="com.flx.study.User">
select * from t_user;
</select>
sql关键字(定义一段通用代码)
模板定义
一般是定义一段通用代码,供其他地方使用
定义SQL代码
<sql id="Base_Column_List">
id, key_id, key_word, video_id, cover, title, url, stat_time, create_time, update_time
</sql>
引用模板
使用如下:
select
<include refid="Base_Column_List"/>
from mcn_account
select关键字(用作查询语句)
McnStudyChallengeKey selectByPrimaryKey(Long id);
<select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from mcn_study_dy_challenge_key
where id = #{id,jdbcType=BIGINT}
</select>
List判断语句(判断字段是否存在)
列表判断查询多个状态列表
<if test="statusList !=null and statusList.size() > 0">
and status in
<foreach collection="statusList" item="status" index="index" open="(" close=")" separator=",">
#{status}
</foreach>
</if>
分页查询(页起始位置计算)
xml如下所示
<if test="param.pageNo!=null and param.pageSize!=null">
limit ${(param.pageNo - 1) * param.pageSize} #{param.pageSize}
</if>
代码中包装分页
public Page<UsrServer> findPage(UsrServerParam param){
Page<UsrServer> page = Page.of(param.getPageNo(),param.getPageSize());
Long totalCount = serverMapper.countTotal(param);
if(totalCount>0) {
List<UsrServer> dataList = serverMapper.findPage(param);
page.setTotal(totalCount);
page.setRecords(dataList);
}
return page;
}
insert关键字(用作新增语句)
插入成功后返回主键id
有两种写法可以返回ID
写法一:order=AFTER表明是先进行插入操作然后再去获取id,因为插入之后才会有自增主键
<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long">
SELECT LAST_INSERT_ID()
</selectKey>
写法二:
<insert id="insertSelective" parameterType="com.flx.study.StudentDO" keyProperty="id"
keyColumn="id" useGeneratedKeys="true">
直接插入数据并返回插入id值
int insert(McnStudyChallengeKey record);
<insert id="insert" parameterType="com.yupaopao.mcn.infrastructure.dal.model.McnStudyChallengeKey">
<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long">
SELECT LAST_INSERT_ID()
</selectKey>
insert into mcn_study_dy_challenge_key (key_word, hot, video_count,
stat_time, create_time, update_time
)
values (#{keyWord,jdbcType=VARCHAR}, #{hot,jdbcType=INTEGER}, #{videoCount,jdbcType=INTEGER},
#{statTime,jdbcType=TIMESTAMP}, #{createTime,jdbcType=TIMESTAMP}, #{updateTime,jdbcType=TIMESTAMP}
)
</insert>
当字段不为空才插入
标签:mybatis的trim标签一般用于去除sql语句中多余的and关键字,逗号,或者给sql语句前拼接 “where“、“set“以及“values(“ 等前缀,或者添加“)“等后缀,可用于选择性插入、更新、删除或者条件查询等操作。
int insertSelective(McnStudyChallengeKey record);
<insert id="insertSelective" parameterType="com.yupaopao.mcn.infrastructure.dal.model.McnStudyChallengeKey">
<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long">
SELECT LAST_INSERT_ID()
</selectKey>
insert into mcn_study_dy_challenge_key
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="keyWord != null">
key_word,
</if>
<if test="hot != null">
hot,
</if>
<if test="createTime != null">
create_time,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="keyWord != null">
#{keyWord,jdbcType=VARCHAR},
</if>
<if test="hot != null">
#{hot,jdbcType=INTEGER},
</if>
<if test="createTime != null">
#{createTime,jdbcType=TIMESTAMP},
</if>
</trim>
</insert>
批量插入数据
int batchInsert(List records);
<insert id="batchInsert" param>
insert into mcn_study_dy_challenge_key
(key_word, hot, video_count)
values
<foreach collection="records" item="item" separator=",">
(#{item.keyWord,jdbcType=VARCHAR},#{item.hot,jdbcType=INTEGER},#{item.videoCount,jdbcType=INTEGER})
</foreach>
</insert>
批量插入数据并且返回id值(适用于需要获取插入id的场景)
int batchInsert(List records);
<insert id="batchInsert" parameterType="list" keyColumn="id" keyProperty="id" useGeneratedKeys="true">
insert into mcn_study_dy_challenge_key
(key_word, hot, video_count)
values
<foreach collection="list" item="item" index="index" separator=",">
(#{item.keyWord,jdbcType=VARCHAR},#{item.hot,jdbcType=INTEGER},#{item.videoCount,jdbcType=INTEGER})
</foreach>
</insert>
批量插入成功后,原来的插入实体集合的id字段都被赋值了插入的id值
update关键字(用作更新语句)
直接更新数据
int updateByPrimaryKey(McnStudyChallengeKey record);
<update id="updateByPrimaryKey" parameterType="com.yupaopao.mcn.infrastructure.dal.model.McnStudyChallengeKey">
update mcn_study_dy_challenge_key
set key_word = #{keyWord,jdbcType=VARCHAR},
hot = #{hot,jdbcType=INTEGER},
create_time = #{createTime,jdbcType=TIMESTAMP}
where id = #{id,jdbcType=BIGINT}
</update>
更新不为空的数据
int updateByPrimaryKeySelective(McnStudyChallengeKey record);
<update id="updateByPrimaryKeySelective" parameterType="com.yupaopao.mcn.infrastructure.dal.model.McnStudyChallengeKey">
update mcn_study_dy_challenge_key
<set>
<if test="keyWord != null">
key_word = #{keyWord,jdbcType=VARCHAR},
</if>
<if test="hot != null">
hot = #{hot,jdbcType=INTEGER},
</if>
<if test="createTime != null">
create_time = #{createTime,jdbcType=TIMESTAMP},
</if>
</set>
where id = #{id,jdbcType=BIGINT}
</update>
delete关键字(用作删除语句)
int deleteByPrimaryKey(Long id);
<delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
delete from mcn_study_dy_challenge_key
where id = #{id,jdbcType=BIGINT}
</delete>