整合Mybatis
添加依赖
-
使用springboot2.4是因为2.4之后测试默认用的是junit5不用过多配置
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>org.example</groupId> <artifactId>SpringBoot-Mybatis</artifactId> <version>1.0-SNAPSHOT</version> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.4.13</version> </parent> <properties> <maven.compiler.source>8</maven.compiler.source> <maven.compiler.target>8</maven.compiler.target> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.0</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.45</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> </dependency> </dependencies> </project>
yaml配置
server:
port: 8080 #服务地址
spring:
datasource:
url: #数据库连接地址
username: #用户名
password: #密码
driver-class-name: com.mysql.jdbc.Driver
# 配置mybatis规则
mybatis:
# config-location: classpath:mybatis/mybatis-config.xml #全局配置文件位置,可以不用配置
mapper-locations: classpath:mybatis/mapper/*.xml #sql映射文件位置
configuration:
map-underscore-to-camel-case: true #开启下划线转驼峰命名规则
logging:
level:
com.mybatis.dao: debug #控制台打印sql
表设计 新建实体类
@Data
public class User {
private Long userId;
private String userName;
private String address;
private Integer age;
private String sex;
public User() {
}
public User(String userName, String address, Integer age, String sex) {
this.userName = userName;
this.address = address;
this.age = age;
this.sex = sex;
}
}
新建Mapper接口
-
@Param适用于传多个参数
-
不想使用@Param可以用对象/Map传多个值
@Component
public interface UserMapper {
List<User> getAll();
Integer addUser(User user);
Integer updateUser(User user);
Integer delUser(User user);
List<User> queryUser(User user);
List<User> getUserByNames(List<String> names);
List<User> getUser(@Param("name") String userName,@Param("age") Integer age);
}
编写Mapper.xml配置
* resultMap标签:可以做结果集的映射 * property属性:实体中的属性名 * column属性:数据库中的属性 * sql标签:可以编写一些代码片段,有利于代码的复用 * include标签:用于引用,常和sql标签一起使用,可以引用sql标签 * if标签:和Java中的if一样,用于动态sql中 * trim标签:用于动态sql中 * prefix属性:动态sql中的开始位置内容 * suffix属性:动态sql中的结束位置内容 * suffixOverrides属性:动态sql中的拼接符 * set标签:动态sql,用于update中给字段赋值,会在开始位置加入set * where标签:动态sql,用于where条件,会在开始位置加入where,同时会对and和or进行处理 * bind标签:用于加工属性,并生成新的属性 * name属性:新属性的的名称 * value属性:新属性的值,想获取对象中的数据必须使用_parameter,和Java类似 * select标签:查询专用 * insert标签:添加专用 * update标签:更新专用 * delete标签:删除专用 * id属性:用于绑定接口的方法名 * resultMap属性:用于绑定resultMap标签,并指定返回类型 * parameterType属性:接口的传入类型 * resultType属性:指定返回类型 * keyProperty属性:实体类绑定表中的主键,值为实体类中的属性(inset用于返回自增id) * useGeneratedKeys:开始生成key,也就是开启主键的生成(inset用于返回自增id)
<?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="com.mybatis.dao.UserMapper">
<resultMap id="userResult" type="com.mybatis.entity.User">
<result property="userId" column="user_id"/>
<result property="userName" column="user_name"/>
<result property="address" column="address"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
</resultMap>
<sql id="selectColumn">
`user_id`,`user_name`,`address`,`age`,`sex`
</sql>
<select id="getAll" resultMap="userResult">
select
<include refid="selectColumn"></include>
from user;
</select>
<insert id="addUser" parameterType="com.mybatis.entity.User" keyProperty="userId" useGeneratedKeys="true">
insert into user
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="userName != null">
`user_name`,
</if>
<if test="address != null">
`address`,
</if>
<if test="age != null">
`age`,
</if>
<if test="sex != null">
`sex`,
</if>
</trim>
values
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="userName != null">
#{userName},
</if>
<if test="address != null">
#{address},
</if>
<if test="age != null">
#{age},
</if>
<if test="sex != null">
#{sex},
</if>
</trim>
</insert>
<update id="updateUser" parameterType="com.mybatis.entity.User">
update user
<set>
<if test="userName != null">
`user_name` = #{userName},
</if>
<if test="address != null">
`address` = #{address},
</if>
<if test="age != null">
`age` = #{age},
</if>
<if test="sex != null">
`sex` = #{sex},
</if>
</set>
where user_id = #{userId}
</update>
<delete id="delUser" parameterType="com.mybatis.entity.User">
delete from user
<where>
<if test="userName != null">
user_name = #{userName}
</if>
<if test="address != null">
and address = #{address}
</if>
<if test="age != null">
and age = #{age}
</if>
<if test="sex != null">
and sex = #{sex}
</if>
</where>
</delete>
<select id="queryUser" parameterType="com.mybatis.entity.User" resultMap="userResult">
<bind name="name" value="'%' + _parameter.getUserName() + '%'"/>
select
<include refid="selectColumn"/>
from user where `user_name` like #{name}
</select>
<select id="getUserByNames" parameterType="list" resultMap="userResult">
select
<include refid="selectColumn"/>
from user where `user_name` in
<foreach collection="list" item="name" index="index" open="(" separator="," close=")">
#{name}
</foreach>
</select>
<select id="getUser" resultType="com.mybatis.entity.User">
select * from user where `user_name` = #{name} and `age` = #{age}
</select>
</mapper>
service层代码
@Service
public class UserServiceImpl implements UserService{
@Autowired
private UserMapper userMapper;
@Override
public List<User> getAll() {
return userMapper.getAll();
}
@Override
public Integer addUser(User user) {
return userMapper.addUser(user);
}
@Override
public Integer updateUser(User user) {
return userMapper.updateUser(user);
}
@Override
public Integer delUser(User user) {
return userMapper.delUser(user);
}
@Override
public List<User> queryUser(User user) {
return userMapper.queryUser(user);
}
@Override
public List<User> getUserByNames(List<String> names) {
return userMapper.getUserByNames(names);
}
@Override
public List<User> getUser(String userName, Integer age) {
return userMapper.getUser(userName,age);
}
}
启动类
@SpringBootApplication
@MapperScan("com.mybatis.dao")//指定扫描mapper接口的位置
public class RunApplication {
public static void main(String[] args) {
SpringApplication.run(RunApplication.class,args);
}
}
编写SpringBoot测试类 * 测试类使用的junit5 * 测试类必须和启动类目录同级
@SpringBootTest
public class UserTest {
@Autowired
UserService userService;
@Test
public void getAll(){
List<User> all = userService.getAll();
for (User user : all) {
System.out.println(user);
}
}
@Test
public void addUser(){
User user = new User("王五","上海市",22,"男");
Integer integer = userService.addUser(user);
System.out.println(integer);
System.out.println(user.getUserId());
}
@Test
public void del(){
User user = new User();
user.setAge(18);
Integer integer = userService.delUser(user);
System.out.println(integer);
}
@Test
public void update(){
User user = new User();
user.setUserName("李四");
user.setAddress("杭州");
user.setSex("女");
user.setAge(20);
user.setUserId(11l);
Integer integer = userService.updateUser(user);
System.out.println(integer);
}
@Test
public void queryUser(){
User user = new User();
user.setUserName("四");
List<User> users = userService.queryUser(user);
for (User user1 : users) {
System.out.println(user1);
}
}
@Test
public void getUserByNames(){
List<String> list = new ArrayList<>();
list.add("张三");
list.add("李四");
List<User> userByNames = userService.getUserByNames(list);
for (User userByName : userByNames) {
System.out.println(userByName);
}
}
@Test
public void getUser(){
List<User> users = userService.getUser("张三", 18);
for (User user : users) {
System.out.println(user);
}
}
}
启动测试类