Mybatis study notes, mybatis

Mybatis study notes, mybatis

I. The database Field Names and object class names are different

1.1 prepare data tables and data

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_price) VALUES('cccc', 22);

1.2 define object classes

public class Order {private int id;private String orderNo;private float price;}

1.3 Data Query implementation

Method 1: Define the alias <select id = "selectOrder" parameterType = "int" resultType = "_ Order"> select order_id id, order_no orderNo, order_price price from orders where order_id =#{ id} </select> Method 2: use <resultMap> <select id = "selectOrderResultMap" parameterType = "int" resultMap = "orderResultMap"> select * from orders where order_id =#{ id} </select> <resultMap type = "_ Order" id = "orderResultMap"> <id property = "id" column = "order_id"/> <result property = "orderNo" column = "order_no"/> <result property = "price" column = "order_price"/> </resultMap>

2. One-to-one join Table query

2.1 create tables and data

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 teacher(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);

Requirement: query the class information based on the class id (with the Teacher's Information)

2.2 define object classes

public class Teacher {private int id;private String name;}

public class Classes {private int id;private String name;private Teacher teacher;}

2.3 define the SQL ing file ClassMapper. xml

<! -- Method 1: Nested results: Use nested result ing to process the subset of duplicate Union results to encapsulate the data queried in the join table (remove duplicate data) select * from class c, teacher t where c. teacher_id = t. t_id and c. c_id = 1 --> <select id = "getClass" parameterType = "int" resultMap = "ClassResultMap"> select * from class c, teacher t where c. teacher_id = t. t_id and c. c_id = # {id} </select> <resultMap type = "_ Classes" id = "ClassResultMap"> <id property = "id" column = "c_id"/> <result property = "name" column = "c_name"/> <Association property = "teacher" javaType = "_ Teacher"> <id property = "id" column = "t_id"/> <result property = "name" column = "t_name "/> </association> </resultMap> <! -- Method 2: Nested query: execute another SQL ing statement to return the expected complex type SELECT * FROM class WHERE c_id = 1; SELECT * FROM teacher WHERE t_id = 1 // 1 is the value of teacher_id obtained in the previous query --> <select id = "getClass2" parameterType = "int" resultMap = "ClassResultMap2"> select * from class where c_id = # {id} </select> <resultMap type = "_ Classes" id = "ClassResultMap2"> <id property = "id" column = "c_id "/> <result property =" name "column =" c_name "/> <association property =" teacher "column =" teacher_id "select =" getTeacher "> </association> </resultMap> <select id = "getTeacher" parameterType = "int" resultType = "_ Teacher"> SELECT t_id, t_name name FROM teacher WHERE t_id =#{ id} </select>

3. One-to-Multiple Association queries

3.1 Add a student table

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) VALUES('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);

As a Student table is added, create a student object class and modify the Classes class. The class and Student are one-to-many relationships.

public class Student {private int id;private String name;}public class Classes {private int id;private String name;private Teacher teacher;private List<Student> students;}

3.2 define a ing File

<! -- Method 1: Nested results: Use nested result ing to process the subset of repeated Union results SELECT * 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 = 1 --> <select id = "getClass3" parameterType = "int" resultMap = "ClassResultMap3"> select * 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> <resultMap type = "_ Classes" id = "ClassResultMap3"> <id property = "id" column = "C_id"/> <result property = "name" column = "c_name"/> <association property = "teacher" column = "teacher_id" javaType = "_ Teacher"> <id property = "id" column = "t_id"/> <result property = "name" column = "t_name"/> </association> <! -- OfType specifies the object type in the students set --> <collection property = "students" ofType = "_ Student"> <id property = "id" column = "s_id"/> <result property = "name" column = "s_name"/> </collection> </resultMap> <! -- Method 2: Nested query: execute another SQL ing statement to return the expected complex type SELECT * FROM class WHERE c_id = 1; SELECT * FROM teacher WHERE t_id = 1 // 1 is the value of teacher_id obtained from the previous query select * FROM student WHERE class_id = 1 // 1 is the c_id field obtained FROM the first query value --> <select id = "getClass4" parameterType = "int" resultMap = "ClassResultMap4"> select * from class where c_id =#{ id} </select> <resultMap type = "_ Classes" id = "ClassResultMap4"> <id property = "id" column = "c_id"/> <result property = "name" column = "c_name"/> <association property = "teacher" column = "teacher_id" javaType = "_ Teacher" select = "getteacher"> </association> <collection property = "students" ofType = "_ Student" column = "c_id" select = "getStudent"> </collection> </resultMap> <select id = "getteacher" parameterType = "int" resultType = "_ Teacher"> SELECT t_id, t_name name FROM teacher WHERE t_id = # {id} </select> <select id = "getStudent" parameterType = "int" resultType = "_ Student"> SELECT s_id, s_name name FROM student WHERE class_id =#{ id} </select>

4. dynamic SQL and fuzzy query

4.1 prepare a data table

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);  insert into d_user(name,age) values('Bob',13);  insert into d_user(name,age) values('Jack',18);

Requirement: Implement Multi-condition query of users (name fuzzy match, age between the specified minimum and maximum)

4.2 query condition entity class ConditionUser

private String name;private int minAge;private int maxAge;

4.3 table entity class User

private int id;private String name;private int age;

4.4 SQL ing file userMapper. 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.atguigu.day03_mybatis.test6.userMapper"><select id="getUser" parameterType="com.atguigu.day03_mybatis.test6.ConditionUser" resultType="com.atguigu.day03_mybatis.test6.User">select * from d_user where age>=#{minAge} and age<=#{maxAge}<if test='name!="%null%"'>and name like #{name}</if></select></mapper>

4.5 Test

String statement = "com.atguigu.day03_mybatis.test6.userMapper.getUser";List<User> list = sqlSession.selectList(statement, new ConditionUser("%a%", 1, 12));System.out.println(list);

Dynamic SQL labels available in Mybatis

V. Mybatis Cache

Like most persistence layer frameworks, MyBatis also provides support for level 1 cache and level 2 cache.
1. level-1 Cache: The HashMap local Cache based on PerpetualCache. Its Storage scope is Session. After the Session is flushed or closed, all the caches in the Session will be cleared.
2. The second-level cache mechanism is the same as that of the first-level cache. PerpetualCache and HashMap are used by default. The difference is that their storage scope is Mapper (Namespace) and they can customize storage sources, such as Ehcache.
3. for the cache data update mechanism, after a certain scope (level-1 cache Session/level-2 Cache Namespaces) performs the C/U/D operation, by default, the cache in all select statements in this scope will be clear.

5.1 Level 1 Cache

Level 1 cache: Session-level cache (enabled by default)

A. Level 1 cache: Session-level cache (enabled by default)

B. the query conditions are the same.

C. You have not executed session. Sort AchE () to clear the cache or session. close ()

D. No add, delete, modify, or delete operations have been performed (these operations will clear the cache)

5.2 second-level cache

You only need to add a tag to the userMapper. xml file to enable Level 2 Cache:

<Cache/> <! -Map File-level cache -->

5.3 additional instructions

A. All select statements in the ing statement file will be cached.
B. All insert, update, and delete statements in the ing statement file refresh the cache.
C. The cache uses the Least Recently Used (LRU, Least Recently Used) algorithm to reclaim it.
D. The cache will be refreshed Based on the specified time interval.
E. The cache stores 1024 objects.

<Cache eviction = "FIFO" // The reclaim policy is first-in-first-out flushInterval = "60000" // The automatic refresh time is 60 ssize = "512" // a maximum of 512 referenced objects can be cached. readOnly = "true"/> // read-only