Mybatis入门
数据库连接
spring.application.name=springboot-mybatis-quickart
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mybatis
spring.datasource.username=root
spring.datasource.password=888888//自己数据库的密码
lombok
import lombok.*;
Mybatis基础操作
准备
- 连接数据库(会生成自动补全)
删除
SQL语句
delete from emp where id = #{id}
mybatis操作
- 声明一个接口方法
@Mapper
public interface EmpMapper {
//根据ID删除数据
@Delete("delete from emp where id = #{id}")//动态获取ID
public void delete(Integer id);//传参为ID
}//使用注解,通过“value"语句执行SQL语句
- 进行单元测试
@SpringBootTest
class SpringbootMybatisCrudApplicationTests {
@Autowired//自动注入接口对象
private EmpMapper empMapper;//声明一个接口对象
@Test
public void testDelete(){
empMapper.delete(17);
}
}
- 设置返回值(但是没啥用)
public interface EmpMapper {
//根据ID删除数据
@Delete("delete from emp where id = #{id}")//动态获取ID
public int delete(Integer id);
}
@Test
public void testDelete(){
int delete = empMapper.delete(16);
System.out.println(delete);
}
错误记录
- 运行测试报错找不到包
解决方法
- pom出错,要和自己版本对应,注意依赖,父工程,JDK版本
- 忘记在依赖里添加Junit!!(好多次了!能不能吸烟刻肺)
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
//<scope>test</scope>
</dependency>
- 记得时刻刷新Maven
- 第二次运行又说缺少包,去掉了两个/test标签解决了
- 存一份正确的pom
<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.2.5</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.itheima</groupId>
<artifactId>springboot-mybatis-crud</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<java.version>17</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</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>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter-test</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
连接数据库注意事项
- 密码密码密码!
配置日志
-
打开application.properties
-
配置mybatis的日志,指定输出到控制台
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
预编译SQL
- 编写接口方法时采用预编译SQL
性能更高
更安全(防止SQL注入)
登录
- 实际就是查询用户名和密码是否在数据库中存在
select count(*) from emp where username = '' and password = '';//返回值大于0说明存在该用户,即密码正确
-
SQL注入
将参数直接拼接在SQL语句后
select count(*) from emp where username = '' and password = '' or '1' = '1';//where后面的条件永远为1
- 预编译更安全
- #{…}更安全
新增
SQL语句
#插入数据
insert into emp (username, name, gender, image, job, entrydate, dept_id, create_time, update_time)
values ('Bob', '鲍勃', '男', '1.jpg',1,'2005-01-01',1,now(),now());
mybatis操作
- 声明一个接口方法
@Mapper
public interface EmpMapper {
//新增ID员工
@Insert(" insert into emp (username, name, gender, image, job, entrydate, dept_id, create_time, update_time)"+
"values (#{username}, #{name}, #{gender}, #{image},#{job},#{entrydate},#{deptId},#{createTime},#{updateTime})")//属性名采用驼峰命名
public void insert(Emp emp);//使用实体类将多个参数封装封装
}
//谁懂少写了一个后括号报了86个错误
- 进行单元测试
@Test
public void testInsert(){
//构造员工对象
Emp emp = new Emp();
emp.setUsername("zhangsan");
emp.setName("李三");
emp.setGender((short)1);
emp.setImage("16.jpg");
emp.setJob((short)1);
emp.setEntrydate(LocalDate.of(2020,12,5));
emp.setDeptId(1);
emp.setCreateTime(LocalDateTime.now());
emp.setUpdateTime(LocalDateTime.now());
//执行新增员工信息操作
empMapper.insert(emp);
}
- 日志内容
错误记录
- 只能测试一次,已存在该数据时会大量报错
- SQL属性过多时,注意括号
主键返回
@Options(keyProperty = "id",useGeneratedKeys = true)//会自动将生成的主键值,赋值给emp对象的id属性
@Insert(" insert into emp (username, name, gender, image, job, entrydate, dept_id, create_time, update_time)"+
"values (#{username}, #{name}, #{gender}, #{image},#{job},#{entrydate},#{deptId},#{createTime},#{updateTime})")
public void insert(Emp emp);
}
empMapper.insert(emp);
System.out.println(emp.getId());
更新(修改)
SQL语句
update emp set username = '',name = '',gender = '男', image = '', job = '', entrydate = '',dept_id = '',update_time ='' where id = 1;
mybatis操作
- 声明一个接口方法
//更新员工
@Update("update emp set username = #{username}, name = #{name}, gender = #{gender}, image = #{image}, job = #{job}, entrydate = #{entrydate}, dept_id = #{deptId}, create_time = #{createTime}, update_time = #{updateTime} where id = #{id}")
public void update(Emp emp);
- 进行单元测试
@Test
public void testUpdate() {
Emp emp = new Emp();
emp.setId(22);
emp.setUsername("xxx");
emp.setName("西彭寻");
emp.setGender((short) 1);
emp.setImage("13.jpg");
emp.setJob((short) 1);
emp.setEntrydate(LocalDate.of(2020, 12, 5));
emp.setDeptId(1);
emp.setCreateTime(LocalDateTime.now());
emp.setUpdateTime(LocalDateTime.now());
//执行更更新员工操作
empMapper.update(emp);
}
查询
SQL语句
select * from emp;
select * from emp where id = 1;
select * from emp where id = 1 and name = '鲍勃';
select * from emp where id = 1 or name = '鲍勃';
mybatis操作
- 方法声明
//查询员工
@Select("select * from emp where id = #{id}")
public Emp getEmpById(Integer id);
}//查询有返回值,封装在员工对象中
- 单元测试
@Test
public void testGetEmpById() {
Emp emp = empMapper.getEmpById(22);
System.out.println(emp);
}
- 后三列为null
数据封装
解决方案
- 给字段起别名,让别名与实体类属性一致
@Select("select id,username,password,name,gender,image, job, entrydate, deptId, createTime, updateTime from emp where id = #{id}")
public Emp getEmpById(Integer id);
- 通过@Results,@Result注解手动映射封装
@Results({
@Result(column = "dept_id",property = "deptId"),
@Result(column = "create_time",property = "createTime"),
@Result(column = "update_time",property = "updateTime"),
})
@Select("select * from emp where id = #{id}")
public Emp getEmpById(Integer id);
- 开启mybatis驼峰命名自动映射开关
- 打开application.properties配置+
- 严格遵守a_column映射到aColumn
#配置驼峰命名自动映射开关
mybatis.configuration.map-underscore-to-camel-case=true
条件查询
SQL语句
select * from emp where name like '%张%' and gender = 1 and entrydate between '2010-01-01' and '2020-01-01'order by update_time desc ;//姓名模糊查询
mybatis操作
- 声明方法
@Select("select * from emp where name like '%${name}%' and gender = #{gender} and " +//#{}相当于? ,?不能在 ' '内,换为¥,起到拼接的作用,但是存在SQL注入问题
"entrydate between #{begin} and #{end}order by update_time desc ;")
public List<Emp> list (String name, Short gender, LocalDate begin, LocalDate end);//可能是多个对象,封装在list里
- 单元测试
@Test
public void testList(){
List<Emp> emplist = empMapper.list("张", (short) 1, LocalDate.of(2010, 1, 1), LocalDate.of(2020, 1, 1));
System.out.println(emplist);
}
contact 字符串拼接函数
select * from emp where name like concat('%','张','%') and gender = 1 and entrydate between '2010-01-01' and '2020-01-01'order by update_time desc ;
- 改造接口方法,生成预编译的SQL
@Select("select * from emp where name like concat('%',#{name},'%') and gender = #{gender} and " +
"entrydate between #{begin} and #{end}order by update_time desc ;")
public List<Emp> list (String name, Short gender, LocalDate begin, LocalDate end);//可能是多个对象,封装在list里
XML映射文件
- 遵守三个规范
定义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="com.itheima.mapper.EmpMapper">
<!--resultType:单条记录所封装的类型-->
<select id="list" resultType="com.itheima.pojo.Emp">
select * from emp
where name like concat('%', #{name}, '%')
and gender = #{gender}
and entrydate between #{begin} and #{end}order by update_time desc
</select>
</mapper>
- namespace=
- resultType=
- 单元测试
- 插件
- 小鸟之间可以自动跳转
-
总结
简单功能用注解,复杂功能用xml文件
动态SQL
- 随着用户的输入或外部条件的变化而变化的SQL语句
<if>
<select id="list" resultType="com.itheima.pojo.Emp">
select *
from emp
where
<if test="name != null">
name like concat('%', #{name}, '%')
</if>
<if test="gender != null">
and gender = #{gender}
</if>
<if test="begin != null and end != null">
and entrydate between #{begin} and #{end}
</if>
order by update_time desc
</select>
- 单元测试
@Test
public void testList(){
List<Emp> emplist = empMapper.list("张", null, null,null);
System.out.println(emplist);
}