准备数据
创建数据库,创建表
create database note; use note; CREATE TABLE `teacher` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `t_name` varchar(255) DEFAULT NULL COMMENT '姓名', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `note`.`teacher`(`id`, `t_name`) VALUES (1, '姜文'); INSERT INTO `note`.`teacher`(`id`, `t_name`) VALUES (2, '罗翔'); CREATE TABLE `student` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `s_name` varchar(255) DEFAULT NULL COMMENT '姓名', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `note`.`student`(`id`, `s_name`) VALUES (1, '张三'); INSERT INTO `note`.`student`(`id`, `s_name`) VALUES (2, '李四'); INSERT INTO `note`.`student`(`id`, `s_name`) VALUES (3, '王五');
创建SpringBoot项目
创建项目,导入相关依赖
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-freemarker</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.72</version> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.4.2</version> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.4</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> <scope>runtime</scope> <optional>true</optional> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> <exclusions> <exclusion> <groupId>org.junit.vintage</groupId> <artifactId>junit-vintage-engine</artifactId> </exclusion> </exclusions> </dependency> </dependencies>
配置.yml文件
mybatis: mapper-locations: classpath:/mybatis/**/*.xml type-aliases-package: com.example.note.domain map-underscore-to-camel-case: true mybatis-plus: configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl typeAliasesPackage: com.example.note.domain mapperLocations: classpath:mybatis/**/*.xml global-config: db-config: id-type: auto server: port: 8080 spring: application: name: note datasource: driver-class-name: com.mysql.cj.jdbc.Driver name: defaultDataSource password: '123456789' url: jdbc:mysql://localhost:3306/note?serverTimezone=UTC username: 'root'
编写内容
1.创建test包,创建Student,Teacher类
package com.example.note.test.domain; import lombok.Data; @Data public class Student { private Long id; private String sName; }
package com.example.note.test.domain; import lombok.Data; @Data public class Teacher { private Long id; private String tName; }
2.创建mapper接口(注意添加@Mapper注解)
package com.example.note.test.mapper; import org.apache.ibatis.annotations.Mapper; @Mapper public interface TestMapper { }
3.创建业务service接口
package com.example.note.test.service; public interface ITestService { }
业务service类(注意添加@Service注解)
package com.example.note.test.service.impl; import org.springframework.stereotype.Service; @Service public class TestServiceImpl extends ITestService { }
4.创建控制层controller
package com.example.note.test.controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; @RestController @RequestMapping("test") public class TestController { }
5.在resources目录下创建mybatis/test目录,创建TestMapper.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"> <!--namespace的值是我们要映射的TestMapper的路径--> <mapper namespace="com.example.note.test.mapper.TestMapper"> </mapper>
Mybatis编写
1.查询:,写在<select>标签中,<select id="" resultType="" resultMap="">,id是在命名空间中唯一的标识符。resultType期望从这条语句中返回结果的类全限定名或别名,我们写的Javabean类路径或Java包装类路径。resultMap对外部 <resultMap>标签 的命名引用。resultType 和 resultMap 之间只能同时使用一个。用法如下(编辑TestMapper.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"> <!--namespace的值是我们要映射的TestMapper的路径--> <mapper namespace="com.example.note.test.mapper.TestMapper"> <!--获取全部学生信息--> <select id="getAllStudent" resultType="com.example.note.test.domain.Student"> select * from student </select> </mapper>
编写TestMapper
package com.example.note.test.mapper; import com.example.note.test.domain.Student; import org.apache.ibatis.annotations.Mapper; import java.util.List; @Mapper public interface TestMapper { public List<Student> getAllStudent(); }
编写ITestService
package com.example.note.test.service; import com.example.note.test.domain.Student; import java.util.List; public interface ITestService { public List<Student> getAllStudent(); }
编写TestServiceImpl
package com.example.note.test.service.impl; import com.example.note.test.domain.Student; import com.example.note.test.mapper.TestMapper; import com.example.note.test.service.ITestService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; @Service public class TestServiceImpl implements ITestService { @Autowired private TestMapper testMapper; public List<Student> getAllStudent(){ return this.testMapper.getAllStudent(); } }
编写TestController
package com.example.note.test.controller; import com.example.note.test.domain.Student; import com.example.note.test.service.impl.TestServiceImpl; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; @RestController @RequestMapping("test") public class TestController { @Autowired private TestServiceImpl testService; @RequestMapping("getAllStudent") public List<Student> getAllStudent(){ return this.testService.getAllStudent(); } }
前端访问:
2.条件查询:<if test="">条件判断语句,满足test里面的条件,执行<if>标签里面的语句。
<mapper namespace="com.example.note.test.mapper.TestMapper"> <select id="getAllStudent" resultType="com.example.note.test.domain.Student"> select * from student <!--id!=null 会执行select * from student where id = #{id} 否则执行 select * from student--> <if test="id!=null"> where id = #{id} </if> </select> </mapper>
3.条件查询<where><if>:满足test里面的条件,执行<if>标签里面的语句。
<mapper namespace="com.example.note.test.mapper.TestMapper"> <select id="getAllStudent" resultType="com.example.note.test.domain.Student"> select * from student <!-- 满足哪个条件就会执行哪条语句,如果id=null但是name!=null 会执行select * from student where s_name= #{name} 在执行时跟据条件把 and 自动消除掉--> <where> <if test="id!=null"> id = #{id} </if> <if test="name!=null"> and s_name = #{name} </if> </where> </select> </mapper>
4.插入:写在<insert>标签中,<insert id="" parameterType="" keyProperty="" keyColumn="" useGeneratedKeys="">id是命名空间唯一标识符;parameterType入参类型;keyProperty指定能够唯一识别对象的属性;keyColumn设置生成键值在表中的列名;MyBatis 使用 JDBC 的 getGeneratedKeys 方法来取出由数据库内部生成的主键。
<mapper namespace="com.example.note.test.mapper.TestMapper"> <insert id="setStudent" parameterType="com.example.note.test.domain.Student"> insert into student (s_name) values (#{student.name}) </insert> </mapper>
5.遍历语句<foreach item="item" index="index" collection="list" open="(" separator="," close=")">item是循环中的一个对象;index索引变量;collection传入的对象集合的名称;open指定开头的字符串;separator指定的集合项迭代之间的分隔符;close指定的结尾的字符串。批量插入操作如下:
<mapper namespace="com.example.note.test.mapper.TestMapper"> <insert id="setStudent" parameterType="com.example.note.test.domain.Student"> insert into student (s_name) values <foreach collection="student" item="item" index="index" separator="," > ( #{item.sName} ) </foreach> </insert> </mapper>
6.返回最后的自增id(相当于执行 select LAST_INSERT_ID()):
<mapper namespace="com.example.note.test.mapper.TestMapper"> <insert id="setStudent" useGeneratedKeys="true" keyColumn="id" keyProperty="id" parameterType="com.example.note.test.domain.Student"> insert into student (s_name) values (#{student.sName} ) </insert> </mapper>
7.修改:写在<update>标签中,<update id="" parameterType="" keyProperty="" keyColumn="" useGeneratedKeys="">id是命名空间唯一标识符;parameterType入参类型;keyProperty指定能够唯一识别对象的属性;keyColumn设置生成键值在表中的列名; MyBatis 使用 JDBC 的 getGeneratedKeys 方法来取出由数据库内部生成的主键。
<mapper namespace="com.example.note.test.mapper.TestMapper"> <!--参数是一个Student对象--> <insert id="setStudent" parameterType="com.example.note.test.domain.Student"> update student set s_name=#{student.sName} where id=#{student.id} </insert> </mapper>
8.删除:写在<delete>标签中,<delete id="" parameterType="" >id是命名空间唯一标识符;parameterType入参类型。
<mapper namespace="com.example.note.test.mapper.TestMapper"> <!--删除id等于id的数据--> <delete id="deleteStudent"> delete from student where id=#{id} </delete> </mapper>
9.结果映射:<resultMap id="" type="">,id是在命名空间中唯一的标识符。type期望从这条语句中返回结果的类全限定名或别名(Javabean类路径)。用法如下:
<mapper namespace="com.example.note.test.mapper.TestMapper"> <select id="getStudentTeacher" resultMap="getMap"> select * from student </select> <resultMap id="getMap" type="com.example.note.test.domain.Student"> <result column="id" property="id"/> <result column="s_name" property="sName"/> </resultMap> </mapper>
10.关联:<association property="" javaType="">property映射到列结果的字段或属性;javaType一个 Java 类的完全限定名,或一个类型别名。用法如下(一对一,多对一查询):
修改数据库:
CREATE TABLE `student` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `s_name` varchar(255) DEFAULT NULL COMMENT '姓名', `t_id` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4;
修改Student类:
package com.example.note.test.domain; import lombok.Data; @Data public class Student { private Long id; private String sName; private Long tId; private Teacher teacher; }
编辑xml
<mapper namespace="com.example.note.test.mapper.TestMapper"> <!-- 查询学生及对应的老师 --> <select id="getStudentTeacher" resultMap="getMap"> select * from student left join teacher on student.t_id=teacher.id </select> <resultMap id="getMap" type="com.example.note.test.domain.Student"> <result column="id" property="id"/> <result column="s_name" property="sName"/> <result column="teacher.id" property="tId"/> <association property="teacher" javaType="com.example.note.test.domain.Teacher"> <result column="id" property="id"/> <result column="t_name" property="tName"/> </association> </resultMap> </mapper>
查询结果:
11.集合:<collection property="" ofType="">property映射到列结果的字段或属性;ofType一个 Java 类的完全限定名,或一个类型别名。用法如下(一对多查询):
修改Student类:
package com.example.note.test.domain; import lombok.Data; @Data public class Student { private Long id; private String sName; }
修改Teacher类:
package com.example.note.test.domain; import lombok.Data; import java.util.List; @Data public class Teacher { private Long id; private String tName; private List<Student> students; }
编辑xml
<mapper namespace="com.example.note.test.mapper.TestMapper"> <!-- 查询老师及对应的学生 (一对多) --> <select id="getTeacherStudents" resultMap="getMap"> select * from teacher left join student on student.t_id=teacher.id </select> <resultMap id="getMap" type="com.example.note.test.domain.Teacher"> <result column="id" property="id"/> <result column="t_name" property="tName"/> <collection property="students" ofType="com.example.note.test.domain.Student"> <result column="id" property="id"/> <result column="s_name" property="sName"/> </collection> </resultMap> </mapper>
查询结果:
总结
上述就是我在平时学习和SpringBoot项目里面用到的比较多的mybatis标签及他们的简单用法。