MyBatis总结

Mybatis基础

[TOC]

概念

MyBatis是一个ORM持久层的框架

持久层:对数据库进行操作的一层

ORM:对象关系映射规范

面试题:MyBatis相较于jdbc的优点

jdbc重复性代码较多,mybatis不需要去写加载数据库驱动的那些步骤

jdbc封装代码比较麻烦,mybatis自动封装

MyBatis的使用

导包

这里我创建的是普通Java项目,没有通过Maven管理,如果是Maven管理只需要加入一句依赖即可

1
2
3
4
5
6
7
8
9
10
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.17</version>
</dependency>

配置核心文件

在resources中新建文件mybatis-config.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
<?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>
<!--(1)引入jdbc.propeties文件-->
<properties resource="db.properties" />
<!-- 环境们 (很多环境的意思)
default:默认使用哪一个环境(必需对应一个环境的id)
-->
<!-- 配置别名 -->
<typeAliases>
<package name="com.ifueen.mybatis.domain"/>
<package name="com.ifueen.mybatis.query"/>
</typeAliases>

<environments default="development">
<!--
一个环境 id:为这个环境取唯一一个id名称
-->
<environment id="development">
<!--
事务管理 type:JDBC(支持事务)/MANAGED(什么都不做-就不用事务)
数据库引擎设置innodb
-->
<transactionManager type="JDBC" />
<!--
数据源, 连接池 type(POOLED):MyBatis自带的连接池
https://blog.csdn.net/crankz/article/details/82874158
-->
<dataSource type="POOLED">
<!-- 连接数据库的参数 -->
<property name="driver" value="${jdbc.driverClassName}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</dataSource>
</environment>
</environments>
<!-- 这个mappers代表的是相应的ORM映射文件 -->
<mappers>
<mapper resource="com/ifueen/mybatis/dao/impl/ProductMapper.xml" />
</mappers>

</configuration>

db.properties

1
2
3
4
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql:///mybatis
jdbc.username=root
jdbc.password=123456

然后配置相应的mybatisMapper文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
<?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.ifueen.mybatis.dao.impl.ProductDaoImpl">
<!-- 增加 -->
<!-- useGeneratedKeys="true" keyColumn="id" keyProperty="id" 设置拿到主键-->
<insert id="insert" parameterType="com.ifueen.mybatis.domain.Product" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
insert into product (productName,brand,supplier,salePrice,costPrice,cutoff,dir_id)
values (#{productName},#{brand},#{supplier},#{salePrice},#{costPrice},#{cutoff},#{dir_id})
</insert>

<!-- 删除 -->
<delete id="delete" parameterType="long">
delete from product where id = #{id}
</delete>

<!-- 修改 -->
<update id="update" parameterType="product">
update product set productName=#{productName},brand=#{brand},
supplier=#{supplier},salePrice=#{salePrice},
costPrice=#{costPrice},cutoff=#{cutoff},dir_id=#{dir_id}
where id=#{id}
</update>

<!-- 通过ID查询 -->
<select id="findById" parameterType="long" resultType="product">
select * from product where id = #{id}
</select>

<!-- 查询全部 -->
<select id="findAll" resultType="product">
select * from product
</select>
</mapper>

我是将它放在dao层的实现层下面

利用Mybatis进行CRUD

准备好实体类Product

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
package com.ifueen.mybatis.domain;

import java.math.BigDecimal;

public class Product {

private Long id;
//商品名称
private String productName;
//品牌
private String brand;
//供应商
private String supplier;
//零售价
private BigDecimal salePrice;
//进价
private BigDecimal costPrice;
//折扣价
private Double cutoff;
//商品分类编号
private Long dir_id;

//...getset
}

Dao层接口

1
2
3
4
5
6
7
8
public interface IProductDao {

void insert(Product product) throws IOException;
void update(Product product);
void delete(Long id);
Product findById(Long id);
List<Product> findAll();
}

然后实现类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
public class ProductDaoImpl implements IProductDao {


@Override
public void insert(Product product) throws IOException {

//得到sqlSession,来运行方法
SqlSession sqlSession = MyBatisUtil.INSTANCE.createSqlSession();
sqlSession.insert("com.ifueen.mybatis.dao.impl.ProductDaoImpl.insert",product);
//提交事务
sqlSession.commit();
}

@Override
public void update(Product product) {
SqlSession sqlSession = MyBatisUtil.INSTANCE.createSqlSession();
sqlSession.update("com.ifueen.mybatis.dao.impl.ProductDaoImpl.update",product);
sqlSession.commit();
}

@Override
public void delete(Long id) {
SqlSession sqlSession = MyBatisUtil.INSTANCE.createSqlSession();
sqlSession.delete("com.ifueen.mybatis.dao.impl.ProductDaoImpl.delete",id);
sqlSession.commit();
}

@Override
public Product findById(Long id) {
SqlSession sqlSession = MyBatisUtil.INSTANCE.createSqlSession();
Product product = sqlSession.selectOne("com.ifueen.mybatis.dao.impl.ProductDaoImpl.findById", id);
sqlSession.commit();
return product;
}

@Override
public List<Product> findAll() {
SqlSession sqlSession = MyBatisUtil.INSTANCE.createSqlSession();
List<Product> list = sqlSession.selectList("com.ifueen.mybatis.dao.impl.ProductDaoImpl.findAll");
sqlSession.commit();
return list;
}
}

在此之前我将创建SqlSession对象抽取出来封装成了一个工具类

MyBatisUtil

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
public enum  MyBatisUtil {

INSTANCE;

public static SqlSessionFactory sqlSessionFactory;
static {
InputStream is = null;
try {
//读取配置文件 --获取对象
//通过读取配置文件得到 SqlSessionFactory -- EntityManagerFactory
is = Resources.getResourceAsStream("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
} catch (IOException e) {
e.printStackTrace();
}
}

public SqlSession createSqlSession(){
SqlSession sqlSession = sqlSessionFactory.openSession();
return sqlSession;
}

}

创建测试类进行测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
public class ProductDaoImplTest {

/**
* 保存
* @throws IOException
*/
@Test
public void save() throws IOException {
Product product = new Product();
product.setProductName("DunkSB");
product.setBrand("Nike");
product.setSupplier("迈阿密供应商");
product.setCostPrice(new BigDecimal(10));
product.setSalePrice(new BigDecimal(5000));
System.out.println(product);
new ProductDaoImpl().insert(product);
System.out.println(product);
}

/**
* 更新
*/
@Test
public void update(){
Product product = new Product();
product.setProductName("Converse1970s");
product.setBrand("Converse");
product.setSupplier("越南供应商");
product.setCostPrice(new BigDecimal(10));
product.setSalePrice(new BigDecimal(5000));
product.setId(22L);
new ProductDaoImpl().update(product);
}

/**
* 删除
*/
@Test
public void delete(){
new ProductDaoImpl().delete(23L);
}

/**
* 通过ID查找
*/
@Test
public void findById(){
Product product = new ProductDaoImpl().findById(22L);
System.out.println(product);
}

/**
* 查询全部
*/
@Test
public void findAll(){
List<Product> products = new ProductDaoImpl().findAll();
products.forEach(product -> {
System.out.println(product);
});
}

}

MyBatis高级查询

上面我们进了最基本的CRUD,现在来看看通过Mybatis实现高级查询

批量删除

1
2
3
4
5
6
7
8
9
<!-- 批量删除 -->
<delete id="deleteBatch" parameterType="list">
delete from product where id in
<foreach collection="list" index="index" item="id" open="(" separator="," close=")">
#{id}
</foreach>


</delete>

然后测试

1
2
3
4
5
6
7
8
/**
* 批量删除
*/
@Test
public void deleteBatch(){
List<Integer> list = Arrays.asList(22, 24);
new ProductDaoImpl().deleteBatch(list);
}

批量增加

1
2
3
4
5
6
7
8
<!-- 批量添加 -->
<insert id="insertBatch" parameterType="list">
insert into product (productName,brand,supplier,salePrice,costPrice,cutoff,dir_id)
value
<foreach collection="list" index="index" item="product" separator=",">
(#{product.productName},#{product.brand},#{product.supplier},#{product.salePrice},#{product.costPrice},#{product.cutoff},#{product.dir_id})
</foreach>
</insert>

然后测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/**
* 批量添加
*/
@Test
public void insertBatch(){
Product product = new Product();
product.setProductName("山泥若");
product.setSupplier("猪");
Product product1 = new Product();
product1.setProductName("电棍");
product1.setSupplier("猴");
ArrayList<Product> list = new ArrayList<>();
list.add(product);
list.add(product1);
new ProductDaoImpl().insertBatch(list);
}

模糊查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<!-- 高级查询 -->
<select id="selectSsnior" parameterType="productQuery" resultType="product">
select * from product
/* 这里写查询条件 */
<where>
<include refid="whereSql"/>
</where>
</select>

<!-- 将高级查询条件抽取出来 -->
<sql id="whereSql">
<if test="productName != null">
and productName like #{productName}
</if>
<if test="brand != null">
and brand like #{brand}
</if>
</sql>

测试

1
2
3
4
5
6
7
8
9
10
11
12
13
/**
* 高级查询
*/
@Test
public void selectSsnior(){
ProductQuery query = new ProductQuery();
query.setProductName("罗%");
query.setBrand("罗%");
List<Product> list = new ProductDaoImpl().selectSsnior(query);
list.forEach(product -> {
System.out.println(product);
});
}

面试题:$和#的区别?

$在mybais中代表拼接的意思,将传入的数据之间显示成为sql语句

比如:select *from user where id =${id}传给id的值为username,username的值为11

相当于select * user where id = username

#是取后面的值

比如:select *from user where id =${id}传给id的值为username,username的值为11

相当于select * user where id = 11

大多数情况都是用#

日志框架Log4j使用

将Log4j包导入后在resources中进行配置

log4j.properties

1
2
3
4
5
6
7
8
9
10
11
log4j.rootLogger=ERROR, stdout
#屏蔽输出
#log4j.rootLogger=NONE
# 配置日志级别
# 配置包名
log4j.logger.com.ifueen=TRACE
# ConsoleAppender 表示输出到控制台
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
# 输出的格式
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d %p [%c] - %m%n

然后再次运行的时候就可以看见效果,下面拿查询单个实体举例

❤赏点钱让我买杯快乐水8❤