MyBatis is an excellent persistence layer framework that supports ordinary SQL queries, stored procedures and advanced mapping. MyBatis eliminates manual settings of almost all JDBC code and parameters and search for result sets. MyBatis uses simple XML or annotations for configuration and original mapping to map interfaces and Java's POJOs (Plain Old Java Objects) into records in the database.
MyBatis is an open source project of apache. In 2010, this project was moved from apache software foundation to Google code and was renamed MyBatis.
Migrated to Github in November 2013, MyBatis' Github address: https://github.com/mybatis/mybatis-3.
The term iBATIS comes from the combination of "internet" and "abatis", and is a Java-based persistence layer framework. iBATIS provides persistence layer frameworks including SQL Maps and Data Access Objects (DAO).
Each MyBatis application mainly uses SqlSessionFactory instances, and a SqlSessionFactory instance can be obtained through SqlSessionFactoryBuilder. SqlSessionFactoryBuilder can be obtained from an XML configuration file or an instance of a predefined configuration class.
1. Use Generator to automatically generate Dao layer, Model layer and Mapper layer.
MyBatis Generator download address: http://www.mybatis.org/generator/
MyBatis Generator Chinese introduction: http://generator.sturgeon.mopaas.com/
The following uses the mybatis-generator-core-1.3.2.jar plug-in to add the jdbc database connection package to automatically export the persistence layer dao package, model package and mapper package.
The Java packages that need to be used are:
mybatis-generator-core-1.3.2.jar,
mysql-connector-java-5.1.34.jar,
ojdbc14-10.2.0.1.0.jar,
sqljdbc4-4.0.jar.
Configuration file: generator.xml
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE generatorConfiguration PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN" "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd"><generatorConfiguration> <!-- Configuration property file is used to introduce variable El expressions in the configuration file --> <!-- If you run it in cmd, you should write the full path in the URL because classpath cannot be found. For resource--> <!-- <properties url="file:///D:/workspaces/mybatisGen/bin/generatorConfig.properties"/> --> <!-- Database Driver Package Location--> <!-- SQL Server Data Driver Package--> <classPathEntry location="D:/JavaProject/generator/sqljdbc4-4.0.jar" /> <!-- Oracle Data Driver Package--> <!-- <classPathEntry location="D:/Java/m2/repository/com/oracle/ojdbc14/10.2.0.1.0/ojdbc14-10.2.0.1.0.jar" /> --> <!-- MySQL Data Driver Package--> <!-- <classPathEntry location="D:/JavaProject/generator/mysql-connector-java-5.1.34.jar" /> --> <!-- Here specifies that DAO for MyBatis3--> <!-- id: Must be configured. The unique identifier of this context. This value will be used in some error messages. defaultModelType: Used to define the generative model type policy. 1.conditional default policy, generate a Model class for each table 2.flat: Generate a Model class from all tables, that is, this class will save all fields from tables 3.hierarchical: If the table has a primary key, the model will generate a primary key class, another class, which is used to accommodate any BLOB columns in the table, and another class, which is used to accommodate the remaining fields. This is a proper relationship between inherited classes. targetRuntime: This property is used to specify the code generated by the runtime target. 1.MyBatis3 default values will generate objects that are compatible with MyBatis version 3.0 and later, and JSE 5.0 and later (for example, Java models and mapper interfaces will use generic types). The "by example" method supports almost unlimited dynamic where clauses on these generated objects. Additionally, Java objects with these generators support many JSE 5.0 features including parameterized types and comments. 2.Ibatis2Java2 3.Ibatis2Java5 --> <context id="MySQLTables" targetRuntime="MyBatis3" defaultModelType="conditional"> <!-- <plugin type="org.mybatis.generator.plugins.EqualsHashCodePlugin" /> --> <!-- Used to generate comments 1. suppressAllComments By default is false This property is used to specify whether any comments will be included in the generated code. If set to true, no comment is generated 2. SuppressDate defaults to false This property is used to specify whether the generated comment will include the MBG generation timestamp. --> <commentGenerator> <property name="suppressAllComments" value="true" /> </commentGenerator> <!-- Database Link URL, Username, Password--> <!-- MySQL Database Link URL, Username, Password--> <!-- <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://127.0.0.1:3310/test" userId="test" password="1234"> </jdbcConnection> --> <!-- Oracle Database Link URL, Username, Password--> <!-- <jdbcConnection driverClass="oracle.jdbc.driver.OracleDriver" connectionURL="jdbc:oracle:thin:@127.0.0.1:1521:orcl" userId="test" password="1234"> </jdbcConnection> --> <!-- SQL Server database link URL, username, password--> <jdbcConnection driverClass="com.microsoft.sqlserver.jdbc.SQLServerDriver" connectionURL="jdbc:sqlserver://127.0.0.1:1433;DatabaseName=test" userId="test" password="1234"> </jdbcConnection> <!-- H2 <entry key="jdbc.url">jdbc:h2:tcp://localhost/test</entry> <entry key="jdbc.driver">org.h2.Driver</entry> --> <!-- SQLServer2000 <entry key="jdbc.url">jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=[database]</entry> <entry key="jdbc.driver">com.microsoft.jdbc.sqlserver.SQLServerDriver</entry> --> <!-- SQLServer2005 <entry key="jdbc.url">jdbc:sqlserver://192.168.0.98:1433;DatabaseName=[database]</entry> <entry key="jdbc.driver">com.microsoft.sqlserver.jdbc.SQLServerDriver</entry> --> <!-- JTDs for SQLServer <entry key="jdbc.url">jdbc:jtds:sqlserver://192.168.0.102:1433/[database];tds=8.0;lastupdatecount=true</entry> <entry key="jdbc.driver">net.sourceforge.jtds.jdbc.Driver</entry> --> <!-- PostgreSql <entry key="jdbc.url">jdbc:postgresql://localhost/[database]</entry> <entry key="jdbc.driver">org.postgresql.Driver</entry> --> <!-- Sybase <entry key="jdbc.url">jdbc:sybase:Tds:localhost:5007/[database]</entry> <entry key="jdbc.driver">com.sybase.jdbc.SybDriver</entry> --> <!-- DB2 <entry key="jdbc.url">jdbc:db2://localhost:5000/[database]</entry> <entry key="jdbc.driver">com.ibm.db2.jdbc.app.DB2Driver</entry> --> <!-- HsqlDB <entry key="jdbc.url">jdbc:hsqldb:mem:generatorDB</entry> <entry key="jdbc.driver">org.hsqldb.jdbcDriver</entry> --> <!-- Derby <entry key="jdbc.url">jdbc:derby://localhost/databaseName</entry> <entry key="jdbc.driver">org.apache.derby.jdbc.ClientDriver</entry> --> <!-- java type parser optional configuration --> <!-- <javaTypeResolver type=""> type property: This can be used to specify a user-provided Java type parser. This class must implement the interface org.mybatis.generator.api. JavaTypeResolver, there must be a public default constructor. The property can also accept special values by default in this case, the default implementation will be used (the same effect does not specify the type). The properties supported by this tag: forceBigDecimals: default is false Whether to force BigDecimal to represent all decimal and numeric fields. •If the scale is large and the length is greater than 18, the BigDecimal type will be used. •If its length is 10 to 18, the Java type parser will replace java.lang.Long. •If the length is 5 to 9, then the Java type parser will be replaced with a Java.lang.integer. •If its length is less than 5, the Java type parser will be replaced by java.lang.Short. --> <javaTypeResolver> <property name="forceBigDecimals" value="false" /> </javaTypeResolver> <!-- Generate vo object--> <!-- < javaModelGenerator> Elements are used to define properties generated by Java models. The Java model generator creates the table matching the primary key class, record class, and query sample class for introspection. This element is the required child element <context> element. Supported properties: constructorBased: This property is used to select whether the MyBatis generator will generate a class constructor, which accepts each field in a value class. Meanwhile, SQL result maps will be built into each field using constructor instead of "setter". This property is only applicable to MyBatis3 and will be ignored for iBATIS2. The default value is false. immutable: immutable, this property is used to select whether the MyBatis generator will generate an immutable model class - this means that the class will not have a "setter" method and the constructor will accept the value of each field in the class. Default is false. trimStrings: This property is used to select whether the MyBatis generator adds code to trim the empty space returned from the database. This is useful if your database stores data in a character field instead of a VARCHAR field. The MyBatis generator will insert code to cut character fields. The default value is false. --> <!-- Generate the package name and location of the entity class. Here, place the generated entity class under the package com.ouc.model--> <javaModelGenerator targetPackage="com.ouc.model" targetProject="D:/JavaProject/generator/src"> <property name="enableSubPackages" value="true" /> <property name="trimStrings" value="true" /> </javaModelGenerator> <!-- Generate mapping object for query--> <!-- Generate the package name and location of the generated SQL map file. Here, place the generated SQL map file under the package com.ouc.mapping--> <sqlMapGenerator targetPackage="com.ouc.mapping" targetProject="D:/JavaProject/generator/src"> <property name="enableSubPackages" value="true" /> </sqlMapGenerator> <!-- Generate the class file and configuration file of DAO --> <!-- < javaClientGenerator> Elements are attributes used to define Java client code generators. The Java client generator is used to establish Java interfaces and classes so that the generated Java model and XML mapping files can be easily used. For the iBATIS2 target environment, these generated objects adopt the form of DAO interface and implementation classes. For MyBatis, the generated object uses a mapper interface. This element is an optional child element. If you do not specify this element, the MyBatis generator (MBG) will not generate Java client interfaces and classes. Where type attribute: If targetRuntime is MyBatis3 XMLMAPPER: The generated object will interface the Java interface to the MyBatis 3.x mapper infrastructure. The interface will depend on the generated XML mapper file. Generally, this XMLMAPPER is used. --> <!-- Generate the package name and location of the DAO. Here, the generated dao class is placed under the com.ouc.dao package --> <javaClientGenerator type="XMLMAPPER" targetPackage="com.ouc.dao" targetProject="D:/JavaProject/generator/src"> <property name="enableSubPackages" value="true" /> </javaClientGenerator> <!-- <table> element is used to select a table in the database. The selected table will result in the following objects being generated for each table: • A MyBatis/iBATIS • Formatted SQL mapping file • A set of classes that form the "model" table including: • A class to match • The table's primary key (if the table has a primary key). •The fields in the table match not in the primary key, not in the BLOB field. This class will extend the primary key if there is one. • A class to hold the BLOB field in any table (if any). This class will extend the first two classes of one depending on the table configuration. •A class, used to generate dynamic where clauses, in different "by Example" methods (selectByExample, deleteByExample). • (Optional) DAO interface and class tableName: The name of the specified table must be configured. domainObjectName: The basic name of the generated javabean object. If not specified, MBG will be automatically generated based on the table name. This name (whether specified here, or automatically generated) will be used as the domain class name and the name of the DAO class. enableInsert: Whether to generate an insert statement. The default is true enableSelectByPrimaryKey: Whether to generate a selection statement through the primary key. Regardless of whether there is such a setting, if the table does not have a primary key, it will not be generated. enableUpdateByPrimaryKey: Whether to generate update statements through the primary key. If the table does not have a primary key, the statement will not be generated regardless of whether the property is set or not. enableDeleteByPrimaryKey: Whether to generate a delete statement through the primary key. If the table does not have a primary key, no matter this property is set, the statement will not be generated. enableDeleteByExample: Whether to generate a delete statement through the example object. This declaration makes many different dynamic deletions generated at runtime. enableCountByExample: Whether to generate a statement to calculate the number of rows through the example object. This statement returns an example that matches the number of rows in a table. enableUpdateByExample: Whether to generate update statements through the example object. This statement will update a matching record in a table. selectByPrimaryKeyQueryId: This value will be added to the selection list to select the declaration by the primary key in this table: "' <value> as QUERYID". This can be used to identify queries in the DBA at runtime tracking tool. If you need to use, you should specify a unique id to generate MBG for each different query. selectByExampleQueryId: This value will be added to the selection list to select the statement by the example in this table: "' <value> as QUERYID". This can be used to identify queries in the DBA at runtime tracking tool. If you need to use, you should specify a unique id to generate MBG for each different query. enableSelectByExample: Should the selection statement be generated through example? This declaration makes many different dynamic queries generated at runtime. modelType: This property is used to override the default model type if you want to do this for this table. If not specified, MBG will generate the domain object based on the context default model type. This model type defines how the MBG domain class will be generated. Some model types MBGs will generate a single domain class for each table, and others may generate different classes MBGs depending on the table's structure. escapeWildcards: Exclude wildcards. This means that both the schema and table names of SQL wildcards ('_' and ' %') should be avoided in search columns. This is some drivers that require that if the schema or table contains a SQL wildcard (for example, if the name of a table is MY_TABLE, some drivers require that the underscore characters be escaped). --> <!-- To generate those tables (change tableName and domainObjectName) --> <table tableName="V_SupplyUser" domainObjectName="VSupplyUser" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false" /> <table tableName="WJ_GateList" domainObjectName="WJGateList" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false" /> </context></generatorConfiguration> Open the cmd command line, go to the file where the configuration file is located, and execute the following generation statement:
java -jar mybatis-generator-core-1.3.2.jar -configfile generator.xml -overwrite
After the command is executed, you can see that the dao package, model package and mapper package files are generated in the corresponding path.
2.MyBatis framework integration
1) MyBatis uses parameter configuration: sqlMapConfig.xml.
① Cache configuration (Cache): cacheEnabled: Global switch: default is true. If it is configured as false, it is useless to configure the other Mapper XML files to support cache.
② Delay loading:
lazyLoadingEnabled: true uses lazy loading, false disables lazy loading, default is true, when disabled, all associated objects will be loaded instantly.
When aggressiveLazyLoading: true is enabled, when accessing a lazy object property in the object when lazy loading is enabled, all lazy object properties of this object will be fully loaded. false. When loading delays, load the object properties as needed (that is, accessing a lazy object property in the object, and other lazy object properties in the object will not be loaded). Default is true.
③ multipleResultSetsEnabled: Allow and do not allow a single statement to return multiple data sets (depending on the driver requirements). Default is true.
④ useColumnLabel: Use column labels instead of column names. Different drives have different approaches. Refer to the drive documentation or test it out with these two different options.
⑤ useGeneratedKeys: Allows JDBC to generate primary keys. Drive support is required. If set to true, this setting will force the generated primary key, some drives are incompatible but can still be executed.
⑥ autoMappingBehavior: Specifies whether and how MyBatis automatically maps data table fields and objects' properties. PARTIAL will only automatically map simple, without nested results. FULL will automatically map all complex results.
⑦ defaultExecutorType: configures and sets the executor, and the SIMPLE executor executes other statements. The REUSE executor may reuse prepared statements statements, and the BATCH executor may repetitively execute statements and batch updates.
⑧ defaultStatementTimeout: Set a time limit to determine how long the drive will wait for the database to respond to timeout.
The complete sqlMapConfig.xml configuration file is as follows:
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration><settings><setting name="cacheEnabled" value="true" /><setting name="lazyLoadingEnabled" value="true" /><setting name="multipleResultSetsEnabled" value="true" /><setting name="multipleResultSetsEnabled" value="true" /><setting name="useColumnLabel" value="true" /><setting name="useGeneratedKeys" value="false" /><setting name="autoMappingBehavior" value="PARTIAL" /><setting name="defaultExecutorType" value="SIMPLE" /><!-- SIMPLE REUSE BATCH --><!-- <setting name="defaultExecutorType" value="BATCH" /> --><setting name="defaultStatementTimeout" value="" /><setting name="safeRowBoundsEnabled" value="false" /><setting name="mapUnderscoreToCamelCase" value="false" /><setting name="localCacheScope" value="SESSION" /><!-- <setting name="jdbcTypeForNull" value="OTHER" /> --><setting name="jdbcTypeForNull" value="NULL" /><setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString" /></settings><typeAliases><!-- Module--><typeAlias alias="User" type="com.ouc.mkhl.platform.authority.model.User"/> <typeAlias alias="Role" type="com.ouc.mkhl.platform.authority.model.Role"/><typeAlias alias="Equipment" type="com.ouc.mkhl.platform.basedata.model.Equipment"/><typeAlias alias="Factory" type="com.ouc.mkhl.platform.basedata.model.Factory"/></typeAliases><typeHandlers><typeHandler handler="com.ouc.openplatform.dao.mybatis.SerializableTypeHandler"/></typeHandlers></configuration>
Serialization special value processing: SerializableTypeHandler
package com.ouc.openplatform.dao.mybatis;import java.io.Serializable;import java.sql.CallableStatement;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import org.apache.ibatis.type.BaseTypeHandler;import org.apache.ibatis.type.JdbcType;/*** @author WuPing*/public class SerializableTypeHandler extends BaseTypeHandler<Serializable> {@Overridepublic void setNonNullParameter(PreparedStatement ps, int i, Serializable parameter, JdbcType jdbcType)throws SQLException {ps.setObject(i, parameter);}@Overridepublic Serializable getNullableResult(ResultSet rs, String columnName)throws SQLException {return (Serializable)rs.getObject(columnName);}@Overridepublic Serializable getNullableResult(ResultSet rs, int columnIndex)throws SQLException {return (Serializable)rs.getObject(columnIndex);}@Overridepublic Serializable getNullableResult(CallableStatement cs, int columnIndex)throws SQLException {return (Serializable)cs.getObject(columnIndex);}} 2) resultMap result set:
When select mapping in MyBatis, the return type can be used with resultType or resultMap. resultType directly represents the return type, while resultMap is a reference to the external ResultMap, but resultType and resultMap cannot exist at the same time. When MyBatis is querying mapping, in fact, each attribute query is placed in a corresponding map, where the key is the attribute name and the value is its corresponding value. When the provided return type attribute is resultType, MyBatis will take out the key value pairs in the map and assign them to the corresponding attributes of the object specified by resultType. So in fact, the return type of each query map of MyBatis is a ResultMap. However, when the return type attribute we provide is resultType, MyBatis automatically assigns the corresponding value to the attributes of the object specified by resultType. When the return type we provide is resultMap, because the Map cannot represent the domain model well, we need to further convert it into the corresponding object ourselves, which is often very useful in complex queries.
Example: UserBaseResultMap
<resultMap id="UserBaseResultMap" type="User" ><id column="id" property="id" jdbcType="INTEGER" /><result column="userName" property="userName" jdbcType="VARCHAR" /><result column="password" property="password" jdbcType="VARCHAR" /><result column="email" property="email" jdbcType="VARCHAR" /><result column="trueName" property="trueName" jdbcType="VARCHAR" /><result column="sex" property="sex" jdbcType="VARCHAR" /><result column="age" property="age" jdbcType="INTEGER" /><result column="telephone" property="telephone" jdbcType="VARCHAR" /></resultMap>
Model class: User
package com.ouc.mkhl.platform.authority.model;import java.io.Serializable;//User information public class User implements Serializable { private static final long serialVersionUID = 1098321123L; private Integer id; //User ID private String userName; //User name private String password; //User name private String email; //Email private String trueName; //Real name private String sex; //Gender private Integer age; //Age private String telephone; //Mobile phone public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName == null ? null : userName.trim(); } public String getPassword() { return password; } public void setPassword(String password) { this.password = password == null ? null : password.trim(); } public String getEmail() { return email; } public void setEmail(String email) { this.email = email == null ? null : email.trim(); } public String getTrueName() { return trueName; } public void setTrueName(String trueName) { this.trueName = trueName == null ? null : trueName.trim(); } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex == null ? null : sex.trim(); } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public String getTelephone() { return telephone; } public void setTelephone(String telephone) { this.telephone = telephone == null ? null : telephone.trim(); }} 3) Add, delete, modify and check:
(1) Select query:
① id: The unique identifier in this mode can be referenced by other statements.
② parameterType: The complete class name or alias of the parameter passed to this statement.
③ resultType: The statement returns the entire class name or alias of the value type. Note that if it is a collection, then the entire class name or alias of the item in the collection is filled in here, rather than the class name of the collection itself. (resultType and resultMap cannot be used together)
④ resultMap: The referenced external resultMap name. Result Set Mapping is the most powerful feature in MyBatis. Many complex mappings can be easily solved. (resultType and resultMap cannot be used together)
⑤ flushCache: If set to true, the cache will be cleared every time the statement is called. The select statement is set to false by default.
⑥ useCache: If set to true, the result set of the statement will be cached. The select statement is set to false by default.
⑦ timeout: Set the maximum time the drive waits for a response before throwing an exception. The default is to not set the value, and the drive decides it itself.
Example: Query all user information: selectUsers
<select id="selectUsers" resultMap="UserBaseResultMap">select id,userName,email from user </select>
(2) insert insert: saveUser
Here the database table uses the primary key to increase itself, and the primary key is id.
① fetchSize: After setting a value, the drive will be excited to return after the number of result sets reaches this value. The default is not set, which is determined by the drive itself.
② statementType: statement, prepared statement, callablestatement. Prepared statements, callable statements.
③ useGeneratedKeys: Use JDBC's getGeneratedKeys method to obtain the primary key generated by the database itself (MySQL, SQLSERVER and other relational databases will have fields that will be automatically generated).
④ keyProperty: Identifies a value returned by the key that will be set into getGeneratedKeys by MyBatis, or use a selectKey child element for the insert statement.
<insert id="saveUser" parameterType="User" >insert into user (userName, password, email, trueName, sex, age, telephone)values (#{userName,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR}, #{trueName,jdbcType=VARCHAR}, #{sex,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER}, #{telephone,jdbcType=VARCHAR})</insert> (3) update update: dynamically update SQL: updateUser
<update id="updateUser" parameterType="User" >update user<set ><if test="userName != null" >userName = #{userName,jdbcType=VARCHAR},</if><if test="password != null" >password = #{password,jdbcType=VARCHAR},</if><if test="email != null" >email = #{email,jdbcType=VARCHAR},</if><if test="trueName != null" >trueName = #{trueName,jdbcType=VARCHAR},</if><if test="sex != null" >sex = #{sex,jdbcType=VARCHAR},</if><if test="age != null" >age = #{age,jdbcType=INTEGER},</if><if test="telephone != null" >telephone = #{telephone,jdbcType=VARCHAR},</if></set>where id = #{id,jdbcType=INTEGER}</update> (4) delete delete: deleteUser
<delete id="deleteUser" parameterType="Integer">delete from userwhere id = #{id,jdbcType=INTEGER}</delete> (5) sql: The Sql element is used to define a reusable SQL statement segment for other statements to call.
<sql id="UserBaseColumnList" >userName, password, email, telephone</sql><select id="getUsers" resultMap="UserBaseResultMap">select <include refid="UserBaseColumnList" />from user</select>
(6) Parameters: parameters: MyBatis can use basic data types and Java's complex data types.
Basic data types, String, int, date, etc.
Using basic data types can only provide one parameter, so you need to use Java entity class or Map type as parameter type. Its attributes can be obtained directly through #{}.
① Basic data type parameters: String
<select id="getUserByName" resultType="User" parameterType="String" >select id, userName, email from userwhere userName = #{userName,jdbcType=VARCHAR}</select> Java code:
public User getUserByName(String name); // Get user information based on user name
② Java entity type parameter: User
<insert id="saveUser" parameterType="User" >insert into user (userName, password, email, trueName, sex, age, telephone)values (#{userName,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR}, #{trueName,jdbcType=VARCHAR}, #{sex,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER}, #{telephone,jdbcType=VARCHAR})</insert> Java code:
public int saveUser(User user); // Insert user information
③ Map parameters: Map<String, Object> recordMap
<select id="selectChildGroupTotalNum" resultType="Integer" >select count(*) from groupinfo<trim prefix="WHERE" prefixOverrides="AND|OR">and id in <foreach collection="idStr" item="ids" open="(" separator="," close=")"> #{ids} </foreach> <if test="name!= null and name!='' " >and name LIKE CONCAT(CONCAT('%', #{name}),'%')</if><if test="description!= null and description!='' " >AND description LIKE CONCAT(CONCAT('%', #{description}),'%')</if><if test="type != null and type!=-1 " >AND type = #{type,jdbcType=INTEGER}</if><if test="category != null and category!=-1 " >AND category = #{category,jdbcType=INTEGER}</if></trim> </select> Java code:
//Get the total number of records in subgroups public int selectChildGroupTotalNum(Map<String, Object> recordMap); Map<String, Object> recordMap = new HashMap<String, Object>();recordMap.put("idStr", group.getChildgroupids().split(","));recordMap.put("name", name);recordMap.put("description", description);recordMap.put("type", -1);recordMap.put("category", -1);childGroupTotalNum = groupDao.selectChildGroupTotalNum(recordMap); ④ Multiple parameters:
Method 1: Pass parameters in order.
<!-- Query parameters based on parameter name--><select id="selectSensorNobySensorName" resultType="Integer" useCache="false" flushCache="true">select SensorNo from sensorconfig where Name = #{0} and TestunitNo = #{1} and LABCODE = #{2}</select> Java code:
//Query the parameter IDpublic int selectSensorNobySensorName(String sensorName, int testUnitNo, String labCode);
Method 2: Add @Param annotation to the interface parameters.
<select id="selectByUserNameAndVCode" resultMap="UserBaseResultMap">select id, userName from user<trim prefix="WHERE" prefixOverrides="AND|OR"><if test="userName!= null and userName!='' ">and userName LIKE CONCAT(CONCAT('%', #{userName}),'%')</if><if test="supplierno!= null and supplierno!='' ">and supplierNo LIKE CONCAT(CONCAT('%', #{supplierno}),'%')</if> and supplierNo != 'test'</trim>LIMIT #{startIndex},#{pageSize}</select> Java code:
// Query user information based on username and V code public List<User> selectByUserNameAndVCode(@Param("userName") String userName,@Param("supplierno") String supplierno,@Param("startIndex") int startIndex, @Param("pageSize") int pageSize); 4) Dynamic SQL statement:
selectKey tag, if tag, conditional judgment of if + where, update statement of if + set, if + trim replaces where/set tag, trim replaces set, choose (when, otherwise), foreach tag. Dynamic SQL statements are the most flexible part of MyBatis, and it is very convenient to use them well.
Example: selectTotalNumByAccountType
<select id="selectTotalNumByAccountType" resultType="Integer" >select count(*) from user<trim prefix="WHERE" prefixOverrides="AND|OR">and id not in <foreach collection="idStr" item="ids" open="(" separator="," close=")"> #{ids} </foreach> <if test="userName!= null and userName!='' ">and userName LIKE CONCAT(CONCAT('%', #{userName}),'%')</if><if test="supplierno!= null and supplierno!='' ">and supplierNo LIKE CONCAT(CONCAT('%', #{supplierno}),'%')</if> <if test="trueName!= null and trueName!='' ">and trueName LIKE CONCAT(CONCAT('%', #{trueName}),'%')</if>AND accountType = #{accountType}</trim></select>The above is a summary of the usage knowledge of the MyBatis persistent layer framework introduced to you by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to Wulin.com website!