Mybatis
-
mybatis transactions are not automatically committed by default
-
Add this to the main configuration file to display the log
<settings> <setting name="logImpl" value="STDOUT_LOGGING" /> </settings>
1. Framework overview
1.1 three tier architecture
Interface layer (controller package)
Spring MVC framework
Connect with the user, accept the user's request parameters and display the processing results.
jsp,html,servlet
Business logic layer (XXXservice package)
spring framework
Receive the data transmitted by the interface layer, calculate the logic, call the database and obtain the data
Data access layer (XXXDao package)
mybatis framework
Access the database and perform operations such as query of object data
1.2 framework concept
A framework is a template in which some functions have been defined and are available. You can add your own functions to the project.
The framework is a semi-finished software. The defined basic functions are reusable and upgradeable.
Frame features:
- Not omnipotent, can't do everything
- Only effective for a certain field
- It's a software
1.3 introduction to mybatis
Mybatis is an sql mapping framework that provides database operation capabilities and enhanced JDBC. You only need to write sql statements to use it.
Mybatis is a framework, early called ibatis
MyBatis SQL Mapper Framework for Java
-
sql mapper: sql mapping
A row of data in the database table is mapped into a java object, and a row of data can be regarded as a java object. Operating this object is equivalent to operating the data in the table
-
Data Access Objects (DAOs): data access
Perform addition, deletion, modification and query on the database
Functions provided:
- Ability to create Connection, Statement, etc
- Execute sql statement
- Loop sql, convert the result into java object, List collection
- close resource
Developers only need to provide sql statements
2. Analyze JDBC shortcomings
Too much duplicate code reduces development efficiency.
The above process can be replaced by reflection mechanism, and the Mybatis framework is the java code written in advance by others. The Mybatis framework encapsulates the JDBC code, and the Mybatis framework is the java code written by others.
sql is written in java programs.
The sql statement may need to be tuned in the later stage, and the probability of the statement being modified is very high. However, after modifying the sql statement, the java code is recompiled and the project is redeployed, which is very cumbersome
Serious violation of opening and closing principle: OCP
3. Implementation steps
3.1 adding dependencies
<dependencies> <dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>mssql-jdbc</artifactId> <version>9.2.0.jre15</version> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.4.6</version> </dependency> </dependencies>
3.2 creating data entity classes
public class Student { private String sno; private String sname; private String ssex; private Integer sage; private String sdept; public String getSno() { return sno; } public void setSno(String sno) { this.sno = sno; } @Override public String toString() { return "Student{" + "sno='" + sno + '\'' + ", sname='" + sname + '\'' + ", ssex='" + ssex + '\'' + ", sage=" + sage + ", sdept='" + sdept + '\'' + '}'; } public String getSname() { return sname; } public void setSname(String sname) { this.sname = sname; } public String getSsex() { return ssex; } public void setSsex(String ssex) { this.ssex = ssex; } public Integer getSage() { return sage; } public void setSage(Integer sage) { this.sage = sage; } public String getSdept() { return sdept; } public void setSdept(String sdept) { this.sdept = sdept; } }
3.3 creating dao interface
Which defines the method of operating the database
public interface StudentDao { // Query all data List<Student> findAll(); }
3.4 create sql mapping file
<!-- sql Mapping files: Writing in sql sentence, mybatis Will perform these sql 1. Specify constraint file mybatis-3-mapper.dtd Is the name of the constraint file and the extension is dtd Role of constraint file: limit, check the labels appearing in the current file, and the attributes must comply with mybatis Requirements 2. mapper label The root label of the current file is required. namespace(Namespace): Unique value. It can be customized, but it is required to use dao Fully qualified name of the interface 3. In the current file, you can use specific labels to represent the operation of the database <select>: query <update>: to update <insert>: insert <delete>: delete --> <?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.company.dao.StudentDao"> <!-- select : Represents a query operation id : Indicates to execute sql Unique identification of the statement, mybatis Will use this id Value found to execute sql sentence It can be customized, but the method name in the interface is required resultType : Indicates the result type, yes sql Type of result after statement execution --> <select id="findAll" resultType="com.company.domain.Student"> select Sno, Sname, Ssex, Sage, Sdept from Student where Ssex = 'female' </select> </mapper>
3.5 creating jdbc configuration files
Write the properties file under resources
jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver jdbc.url=jdbc:sqlserver://localhost:1433;Database=S_T jdbc.user=sa jdbc.password=xxxxqq76985
3.5 create master profile
<?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> <!-- Define profile path --> <properties resource="jdbc.properties" /> <!-- settings,Print log to console There are many more settings Configuration, but most of them can use the default value without manual configuration --> <settings> <setting name="logImpl" value="STDOUT_LOGGING" /> </settings> <!-- to configure java Alias of class --> <typeAliases> <typeAlias type="com.company.vo.SelectAge" alias="SelectAge" /> <package name="com.company.domain" /> </typeAliases> <!-- When configuring plug-ins, be sure to put them in the environments front --> <plugins> <plugin interceptor="com.github.pagehelper.PageInterceptor" /> </plugins> <!-- Default use developmet environment --> <environments default="development"> <!-- environment: Configuration of a database information id: A unique value that represents the name of the environment --> <environment id="development"> <!-- transactionManager: mybatis Transaction type type: JDBC (Indicates use jdbc Medium Connection Object commit,rollback Do transactions) MANAGED (hold mybatis The transaction is delegated to other containers, a server software, a framework, and so on) --> <transactionManager type="JDBC"/> <!-- dataSource: Represents the data source and connects to the database javax.sql.DataSource Interfaces are data sources type: Represents the type of data source, POOLED Indicates that the connection pool is used, UNPOOLED Indicates that the connection pool is not used. Create a connection and then close it --> <dataSource type="POOLED"> <!-- driver, user, username, password They are fixed and cannot be modified --> <property name="driver" value="${jdbc.driver}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.user}"/> <property name="password" value="${jdbc.password}"/> </dataSource> </environment> </environments> <!-- sql mapper(sql Mapping file)Location of --> <mappers> <!-- One mapper Specify the location of a file Start with classpath --> <!-- The first way --> <mapper resource="com/company/dao/StudentDao.xml"/> <!-- The second way name: xml Documents( mapper The name of the package in which the file is located, and all the files in this package xml The file can be loaded to at one time mybatis requirement: 1. mapper The file name must be exactly the same as the interface name 2. mapper Documents and dao The interface needs to be in the same directory --> <package name="com.company.dao" /> </mappers> </configuration>
The mapper mapping file must be specified because when writing sqlId, the namespace will be found in the specified mapper
3.6 accessing database
import com.company.domain.Student; 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; // This is a test class public class Test { public static void main(String[] args) throws IOException { // 1. Define the name of the mybatis main configuration file, starting from the classpath String config = "data.xml"; // 2. Read the file represented by this config InputStream is = Resources.getResourceAsStream(config); // 3. Create SqlSessionFactoryBuilder object SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); // 4. Create SqlSessionFactory object SqlSessionFactory factory = builder.build(is); // 5. [important] get SqlSession object SqlSession sqlSession = factory.openSession(); // 6. [important] specify the id of the sql statement to be executed, and the id value of the namespace + ". + tag in the sql mapping file String sqlId = "com.company.dao.StudentDao" + "." + "findAll"; // 7. Execute sql statement List<Student> studentList = sqlSession.selectList(sqlId); // 8. Output results for (Student student : studentList) { System.out.println(student); } // 9. Close the SqlSession object sqlSession.close(); } }
String sqlId = "com.company.dao.StudentDao" + "." + "findAll";
com.company.dao.StudentDao matches the namespace of StudentDao
4. Introduction to implementation class
-
Resources
A class in mybatis, which is responsible for reading the main configuration file
-
SqlSessionFactoryBuilder
Create SqlSessionFactory object
-
SqlSessionFactory (Interface)
An important object, which takes a long time to create and uses a lot of resources, just one
Get SqlSession object
openSession Description:
- No parameters, get SqlSession object of non auto commit transaction
- true, get the SqlSession object that automatically commits the transaction
-
SqlSession (Interface)
Defines how to manipulate data
SqlSession is not thread safe. It needs to be used inside the method and closed after use to ensure thread safety
5. Get session encapsulation
public class MybatisUtil { private static SqlSessionFactory factory; /* Get the factory when loading the class. Only one factory is required */ static { String config = "data.xml"; try { InputStream is = Resources.getResourceAsStream(config); factory = new SqlSessionFactoryBuilder().build(is); } catch (IOException e) { e.printStackTrace(); } } private MybatisUtil() {} public static SqlSession getSqlSession() { return factory.openSession(); } public static SqlSession getSqlSession(boolean flag) { return factory.openSession(flag); } }
6. Dynamic agent implementation class
Mybatis helps you create the implementation class of the dao interface, and executes the sql statement in the implementation class by calling SqlSession.
(reflection mechanism)
Conditions for implementing dynamic proxy:
- dao interface and mapper file should be put together as much as possible
- dao interface is consistent with mapper file name
- The namespace in the mapper file is the fully qualified name of the dao interface
- The operation tag id value in the mapper file is the method name in the interface
- Do not use overloaded methods (same name, different parameters) for dao interface
public class Main { public static void main(String[] args) { SqlSession sqlSession = MybatisUtil.getSqlSession(); // Dynamic proxy get interface implementation class StudentDao dao = sqlSession.getMapper(StudentDao.class); List<Student> studentList = dao.findAll(); for (Student student : studentList) { System.out.println(student); } sqlSession.close(); } }
7. In depth understanding of parameters
public interface StudentDao { // Query all data List<Student> findAll(); // insert data int insert(Student stu); // Delete according to Sno int deleteBySno(String sno); // Query by name and age List<Student> findByNameSex (@Param("name") String name, @Param("sex") String sex); }
When the implementation class created by the dynamic agent executes the method, the parameter type will be detected and automatically assigned to the placeholder in the sql statement
However, if the parameter type is a class, the name in the placeholder must be the property name in the class, and the dynamic proxy mechanism will detect the getter method of the property name.
#{property name of incoming class}
7.1 passing in a single simple parameter
The placeholder name is arbitrary (preceded by #'s premise)
#{any character}
${specify name}
Simple parameters:
- Basic data types in java
- String type
<delete id="deleteBySno"> delete from Student where Sno=#{sno} </delete>
public void testDelete() { SqlSession sqlSession = MybatisUtil.getSqlSession(); StudentDao dao = sqlSession.getMapper(StudentDao.class); Student stu = new Student(); stu.setSno("201515010"); int num = dao.deleteBySno("201515010"); sqlSession.commit(); System.out.println(num); // 9. Close the SqlSession object sqlSession.close(); }
7.2 passing in multiple simple parameters
public void testFindByNameSex() { SqlSession sqlSession = MybatisUtil.getSqlSession(); StudentDao dao = sqlSession.getMapper(StudentDao.class); String name = "Zhang"; String sex = "male"; List<Student> studentList = dao.findByNameSex(name, sex); for (Student student : studentList) { System.out.println(student); } sqlSession.close(); }
7.2.1 use notes (important)
If the method needs to pass in multiple parameters, you need to specify the name of the placeholder in the sql mapping file using annotations in defining the method parameters
List<Student> findByNameSex (@Param("name") String name, @Param("sex") String sex);
<select id="findByNameSex" resultType="com.company.domain.Student"> select Sno, Sname, Ssex, Sage, Sdept from Student where Ssex = #{sex} and Sname like CONCAT('%',#{name},'%') </select>
7.2.2 packaging parameters (important)
This method encapsulates parameters into objects, similar to sqlSession.insert(String sqlId, Object obj)
In the original method, the parameters are found through the getter method of obj, and the encapsulated parameters are similar to this
public class FindByNameSex { private String paramName; private String paramSex; public String getParamName() { return paramName; } public void setParamName(String paramName) { this.paramName = paramName; } public String getParamSex() { return paramSex; } public void setParamSex(String paramSex) { this.paramSex = paramSex; } }
List<Student> findByNameSex (FindByNameSex params);
<select id="findByNameSex" resultType="com.company.domain.Student"> select Sno, Sname, Ssex, Sage, Sdept from Student where Sname like CONCAT('%',#{paramName},'%') and Ssex = #{paramSex} </select>
The simplified writing method is: #{attribute name}
The specific writing method is: #{attribute name, javaType = attribute name type, jdbcType = attribute type in database}
javaType and JDBC type can be obtained in mybatis reflection
7.2.3 order of designation
Parameter 'id' not found. Available parameters are [arg1, arg0, param1, param2]. This sentence means that the id cannot be found. The available parameters are [arg1, arg0, param1, param2]. Therefore, you can use the sequence number of the parameter to refer to the parameter. The first parameter is represented by arg0 or param1, the second parameter is represented by arg1 or param2, and so on (Arg counts from 0, param counts from 1).
<select id="findByNameSex" resultType="com.company.domain.Student"> select Sno, Sname, Ssex, Sage, Sdept from Student where Sname like CONCAT('%',#{arg0},'%') and Ssex = #{arg1} </select>
7.2.4 packaging map
In the main function, the parameters are encapsulated into a map set. key is the name of the mapping file placeholder, and value is the value of the placeholder
This method is not recommended!!!
Because it's very inconvenient for others to read the code
/** * Set query encapsulated by name and gender * @param params Parameter set * @return Student assembly */ List<Student> findByNameSex (Map<String, Object> params);
public void testFindByNameSex() { SqlSession sqlSession = MybatisUtil.getSqlSession(); StudentDao dao = sqlSession.getMapper(StudentDao.class); Map<String, Object> params = new HashMap<>(); params.put("myName", "Zhang"); params.put("mySex", "male"); List<Student> studentList = dao.findByNameSex(params); for (Student student : studentList) { System.out.println(student); } sqlSession.close(); }
<select id="findByNameSex" resultType="com.company.domain.Student"> select Sno, Sname, Ssex, Sage, Sdept from Student where Sname like CONCAT('%',#{myName},'%') and Ssex = #{mySex} </select>
7.3 difference between $and #
select * from student where id=#{studentId}
select * from student where id=?
select * from student where id=${studentId}
select * from student where id=1001
#{} | ${} |
---|---|
Using PreparedStatement | Use Statement |
efficient | Low efficiency |
Avoid SQL injection to a large extent | SQL injection cannot be avoided |
Replaceable table or column names |
8. Package output results
8.1 resultType
The output parameter type should preferably be a fully qualified name.
It can be an alias
8.1.1 simple type parameters
<select id="countStudent" resultType="java.lang.Integer"> select count(*) from Student </select>
8.1.2 object type parameters
be careful:
- The property name of the encapsulated object should be the same as the field name (case insensitive)
- Encapsulated objects must have setter and getter methods
Encapsulate output objects
public class SelectAge { private String sname; private int sage; public String getStuName() { return sname; } public void setStuName(String sname) { this.sname = sname; } public int getStuAge() { return sage; } public void setStuAge(int sage) { this.sage = sage; } @Override public String toString() { return "SelectAge{" + "sname='" + sname + '\'' + ", sage=" + sage + '}'; } }
The output result is set to this object
<select id="selectAge" resultType="com.company.vo.SelectAge"> select Sname, Sage from Student </select>
Perform lookup
@Test public void testSelectAge() { SqlSession sqlSession = MybatisUtil.getSqlSession(); StudentDao dao = sqlSession.getMapper(StudentDao.class); List<SelectAge> stuList = dao.selectAge(); for (SelectAge stu : stuList) { System.out.println(stu); } sqlSession.close(); }
8.1.3 Map
If the returned object is Map, the column name is key and the value is value
However, you cannot return multiple maps unless you use list < map < object, Object > > to encapsulate the map
8.2 resultMap
resultMap is used when the attribute name and column name of the encapsulated object are inconsistent
Configure the mapping relationship between attribute name and field name in sql mapping file
If you do not use resultMap, the property name of the encapsulated object must be the same as the field name (case insensitive)
<?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.company.dao.StudentDao"> <select id="selectAge" resultMap="selectAge"> select Sno, Sname, Sage from Student </select> <resultMap id="selectAge" type="com.company.vo.SelectAge"> <id column="Sno" property="stuId" /> <result column="Sname" property="stuName" /> <result column="Sage" property="stuAge" /> </resultMap> </mapper>
Primary key field use id
Use result for other fields
8.3 as rewriting field names
In order to match the attribute name and field name, the field name can be rewritten in the sql statement to make it consistent with the attribute name when the resultMap is not used
<select id="selectAge" resultType="com.company.vo.SelectAge"> select Sno as stuId, Sname as stuName, Sage as stuAge from Student </select>
8.3 defining encapsulation object aliases
Defines that the returned encapsulated object alias is configured in the main configuration file
The first way:
<typeAliases> <typeAlias type="com.company.vo.SelectAge" alias="SelectAge" /> </typeAliases>
<select id="selectAge" resultType="SelectAge"> select Sname, Sage from Student </select>
The second way:
Name is the package name. The class names of all classes in the package are aliases. At this time, the class names are not case sensitive
<typeAliases> <package name="com.company.domain" /> </typeAliases>
<select id="findByNameSex" resultType="student"> select Sno, Sname, Ssex, Sage, Sdept from Student where Sname like CONCAT('%',#{myName},'%') and Ssex = #{mySex} </select>
9. Dynamic SQL
According to the conditions, different sql statements can be obtained, which can be realized by using the label of mybatis
9.1 if
<if test="Judgment incoming java Property value of the object"> part sql sentence </if>
Example:
<select id="selectAgeIf" resultMap="selectAge"> select Sno, Sname, Sage from Student where Sno > 0 <if test="sname != null and sname != ''"> and Sname = #{sname} </if> <if test="sage > 0"> or Sage > #{sage} </if> </select>
public void testSelectAgeIf() { SqlSession sqlSession = MybatisUtil.getSqlSession(); StudentDao dao = sqlSession.getMapper(StudentDao.class); Student stu = new Student(); stu.setSname("tension"); stu.setSage(19); List<SelectAge> stuList = dao.selectAgeIf(stu); for (SelectAge student : stuList) { System.out.println(student); } }
9.2 where
Used to contain multiple
When only one of multiple IFS is true, a where keyword will be automatically added and redundant and, or, etc. will be removed
When more than one if is true, the redundant and, or, etc. in the first if will be automatically deleted, and the redundant fields in the remaining if will be retained
Example:
<select id="selectAgeIf" resultMap="selectAge"> select Sno, Sname, Sage from Student <where> <if test="sname != null and sname != ''"> and Sname = #{sname} </if> <if test="sage > 0"> or Sage > #{sage} </if> </where> </select>
@Test public void testSelectAgeIf() { SqlSession sqlSession = MybatisUtil.getSqlSession(); StudentDao dao = sqlSession.getMapper(StudentDao.class); Student stu = new Student(); stu.setSname("tension"); stu.setSage(19); List<SelectAge> stuList = dao.selectAgeIf(stu); for (SelectAge student : stuList) { System.out.println(student); } }
9.3 foreach
Loop java array, list collection. It is mainly used in in sql statements
Relevant label parameters:
-
collection
The type of method parameters in the interface. The array is array and the collection is list
-
item
Custom, representing member variables
-
open
Character at the beginning of the loop
-
close
Character at the end of the loop
-
separator
Separator between collection members
Example:
<select id="selectAgeFor" resultMap="selectAge"> select Sno, Sname, Sage from Student where Sno in <foreach collection="list" item="id" open="(" close=")" separator=","> #{id} </foreach> </select>
@Test public void testSelectAgeFor() { SqlSession sqlSession = MybatisUtil.getSqlSession(); StudentDao dao = sqlSession.getMapper(StudentDao.class); List<String> idList = new ArrayList<>(); idList.add("201415010"); idList.add("201415011"); idList.add("201415012"); List<SelectAge> stuList = dao.selectAgeFor(idList); for (SelectAge student : stuList) { System.out.println(student); } }
The use of foreach tags has many subtleties:
<select id="selectAgeFor" resultMap="selectAge"> select Sno, Sname, Sage from Student where Sno in <foreach collection="list" item="id" open="(" close=")"> #{id} , </foreach> </select>
result:
select Sno, Sname, Sage from Student where Sno in ( ? , ? , ? , )
<select id="selectAgeFor" resultMap="selectAge"> select Sno, Sname, Sage from Student where Sno in( <foreach collection="list" item="id"> #{id} , </foreach> -1, ) </select>
result:
select Sno, Sname, Sage from Student where Sno in( ? , ? , ? , -1, )
If an object is encapsulated in a collection or array and a property needs to be fixed in the traversal object, you can use #{object. Property name} traversal
9.4 code reuse
Multiple sql statements can be reused
<sql id="allAttr"> select Sno, Sname, Ssex, Sage, Sdept from Student </sql>
<select id="findAll" resultType="com.company.domain.Student"> <include refid="allAttr" /> where Ssex = 'male' </select> <select id="findByNameSex" resultType="student"> <include refid="allAttr" /> where Sname like #{name} and Ssex = #{sex} </select>
10. Paging output
The results can be displayed in pages through a plug-in. This function is not provided by mybatis.
10.1 import dependencies
<dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper</artifactId> <version>5.2.0</version> </dependency>
10.2 main profile import plug-in
<!-- When configuring plug-ins, be sure to put them in the environments front --> <plugins> <plugin interceptor="com.github.pagehelper.PageInterceptor" /> </plugins> <environments default="development"> <environment id="development"> ...
10.3 paging
@Test public void testFindAll() { SqlSession sqlSession = MybatisUtil.getSqlSession(); StudentDao dao = sqlSession.getMapper(StudentDao.class); // Implement paging operation PageHelper.startPage(1,5); List<Student> studentList = dao.findAll(); for (Student student : studentList) { System.out.println(student); } sqlSession.close(); }
This PageHelper.startPage(1,5); Represents the five rows of data on the first page. If you want to view the five rows of data on the second page, it is PageHelper.startPage(2,5);
Multiple sql statements can be reused
<sql id="allAttr"> select Sno, Sname, Ssex, Sage, Sdept from Student </sql>
<select id="findAll" resultType="com.company.domain.Student"> <include refid="allAttr" /> where Ssex = 'male' </select> <select id="findByNameSex" resultType="student"> <include refid="allAttr" /> where Sname like #{name} and Ssex = #{sex} </select>
10. Paging output
The results can be displayed in pages through a plug-in. This function is not provided by mybatis.
10.1 import dependencies
<dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper</artifactId> <version>5.2.0</version> </dependency>
10.2 main profile import plug-in
<!-- When configuring plug-ins, be sure to put them in the environments front --> <plugins> <plugin interceptor="com.github.pagehelper.PageInterceptor" /> </plugins> <environments default="development"> <environment id="development"> ...
10.3 paging
@Test public void testFindAll() { SqlSession sqlSession = MybatisUtil.getSqlSession(); StudentDao dao = sqlSession.getMapper(StudentDao.class); // Implement paging operation PageHelper.startPage(1,5); List<Student> studentList = dao.findAll(); for (Student student : studentList) { System.out.println(student); } sqlSession.close(); }
This PageHelper.startPage(1,5); Represents the five rows of data on the first page. If you want to view the five rows of data on the second page, it is PageHelper.startPage(2,5);