mybatis是持久层框架,用于简化JDBC,具体就不作复述了。
这里重点阐述下mybatis注解的方式对比传统jdbc完成复杂sql编写。
注解方式是mybatis的独有的,关键的几个@Select @Insert @Delete@Update都是ibatis下的,如@Select
注解的方式极大地简化了sql的编写过程,如在接口文件里
对比JDBC标签写法
会发现能省去id,和resultMap的配置,代码也易读性也增强了不少。
接下来我要介绍下面各种情况的sql写法,
1.首先是基础sql,如上两个图就是了
2.联表查询写法
根据是否需要全表信息查询再分为两类
a.只须用条件表的条件,查询目标表的所有信息的
//多对多 任务对角色多表查询举例
//interfaceMapper.java
@Select("select * from user a\n" +
" inner join user_role b on a.id = b.user_id\n" +
" inner join role c on b.role_id = c.role_id\n" +
" where a.id = #{uid}")
List<Role> findRolesByUserIdTest(Integer uid);
//pojo
public class Role{
private Integer oId;
private Integer userId;
private String number;
private Timestamp createTime;
private String note;
//角色Role实体类里配置了人物属性
private List<User> user;
...}
//test
List<Role> roles = mapper.findRolesByUserIdTest(1);
for (Role role : roles) {
System.out.println(role);
}
//运行结果(1个人物user对应多个角色role)
//Role{roleId=1, roleName='校长', roleDetail='全校的管理者', users=null}
//Role{roleId=2, roleName='讲师', roleDetail='传道授业解惑', users=null}
//Role{roleId=3, roleName='班主任', roleDetail='班级的灵魂', users=null}
b.除了目标表信息,同时也需要条件表的所有信息的
//多对多 任务对角色多表查询举例
//interfaceMapper.java
@Select("select * from user a\n" +
" inner join user_role b on a.id = b.user_id\n" +
" inner join role c on b.role_id = c.role_id\n" +
" where a.id = #{uid}")
@Results({ //如没有这个@Results部分,下面user查询结果会为null,如上面那个例子
@Result(column = "role_id",property = "roleId", id = true),
@Result(column = "role_name",property = "roleName"),
@Result(column = "role_detail",property = "roleDetail"),
@Result(property = "users",column = "id",many = @Many(select = "findUserByIdParam"))
})
List<Role> findRolesByUserIdTest(Integer uid);
//pojo
public class Role {
private Integer oId;
private Integer userId;
private String number;
private Timestamp createTime;
private String note;
//角色Role实体类里配置了人物属性
private List<User> user;
...}
//test
List<Role> roles = mapper.findRolesByUserIdTest(1);
for (Role role : roles) {
System.out.println(role);
}
//运行结果(1个人物user对应多个角色role)
//Role{roleId=1, roleName='校长', roleDetail='全校的管理者', users=[id=1, username='孙悟空', birthday=1980-10-24, sex='男', address='花果山水帘洞', userInfo=null, orders=null, roles=null]}
//Role{roleId=2, roleName='讲师', roleDetail='传道授业解惑', users=[id=1, username='孙悟空', birthday=1980-10-24, sex='男', address='花果山水帘洞', userInfo=null, orders=null, roles=null]}
//Role{roleId=3, roleName='班主任', roleDetail='班级的灵魂', users=[id=1, username='孙悟空', birthday=1980-10-24, sex='男', address='花果山水帘洞', userInfo=null, orders=null, roles=null]}
结论:@Results部分是为了映射其它表字段的,没有配置也能查出主表结果,就是关联表的数据不会有,因为没能映射user的字段过来。
顺带也介绍普通配置的写法(mybatis也能用)
<resultMap id="RoleMap" type="Role">
<id column="role_id" property="roleId"/>
<result column="role_name" property="roleName"/>
<result column="role_detail" property="roleDetail"/>
<collection property="users" ofType="User">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</collection>
</resultMap>
<select id="findRoleAUserById" resultMap="RoleMap">
select * from user a
inner join user_role b on a.id = b.user_id
inner join role c on b.role_id = c.role_id
where a.id = #{uid}
</select>
<!--其它mapper、pojo、及运行结果和上面的举例基本一样-->
回到文章主题上,链表复杂查询有了,那类似Update set这种动态查询, 这类的动态sql标签怎么用上呢。
<select id="selectByIdsMap" resultType="pojo.User">
select * from user
<where>
<if test="username!=null and username!=''">
username like #{username}
</if>
<if test="sex!=null and sex!=''">
and sex = #{sex}
</if>
</where>
</select>
<select id="selectByIds" resultType="pojo.User">
select * from user where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</select>
<update id="updateByIdName">
update user
<set>
<if test="username != null and username != ''">
username=#{username},
</if>
<if test="birthday != null">
birthday=#{birthday},
</if>
<if test="sex != null and sex != ''">
sex=#{sex},
</if>
<if test="address != null and address != ''">
address=#{address}
</if>
</set>
where id=#{id}
</update>
对应的注解写法也不难
//<foreach></foreach>引用
@Select("<script>"
+ "select * from table_name where grade=#{grade}' and num in "
+ "<foreach item = 'num' index = 'index' collection = 'numList' open='(' separator=',' close=')'>"
+ "#{num}"
+ "</foreach>"
+ "</script>")
List<Student> getListByNum(@Param("grade") String grade, @Param("numList") List<String> numList);
//<if test = ""></if>标签的引用
//<when test = ''></when>标签引用
//替代where\set\if 三个标签做法
@Update("<script> update table_name set grade='三年级' " +
" <if test=\"name != null\"> , name = #{name} </if> " +
" <if test=\"sex != null\"> , sex = #{sex}</if>" +
" where num = #{num}</script>")
void update(Student student);
//注意点:1.都包含再<script>标签里,2.when test后面是单引号
————————————————
//原文链接:https://blog.csdn.net/weixin_45561263/article/details/115629739
//博题:mybatis注解在方法直接编写SQL@Select@Insert@Update@Delete
结论:条件查询这种的,可读性就没有普通写法那么好了
暂时还不知道 if test 和when test的区别,有大佬知道可以回复指点下,万分感谢!