1, Construction of complex query environment
mybatis-config.xml file configuration
<?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>
<settings>
<!--<setting name="logImpl" value="STDOUT_LOGGING"/>--><!--Note that there must be no spaces-->
<setting name="logImpl" value="LOG4J"/>
</settings>
<typeAliases>
<package name="com.kuang.pojo"/>
</typeAliases>
<environments default="test"> <!--default The value is written in the following environment id Which one,The corresponding environment is used-->
<environment id="test">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper class="com.kuang.dao.*Mapper"></mapper>
</mappers>
</configuration>
2, Many to one (check all students and teachers)
- Joint table query method
<mapper namespace="com.kuang.dao.StudentMapper">
<resultMap id="stuMap" type="student" >
<result property="id" column="id"/>
<result property="name" column="name"/>
<result property="tid" column="tid"/>
<association property="teacher" javaType="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
</association>
</resultMap>
<select id="getStuList" resultMap="stuMap">
SELECT s.id id,s.name name,t.id tid,t.name tname FROM Student s,teacher t WHERE s.tid = t.id
</select>
-
Nested sub table query method
<select id="getStu" resultMap="stuTeach"> select * from student </select> <resultMap id="stuTeach" type="Student" > <!--id The label is used to indicate the primary key. It is available result Label substitution Used when the parameter in the subquery is an object association，Used when the parameter is a collection collection--> <result column="id" property="id"/> <result column="name" property="name"/> <association property="teacher" column="tid" javaType="Teacher" select="getTea"/> <!--column="tid"Participate in the next level query as a parameter,--> </resultMap> <select id="getTea" resultType="Teacher"> select * from teacher where id=#{tid} </select>
3, One to many
1. Nested query by result
<resultMap id="teaMap" type="teacher">
<result property="id" column="id"/>
<result property="name" column="name"/>
<!-- The entity class attribute is list When collecting collection Label, and Java Type Type to use ofType，ofType To receive generic information from the collection
javaType Is used to specify the property type
Used when the entity class attribute is an object association label-->
<collection property="students" ofType="student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="stid"/>
</collection>
</resultMap>
<!-- Nested query by result-->
<select id="getTeach" resultMap="teaMap">
SELECT s.id sid,s.name sname,t.id id ,t.name NAME,s.tid stid
FROM teacher t,student s WHERE t.id=s.tid
</select>
2. Sub query
<select id="getTeach1" resultMap="getStuMap">
select * from teacher
</select>
<!-- type Entity type of corresponding result set teacher Fields in the class can be omitted without writing-->
<resultMap id="getStuMap" type="Teacher">
<collection property="students" select="getStu" column="id" >
<result property="id" column="id"/>
<result property="name" column="name"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
<!-- #{aid} corresponds to the column field in the collection, and the value in it can be different from aid -- >
<select id="getStu" resultType="Student">
select * from student where tid=#{aid}
</select>
Summary
1. Association – association is used for many to one entity class
2. Collection – collection is used when the one to many entity class attribute is a list collection
3.javaType is used to specify the type of attribute in the entity class
4.ofType is used to specify pojo types mapped to list s or collections, and constraint types in generic types
4, Query with parameters
List<Teacher> getTeaById(int id);
<resultMap id="teaMap" type="teacher">
<result property="id" column="id"/>
<result property="name" column="name"/>
<!-- The entity class attribute is list When collecting collection Label, and Java Type Type to use ofType，ofType To receive generic information from the collection
javaType Is used to specify the property type
Used when the entity class attribute is an object association label-->
<collection property="students" ofType="student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="stid"/>
</collection>
</resultMap>
<!-- Nested query by result-->
<select id="getTeach" resultMap="teaMap">
SELECT s.id sid,s.name sname,t.id id ,t.name NAME,s.tid stid
FROM teacher t,student s WHERE t.id=s.tid and t.id=#{id}
</select>
Or this way
<!-- Subquery-->
<select id="getTeaById1" resultMap="getStuMap1">
select * from teacher where id=#{id} <!--#{id} corresponds to the parameter passed in the interface method -- >
</select>
<!-- type Entity type of corresponding result set teacher Fields in the class can be omitted without writing-->
<resultMap id="getStuMap1" type="Teacher">
<!-- collection in property Corresponding to the entity class attribute created in the entity class, column Corresponding entity class attribute, i.e. foreign key-->
<collection property="students" select="getStu1" column="id" >
<result property="id" column="id"/>
<result property="name" column="name"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
<!-- #{aid} corresponds to the column field in the collection, and the value in it can be different from aid -- >
<select id="getStu1" resultType="Student">
select * from student where tid=#{aid} <!--#{aid} corresponds to the column value in the collection -- >
</select>
5, Dynamic sql:if/choose(when,otherwise)/trim(where,set)/foreach
1. Write the expression of the parameter in the if statement test
<select id="getBlogIf" parameterType="blog" resultType="blog"> select * from blog where 1=1 <if test="title != null"> and title like #{title} </if> <if test="author != null"> or author like #{author} </if></select>
@Testpublic void getBlogIfTest(){ SqlSession sqlSession = MyUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); Blog blog = new Blog(); blog.setAuthor("%za%"); blog.setTitle("%sdf%"); List<Blog> blogIf = mapper.getBlogIf(blog); for (Blog blog1 : blogIf) { System.out.println(blog1); } sqlSession.close();}
The generation method of UUID will not be repeated!
public static String getId(){ return UUID.randomUUID().toString().replaceAll("-","");}
2.choose(when,otherwise) statement
@Testpublic void getChooseTest(){ SqlSession sqlSession = MyUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); Blog blog = new Blog(); // blog.setAuthor("%za%"); blog.setTitle("%sdf%"); List<Blog> blogIf = mapper.getChoose(blog); for (Blog blog1 : blogIf) { System.out.println(blog1); } sqlSession.close();}
<select id="getChoose" resultType="blog" parameterType="blog"> select * from blog where 1=1 <choose> <when test="author !=null"> and author like #{author} </when> <when test="author !=null and title !=null"> and title like #{title} </when> <otherwise> and views=3 </otherwise> </choose> </select>
3.trim(where,set) statement
@Testpublic void updateBlogTest(){ SqlSession sqlSession = MyUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); Blog blog = new Blog(); blog.setAuthor("lisi"); blog.setTitle("nisagdslg"); blog.setId("21d20e21bc724363974888ce1fbabcf9"); int blogIf = mapper.updateBlog(blog); if (blogIf>0){ System.out.println("Update succeeded!"); } sqlSession.commit(); sqlSession.close();}
<update id="updateBlog" parameterType="blog" > update blog <set> <if test="title !=null"> title = #{title}, </if> <if test="author !=null"> author = #{author}, </if> </set> where id=#{id};</update>
The set tag can automatically filter over written "," and,
Or it can be implemented with sql fragments
<sql id="sql_title_author" > <if test="title !=null"> title = #{title}, </if> <if test="author !=null"> author = #{author}, </if></sql><update id="updateBlog1" parameterType="blog"> update blog <set> <include refid="sql_title_author"></include> </set> where id=#{id};</update>
4.foreach statement
<!-- select * from blog where id=1 or id=2 or id=3; --> <select id="getForEach" parameterType="map" resultType="blog"> select * from blog <where> <foreach collection="ids" item="id" open="(" close=")" separator="or"> id=#{id} </foreach> </where> </select>
@Testpublic void getForEachTest(){ SqlSession sqlSession = MyUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); ArrayList<Integer> ids = new ArrayList<>(); ids.add(1); ids.add(2); Map map = new HashMap(); map.put("ids",ids); List<Blog> forEach = mapper.getForEach(map); for (Blog each : forEach) { System.out.println(each); } sqlSession.close();}
6, Cache
mybatis defines two levels of cache by default: the first level cache sqlSession and the second level cache
By default, only the first level cache is enabled, that is, the sqlSession level cache, also known as the local cache;
The L2 cache needs to be manually enabled and configured. It is based on the namespace level cache
1. L1 cache
It is enabled by default. It is only valid in one sqlsession. When the sqlsession is closed, it disappears, that is, get the connection to the connection and close the interval!
-
The test queries the same record twice in a session
@Test public void getBlogById(){ SqlSession sqlSession = MyUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); Blog blogById = mapper.getBlogById("1"); System.out.println(blogById); Blog blogById1 = mapper.getBlogById("1"); System.out.println(blogById1); sqlSession.close(); }
[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-xo4gdea6-1628298109933) (C: \ users \ 56504 \ appdata \ roaming \ typora user images \ image-20210806171306636. PNG)]
-
Cache invalidation
- Query different things
- Adding, deleting and modifying may change the original data, so the cache must be refreshed
- Query different mapper XML time
- Manual cache cleanup
[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-niao8524-1628298109937) (C: \ users \ 56504 \ appdata \ roaming \ typora \ typora user images \ image-20210806172027295. PNG)]
@Testpublic void getBlogById(){ SqlSession sqlSession = MyUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); Blog blogById = mapper.getBlogById("1"); //Add records and change the original data, so the cache mapper will be refreshed addBlog(new Blog(MyUtils.getId(), "asdsfs", "zhangsa", new Date(), 3)); sqlSession. commit(); Blog blogById1 = mapper. getBlogById("1"); sqlSession. close();} Three methods, three queries,
@Testpublic void getBlogById(){ SqlSession sqlSession = MyUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); Blog blogById = mapper.getBlogById("1"); sqlSession.clearCache();//Manually clean up the cache, and the cache will become invalid. Blog blogbyid1 = mapper getBlogById("1"); sqlSession. close();}
2. L2 cache
-
It is also a global cache, which makes up for the problem that the scope of the L1 cache is too low
-
Cache based on namespace level, that is, a namespace corresponds to a L2 cache
-
Working mechanism:
- A session queries a piece of data, which is placed in the current L1 cache;
- If the current session is closed, the corresponding L1 cache will disappear. At this time, we need to save the data in the L1 cache to the L2 cache
- The new session query information is obtained from the L2 cache
- The data queried by different mapper s are placed in their corresponding caches
-
To enable L2 cache
-
Open the global cache in mybatis config Configuration in XML
<settings><!-- Displayed open cache (L2 cache)--> <setting name="cacheEnabled" value="true"/> </settings>
-
[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-cxuwmpwq-1628298109942) (C: \ users \ 56504 \ appdata \ roaming \ typora user images \ image-20210806175749286. PNG)]
-
Turn it on in the mapper where you want to use L2 cache
Without configuration parameters<cache/>
Or how to customize parameters
<!-- FIFO, refresh from time to time=60s, Save 1024 references to a list or object (default), read-only--> <cache eviction="FIFO" flushInterval="60000" size="1024" readOnly="true"/>
After using L2 cache, the improved results are shown in the figure above
[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-kmigmo6u-1628298109944) (C: \ users \ 56504 \ appdata \ roaming \ typora user images \ image-20210806180853547. PNG)]
summary
1. As long as the L2 cache is enabled, in the same mapper It will be effective next time 2. All data will be put into the L1 cache first 3. Only when the session is committed or closed will it be committed to the L2 cache
***Cache query order:***
1. First check whether the L2 cache is available;
2. Check whether the L1 cache is available;
3. Finally, query the database