入门MyBatis
使用MyBatis框架的作用主要是简化JDBC
JDBC缺点
- 注册驱动,获取连接
- SQL语句
- 手动设置参数
- 手动封装结果集
MyBatis优点
- 将字符串信息写入配置文件
- 免除几乎所有的JDBC代码,以及获取参数和获取结果集的工作
MyBatis快速入门
- 创建user表添加数据
- 创建模块导入坐标
- 编写Mybatis核心配置文件 – 》替换连接信息 解决硬编码问题
- 编写SQL映射文件 --》 统一管理sql语句,解决硬编码问题
- 编码
- 定义POJO类
- 加载核心配置文件,获取SqlSessionFactory对象
- 获取Session对象,执行SQL语句
- 释放资源
创建user表添加数据
名称 | 数据类型 | 长度 | 描述 |
---|---|---|---|
id | int | 10 | 主键自增 |
username | varchar | 20 | |
password | varchar | 20 | |
gender | varchar | 1 | |
addr | varchar | 30 |
创建模块导入坐标
<dependencies>
<!--Mybatis 依赖-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.5</version>
</dependency>
<!--Mysql 驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
<!--junit 单元测试-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13</version>
<scope>test</scope>
</dependency>
<!--添加slf4j 日志api-->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.20</version>
</dependency>
<!--添加logback-classic依赖-->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.2.3</version>
</dependency>
<!--添加logback-core依赖-->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-core</artifactId>
<version>1.2.3</version>
</dependency>
</dependencies>
编写Mybatis核心配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<!-- 数据库的连接信息-->
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///mybatis?useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<!-- 加载sql的映射文件-->
<mapper resource="UserMapper.xml"/>
</mappers>
</configuration>
注意:
数据库连接信息留下备用。
其中的sql映射文件因为和UserMapper.xml文件属于同一目录,所以可以直接引用。其中的文件数量视情况而定。
编写SQL映射文件
<?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">
<!--
namespace :名称空间
id: sql语句的唯一标识
resultType: 返回所要包装的类型
-->
<mapper namespace="test">
<select id="selectAll" resultType="com.example.pojo.User">
select * from tb_user;
</select>
</mapper>
编码
- 定义POJO类
创建pojo目录,创建实体类(.java文件)按照数据库定义所有的变量,定义所有getter和setter方法
-
加载核心配置文件,获取SqlSessionFactory对象
-
获取Session对象,执行SQL语句
-
释放资源
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import com.example.pojo.User;
/**
* Mybatis 快速入门
* */
public class MybatisDemo {
public static void main(String[] args) throws IOException {
// 1. 加载核心配置文件,获取SqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 2. 获取Session对象,执行SQL语句
SqlSession sqlSession = sqlSessionFactory.openSession();
// 3. 执行sql
List<User> users = sqlSession.selectList("test.selectAll");
System.out.println(users);
// 4. 释放资源
sqlSession.close();
}
}
最后把项目run起来,你的项目就可以运行了
使用idea编写sql代码
链接数据库
注意:我使用的MySQL版本是8.0.26
调出console控制台
现在我们就可以愉快地使用idea来写sql语法了
Mapper代理开发
- 定义与SQL映射文件同名的Mapper接口,并且将Mapper接口和SQL映射文件放置在统一目录下
- 设置SQL映射文件的namespace属性为Mapper接口全限定名
- 在Mapper接口中定义方法,方法名就是SQL映射文件中的sql语句中的id,并保持参数类型和返回值类型一致
- 编码
- 通过SqlSession 的 getMapper方法获取Mapper接口的代理对象
- 调用对应方法完成sql的执行
定义与SQL映射文件同名的Mapper接口
注意:这里创建目录的时候需要使用/
作为分隔符,否则编译的时候创建文件会加上.
别忘了修改mybatis.config.xml
文件
编码
package com.example;
import com.example.mapper.UserMapper;
import com.example.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
* Mybatis 代理开发
* */
public class MybatisDemo2 {
public static void main(String[] args) throws IOException {
// 1. 加载核心配置文件,获取SqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 2. 获取Session对象,执行SQL语句
SqlSession sqlSession = sqlSessionFactory.openSession();
// 3. 执行sql
// List<User> users = sqlSession.selectList("test.selectAll");
// 3.1 获取UserMapper接口的代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> users = userMapper.selectAll();
System.out.println(users);
// 4. 释放资源
sqlSession.close();
}
}
在上面mybatis.config.xml文件中可以使用包扫描的方式来读入mapper接口文件
MyBatis核心配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 配置别名-->
<typeAliases>
<package name="com.example.pojo"/>
</typeAliases>
<!--
environment:配置数据库连接环境信息,可以配置多个,通过default属性切换不同的environment
-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<!-- 数据库的连接信息-->
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///mybatis?useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
<environment id="test">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<!-- 数据库的连接信息-->
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///mybatis?useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<!-- 加载sql的映射文件-->
<!-- <mapper resource="com/example/mapper/UserMapper.xml"/>-->
<!-- 使用包扫描的方式 Mapper代理 可以获取所有的mapper接口-->
<package name="com.example.mapper"/>
</mappers>
</configuration>
安装mybatisx插件
mybatisx插件的优点:方便mybatis中的xml文件与java的mapper接口之间的跳转。
配置文件完成增删改查
mybatis完成数据库的查询操作
步骤:
- 编写接口方法
- 编写sql
- 执行方法
编写接口方法
public interface BrandMapper {
/**
* 查询所有
* */
public List<Brand> selectAll();
}
编写sql
<mapper namespace="com.example.mapper.BrandMapper">
<select id="selectAll" resultType="brand">
select *
from tb_brand;
</select>
</mapper>
执行方法
@Test
public void testSelectAll() throws IOException {
// 1. 获取sqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 2. 获取sqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
// 3. 获取mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
// 4. 执行方法
List<Brand> brands = brandMapper.selectAll();
System.out.println(brands);
// 5. 释放资源
sqlSession.close();
}
注意:Springboot里面可以设置命名规则关系映射,可以有效映射出所有的下划线命名和驼峰命名
mybatis里面可以在xml文件中设置resultMap标签一个一个设置驼峰命名
参数占位符
参数占位符:
1. #{} : 会将值替换为?,防止了SQL注入
2. ${} : 拼sql,会存在SQL注入问题
3. 使用时机:
* 参数传递的时候 : #{}
* 表名或者列名不固定的时候 : ${}
多条件查询
散装参数的多条件查询
brandMapper.xml
<select id="selectByCondition" resultMap="brandResultMap">
select *
from tb_brand
where
status = #{status}
and company_name like #{companyName}
and brand_name like #{brandName}
</select>
BrandMapper.java
public interface BrandMapper {
/**
* 条件查询
* 参数接收:
* 1. 散装参数:需要使用@Param()。对象属性名称需要和参数占位符一致。
* 2. 对象参数
* 3. map集合的参数
* */
public List<Brand> selectByCondition(@Param("status") int status, @Param("companyName") String companyName, @Param("brandName") String brandName);
}
MybatisTest.java
@Test
public void testSelectByCondition() throws IOException {
// 接收参数
int status = 1;
String companyName = "华为";
String brandName = "华为";
// 处理参数
companyName = "%" + companyName + "%";
brandName = "%" + brandName + "%";
// 1. 获取sqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 2. 获取sqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
// 3. 获取mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
// 4. 执行方法
List<Brand> brands = brandMapper.selectByCondition(status,companyName,brandName);
System.out.println(brands);
// 5. 释放资源
sqlSession.close();
}
对象参数的多条件查询
BrandMapper.java
public interface BrandMapper {
/**
* 条件查询
* 参数接收:
* 1. 散装参数:需要使用@Param()
* 2. 对象参数
* 3. map集合的参数
* */
List<Brand> selectByCondition(Brand brand);
}
MybatisTest.java
@Test
public void testSelectByCondition() throws IOException {
// 接收参数
int status = 1;
String companyName = "华为";
String brandName = "华为";
// 处理参数
companyName = "%" + companyName + "%";
brandName = "%" + brandName + "%";
// 封装对象
Brand brand = new Brand();
brand.setStatus(status);
brand.setBrandName(brandName);
brand.setCompanyName(companyName);
// 1. 获取sqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 2. 获取sqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
// 3. 获取mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
// 4. 执行方法
// List<Brand> brands = brandMapper.selectByCondition(status,companyName,brandName);
List<Brand> brands = brandMapper.selectByCondition(brand);
System.out.println(brands);
// 5. 释放资源
sqlSession.close();
}
动态条件查询
情境:输入条件时,用户是否都会填写内容
<select id="selectByCondition" resultMap="brandResultMap">
select *
from tb_brand
where
<if test="status != null">
status = #{status}
</if>
<if test="companyName != null and companyName != ''">
and company_name like #{companyName}
</if>
<if test="brandName != null and brandName != ''">
and brand_name like #{brandName}
</if>
</select>
if标签:
test:逻辑表达式
问题:如果第一个if判断没有值会出现where-and 语法错误
解决1:在每一个if里面加上and,然后在where后面加上and
解决2:where标签替换where关键字
<!--解决方案1-->
<select id="selectByCondition" resultMap="brandResultMap">
select *
from tb_brand
where 1 = 1
<if test="status != null">
and status = #{status}
</if>
<if test="companyName != null and companyName != ''">
and company_name like #{companyName}
</if>
<if test="brandName != null and brandName != ''">
and brand_name like #{brandName}
</if>
</select>
<!--解决方案1-->
<select id="selectByCondition" resultMap="brandResultMap">
select *
from tb_brand
<where>
<if test="status != null">
status = #{status}
</if>
<if test="companyName != null and companyName != ''">
and company_name like #{companyName}
</if>
<if test="brandName != null and brandName != ''">
and brand_name like #{brandName}
</if>
</where>
</select>
但条件动态查询
BrandMapper.xml
<select id="selectByConditionSingle" resultMap="brandResultMap">
select *
from tb_brand
where
<choose>
<when test="status != null">
status = #{status}
</when>
<when test="companyName != null and companyName != ''">
company_name like #{companyName}
</when>
<when test="brandName != null and brandName != ''">
brand_name like #{brandName}
</when>
</choose>
</select>
BrandMapper.java
public interface BrandMapper {
List<Brand> selectByCondition(Brand brand);
// 单条件动态查询
List<Brand> selectByConditionSingle(Brand brand);
}
MybatisTest.java
@Test
public void testSelectByConditionSingle() throws IOException {
// 接收参数
int status = 1;
String companyName = "华为";
String brandName = "华为";
// 处理参数
companyName = "%" + companyName + "%";
brandName = "%" + brandName + "%";
// 封装对象
Brand brand = new Brand();
brand.setStatus(status);
// brand.setBrandName(brandName);
// brand.setCompanyName(companyName);
// 1. 获取sqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 2. 获取sqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
// 3. 获取mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
// 4. 执行方法
// List<Brand> brands = brandMapper.selectByCondition(status,companyName,brandName);
List<Brand> brands = brandMapper.selectByConditionSingle(brand);
System.out.println(brands);
// 5. 释放资源
sqlSession.close();
}
mybatis完成数据库的添加操作
在数据库中插入记录行
BrandMapper.java
public interface BrandMapper {
void add(Brand brand);
}
MybatisTest.java
@Test
public void testAdd() throws IOException {
// 接收参数
int status = 1;
String companyName = "波导手机";
String brandName = "波导";
String description = "手机中的战斗机";
int ordered = 100;
// 封装对象
Brand brand = new Brand();
brand.setStatus(status);
brand.setBrandName(brandName);
brand.setCompanyName(companyName);
brand.setDescription(description);
brand.setOrdered(ordered);
// 1. 获取sqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 2. 获取sqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
// 3. 获取mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
// 4. 执行方法
brandMapper.add(brand);
// // 提交事物
// sqlSession.commit();
// 5. 释放资源
sqlSession.close();
}
BrandMapper.xml
<insert id="add">
insert into tb_brand(brand_name, company_name, ordered, description, status)
values (#{brandName},#{companyName},#{ordered},#{description},#{status})
</insert>
在数据库中插入记录行同时获取id
在插入代码中进行修改
MybatisTest.java
Integer id = brand.getId();
System.out.println(id);
BrandMapper.xml
<insert id="add" useGeneratedKeys="true" keyProperty="id">
insert into tb_brand(brand_name, company_name, ordered, description, status)
values (#{brandName},#{companyName},#{ordered},#{description},#{status})
</insert>
mybatis完成数据库的修改操作
修改数据库中某一行的所有数据
BrandMapper.java
public interface BrandMapper {
void update(Brand brand);
}
BrandMapper.xml
<update id="update">
update tb_brand
set
brand_name = #{brandName},
company_name = #{companyName},
ordered = #{ordered},
description = #{description},
status = #{status}
where id = #{id};
</update>
Mybatis.java
@Test
public void testUpdate() throws IOException {
// 接收参数
int status = 1;
String companyName = "波导手机";
String brandName = "波导";
String description = "波导手机,手机中的战斗机";
int ordered = 200;
int id = 4;
// 封装对象
Brand brand = new Brand();
brand.setStatus(status);
brand.setBrandName(brandName);
brand.setCompanyName(companyName);
brand.setDescription(description);
brand.setOrdered(ordered);
brand.setId(id);
// 1. 获取sqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 2. 获取sqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
// 3. 获取mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
// 4. 执行方法
brandMapper.update(brand);
// // 提交事物
// sqlSession.commit();
// 5. 释放资源
sqlSession.close();
}
修改动态字段
BrandMapper.xml
<update id="update">
update tb_brand
<set>
<if test="brandName != null and brandName != ''">
brand_name = #{brandName},
</if>
<if test="companyName != null and companyName != ''">
company_name = #{companyName},
</if>
<if test="ordered != null and ordered != ''">
ordered = #{ordered},
</if>
<if test="description != null and description != ''">
description = #{description},
</if>
<if test="status != null and status != ''">
status = #{status}
</if>
</set>
where id = #{id};
</update>
mybatis完成数据库的删除操作
删除一个
BrandMapper.java
public interface BrandMapper {
void deleteById(int id);
}
BrandMapper.xml
<delete id="deleteById">
delete from tb_brand where id = #{id};
</delete>
Mybatis.java
@Test
public void testDeleteById() throws IOException {
// 接收参数
int id = 4;
// 1. 获取sqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 2. 获取sqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
// 3. 获取mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
// 4. 执行方法
brandMapper.deleteById(id);
// // 提交事物
// sqlSession.commit();
// 5. 释放资源
sqlSession.close();
}
批量删除
BrandMapper.java
public interface BrandMapper {
void deleteByIds(@Param("ids") int[] ids);
}
BrandMapper.xml
注意:动态删除的sql语句在mybatis的foreach标签中collection属性只能是array,除非在传参的时候用@Params注解修饰过
上面的方法中也可以不用@Param注解修饰
<delete id="deleteByIds">
delete from tb_brand where id
in (<foreach collection="ids" item="id" separator=",">
#{id}
</foreach>);
</delete>
Mybatis.java
@Test
public void testDeleteByIds() throws IOException {
// 接收参数
int ids [] = {1,2,3};
// 1. 获取sqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 2. 获取sqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
// 3. 获取mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
// 4. 执行方法
brandMapper.deleteByIds(ids);
// // 提交事物
// sqlSession.commit();
// 5. 释放资源
sqlSession.close();
}