Sprintboot+MyBatis配置
一、基本配置
- pom.xml配置
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
- application.properties配置
# mysql配置
spring.datasource.url=jdbc:mysql://192.168.188.11:3306/wechat_store?useUnicode=true&characterEncoding=utf-8
spring.datasource.username=root
spring.datasource.password=666666
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
二、基本使用(基于注解)
- 创建实体类
package net.ken.shop.wx.domain.po;
import lombok.Data;
@Data
public class UserPO {
public UserPO(int id, String userName) {
this.id = id;
this.userName = userName;
}
private int id;
private String userName;
}
- 创建Mapper类
package net.ken.shop.wx.mapper;
import net.ken.shop.wx.domain.po.UserPO;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import java.util.List;
//@Mapper
public interface UserMapper {
@Insert("insert into user(id,user_name) values(#{id},#{userName})")
int insertT(UserPO userPO);
@Delete("delete from user where id = #{id}")
int deleteById(int id);
@Delete("delete from user")
int deleteAll();
@Update("update user set user_name=#{userName} where id = #{id}")
int updateT(UserPO userPO);
@Select("select * from user where id = #{id}")
UserPO findUserById(int id);
@Select("select * from user")
List<UserPO> findAll();
}
- 启动类中增加扫描Mapper类
package net.ken.shop;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan(basePackages= {"net.ken.shop.wx.mapper"})
public class ShopApplication {
public static void main(String[] args) {
SpringApplication.run(ShopApplication.class, args);
}
}
- 测试类
package net.ken.shop;
import net.ken.shop.wx.domain.po.UserPO;
import net.ken.shop.wx.mapper.UserMapper;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
@RunWith(SpringRunner.class)
@SpringBootTest
public class MybatisTests {
@Autowired
UserMapper userMapper;
@Before
public void testBefore(){
userMapper.deleteAll();
}
@Test
public void testUserMapper(){
Assert.assertEquals(1,userMapper.insertT(new UserPO(1,"ken")));
Assert.assertEquals(1,userMapper.deleteById(1));
userMapper.insertT(new UserPO(1,"ken"));
Assert.assertEquals(1,userMapper.updateT(new UserPO(1,"joy")));
Assert.assertEquals("joy",userMapper.findUserById(1).getUserName());
userMapper.insertT(new UserPO(2,"ken"));
Assert.assertEquals(2,userMapper.findAll().size());
}
}
-
执行测试类后,数据库中数据
-
备注1
Mapper需要添加注解才能被springboot扫描到并加入容器管理,方式如下两种,个人推荐方式2
- Mapper类中上增加 @Mapper
- 启动类中增加 @MapperScan(basePackages= {“net.ken.shop.wx.mapper”})
- 备注2
数据库中字段为下划线“user_name”,po类中为驼峰"userName",查询的时候会自动转换
三、基本使用(基于xml)
- application.properties配置
# mybatis 配置 resource路径下
mybatis.mapper-locations=classpath:mapper/*Mapper.xml
- 启动类配置
package net.havejoy.springboot_mybatis_xml;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("net.havejoy.mapper")
public class SpringbootMybatisXmlApplication {
public static void main(String[] args) {
SpringApplication.run(SpringbootMybatisXmlApplication.class, args);
}
}
- 创建实体类
package net.havejoy.domain;
import lombok.Data;
@Data
public class UserPO {
public UserPO(int id, String userName) {
this.id = id;
this.userName = userName;
}
private int id;
private String userName;
}
- 创建Mapper类
package net.havejoy.mapper;
import net.havejoy.domain.UserPO;
import java.util.List;
public interface UserMapper {
UserPO findById(int id);
List<UserPO> findAll();
void insertT(UserPO userPO);
void updateT(UserPO userPO);
void deleteById(int id);
void deleteAll();
}
- 创建Mapper.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" >
<mapper namespace="net.havejoy.mapper.UserMapper">
<select id="findById" resultType="net.havejoy.domain.UserPO" parameterType="java.lang.Integer">
select * from user where id = #{id}
</select>
<select id="findAll" resultType="net.havejoy.domain.UserPO">
select * from user
</select>
<insert id="insertT" parameterType="net.havejoy.domain.UserPO">
insert into user (id,user_name) values (#{id},#{userName})
</insert>
<update id="updateT" parameterType="net.havejoy.domain.UserPO">
update user set user_name=#{userName} where id = #{id}
</update>
<delete id="deleteById" parameterType="java.lang.Integer">
delete from user where id = #{id}
</delete>
<delete id="deleteAll" parameterType="java.lang.Integer">
delete from user
</delete>
</mapper>
- 测试类
package net.havejoy.springboot_mybatis_xml;
import net.havejoy.domain.UserPO;
import net.havejoy.mapper.UserMapper;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
@RunWith(SpringRunner.class)
@SpringBootTest
public class SpringbootMybatisXmlApplicationTests {
@Autowired
UserMapper userMapper;
@Test
public void contextLoads() {
}
@Test
public void testUserMapper(){
userMapper.deleteAll();
Assert.assertEquals(0,userMapper.findAll().size());
userMapper.insertT(new UserPO(1,"ken"));
Assert.assertEquals("ken",userMapper.findById(1).getUserName());
userMapper.updateT(new UserPO(1,"joy"));
Assert.assertEquals("joy",userMapper.findById(1).getUserName());
userMapper.deleteById(1);
Assert.assertEquals(0,userMapper.findAll().size());
userMapper.insertT(new UserPO(1,"ken"));
userMapper.insertT(new UserPO(2,"joy"));
Assert.assertEquals(2,userMapper.findAll().size());
}
}
四、关联查询(基于注解)
对象包含:
- student:学生
- class:班级
- teacher:教师
相互关系:
班级和教师:一对一
班级和学生:一对多
- 数据库初始化:
CREATE TABLE class (
c_id int(11) NOT NULL AUTO_INCREMENT,
c_name varchar(255) DEFAULT NULL,
teacher_id int(11) DEFAULT NULL,
PRIMARY KEY (c_id)
);
CREATE TABLE teacher (
t_id int(11) NOT NULL AUTO_INCREMENT,
t_name varchar(255) DEFAULT NULL,
PRIMARY KEY (t_id)
);
CREATE TABLE student (
s_id int(11) NOT NULL AUTO_INCREMENT,
s_name varchar(255) DEFAULT NULL,
class_id int(11) DEFAULT NULL,
PRIMARY KEY (s_id)
);
INSERT INTO class VALUES ('1', 'class1', '1');
INSERT INTO class VALUES ('2', 'class2', '2');
INSERT INTO teacher VALUES ('1', 'teacher1');
INSERT INTO teacher VALUES ('2', 'teacher2');
INSERT INTO student VALUES ('1', 'student1', '1');
INSERT INTO student VALUES ('2', 'student2', '1');
- 创建实体类:
package net.havejoy.springboot_mybatis_annotation.domain;
import lombok.Data;
import java.util.List;
@Data
public class Class {
private int id;
private String name;
private Teacher teacher;
private List<Student> students;
}
package net.havejoy.springboot_mybatis_annotation.domain;
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
private String classId;
private Class aClass;
}
package net.havejoy.springboot_mybatis_annotation.domain;
import lombok.Data;
@Data
public class Teacher {
public Teacher(String name){
this.name = name;
}
private int id;
private String name;
}
- 创建Mapper
package net.havejoy.springboot_mybatis_annotation.mapper;
import net.havejoy.springboot_mybatis_annotation.domain.Class;
import org.apache.ibatis.annotations.*;
public interface ClassMapper {
@Select("select * from class where c_id = #{id}")
@Results({
@Result(property = "id", column = "c_id"),
@Result(property = "name", column = "c_name"),
@Result(property = "teacher", column = "teacher_id", one = @One(select = "net.havejoy.springboot_mybatis_annotation.mapper.TeacherMapper.findById")),
@Result(property = "students", column = "c_id", many = @Many(select = "net.havejoy.springboot_mybatis_annotation.mapper.StudentMapper.findByClassId"))
})
Class findById(int id);
}
package net.havejoy.springboot_mybatis_annotation.mapper;
import net.havejoy.springboot_mybatis_annotation.domain.Student;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import java.util.List;
import java.util.Map;
public interface StudentMapper {
@Select("select * from student where class_id = #{classId}")
@Results({
@Result(property = "id", column = "s_id"),
@Result(property = "name", column = "s_name"),
@Result(property = "classId", column = "class_id"),
@Result(property = "aClass",column = "class_id",one = @One(select = "net.havejoy.springboot_mybatis_annotation.mapper.ClassMapper.findById"))
})
List<Student> findByClassId(int classId);
@Select("select s_name,class_id from student where class_id = #{classId}")
List<Map<String,Object>> findCustomByClassId(int ClassId);
}
package net.havejoy.springboot_mybatis_annotation.mapper;
import net.havejoy.springboot_mybatis_annotation.domain.Teacher;
import org.apache.ibatis.annotations.*;
public interface TeacherMapper {
@Insert("insert into teacher (t_name) value(#{name})")
@Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "t_id")
int insert(Teacher teacher);
@Select("select * from teacher where t_id = #{id}")
@Results({
@Result(property = "id", column = "t_id"),
@Result(property = "name", column = "t_name")}
)
Teacher findById(int id);
}
说明
- @Options(useGeneratedKeys = true, keyProperty = “id”, keyColumn = “t_id”):
用于返回主键- @Result(property = “id”, column = “t_id”)
property为类的属性名称,column为数据库字段。- @Result(property = “aClass”,column = “class_id”,one = @One(select = “net.havejoy.springboot_mybatis_annotation.mapper.ClassMapper.findById”)
one = @One(select=“单对象查询方法,条件为column中的字段”)- @Result(property = “students”, column = “c_id”, many = @Many(select = “net.havejoy.springboot_mybatis_annotation.mapper.StudentMapper.findByClassId”)
many = @Many(select = “多对象查询方法,条件为column中的字段”)
- 创建Mapper
package net.havejoy.springboot_mybatis_annotation;
import net.havejoy.springboot_mybatis_annotation.domain.Class;
import net.havejoy.springboot_mybatis_annotation.domain.Student;
import net.havejoy.springboot_mybatis_annotation.domain.Teacher;
import net.havejoy.springboot_mybatis_annotation.mapper.ClassMapper;
import net.havejoy.springboot_mybatis_annotation.mapper.StudentMapper;
import net.havejoy.springboot_mybatis_annotation.mapper.TeacherMapper;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
@RunWith(SpringRunner.class)
@SpringBootTest
public class SpringbootMybatisAnnotationApplicationTests {
@Autowired
TeacherMapper teacherMapper;
@Autowired
ClassMapper classMapper;
@Autowired
StudentMapper studentMapper;
@Test
public void testTeacherMapper() {
Teacher teacher = new Teacher("daf");
Assert.assertEquals(1,teacherMapper.insert(teacher));
Assert.assertEquals(7,teacher.getId());
Assert.assertEquals("teacher1",teacherMapper.findById(1).getName());
}
@Test
public void testClassMapper(){
Class class1 = classMapper.findById(1);
Assert.assertEquals("teacher1",class1.getTeacher().getName());
Assert.assertEquals(2,class1.getStudents().size());
}
@Test
public void testStudent(){
List<Student> list1 = studentMapper.findByClassId(1);
Assert.assertEquals(2,list1.size());
List<Map<String,Object>> list = studentMapper.findCustomByClassId(1);
Assert.assertEquals(1,list.get(0).get("class_id"));
}
}