1. The CRUD operations

1.1 select

  • The Select tag is one of the most commonly used tags in Mybatis

  • Select statements have many properties that can be configured in detail for each SQL statement

    • SQL statement return value typeresultType. [Full class name or alias]
    • The type of argument passed to the SQL statementparameterType. [Universal Map, you can try to use more]
    • A unique identifier in a namespace
    • The method name in the interface corresponds to the SQL statement ID 1-1 in the mapping file

  • Query users by ID

    1. Add the corresponding method in UserMapper

      public interface UserMapper {
    // Query all users
    List<User> selectUser(a);
    // Query users by id
    User selectUserById(int id);
}
Copy the code

    2. Add a SELECT statement to the XML configuration file

      <select id="selectUserById" resultType="top.linzeliang.pojo.User" parameterType="int">
    select *
    from user
    where id = #{id};
</select>
Copy the code

    3. test

  • Query users by password and name

    1. Pass parameters directly in the method

      // Query the user by password and name
User selectUserByNP(@Param("username") String username,@Param("pwd") String pwd);

/*  */
Copy the code

    2. Using universal Map

      • In the interface method, the parameters are passed directly to the Map

        User selectUserByNP(Map<String, Object> map);
Copy the code

      • When you write an SQL statement, you need to pass the parameter type, which is map (when used, SQL takes the value of the map value of the corresponding key).

        <select id="selectUserByNP" parameterType="map" resultType="top.linzeliang.User">
    select * from user where name = #{username} and pwd = #{pwd}
</select>
Copy the code

  • If there are too many parameters, we can consider using Map directly. If there are too few parameters, we can pass them directly

The 1.2 update

  • Modifying User Information

    1. Writing interface methods

      // Modify a user
int updateUser(User user);
Copy the code

    2. Write the corresponding SQL statement in the configuration file

      <update id="updateUser" parameterType="top.linzeliang.pojo.User">
    update user set name=#{name},pwd=#{pwd} where id = #{id}
</update>
Copy the code

    3. test

1.3 the delete

  • Delete a user based on its ID

    1. Writing interface methods

      // Delete the user based on the ID
int deleteUser(int id);
Copy the code

    2. Write SQL statements in the configuration file

      <delete id="deleteUser" parameterType="int">    delete from user where id = #{id}</delete>
Copy the code

    3. Test (commit the transaction or the changes won’t take effect)

  • Conclusion:

    • About the increase, deletion and change to submit business!
    • Interface all common parameters, as far as possible to write @param parameter, especially multiple parameters, must write!
    • Sometimes, depending on your business needs, you can consider using map to pass parameters!
    • In order to standardize operation, in the SQL configuration file, we try to write Parameter Parameter and resultType!

2

  1. Concatenate wildcards through Java statements

    String name = "%a%"; List<User> names = mapper.selectLike(name);Copy the code
    <select id="selectLike" resultType="top.linzeliang.pojo.User" parameterType="string">    select *    from user    where name like #{name};</select>
Copy the code

  2. Concatenate wildcards in SQL statements, but cause SQL injection problems

    <select id="selectLike" resultType="top.linzeliang.pojo.User">    select *    from user    where name like "%${name}%"; </select>Copy the code

  3. Concatenation via the CONCAT function

    <select id="selectLike" resultType="top.linzeliang.pojo.User">    select *    from user    where name like CONCAT('%', #{name}, '%');</select>
Copy the code
  • Conclusion:
    • #{} is a pre-compiled process, MyBatis will process #{} in SQL instead of? , and then call the set method in PreparedStatement to assign the value. When the string is passed in, single quotation marks are placed around the value. Placeholders are used to improve efficiency and prevent SQL injection
    • ${} indicates the concatenation of the SQL string. Concatenate the contents of the received parameters into THE SQL without any modification, which may cause SQL injection

3. Configure resolution

3.1 Core Configuration File

  • Mybatis -config. XML System core configuration file

  • The MyBatis configuration file contains Settings and property information that will deeply affect MyBatis behavior

  • The configuration list is as follows ** (note the order of element nodes, error will be reported) ** :

    Configuration properties Settings typeAliases typeHandlers objectFactory plugins environment DataSource databaseIdProvider MappersCopy the code

3.2 environments elements

  • MyBatis multiple runtime environment, to map SQL to multiple database, must specify one of the default runtime environment (specified by default)

    <environments default="development">    <environment id="development">        <transactionManager type="JDBC">            <property name="..." value="..."/>        </transactionManager>        <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>
Copy the code

  • Child node element: environment

    • The dataSource element uses the standard JDBC dataSource interface to configure JDBC connection object resources

    • The database must be configured (Spring takes care of it after integrating with Spring)

    • There are three built-in data source types

      Type = "[UNPOOLED | POOLED | JNDI]")Copy the code

    • UNPOOLED: This implementation of the data source simply opens and closes the connection each time it is requested

    • POOLED: This implementation of data sources organizes JDBC connection objects using the concept of “pooling,” a popular processing method that enables concurrent Web applications to respond quickly to requests

    • JNID: This data source is implemented for use in a container such as Spring or an application server, which can centrally or externally configure the data source and then place a reference to the JNDI context

    • There are also many third-party implementations of data sources, such as DBCP, C3P0, Druid, and so on

  • Child element node: transactionManager

    • Transaction manager

    • grammar

      <! Of grammar - ><transactionManager type="[ JDBC | MANAGED ]"/>
Copy the code

    • Neither of these transaction manager types requires any properties to be set

3.3 mappers elements

  • Mapper: Defines the mapping SQL statement file

  • Now that the other elements of MyBatis’ behavior have been configured, we are ready to define the SQL mapping statement. But first we need to tell MyBatis where to find these statements. Java doesn’t provide a good way to automate lookups, so the best way is to tell MyBatis where to find the mapping file. You can use resource references relative to the classpath, or fully qualified resource locators (including file:/// URL), or class names and package names, etc. Mapper is one of the most core components in MyBatis. Before MyBatis 3, only XML mapper was supported, that is, all SQL statements must be configured in XML files. From MyBatis 3, interface mapper is also supported. This mapper allows annotations to define SQL statements in Java code, which is very concise.

  • Introduction of resources:

    1. Use resource references relative to the classpath

      <mappers>  <mapper resource="org/mybatis/builder/PostMapper.xml"/></mappers>
Copy the code

    2. Using fully qualified resource locators (urls)

      <mappers>  <mapper url="file:///var/mappers/AuthorMapper.xml"/></mappers>
Copy the code

    3. The fully qualified class name of the class implemented using the mapper interface requires that the profile name and interface name be the same and in the same directory

      <mappers>  <mapper class="org.mybatis.builder.AuthorMapper"/></mappers>
Copy the code

    4. Register all mapper interface implementations within the package as mapper, but only if the configuration file name is the same as the interface name and is located in the same directory

      <mappers>  <package name="org.mybatis.builder"/></mappers>
Copy the code

3.4 the Properties optimization

  • Database these properties are externally configurable and dynamically replaceable, either in a typical Java properties file or through child elements of the Properties element. Specific official documents

  • Optimized configuration file:

    1. Create db.properties in the resource directory

      driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/mybatis? useSSL=true&useUnicode=true&characterEncoding=utf8username=rootpassword=root
Copy the code

    2. Import the file into the properties configuration file ~

      <configuration>    <! Import properties file -->    <properties resource="db.properties"/>    <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>    <mappers>        <mapper resource="mapper/UserMapper.xml"/>    </mappers></configuration>
Copy the code

3.5 typeAliases optimization

  • A type alias is a short name for a Java type. It is only relevant for XML configuration and exists only to reduce the redundancy of fully qualified names of classes

  1. When configured this way, User can be used anywhere top.linzeliang.pojo.user is used

    <! -- Configure alias, pay attention to the order --><typeAliases>    <typeAlias type="top.linzeliang.pojo.User" alias="User"/></typeAliases>
Copy the code

  2. It is also possible to specify a package name. MyBatis will search for the desired Java Bean under the package name. Each Java Bean in the package top.linzeliang.pojo will be alias using the Bean’s lowercase unqualified class name without annotations

    <typeAliases>    <package name="top.linzeliang.pojo"/></typeAliases>
Copy the code

  3. If there are annotations, the alias is their annotation value

    @Alias("user")public class User {    ...}
Copy the code

  4. There are also some MyBatis default type aliases, can be viewed on the official website

3.6 Other Configurations

  1. Settings

    • Lazy loading

    • The logging implementation

    • Cache on/off

    • An example of a fully configured Settings element is as follows:

      <settings>  <setting name="cacheEnabled" value="true"/>  <setting name="lazyLoadingEnabled" value="true"/>  <setting name="multipleResultSetsEnabled" value="true"/>  <setting name="useColumnLabel" value="true"/>  <setting name="useGeneratedKeys" value="false"/>  <setting name="autoMappingBehavior" value="PARTIAL"/>  <setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>  <setting name="defaultExecutorType" value="SIMPLE"/>  <setting name="defaultStatementTimeout" value="25"/>  <setting name="defaultFetchSize" value="100"/>  <setting name="safeRowBoundsEnabled" value="false"/>  <setting name="mapUnderscoreToCamelCase" value="false"/>  <setting name="localCacheScope" value="SESSION"/>  <setting name="jdbcTypeForNull" value="OTHER"/>  <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/></settings>
Copy the code

  2. Type processor

    • When MyBatis sets a parameter in a PreparedStatement or fetches a value from a result set, it uses a type handler to convert the obtained value to a Java type in an appropriate manner
    • You can override the type handler or create your own type handler to handle unsupported or non-standard types.

  3. Object factory

    • Each time MyBatis creates a new instance of the resulting object, it does so using an ObjectFactory instance

    • All the default object factory needs to do is instantiate the target class, either through the default constructor or the parameter constructor if the parameter mapping exists

    • If you want to override the default behavior of an object factory, you can do so by creating your own object factory.

4. The Mapper file


      <! DOCTYPEmapper        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"        "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.kuang.mapper.UserMapper">    </mapper>
Copy the code
  • Namespace: a namespace that provides the following functions:
    • The namespace name must be the same as the name of an interface
    • Methods in the interface should correspond to SQL statement ids in the mapping file
  • The ids of namespace and child elements are combined to ensure that they are unique and distinct from different Mappers
  • Bind the DAO interface
  • Namespace naming rule: Package name + class name

5. Life cycle and scope

  • Understanding the different scope and lifecycle classes we’ve discussed so far is critical, because using them incorrectly can lead to very serious concurrency problems

  • MyBatis execution process:

  • Scope understanding:

    • The function of SqlSessionFactoryBuilder is to create the SqlSessionFactory. So it should only exist in the method that creates the SqlSessionFactory, not permanently. So the best scope for SqlSessionFactoryBuilder instances is the method scope (that is, local method variables)

    • SqlSessionFactory can be thought of as a database connection pool that creates SqlSession interface objects. Since the essence of MyBatis is Java operation on the database, the life cycle of SqlSessionFactory exists in the whole MyBatis application, so once the SqlSessionFactory is created, it should be stored for a long time. Until the MyBatis application is no longer used, the life cycle of SqlSessionFactory can be considered the same as the life cycle of MyBatis application

    • Since SqlSessionFactory is a pool of connections to the database, it occupies the connection resources of the database. If you create multiple SQlsessionFactories, there will be multiple database connection pools, which is not conducive to the control of database resources, but also leads to the depletion of database connection resources, and system downtime. Therefore, try to avoid such situations

    • Therefore, in general applications we want SqlSessionFactory to be a singleton and to be shared across applications. So the best scope for SqlSessionFactory is the application scope

    • SqlSession is a database Connection object. You can execute multiple SQL transactions in one transaction, and then perform commit, rollback, etc. Commit or roll back the transaction. After processing the complete request, close the connection and return it to the SqlSessionFactory. Otherwise, the database will run out of resources quickly and the system will crash. catch… finally… Statement to ensure that it closes correctly.

    • So the best scope for SqlSession is the request or method scope