目录
使用注解开发
设置事务自动提交
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 { @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); } 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 tname from 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 tid from student s,teacher t where 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 mapper PUBLIC "-//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.Test public 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 mapper PUBLIC "-//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.Test public 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的一个列表list ids.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(); }
运行结果