Article catalogue
1. Query all
Basic steps
1. Define the mapper interface and write the interface method
2. Define sql mapping file
Create an xml file that is the same as the mapper interface, put it under the same package directory, and then write sql statements
I query all, so sql statements are the same as mysql queries. If you want to query specific fields, you can also define them by yourself.
3. Write MyBaits code
To write code (refer to another article)
package com.mybatis.demo;
import com.mybatis.mapper.StuMapper;
import com.mybatis.pojo.Stu;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
import java.util.List;
public class MyBatis003 {
public static void main(String[] args) throws Exception {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
StuMapper stuMapper = sqlSession.getMapper(StuMapper.class);
List<Stu> stus = stuMapper.selectAll();
for (Stu stu:stus) {
System.out.println(stu);
}
sqlSession.close();
}
}
4. Query results. All the information in the database table stu is queried
5. Pay attention to information
When your database field name is different from your defined entity class attribute name, you cannot query the data information corresponding to different fields. If my database name is username and the entity category is username, here are three solutions.
Method 1: alias when defining sql statements
Method 2: define the SQL fragment, directly use the SQL tag to introduce the SQL statement, and directly use the include tag plus the ID name of the SQL fragment to find the corresponding SQL statement.
Method 3: use resultMap tag to map database field name and attribute name (the most commonly used)
The two most commonly used sub tags in resultMap are result, which maps common field names and attribute names, and id sub tags, which map primary keys and attribute names. The column attribute is the field name of the database table, and property is the attribute name of the entity class.
2. Query details (query by specific attributes)
1. Define the mapper interface and write the interface method. The parameter list here is all the information corresponding to the attribute you want to query. You can query all the information of the student through the name attribute.
2. Write sql mapping file
Create an xml file that is the same as the mapper interface, put it under the same package directory, and then write sql statements
3. Write MyBaits code
package com.mybatis.demo;
import com.mybatis.mapper.StuMapper;
import com.mybatis.pojo.Stu;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
public class MyBatis004 {
public static void main(String[] args) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
StuMapper stuMapper = sqlSession.getMapper(StuMapper.class);
Stu stu = stuMapper.selectByName("Xiao Zhao");
System.out.println(stu);
}
}
4. Query results
5. Pay attention to details
1. Use of parameter placeholders
The parameter placeholder is the name=#{name} above me. There are generally two kinds. One is #{}. This parameter placeholder can prevent sql injection, which is equivalent to name=?, Another is ${}, which cannot prevent sql injection, because it directly spell the parameters to the end, which is equivalent to name=name.
2. Special character processing
Special characters are =, >, < and so on. Some special characters in xml will be recognized by xml files. At this time, special characters need to be processed. One method is to use escape characters, and the other is to use CDATA area
For example, I want to query the information of students with id less than 18
3. Multi condition query
The following cases begin with the small characters in the stu table, students who live in Shanghai and are male
(1) Three expressions of interface parameter list
-
Bulk parameters: bulk parameters need to use @Param to represent the placeholder in sql, so that the corresponding parameters can find the corresponding placeholder.
List<Stu> selectByCondition(@Param("name")String name,@Param("adr")String adr,@Param("gender")String gender); -
Class encapsulation parameters: class encapsulation parameters need to define the corresponding parameter content in the MyBatis program, and then pass it into the method.
List<Stu> selectByCondition(Stu stu);
-
Map set encapsulates parameters: ensure that the parameter name in sql is consistent with the key name in map set.
List<Stu> selectByCondition(Map map);
I will use different parameter list types for different examples below.
(2) Multi condition query
Implementation steps‘
1. Define Mapper interface and write corresponding methods
2. Write sql mapping file
3. Write MyBatis code
package com.mybatis.demo;
import com.mybatis.mapper.StuMapper;
import com.mybatis.pojo.Stu;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class MyBatis005 {
public static void main(String[] args) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
StuMapper stuMapper = sqlSession.getMapper(StuMapper.class);
List<Stu> stus = stuMapper.selectByCondition("Small%", "Shanghai", "male");
for (Stu stu:stus) {
System.out.println(stu);
}
sqlSession.close();
}
}
4. Operation results
(3) Dynamic Sql
What is dynamic sql: dynamic sql is sql statements. With different conditions entered by users, the corresponding generated sql statements will be different. For example, when querying users, you may only know the user's name, or you may know the user's name, gender, etc. Different inputs correspond to different sql statements. Dynamic sql can solve this problem. MyBatis provides some dynamic sql methods and introductions, which I will not introduce one by one here. You can go Official website Look at the corresponding sentences and usage.
(4) Multi condition dynamic query
Implementation steps
1. Define the mapper interface and write methods. (class is used to encapsulate the parameter list here) 
2. Write the sql mapping file, where the dynamic sql if statement is used, that is, judge whether this parameter is passed in first, if so, execute the corresponding where condition, if not, do not add the where condition to the sql statement.
3. Write MyBatis code
When writing code, I use the list of class encapsulation parameters, so I need to encapsulate entity classes before executing sql statements.
package com.mybatis.demo;
import com.mybatis.mapper.StuMapper;
import com.mybatis.pojo.Stu;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class MyBatis006 {
public static void main(String[] args) throws IOException {
Stu stu = new Stu();
String name = "Small%";
String adr = "Shanghai";
String gender = "male";
stu.setName(name);
stu.setAdr(adr);
stu.setGender(gender);
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
StuMapper stuMapper = sqlSession.getMapper(StuMapper.class);
List<Stu> stus = stuMapper.selectByCondition(stu);
for (Stu s:stus
) {
System.out.println(s);
}
sqlSession.close();
}
}
4. Operation results
5. Precautions
Because the sql statements in the if condition are directly spliced on the total sql statements, if the text in the first if is empty, the sql statements in it will not be added. Take what I wrote as an example. If the parameter list name is empty, the total sql statement becomes select * from stu where and ADR in ADR and gender = gender; Obviously, it does not conform to the syntax of sql statements and will report errors.
MyBatis provides a solution to this problem, that is, replace the where keyword with a where tag outside the if tag, and then add an and to each sql statement inside.
(5) Single condition dynamic query
Implementation steps
1. Define a mapper interface and define the corresponding method (map set encapsulation parameters are used here)
2. Define the sql mapping file. The single condition dynamic query uses choose:when, otherwise, which is equivalent to Switch:case, default in java. When the test condition of the first when tag is met, the current statement will be executed and will not be executed further. When you are not satisfied, execute the other.
3. Write MyBatis code
The map set is used here to encapsulate parameters, so the parameter list should be encapsulated into the map set before executing sql.
package com.mybatis.demo;
import com.mybatis.mapper.StuMapper;
import com.mybatis.pojo.Stu;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MyBatis007 {
public static void main(String[] args) throws IOException {
Map map = new HashMap<>();
String name = "Small%";
String adr = "Shanghai";
String gender = "male";
map.put("name",name);
map.put("adr",adr);
map.put("gender",gender);
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
StuMapper stuMapper = sqlSession.getMapper(StuMapper.class);
List<Stu> stus = stuMapper.selectByCondition(map);
for (Stu s:stus
) {
System.out.println(s);
}
sqlSession.close();
}
}
4. Implementation results
Seeing this implementation result, you may be a little skeptical. Isn't there a limited address in Shanghai, and the gender is male. In fact, the third step has explained that when the condition of a when tag is met, the latter condition will not be transferred to the sql statement. So here is equivalent to only finding students who start with small words, which is the explanation of single condition.
First of all, I would like to introduce myself. I graduated from Jiaotong University in 13 years. I once worked in a small company, went to large factories such as Huawei OPPO, and joined Alibaba in 18 years, until now. I know that most junior and intermediate Java engineers who want to improve their skills often need to explore and grow by themselves or sign up for classes, but there is a lot of pressure on training institutions to pay nearly 10000 yuan in tuition fees. The self-study efficiency of their own fragmentation is very low and long, and it is easy to encounter the ceiling technology to stop. Therefore, I collected a "full set of learning materials for java development" and gave it to you. The original intention is also very simple. I hope to help friends who want to learn by themselves and don't know where to start, and reduce everyone's burden at the same time. Add the business card below to get a full set of learning materials