Mybatis03学习笔记
目录
使用注解开发
设置事务自动提交
mybatis运行原理
注解CRUD
lombok使用(偷懒神器,大神都不建议使用)
复杂查询环境(多对一)
复杂查询环境(一对多)
动态sql环境搭建
动态sql常用标签
使用注解开发
注解本质是反射机制,使用注解可以使得代码内容得到简化,注解可以取代注册文件UUserMapper.xml
如下面定义接口,在接口上方定义注解写sql语句
public interface UserMapper {@Select("select * from user")List<User> getUsers(); }
需要在核心配置文件绑定接口
设置事务自动提交
基于源码
我们可以设置事务自动提交
SqlSession sqlSession=sqlSessionFactory.openSession(true);
mybatis运行原理
注解CRUD
查询所有用户
接口
@Select("select * from user") List<User> getUsers();
测试
@Test public void getUsers(){SqlSession sqlSession= MybatisUtils.getSqlSession();UserMapper mapper=sqlSession.getMapper(UserMapper.class);List<User> users=mapper.getUsers();for(User user:users){System.out.println(user);}sqlSession.close(); }
根据id查询用户
接口
@Select("select * from user where id=#{35342}")User getUserById(@Param("35342") int id);
测试
public void getUsers(){SqlSession sqlSession= MybatisUtils.getSqlSession();UserMapper mapper=sqlSession.getMapper(UserMapper.class);User userById=mapper.getUserById(1);System.out.println(userById);sqlSession.close(); }
增加用户
接口
@Insert("insert into user(id,name,pwd) values(#{id},#{name},#{pwd})")int addUser(User user);
测试
SqlSession sqlSession= MybatisUtils.getSqlSession(); UserMapper mapper=sqlSession.getMapper(UserMapper.class); mapper.addUser(new User(12,"hello","Ssdd"));
修改用户信息
接口
@Update("UPDATE USER set NAME=#{name},PWD=#{pwd} where id=#{id}") int updateUser(User user);
测试
SqlSession sqlSession= MybatisUtils.getSqlSession(); UserMapper mapper=sqlSession.getMapper(UserMapper.class); mapper.updateUser(new User(12,"ttt","12443"));
删除用户
接口
@Delete("delete from user where id=#{id}") int delUser(int id);
测试
SqlSession sqlSession= MybatisUtils.getSqlSession(); UserMapper mapper=sqlSession.getMapper(UserMapper.class); mapper.delUser(12);
接口总的代码:
package com.dao;import com.pojo.User; import org.apache.ibatis.annotations.*;import java.util.List; import java.util.Map;public interface UserMapper { @Select("select * from user")List<User> getUsers(); // 通过id查询 @Select("select * from user where id=#{35342}")User getUserById(@Param("35342") int id); @Insert("insert into user(id,name,pwd) values(#{id},#{name},#{pwd})")int addUser(User user); @Update("UPDATE USER set NAME=#{name},PWD=#{pwd} where id=#{id}") int updateUser(User user); @Delete("delete from user where id=#{id}") int delUser(int id); }
测试总的代码:
import com.dao.UserMapper; import com.pojo.User; import com.utils.MybatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import java.util.List; public class test {@Testpublic void getUsers(){SqlSession sqlSession= MybatisUtils.getSqlSession();UserMapper mapper=sqlSession.getMapper(UserMapper.class);List<User> users=mapper.getUsers();for(User user:users){System.out.println(user);}User userById=mapper.getUserById(1);System.out.println(userById);mapper.updateUser(new User(12,"ttt","12443"));mapper.addUser(new User(12,"hello","Ssdd"));mapper.delUser(12);sqlSession.close();} }
关于@Params()注解:
有多个参数则所有参数前一定要加上@Params()
lombok使用(偷懒神器,大神都不建议使用)
自动帮我们优化一些变量,但是会淡化我们对源码的理解
使用:导入jar包,在实体类上方加注解即可
@Data取代getter和setter方法
@AllArgsConstructor生成所有有参构造
@NoArgsConstructor生成所有无参构造
@ToString
如可以帮助我们写get,set,toString方法等
复杂查询环境(多对一)
实体类准备
//多对一 @Data @ToString public class Student {private int id;private String name;private Teacher teacher; }
@Data @ToString public class Teacher {private int id;private String name;}
tid是一个外键,用通常的方法:写接口-配置的xml文件-测试类查询student表信息,,这种方法查出来无法查到具有复制属性的tid
因此我们应该在Student.xml文件中通过某种方式关联上复杂属性tid才能查出来,下面有两种方式:
1.类似子查询方式
Test.java代码:
public void testStudent(){SqlSession sqlSession=MybatisUtils.getSqlSession();StudentMapper mapper1=sqlSession.getMapper(StudentMapper.class);List<Student> studentList=mapper1.getStudent();for(Student student:studentList){System.out.println(student);}sqlSession.close();}
Student.xml代码:
<!--通过子查询方式--><select id="getStudent" resultMap="StudentTeacher" >select * from student</select><!-- 结果集查询--><resultMap id="StudentTeacher" type="com.pojo.Student"><result property="id" column="id"/><result property="name" column="name"/><!-- 复制的属性tid是外键,要单独处理--><association property="teacher" column="tid" javaType="com.pojo.Teacher" select="getTeacher"/></resultMap><select id="getTeacher" resultType="com.pojo.Teacher">select * from teacher where id=#{id}</select>
2.结果集映射方式
Test.java代码不变,Student.xml代码:
<!-- 通过结果嵌套处理--><select id="getStudent" resultMap="StudentTeacher2">select s.id sid,s.name sname,t.name tnamefrom student s,teacher t where s.tid=t.id;</select><resultMap id="StudentTeacher2" type="com.pojo.Student"><result property="id" column="sid"/><result property="name" column="sname"/><association property="teacher" javaType="com.pojo.Teacher"><result property="name" column="tname"/></association></resultMap>
查询结果:
复杂查询环境(一对多)
实体类准备
//一对多 @Data @ToString public class Student {private int id;private String name;private int tid; }
@Data @ToString public class Teacher {private int id;private String name;//一个老师拥有的学生集合private List<Student> students; }
TeacherMapper接口代码:
//获取指定老师下的所有学生 Teacher getTeacher(@Param("tid") int id);
TeacherMapper.xml代码
<!-- 方式一按结果嵌套查询--><select id="getTeacher" resultMap="TeacherStudent">select s.id sid,s.name sname,t.name tname,t.id tidfrom student s,teacher twhere s.tid=t.id and t.id=#{tid}</select><resultMap id="TeacherStudent" type="com.pojo.Teacher"><result property="id" column="tid"/><result property="name" column="tname"/> <!-- 实体类teacher还有一个学生集合的属性,javaType指定属性的类型 复杂属性我们要单独处理,集合中泛型得到信息用ofType获取, private List<Student>--><collection property="students" ofType="com.pojo.Student"><result property="id" column="sid"/><result property="name" column="sname"/><result property="tid" column="tid"/></collection></resultMap>
<!-- 方式二按子查询--><select id="getTeacher" resultMap="TeacherStudent">select * from mybatis.teacher where id=#{tid}</select><resultMap id="TeacherStudent" type="com.pojo.Teacher"><collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"/></resultMap><select id="getStudentByTeacherId" resultType="com.pojo.Student">select * from mybatis.student where tid=#{tid}</select>
测试代码
public void test() {SqlSession sqlSession = MybatisUtils.getSqlSession();TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);Teacher teacher = mapper.getTeacher(1);System.out.println(teacher);sqlSession.close(); }
查询结果:
关于type,resultType, ofType,javaType:
1.type在resultMap标签中使用
2.resultType用于select标签中,表示sql语句返回的对应在Java中的类型
2.ofType用来指定映射到List或集合中的pojo的类型,泛型中的约束类型
3.javaType是用来指定实体类的属性的类型
动态sql环境搭建
这部分相当于回顾之前所学
编写获取UUID的工具类
public class IDutils {public static String getId(){return UUID.randomUUID().toString().replaceAll("-","");} // @Test // public void test(){ // System.out.println(IDutils.getId()); // } }
编写java映射数据库ORM实体类
public class Blog {private String id;private String title;private String author;private Date createTime; //属性和字段不一致private int views; }
编写添加接口BlogMapper
public interface BlogMapper {int addBlog(Blog blog); }
BlogMapper.xml注册sql文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""https://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.dao.BlogMapper"><insert id="addBlog" parameterType="com.pojo.Blog">insert into mybatis.blog(id,title,author,create_time,views)values(#{id},#{title},#{author},#{createTime},#{views});</insert> </mapper>
测试代码:
public class Test {@org.junit.Testpublic void addInitBlog(){SqlSession sqlSession= MybatisUtils.getSqlSession();BlogMapper mapper=sqlSession.getMapper(BlogMapper.class);Blog blog=new Blog();blog.setId(IDutils.getId());blog.setTitle("MyBATIS如此简单");blog.setAuthor("狂");blog.setCreateTime(new Date());blog.setViews(9999);mapper.addBlog(blog);blog.setId(IDutils.getId());blog.setTitle("java如此简单");mapper.addBlog(blog);blog.setId(IDutils.getId());blog.setTitle("spring如此简单");mapper.addBlog(blog);blog.setId(IDutils.getId());blog.setTitle("vue如此简单");mapper.addBlog(blog);sqlSession.close();} }
测试结果:
动态sql常用标签
<if>标签测试
接口
List<Blog> queryBlogIF(Map map);
对应注册文件
if标签如果满足标签内部的条件,就执行
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""https://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.dao.BlogMapper"><insert id="addBlog" parameterType="com.pojo.Blog">insert into mybatis.blog(id,title,author,create_time,views)values(#{id},#{title},#{author},#{createTime},#{views});</insert><select id="queryBlogIF" parameterType="map" resultType="com.pojo.Blog">select * from mybatis.blog where 1=1<if test="title !=null">and title=#{title}</if><if test="author !=null">and author=#{author}</if></select> </mapper>
测试代码
public class Test {@org.junit.Testpublic void queryBlogIF(){SqlSession sqlSession=MybatisUtils.getSqlSession();BlogMapper mapper=sqlSession.getMapper(BlogMapper.class);List<Blog> blogs=mapper.queryBlogIF(new HashMap());for(Blog blog:blogs){System.out.println(blog);}} }
choose(when,otherwise)
<select id="queryBlogChoose" parameterType="map" resultType="com.pojo.Blog">select * from mybatis.blog<choose><when test="title!=null">title=#{title}</when><when test="author!=null">and author=#{author}</when><otherwise>and views=#{views}</otherwise></choose> </select>
测试代码
@org.junit.Test public void queryBlogChoose(){SqlSession sqlSession=MybatisUtils.getSqlSession();BlogMapper mapper=sqlSession.getMapper(BlogMapper.class);Map map=new HashMap();List<Blog> blogs=mapper.queryBlogIF(map);for(Blog blog:blogs){System.out.println(blog);}sqlSession.close();}
trim(where,set)
1.where元素只会在至少一个子元素的条件返回sql子句的情况下才去插入where子句,而且若语句开头为and或者or,where元素会自动将其去掉,如:
2.set+<update>标签
接口
int updateBlog(Map map);
xml代码
<update id="updateBlog" parameterType="map">update mybatis.blog<set><if test="title!=null">title=#{title},</if><if test="author!=null">author=#{author}</if></set>where id=#{id} </update>
测试代码
public void updateBlog(){SqlSession sqlSession=MybatisUtils.getSqlSession();BlogMapper mapper=sqlSession.getMapper(BlogMapper.class);Map map=new HashMap();map.put("title","vue如此简单2"); //相当于显示条件,只查找title="java如此简单"的记录map.put("id","0ae64f7aa9c94d2e8b10046d902dc4cd");mapper.updateBlog(map);sqlSession.close(); }
运行结果:
解决代码冗余<sql>标签和<include>标签
将重复的代码提取出来用sql标签包装,在需要使用的地方用include标签使用
如
<foreach>标签
接口
List<Blog> queryBlogForEach(Map map);
xml代码
<select id="queryBlogForEach" parameterType="map" resultType="com.pojo.Blog">select * from mybatis.blog<where><foreach collection="ids" item="id" open="and (" close=")" separator="or">id=#{id}</foreach></where> </select>
测试代码
@org.junit.Test public void queryBlogForEach(){SqlSession sqlSession=MybatisUtils.getSqlSession();BlogMapper mapper=sqlSession.getMapper(BlogMapper.class);Map map=new HashMap();ArrayList<Integer> ids=new ArrayList<Integer>();//存放interger的一个列表listids.add(1);ids.add(2);map.put("ids",ids);List<Blog> blogs=mapper.queryBlogForEach(map);for(Blog blog:blogs){System.out.println(blog);}sqlSession.close(); }
运行结果