1、主要作用:
快速实现对关系型数据库中的数据进行访问,实现快速的增删改查。
2、如何创建Mybatis工程
基于Spring框架是目前业内使用的标准之一,Mybatis都会整合Spring一起使用。
1>、创建工程项目
2>、添加项目依赖项
<dependencies>
<!-- Mybatis 整合Spring框架依赖 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>2.0.6</version>
</dependency>
<!-- Mybatis 框架依赖 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.6</version>
</dependency>
<!-- Spring 框架依赖 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.3.14</version>
</dependency>
<!-- Spring JDBC依赖 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.3.14</version>
</dependency>
<!-- mysql连接 依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
<!-- 数据库连接池 依赖 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.8.0</version>
</dependency>
</dependencies>
<!-- JUnit 测试 依赖 -->
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-engine</artifactId>
<version>5.7.2</version>
<scope>test</scope>
</dependency>
3>、编写测试类,测试项目创建是否成功。
package com.stu.mybatis;
import org.junit.jupiter.api.Test;
public class MybatisTests {
@Test
public void test(){
System.out.println("test");
}
}
4>、运行结果:测试成功(绿色对号即为成功)
3、配置Mybatis开发环境
1>、创建数据库(这里我是用的是Mysql数据库)
打开数据库操作界面
mysql -u username -p password
创建test1数据库
create database test1;
2>、在IDEA中配置数据库视图
3>、配置数据库连接
4>、创建数据表
create table admin
(
id bigint unsigned auto_increment,
name varchar(50) default null unique comment '用户名',
password varchar(50) default null comment '密码',
primary key (id)
) comment '管理员' character set utf8mb4;
创建成功:
5>、创建配置文件,用于配置连接数据库的参数:datasource.properties
datasource.url = jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
datasource.driver = com.mysql.cj.jdbc.Driver
datasource.username = root
datasource.password = root
6>、创建配置文件,读取配置文件:SpringConfig.java
配置读取注解:
package com.stu.mybatis;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
@Configuration
@PropertySource("classpath:datasource.properties")
public class SpringConfig {
}
7>、测试文件是否读取成功,添加测试代码
package com.stu.mybatis;
import org.junit.jupiter.api.Test;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.core.env.ConfigurableEnvironment;
public class MybatisTests {
@Test
public void test(){
System.out.println("MybatisTests.test");
AnnotationConfigApplicationContext ac = new AnnotationConfigApplicationContext(SpringConfig.class);
ConfigurableEnvironment env = ac.getEnvironment();
System.out.println("env.url:"+ env.getProperty("datasource.url"));
System.out.println("env.driver:"+ env.getProperty("datasource.driver"));
System.out.println("env.username:"+ env.getProperty("datasource.username"));
System.out.println("env.root:"+ env.getProperty("datasource.password"));
ac.close();
}
}
测试成功:
8>、在SpringConfig中配置一个DataSource对象,获取数据库来连接:
@Bean
public DataSource dataSource(Environment env){
BasicDataSource dataSource = new BasicDataSource();
dataSource.setUrl(env.getProperty("datasource.url"));
dataSource.setDriverClassName(env.getProperty("datasource.driver"));
dataSource.setUsername(env.getProperty("datasource.username"));
dataSource.setPassword(env.getProperty("datasource.password"));
return dataSource;
}
测试:
@Test
public void testGetConnection() throws SQLException {
AnnotationConfigApplicationContext ac = new AnnotationConfigApplicationContext(SpringConfig.class);
DataSource dataSource = (DataSource) ac.getBean("dataSource");
Connection connection = dataSource.getConnection();
System.out.println("MybatisTests.testGetConnection");
System.out.println(connection);
ac.close();
}
4、Mybatis的基本使用(插入数据)
在使用Mybatis框架实现数据访问时,需要:编写数据访问的抽象方法、配置抽象方法对应的Sql语句,(Mybatis的所有方法都是抽象的,必须定义在接口中,Mybatis通过接口代理模式来实现,方法的返回值为int或能装载的返回数据的类型)
1>、创建接口:
2>、创建实体类,实体类属性于数据库字段对应:
实现Serializable接口,生成get、set、equals、hashcode方法。
package com.stu.mybatis.entity;
import java.io.Serializable;
import java.util.Objects;
public class Admin implements Serializable {
private String name;
private String password;
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
Admin admin = (Admin) o;
return Objects.equals(name, admin.name) && Objects.equals(password, admin.password);
}
@Override
public int hashCode() {
return Objects.hash(name, password);
}
@Override
public String toString() {
return "Admin{" +
"name='" + name + '\'' +
", password='" + password + '\'' +
'}';
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
3>、引用配置,让接口被Mybatis识别。找到SpringConfig配置类,添加注解:
@MapperScan("com.stu.mybatis.mapper")
4>、配置抽象方法对应的Sql语句,在resources文件夹下创建mapper文件夹,创建xml文件amespace是必须的,指定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.stu.mybatis.mapper.AdminMapper">
</mapper>
<mapper namespace="com.stu.mybatis.mapper.AdminMapper">
<insert id="insertAdmin">
insert into admin (
name,password
) values (
#{name},#{password}
)
</insert>
</mapper>
5>、在datasource.properties文件中添加XML文件位置
mybatis.mapper-locations = classpath:mapper/testMapper.xml
6>、在配置类中创建SqlSessionFactoryBean类型对象
@Bean
public SqlSessionFactoryBean sqlSessionFactoryBean(DataSource dataSource, @Value("${mybatis.mapper-locations}") Resource mapperLocations) {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
sqlSessionFactoryBean.setMapperLocations(mapperLocations);
return sqlSessionFactoryBean;
}
7>、编写测试方法,测试插入数据。
@Test
public void testInsert(){
AnnotationConfigApplicationContext ac = new AnnotationConfigApplicationContext(SpringConfig.class);
AdminMapper adminMapper = ac.getBean(AdminMapper.class);
Admin admin = new Admin();
admin.setName("admin001");
admin.setPassword("1234");
adminMapper.insertAdmin(admin);
ac.close();
}
测试成功:
8>、获取自动编号id
添加id属性,并自动生成其他的方法
修改XML文件
<insert id="insertAdmin" useGeneratedKeys="true" keyProperty="id">
insert into admin (
name,password
) values (
#{name},#{password}
)
</insert>
修改测试类
@Test
public void testInsert(){
AnnotationConfigApplicationContext ac = new AnnotationConfigApplicationContext(SpringConfig.class);
AdminMapper adminMapper = ac.getBean(AdminMapper.class);
Admin admin = new Admin();
admin.setName("admin005");
admin.setPassword("1234");
System.out.println("执行前:"+admin);
adminMapper.insertAdmin(admin);
System.out.println("执行后:"+admin);
ac.close();
}
运行测试,成功得到id的值
5、数据的删除与修改
数据删除:
1>、添加接口中的 抽象方法:
2>、 编写删除操作的SQL语句:
3>、编写测试方法:
@Test
public void testDeleteById(){
AnnotationConfigApplicationContext ac = new AnnotationConfigApplicationContext(SpringConfig.class);
AdminMapper adminMapper = ac.getBean(AdminMapper.class);
Long id = 1L;
int rows = adminMapper.deleteById(id);
System.out.println("受影响行数为:"+rows);
ac.close();
}
测试结果:
数据修改:
1>、添加接口中的抽象方法:
int updatePasswordById(Long id ,String password);
2>、 编写修改操作的SQL语句:
<update id="updatePasswordById">
update admin set password = #{password} where id = #{id}
</update>
3>、编写测试:
@Test
public void testUpdatePasswordById(){
AnnotationConfigApplicationContext ac = new AnnotationConfigApplicationContext(SpringConfig.class);
AdminMapper adminMapper = ac.getBean(AdminMapper.class);
Long id = 2L;
String password = "admin123";
int rows = adminMapper.updatePasswordById(id,password);
System.out.println("受影响行数为:"+rows);
ac.close();
}
测试结果:
6、数据查询
统计数据:
1>、XML文件中书写SQL语句
<select id="count" resultType="int">
select count(*) form admin
</select>
2>、添加接口抽象方法:
int count();
3>、编写测试:
@Test
public void testCount(){
AnnotationConfigApplicationContext ac = new AnnotationConfigApplicationContext(SpringConfig.class);
AdminMapper adminMapper = ac.getBean(AdminMapper.class);
int count = adminMapper.count();
System.out.println("总行数为:"+count);
ac.close();
}
查询某一条数据:
1>、编写接口抽象方法:
Admin getById(Long id);
2>、xml文件中添加SQL语句:
<select id="getById" resultType="com.stu.mybatis.entity.Admin" >
select * from admin where id=#{id}
</select>
3>、编写测试方法:
@Test
public void testGetById(){
AnnotationConfigApplicationContext ac = new AnnotationConfigApplicationContext(SpringConfig.class);
AdminMapper adminMapper = ac.getBean(AdminMapper.class);
Long id = 8L;
Admin admin = adminMapper.getById(id);
System.out.println("受影响行数为:"+admin);
ac.close();
}
注:当查询结果集中的列名和类的属性名不匹配时,默认将放弃处理这些结果的数据,返回的属性值为null。在解决这些问题时,可以通过配置<resultMap>指导Mybatis封装查询结果
<resultMap>书写格式:
<!-- resultMap节点的作用是:指导Mybatis如何将结果集中的数据封装到返回的对象中 -->
<!-- id属性:自定义名称 -->
<!-- type属性:将结果集封装到哪种类型的对象中 -->
<resultMap id="" type="">
<!-- 使用若干个result节点配置名称不统一的对应关系 -->
<!-- 在单表查询中,名称本来就一致的是不需要配置的 -->
<!-- column属性:列名 -->
<!-- property属性:属性名 -->
<result column="" property="" />
</resultMap>
查询列表
1>、添加接口的抽象方法:
List<Admin> selectAll();
2>、添加SQL语句:
<select id="selectAll" resultType="com.stu.mybatis.entity.Admin" >
select * from admin
</select>
3>、测试语句:
@Test
public void testSelectAll(){
AnnotationConfigApplicationContext ac = new AnnotationConfigApplicationContext(SpringConfig.class);
AdminMapper adminMapper = ac.getBean(AdminMapper.class);
List<Admin> list = adminMapper.selectAll();
for (Admin admin : list){
System.out.println(admin);
}
ac.close();
}
7、动态SQL
批量删除:
1>、添加抽象方法;
int deleteByIds(Long... ids);
2>、编辑动态SQL语句:
<delete id="deleteByIds">
delete from admin where id in (
<foreach collection="array" item="id" separator=",">
#{id}
</foreach>
)
</delete>
3>、编写试方法:
@Test
public void testDeleteByIds(){
AnnotationConfigApplicationContext ac = new AnnotationConfigApplicationContext(SpringConfig.class);
AdminMapper adminMapper = ac.getBean(AdminMapper.class);
int rows = adminMapper.deleteByIds(4L,5L,7L,8L);
System.out.println("受影响行数为:"+rows);
ac.close();
}
8、关联查询
1>、创建供操作的数据库表(此处数据库表根据个人情况,自行创建);
2>、编写SQL语句;
在使用关联查询时,经常会涉及到SQL语句代码的复用,可通过sql和include节点实现复用。
<sql id=" SimpleQueryFields ">id, username, password</sql><select id="" resultType="xx.xx.xx.AdminVO">select <include refid=" SimpleQueryFields " />from admin where id=#{id}</select>
3>、创建查询结果对应的实体类(实体类需要包含所有查询的数据字段的对应的实体属性)