(一)知识点梳理:
1.什么是Mybatis?
mybatis是一个支持普通sql查询,存储过程以及高级映射的持久层框架。
mybatis也称为ORM(对象关系映射框架),ORM就是一种为了解决面向对象与关系数据库中数据类型不匹配的技术,通过描述Java对象与数据库表之间的映射关系,自动地将应用程序中的对象持久化到关系数据库的表中。
2.Mybatis的工作原理?
(1)读取配置文件mybatis-config.xml;
(2)加载映射文件xxxMapper.xml;
(3)构建会话工厂SqlSession;
(4)构建会话对象SqlSession;
(5)Executor执行器,执行相关操作;
(6)MappedStatement对象;
(7)输入映射;
(8)输出映射;
3.查询客户,分为单条客户数据的精确查询和多条数据的模糊查询;
(1)精确查询:
例:select * from t_customer where id=#{id}
(2)模糊查询:
例:select * from t_customer where username like concat(’%’,#{username},’%’)
4.添加、更新、删除客户都需要在对应的测试方法中进行事物的提交commit()操作。
5.实际在单元测试中分5步完成mybatis框架的使用:
(1)读取配置文件mybatis-config.xml;
(2)根据配置文件创建会话工厂SqlSessionFactory;
(3)根据会话工厂创建会话SqlSession;
(4)会话执行相应的操作;
(5)关闭会话(若是增删改操作需要先提交事务);
(二) 实例:
(1)database.sql
#创建一个名称为mybatis的数据库
create database mybatis;
#使用mybatis数据库
use mybatis;
#创建t_customer的表
create table t_customer(
id int(32) primary key auto_increment,
username varchar(50),
jobs varchar(50),
phone varchar(16)
);
#插入数据
insert into t_customer values('1','joy','doctor','17339821917');
insert into t_customer values('2','jack','teacher','17339821916');
insert into t_customer values('3','rose','student','17339821911');
insert into t_customer values('4','tom','cooker','17339821910');
(2)日志信息log4j.properties
# Global logging configuration
log4j.rootLogger=ERROR, stdout
# MyBatis logging configuration...
log4j.logger.com.wds=DEBUG
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
(3)配置文件mybatis-config.xml
<?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.配置环境-->
<environments default="mysql">
<environment id="mysql">
<!--只用JDBC的事务管理-->
<transactionManager type="JDBC"></transactionManager>
<!--数据库连接池-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
<property name="username" value="root"/>
<property name="password" value="12345678"/>
</dataSource>
</environment>
</environments>
<!--2.映射文件-->
<mappers>
<mapper resource="com/wds/po/CustomerMapper.xml"/>
</mappers>
</configuration>
(4)实体类Customer.java
package com.wds.po;
public class Customer {
private Integer id;
private String username;
private String jobs;
private String phone;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getJobs() {
return jobs;
}
public void setJobs(String jobs) {
this.jobs = jobs;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "Customer{" +
"id=" + id +
", username='" + username + '\'' +
", jobs='" + jobs + '\'' +
", phone='" + phone + '\'' +
'}';
}
}
(5)映射文件CustomerMapper.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.wds.po.Customer">
<select id="findCustomerById" parameterType="Integer" resultType="com.wds.po.Customer">
<!--#{id}相当于占位符?-->
select * from t_customer where id = #{id}
</select>
<select id="findCustomerByName" parameterType="String" resultType="com.wds.po.Customer">
<!--select * t_customer where username like '%${value}%'-->
<!--使用concat('%',#{username},'%')防止sql注入问题-->
select * from t_customer where username like concat('%',#{username},'%')
</select>
<insert id="addCustomer" parameterType="com.wds.po.Customer">
insert into t_customer(username, jobs, phone)
values (#{username}, #{jobs}, #{phone})
</insert>
<update id="updateCustomer" parameterType="com.wds.po.Customer">
update t_customer
set username = #{username},
jobs = #{jobs},
phone = #{phone}
where id = #{id}
</update>
<delete id="deleteCustomer" parameterType="com.wds.po.Customer">
delete
from t_customer
where id = #{id}
</delete>
</mapper>
(6)测试类MybatisTest
package com.wds.test;
import com.wds.po.Customer;
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 org.junit.Test;
import java.io.InputStream;
import java.util.List;
public class MybatisTest {
@Test
/**
* 单个客户数据的精确查询
*/
public void findCustomerById() throws Exception{
//1.读取配置文件
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
//2.根据配置文件构建会话工厂SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//3.根据sqlSessionFactory会话工厂创建对象SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
//4.sqlSession执行sql语句,返回结果
//第一个参数是sql的id,第二个参数是传入给sql的占位符的参数
Customer customer = sqlSession.selectOne("com.wds.po.Customer.findCustomerById",1);
//5.输出结果
System.out.println(customer.toString());
//6.关闭sqlSession
sqlSession.close();
}
/**
根据客户名称模糊查询用户列表
*/
@Test
public void findCustomerByName() throws Exception{
//1.读取配置文件
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
//2.根据配置问价构建会话工厂SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//3.根据sqlSessionFactory创建会话
SqlSession sqlSession = sqlSessionFactory.openSession();
//4.执行sqlSession
List<Customer> customers = sqlSession.selectList("com.wds.po.Customer.findCustomerByName","j");
//5.输出结果
for(Customer customer:customers){
System.out.println(customer.toString());
}
//6.关闭流
sqlSession.close();
}
/**
* 添加客户
*/
@Test
public void addCustomer() throws Exception{
//1.读取配置问价
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
//2.根据配置文件创建会话工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//3.根据会话工厂创建会话对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//4.执行SqlSession
//4.1 创建客户对象
Customer customer = new Customer();
customer.setUsername("东生");
customer.setJobs("coder");
customer.setPhone("18139954468");
//4.2 执行添加方法
//4.3第一个参数是sql的id,第二个是传给sql的占位符参数
int num = sqlSession.insert("com.wds.po.Customer.addCustomer", customer);
if(num>0){
System.out.println("插入操作执行成功!!!");
}else
{
System.out.println("执行失败!!!");
}
//4.4执行事务,提交事务
sqlSession.commit();
//5.关闭流
sqlSession.close();
}
/**
* 更新客户
*/
@Test
public void updateCustomer() throws Exception{
//1.读取配置问价
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
//2.根据配置文件创建会话工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//3.根据SqlSessionFactory创建会话
SqlSession sqlSession = sqlSessionFactory.openSession();
//4.执行操作
//4.1创建对象
Customer customer = new Customer();
customer.setId(1);
customer.setUsername("吕憨憨");
customer.setJobs("doctor");
customer.setPhone("110256");
int num = sqlSession.update("com.wds.po.Customer.updateCustomer",customer);
if(num>0){
System.out.println("您的修改已经成功!!");
}
//4.3提交事务
sqlSession.commit();
//5.关闭
sqlSession.close();
}
/**
* 删除客户操作
*/
@Test
public void deleteCustomer() throws Exception{
//1.读取配置问价
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
//2.根据配置问价创建会话工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//3.根据会话工厂创建会话对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//4.执行操作
int num = sqlSession.delete("com.wds.po.Customer.deleteCustomer",4);
if(num>0){
System.out.println("删除操作执行成功!!!");
}else{
System.out.println("删除操作执行失败!!!");
}
//提交事务,增删改都需要事务
sqlSession.commit();
//5.关闭会话
sqlSession.close();
}
}
(三)运行结果
(1)精确查询
(2)模糊查询
(3)添加客户
(4)更新客户
(6)删除客户