[尚硅谷]_張曉飛_Mybatis

1、尚硅谷_張曉飛_MybatisMybatis1. Mybatis介紹MyBatis是支持普通SQL查詢，存儲過程和高級映射的優(yōu)秀持久層框架。MyBatis消除了幾乎所有的JDBC代碼和參數(shù)的手工設(shè)置以及對結(jié)果集的檢索封裝。MyBatis可以使用簡單的XML或注解用于配置和原始映射，將接口和Java的POJO（Plain Old Java Objects，普通的Java對象）映射成數(shù)據(jù)庫中的記錄.JDBC-dbutils(自動封裝)-MyBatis-Hibernatexml2. mybatis快速入門編寫第一個基于mybaits的測試?yán)?2.1. 添加jar包【mybatis】mybatis-

2、3.1.1.jar【MYSQL驅(qū)動包】mysql-connector-java-5.1.7-bin.jar2.2. 建庫+表create database mybatis;use mybatis;CREATE TABLE users(id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), age INT);INSERT INTO users(NAME, age) VALUES(Tom, 12);INSERT INTO users(NAME, age) VALUES(Jack, 11);2.3. 添加Mybatis的配置文件conf.xml2.4

3、. 定義表所對應(yīng)的實體類public class User private int id;private String name;private int age; /get,set方法2.5. 定義操作users表的sql映射文件userMapper.xml select * from users where id=#id2.6. 在conf.xml文件中注冊userMapper.xml文件2.7. 編寫測試代碼：執(zhí)行定義的select語句public class Test public static void main(String args) throws IOException Stri

4、ng resource = conf.xml; /加載mybatis的配置文件（它也加載關(guān)聯(lián)的映射文件）Reader reader = Resources.getResourceAsReader(resource); /構(gòu)建sqlSession的工廠SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);/創(chuàng)建能執(zhí)行映射文件中sql的sqlSessionSqlSession session = sessionFactory.openSession();/映射sql的標(biāo)識字符串String s

5、tatement = com.atguigu.mybatis.bean.userMapper+.selectUser;/執(zhí)行查詢返回一個唯一user對象的sqlUser user = session.selectOne(statement, 1);System.out.println(user);3. 操作users表的CRUD3.1. XML的實現(xiàn)1). 定義sql映射xml文件：insert into users(name, age) values(#name, #age);delete from users where id=#idupdate users set name=#name,

6、age=#age where id=#idselect * from users where id=#idselect * from users2). 在config.xml中注冊這個映射文件3). 在dao中調(diào)用：public User getUserById(int id) SqlSession session = sessionFactory.openSession();User user = session.selectOne(URI+.selectUser, id);return user;3.2. 注解的實現(xiàn)1). 定義sql映射的接口public interface UserMa

7、pper Insert(insert into users(name, age) values(#name, #age)public int insertUser(User user);Delete(delete from users where id=#id)public int deleteUserById(int id);Update(update users set name=#name,age=#age where id=#id)public int updateUser(User user);Select(select * from users where id=#id)publi

8、c User getUserById(int id);Select(select * from users)public List getAllUser();2). 在config中注冊這個映射接口3). 在dao類中調(diào)用public User getUserById(int id) SqlSession session = sessionFactory.openSession();UserMapper mapper = session.getMapper(UserMapper.class);User user = mapper.getUserById(id);return user;4. 幾

9、個可以優(yōu)化的地方4.1. 連接數(shù)據(jù)庫的配置單獨放在一個properties文件中# perties4.2. 為實體類定義別名,簡化sql映射xml文件中的引用4.3. 可以在src下加入log4j的配置文件,打印日志信息1. 添加jar: log4j-1.2.16.jar2.1. perties(方式一)perties，log4j.rootLogger=DEBUG, Console#Consolelog4j.appender.Console=org.apache.log4j.ConsoleAppenderlog4j.appender.Consol

10、e.layout=org.apache.log4j.PatternLayoutlog4j.appender.Console.layout.ConversionPattern=%d %t %-5p %c - %m%nlog4j.logger.java.sql.ResultSet=INFO.apache=INFOlog4j.logger.java.sql.Connection=DEBUGlog4j.logger.java.sql.Statement=DEBUGlog4j.logger.java.sql.PreparedStatement=DEBUG2.2. log4

11、j.xml(方式二)5. 解決字段名與實體類屬性名不相同的沖突5.1. 準(zhǔn)備表和數(shù)據(jù)：CREATE TABLE orders(order_id INT PRIMARY KEY AUTO_INCREMENT,order_no VARCHAR(20), order_price FLOAT);INSERT INTO orders(order_no, order_price) VALUES(aaaa, 23);INSERT INTO orders(order_no, order_price) VALUES(bbbb, 33);INSERT INTO orders(order_no, order_pri

12、ce) VALUES(cccc, 22);5.2. 定義實體類：public class Order private int id;private String orderNo;private float price;5.3. 實現(xiàn)getOrderById(id)的查詢：方式一: 通過在sql語句中定義別名select order_id id, order_no orderNo,order_price price from orders where order_id=#id方式二: 通過select * from orders where order_id=#id6.實現(xiàn)關(guān)聯(lián)表查詢6.1. 一

13、對一關(guān)聯(lián)1). 提出需求根據(jù)班級id查詢班級信息(帶老師的信息)2). 創(chuàng)建表和數(shù)據(jù)CREATE TABLE teacher(t_id INT PRIMARY KEY AUTO_INCREMENT, t_name VARCHAR(20);CREATE TABLE class(c_id INT PRIMARY KEY AUTO_INCREMENT, c_name VARCHAR(20), teacher_id INT);ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teache

14、r(t_id);INSERT INTO teacher(t_name) VALUES(LS1);INSERT INTO teacher(t_name) VALUES(LS2);INSERT INTO class(c_name, teacher_id) VALUES(bj_a, 1);INSERT INTO class(c_name, teacher_id) VALUES(bj_b, 2);3). 定義實體類：public class Teacher private int id;private String name;public class Classes private int id;pr

15、ivate String name;private Teacher teacher;4). 定義sql映射文件ClassMapper.xmlselect * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#id select * from class where c_id=#id SELECT t_id id, t_name name FROM teacher WHERE t_id=#id 5). 測試Testpublic void testOO() SqlSession sqlSession = factory.op

16、enSession();Classes c = sqlSession.selectOne(com.atguigu.day03_mybatis.test5.OOMapper.getClass, 1);System.out.println(c);Testpublic void testOO2() SqlSession sqlSession = factory.openSession();Classes c = sqlSession.selectOne(com.atguigu.day03_mybatis.test5.OOMapper.getClass2, 1);System.out.println(

17、c);6.2. 一對多關(guān)聯(lián)1). 提出需求根據(jù)classId查詢對應(yīng)的班級信息,包括學(xué)生,老師2). 創(chuàng)建表和數(shù)據(jù)：CREATE TABLE student(s_id INT PRIMARY KEY AUTO_INCREMENT, s_name VARCHAR(20), class_id INT);INSERT INTO student(s_name, class_id) VALUES(xs_A, 1);INSERT INTO student(s_name, class_id) VALUES(xs_B, 1);INSERT INTO student(s_name, class_id) VALU

18、ES(xs_C, 1);INSERT INTO student(s_name, class_id) VALUES(xs_D, 2);INSERT INTO student(s_name, class_id) VALUES(xs_E, 2);INSERT INTO student(s_name, class_id) VALUES(xs_F, 2);3). 定義實體類：public class Student private int id;private String name;public class Classes private int id;private String name;priv

19、ate Teacher teacher;private List students;4). 定義sql映射文件ClassMapper.xmlselect * from class c, teacher t,student s where c.teacher_id=t.t_id and c.C_id=s.class_id and c.c_id=#id select * from class where c_id=#id SELECT t_id id, t_name name FROM teacher WHERE t_id=#id SELECT s_id id, s_name name FROM

20、student WHERE class_id=#id 5). 測試：Testpublic void testOM() SqlSession sqlSession = factory.openSession();Classes c = sqlSession.selectOne(com.atguigu.day03_mybatis.test5.OOMapper.getClass3, 1);System.out.println(c);Testpublic void testOM2() SqlSession sqlSession = factory.openSession();Classes c = s

21、qlSession.selectOne(com.atguigu.day03_mybatis.test5.OOMapper.getClass4, 1);System.out.println(c);7. 動態(tài)SQL與模糊查詢7.1. 提出需求: 實現(xiàn)多條件查詢用戶(姓名模糊匹配, 年齡在指定的最小值到最大值之間)7.2. 準(zhǔn)備數(shù)據(jù)表和數(shù)據(jù):create table d_user( id int primary key auto_increment, name varchar(10),age int(3); insert into d_user(name,age) values(Tom,12); i

22、nsert into d_user(name,age) values(Bob,13); insert into d_user(name,age) values(Jack,18);7.3. ConditionUser(查詢條件實體類)private String name;private int minAge;private int maxAge;7.4. User(表實體類)private int id;private String name;private int age;7.5. userMapper.xml(映射文件) select * from d_user where age=#mi

23、nAge and age<=#maxAgeand name like #name7.6. UserTest(測試)public class UserTest public static void main(String args) throws IOException Reader reader = Resources.getResourceAsReader(conf.xml);SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);SqlSession sqlSession = se

24、ssionFactory.openSession();String statement = com.atguigu.day03_mybatis.test6.userMapper.getUser;List list = sqlSession.selectList(statement, new ConditionUser(%a%, 1, 12);System.out.println(list);MyBatis中可用的動態(tài)SQL標(biāo)簽8.調(diào)用存儲過程8.1. 提出需求:查詢得到男性或女性的數(shù)量, 如果傳入的是0就女性否則是男性8.2. 準(zhǔn)備數(shù)據(jù)庫表和存儲過程:create table p_user(

25、id int primary key auto_increment, name varchar(10),sex char(2); insert into p_user(name,sex) values(A,男); insert into p_user(name,sex) values(B,女); insert into p_user(name,sex) values(C,男); #創(chuàng)建存儲過程(查詢得到男性或女性的數(shù)量, 如果傳入的是0就女性否則是男性)DELIMITER $CREATE PROCEDURE mybatis.ges_user_count(IN sex_id INT, OUT u

26、ser_count INT)BEGIN IF sex_id=0 THENSELECT COUNT(*) FROM mybatis.p_user WHERE p_user.sex=女 INTO user_count;ELSESELECT COUNT(*) FROM mybatis.p_user WHERE p_user.sex=男 INTO user_count;END IF;END $#調(diào)用存儲過程DELIMITER ;SET user_count = 0;CALL mybatis.ges_user_count(1, user_count);SELECT user_count;8.3. 創(chuàng)建表

27、的實體類public class User private String id;private String name;private String sex;8.4. userMapper.xml call mybatis.get_user_count(?,?) 8.5. 測試調(diào)用:Map paramMap = new HashMap();paramMap.put(sex_id, 0);session.selectOne(statement, paramMap);Integer userCount = paramMap.get(user_count);System.out.println(us

28、erCount);9. Mybatis緩存9.1. 理解MyBatis緩存正如大多數(shù)持久層框架一樣，MyBatis 同樣提供了一級緩存和二級緩存的支持1. 一級緩存: 基于PerpetualCache 的 HashMap本地緩存，其存儲作用域為 Session，當(dāng) Session flush 或 close 之后，該Session中的所有 Cache 就將清空。2. 二級緩存與一級緩存其機(jī)制相同，默認(rèn)也是采用 PerpetualCache，HashMap存儲，不同在于其存儲作用域為 Mapper(Namespace)，并且可自定義存儲源，如 Ehcache。3. 對于緩存數(shù)據(jù)更新機(jī)制，當(dāng)某一個

29、作用域(一級緩存Session/二級緩存Namespaces)的進(jìn)行了 C/U/D 操作后，默認(rèn)該作用域下所有 select 中的緩存將被clear。9.2. Mybatis一級緩存1) 提出需求:根據(jù)id查詢對應(yīng)的用戶記錄對象2). 準(zhǔn)備數(shù)據(jù)庫表和數(shù)據(jù)CREATE TABLE c_user(id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), age INT);INSERT INTO c_user(NAME, age) VALUES(Tom, 12);INSERT INTO c_user(NAME, age) VALUES(Jack, 11

30、);3). 創(chuàng)建表的實體類public class User implements Serializableprivate int id;private String name;private int age;4). userMapper.xmlselect * from c_user where id=#idupdate c_user setname=#name, age=#age where id=#id5). 測試:/* * 一級緩存: 也就Session級的緩存(默認(rèn)開啟) */Testpublic void testCache1() SqlSession session = Myba

31、tisUtils.getSession();String statement = com.atguigu.mybatis.test8.userMapper.getUser;User user = session.selectOne(statement, 1);System.out.println(user);/* * 一級緩存默認(rèn)就會被使用 */*user = session.selectOne(statement, 1);System.out.println(user);*/* 1. 必須是同一個Session,如果session對象已經(jīng)close()過了就不可能用了 */*session

32、= MybatisUtils.getSession();user = session.selectOne(statement, 1);System.out.println(user);*/* 2. 查詢條件是一樣的 */*user = session.selectOne(statement, 2);System.out.println(user);*/* 3. 沒有執(zhí)行過session.clearCache()清理緩存 */*session.clearCache(); user = session.selectOne(statement, 2);System.out.println(user)

33、;*/* 4. 沒有執(zhí)行過增刪改的操作(這些操作都會清理緩存) */*session.update(com.atguigu.mybatis.test8.userMapper.updateUser,new User(2, user, 23);user = session.selectOne(statement, 2);System.out.println(user);*/9.3. Mybatis二級緩存1). 添加一個在userMapper.xml中2). 測試/* * 測試二級緩存 */Testpublic void testCache2() String statement = com.atguigu.mybatis.test8.userMapper.getUser;SqlSession session = MybatisUtils.getSession();User user = session.selectOne(statement, 1);mit();System.out.println(user=+user);SqlSession session2 = MybatisUtils.getSession();user = session2.selectOne(statement, 1);mit