<resultMapid="getUserByIdMap"type="User"> <resultproperty="id"column="uid"></result></resultMap><selectid="getUsers"resultType="User"> SELECT u.id as uid, u.username, u.password, u.address, u.email FROM USER u WHERE u.id=#{id}</select>
constructor
使用 constructor 元素将结果注入构造方法里,先给 User 添加构造方法:
publicUser(String id,String name) {this.id= id +"--------";this.username= name +"--------";}
<resultMapid="userMap"type="User"> <idproperty="id"column="id"></id> <resultproperty="username"column="username"></result> <resultproperty="password"column="password"></result> <resultproperty="address"column="address"></result> <resultproperty="email"column="email"></result> <associationproperty="role"javaType="Role"> <idproperty="id"column="role_id"></id> <resultproperty="name"column="role_name"></result> </association></resultMap><selectid="getUserById"resultType="User"> SELECT u.id, u.username, u.password, u.address, u.email, r.id as 'role_id', r.name as 'role_name' FROM USER u LEFT JOIN user_roles ur ON u.id = ur.user_id LEFT JOIN role r ON r.id = ur.role_id where u.id=#{id}</select>
<selectid="getMenus"resultMap="menusMap"> SELECT m.id, m.name, m.url, m.parent_id FROM m_menu m where 1=1 <choose> <whentest="parent_id!=null"> and m.parent_id = #{parent_id} </when> <otherwise> and m.parent_id = '0' </otherwise> </choose></select>
在 Mybatis 解析返回值的时候,第一步是获取返回值类型,拿到 Class 对象,然后获取构造器,设置可访问并返回实例,然后又把它包装成 MetaObject 对象。
从数据库 rs 中拿到结果之后,会调用 MetaObject.setValue(String name, Object value) 来填充对象。在这过程中,它会以 . 来分隔这个 name 属性。如果 name 属性中包含 . 符号,就找到 . 符号之前的属性名称,把它当做一个实体对象来。
还是以上面获取用户角色为例,在该 SQL 语句:
<selectid="getUserList"resultType="User"> SELECT u.id, u.username, u.password, u.address, u.email, r.id as 'role.id', r.name as 'role.name' FROM USER u LEFT JOIN user_roles ur ON u.id = ur.user_id LEFT JOIN role r ON r.id = ur.role_id</select>
join
left join(左联接):返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接):返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(内连接):只返回两个表中联结字段相等的行
outer join(全连接):只要左表和右表其中一个表中存在匹配,则返回
假若有两张表 A 和 B,分别如下:
aID
aNum
1
a20050111
2
a20050112
3
a20050113
4
a20050114
5
a20050115
bID
bName
1
2006032401
2
2006032402
3
2006032403
4
2006032404
8
2006032408
left join
select*from Aleft join B on A.aID = B.bID
结果如下:
aID
aNum
bID
bName
1
a20050111
1
2006032401
2
a20050112
2
2006032402
3
a20050113
3
2006032403
4
a20050114
4
2006032404
5
a20050115
NULL
NULL
left join 是以 A 表的记录为基础的,A 可以看成左表,B 可以看成右表,left join 是以左表为准的。换句话说,左表 A 的记录将会全部表示出来,而右表 B 只会显示符合搜索条件的记录(例子中为: A.aID = B.bID),B 表记录不足的地方均为 NULL。
right join
select*from Aright join B on A.aID = B.bID
结果如下:
aID
aNum
bID
bName
1
a20050111
1
2006032401
2
a20050112
2
2006032402
3
a20050113
3
2006032403
4
a20050114
4
2006032404
NULL
NULL
8
2006032408
right join 和 left join 的结果刚好相反,这次是以右表 B 为基础的,A 表不足的地方用 NULL 填充。
inner join
select*from Ainner join B on A.aID = B.bID
结果如下:
aID
aNum
bID
bName
1
a20050111
1
2006032401
2
a20050112
2
2006032402
3
a20050113
3
2006032403
4
a20050114
4
2006032404
inner join 产生左表(A)和右表(B)的交集。
outer join
select*from Aouterjoin B on A.aID = B.bID
结果如下:
aID
aNum
bID
bName
1
a20050111
1
2006032401
2
a20050112
2
2006032402
3
a20050113
3
2006032403
4
a20050114
4
2006032404
5
a20050115
NULL
NULL
NULL
NULL
8
2006032408
outer join 产生左表(A)和右表(B)的并集。
实际用例
已之前写过的 Netty 聊天系统为例,用户查询好友添加请求和自己的好友列表,查询语句如下:
<selectid="queryFriendRequestList"parameterType="String"resultType="com.chanshiyu.pojo.vo.UsersVO"> select u.id, u.username, u.nickname, u.avatar from friends_request f left join users u on f.send_user_id = u.id where f.accept_user_id = #{acceptUserId}</select><selectid="queryFriendList"parameterType="String"resultType="com.chanshiyu.pojo.vo.UsersVO"> select u.id, u.username, u.nickname, u.avatar from my_friends m left join users u on m.my_friend_user_id = u.id where m.my_user_id = #{myUserId}</select>
通用 Mapper
通用 Mapper 就是为了解决单表增删改查,基于 Mybatis 的插件。开发人员不需要编写 SQL,不需要在 DAO 中增加方法,只要写好实体类,就能支持相应的增删改查方法。
方法:List<T> selectByExample(Object example); 说明:根据 Example 条件进行查询 重点:这个查询支持通过 Example 类指定查询列,通过 selectProperties 方法指定查询列。
方法:int selectCountByExample(Object example); 说明:根据 Example 条件进行查询总数。
方法:int updateByExample(@Param("record") T record, @Param("example") Object example); 说明:根据 Example 条件更新实体 record 包含的全部属性,null 值会被更新。
方法:int updateByExampleSelective(@Param("record") T record, @Param("example") Object example); 说明:根据 Example 条件更新实体 record 包含的不是 null 的属性值。
方法:int deleteByExample(Object example); 说明:根据 Example 条件删除数据。
Example 的使用
查询
Example example =newExample(Country.class);example.setForUpdate(true);example.createCriteria().andGreaterThan("id",100).andLessThan("id",151);example.or().andLessThan("id",41);List<Country> countries =mapper.selectByExample(example);// DEBUG [main] - ==> Preparing: SELECT id,countryname,countrycode FROM country WHERE ( id > ? and id < ? ) or ( id < ? ) ORDER BY id desc FOR UPDATE
// DEBUG [main] - ==> Parameters: 100(Integer), 151(Integer), 41(Integer)
动态 SQL
Example example =newExample(Country.class);Example.Criteria criteria =example.createCriteria();if(query.getCountryname() !=null){criteria.andLike("countryname",query.getCountryname() +"%");}if(query.getId() !=null){criteria.andGreaterThan("id",query.getId());}List<Country> countries =mapper.selectByExample(example);// DEBUG [main] - ==> Preparing: SELECT id,countryname,countrycode FROM country WHERE ( countryname like ? ) ORDER BY id desc
// DEBUG [main] - ==> Parameters: China%(String)
排序
Example example =newExample(Country.class);example.orderBy("id").desc().orderBy("countryname").orderBy("countrycode").asc();List<Country> countries =mapper.selectByExample(example);// DEBUG [main] - ==> Preparing: SELECT id,countryname,countrycode FROM country order by id DESC,countryname,countrycode ASC
// DEBUG [main] - ==> Parameters:
去重
CountryExample example =newCountryExample();//设置 distinctexample.setDistinct(true);example.createCriteria().andCountrynameLike("A%");example.or().andIdGreaterThan(100);List<Country> countries =mapper.selectByExample(example);// DEBUG [main] - ==> Preparing: SELECT distinct id,countryname,countrycode FROM country WHERE ( countryname like ? ) or ( Id > ? ) ORDER BY id desc
// DEBUG [main] - ==> Parameters: A%(String), 100(Integer)
设置查询列
Example example =newExample(Country.class);example.selectProperties("id","countryname");List<Country> countries =mapper.selectByExample(example);// DEBUG [main] - ==> Preparing: SELECT id , countryname FROM country ORDER BY id desc// DEBUG [main] - ==> Parameters:
Example.builder 方式
Example example =Example.builder(Country.class).select("countryname").where(Sqls.custom().andGreaterThan("id",100)).orderByAsc("countrycode").forUpdate().build();List<Country> countries =mapper.selectByExample(example);// DEBUG [main] - ==> Preparing: SELECT countryname FROM country WHERE ( id > ? ) order by countrycode Asc FOR UPDATE// DEBUG [main] - ==> Parameters: 100(Integer)
<selectid="queryTestList"resultMap="TestResult"> select * from test <where> id in <foreachitem="item"index="index"collection="ids"open="("close=")"separator=","> #{item} </foreach> order by field <foreachitem="item"index="index"collection="ids"open="(id,"close=")"separator=","> #{item} </foreach> </where></select>
示例:
List<Test>queryTestList(@Param("ids") List<String> ids);// select * FROM test where id in (1,3,2,5) order by field (id,1,3,2,5);
find_in_set()
举例:有个文章表里面有个 type 字段,它存储的是文章类型,有 1 头条、2 推荐、3 热点、4 图文等等。现在有篇文章他既是头条,又是热点,还是图文,type 中以 1,3,4 的格式存储。那我们如何用 sql 查找所有 type 中有 4 的图文类型的文章呢?
这里就需要用到 mysql 的 Find_IN_SET() 函数:
select * from article where FIND_IN_SET('4', type);
WHERE id IN<foreachcollection="ids"item="item"index="index"open="("separator=","close=")" > #{item}</foreach><iftest="tags!=null"> AND <foreachcollection="tags"item="tag"index="index"open=" ("separator=" OR "close=")"> d.tag LIKE CONCAT('%', #{tag}, '%') </foreach></if>
CONCAT('%', #{tag}, '%') 可以用 bind 实现:
<selectid="selectBlogsLike"resultType="Blog"> <bindname="pattern"value="'%' + title + '%'" /> SELECT * FROM BLOG WHERE title LIKE #{pattern}</select>
时间比较
WHERE create_time <![CDATA[>=]]> #{startTime} AND create_time <![CDATA[<=]]> #{endTime}WHERE create_time BETWEEN #{startTime} AND #{endTime}WHERE create_time BETWEEN CONCAT(DATE(#{startDate})," 00:00:00") AND CONCAT(DATE(#{endDate})," 23:59:59")WHERE create_time BETWEEN DATE(#{startDate}) AND DATE_ADD(DATE(#{endDate}),INTERVAL 1 DAY)
批量更新
更新单条记录:
UPDATE course SET name = 'course1' WHERE id = 'id1';
更新多条记录的同一个字段为同一个值:
UPDATE course SET name = 'course1' WHERE id in ('id1', 'id2', 'id3);
更新多条记录为多个字段为不同的值:
比较普通的写法,是通过循环,依次执行 update 语句:
<updateid="updateBatch"parameterType="java.util.List"> <foreachcollection="list"item="item"index="index"open=""close=""separator=";"> update course <set> name=${item.name} </set> where id = ${item.id} </foreach></update>
这样做一条记录 update 一次,性能比较差,容易造成阻塞。
MySQL 没有提供直接的方法来实现批量更新,但可以使用 case when 语法来实现这个功能:
UPDATE course SET name = CASE id WHEN 1 THEN 'name1' WHEN 2 THEN 'name2' WHEN 3 THEN 'name3' END, title = CASE id WHEN 1 THEN 'New Title 1' WHEN 2 THEN 'New Title 2' WHEN 3 THEN 'New Title 3' ENDWHERE id IN (1,2,3)
这条 sql 的意思是,如果 id 为 1,则 name 的值为 name1,title 的值为 New Title1;依此类推。
// 有注入风险
apply("date_format(dateColumn,'%Y-%m-%d') = '2008-08-08' or true or true")
// 安全
apply("date_format(dateColumn,'%Y-%m-%d') = {0}", "2008-08-08 or true or true")