“Offer comes, ask friends to take it! I am participating in the 2022 Spring Recruit Punch card campaign. Click here for more details.”

I. Create mybatis- Intermediate project

Copy the MyBatis – Quick-start project created in MyBatis overview and rename it MyBatis – Intermediate.

MyBatis Configuration file

Mybatis -config. XML is the core of MyBatis, which will affect the behavior of MyBatis.

The MyBatis Configuration TAB contains the following tags, each with a different function

TypeAliases Alias usage

Add the alias configuration under the Settings TAB in mybatis-config. XML

<! -- Define the default alias, lowercase class name -->
<typeAliases>
    <package name="com.lilith.entity" />
</typeAliases>
Copy the code

In UserMapper, wherever “com.lilith.entity.User” is used, “User” can be used instead

<select id="selectByPrimaryKey" resultType="user">
    SELECT
    <include refid="userColumns"></include>
    FROM t_user where id = #{id}
</select>
Copy the code

Execute the selectByPrimaryKey method in the test class

MyBatis (DEV,TEST,PROD)

  • The environment element is the start of configuring a data source, and the attribute ID is its unique identifier, DEV,TEST,PROD
  • The transactionManager element configures database transactions, where the Type attribute can be configured in three ways
  • JDBC, using JDBC way to manage transactions;
  • Managed, which uses a container to manage transactions, used in JNDI data sources;
  • Custom, custom database transaction management method;
  • The dataSource element configures the dataSource connection information. The type attribute configures the connection mode of the database. There are four configuration modes
    • UNPOOLED Indicates the connection in non-connection pool mode
    • POOLED uses a connection pool to connect. After a database connection is closed, it is put back into the pool for next use
    • JNDI uses JNDI data sources
    • Custom data source

MyBatis Settings tag configuration

Settings are global Settings. There are many Settings, but only a few are commonly used

  • CacheEnabled: This configuration affects the global switching of caches configured by all mappers (Mapper tags in Mapper XML files). The default value is true.
  • LazyLoadingEnabled: global switch of lazy loading. When enabled, all associated objects are lazily loaded. The fetchType property can be set to override the on/off state of the item in a particular association, with a silent heat value of false
  • Slogs: When enabled, a call to any delay attribute causes an object with a delay loading attribute to load completely; Instead, each attribute will be loaded on demand, with a default value of true
  • MultipleResultSetsEnabled: whether to allow more than a single statement returns a result set (need to compatible driver), the default is true
  • UserColumnLabel: Replaces column names with column labels. Different drivers have different behavior in this respect. For details, refer to the relevant driver documentation or observe the results of the driver used by testing the two different modes. The default is true
  • UserGeneratedKeys: allows JDBC support for automatic primary key generation, requiring driver compatibility. If set to true this setting enforces the use of auto-generated primary key, which will work even though some drivers are incompatible. The default is Fasle, which is enabled when auto Increment is set
  • AutoMappingBehavior: Specifies how MyBatis should automatically map columns to fields or properties. NONE indicates that PARTIAL mapping is disabled only for result sets that do not define nested result set mappings. FULL automatically maps arbitrarily complex result sets (whether nested or not), default to PARTIAL
  • DefaultExecutorType: Configures the default executor. SIMPLE is a plain actuator; The REUSE executor reuses prepared statements. The BATCH executor reuses statements and performs BATCH updates. The default value is SIMPLE
  • DefaultStatementTimeout: Sets the timeout time, which determines the number of seconds the driver waits for a database response
  • SafeRowBoundsEnabled: Allows paging (RowBounds) in nested statements. Default is false
  • MapUnderscoreToCamelCase: Indicates whether to enable Auto-Camel Case mapping from classic database column name A_COLUMN to classic Java property name aColumn. Default is false
  • LocalCacheScope: MyBatis uses Local Cache to prevent circular references and speed up repeated nested queries. The default value is SESSION, in which case all queries executed in a SESSION are cached. If the value is set to STATEMENT, the local session is only used for STATEMENT execution. Different calls to the same SqlSession will not share data
  • JdbcTypeForNull: Specifies the JDBC type for null values when no specific JDBC type is provided for the parameter. Some drivers need to specify the JDBC type of the column. In most cases, just use the generic type, such as NULL, VARCHAR, or OTHER. The most common jdbcType enumeration is NULL, VARCHAR, OTHER. The default is an OTHER
  • LazyLoadTriggerMethods: Specifies which object’s methods trigger a lazy load. If the list of methods is separated by a comma, default equals,clon e,hashCode,toString
  • CallSettersOnNulls: Specifies whether setter (put for map) methods are called when the result set value is null. This is useful when there are map.keyset () dependencies or null initializations. Note that primitive types (int, Boolean, etc.) cannot be set to NULL.
  • LogPrefix: Specifies the prefix MyBatis adds to the log name.
  • LogImpl: Specifies the specific implementation of logging used by MyBatis. If not specified, it will be found automatically.
  • ProxyFactory: Specifies the proxy tool Mybatis uses to create lazy-loading objects
<! -- Parameter Settings -->
<settings>
    <! This configuration enables the global mapper to enable or disable caching.
    <setting name="cacheEnabled" value="true" />
    <! -- Enable or disable lazy loading globally. When disabled, all associated objects are loaded instantly -->
    <setting name="lazyLoadingEnabled" value="true" />
    <! When enabled, objects with lazy loading properties will be fully loaded with any properties when called. Otherwise, each attribute will be loaded as needed -->
    <setting name="aggressiveLazyLoading" value="true" />
    <! Allow or disallow multiple result sets to return from a single statement (appropriate driver required) -->
    <setting name="multipleResultSetsEnabled" value="true" />
    <! Use column labels instead of column names. Different drives behave differently in this convenience. Consult the driver documentation or fully test both methods to determine the driver to use -->
    <setting name="useColumnLabel" value="true" />
    <! -- Allows JDBC to support generated keys. A suitable driver is required. If set to true, this setting forces the generated key to be used, although some drivers reject compatibility (such as Derby) -->
    <setting name="useGeneratedKeys" value="true" />
    <! -- Specifies how MyBatis automatically maps columns to fields/attributes. PARTIAL automatically maps simply, with no nested results. FULL automatically maps arbitrarily complex results (nested or otherwise) -->
    <setting name="autoMappingBehavior" value="PARTIAL" />
    <! What to do when an unknown column (or attribute) is detected. By default, there is no indication, which makes it inconvenient to find errors when testing. NONE: No processing is performed (default) WARNING: detailed information in the form of WARNING log FAILING: mapping failure, exception thrown, and detailed information -->
    <setting name="autoMappingUnknownColumnBehavior" value="WARNING" />
    <! -- Configures the default actuator. There is nothing special about the SIMPLE actuator. REUSE executor reuses a preprocessed statement. BATCH executor rephrase and BATCH update -->
    <setting name="defaultExecutorType" value="SIMPLE" />
    <! Set the timeout, which determines how long the driver will wait for a database response.
    <setting name="defaultStatementTimeout" value="25000" />
    <! -- Set the number of values returned by the query, which can be overridden by the query value -->
    <setting name="defaultFetchSize" value="100" />
    <! Allow paging in nested statements -->
    <setting name="safeRowBoundsEnabled" value="false" />
    <! -- Whether to enable Auto-Camel Case mapping from classic database column name A_COLUMN to classic Java property name aColumn. -->
    <setting name="mapUnderscoreToCamelCase" value="false" />
    <! MyBatis uses Local Cache to prevent circular references and speed up repeated nested queries. The default value is SESSION, in which case all queries executed in a SESSION are cached. If the value is set to STATEMENT, the local session is only used for STATEMENT execution. Different calls to the same SqlSession will not share data. -->
    <setting name="localCacheScope" value="SESSION" />
    <! -- Specifies the JDBC type for null values when no specific JDBC type is provided for the parameter. Some drivers need to specify the JDBC type of the column, and in most cases just use the generic type, such as NULL or VARCHAR OTHER. -->
    <setting name="jdbcTypeForNull" value="OTHER" />
    <! -- Specifies which object's methods trigger a lazy load. -->
    <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString" />
</settings>
Copy the code

The mappers tag configures the mapper file

There are four ways to configure a Mapper XML file under the Mappers tag

  • Reference with the CLASspath resource
<mappers>
    <mapper resource="mappers/UserMapper.xml"></mapper>
</mappers>
Copy the code
  • Reference as a class registry
<mappers>
    <mapper resource="com.lilith.mapper.UserMapper"></mapper>
</mappers>
Copy the code
  • Import the mapping file name using the package name
<mappers>
    <mapper resource="com.lilith.mapper"></mapper>
</mappers>
Copy the code
  • Apply with the absolute path of the mapping file
<mappers>
    <mapper resource="/Users/Practice/March/mybatis-intermediate/src/main/resources/mappers/UserMapper.xml"></mapper>
</mappers>
Copy the code

The first method is recommended

MyBatis Mapper XML file

The mapper configuration file is mainly used to configure SQL statements and mapping rules. The Mapper tag has a namespace attribute, and its attribute value should be the full class name of the Mapper interface. This is a convention, so that the mapping relationship between the Mapper interface and the Mapper file can be formed. The Mapper tag contains the following tags

  • Cache – Cache configuration for a given namespace
  • Cache-ref – References to other namespace cache configurations
  • ResultMap – the most complex and powerful element describing how to load an object from a database result set
  • SQL – reusable block of statements that can be referenced by other statements
  • Insert – Mapping insert statements
  • Update – Mapping update statement
  • Delete – Mapping delete statement
  • Select – Mapping query statement

SQL TAB

SQL tags are used to configure SQL fragments. For SQL fragments that are repeated frequently, include tags are used to reference the configured SQL fragments

<sql id="Base_Column_List">
    id,user_name,real_name,sex,mobile,email,note,position_id
</sql>

<select id="selectByPrimaryKey" resultType="user">
    SELECT
    <include refid="Base_Column_List"></include>
    FROM t_user where id = #{id}
</select>
Copy the code

Parameter pass #{} and ${}

${parameter name} ${parameter name} ${parameter name} ${parameter name} ${parameter name}

  • #{} precompiles the data as if it were a string, that is, puts double quotes around the data that is automatically passed in, and can be used for SQL injection to a large extent
  • ${} displays the incoming value directly in the SQL statement, and does not prevent SQL injection

SQL injection occurred when ${} was passed

Add a method selectById to UserMapper

List<User> selectById(Object id);
Copy the code

SQL statement to add the mapping to usermapper. XML using ${} to pass the value

<select id="selectById" resultType="user">
    SELECT
    <include refid="userColumns"></include>
    FROM t_user where id = ${id}
</select>
Copy the code

Add test method selectById to UserMapperTest

@Test
public void selectById(a) {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    List<User> users = mapper.selectById("1 or 1 = 1");
    System.out.println("The queried data is:" + users);
}
Copy the code

Perform the test

Based on the console output, this method queries all the data. This is a typical SQL injection case. Change ${} to #{} and run the test again

Only the data with id 1 is detected, effectively avoiding SQL injection

Table names and selected columns are dynamic, and ORDER BY and IN operations can use ${} to pass values

Added method selectByTablename to UserMapper interface

User selectByTablename(String tablename);
Copy the code

Add selectByTablename mapping SQL statement

<select id="selectByTablename" resultType="user">
    SELECT
    <include refid="userColumns"></include>
    FROM ${tablename} where id = 1
</select>
Copy the code

Add test method selectByTablename to test class

@Test
public void selectByTablename(a) {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User user =  mapper.selectByTablename("t_user");
    System.out.println("The queried data is:" + user);
}
Copy the code

Perform the test

ResultMap label usage

ResultMap is the most important and powerful tag in MyBatis. It allows you to remove 90% of JDBC ResultSets from your code, making it very convenient to combine complex statements

The design idea of resultMap is that simple statements do not need explicit result mapping, while complex statements only need to describe the relationship between attributes and fields

Add the resultMap configuration to usermapper.xml to define the mapping between fields in the T_USER table and attributes of the User entity class

<resultMap id="BaseResultMap" type="com.lilith.entity.User">
    <id column="id" property="id" />
    <result column="user_name" property="userName" />
    <result column="real_name" property="realName" />
    <result column="sex" property="sex" />
    <result column="mobile" property="mobile" />
    <result column="email" property="email" />
    <result column="note" property="note" />
    <result column="position_id" property="positionId" />
</resultMap>
Copy the code

Id and result are attributes or fields that map the value of a field to a simple data type (string, integer, floating point, etc.)

attribute Attributes describe
property Attribute in Entity. If the field matching the attribute of Entity exists, it will be mapped automatically
column Watch the field name
javaType Configured Java classes
jdbcType Type of the configured database
property Type handler. Using this property overrides the default type handler and requires a full class name or alias

If it is an enumerated type, a custom type converter is required. Common types do not write jDBCType and Javatype attributes

ResultMap attributes:

  • Id: A unique representation in the current namespace that identifies a resultMap
  • Type: the fully qualified name of a class or an alias for a class
  • AutoMapping: If this attribute is set, MyBatis will enable or disable automatic mapping for the resultMap. This attribute overrides the global autoMappingBehavior. The default value is unset

Modify the SQL statement corresponding to the selectByPrimaryKey method

<select id="selectByPrimaryKey" resultMap="BaseResultMap">
    SELECT
    <include refid="userColumns"></include>
    FROM t_user where id = #{id}
</select>
Copy the code

Execute the selectByPrimaryKey method in the test class

In addition to the ID and result child tags, the resultMap also has the following child tags:

  • Constructor: Used when instantiating a class to inject results into the constructor, and when the class does not contain a no-argument constructor
  • Association: a complex type of association, nested result mapping, mostly used for one-to-one queries
  • Collection: A collection of complex types, nested result mappings, used mostly for one-to-many or many-to-many queries
  • Discriminator: a result value is used to determine which resultMap to use
    • Case: Mapping based on some worthwhile results

Association and Collection tags are frequently used in joint queries

Difference between resultType and resultMap

ResultType, map the result set to a class, a class full path class name or class alias, according to whether the class attribute name and database field name are the same mapping, the field value is assigned to the attribute, can also be set to enable camel name

ResultMap, which maps result sets to a Map, defines transformation rules.

The select tag

attribute describe note
id A unique identifier in the namespace that can be used to refer to this statement. If the combination of namespace and ID is not unique, an exception is thrown
parameterType The type of the parameter passed in; MyBatis alias must be internal or custom defined; Basic data types :int, String, long, date(sql.date or util. Date) Complex data types: class and Map You can select Javabeans, Maps, and other complex parameter types to pass to SQL
parameterMap Deprecated property used to reference an external parameterMap. Use inline parameter mapping and the parameterType attribute.
resultType The fully qualified name or alias of the class of the desired type returned from this statement. Note that in the collection case, it should be the types that the collection can contain, not the collection itself; Use resultType or resultMap, but not both the full path of the defining class. With automatic matching allowed, the result set will be mapped through the JavaBean specification; Or int,double,float, etc… , you can also use an alias. However, the alias must comply with the alias specification and cannot be used together with a resultMap. It’s one of the arguments that we use a lot, so if we’re adding up the total, we can set it to int
resultMap Named reference to an external resultMap. Use resultMap or resultType, but not both. It is a reference to a mapping set and will perform powerful mapping functions. We can use either a resultType or a resultMap, and a resultMap gives us the opportunity to customize mapping rules It is the most complex element of MyBatis and can be configured with mapping rules, cascading, typeHandler, etc
flushCache MyBatis is required to clear the local cache and secondary cache of the previous query after the SQL call True /false, the default is false
useCache Start the level 2 cache switch, whether to require MyBatis to cache the results True /false, the default is false
timeout Set the timeout period, after the timeout exception, seconds The default value is the number of seconds set by the JDBC driver provided by the database vendor
fetchSize Gets the total number of records set The default value is the number set by the JDBC driver provided by the database vendor
statementType The value can be STATEMENT, PREPARED or CALLABLE. This will make MyBatis use Statement, PreparedStatement, or CallableStatement, respectively. The default value is PREPARED.
resultSetType FORWARD_ONLY, SCROLL_SENSITIVE, SCROLL_INSENSITIVE, or one of DEFAULT (equivalent to unset), which defaults to unset (database driver dependent).
databaseId If databaseIdProvider is configured, MyBatis will load all statements that do not have a databaseId or match the current databaseId. If both tagged and untagged statements are present, untagged statements are ignored.
resultOrdered This setting applies only to nested result SELECT statements: if true, nested result sets or groups are assumed to be included, and no references to previous result sets are generated when a primary result row is returned. This keeps memory from running out when retrieving nested result sets. Default value:false.
resultSets This setting only works with multiple result sets. It lists the result sets returned after statement execution and gives each result set a name, separated by commas.

SQL statements pass multiple parameters

Select statements can pass multiple parameters in three ways:

  • Using Map to pass parameters has poor readability, scalability, and maintainability
  • Using annotations to pass parameters is intuitive and is recommended when less than 5 parameters are passed
  • Use Java beans to pass parameters when there are more than five parameters
The Map and the cords

New method in UserMapper that uses Map to pass parameters

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

Define the corresponding SQL statements in XML

<select id="selectByUserNameAndSex" resultType="user">
    SELECT
    <include refid="userColumns"></include>
    FROM t_user where user_name = #{userName} AND sex = #{sex}
</select>
Copy the code

Add a test method to the test class, where you must use a Map to construct the query criteria

@Test
public void selectByUserNameAndSex(a) {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    // construct the query condition
    Map<String, Object> map = new HashMap<>();
    map.put("userName"."loki");
    map.put("sex".1);
    List<User> userList =  mapper.selectByUserNameAndSex(map);
    System.out.println("The queried data is:" + userList);
}
Copy the code

Execute the test methodThe data is successfully queried

Annotation transfer and

New method in UserMapper selectByRealNameAndSexAndSex, used here @ Param annotations for the name of the parameter.

List<User> selectByRealNameAndSexAndSex(@Param("realName") String realName, @Param("sex") Integer sex);
Copy the code

Add the corresponding SQL statement to usermapper. XML

<select id="selectByRealNameAndSexAndSex" resultType="user">
    SELECT
    <include refid="userColumns"></include>
    FROM t_user where real_name = #{realName} AND sex = #{sex}
</select>
Copy the code

New test method, called ShiChuanCan selectByRealNameAndSexAndSex method does not need to construct the Map query conditions, can be directly inserted into the corresponding parameters

@Test
public void selectByRealNameAndSexAndSex(a) {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    List<User> userList =  mapper.selectByRealNameAndSexAndSex("Thor Odin".1);
    System.out.println("The queried data is:" + userList);
}
Copy the code

Execute the test method

The Java Bean to participate

When there are many query conditions, you are advised to encapsulate all query conditions into Java beans and directly upload Java beans as input parameters to methods.

User selectOneByEntity(User user);
Copy the code

Add the SQL statement corresponding to selectOneByEntity

<select id="selectOneByEntity" resultType="user">
    SELECT
    <include refid="userColumns"></include>
    FROM t_user
    WHERE id = #{id}
    AND user_name = #{userName}
    AND real_name = #{realName}
</select>
Copy the code

Add test methods to UserMapperTest

@Test
public void selectOneByEntity(a) {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User user = new User();
    user.setId(1);
    user.setUserName("stark");
    user.setRealName("Tony Stark");

    User dbUser =  mapper.selectOneByEntity(user);
    System.out.println("The queried data is:" + dbUser);
}
Copy the code

Execute the test methodThe attributes of Value set in the query method appear in the query conditions of the SQL statement.