“This is the 21st day of my participation in the First Challenge 2022. For details: First Challenge 2022”
SQL Mapping XML
The real power of MyBatis lies in its statement mapping, which is its magic. Because of its exceptional power, the MAPper’s XML file is relatively simple. If you compare it to JDBC code with the same functionality, you’ll immediately see that nearly 95% of the code is saved. MyBatis aims to reduce usage costs and allow developers to focus more on SQL code.
The namespace attribute in the mapper tag refers to the Dao layer interface class implemented by the mapping file
The SQL mapping file has only a few top-level elements (listed in the order they should be defined) :
cache– Cache configuration of the namespace.cache-ref– Reference the cache configuration of other namespaces.resultMap– Describes how to load objects from a database result set, and is the most complex and powerful element.sql– A reusable statement block that can be referenced by other statements.insert– Mapping insert statement.update– Mapping update statement.delete– Mapping deletion statement.select– Mapping query statement.
Ii. Engineering construction
Create a Maven project mybatis- Mapping-xml. The project construction steps are the same as those described in QA from shallow to deep persistence layer framework (ii) – Mybatis Config XML.
Insert, update and DELETE
The data change statements INSERT, UPDATE, and DELETE are very similar in that they all have the following attributes, of which the attributes highlighted in red are commonly used.
| attribute | describe |
|---|---|
id |
A unique identifier in the namespace that can be used to refer to this statement. |
| parameterType | The class-fully qualified name or alias that will be passed for the argument to this statement. This property is optional because MyBatis can infer the parameters of a specific incoming statement through the TypeHandler, which defaults to unset. Low frequency of use. |
| flushCache | Setting this to true causes the local and secondary caches to be cleared whenever a statement is called. The default is true (for INSERT, UPDATE, and DELETE statements). Low frequency of use. |
timeout |
This setting is the number of seconds the driver waits for the database to return the result of the request before throwing an exception. The default is unset (database driven). |
| statementType | The value can be STATEMENT, PREPARED or CALLABLE. This will make MyBatis use Statement, PreparedStatement, or CallableStatement, respectively. The default value is PREPARED. Low frequency of use. |
useGeneratedKeys |
(for INSERTS and Updates only) This will cause MyBatis to use JDBC’s getGeneratedKeys method to retrieve primary keys generated internally by the database (e.g. Auto-increment fields for relational database management systems like MySQL and SQL Server), default: false. |
keyProperty |
(for INSERT and UPDATE only) Specifies a property that uniquely identifies the object. MyBatis sets its value using the return value of getGeneratedKeys or the selectKey child element of the INSERT statement. Default: not set (unset). If more than one column is generated, you can separate multiple attribute names with commas. |
| keyColumn | (for INSERT and UPDATE only) Sets the name of the column in the table that generates the key value. In some databases (like PostgreSQL), this is mandatory when the primary key column is not the first column in the table. If more than one column is generated, you can separate multiple attribute names with commas. Low frequency of use. |
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. |
Gets the value of the auto-increment primary key
How do I obtain the primary key value generated after data is inserted into a database that supports automatic primary key increment?
Modify the test method of insertEmploye in EmployeeDaoTest and print the primary key value after successful insertion
@Test
public void insertEmployee(a) {
Employee employee = new Employee();
employee.setEmpName("peter");
employee.setGender(0);
employee.setEmail("[email protected]");
EmployeeDao employeeDao = openSession.getMapper(EmployeeDao.class);
employeeDao.insertEmployee(employee);
openSession.commit();
System.out.println("ID:" + employee.getId());
}
Copy the codeExecute test method
The database is successfully inserted
MyBatis = null; MyBatis = null; MyBatis = null;
Modify the SQL statement corresponding to the insertEmployee method to add two attributes
<insert id="insertEmployee" useGeneratedKeys="true" keyProperty="id">
INSERT INTO t_employee(empname,gender,email) values (#{empName},#{gender},#{email})
</insert>
Copy the code- UseGeneratedKeys: Default to false, that is, do not use the generated primary key
- KeyProperty: Assigns an autoincrement primary key to the specified property
Execute the test method again
UseGeneratedKeys =”true” means that MyBatis calls getGenerateKeys() in Statement, obtains the primary key automatically generated by the database, and assigns it to the entity class property specified by the keyProperty property
Gets the value of a non-increment primary key
For databases that do not support self-generated primary keys, such as Oracle, you can use the selectKey subtag. Statements in the selectKey subtag can be set to be executed first or later. If the value is set to run first, the maximum ID can be found, the +1 operation is performed, and then the value is assigned to the newly inserted ID.
The selectKey tag has the following properties
| attribute | describe |
|---|---|
keyProperty |
selectKeyThe target property to which statement results should be set. If more than one column is generated, you can separate multiple attribute names with commas. |
keyColumn |
Returns the column name of the generated column attribute in the result set. If more than one column is generated, you can separate multiple attribute names with commas. |
resultType |
Type of result. Usually MyBatis can be inferred, but for more accuracy, there is no problem with writing. MyBatis allows any simple type to be used as a primary key type, including strings. If more than one column is generated, you can use an Object or Map that contains the desired attributes. |
order |
Can be set toBEFORE 或 AFTER. If set toBEFORE, so it first generates the primary key, setkeyPropertyThen execute the insert statement. If set toAFTER, so the insert statement is executed first, and thenselectKey– Similar to the behavior of an Oracle database, there may be an embedded index call inside an insert statement. |
statementType |
As before, MyBatis supportsSTATEMENT.PREPARED 和 CALLABLEType of mapping statement, respectively representingStatement.PreparedStatement 和 CallableStatementType. |
The method of adding an INSERT to the EmployeeDao interface does not increment the primary key.
int insertEmployeeWithAllProperties(Employee employee);
Copy the codeAdd the SQL mapping statement of the response to employee.xml, using the calculated value detected by the selectKey tag as the inserted primary key of the ID
<insert id="insertEmployeeWithAllProperties">
<selectKey order="BEFORE" resultType="integer" keyProperty="id">
select max(id)+1 from t_employee
</selectKey>
INSERT INTO t_employee(id,empname,gender,email) values (#{id},#{empName},#{gender},#{email})
</insert>
Copy the codeThe order attribute defines the order in which SQL statements in the selectKey tag are executed first
Increase in EmployeeDaoTest insertEmployeeWithAllProperties test method
@Test
public void insertEmployeeWithAllProperties(a) {
Employee employee = new Employee();
employee.setEmpName("hulk");
employee.setGender(0);
employee.setEmail("[email protected]");
EmployeeDao employeeDao = openSession.getMapper(EmployeeDao.class);
employeeDao.insertEmployeeWithAllProperties(employee);
openSession.commit();
System.out.println("ID:" + employee.getId());
}
Copy the codePerform the test
The value of the primary key is successfully printed to view the data inserted into the database
