Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "how to use ResultMap in Mybatis to solve the problem of inconsistent attribute names and database field names". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Preface

The inconsistency between the property names of our Pojo class and the field names in the database occurs from time to time. In a simple case, we can turn on hump naming to solve the case problem, but when we encounter other non-case problems, we have to use the result set in Mybatis to map resultMap.

1. Inconsistent field names

Fields in the database

The fields of the entity class in our project

Public class User {private int id; private String name; private String password; workaround:

The first way: alias

Pwd as password

Select id,name,pwd as password from user1 where id = # {id}; second way: result set mapping resultMap

Use resultMap to match the property name in the pojo class with the value of the field in the database.

Where column is the field in the database and property is the attribute in the entity class.

Select * from user1 where id = # {id}

There is a passage in the official document:

The resultMap element is the most important and powerful element in MyBatis

The design idea of ResultMap is to achieve zero configuration for simple statements and only to describe the relationship between statements for more complex statements.

The beauty of ResultMap is that you don't have to display the same properties with the same field name.

According to the official documentation, the above result set mapping can be simplified to:

Select * from user1 where id = # {id}; 2. Many-to-one processing

The problem described above is only the inconsistency of ordinary field names, which is relatively easy to solve using result set mapping. In Mybatis, the problems solved by result set mapping are famous for many-to-one processing and one-to-many processing (these two problems are relative).

There are many related examples in our lives. Here, take teachers and students as examples. For students, multiple students have a common teacher, which is a many-to-one problem; for teachers, a teacher has many students. This is another one-to-many problem.

Related resolution:

For students, it belongs to the phenomenon of relevance, in which multiple teachers are associated with a teacher (many to one)

For teachers, it belongs to the collective phenomenon, a teacher has many students (one to many)

Test environment building

[1] establish a list of teachers and students

CREATE TABLE `teacher` (`id` INT (10) NOT NULL, `name` VARCHAR (30) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE = INNODB DEFAULT CHARSET=utf8INSERT INTO teacher (`id`, `name`) VALUES CREATE TABLE `student` (`id` INT (10) NOT NULL, `name` VARCHAR (30) DEFAULT NULL, `tid` INT (10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fktid` (`tid`), CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`) ENGINE=INNODB DEFAULT CHARSET=utf8INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1'); INSERT INTO `student` (`id`, `name`, `tid`) VALUES INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3Little Zhang','1'); INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4Li','1'); INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5Li','1')

[2] create a new Maven project, import related dependencies, and write entity classes and their mappings

The project directory after construction is as follows:

Import dependencies and resolve packaging problems

Mysql mysql-connector-java 5.1.46 org.mybatis mybatis 3.5.2 junit junit 4.13 org.projectlombok lombok 1.18.8 src/main/resources * * / .properties * * / .xml true src/main/java * * / * .properties * * / .xml true

Write database configuration file db.properties

Driver = com.mysql.jdbc.Driverurl = jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF-8username = root

Write Mybatis core configuration file

Write entity classes

There is a teacher's field in the Student class here, which is inconsistent with our database and is the focus of this problem solving.

Student.class

Package com.gs.pojo;import lombok.Data;/** * @ Auther: Gs * @ Date: 2020-6-9 * @ Description: com.com.com.gs.pojo * @ version: 1.0 * / @ Datapublic class Student {private int id; private String name; / / A teacher private Teacher teacher;}

Teacher.class

Package com.gs.pojo;import lombok.Data;/** * @ Auther: Gs * @ Date: 2020-6-9 * @ Description: com.com.com.gs.pojo * @ version: 1.0 * / @ Datapublic class Teacher {private int id; private String name;}

Write interface StudentMapper

Public interface StudentMapper {/ / query all student information and corresponding teacher information public List getStudent ();}

Use resultMap to solve problems

[1] nested queries by result use association fields to match field properties in Teacher in the result set

Select s.id sid, s.name sname, t.name tname from student s, teacher t where s.tid = t.id

Parsing: at first, the sql statement must be very round. let's start with the original sql statement and write a statement that can be queried by join tables (because there is an entity attribute of the teacher in the student class).

Here we use aliases and associated table query to get the student number, student name, teacher name, it is not difficult to find that we use simple reultType can not solve this multi-attribute problem (resultType is suitable for solving a single entity class, field name and database attribute one-to-one corresponding situation), so we want to use the result set mapping resultMap.

Select s.id sid, s.name sname, t.name tname from student s, teacher t where s.tid = t.id

The field names and attribute names of the first two items in resultMap are easy to map, directly the student's id, the student's name, but the attribute of the last item is a Teacher object, so we must use the associated association to nest the attributes of the teacher's entity class. The related statements are:

Then the complete implicit statement is:

Finally, we can concatenate the two sentences and get our final sentence.

[2] nesting according to the query (relatively complex to understand)

Select * from student; select * from teacher where id = # {id}

Parsing: looking at the statement is even more dizzy, which is actually equivalent to our native Sql subquery, which is divided into three steps to encapsulate the original result set through the query.

Write a simple student query

Select * from student

To complete the mapping of relevant entity class attributes and database fields, the first two are still simple, and the key is the entity class Teacher. This time we do not nest attributes, but solve the problem through a subquery (property corresponds to entity class attributes, column corresponds to database name, javaType indicates the type, and select indicates the method of using query)

Here we use the method of getTeacher to get the new property value

Select * from teacher where id = # {id}

Complete mapping

Finally, we assemble these three sentences of sql and we can get the final result above.

3. One-to-many processing

The same example above, for teachers, is an one-to-many relationship.

[1] the environment is the same as above.

[2] changes in entity classes tested

Student

@ Datapublic class Student {/ / student id private int id; / / student name private String name; / / teacher id private int tid;}

Teacher

@ Datapublic class Teacher {private int id; private String name; / / A teacher has multiple students private List students;}

[3] Business requirements: get the information of all students and teachers themselves under the designated teacher.

Write an interface

/ / get the information of all students and teachers under the designated teacher Teacher getTeacher (@ Param ("tid") int id)

There are still two solutions, one is to nest the query by result (the so-called join query), and the other is to nest the query (the so-called subquery)

The first way: nesting queries by result

Write sql statements for join queries

Select s.id sid, s.name sname, t.id tid, t.name tname from student s, teacher t where s.tid = t.id and t.id = # {tid}

Find the appropriate attribute through the result set mapping (because it is an one-to-many relationship, use the collection type collection)

Related resolution:

Complex attributes: we need to deal with objects separately, many-to-one using association, and one-to-many using collection

If a normal pojo class uses javaType= "" to specify the attribute type, if it is a generic type, use ofType to specify the generic type in the attribute.

3) Let's splice the above two pieces of code to get our result.

Select s.id sid, s.name sname, t.id tid, t.name tname from student s, teacher t where s.tid = t.id and t.id = # {tid}

The second way: according to the query nesting processing

Splicing using subqueries

First find out the information of the designated teacher.

Select * from teacher where id = # {tid}

Because there is student object information in the query information, it is necessary to map the result set.

Parsing:

Property indicates the attribute name of the entity class to be queried, javaType indicates the type of query, ofType indicates the type of generic type, and select indicates the way to use the query. What is confusing here should be the value of column, which corresponds to the columns in the database, so what should it use? it is not difficult to find that we want to query all the students under the teacher, while the students indicate that they are associated through the teacher's id. Then its corresponding value should be the column name of id, the teacher of the database.

Finally, write the method of querying students through the teacher's id. GetStudent

Select * from student where tid=# {id}

Finally, we can splice the above code blocks to meet our business needs.

Select * from teacher where id=# {tid} select * from student where tid=# {id} "how to use ResultMap in Mybatis to solve the problem of inconsistency between attribute names and database field names" ends here. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.