项目配置
Sql配置
spring:
datasource:
url: jdbc:mysql://localhost:3306/xxxx?useUnicode=true&characterEncoding=utf-8
username: root
password: 123456
driverClassName: com.mysql.cj.jdbc.Driver
Mybatis配置
1. dao层的接口上加上@Mapper,@Repository注解
2. 配置application.yml
mybatis:
mapperLocations: classpath:mappers/*.xml # ***Mapper.xml文件所在路径
# typeAliasesPackage: com.example.entity # Model类所在包名
# configuration:
# mapUnderscoreToCamelCase: true # 开启驼峰命名转换(将表字段下划线形式映射到实体属性)
***Mapper.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.example.dao.UserDao">
<insert></insert>
<delete></delete>
<update></update>
<select></select>
</mappper>
<insert></insert>标签
插入操作
语法:<insert id="" parameterType="" useGeneratedKeys="" keyProperty="" keyColumn=""></insert>
id: 唯一标识,与dao接口里面的方法名对应
parameterType: 参数类型
useGeneratedKeys: 是否获取自增主键,默认false
keyProperty: 将操作时返回的值,赋值给实体类中的某个属性,通常设置为主键对应的属性
keyColumn: 设置第几列是主键,当表结构中第一列不是主键列时则需要设置
举例:
<insert id="addUser" parameterType="com.example.entity.User" useGeneratedKeys="true" keyProperty="id">
insert into t_user(USERNAME, PASSWORD) values (#{username}, #{password})
</insert>
假设插入后,该条数据的自增主键ID为6,则该值会赋给User实体类中的id,实体类中的id也会为6
<delete></delete>标签
删除操作
语法:<delete id="" parameterType=""></delete>
举例:
<delete id="deleteUserById" parameterType="Integer">
delete from t_user where ID=#{id}
</delete>
<update></update>标签
更新操作
语法:<update id="" parameterType=""></update>
举例:
<update id="updateUserById" parameterType="com.example.entity.User">
update t_user set USERNAME=#{username}, PASSWORD=#{password}, SEX=#{sex} where ID=#{id}
<update>
<select></select>标签
查询操作
语法:
<select id="" parameterType="" resultType=""></select>
<select id="" parameterType="" resultMap=""></select>
resultType和resultMap为返回结果集
举例:
<select id="findUserById" parameterType="Integer" resultType="com.example.entity.User">
select ID, USERNAME, PASSWORD, SEX from t_user where ID=#{id}
</select>
<where></where>标签
该标签会在使用的地方输出where语句,并对标签内的语句智能处理:
1. 标签内条件均不满足,则返回所有记录
2. 标签内条件满足,若输出是以and、or开头的,则会把第一个and、or忽略掉
语法:<where></where>
举例:
1. 不使用该标签
<select id="findUserByCondition" parameterType="Map" resultType="com.example.entity.User">
select * from t_user where 1=1
<if test="password!=''">
and PASSWORD=#{password}
</if>
<if test="sex!=''">
and SEX=#{sex}
</if>
</select>
2. 使用该标签
<select id="findUserByCondition" parameterType="Map" resultType="com.example.entity.User">
select * from t_user
<where>
<if test="password!=''">
and PASSWORD=#{password}
</if>
<if test="sex}!=''">
and SEX=#{sex}
</if>
</where>
</select>
若标签内条件均不满足,则会执行 select * from t_user
若仅password满足,则会执行 select * from t_user where PASSWORD=#{password},输出where语句,并忽略掉第一个and
若password、sex均满足,则会执行 select * from t_user where PASSWORD=#{password} and SEX=#{sex}
<set></set>标签
<set></set>标签
该标签会在使用的地方输出set语句,若标签内多个满足条件的语句最后均有逗号,则忽略最后一个逗号
语法:<set></set>
举例:
<update id="updateUserById" parameterType="com.example.entity.User">
update t_user
<set>
<if test="username!=''">USERNAME=#{username},</if>
<if test="password!=''">PASSWORD=#{password},</if>
<if test="sex!=''">SEX=#{sex}</if>
</set>
where ID=#{id}
</update>
若仅username、password满足条件,则会执行 update t_user set USERNAME=#{username}, PASSWORD=#{password} where ID=#{id},输出set语句,并忽略掉 PASSWORD=#{password},中的逗号
<foreach></foreach>标签
该标签主要用于构建in条件
语法:<foreach collection="" item="" index="" open="" separator="" close=""></foreach>
collection: 迭代集合或数组
item: 元素迭代时的别名
index: 元素迭代时的下标
open: 迭代开始语句
separator: 迭代间分隔符
close: 迭代结束语句
举例:
<select id="findUserByIds" parameterType="List" resultType="com.example.entity.User">
select * from t_user where id in
<foreach collection="list" item="item" index="index" open="(" separator="," close=")">
#{item}
</foreach>
</select>
若传入时,id只有1和3,则会执行 select * from t_user where id in(1, 3)
<sql></sql>标签
该标签主要定义一些通用的语句,将公共部分抽取出来,便于在多个地方复用,降低代码冗余
语法:<sql id="xxx">yyy</sql>,id为唯一标识,后续引用时通过id引用,<include refid="xxx"/>
举例:
1. 不使用该标签
<select id="findUserById" parameterType="Integer" resultType="com.example.entity.User">
select ID, USERNAME, PASSWORD, SEX from t_user where ID=#{id}
</select>
2. 使用该标签
<sql id="userProperty">
ID, USERNAME, PASSWORD, SEX
</sql>
<select id="findUserById" parameterType="Integer" resultType="com.example.entity.User">
select <include refid="userProperty"/> from t_user where ID=#{id}
</select>
<trim></trim>标签
该标签可以在查询的内容中加上前/后缀,也可把首/位部中的某些内容去掉
语法:<trim prefix="" suffix="" prefixOverrides="" suffixOverrides=""></trim>
举例:
<select id="findUserById" parameterType="Integer" resultType="com.example.entity.User">
select * from t_user
<trim prefix="where" prefixOverrides="and|or">
and ID=#{id}
</trim>
</select>
查询时,会执行select * from t_user where ID=#{id},前缀加上where,并去掉首部的and
<choose/> <when/> <otherwise/>标签
该标签主要用于不想用到所有条件,而是从中选择一二,类似Java中的switch语句
语法:
<choose>
<when test=""></when>
<otherwise></otherwise>
</choose>
举例:
<select id="findUserByCondition" parameterType="com.example.vo.UserVo" resultType="com.example.entity.User">
select * from t_user where 1=1
<choose>
<when test="username!=''">
and USERNAME=#{username}
</when>
<when test="password!=''">
and PASSWORD=#{password}
</when>
<otherwise>
and SEX='M'
</otherwise>
</choose>
</select>
若仅username满足,则会执行 select * from t_user where 1=1 and USERNAME=#{username}
若仅password满足,则会执行 select * from t_user where 1=1 and PASSWORD=#{password}
若仅username、password满足,则会执行 select * from t_user where 1=1 and USERNAME=#{username},username条件顺序先于password,当username条件满足时,后续条件都不会再执行,若调换username和password顺序,则会执行password条件
若username、password均不满足,则会执行 select * from t_user where 1=1 and SEX='M'
总结,仅有一个条件满足时,则执行此条件;当有多个条件满足时,按照语句顺序,只执行第一个条件;当所有条件都不满足时,则执行otherwise条件
<resultMap></resultMap>标签
封装返回结果集
语法:
<resultMap id="" type="">
<id/>
<result/>
<association/>
<collection/>
...
</resultMap>
举例:
1. 当实体属性名和表字段名不一致时,会映射不到,若想将表字段映射到实体属性时,可以使用该标签:
<resultMap id="userMap" type="com.example.entity.User">
<id property="id" column="U_ID"/>
<result property="username" column="U_NAME"/>
<result property="password" column="U_PASS"/>
<result property="sex" column="U_SEX"/>
</resultMap>
2. 一对一级联查询
假设存在如下结构:
public class User {
private Integer uid;
private String username;
private String password;
private String sex;
private IdCard idCard;
}
public class IdCard {
private Integer id;
private String idCardNum;
private String department;
}
t_user表:UID, USERNAME, PASSWORD, SEX, ID_CARD_NUM
t_idcard表:ID, ID_CARD_NUM, DEPARTMENT
<resultMap id="userAndIdCard" type="com.example.entity.User">
<id property="uid" column="UID"/>
<result property="username" column="USERNAME"/>
<result property="password" column="PASSWORD"/>
<result property="sex" column="SEX"/>
<association property="idCard" javaType="com.example.entity.IdCard">
<id property="id" column="ID"/>
<result property="idCardNum" column="ID_CARD_NUM"/>
<result property="department" column="DEPARTMENT"/>
</association>
</resultMap>
<select id="findUserAndIdCardByUid" parameterType="Integer" resultMap="userAndIdCard">
select u.UID, u.USERNAME, u.PASSWORD, u.SEX, i.ID, i.ID_CARD_NUM, i.DEPARTMENT
from t_user u, t_idcard i
where u.ID_CARD_NUM=i.ID_CARD_NUM and u.UID=#{id}
</select>
返回结果:
{
"code": 200,
"status": "success",
"data": {
"uid": 1,
"username": "Tom",
"password": "666",
"sex": "M",
"idCard": {
"id": 1,
"idCardNum": "100001",
"department": "数学系"
}
}
}
3. 一对多级联查询
假设存在如下结构:
public class User {
private Integer uid;
private String username;
private String password;
private String sex;
private List<Order> orderList;
}
public class Order{
private Integer oid;
private String sn;
}
t_user表:UID, USERNAME, PASSWORD, SEX
t_order表:OID, SN, UID
<resultMap id="userAndOrder" type="com.example.entity.User">
<id property="uid" column="UID"/>
<result property="username" column="USERNAME"/>
<result property="password" column="PASSWORD"/>
<result property="sex" column="SEX"/>
<collection property="orderList" ofType="com.example.entity.Order"> <!-- ofType表示集合中的元素类型 -->
<id property="oid" column="OID"/>
<result property="sn" column="SN"/>
</collection>
</resultMap>
<select id="findUserAndOrderByUid" parameterType="Integer" resultMap="userAndOrder">
select u.UID, u.USERNAME, u.PASSWORD, u.SEX, o.OID, o.SN
from t_user u, t_order o
where u.UID=o.UID and u.UID=#{id}
</select>
返回结果:
{
"code": 200,
"status": "success",
"data": {
"uid": 1,
"username": "Tom",
"password": "666",
"sex": "M",
"orderList": [
{
"oid": 1,
"sn": "SN101"
},
{
"oid": 2,
"sn": "SN102"
}
]
}
}
4. 多对多级联查询