mybatis测试

mybatis小结

1.mybatis测试

1.1读取mybatis配置文件1.2 创建SqlSessionFactory工厂1.3 开启一次sql会话1.4 创建数据库操作接口对象1.5 接口操作1.6 事务提交，关闭会话 2.mybatis细节

2.1 log4j日志配置2.2 参数问题2.3 mybatis查询

2.3.1单表查询2.3.2类的别名2.3.3 多表查询

2.3.3.1 多对一2.3.3.2 一对多 2.4 $和#的区别2.5缓存问题

2.5.1 mybatis运行原理2.5.2 运行时的缓存

1.mybatis测试 1.1读取mybatis配置文件

Reader in = Resources.getResourceAsReader("resources/config/mybatis- config.xml");

1.2 创建SqlSessionFactory工厂

管理多个数据库操作会话

SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);

1.3 开启一次sql会话

SqlSession sqlSession = sqlSessionFactory.openSession();

1.4 创建数据库操作接口对象

IBookMapper mapper = sqlSession.getMapper(IBookMapper.class)

1.5 接口操作

调用接口的方法

1.6 事务提交，关闭会话

sqlSession.commit();
sqlSession.close();

2.mybatis细节 2.1 log4j日志配置

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

2.2 参数问题

delete(int id);
book_id = #{id}
delete(Book book);
book_id = #{id}------>book.getId();

单参数（非对象）：和参数名称一致
单参数（对象类型）：和参数名称一致（对象调用get方法）
多个参数：void updateAuthorOrPrice(int id, double sprice, double sauthor); 要配合@Param解决这类问题

void updateAuthorOrPrice(@Param("sid")int id, @Param("sprice")double price, @Param("sauthor")String author);
------------------------------------------------------------
 
UPDATE book_tab SET book_price= #{sprice},book_author=#{sauthor} WHERe book_id=#{sid}

2.3 mybatis查询 2.3.1单表查询

1.别名法
别名 book_id AS id,book_title AS title
2.表中字段和属性一致
3.定义一个映射关系（表中字段和类中的属性），resultMap

我们主要分析第三种映射法

 
   
  
   
    
    
    
 



--------------------------------------------------------

  
SELECt * FROM book_tab 

 
2.3.2类的别名 

 
 
 
实体类
 
@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; 
}
 
在xml中的使用，单表查询的映射法
 

 
   
  
   
    
    
    
 
 
2.3.3 多表查询 

 
 
2.3.3.1 多对一 
类的设计
 
@Alias("Publisher")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Publisher {
	
	private Integer id;
	private String name;
	private String loc;

}

@Alias("Book")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Book implements Serializable{
	private Integer id;
	private String isbn;
	private String title;
	private String author;
	private String cover;
	private Double price;
	private Date date;
	private Publisher publisher;
}
 
xml文件配置
 
	
		
		
		
		
		
		
		
		
	
 
	
		
		
		
		
		
	
 
2.3.3.2 一对多 
类的设计
 
@Alias("Publisher")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Publisher {
	
	private Integer id;
	private String name;
	private String loc;
	List books;

}

@Alias("Book")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Book implements Serializable{
	private Integer id;
	private String isbn;
	private String title;
	private String author;
	private String cover;
	private Double price;
	private Date date;

}
 
xml文件的配置
 
	
		
		
		
		
		
	
 
	
		
		
		
		
		
		
		
		
	
 
2.4 $和#的区别 
#
$
PreparedStatment
Statement
?
拼接
解决了sql注入漏洞
SQL注入漏洞（OR 1=‘1’）
#{title}–>book.getTitle()
ORDER BY ${price}—>ORDER BY book_price
 
 
$的注入
 
 

 SELECT * FROM book_tab ORDER BY ${price} 
 

测试：SELECT * FROM book_tab ORDER BY book_price DESC

IBookMapper mapper = sqlSession.getMapper(IBookMapper.class); 
List list = mapper.findBooksByPriceOrder("book_price DESC"); 
list.forEach(item->System.out.println(item));

2.5缓存问题 2.5.1 mybatis运行原理

当一次事务开启时，我们可以从日志中读取到下面信息： mybatis内置有一个缓存，SqlSession对象->一级缓存（运行速度快，空间小），是内置的。而且一级缓存不能关闭。

 #打开jdbc连接，从数据源中取得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]

2.5.2 运行时的缓存

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的一级缓存


	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]

关闭SqlSession开启一个新的事务，缓存也将清除。

二级缓存，客户选择实现，默认关闭状态 SqlSessionFactory。

xml: 	

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)