QA From shallow to deep Persistence Layer Framework (3)

“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.

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 code

Execute 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 againThe reality successfully printed out the value of the primary key and matched the primary key generated in the database

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

The method of adding an INSERT to the EmployeeDao interface does not increment the primary key.

int insertEmployeeWithAllProperties(Employee employee);
Copy the code

Add 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 code

The 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 code

Perform the test

The value of the primary key is successfully printed to view the data inserted into the database

The selectKey tag can be used to obtain the value of the primary key for a database that does not support auto-increment of the primary key