目录
需求:
-
删除
-
根据主键ID批量删除
-
根据主键ID删除
-
-
添加
-
更新
-
查询
-
条件查询
-
根据主键ID查询
-
数据准备:
#创建用户表
CREATE TABLE `user` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(100) DEFAULT NULL COMMENT '姓名',
`age` tinyint unsigned DEFAULT NULL COMMENT '年龄',
`gender` tinyint unsigned DEFAULT NULL COMMENT '性别, 1:男, 2:女',
`phone` varchar(11) DEFAULT NULL COMMENT '手机号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';
#添加数据
insert into user(id, name, age, gender, phone, create_time, update_time)
values(null,'张三',15,1,'133xxxxxxxx'),
(null,'张三1',21,1,'131xxxxxxxx'),
(null,'张三2',13,1,'133xxxxxxxx'),
(null,'张三3',15,2,'144xxxxxxxx'),
(null,'张三4',32,1,'151xxxxxxxx'),
(null,'张三5',26,2,'133xxxxxxxx'),
(null,'李四',36,1,'131xxxxxxxx'),
(null,'李四1',13,1,'147xxxxxxxx'),
(null,'李四2',24,2,'175xxxxxxxx'),
(null,'李四3',16,1,'185xxxxxxxx'),
(null,'李四4',19,2,'169xxxxxxxx'),
(null,'李四5',17,1,'131xxxxxxxx');
结果如图所示:
创建SpringBoot项目
项目结构:
创建实体类:
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
private Integer id;
private String name;
private short age;
private short gender;
private String phone;
}
Mapper接口:
/*@Mapper注解:表示当前接口为mybatis中的Mapper接口
程序运行时会自动创建接口的实现类对象(代理对象),并交给Spring的IOC容器管理
*/
@Mapper
public interface UserMapper {
}
删除操作:
根据主键删除数据:
SQL语句实现:
#删除id=10的数据
delete from user where id = 10;
UserMapper接口:
@Mapper
public interface EmpMapper {
//@Delete("delete from user where id = 10")
//public void delete();
//以上delete操作的SQL语句中的id值写成固定的10,就表示只能删除id=10的用户数据
//SQL语句中的id值不能写成固定数值,需要变为动态的数值
//解决方案:在delete方法中添加一个参数(用户id),将方法中的参数,传给SQL语句
/**
* 根据id删除数据
* @param id 用户id
*/
@Delete("delete from user where id = #{id}")//使用#{key}方式获取方法中的参数值
public void delete(Integer id);
}//#{}里的属性名可随便写,但是便于操作,建议保持名字一致
测试类:
@SpringBootTest
class MybatisApplicationTests {
@Autowired //从Spring的IOC容器中,获取类型是EmpMapper的对象并注入
private UserMapper userMapper;
@Test
public void testDel(){
//调用删除方法
userMapper.delete(10);
}
}
到这里,删除的操作就完成了,回到数据就会发现id=10的数据已经被删除了
添加操作:
SQL语句实现:
insert into user(id, name, age, gender, phone, create_time, update_time)
values(null,'张三',15,1,'133xxxxxxxx');
UserMapper接口:
@Mapper
public interface UserMapper {
@Insert("insert into user(id, name, age, gender, phone) "+
"values(#{id},#{name},#{age},#{gender},#{phone}) ")
public void insert(User user);
}
测试类:
@SpringBootTest
class MybatisApplicationTests {
@Autowired
private UserMapper userMapper;
@Test
public void testInsert(){
//创建员工对象
User user = new User();
user.setName("汤姆");
user.setAge((short) 25);
user.setGender((short)1);
user.setPhone("19881302340");
//调用添加方法
userMapper.insert(user);
}
}
终端日志:
数据添加成功:
更新操作:
SQL语句实现:
update user set name = 'TOM', gender = 1 , age=21,phone=13734255657 where id = 18;
UserMapper接口:
@Mapper
public interface UserMapper {
//更新
@Update("update user set name=#{name}," +
"age=#{age},gender=#{gender},phone=#{phone} where id=#{id}")
public void update(User user);
}
}
测试类:
@SpringBootTest
class MybatisApplicationTests {
//更新
@Autowired
private UserMapper userMapper;
@Test
public void testUpdate(){
User user = new User();
user.setId(18);
user.setName("Tom");
user.setAge((short) 34);
user.setGender((short) 1);
user.setPhone("13734255657");
userMapper.update(user);
}
}
终端日志:
更新完成:
查询操作:
根据ID查询
SQL语句实现:
select * from user where id = 9;
UserMapper类:
@Mapper
public interface UserMapper {
//根据ID查询
@Select("select * from user where id = #{id}")
public User getByid(Integer id);
}
测试类:
@SpringBootTest
class MybatisApplicationTests {
// 查询(根据ID查询)
@Autowired
private UserMapper userMapper;
@Test
public void testSelect() {
User user = userMapper.getByid(9);
System.out.println(user);
}
终端日志:
条件查询:
SQL语句实现:
select *from user where name like '张' and gender=1 and age<=30;
UserMapper类:
@Mapper
public interface UserMapper {
//条件查询
@Select("select *from user where name like '%${name}%' and gender=#{gender} and age<=#{age}")
public List<User> list(String name, Short gender, Integer age);
//concat拼接方法
// @Select("select *from user where name like concat('%',#{name},'%') and gender=#{gender} and age<=#{age}")
// public List<User> list(String name,Short gender,Integer age);
}
测试类:
@SpringBootTest
class MybatisApplicationTests {
@Autowired
private UserMapper userMapper;
@Test
public void testList(){
List<User> userList = userMapper.list("张", (short) 1,30);
System.out.println(userList);
}
}