目录
什么是MyBatis?
MyBatis入门
1)创建工程
2)数据准备
3)配置数据库连接字符串
4)写持久层代码
5)生成测试类
MyBatis打印日志
传递参数
MyBatis的增、删、改
增(Insert)
删(Delete)
改(Update)
查(Select)
使用XML方式
增删改查
增(Insert)
删(Delete)
改(Update)
查(Select)
补充:MySQL开发规范
什么是MyBatis?
MyBatis是一款持久层框架,用于简化JDBC开发
持久层:持久化操作的层,通常指数据访问层(DAO),是用来操作数据库的
MyBatis入门
- 准备工作(创建springboot工程、数据库表准备、实体类)
- 引入MyBatis的相关依赖,配置MyBatis(数据库连接信息)
- 编写SQL语句(注解/XML)
- 测试
1)创建工程
创建springboot工程,并引入MyBatis、MySQL依赖
<!--Mybatis 依赖包-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.3.1</version>
</dependency>
<!--mysql驱动包-->
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
</dependency>
MyBatis是一个持久层框架,具体的数据库存储和数据操作还是在MySQL中操作的
2)数据准备
创建用户表
DROP DATABASE IF EXISTS mybatis_test; CREATE DATABASE mybatis_test;
DEFAULT CHARACTER SET utf8mb4;
USE mybatis_test;
DROP TABLE IF EXISTS userinfo;
CREATE TABLE `userinfo` ( `id` INT ( 11 ) NOT NULL AUTO_INCREMENT, `username` VARCHAR ( 127 ) NOT NULL, `password` VARCHAR ( 127 ) NOT NULL, `age` TINYINT ( 4 ) NOT NULL, `gender` TINYINT ( 4 ) DEFAULT '0' COMMENT '1-男 2-⼥ 0-默认', `phone` VARCHAR ( 15 ) DEFAULT NULL, `delete_flag` TINYINT ( 4 ) DEFAULT 0 COMMENT '0-正常, 1-删除', `create_time` DATETIME DEFAULT now(), `update_time` DATETIME DEFAULT now(), PRIMARY KEY ( `id` ) ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone ) VALUES ( 'admin', 'admin', 18, 1, '18612340001' ); INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone ) VALUES ( 'zhangsan', 'zhangsan', 18, 1, '18612340002' ); INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone ) VALUES ( 'lisi', 'lisi', 18, 1, '18612340003' ); INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone ) VALUES ( 'wangwu', 'wangwu', 18, 1, '18612340004' );
创建对应实体类UserInfo
(注意实体类中的属性名与表中的字段名一一对应)
import lombok.Data;
import java.util.Date;
@Data
public class UserInfo {
private Integer id;
private String username;
private String password;
private Integer age;
private Integer gender;
private String phone;
private Integer deleteFlag;
private Date createTime;
private Date updateTime;
}
3)配置数据库连接字符串
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/mybatis_test?characterEncoding=utf8&useSSL=false
username: root
password: root #改为你的数据库密码
driver-class-name: com.mysql.cj.jdbc.Driver
4)写持久层代码
创建持久层接口UserInfoMapper
@Mapper
public interface UserInfoMapper {
@Select("select username,`password`,age,gender,phone from userinfo")
public List<UserInfo> queryAllUser();
}
Mybatis的持久层接⼝规范⼀般都叫XxxMapper
@Mapper注解:表⽰是MyBatis中的Mapper接⼝
程序运⾏时,框架会⾃动⽣成接⼝的实现类对象(代理对象),并给交Spring的IOC容器管理
5)生成测试类
在需要测试的Mapper接口中,右键->Generate->Test
书写测试代码
@SpringBootTest
class UserInfoMapperTest {
@Autowired
private UserInfoMapper userInfoMapper;
@Test
void queryAllUser() {
List<UserInfo> userInfoList=userInfoMapper.queryAllUser();
System.out.println(userInfoList);
}
}
注解@SpringBootTest,该测试类在运行时,就会自动加载Spring的运行环境
MyBatis打印日志
通过日志看到sql语句的执行、执行传递的参数以及执行结果
mybatis:
configuration: # 配置打印 MyBatis⽇志
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
①:查询语句
②:传递参数及类型
③:SQL执行结果
传递参数
@Mapper
public interface UserInfoMapper {
@Select("select username,`password`,age,gender,phone from userinfo where id=#{id}")
public List<UserInfo> queryAllUser(Integer id);
}
@SpringBootTest
class UserInfoMapperTest {
@Autowired
private UserInfoMapper userInfoMapper;
@Test
void queryAllUser() {
List<UserInfo> userInfoList=userInfoMapper.queryAllUser(4);
System.out.println(userInfoList);
}
}
MyBatis的增、删、改
增(Insert)
(Mapper中)
@Mapper
public interface UserInfoMapper {
@Options(useGeneratedKeys = true,keyProperty = "id")
@Insert("insert into userinfo(username,`password`,age,gender,phone) values (#{username},#{password},#{age},#{gender},#{phone})")
public Integer insert(UserInfo userInfo);
}
(MapperTest中)
@SpringBootTest
class UserInfoMapperTest {
@Test
void insert() {
UserInfo userInfo=new UserInfo();
userInfo.setAge(10);
userInfo.setGender(2);
userInfo.setUsername("赵六");
userInfo.setPhone("123456");
userInfo.setPassword("123");
userInfoMapper.insert(userInfo);
}
}
注意:如果使用了@Param属性来重命名,#{...}需要使用“参数.属性”来获取
@Insert("insert into userinfo(username,`password`,age,gender,phone) values (#{userinfo.username},#{userinfo.password},#{userinfo.age},#{userinfo.gender},#{userinfo.phone})")
public Integer insert(@Param("userinfo") UserInfo userInfo);
Insert语句返回值是收影响的行数
有些情况下我们需要获得新插入数据的id,此时使用@Options注解
@Options(useGeneratedKeys = true,keyProperty = "id")
@Insert("insert into userinfo(username,`password`,age,gender,phone) values (#{username},#{password},#{age},#{gender},#{phone})")
public Integer insert(UserInfo userInfo);
@Test
void insert() {
UserInfo userInfo=new UserInfo()
userInfo.setAge(10);
userInfo.setGender(2);
userInfo.setUsername("赵六");
userInfo.setPhone("123456");
userInfo.setPassword("123");
Integer count = userInfoMapper.insert(userInfo);
//设置useGeneratedKeys=true后,方法返回值仍然是受影响行数
System.out.println("添加数据条数:" +count +",数据id:"+userInfo.getId());
}
useGeneratedKeys:令MyBatis使⽤JDBC的getGeneratedKeys⽅法来取出由数据库内部⽣成的主键,默认值:false.
keyProperty:这指定了实体类中用于存储数据库生成的主键的属性的名称,当 MyBatis 从数据库获取到新生成的主键后,它会将这个值设置到实体类的
id
属性中。
删(Delete)
@Delete("delete from userinfo where id=#{id}")
public void delete(Integer id);
@Test
void delete() {
userInfoMapper.delete(4);
}
改(Update)
@Update("update userinfo set username=#{username} where id=#{id}")
public void update(String username,Integer id);
@Test
void update() {
userInfoMapper.update("王五",3);
}
查(Select)
上述查询中,我们发现,只有在java对象属性和数据库字段名一样(忽略大小写)时,才会进行赋值
(java对象中deleteFlag、createTime、updateTime属性与数据库中字段delete_flag、create_time、update_time对应不上)
解决办法
①起别名
@Select("select id, username, `password`, age, gender, phone, delete_flag as
deleteFlag,create_time as createTime, update_time as updateTime from userinfo")
public List<UserInfo> queryAllUser();
②结果映射
@Select("select id, username, `password`, age, gender, phone, delete_flag,
create_time, update_time from userinfo")
@Results({
@Result(column = "delete_flag",property = "deleteFlag"),
@Result(column = "create_time",property = "createTime"),
@Result(column = "update_time",property = "updateTime")
})
List<UserInfo> queryAllUser();
可以给Results定义一个名称,使其他sql也能复用这个映射关系
@Select("select id, username, `password`, age, gender, phone, delete_flag,
create_time, update_time from userinfo")
@Results(id = "resultMap",value = {
@Result(column = "delete_flag",property = "deleteFlag"),
@Result(column = "create_time",property = "createTime"),
@Result(column = "update_time",property = "updateTime")
})
List<UserInfo> queryAllUser();
@Select("select id, username, `password`, age, gender, phone, delete_flag,
create_time, update_time " +
"from userinfo where id= #{userid} ")
@ResultMap(value = "resultMap")
UserInfo queryById(@Param("userid") Integer id);
③开启驼峰命名(推荐)
数据库中字段通常使用蛇形命名,java属性通常使用驼峰命名,可以通过配置使得这两种命名方式自动映射(abc_xyz => abcXyz)
mybatis:
configuration:
map-underscore-to-camel-case: true #配置驼峰⾃动转换
MyBatis有使用注解和XML两种方式
下面,我们介绍
使用XML方式
①配置数据库连接字符串
#数据库连接配置
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/mybatis_test?characterEncoding=utf8&useSSL=false
username: root
password: root //改成你自己的密码
driver-class-name: com.mysql.cj.jdbc.Driver
#配置 mybatis xml 的⽂件路径,在 resources/mapper 创建所有表的 xml ⽂件
mybatis:
mapper-locations: classpath:mapper/**Mapper.xml
②写持久层代码
1)方法定义Interface
@Mapper
public interface UserInfoXMLMapper {
List<UserInfo> queryAllUser();
}
2)方法实现:UserInfoXMLMapper.xml(路径参考yml中的配置 mapper-locations: classpath:mapper/**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="com.example.sql.mapper.UserInfoMapper">
<select id="queryAllUser" resultType="com.example.sql.UserInfo">
select username,`password`,age,gender,phone from userinfo
</select>
</mapper>
- <mapper>标签:需要指定namespace 属性,表⽰命名空间,值为mapper接⼝的全限定 名,包括全包名.类名。
- <select>查询标签:是⽤来执⾏数据库的查询操作的:
◦id :是和Interface (接⼝)中定义的⽅法名称⼀样的,表⽰对接⼝的具体实现⽅法。
◦ resultType :是返回的数据类型,也就是开头我们定义的实体类
单元测试
@SpringBootTest
public class UserInfoXMLMapperTest {
@Autowired
private UserInfoXMLMapper userInfoXMLMapper;
@Test
void queryAllUser(){
List<UserInfo> userInfoList=userInfoXMLMapper.queryAllUser();
System.out.println(userInfoList);
}
}
增删改查
增(Insert)
@Mapper
public interface UserInfoXMLMapper {
Integer insertUser(UserInfo userInfo);
}
<?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.example.sql.mapper.UserInfoXMLMapper">
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
insert into userinfo (username,`password`,age,gender,phone) values(#{username},#{password},#{age},#{gender},#{phone})
</insert>
</mapper>
@SpringBootTest
public class UserInfoXMLMapperTest {
@Autowired
private UserInfoXMLMapper userInfoXMLMapper;
@Test
void insertUser(){
UserInfo userInfo=new UserInfo();
userInfo.setAge(10);
userInfo.setGender(2);
userInfo.setUsername("赵七");
userInfo.setPhone("123457");
userInfo.setPassword("123");
Integer count=userInfoXMLMapper.insertUser(userInfo);
System.out.println(count);
}
}
使用@Param设置参数名称与注解类似
Integer insertUser(@Param("userinfo") UserInfo userInfo);
<insert id="insertUser">
insert into userinfo (username, `password`, age, gender, phone) values
(#{userinfo.username},#{userinfo.password},#{userinfo.age},#
{userinfo.gender},#{userinfo.phone})
</insert>
返回自增id
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
insert into userinfo (username, `password`, age, gender, phone) values
(#{userinfo.username},#{userinfo.password},#{userinfo.age},#
{userinfo.gender},#{userinfo.phone})
</insert>
删(Delete)
UserInfoXMLMapper接⼝
Integer deleteUser(Integer id);
UserInfoXMLMapper.xml实现:
<delete id="deleteUser">
delete from userinfo where id = #{id}
</delete>
改(Update)
UserInfoXMLMapper接⼝:
Integer updateUser(UserInfo userInfo);
UserInfoXMLMapper.xml实现:
update id="updateUser"> update userinfo set username=#{username} where id=#{id} update>
查(Select)
与注解方式类似,查找操作也涉及到java对象和数据库字段命名问题
解决办法
①起别名
②结果映射
③开启驼峰命名
(①③与注解一样),下面介绍结果映射
<resultMap id="BaseMap" type="com.example.demo.model.UserInfo">
<id column="id" property="id"></id>
<result column="delete_flag" property="deleteFlag"></result>
<result column="create_time" property="createTime"></result>
<result column="update_time" property="updateTime"></result>
</resultMap>
<select id="queryAllUser" resultMap="BaseMap">
select id, username,`password`, age, gender, phone, delete_flag,
create_time, update_time from userinfo
</select>
开发中,建议简单sql使用注解方式,复杂sql(如动态sql)使用xml方式
注解方式和xml方式可以一起使用
补充:MySQL开发规范
①表名、字段名使⽤⼩写字⺟或数字,单词之间以下划线分割.尽量避免出现数字开头或者两个下划线,中间只出现数字.数据库字段名的修改代价很⼤,所以字段名称需要慎重考虑。
MySQL在Windows下不区分⼤⼩写,但在Linux下默认是区分⼤⼩写.
因此,数据库名、表名、字段名都不允许出现任何⼤写字⺟,避免节外⽣枝
②表必备三字段:id,create_time,update_time,id必为主键,类型为bigintunsigned,create_time,update_time的类型均为datetime类型,create_time表⽰创建时间, update_time表⽰更新时间
③在表查询中,避免使⽤*作为查询的字段列表,标明需要哪些字段
1. 增加查询分析器解析成本
2. 增减字段容易与resultMap配置不⼀致
3. ⽆⽤字段增加⽹络消耗,尤其是text类型的字段