Introduction to MyBatis
MyBatis was predecessored by iBatis, which is a Java-based data persistence layer/object relational mapping (ORM) framework.
MyBatis is an encapsulation of JDBC, allowing developers to only focus on SQL itself without spending too much effort to process JDBC procedural code such as registering drivers, setting parameters, creating Connection/Statement, parsing result sets, etc. MyBatis configures Statement based on XML/annotation, executes SQL, and maps the execution results into Java objects, greatly reducing the difficulty of database development.
MyBatis is a first class persistence framework with support for custom SQL, stored procedures and advanced mappings. MyBatis eliminates almost all of the JDBC code and manual setting of parameters and retrieval of results. MyBatis can use simple XML or Annotations for configuration and map primitives, Map interfaces and Java POJOs (Plain Old Java Objects) to database records.
MyBatis project address/online documentation.
First meet MyBatis
Using MyBatis requires adding the following dependencies in pom.xml:
<code><dependency><groupid>org.mybatis</groupid><artifactid>mybatis</artifactid><version>3.3.0</version></dependency><dependency><groupid>mysql</groupid><artifactid>mysql-connector-java</artifactid><version>5.1.36</version></dependency></code>
Select
Configure mybatis/mybatis-configuration.xml
As the global configuration file of MyBatis, it is configured with MyBatis's running environment information (such as data source/mapper file, etc.).
<code><code><!--{cke_protected}{C}%3C!%2D%2D%3Fxml%20version%3D%221.0%22%20encoding%3D%22UTF-8%22%20%3F%2D%2D%3E--><configuration><environments default="development"><environment id="development"><!--{cke_protected}{C}%3C!%2D%2D%20%E9%85%8D%E7%BD%AEJDBC%E4%BA%8B%E5%8A%A1%E7%AE%A1%E7%90%86%2D%2D%3E--><transactionmanager type="JDBC"><!--{cke_protected}{C}%3C!%2D%2D%20%E9%85%8D%E7%BD%AE%E6%95%B0%E6%8D%AE%E6%BA%90%2D%2D%3E--><datasource type="POOLED"><property name="driver" value="com.mysql.jdbc.Driver"><property name="url" value="jdbc:mysql://host:port/db?characterEncoding=utf-8"><property name="username"><property name="password" value="password"></property></property></property></datasource></transactionmanager></environment></environments><!--{cke_protected}{C}%3C!%2D%2D%20%E5%8A%A0%E8%BD%BDmapper%E6%98%A0%E5%B0%84%E6%96%87%E4%BB%B6%20%2D%2D%3E--><mappers><mapper resource="mybatis/mapper/UserDAO.xml"></mapper></mappers></configuration></code>Writing UserDAO (mapper map)
The most core part of MyBatis is configured with SQL statements that operate the database:
<code><code><code><!--{cke_protected}{C}%3C!%2D%2D%3Fxml%20version%3D%221.0%22%20encoding%3D%22UTF-8%22%20%3F%2D%2D%3E--><mapper namespace="namespace"><select id="selectUserById" parametertype="java.lang.Integer" resulttype="com.fq.domain.User">SELECT * FROM user WHERE id = #{id};</select><select id="selectUserByName" parametertype="java.lang.String" resulttype="com.fq.domain.User">SELECT * FROM user WHERE name LIKE '%${value}%';</select></mapper></code></code> Attribute description
namespace namespace, used to isolate SQL statements
parameterType defines the SQL input mapping type, and MyBatis obtains parameters from the input object through OGNL to pass into SQL statements.
resultType Defines the SQL output mapping type. MyBatis maps a row of records of SQL query results to the type specified by resultType.
The mapper mapping file name includes UserDAO.xml/UserMapper.xml/User.xml and other forms. They are generally stored in the mapper directory of the same level as mybatis-configuration.xml. Since its main function is to define the relationship between SQL statements and mapping, it is generally collectively called mapper mapping files.
Define PO class
The main function of PO class is SQL (input/output) mapping, which usually corresponds to database tables:
<code><code><code><code><code>/*** @author jifang* @since 15/12/31 2:27 pm.*/public class User {private Integer id;private String name;private String password;public User() {}public User(Integer id, String name, String password) {this.id = id;this.name = name;this.password = password;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}@Overridepublic String toString() {return "User{" +"id=" + id +", name='" + name + '/'' +", password='" + password + '/'' +'}';}}</code></code></code></code>UserDAO (Java object)
Get SqlSession, execute SQL statements, and get the mapping result:
<code><code><code><code><code><code><code>/*** @author jifang* @since 16/2/24 6:15 pm.*/public class UserDAO {private SqlSessionFactory factory;@Beforepublic void setUp() throws IOException {String resource = "mybatis/mybatis-configuration.xml";factory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream(resource));}@Testpublic void selectUserById() {try (SqlSession session = factory.openSession()) {User user = session.selectOne("namespace.selectUserById", 1);System.out.println(user);}}@Testpublic void selectUserByName() {try (SqlSession session = factory.openSession()) {List<user> users = session.selectList("namespace.selectUserByName", "student"); for (User user : users) {System.out.println(user);}}}}</user></code></code></code></code></code>Insert
mapper
<code><code><code><code><code><code><code><code><code><code><insert id="insertUser" parametertype="com.fq.domain.User">INSERT INTO user(name, password) VALUES(#{name}, #{password});</insert></code></code></code></code>UserDAO
<code><code><code><code><code><code><code><code><code><code><code>@Testpublic void insertUser() {try (SqlSession session = factory.openSession()) {User user = new User();user.setName("new_name1");user.setPassword("new_password");session.insert("namespace.insertUser", user);session.commit();}}</code></code></code></code></code></code> Return by auto-increment primary key
Modify the mapper file and add it, and you can return the auto-increment primary key of MySQL (i.e. the ID generated when the data is just inserted):
<code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><insert id="insertUser" parametertype="com.fq.domain.User"><selectkey keyproperty="id" order="AFTER" resulttype="java.lang.Integer">SELECT LAST_INSERT_ID();</selectkey>INSERT INTO user(name, password) VALUES(#{name}, #{password});</insert></code></code></code></code></code>UserDAO
<code><code><code><code><code><code><code><code><code><code><code><code><code><code><code>@Testpublic void insertUser() {try (SqlSession session = factory.openSession()) {System.out.println(session);User user = new User(null, "new_name", "new_password");session.insert("namespace.insertUser", user);// You need to get the autoincrement primary key session.commit();System.out.println(user.getId());}}</code></code></code></code></code></code></code></code></code>This function can also be completed through the useGeneratedKeys/keyProperty properties, please refer to the MyBatis documentation for details.
Update
mapper
<code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><update id="updateUserById" parametertype="com.fq.domain.User">UPDATE user SET name = #{name}, password = #{password} WHERE id = #{id};</update></code></code></code></code></code></code></code></code></code></code>UserDAO
<code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code>@Testpublic void updateUserById() {try (SqlSession session = factory.openSession(true)) {session.update("namespace.updateUserById",new User(1, "feiqing", "ICy5YqxZB1uWSwcVLSNLcA=="));}}</code></code></code></code></code></code></code></code></code>Delete
mapper
<code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><delete id="deleteUserById" parametertype="java.lang.Integer">DELETE FROM user WHERE id = #{id};</delete></code></code></code></code></code></code></code></code></code></code>UserDAO
<code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code>@Testpublic void deleteUserById() {try (SqlSession session = factory.openSession(true)) {session.delete("namespace.deleteUserById", 51615);}}</code></code></code></code></code></code></code></code></code></code></code>summary
#{}/${}
#{}: Represents a placeholder, implements setting a value to the PreparedStatement placeholder (#{} represents a placeholder?), and automatically converts Java type to JDBC type (so #{} can effectively prevent SQL injection). #{} can receive simple type or PO attribute values. If parameterType transmits a single simple type value, the #{} can be value or other names in the #{} curly braces. ${} can be used to splice SQL strings. The parameterType content can be spliced in SQL without JDBC type conversion. ${} can be used to receive simple type or PO attribute values. If parameterType transmits a single simple type value, the ${} can only be used to value in the ${} curly braces.
Although ${} cannot prevent SQL injection, sometimes ${} is very convenient (such as order by sorting, column names need to be passed into SQL through parameters, then ORDER BY ${column} is used, and #{} cannot be used to implement this function (see JDBC basic discussion on PreparedStatement for details).
SqlSession
Provide methods to operate the database (such as: selectOne/selectList). However, SqlSession is thread-insecure, so it is best to define it as a local variable to use.
Advantages of MyBatis (compared with JDBC)
SQL is written in Java code, which makes it difficult to maintain. MyBatis writes SQL in mapper, and XML is separated from Java code. It is complicated to pass parameters to SQL statements (such as: SQL where conditions are different, SQL data types are different from Java). MyBatis automatically maps Java objects to SQL statements through parameterType. Result set parsing is troublesome (SQL changes lead to parsing code changes, SQL data types are different from Java). MyBatis automatically maps SQL execution results into Java objects through resultType.
Attachment: It is best to add a log system implementation (logback/log4j) to pom.xml, so that log information will be printed when debugging the program, making it easier to check errors. Take logback as an example:
pom.xml
<code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><dependency><groupid>ch.qos.logback</groupid>logback-classic</artifactid><version>1.1.2</version></dependency></code></code></code></code></code></code></code></code></code></code></code></code></code></code>
logback.xml
<code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><configuration><property name="logRoot" value="/data/logs"><property name="pattern" value="%d{HH:mm:ss.SSS} [%thread] %-5level %logger{0} - %msg%n"><appender name="STDOUT"><encoder><pattern>${pattern}</pattern></encoder></appender><appender name="FILE"><rollingpolicy><filenamepattern>${logRoot}/common-server.%d{yyyy-MM-dd}.log</filenamepattern><maxhistory>7</maxhistory></rollingpolicy><encoder><pattern>${pattern}</pattern></encoder></appender><root level="DEBUG"><appender-ref ref="STDOUT"><appender-ref ref="FILE"></appender-ref></appender-ref></root></property></property></configuration></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>For other details about MyBatis logs, please refer to the MyBatis document log section.
DAO Development
There are two ways to develop DAO using MyBatis, original DAO development and Mapper mapping DAO development.
Original DAO Development Original DAO development requires developers to write DAO interfaces and DAO implementations, such as querying user information based on ID:
mapper (same as before)
<code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><select id="selectUserById" parametertype="java.lang.Integer" resulttype="com.fq.domain.User">SELECT * FROM user WHERE id = #{id};</select></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code> UserDAO interface
<code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code>/*** @author jifang* @since 16/2/22 10:20 am.*/public interface UserDAO {User selectUserById(Integer id) throws Exception;}</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code> UserDAO implementation
<code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code>public class UserDAOImpl implements UserDAO {private SqlSessionFactory factory;public UserDAOImpl(SqlSessionFactory factory) {this.factory = factory;}@Overridepublic User selectUserById(Integer id) throws Exception {SqlSession session = factory.openSession();User user = session.selectOne("namespace.selectUserById", id);session.close();return user;}}</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code> Client
<code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code>public class MyBatisClient {@Testpublic void originalClient() throws Exception {UserDAO dao = new UserDAOImpl(new SqlSessionFactoryBuilder().build(ClassLoader.getSystemResourceAsStream("mybatis/mybatis-configuration.xml")));User user = dao.selectUserById(1);System.out.println(user);}}</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>Problems in original DAO development:
1) There are many procedural codes in the DAO implementation method body.
2) Calling the SqlSession method (select/insert/update) requires specifying the id of the Statement, which is hard-coded, which is not conducive to code maintenance.
Mapper mapping development
The mapper mapping development method only requires writing a DAO interface, and MyBatis dynamically creates an interface implementation based on the interface definition and SQL statements in the mapper file.
mapper
<code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><!--?xml version="1.0" encoding="UTF-8" ?--><mapper namespace="com.fq.mybatis.UserDAO"><select id="selectUserById" parametertype="java.lang.Integer" resulttype="com.fq.domain.User">SELECT * FROM user WHERE id = #{id};</select></mapper></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>Note: At this time, the namespace must be the same as the fully qualified name of the UserDAO interface.
The UserDAO interface is the same as before, but the UserDAOImpl Client is no longer used.
<code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code>/*** @author jifang* @since 16/2/22 2:57 pm.*/public class MyBatisClient {private SqlSession session;private SqlSessionFactory factory;@Beforepublic void setUp() {factory = new SqlSessionFactoryBuilder().build(ClassLoader.getSystemResourceAsStream("mybatis/mybatis-configuration.xml"));session = factory.openSession();}@Testpublic void mappperClient() throws Exception {UserDAO dao = session.getMapper(UserDAO.class);User user = dao.selectUserById(1);System.out.println(user);}@Afterpublic void tearDown() {session.close();}}</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>Mapper mapping development methods need to follow the following specifications:
The namespace in the mapper file is the same as the fully qualified name of the DAO interface; the id of the Statement in the mapper file is the same as the method name of the DAO interface; the parameterType/resultType of the Statement in the mapper file is the same as the parameter/resultType of the DAO method.
Mapper mapping
The main function of mapper mapping files (such as UserDAO.xml) is to define SQL statements (each SQL is a Statement), which is the core of MyBatis.
MyBatis officially recommends using mapper mapping method to develop DAO, so we will not introduce too much about the development of the original DAO in the future.
Input Mapping
Multiple formal parameters
The previous example of passing simple types has been used, so I will not repeat it here. When multiple formal parameters need to be passed, the parameterType parameter is no longer necessary:
mapper
<code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><update id="updateUserById">UPDATE user SET name = #{1}, password = #{2} WHERE id = #{0};</update></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>UserDAO
<code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code>void updateUserById(Integer id, String name, String password) throws Exception;</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>
Incoming PO
MyBatis uses OGNL expression to parse object attribute values:
mapper
<code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><select id="selectUserByNamePassword" parametertype="com.fq.domain.User" resulttype="com.fq.domain.User">SELECT *FROM userWHERE name = #{name} AND password = #{password};</select></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>UserDAO
<code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code>User selectUserByNamePassword(User user) throws Exception;</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>
mapper
<code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><select id="selectUserByMap" parametertype="java.util.Map" resulttype="com.fq.domain.User">SELECT *FROM userWHERE name = #{name} AND password = #{password};</select></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>#{}The corresponding key of the Map in the curly braces.
UserDAO
<code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code>User selectUserByMap(Map<string, object=""> map) throws Exception;</string,></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>
Output Mapping
Output simple type
mapper
<code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><select id="selectUserCount" parametertype="java.lang.String" resulttype="java.lang.Integer">SELECT count(*)FROM userWHERE name LIKE '%${value}%';</select></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>UserDAO
<code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code><code>Integer selectUserCount(String name) throws Exception;</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>
Returning a simple type must ensure that the query result has only one row of records, and the value of the first field is finally converted to the output type.
Output PO object/list
Two types of output have been demonstrated before (selectUserById/selectUserByName used the original DAO development method at that time, but the mapper definition form was similar), so I will only make a simple summary here:
The resultType defined in the mapper when outputting a single PO object is the same as the resultType defined in the mapper; the SQL query result must be guaranteed to be a single piece of data, and it is called internally using the selectOne method; the output PO list indicates that the query result may be multiple, and it is called internally using the selectList method, and the interface return value can be carried by List/Set.
Output Map
The output PO object can be used instead to use Map output, with the field name as key and the field value as value.
mapper
<code><select id="selectUserLikeName" resulttype="java.util.Map">SELECT *FROM userWHERE name LIKE '%${value}%';</select></code>UserDAO
<code><code>List<map<string, object="">> selectUserLikeName(String name) throws Exception;</map<string,></code></code>
resultMap
resultType can map the query result to PO, but the premise is that the PO property name and the SQL field name must be the same. If it is inconsistent, the corresponding mapping can be made through resultMap:
mapper
<code><code><code><resultmap id="userMap" type="com.fq.domain.User"><id column="user_id" property="id"><result column="user_name" property="name"><result column="user_password" property="password"></result></id></resultmap><select id="selectUserByName" parametertype="java.lang.String" resultmap="userMap">SELECTid user_id,name user_name,password user_passwordFROM userWHERE name = #{name};</select></code></code></code>UserDAO interface is the same as before.