preface:
Hello, guys, I'm running snail rz. Of course, you can call me snail Jun. I'm a rookie who has studied Java for more than half a year. At the same time, I also have a great dream, that is, to become an excellent Java Architect one day.
This Mybatis basic learning series is used to record the whole process of learning the basic knowledge of Mybatis framework (this series is written with reference to the latest Mybatis tutorial of crazy God in station B. because it was sorted out before, but it was not released at that time, there may be errors in some places. I hope you can correct them in time!)
After that, I will try my best to update this series at the rate of two days and one hour. For those who haven't learned the Mybatis3 framework, please refer to my blog; Of course, the little friends who have studied can also review the basics with me. Finally, I hope I can make progress with you! Come on! Boys!
Special note: if you are interested in Mybatis basic learning series, you can read the previous blogs of this series:
Part I: First knowledge of Mybatis basic learning
Part II: The first Mybatis program for basic learning of Mybatis
Part III: Addition, deletion and modification of CRUD in Mybatis basic learning
Part IV: Mybatis basic learning omnipotent Map and fuzzy query
Part V: Configuration analysis of Mybatis basic learning (Part I)
Part VI: Configuration analysis of Mybatis basic learning (Part 2)
Part VII: Mybatis basic learning uses ResultMap to solve the inconsistency of field names
Part VIII: Simple use of Mybatis basic learning log factory
Part IX: Simple use of data paging in Mybatis basic learning
Part 10: Development of usage annotation for Mybatis basic learning
Part XI: Simple use of Lombok in Mybatis basic learning
Part XII: Many to one relationship processing in Mybatis basic learning
Part 13: Mybatis basic learning one to many relationship processing
Today we come to the 13th stop of Mybatis basic learning: simple use of dynamic SQL. No more nonsense. Let's start today's study.
13. Simple use of dynamic SQL
13.1 basic knowledge of dynamic SQL
13.1.1 what is dynamic SQL?
Dynamic SQL is to generate different SQL statements according to different conditions
13.1.2 benefits of dynamic SQL
Using dynamic SQL, we can completely get rid of the problems such as forgetting to add the necessary spaces and removing the comma of the last column name in the list.
13.1.2 official interpretation of dynamic SQL
- Dynamic SQL elements are similar to JSTL or any XML like text processor. In versions before MyBatis, many elements need to take time to understand
- MyBatis3 has greatly reduced the types of elements. Now you only need to learn half of the original elements
- MyBatis uses powerful OGNL based expressions to eliminate most other elements
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
13.2 setting up environment and inserting information
13.2.1 writing SQL statements
Create table `blog`(
`id` varchar(50) not null comment 'Blog id',
`title` varchar(100) not null comment 'Blog title',
`author` varchar(30) not null comment 'Blogger',
`create_time` datetime not null comment 'Creation time',
`views` int(30) not null comment 'Views'
)engine=InnoDB default charset=utf8;
13.2.2 creating foundation works
1. Import resource dependent jar package
- After creating the Maven project, import the corresponding resource dependencies in the pom.xml configuration file
<dependencies>
<!-- mysql Drive resource dependency -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<!-- mybatis resources dependence -->
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
<!-- junit Unit test resource dependency -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<!-- log4j resources dependence -->
<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<!-- Lombok resources dependence -->
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.10</version>
</dependency>
</dependencies>
<!-- stay build Medium configuration resources，To prevent the failure of resource export -->
<build>
<resources>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
</build>
2. Prepare core configuration file
- First, create the db.properties configuration file and mybatis-config.xml configuration file in the resources file directory
2-1 write db.properties configuration file
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF-8 username=root pwd=123456
2-2 write the mybatis-config.xml configuration file
<?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: Core configuration -->
<configuration>
<!-- Import external profile: External configuration files are preferred -->
<properties resource="db.properties"/>
<!-- Set standard log output -->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<!-- By aliasing the package -->
<typeAliases>
<package name="com.kuang.pojo"/>
</typeAliases>
<!-- Set the default environment to the development environment -->
<environments default="development">
<!-- Set an environment as the development environment -->
<environment id="development">
<!--transactionManager: Represents the transaction manager, and MyBatis The default manager for is JDBC-->
<transactionManager type="JDBC"/>
<!-- dataSource: data source, effect: Connect to database(MyBatis The default data source type is POOLED，That is, a connection with a pool) -->
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${pwd}"/>
</dataSource>
</environment>
</environments>
<!-- Binding interface: Using class File binding registration -->
<mappers>
<!-- Same name as package, use class The file can be found, Annotation can be used to test, which is complementary to the interface configuration file-->
<mapper class="com.kuang.dao.BlogMapper"/>
</mappers>
</configuration>
3. Write Java entity classes and Utils tool classes
3-1 writing Blog entity classes
package com.kuang.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
// Use @ Data annotation to introduce parameterless construction, get, set, toString and other methods
@Data
// Use the @ AllArgsConstructor annotation to introduce a parametric construction method
@AllArgsConstructor
// Use the @ NoArgsConstructor annotation to introduce the parameterless construction method
@NoArgsConstructor
public class Blog {
private String id; // Blog id
private String title; // Blog title
private String author; // author
private Date createTime; // Creation time
private int views; // Views
}
3-2 writing MybatisUtils tool class
package com.kuang.utils;
/**
* SqlSessionFactoryBuilder: Build a factory
* --> sqlSessionFactory: Production sqlSession
* --> sqlSession
*/
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory;
// Static method body
static {
try {
// Read configuration file
String resource = "mybatis-config.xml";
// Parse profile stream
InputStream inputStream = Resources.getResourceAsStream(resource);
// Get factory
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* SqlSession Provides all the methods required to execute SQL commands in the database
*/
public static SqlSession getSqlSession() {
// Set the parameter to true to realize automatic submission
return sqlSessionFactory.openSession(true);
}
}
3-3 writing IdUtils tool class
package com.kuang.utils;
import org.junit.Test;
import java.util.UUID;
// Use the @ SuppressWarnings annotation to suppress all warnings
@SuppressWarnings("all")
public class IdUtils {
public static String getId() {
// Using UUID, replace "." with "-"
// return UUID.randomUUID().toString().replaceAll(".","-");
// Using UUID, replace "-" with ""
return UUID.randomUUID().toString().replaceAll("-","");
}
// Test IdUtils tool class
@Test
public void test() {
// The printout generates three different UUID s
System.out.println(IdUtils.getId());
System.out.println(IdUtils.getId());
System.out.println(IdUtils.getId());
}
}
Test results:
Result: three different UUID s were successfully generated!
4. Write the BlogMapper interface and its mapping file
4-1 writing BlogMapper interface
package com.kuang.dao;
import com.kuang.pojo.Blog;
public interface BlogMapper {
// insert data
int addBlog(Blog blog);
}
4-2 write the BlogMapper.xml mapping file
<?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.kuang.dao.BlogMapper">
<!-- Insert blog info -->
<insert id="addBlog" parameterType="blog">
insert into mybatis.blog(id, title, author, create_time, views)
values (#{id}, #{title}, #{author}, #{createTime}, #{views})
</insert>
</mapper>
5. Write test classes and test results
5-1 writing MyTest test class code
package com.kuang.dao;
import com.kuang.pojo.Blog;
import com.kuang.utils.IdUtils;
import com.kuang.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.Date;
public class MyTest {
@Test
public void addBlog() {
// Get sqlSession object
SqlSession sqlSession = MybatisUtils.getSqlSession();
// Get Mapper interface object
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
// Instantiate Blog entity class
Blog blog = new Blog();
// Set the first data message
blog.setId(IdUtils.getId());
blog.setTitle("MyBatis So simple");
blog.setAuthor("Madness theory");
blog.setCreateTime(new Date());
blog.setViews(9999);
// Insert first data
mapper.addBlog(blog);
// Set the second data message
blog.setId(IdUtils.getId());
blog.setTitle("Java So simple");
// Insert second data
mapper.addBlog(blog);
// Set the third data information
blog.setId(IdUtils.getId());
blog.setTitle("Spring So simple");
// Insert third data
mapper.addBlog(blog);
// Set the fourth data information
blog.setId(IdUtils.getId());
blog.setTitle("Microservices are so simple");
// Insert fourth data
mapper.addBlog(blog);
// Close the sqlSession object
sqlSession.close();
}
}
5-2 test results
- View console output
- View database information
Results: four pieces of data were successfully inserted!
13.3 using IF statement to realize query
13.3.1 modify Mapper interface and mapping file
1. Modify the BlogMapper interface
package com.kuang.dao;
import com.kuang.pojo.Blog;
public interface BlogMapper {
// Query blog
List<Blog> queryBlogIF(Map map);
}
2. Modify the BlogMapper.xml mapping file
<?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.kuang.dao.BlogMapper">
<!-- Query blog information -->
<select id="queryBlogIF" parameterType="map" resultType="blog">
<!-- where 1=1 Firstly, it will not hinder the normal query. Secondly, it is convenient for the following and Conditional splicing -->
Select * from mybatis.blog where 1=1
<!-- Determine whether the title is empty -->
<if test="title != null">
<!-- If it is not empty, perform the following and Conditional splicing -->
and title = #{title}
</if>
<!-- Judge whether the author is empty -->
<if test="author !=null">
<!-- If it is not empty, perform the following and Conditional splicing -->
and author = #{author}
</if>
</select>
</mapper>
13.3.5 write test classes and test results
1. Write MyTest test class code
package com.kuang.dao;
public class MyTest {
// Query blog information
@Test
public void queryBlogIF() {
// Get sqlSession object
SqlSession sqlSession = MybatisUtils.getSqlSession();
// Get Mapper1 interface object
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
// Instantiate HashMap
HashMap map = new HashMap();
// Call the queryBlogIF method, put the map into the List, and get the List collection
List<Blog> blogs = mapper.queryBlogIF(map);
// Traverse the List collection
for (Blog blog : blogs) {
System.out.println(blogs);
}
//Close the sqlSession object
sqlSession.close();
}
}
2. Test results
report errors:
Caused by: org.apache.ibatis.executor.ExecutorException: A query was run and no Result Maps were found for the Mapped Statement 'com.kuang.dao.BlogMapper.queryBlogIF'. It's likely that neither a Result Type nor a Result Map was specified.
The reasons for error reporting are:
No result type is returned, that is, the resultType is not set in the corresponding Mapper interface mapping file
13.2.6 retest after modification
1. Modify the BlogMapper.xml mapping file
- Add resultType to the select tag to return the result type
<?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.kuang.dao.BlogMapper">
<!-- Query blog information(add resultType Result return type)-->
<select id="queryBlogIF" parameterType="map" resultType="blog">
<!-- Select * from mybatis.blog where title = #{title} and author = # {author} -->
<!-- where 1=1 Firstly, it will not hinder the normal query. Secondly, it is convenient for the following and Conditional splicing -->
Select * from mybatis.blog where 1=1
<!-- Determine whether the title is empty -->
<if test="title != null">
<!-- If it is not empty, perform the following and Conditional splicing -->
and title = #{title}
</if>
<!-- Judge whether the author is empty -->
<if test="author !=null">
<!-- If it is not empty, perform the following and Conditional splicing -->
and author = #{author}
</if>
</select>
</mapper>
2. Test results
Results: all blog information was successfully queried!
13.2.7 query blog information with specified conditions
1. Query with a specified condition
1-1 modify MyTest test class
package com.kuang.dao;
public class MyTest {
// Query the specified blog information
@Test
public void queryBlogIF() {
// Get sqlSession object
SqlSession sqlSession = MybatisUtils.getSqlSession();
// Get Mapper1 interface object
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
// Instantiate HashMap
HashMap map = new HashMap();
// Set title information
map.put("title","Java So simple");
// Call the queryBlogIF method, put the map into the List, and get the List collection
List<Blog> blogs = mapper.queryBlogIF(map);
// Traverse the List collection
for (Blog blog : blogs) {
System.out.println(blogs);
}
// Close the sqlSession object
sqlSession.close();
}
}
1-2 test results
Results: the blog post titled "Java is so simple" was successfully queried!
2. Query using multiple specified criteria
2-1 modify MyTest test class
package com.kuang.dao;
public class MyTest {
// Query the specified blog information
@Test
public void queryBlogIF() {
// Get sqlSession object
SqlSession sqlSession = MybatisUtils.getSqlSession();
// Get Mapper1 interface object
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
// Instantiate HashMap
HashMap map = new HashMap();
//Set title information
map.put("title","Java So simple");
// Set author information
map.put("author","Madness theory");
// Call the queryBlogIF method, put the map into the List, and get the List collection
List<Blog> blogs = mapper.queryBlogIF(map);
// Traverse the List collection
for (Blog blog : blogs) {
System.out.println(blogs);
}
//Close the sqlSession object
sqlSession.close();
}
}
1-2 test results
Results: the blog post with the title "Java is so simple" and the author "crazy God Theory" was successfully queried!
13.4 query using where tag
13.4.1 routine method test
1. Modify the BlogMapper.xml file
- Modified based on the BlogMapper.xml mapping file written in 13.3.4
<?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.kuang.dao.BlogMapper">
<!-- Query blog information: Use dynamic SQL Medium IF -->
<select id="queryBlogIF" parameterType="map" resultType="blog">
Select * from mybatis.blog where
<!-- Determine whether the title is empty -->
<if test="title != null">
<!-- If it is not empty, perform the following conditional splicing -->
title = #{title}
</if>
<!-- Judge whether the author is empty -->
<if test="author != null">
<!-- If it is not empty, perform the following and Conditional splicing -->
and author = #{author}
</if>
</select>
</mapper>
2. Modify MyTest test class
2-1 setting title and author information
package com.kuang.dao;
public class MyTest {
// Query the specified blog information
@Test
public void queryBlogIF() {
// Get sqlSession object
SqlSession sqlSession = MybatisUtils.getSqlSession();
// Get Mapper1 interface object
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
// Instantiate HashMap
HashMap map = new HashMap();
// Set title information
map.put("title","Java So simple");
// Set author information
map.put("author","Madness theory");
// Call the queryBlogIF method, put the map into the List, and get the List collection
List<Blog> blogs = mapper.queryBlogIF(map);
// Traverse the List collection
for (Blog blog : blogs) {
System.out.println(blogs);
}
//Close the sqlSession object
sqlSession.close();
}
}
2-2 set Only Title Information
package com.kuang.dao;
public class MyTest {
// Query the specified blog information
@Test
public void queryBlogIF() {
// Get sqlSession object
SqlSession sqlSession = MybatisUtils.getSqlSession();
// Get Mapper1 interface object
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
// Instantiate HashMap
HashMap map = new HashMap();
// Set title information
map.put("title","Java So simple");
// Call the queryBlogIF method, put the map into the List, and get the List collection
List<Blog> blogs = mapper.queryBlogIF(map);
// Traverse the List collection
for (Blog blog : blogs) {
System.out.println(blogs);
}
// Close the sqlSession object
sqlSession.close();
}
}
2-3 set author information only
package com.kuang.dao;
public class MyTest {
// Query the specified blog information
@Test
public void queryBlogIF() {
// Get sqlSession object
SqlSession sqlSession = MybatisUtils.getSqlSession();
// Get Mapper1 interface object
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
// Instantiate HashMap
HashMap map = new HashMap();
// Set author information
map.put("author","Madness theory");
// Call the queryBlogIF method, put the map into the List, and get the List collection
List<Blog> blogs = mapper.queryBlogIF(map);
// Traverse the List collection
for (Blog blog : blogs) {
System.out.println(blogs);
}
// Close the sqlSession object
sqlSession.close();
}
}
3. Test results
3-1 setting title and author information
Results: the blog with the title "Java is so simple" and the author "crazy God Theory" was successfully queried!
3-2 set Only Title Information
Results: the blog information titled "Java is so simple" was successfully queried!
3-3 set author information only
report errors:
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; Check the manual that responses to your MySQL server version for the right syntax to use near 'and author ='
analysis:
Error writing SQL statement, where and author =? Between and!
13.4.2 use the where label to test one
1. Modify the BlogMapper.xml file
- Modified based on the BlogMapper.xml mapping file written in 13.3.4
<?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.kuang.dao.BlogMapper">
<!-- Query blog information: Using where label -->
<select id="queryBlogIF" parameterType="map" resultType="blog">
Select * from mybatis.blog
<where>
<!-- Determine whether the title is empty -->
<if test="title != null">
<!-- If condition 1 is empty and condition 2 is not empty, remove this condition and the following and
If condition 1 is not empty and condition 2 is empty, it is removed and Together with the following condition 2
If both are empty, remove where All conditions of clause -->
title = #{title}
</if>
<!-- Judge whether the author is empty -->
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
</mapper>
2. Modify MyTest test class
2-1 set author information only
- It is the same as the code for setting only author information in 13.4.1
2-2 title and author information are set at the same time
- It is the same as the code for setting title and author information in 13.4.1
3. Test results
3-1 set author information only
Results: all blog information with the author of "crazy God Theory" was successfully queried!
Conclusion:
Therefore, we found that after using the where tag, if only the second condition is satisfied (that is, the title information is empty, but the author information is not empty), it will automatically remove the redundant and and and the previous conditions
3-2 both title and author information are set
Results: the blog with the title "Java is so simple" and the author "crazy God Theory" was successfully queried!
Conclusion:
Therefore, we found that after using the where tag, if both conditions are met (i.e. the title and author are not empty), it will automatically add the previous redundant and together with the previous conditions
13.4.3 test II with where label
1. Write BlogMapper interface and configuration file
1-1 write BlogMapper interface
package com.kuang.dao;
import com.kuang.pojo.Blog;
import java.util.List;
import java.util.Map;
public interface BlogMapper {
// Querying Blogs: using Choose
List<Blog> queryBlogChoose(Map map);
}
1-2 write the BlogMapper.xml file
<?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.kuang.dao.BlogMapper">
<!-- Query blog information: use where label -->
<select id="queryBlogChoose" parameterType="map" resultType="blog">
Select * from mybatis.blog
<where>
<!-- If condition 1 is empty and condition 2 is not empty, remove this condition and the following and
If condition 1 is not empty and condition 2 is empty, it is removed and Together with the following condition 2
If both are empty, remove where All conditions of clause -->
<if test="title != null">
id = #{id}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
</mapper>
2. Write MyTest test class
- Title and author information are not set
package com.kuang.dao;
public class MyTest {
// Query blog information: use the where tag
@Test
public void queryBlogChoose() {
// Get sqlSession object
SqlSession sqlSession = MybatisUtils.getSqlSession();
// Get Mapper1 interface object
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
//Instantiate HashMap
HashMap map = new HashMap();
//Call the queryBlogIF method, put the map into the List, and get the List collection
List<Blog> blogs = mapper.queryBlogChoose(map);
//Traverse the List collection
for (Blog blog : blogs) {
System.out.println(blog);
}
//Close the sqlSession object
sqlSession.close();
}
}
3. Test results
Results: all blog information was successfully queried!
Conclusion:
Therefore, we found that after using the where tag, if the conditions are not met (i.e. the title and author are empty), it will automatically delete the following where and its conditions
13.4.4 use summary of where label
- The where element will automatically insert the "where" clause only when the condition of at least one child element returns an SQL clause (that is, at least one condition is met)
- If the statement starts with "and" or "or", the where element also removes them automatically
13.5 query using choose statement
13.5.1 write the BlogMapper interface and its mapping file
1. Write BlogMapper interface
package com.kuang.dao;
import com.kuang.pojo.Blog;
import java.util.List;
import java.util.Map;
public interface BlogMapper {
// Querying Blogs: using Choose
List<Blog> queryBlogChoose(Map map);
}
2. Write the BlogMapper.xml mapping file
<?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.kuang.dao.BlogMapper">
<!-- Query blog information: use Choose sentence -->
<select id="queryBlogChoose" parameterType="map" resultType="blog">
Select * from mybatis.blog
<where>
<choose>
<when test="title != null">
title= #{title}
</when>
<when test="author != null">
and author = #{author}
</when>
<otherwise>
and views = #{views}
</otherwise>
</choose>
</where>
</select>
</mapper>
13.5.2 writing test classes
1. Set only the playback volume information
package com.kuang.dao;
public class MyTest {
// Query blog information: using choose statement
@Test
public void queryBlogChoose() {
// Get sqlSession object
SqlSession sqlSession = MybatisUtils.getSqlSession();
// Get Mapper1 interface object
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
// Instantiate HashMap
HashMap map = new HashMap();
// Set browsing information
map.put("views","9999");
// Call the queryBlogIF method, put the map into the List, and get the List collection
List<Blog> blogs = mapper.queryBlogChoose(map);
// Traverse the List collection
for (Blog blog : blogs) {
System.out.println(blog);
}
//Close the sqlSession object
sqlSession.close();
}
}
2. Set the title and playback volume information
package com.kuang.dao;
public class MyTest {
// Query blog information: using choose statement
@Test
public void queryBlogChoose() {
// Get sqlSession object
SqlSession sqlSession = MybatisUtils.getSqlSession();
// Get Mapper1 interface object
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
// Instantiate HashMap
HashMap map = new HashMap();
// Set title information
map.put("title","Spring So simple");
// Set browsing information
map.put("views","9999");
// Call the queryBlogIF method, put the map into the List, and get the List collection
List<Blog> blogs = mapper.queryBlogChoose(map);
// Traverse the List collection
for (Blog blog : blogs) {
System.out.println(blog);
}
// Close the sqlSession object
sqlSession.close();
}
}
13.5.3 test results
1. Set only the playback volume information
Results: all blog information with 9999 playback volume was successfully queried!
2. Set the title and playback volume information
Results: the blog information titled "Spring is so simple" was successfully queried!
analysis:
However, the playback volume is 10000, which is inconsistent with the set playback volume 9999, but it can be found successfully
13.5.4 test summary
From this point of view, choose (when, otherwise) is equivalent to the switch... case... Statement in Java. When the title meets a condition (that is, the title is "Spring is so simple"), because the where tag is used, the condition after and will be automatically removed, and then the query will be ended
13.6 update using set tag
1365.1 write BlogMapper interface and its configuration file
1. Write BlogMapper interface
package com.kuang.dao;
import com.kuang.pojo.Blog;
import java.util.List;
import java.util.Map;
public interface BlogMapper {
// Update blog
int updateBlog(Map map);
}
2. Write the BlogMapper.xml file
<?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.kuang.dao.BlogMapper">
<!-- Update blog information: Using set label -->
<update id="updateBlog" parameterType="map">
Update mybatis.blog
<set>
<if test="title != null">
title = #{title},
</if>
<if test="author != null">
author = #{author}
</if>
</set>
where id = #{id}
</update>
</mapper>
13.5.2 writing test classes
1. Set the title, author and id information
package com.kuang.dao;
public class MyTest {
// Update blog information: use the set tag
@Test
public void updateBlog() {
// Get sqlSession object
SqlSession sqlSession = MybatisUtils.getSqlSession();
// Get Mapper1 interface object
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
// Instantiate HashMap
HashMap map = new HashMap();
// Set title information
map.put("title","JavaWeb So simple");
// Set author information
map.put("author","Meet the crazy God");
// Set id information
map.put("id","999542fc1e1344908ef4cd22fe664906");
// Call the updateBlog method to update the blog information
mapper.updateBlog(map);
// Close the sqlSession object
sqlSession.close();
}
}
2. Set only id information
package com.kuang.dao;
public class MyTest {
// Update blog information: use the set tag
@Test
public void updateBlog() {
// Get sqlSession object
SqlSession sqlSession = MybatisUtils.getSqlSession();
// Get Mapper1 interface object
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
// Instantiate HashMap
HashMap map = new HashMap();
// Set title information
map.put("title","JavaWeb So simple");
// Set id information
map.put("id","999542fc1e1344908ef4cd22fe664906");
// Call the updateBlog method to update the blog information
mapper.updateBlog(map);
// Close the sqlSession object
sqlSession.close();
}
}
3. Set title and id information
package com.kuang.dao;
public class MyTest {
// Update blog information: use the set tag
@Test
public void updateBlog() {
// Get sqlSession object
SqlSession sqlSession = MybatisUtils.getSqlSession();
// Get Mapper1 interface object
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
// Instantiate HashMap
HashMap map = new HashMap();
// Set id information
map.put("id","999542fc1e1344908ef4cd22fe664906");
// Call the updateBlog method to update the blog information
mapper.updateBlog(map);
// Close the sqlSession object
sqlSession.close();
}
}
13.5.3 test results
1. Set the title, author and id information
- View console output
- View database information
Result: the blog information with id "999542fc1e1344908ef4cd22fe664906" was successfully modified!
2. Set only id information
Result: failed to update blog information!
report errors:
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where id = '999542fc1e1344908ef4cd22fe664906''
analysis:
The update failed because the set statement before the where clause condition is missing
3. Set title and id information
- View console output
- View database information
Results: the blog information with id "999542fc1e1344908ef4cd22fe664906" was still successfully modified!
13.5.4 test conclusion
Although the author information is not set, after using the set tag, the comma after the title condition and the author information condition are automatically removed
13.7 using trim(where,set) statements
13.7.1 using trim(where) statements
<trim prefix="where" prefixOverrides="and | or">
...
</trim>
refix = "where": prefix where
prefixOverrides = "and | or": whether to replace and or. If there is no prefix where, replace "and | or"
"and | or" precedes the statement
13.7.2 using trim(set) statements
<trim prefix="set" suffixOverrides=",">
...
</trim>
Prefix = "set": whether to prefix set
suffixOverrides = ",": replace "," ", if there is no prefix set, replace", "
"," is after the statement
13.7.3 use summary
- Prefix means to set the prefix. The value can usually be set to "where" or "set"
- prefixOverrides refers to the coverage before the sentence. If the prefix does not exist, its setting value will be automatically removed. It can usually be set to "and | or"
- suffixOverrides means to overwrite after a sentence. If the suffix does not exist, its setting value will be automatically removed, which can usually be set to ","
Therefore, the so-called dynamic SQL is still an SQL statement in essence, but we can execute a logical code at the SQL level
Common Tags: if, where, set, choose, when
13.8 using SQL fragments
Sometimes, we may extract some functions for reuse!
13.8.1 steps for using SQL fragments
1. Use SQL tags to extract common parts
<sql id="if-title-author" >
<!-- Determine whether the title is empty -->
<if test="title != null">
title = #{title}
</if>
<!-- Judge whether the author is empty -->
<if test="author != null">
and author = #{author}
</if>
</sql>
2. Use the include tag to reference
- Use the include tag reference where necessary
<!-- Query blog information: Using where label -->
<select id="queryBlogIF" parameterType="map" resultType="blog">
Select * from mybatis.blog
<where>
<!-- refid: Referenced id，This refers to what was written before sql Tagged id -->
<include refid="if-title-author"></include>
</where>
</select>
13.8.2 SQL fragment usage test
1. Modify the BlogMapper.xml mapping file
<?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.kuang.dao.BlogMapper">
<!-- Query blog information: use sql and include label -->
<select id="queryBlogIF" parameterType="map" resultType="blog">
Select * from mybatis.blog
<where>
<!-- use include Label reference
refid: Referenced id，This refers to what was written before sql Tagged id -->
<include refid="if-title-author"></include>
</where>
</select>
<!-- use sql Tag extraction common part -->
<sql id="if-title-author" >
<!-- Determine whether the title is empty -->
<if test="title != null">
title = #{title}
</if>
<!-- Judge whether the author is empty -->
<if test="author != null">
and author = #{author}
</if>
</sql>
</mapper>
2. Modify MyTest test class
2-1 set Only Title Information
package com.kuang.dao;
public class MyTest {
// Query the specified blog information
@Test
public void queryBlogIF() {
// Get sqlSession object
SqlSession sqlSession = MybatisUtils.getSqlSession();
// Get Mapper1 interface object
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
// Instantiate HashMap
HashMap map = new HashMap();
// Set title information
map.put("title","Java So simple");
// Call the queryBlogIF method, put the map into the List, and get the List collection
List<Blog> blogs = mapper.queryBlogIF(map);
// Traverse the List collection
for (Blog blog : blogs) {
System.out.println(blogs);
}
// Close the sqlSession object
sqlSession.close();
}
}
2-2 set author information only
package com.kuang.dao;
public class MyTest {
// Query the specified blog information
@Test
public void queryBlogIF() {
// Get sqlSession object
SqlSession sqlSession = MybatisUtils.getSqlSession();
// Get Mapper1 interface object
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
// Instantiate HashMap
HashMap map = new HashMap();
// Set author information
map.put("author","Madness theory");
// Call the queryBlogIF method, put the map into the List, and get the List collection
List<Blog> blogs = mapper.queryBlogIF(map);
// Traverse the List collection
for (Blog blog : blogs) {
System.out.println(blogs);
}
// Close the sqlSession object
sqlSession.close();
}
}
3. Test results
3-1 set Only Title Information
Results: the blog information titled "Java is so simple" was successfully queried!
3-2 set author information only
Results: all blog information with the author of "crazy God Theory" was successfully queried!
13.8.3 precautions for using SQL fragments
- It is best to define SQL fragments based on a single table
- Do not have a where tag
13.9 using ForEach statements
In dynamic SQL, you can use a foreach statement to traverse a collection, usually when building an in conditional statement
13.9.1 write BlogMapper interface
package com.kuang.dao;
import com.kuang.pojo.Blog;
import java.util.List;
import java.util.Map;
public interface BlogMapper {
// Update blog
List<Blog> queryBlogForeach(Map map);
}
13.9.2 modify the BlogMapper.xml file
<?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.kuang.dao.BlogMapper">
<!-- Query blog information: Using foreach label -->
<!-- Now pass on a universal map，this map A collection can exist in-->
<select id="queryBlogForeach" parameterType="map" resultType="blog">
Select * from mybatis.blog
<where>
<!-- foreach: Traversal set; item: Collection item; index: Indexes(Generally not used);
collection: aggregate; open: start; close: ending; separator: Separator -->
<foreach item="id" collection="ids" open="and (" separator="or" close=")">
id = #{id}
</foreach>
</where>
</select>
</mapper>
13.9.3 modify MyTest test class
1. Only put the ids set into the map
package com.kuang.dao;
public class MyTest {
// Query blog information: use foreach tag
@Test
public void queryBlogForeach() {
// Get SqlSession object
SqlSession sqlSession = MybatisUtils.getSqlSession();
// Get BlogMapper interface
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
// Instantiate HashMap
HashMap map = new HashMap();
// Instantiate ArrayList (store in ids Collection)
ArrayList<Integer> ids = new ArrayList<Integer>();
// Put ids into map
map.put("ids",ids);
// Get the List of Blog collection
List<Blog> blogs = mapper.queryBlogForeach(map);
// Traverse the List collection
for (Blog blog : blogs) {
System.out.println(blog);
}
// Close the sqlSession object
sqlSession.close();
}
}
2. Add the collection item and put the ids collection into the map
package com.kuang.dao;
public class MyTest {
// Query blog information: use foreach tag
@Test
public void queryBlogForeach() {
// Get SqlSession object
SqlSession sqlSession = MybatisUtils.getSqlSession();
// Get BlogMapper interface
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
// Instantiate HashMap
HashMap map = new HashMap();
// Instantiate ArrayList (store in ids Collection)
ArrayList<Integer> ids = new ArrayList<Integer>();
// Add collection item with id 1
ids.add(1);
// Add collection item with id 2
ids.add(2);
// Put ids into map
map.put("ids",ids);
// Get the List of Blog collection
List<Blog> blogs = mapper.queryBlogForeach(map);
// Traverse the List collection
for (Blog blog : blogs) {
System.out.println(blog);
}
// Close the sqlSession object
sqlSession.close();
}
}
13.9.4 test results
1. Only put the ids set into the map
Results: all blog information was successfully queried!
2. Add the collection item and put the ids collection into the map
Results: the blog information with id 1 and 2 was successfully queried!
13.9.5 dynamic SQL test summary
Dynamic SQL is splicing SQL statements. We just need to ensure the correctness of SQL and arrange and combine them according to the SQL format
Suggestion: write a complete SQL in MySQL first, and then modify it accordingly to become dynamic SQL to achieve general purpose
Well, today's study on the simple use of dynamic SQL is over. You are welcome to study and discuss actively. If you like, you can pay attention to Mr. snail. By the way, you can click three times. I'll see you next time. Bye!
Reference video link: [crazy God says Java] the latest complete tutorial of Mybatis, the IDEA version is easy to understand