使用mybatis查询所有用户数据
数据准备
create table user(
id int unsigned primary key auto_increment comment 'ID',
name varchar(100) comment '姓名',
age tinyint unsigned comment '年龄',
gender tinyint unsigned comment '性别, 1:男, 2:女',
phone varchar(11) comment '手机号'
) comment '用户表';
insert into user(id, name, age, gender, phone) VALUES (null,'白眉鹰王',55,'1','18800000000');
insert into user(id, name, age, gender, phone) VALUES (null,'金毛狮王',45,'1','18800000001');
insert into user(id, name, age, gender, phone) VALUES (null,'青翼蝠王',38,'1','18800000002');
insert into user(id, name, age, gender, phone) VALUES (null,'紫衫龙王',42,'2','18800000003');
insert into user(id, name, age, gender, phone) VALUES (null,'光明左使',37,'1','18800000004');
insert into user(id, name, age, gender, phone) VALUES (null,'光明右使',48,'1','18800000005');
一:创建springboot工程
二:创建一个数据库并配置驱动
三:创建一个User封装类
package com.ithima.pojo;
public class User {
private Integer id;
private String name;
private short age;
private short gender;
private String phone;
public User() {
}
public User(Integer id, String name, short age, short gender, String phone) {
this.id = id;
this.name = name;
this.age = age;
this.gender = gender;
this.phone = phone;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public short getAge() {
return age;
}
public void setAge(short age) {
this.age = age;
}
public short getGender() {
return gender;
}
public void setGender(short gender) {
this.gender = gender;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
}
四:创建一个mapper包并创建一个接口来接收数据
package com.ithima.mapper;
import com.ithima.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;
@Mapper //在运行时,会自动生成改接口的实现类对象(代理对象),并且将该对象交给IOC容器管理
public interface UserMapper {
//查询全部用户信息
@Select("select * from user")
public List<User> list();
}
六:在测试类里面遍历出User数据
package com.ithima;
import com.ithima.mapper.UserMapper;
import com.ithima.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
@SpringBootTest
class SpringbootMybatisQuickstartApplicationTests {
//自动创建对象
@Autowired
private UserMapper userMapper;
@Test
public void testListUser(){
List<User> userList=userMapper.list();
userList.stream().forEach(user -> {
System.out.println(user);
});
}
}
数据库连接池
如何切换数据库连接池?
配置pom文件,添加依赖
lombok
需要引入lombok依赖
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
上面的封装User类就可以简化成下面这种
package com.ithima.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data//包含getter,setter,toString,hasCode等方法,但不包含有无参数构造器
@NoArgsConstructor//无参构造器
@AllArgsConstructor//有参构造器
public class User {
private Integer id;
private String name;
private short age;
private short gender;
private String phone;
}
Mybatis基本操作
配置日志
采用预编译SQL的优势
删除
package com.itheima.mapper;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Component;
@Mapper
public interface EmpMapper {
//根据ID删除数据
@Delete("delete from emp where id=#{id}")
public void delete(Integer 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);
@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);
更新
@Update("update emp set username=#{username},name=#{name},gender=#{gender},image=#{image},job=#{job}, entrydate=#{entrydate},dept_id=#{deptId},update_time=#{updateTime} where id=#{id}")
public void update(Emp emp);
查询
//查询数据
@Select("select * from emp where id=#{id}")
public Emp GetById(Integer id);
数据封装问题
条件查询
//条件查询
@Select("select * from emp where name like concat('%',#{name},'%')and entrydate between #{begin} and #{end} order by update_time desc")
public List<Emp> list(String name, short gender, LocalDate begin,LocalDate end);
xml配置文件
<mapper namespace="com.itheima.mapper.EmpMapper">
<!-- resultType:单挑记录所封装的类型-->
<select id="list" resultType="com.itheima.pojo.Emp" >
select * from emp where name like concat('%',#{name},'%')and entrydate between #{begin} and #{end} order by update_time desc
</select>
</mapper>
mybatis动态SQL
if标签
<?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>
<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}
order by update_time desc
</if>
</where>
</select>
</mapper>
<foreach>标签
<sql><include>标签
这个标签的主要目的是提高代码复用性
<sql id="commonSelect">
select id,username,password,name,gender,image,job,entrydate,dept_id,create_time
update_time from emp
</sql>
<!-- resultType:单挑记录所封装的类型-->
<select id="list" resultType="com.itheima.pojo.Emp" >
-- 目的:提高代码复用性
<include refid="commonSelect"/>
<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}
order by update_time desc
</if>
</where>
</select>