Official Chinese version of MyBatis: https://mybatis.org/mybatis-3/zh/index.html
Mybatis website of W3School: https://www.w3cschool.cn/mybatis/
Introduction (Ibatis 3 is called MyBatis) MyBatis: it is a lightweight ORM (object relationship mapping) and semi-automatic persistence layer framework, The function is: Turn the hard compilation and high coupling of the original JDBC SQL and JAVA code into the separation of SQL and JAVA: the SQL statement has the XML configuration of developers Clear functional boundaries: write SQL for SQL and JAVA for JAVA
MyBatis specification Native configuration, POM Dao layer - Interface Dao config domainmapper - implementation class The new method, POM mapper layer - Interface config domainmapper - requires only the interface The difference between the two is that the implementation class must be written in the native way Note: if the domain attribute name is not the same as the table field name of sql, it needs to be aliased (that is, if you want to run successfully, the domain attribute name received should be the same as the table field)
technological process: POM+config.xml+sqlMapper.xml config.xml: core global configuration file: contains data source connection pool information, transaction manager information, and system operating environment information sqlMaper.xml: save all sql mapping information and extract sql Create a SqlSession object (and connliction are not thread safe, and a new object should be used each time): represents a session with the database Because the thread is unsafe, the Serializable interface should be pojo implemented
Entry code implementation
Getting started code structure
Maven coordinate file configuration
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.49</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>First configure a mapping file usermapper xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD MyBatis 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="UserMapper">
<select id="findAll" resultType="com.zanglikun.domain.User">
select * from user
</select>
</mapper>After configuring a core configuration file sqlmapconfig 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>
<environments default="dev1">
<environment id="dev1">
<!-- Which transaction manager is used-->
<transactionManager type="JDBC"></transactionManager>
<!-- Data source type -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
<property name="username" value="root"/>
<property name="password" value="740969606"/>
</dataSource>
</environment>
</environments>
<!-- When loading the mapping file in the future, just import it directly below -->
<mappers>
<mapper resource="com/zanglikun/mapper/UserMapper.xml"></mapper>
</mappers>
</configuration>Create a database named mybatis with the following contents:
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for user -- ---------------------------- DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(11) NOT NULL, `username` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL, `password` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL, `age` int(11) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of user -- ---------------------------- INSERT INTO `user` VALUES (1, 'zhangsan123', '123', 12); INSERT INTO `user` VALUES (2, 'lisi456', '456', 45); SET FOREIGN_KEY_CHECKS = 1;
Test code
@Test
public void test1() throws IOException {
// Get core profile
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
// Get Session factory object
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
// Get Session session object
SqlSession sqlSession = build.openSession();
// Perform operation
List<Object> list = sqlSession.selectList("UserMapper.findAll");
System.out.println(list.get(0));
System.out.println(list.get(1));
// Release resources
sqlSession.close();
}Test results:
Profile introduction:
Attention
#{after setting parameterType, you can directly fill in the property name of the object}
mybatis is a JDBC transaction by default, which needs to be submitted manually
// Mysql will eventually commit the transaction
sqlSession.commit();MyBatis core profile
1 environments tab
2 mappers label
3 proerties label
Under the configuration tab
<properties resource="jdbc.properties"></properties>
......
<!--Data source type-->
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
......4 typeAliases label
At the same time, Mybatis also provides us with some common types of aliases
<!-- User defined aliases (please place them in order) properties (after label) -->
<typeAliases>
<typeAlias type="com.zanglikun.domain.User" alias="user"></typeAlias>
</typeAliases>Mybatis API
This is the most easily overlooked and his core
SqlSessionFactory builder is used to create SqlSessionFactory
SqlSessionFactory
openSession() // Set whether to submit automatically openSession(boolean autoCommit)
The SqlSession session object is not automatically submitted by default and needs to be submitted manually. It is a very powerful class of Mybatis. It can control the methods of executing statements, submitting transactions and obtaining mapper instances.
Methods of operating transactions
void commit() void rollback()
Beginner level advanced chapter
In the actual production process, we will not use the traditional method: create Resources in the Service implementation layer, read the core configuration file, and then create the factory object SqlSessionFactory by SqlSessionFactory builder, and then create the SqlSession object for Sql execution, etc!
Therefore, if we use the Mybatis dynamic interface proxy method, we need to comply with the following specifications
Specific implementation mode
In the mapper object obtained by obtaining SqlSession, use usermapper findByid(1)； Query and get results.
In addition to changing the mode, we also need to introduce:
Dynamic SQL is dynamic SQL
Common tags are if, choose, trim, foreach, and where
Where tag: equivalent to the where 1=1 identity. The and or condition is automatically processed when judging the condition.
if tag: instance:
<select id = "findAll" paramerType="string">
select * from user
<where>
<if test="username != null">
username = #{username}
</if>
</where>
</select>foreach tag
Equivalent to select * from user where username in (LIST)
<select id = "findAll" paramerType=="list">
select * from user
<where>
<foreach collection="list" open="age in(" close=")" item= "id" separator=",">
#{id}
</foreach>
</where>
</select>include tag and sql tag: extracted statements
<sql id = "ABC">select * from user</sql>
<select id = "findAll" paramerType="string">
<include refid = "ABC"></include>
<where>
<if test="username != null">
username = #{username}
</if>
</where>
</select>trim label: generally used for insertion
- Prefix: prefix the sql statement in the trim tag
- Suffix: suffix the sql statement in the trim tag
- prefixOverrides: specifies to remove redundant prefix contents, such as prefixOverrides = "AND | OR", and remove redundant prefix "and" or "of sql statement in trim tag.
- suffixOverrides: specifies to remove redundant suffix content.
<insert>
insert into user
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="username != null">
username,
</if>
<if test="password != null">
username,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="username != null">
#{username,jdbcType=VARCHAR},
</if>
<if test="password != null">
#{password,jdbcType=VARCHAR},
</if>
</trim>
</insert>Set tag: it is generally used to update and automatically add the set keyword, and eliminate any irrelevant commas appended to the end of the condition
Note: when using if combination for set keyword, each if must have a comma at the end
<update id = "changeCompany" paramerType="com.zanglikun.pojo.Company">
update company_order
<set>
<if test="companyId != null">
company_id = #{companyId,jdbcType=BIGINT},
</if>
<if test="orderCreated != null">
order_created = #{orderCreated,jdbcType=TIMESTAMP},
</if>
</set>
where order_id = #{orderId,jdbcType=BIGINT}
</update>#The difference between {} and ${}
#{} is precompiled, while {} is directly taken out. sql statements with sql injection risk are precompiled and in the form of placeholders, and then execute sql that copies placeholders, while {} is directly a value. Scope of action: field names and table names do not support precompiling, so they must be used
Mybatis core profile drill down
Typehandles whether Mybatis sets a parameter in the PrepareStatement or takes a value from the result set, it will convert the obtained value into Java type in an appropriate way through this type converter.
Of course, it must be difficult to understand this directly. We need to solve it with examples:
A field in our database is bigInt timestamp, but we need to find out from the database that the opening time is the Date type under the Java time package.
A User object has a field. The birthday type is Date
When we insert an object directly, we will make an error.
terms of settlement:
Custom TypeHandles
Why basetypehandle < T >?
When we inherit BaseTypeHandel, after specifying the generic type, some methods that must be overridden are returned in this way.
package com.zanglikun.handler;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
/**
* @author : zanglikun
* @date : 2021/3/17 16:49
* @Version: 1.0
* @Desc : Type conversion
*/
public class DateTypeHandler extends BaseTypeHandler<Date> {
// Convert the java type to the timestamp type required by the database
@Override
public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
long time = date.getTime();
preparedStatement.setLong(i,time);
}
// s is the field name of the sql query result
// resultSet is the result set
@Override
public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
long aLong = resultSet.getLong(s);
Date date = new Date(aLong);
return date;
}
// i is the field location index of the sql result
// resultSet is the result set
@Override
public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
long aLong = resultSet.getLong(i);
Date date = new Date(aLong);
return date;
}
// i is the field location index of the sql result
// callableStatement is not very clear
@Override
public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
long aLong = callableStatement.getLong(i);
Date date = new Date(aLong);
return date;
}
} <typeHandlers>
<typeHandler handler="com.zanglikun.handler.DateTypeHandler"></typeHandler>
</typeHandlers>After completion, the timestamp can also be mapped to Data in the future
Plugins tag
Mybatis uses a third-party PageHelper plug-in to enhance paging
Guide Package
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.4</version>
</dependency>
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>1.0</version>
</dependency>Profile import
<!-- plug-in unit -->
<plugins>
<!-- PageHelper 5.0 All subsequent versions are used PageInterceptor If it is 4.0 Please search Baidu by yourself-->
<plugin interceptor="com.github.pagehelper.PageInterceptor">
</plugin>
</plugins>Use to set the current page and the number of bars displayed per page
PageHelper.startPage(page,pagesize); // Paging
PageHelper.clearPage();
PageHelper.startPage(page, pageSize,false); // The total obtained through PageInfo is - 1
PageHelper.startPage(page, pageSize,true); // Equivalent to PageHelper startPage(page, pageSize);
PageHelper.startPage(page, pageSize); // This statement will have count by default, which can be counted through pageinfo Gettotal(), get the number of query results.
PageHelper.orderBy("id desc");// Sort the query results in descending order by idsummary
Here, we must reflect on the specific purpose of these labels, otherwise, we will forget them after a long time!!!
For advanced content, go to the new article: https://www.zanglikun.com/3320.html
Note that there is an error in the configuration file. Please check the file order. How do you see the prompt? If there is an error in the configuration file, the following problems will be thrown when you directly run the project:
The content query of "configuration" must match in the following order "(properties?,settings?,typeAliases?,typeHandlers?,objectFactory?,objectWrapperFactory?,reflectorFactory?,plugins?,environments?,databaseIdProvider?,mappers?)".
Special instructions: The brilliance of solving problems hides the pain of knocking bugs. All things enter samsara, and no one can escape! The above articles are my actual operation and written notes. There will be no full-text embezzlement of other people's articles! Please don't embezzle directly!