Mybatis usage steps
Add requirements = = > add requirements in the xxxMapper interface = = > Add SQL according to requirements in xxxMapper.xml = = > register xxxMapper.xml in configuration XML = = > call according to requirements in controller
XML
<?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>
<!--Database configuration information url stay db.properties Configuration, indicating that you can xml Also available in properties in-->
<properties resource="db.properties">
<property name="username" value="root"/>
<property name="password" value="root"/>
<property name="driver" value="com.mysql.jdbc.Driver"/>
</properties>
<!--Configuration log-->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
<setting name="cacheEnabled" value="true"/>
</settings>
<!--Aliases are mainly mapper of xml Can be used in-->
<typeAliases>
<typeAlias type="com.liu.pojo.User" alias="User"/>
</typeAliases>
<!--Database configuration-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!--every last Mapper.xml All need to be in mybatis There are three types of registration in the core configuration file-->
<mappers>
<mapper class="com.liu.dao.User.UserMapper"/>
<mapper class="com.liu.dao.Bill.BillMapper"></mapper>
<mapper class="com.liu.dao.Provider.ProviderMapper"></mapper>
<mapper class="com.liu.dao.Role.RoleMapper"></mapper>
</mappers>
</configuration>
- Three ways to register mapper
<!--First: resourse-->
<mapper resource="com/liu/dao/UserMapper.xml"/>
<!--Second: class-->
<mapper class="com.liu.dao.UserMapper"></mapper>
<!--Interface and his Mapper The configuration file must have the same name! Meaning time UserMapper.xml=UserMapper Not one UserDao One UserMapper.xml-->
<!--Interface and his Mapper The configuration file must be under the same package!-->
<!--Third: package It's universal registration. All are registered-->
<!--also mappers There can only be package，Indicates all dao It's all configured-->
<mappers><package name="com.liu.dao"/></mappers>
Tool class
//sqlSessionFactory --> sqlSession
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static{
try {
//Step 1 of using Mybatis: get sqlSessionFactory object
String resource= "mybatis.config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
//Now that we have SqlSessionFactory, as the name suggests, we can get an instance of SqlSession from it.
// SqlSession completely contains all the methods required to execute SQL commands for the database.
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession();
//openSession(true); Auto commit transaction
//return sqlSessionFactory.openSession(true);
}
}
- test
public void test(){
MybatisUtils.getSqlSession().getMapper(UserMapper.class).Methods in interfaces
// ...........
}
Log configuration
- log4j.properties
#org.apache.log4j.ConsoleAppender console
#org.apache.log4j.FileAppender file
#org.apache.log4j.DailyRollingFileAppender generates a log file every day
#org.apache.log4j.RollingFileAppender generates a new file when the file size reaches the specified size
#org.apache.log4j.WriterAppender sends log information to any specified place in stream format
#org.apache.log4j.net.SMTPAppender email
#Org.apache.log4j.jdbc.jdbc appender database
#Output the log information with the level of DEBUG to the two destinations of console and file. The definitions of console and file are in the following code
log4j.rootLogger=DEBUG,console,file
#Settings related to console output
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n
#Settings related to file output
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/liu.log # Create your own log directory and its liu.log file
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n
#Log output level
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
Simple use
-
In the class to use log4j, import the package import org.apache.log4j.Logger;
-
Log object. The parameter is the class of the current class
static Logger logger = Logger.getLogger(Current class.class);
-
log level
logger.info("info:Entered testLog4j"); logger.debug("debug:Entered testLog4j"); logger.error("error:Entered testLog4j");
Fuzzy query
-
When executing Java code, pass wildcard%%
// UserMapper public ArrayList<User> getUerListByLike(@Param("likeString") String string); // UserMapper.xml select * from smbms_user where address like #{likeString}; // test List<User> userList = mapper.getUerListByLike("%Lee%"); -
Use wildcards in sql splicing!
select * from mybatis.user where name like "%"#{likeString}"%"
life cycle
The property name and field name are inconsistent
public class User {
private int id;
private String name;
private String password; // Does not match the database!!
}
- Alias: change the database attribute name to the entity field name in the sql of xml
## pwd as password
select id,name,pwd as password from mybatis.user where id = #{id}
- resultMap
- Result set mapping
id name pwd id name password
<!--Result set mapping-->
<resultMap id="liujianyu" type="User">
<!--column Fields in the database, property Properties in entity classes-->
<result column="id" property="id"/>
<result column="name" property="name"/>
<result column="pwd" property="password"/>
</resultMap>
<select id="getUserById" resultMap="liujianyu">
select * from mybatis.user where id = #{id}
</select>
paging
- Limit paging
SELECT * from user limit #{startIndex},#{pageSize};
SELECT * from user limit 3; # Start with the first one (index 0) and look up three
#[0,n] n displayed on each page, starting from 0
- RowBounds
@Test
public void getUserList(){
// Check 2 from the first (index 0)
RowBounds rowBounds = new RowBounds(0,2);
// com.liu.dao.User.UserMapper.getUserList represents the method of the interface
List<Object> userList =
MybatisUtils.getSqlSession().selectList("com.liu.dao.User.UserMapper.getUserList", null, rowBounds);
for (int i = 0; i < userList.size(); i++) {
System.out.println(userList.get(i));
}
}
select * from smbms_user ;
- PageHelper plug-in
- Import Maven dependencies
<!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.2.0</version>
</dependency>
- Configuring in xml
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<property name="dialect" value="mysql"/>
</plugin>
</plugins>
- test
@Test
public void getUserList(){
// 2 data per page starting from the first (index 0)
PageHelper.startPage(0,2);
ArrayList<User> userList = MybatisUtils.getSqlSession().getMapper(UserMapper.class).getUserList();
}
One to many, many to one
One to many
-
Preparation, one teacher corresponds to multiple students
-
teacher table
- Teacher entity class
import java.util.List;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
private int id;
private String name;
private List<Student> students;// One teacher corresponds to multiple students
}
- student table
- Student entity class
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private int id;
private String name;
private int tid;
}
Map by query result
<select id="getTeachers" resultMap="xxx">
select s.id sid, s.name sname, t.name tname,t.id tid
from student s,teacher t
where s.tid = t.id and t.id = #{tid} <!-- One to many, if many to many and t.id = #{tid} remove -- >
</select>
<resultMap id="xxx" type="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<collection property="students" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
//Methods in the TeacherMapper interface
public ArrayList<Teacher> getTeachers(@Param("tid") int id);
@Test
public void getStudents(){
ArrayList<Teacher> teachers = MybatisUtils.getSqlSession().getMapper(TeacherMapper.class).getTeachers(1);
for (Teacher teacher : teachers) {
System.out.println(teacher.getName()+"Students:"+teacher.getStudents());
}
}
Map by query
<select id="getTeachers" resultMap="xxx">
select * from mybatis.teacher where id = #{tid}
</select>
<resultMap id="xxx" type="Teacher">
<collection property="students" ofType="Student" select="liujian" column="id"/>
</resultMap>
<select id="liujian" resultType="Student">
select * from mybatis.student where tid = #{tid} <!-- This TID comes from column = "Id" - - >
</select>
Many to one
-
Preparation, multiple students correspond to one teacher
-
Database unchanged
-
The Teacher entity class removes the students field and the Student entity class adds a private Teacher teacher;
Map by query result
<select id="getStudents" resultMap="xxx">
select s.id sid,s.name sname,t.name tname
from student s,teacher t
where s.tid = t.id;
</select>
<resultMap id="xxx" type="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
//Methods in the StudentMapper interface
public ArrayList<Student> getStudents();
@Test
public void getStudents(){
ArrayList<Student> students = MybatisUtils.getSqlSession().getMapper(StudentMapper.class).getStudents();
for (Student student : students) {
System.out.println(student);
}
}
Map by query
<select id="getStudents" resultMap="xxx">
select * from student
</select>
<resultMap id="xxx" type="Student">
<association property="teacher" javaType="Teacher" select="getTeacher" column="tid"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from teacher where id = #{id} <!-- ID from column = "TID" - - >
</select>
summary
One to many, many to many, using collection and ofType
Many to one, using association and javaType
cache
L1 cache
- The L1 cache is also called the local cache: SqlSession
Cache invalidation:
- Query different things
- Adding, deleting and modifying may change the original data, so the cache will be refreshed!
- Query different Mapper.xml
- Manually clean up the cache! sqlSession.clearCache();
Summary:
The L1 cache is enabled by default and is only valid for one SqlSession, that is, the interval from getting the connection to closing the connection! The first level cache is a Map. Enable log view
L2 cache
- Based on the namespace level cache, a namespace corresponds to a L2 cache;
-
Enable the global cache in the xml configuration
<!--Show global cache enabled--> <setting name="cacheEnabled" value="true"/>
-
Open in xxxMapper.xml where you want to use L2 cache
<!--At present Mapper.xml Using L2 cache in--> <cache/>
You can also customize parameters
<!--At present Mapper.xml Using L2 cache in--> <cache eviction="FIFO" flushInterval="60000" size="512" readOnly="true"/> -
We need to serialize the entity class! Otherwise, an error will be reported!
Caused by: java.io.NotSerializableException: com.liu.pojo.User
-
test
@Test public void getStudents(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); ArrayList<User> students = sqlSession.getMapper(UserMapper.class).getStudents(1);// The query id is 1 for (User student : students) { System.out.println(student); } sqlSession.close(); // Close session System.out.println("========================================"); SqlSession sqlSession1 = MybatisUtils.getSqlSession(); ArrayList<User> students2 = sqlSession1.getMapper(UserMapper.class).getStudents(1);// The query id is 1 for (User student : students2) { System.out.println(student); } sqlSession.close(); }
- Even if the session is closed, the L2 cache is used
Summary:
- As long as the L2 cache is enabled, it is valid under the same Mapper
- All data will be put in the first level cache first;
- Only when the session is submitted or closed will it be submitted to the secondary buffer!
summary
- First, configure mybatis.config, which is mainly used to connect to the database (import external db.properties), register mapper (three), and then alias, cache, etc
- (optional) configure log log4j.properties to facilitate troubleshooting
- Then write a tool class to facilitate calling
- Add a large requirement. First find the entity class (if not, create it), then write the corresponding xxmapper interface, then configure xxmapper.xml to write the requirement sql, and then call the Controller
- For xxxMapper.xml, you must configure the namespace and the corresponding xxxMapper interface
- Every time you write an xxmapper, you must register it in mybatis.config
<?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.liu.dao.User.UserMapper">
Common interview
#What is the difference between {} and ${}?
#{} is precompiled and ${} is string substitution.
-
When Mybatis processes #{}, it will replace #{} in sql with #{}? Number, call the set method of PreparedStatement to assign value;
-
When Mybatis processes ${}, it replaces it with the value of the variable. Using #{} can effectively prevent SQL injection and improve system security.
What happens when the attribute name in the entity class is different from the field name in the table?
-
By defining the alias of the field name in the sql statement of the query, the alias of the field name is consistent with the attribute name of the entity class
-
Map the one-to-one correspondence between field names and entity class attribute names through resultMap.
How to write a fuzzy query like statement
-
Add sql wildcard in Java code, string like = "% smi%"
-
Splicing wildcards in sql statements will cause sql to inject where xxx like "%" #{value} "%"
How does the Dao interface work? Can the method be overloaded when the parameters of the method in Dao interface are different?
- Dao interface is mapper interface. The fully qualified name of the interface is the value of the namespace in the mapping file; The method name of the interface is the id value of mapper's Statement in the mapping file; The parameters in the interface method are the parameters passed to sql. Mapper interface has no implementation class. When calling interface methods, the interface fully qualified name + method name splicing string is used as the key value to uniquely locate a MapperStatement. In Mybatis, each select, insert, update and delete tag will be resolved into a MapperStatement object.
For example: com.mybatis3.mappers.StudentDao.findStudentById, MapperStatement with id findStudentById under namespace com.mybatis3.mappers.StudentDao can be found uniquely.
-
The method in Mapper interface cannot be overloaded because it uses the saving and searching strategy of fully qualified name + method name.
-
The working principle of Mapper interface is JDK dynamic proxy. Mybatis runtime will use JDK dynamic proxy to generate proxy object proxy for Mapper interface. The proxy object will intercept interface methods, execute the sql represented by MapperStatement, and then return the sql execution results.
How does Mybatis encapsulate the sql execution result as a target object and return it? What are the mapping forms?
-
The first is to use the resultMap tag to define the mapping relationship between database column names and object attribute names one by one.
-
The second is to use the alias function of sql column to write the alias of column as object attribute name.
After having the mapping relationship between column name and attribute name, Mybatis creates objects through reflection, and uses the attributes reflected to the object to assign values one by one and return them. Those attributes that cannot find the mapping relationship cannot be assigned.
In the Xml Mapping file of Mybatis, can the IDs of different Xml mapping files be repeated?
-
For different Xml mapping files, if namespace is configured, the id can be repeated;
-
If the namespace is not configured, the id cannot be repeated;
The reason is that namespace+id is used as the key of map < string, mapperstatement >. If there is no namespace, there is only id, and the repeated id will cause the data to overlap each other. With a namespace, the natural id can be repeated. If the namespace is different, the namespace+id will naturally be different.
How do I pass multiple parameters in Mapper?
- You can use #{0} in its corresponding xml to represent the first parameter passed in by Dao layer
- Using @ Param annotation, add @ Param annotation before Dao layer parameters. The parameter name in the annotation is the parameter name passed to xml
- Multiple parameters are encapsulated into a Map and passed to xml in the form of HashMap