文章目录
mybatis框架
ORM 就是通过实例对象的语法，完成关系型数据库的操作的技术，是"对象-关系映射"（Object/Relational Mapping） 的缩写
面向对象与SQL如何交互？
数据库: SQL，以表为核心
java： java 面向对象，以对象和核心
（1）使用jdbc （五部曲） 进行数据库和java对象交互，但是操作繁琐。（ 不适用于商业开发)
（2）使用ORM框架 ： Hibernate / mybatis(国内使用较多)
1）Hibernate—>JPA框架（使用注解，极简编程，适用中小型项目、微服务）
2）Mybatis框架 —> 半自动化框架–>Mybatis PLUS
java(面向对象) — mybatis(java/xml) – 数据库(表):SQL
MyBatis
MyBatis是支持定制化SQL、存储过程以及高级映射的优秀的持久层框架。MyBatis 避免了几乎所有的JDBC代码和手动设置参数以及获取结果集。MyBatis 可以对配置和原生Map使用简单的XML或注解,将接口和Java的POJOs(Plainold Java 0bject,普通的Java对象)映射成数据库中的记录。
mybatis的开发流程
(1)数据库
创建库
mybatis_db
创建表
book_tab
(2)创建表的实体映射类
Book.java
配置lambok
/**
* 映射数据库的表book_tab
* @author Administrator
*
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Book {
private Integer id;
private String isbn;
private String title;
private String author;
private String cover;
private Double price;
private Date date;
}
(3)定义接口，进行表操作
IBookMapper.java 接口文件
类似于dao
创建 mapper包
/**
* 表的操作接口
* @author Administrator
*
*/
public interface IBookMapper {
void save(Book book); //存
void delete(int id); //删
void update(Book book); //改
List<Book> findAll(); //索引
}
(4)定义数据源
MybatisDataSource.java
使用Druid数据源替换mybatis内置的
druid、mysql，jdbc ，jar
/**
* 重定义数据源对象
* @author Administrator
*
*/
public class MybatisDataSource extends UnpooledDataSourceFactory { //继承数据源工厂
public MybatisDataSource() {
this.dataSource = new DruidDataSource();
}
}
(4)数据源配置文件
druid.properties file文件
配置druid
druid.driver = com.mysql.cj.jdbc.Driver
druid.url = jdbc:mysql://localhost:3306/mybatis_db?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
druid.username=root
druid.password=1234
(5)mybatis的配置文件(xml)
mybatis-config.xml 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>
<!-- 读取数据源配置文件 -->
<properties resource="resources/druid/druid.properties"/>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/><!-- 配置数据库事务管理 -->
<dataSource type="com.dyit.util.MybatisDataSource">
<property name="driverClassName" value="${druid.driver}"/>
<property name="url" value="${druid.url}"/>
<property name="username" value="${druid.username}"/>
<property name="password" value="${druid.password}"/>
</dataSource>
</environment>
</environments>
<!-- 表和类之间的映射 -->
<mappers>
</mappers>
</configuration>
(6)表和类的映射文件
类名.mapper.xml
namespace—>接口的位置
sql语句
更新config —>
<?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.dyit.mapper.IBookMapper"><!-- 接口的位置 -->
<insert id="save">
INSERT INTO book_tab(book_isbn,book_title,book_author,book_date,book_price,book_cover)
VALUES(#{isbn},#{title},#{author},#{date},#{price},#{cover})
</insert>
</mapper>
<?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>
<!-- 读取数据源配置文件 -->
<properties resource="resources/druid/druid.properties"/>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/><!-- 配置数据库事务管理 -->
<dataSource type="com.dyit.util.MybatisDataSource">
<property name="driverClassName" value="${druid.driver}"/>
<property name="url" value="${druid.url}"/>
<property name="username" value="${druid.username}"/>
<property name="password" value="${druid.password}"/>
</dataSource>
</environment>
</environments>
<!-- 表和类之间的映射,mapper文件创建之后再配置 -->
<mappers>
<mapper resource="resources/mapper/Book.mapper.xml"/>
</mappers>
</configuration>
(7)测试
读取mybatis配置文件
Reader in = Resources.getResourceAsReader("resources/config/mybatis-config.xml");
创建SqlSessionFactory工厂
管理多个数据库操作会话
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
开启一次sql会话
SqlSession sqlSession = sqlSessionFactory.openSession();
创建数据库操作接口对象
IBookMapper mapper = sqlSession.getMapper(IBookMapper.class);
接口操作
调用接口的方法
事务提交
sqlSession.commit();
mybatis详解mybatis
mybatis日志
log4j
日志配置
src下log4j.properties
log4j.rootLogger=debug,stdout,logfile
#console output
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.SimpleLayout
log4j.appender.stdout.PatternLayout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss}<-->%F<-->%p<-->%m%n
#file output
log4j.appender.logfile=org.apache.log4j.FileAppender
log4j.appender.logfile.File=D:/logs/log.log
log4j.appender.logfile.layout=org.apache.log4j.SimpleLayout
log4j.appender.logfile.PatternLayout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss}<-->%F<-->%p<-->%m%n
#mybatis log
log4j.logger.com.ibatis=DEBUG
log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=DEBUG
log4j.logger.com.ibatis.common.jdbc.ScriptRunner=DEBUG
log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUG
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
参数问题
mapper.xml
delete(int id)
book_id =#{id}
delete(Book book)
xml: book_id = #{id} -----> book.getId();
单参数（非对象) : 和参数名称一致
对象类型(Book) : 对象调用get方法
多个参数：
void updateAuthorOrPrice(int id, double sprice, double sauthor);
@Param
void updateAuthorOrPrice(@Param("sid")int sid,
@Param("sprice")double price,
@Param("sauthor")String author);
<update id="updateAuthorOrPrice">
UPDATE book_tab SET book_price= #{sprice},book_author=#{sauthor} WHERE book_id=#{sid}
</update>
mybatis 查询
单表
表中字段和类的属性不对应问题
解决方案一： 别名法
别名 book_id (AS) id, book_title (AS) title
解决方案二: 表中字段和属性一致
表中的字段和属性对应, boo_id —>id
解决方案三: 定义一个映射关系（表中字段和类中的属性), resultMap
<resultMap type="com.dyit.entity.Book" id="BookMap">
<id property="id" column="book_id"/><!-- 主键 -->
<result property="isbn" column="book_isbn"/>
<result property="title" column="book_title"/>
<result property="author" column="book_author"/>
<result property="price" column="book_price"/>
<result property="cover" column="book_cover"/>
<result property="date" column="book_date"/>
</resultMap>
<select id="findAll" resultMap="BookMap">
<!-- SELECT book_id AS id,book_title AS title FROM book_tab-->
SELECT * FROM book_tab
</select>
类的别名
mybatis-config.xml
<typeAliases>
<package name="com.dyit.entity"/>
</typeAliases>
实体类
@Alias("Bookx") //建议别名和类名相同
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Book {
private Integer id;
private String isbn;
private String title;
private String author;
private String cover;
private Double price;
private Date date;
}
mapper.xml中使用
<resultMap type="Bookx" id="BookMap">
<id property="id" column="book_id"/><!-- 主键 -->
<result property="isbn" column="book_isbn"/>
<result property="title" column="book_title"/>
<result property="author" column="book_author"/>
<result property="price" column="book_price"/>
<result property="cover" column="book_cover"/>
<result property="date" column="book_date"/>
</resultMap>
$和#号的区别
$
PreparedStatement
Statement
拼接
解决了SQL注入漏洞
SQL注入漏洞( OR 1=‘1’)
#{title} —>book.getTitle()
ORDER BY ${price} -->ORDER BY book_price
<select id="findBooksByPriceOrder" resultMap="BookMap">
SELECT * FROM book_tab ORDER BY ${price}
</select>
IBookMapper mapper = sqlSession.getMapper(IBookMapper.class);
List<Book> list = mapper.findBooksByPriceOrder("book_price DESC");
list.forEach(item->System.out.println(item));
缓存问题
#打开jbc连接，从数据源中取得COnnection
DEBUG - Opening JDBC Connection
INFO - {dataSource-1} inited
#将自动提交设置为false
DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@69a10787]
#PreparedStatment 读取SQL语句
DEBUG - ==> Preparing: SELECT * FROM book_tab WHERE book_id = ?
#给？号注入值
DEBUG - ==> Parameters: 4(Integer)
DEBUG - <== Total: 1
：：：
#将事务设置为自动提交(默认)
DEBUG - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@69a10787]
#将Connection放入数据源的连接池中
DEBUG - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@69a10787]
mybatis内置一个缓存，SqlSession对象- 一级缓存（运行速度块，空间小)，内置的，不能关闭的。
Reader in = Resources.getResourceAsReader("resources/config/mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
//开启一个sql会话
sqlSession = sqlSessionFactory.openSession();
IBookMapper mapper = sqlSession.getMapper(IBookMapper.class);
//将id为4的对象放入缓存 map("select * from booK_tab WHERE book_d=4",book);
Book book = mapper.findById(4);
System.out.println("book==>" + book);
//检测缓存看看是否存在，存在从缓存中返回，
//如果不存在从数据查询
Book book2=mapper.findById(4);
System.out.println(book2);
//提交事务，结束会话
sqlSession.commit();
sqlSession.close();
如果关闭SqlSession
Reader in = Resources.getResourceAsReader("resources/config/mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
sqlSession = sqlSessionFactory.openSession();
IBookMapper mapper = sqlSession.getMapper(IBookMapper.class);
Book book = mapper.findById(4);
System.out.println("book==>" + book);
sqlSession.commit();
//sqlSession.close();
sqlSession.clearCache();
sqlSession = sqlSessionFactory.openSession();
mapper = sqlSession.getMapper(IBookMapper.class);
Book book2 = mapper.findById(4);
System.out.println(book2);
sqlSession.commit();
sqlSession.close();
Opening JDBC Connection
INFO - {dataSource-1} inited
DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@69a10787]
DEBUG - ==> Preparing: SELECT * FROM book_tab WHERE book_id = ?
DEBUG - ==> Parameters: 4(Integer)
DEBUG - <== Total: 1
book==>Book(id=4, isbn=dyit-0004, title=java开发宝典4, author=who knows, cover=java.jpg, price=89.99, date=Sun Feb 13 00:00:00 CST 2022)
DEBUG - Opening JDBC Connection
DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@1a38c59b]
DEBUG - ==> Preparing: SELECT * FROM book_tab WHERE book_id = ?
DEBUG - ==> Parameters: 4(Integer)
DEBUG - <== Total: 1
Book(id=4, isbn=dyit-0004, title=java开发宝典4, author=who knows, cover=java.jpg, price=89.99, date=Sun Feb 13 00:00:00 CST 2022)
DEBUG - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@1a38c59b]
DEBUG - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@1a38c59b]
二级缓存，客户选择实现，默认关闭状态 SqlSessionFactory
Reader in = Resources.getResourceAsReader(“resources/config/mybatis-config.xml”);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
sqlSession = sqlSessionFactory.openSession();
IBookMapper mapper = sqlSession.getMapper(IBookMapper.class);
Book book = mapper.findById(4);
System.out.println(“book==>” + book);
xml: <cache />
sqlSession.commit();
//sqlSession.close();
sqlSession.clearCache();
sqlSession = sqlSessionFactory.openSession();
mapper = sqlSession.getMapper(IBookMapper.class);
Book book2 = mapper.findById(4);
System.out.println(book2);
sqlSession.commit();
sqlSession.close();
DEBUG - Checking to see if class com.dyit.entity.Book matches criteria [is assignable to Object]
DEBUG - Cache Hit Ratio [com.dyit.mapper.IBookMapper]: 0.0
DEBUG - Opening JDBC Connection
INFO - {dataSource-1} inited
DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5e25a92e]
DEBUG - ==> Preparing: SELECT * FROM book_tab WHERE book_id = ?
DEBUG - ==> Parameters: 4(Integer)
DEBUG - <== Total: 1
book==>Book(id=4, isbn=dyit-0004, title=java开发宝典4, author=who knows, cover=java.jpg, price=89.99, date=Sun Feb 13 00:00:00 CST 2022)
DEBUG - Cache Hit Ratio [com.dyit.mapper.IBookMapper]: 0.5
Book(id=4, isbn=dyit-0004, title=java开发宝典4, author=who knows, cover=java.jpg, price=89.99, date=Sun Feb 13 00:00:00 CST 2022)
扫一扫
1631
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
