Mybatis has been a professional course in the school for three weeks, and one and a half of them have been studying mapping files. First of all, thank teacher Anna for her guidance. Her teaching is very serious and responsible, and the content is also very rich. However, due to the many and miscellaneous mapping contents, the content is boring and boring (fennel has several writing methods), I have learned numbness recently... Today, in my spare time, I decided to summarize the way of mapping
Introduction to the basic structure of the project
If you want to learn framework technology, project architecture is an unavoidable foundation. If you don't clarify the structure of the framework, any learning is chaotic. Therefore, first of all, you should introduce the basic project architecture of the author during learning. The author's learning of mybatis mainly depends on the eclipse Maven project. Because mybatis learning only involves databases, Therefore, the architecture of the project basically only needs to stay at the database layer. The following figure can clearly show the project structure. Of course, this architecture can only represent the habits of the author and my teachers, and is not the only standard structure.
In the Java folder, all java files are stored, including:
1.entity class folder
2.mapper mapping folder. The interface of database operation method to be implemented by mybatis will be defined here, but it is not implemented. It is equivalent to dao layer in jsp+servlet project structure. However, when we adopt Mybatis+spring+spring Boot architecture, the name dao can be replaced by mapping
3. Maintest class folder. Generally, run the methods defined in mapper here to add, delete, modify and query the database, so as to verify whether the business objectives are achieved
4.util toolkit class. Mainly MybatisUtil
In the resources folder, non java files, mainly xml files, are stored, including:
1.config configuration file mainly includes mybatis-config.xml and database property configuration file mybatis-config.xml. The content of mybatis config file is also rich, which will not be repeated in this paper. Property refers to a. Properties file, which is used to specify jdbc.driver, jdbc.url, jdbc.username and jdbc.password, that is, the properties of the database. This configuration method can avoid modifying the project at the source level when the database environment changes. The author uses My SQL database
2. The mapper mapping file implements the interface defined in the java/com.qdu.mapper file, realizes the attribute mapping of sql statements and java objects, and returns the result set. How to get the result set of the database smoothly is the content of our research today. It is worth noting that in the mybatis-config.xml configuration file, the configuration file here needs to be added to the < mappers > tag, For example, if I have just finished writing StudentMapper.xml, I also need to add the following code in mybatis-config.xml:
<mappers>
<mapper resource="com/qdu/mapper/StudentMapper.xml"/>
</mappers>In addition, the location of < mappers > tags in the mubatis-config.xml file is not arbitrary. The specific tag sorting method belongs to the knowledge points of the mybatis configuration file and will not be repeated today
log4j2.xml log configuration file. log4j2 is a very practical log implementation framework, which will not be repeated today. (the more you write, the more you feel you need to fill in a lot...)
Simple query of one parameter
When implementing a method of interacting with the database, the following three steps are required:
1. Define the methods you need in src/main.java.mapper.
2. Implement this method with sql statement in main.resources.com.qdu.mapper.xml. (note that if mapper.xml has just been created, don't forget to add the < mapping > tag in mybatis-config.xml.)
3. Apply this method where you need it. Including but not limited to test classes and spring framework. Here I will apply the method in src/main.java.main.
The following is a simple example to show this process.
For example, in a student table, query a single student by student id.
To implement this method, we define StudentMapper.java and StudentMapper.xml
1. Define the methods you need in src/main.java.mapper.
//1.1 query individual students according to student number
Student getOneById(String id);A simple method, pass in the Student id with the parameter of String type to get the Student object.
2. Implement this method in main.resources.com.qdu.mapper.xml.
<select id="getOneById" resultType="com.qdu.entity.Student" parameterType="string">
select * from student where sid = #{id}
</select>This query is implemented using sql statements.
< The Select > tag is used to map a query operation of the Mapper interface. It can have the following parameters:
id: specify a name to uniquely identify each operation. It must be the same as the method name in the mapping interface.
resultType: specifies what type the query result is encapsulated into, which can be either a class name or an alias
parameterType: the parameter type passed in this statement, which can generally be omitted, because the system can automatically determine the parameter type according to the passed in parameters
statementType: the type of statement that executes the sql statement. This is also involved in learning jdbc. It has three types
1.STATEMENT: use the statement interface to execute sql statements. If it is executed only once, it is recommended to use the statement interface
2.PREPARED: use PreparedStatement for execution. Because precompiling is required, it is suitable for repeated execution. PREPARED is the default value
3.CALLABLE: this type is used by calling stored procedures
In addition, there are many ways to pass parameters to SQL statements. The most commonly used methods are #{parameter name} and ${parameter name}, which are different. From a security perspective, #{} can prevent SQL injection, but ${} cannot. In principle, #{} when the SQL statement is executed, it will be replaced with?, Then we call the Set method in PreparedStatement to assign the value, and the $+ will be directly replaced by the value of the variable. This determines that there is a certain difference in the usage of the two. For example, as in the previous example, if you use ${} to pass in parameters, it is written as follows:
<select id="getOneById" resultType="com.qdu.entity.Student" parameterType="string">
select * from student where sid = '${id}'
</select>Especially in queries involving string operations, we need to pay special attention to the difference between the two. For example, you need to fuzzy query statements containing keyword s:
select ... from ... where ... like
The equivalent expression of the two is as follows:
select ... from ... where ... like concat('%',#{keyword},'%') //Usage #{} description
select ... from ... where ... like '%${keyword}%' //Use ${} description3. Apply this method where you need it.
public class Test01_1_Query individual students according to student number {
public static void main(String[] args) {
String sid = "2019204385";
Student s = null;
try (SqlSession session = MybatisUtil.openSession()) {
s = session.selectOne("com.qdu.mapper.StudentMapper.getOneById", sid);
}
System.out.println("-------------------------------------------------------------------------------------------");
System.out.println("Student number\t\t full name\t\t password\t\t Gender\t class");
System.out.println("-------------------------------------------------------------------------------------------");
System.out.println(s);
}
}In src/main.java.main, the test class is defined to implement this method. Note how the session is opened, which is necessary in mybatis. The session.selectOne() method is used to return a result, where the returned Student object is directly assigned to s.
Query with multiple parameters
When there are multiple parameters in the method, you can use the annotation @ Param("parameter alias") in the Mapping interface to mark multiple parameters, and then directly use the parameter name in Mapping.xml
Example: query a single student by name and id
1. Define the methods you need in src/main.java.mapper.
//1.1 query individual students according to student number and name
Student getOneByIdAndName(@Param("id") String id, @Param("name") String name)Using @Param annotation to identify aliases for multiple parameters, it is easy to invoke in mapper.xml. Of course, this is not necessary, because the system will automatically name the parameters "param1", "param2", etc., but I think the parameter names defined by myself are more practical.
2. Implement this method in main.resources.com.qdu.mapper.xml.
Using the param just defined, implement the mapping interface
<select id="getOneById" resultType="com.qdu.entity.Student" parameterType="string">
select * from student where sid = '${id}' and sname='${name}'
</select>3. Apply this method where you need it.
public class Test01_1_Query individual students according to student number {
public static void main(String[] args) {
String sid = "2019204385";
String sname="Zhang San";
Student s = null;
try (SqlSession session = MybatisUtil.openSession()) {
StudentMapper mapper=session.getMapper(StudentMapper.class);
s=mapper.getOneByIdAndName(sid,sname);
}
System.out.println("-------------------------------------------------------------------------------------------");
System.out.println("Student number\t\t full name\t\t password\t\t Gender\t class");
System.out.println("-------------------------------------------------------------------------------------------");
System.out.println(s);
}
}Pay attention to the method passed in by multiple parameters. selectone is no longer used
Query multiple results and use the Map collection
When our query needs to return an object List, we can use the List type to store the results.
Example: query student list according to class name and gender:
1. Define the methods you need in src/main.java.mapper.
List<Student> getStudentListByBatchAndGender2(Map<String,Object> map);
Use map to pass in parameters. The key of map is the name of the parameter and the value is the value of the parameter
2. Implement this method in main.resources.com.qdu.mapper.xml.
<select id="getStudentListByBatchAndGender2" resultType="com.qdu.entity.Student">
select * from student where sgender=#{xingbie} and sbatch=#{banji}
</select>3. Apply this method where you need it.
public class Test08_Query student list according to class and gender 2 {
public static void main(String[] args) {
List<Student> list = null;
//Multiple parameters required by sql statements can be encapsulated into a Map object
Map<String,Object> map=new HashMap<>();
//Add data items to the Map collection, each consisting of a key and a value
//Key is the parameter name of the parameter required by the sql statement
//Value is the parameter value of the parameter required by the sql statement
map.put("xingbie", "female");
map.put("banji", "19 Software J01");
try (SqlSession session = MybatisUtil.openSession()) {
//1) If you use methods such as selectList(), you can pass in the Map object containing the parameters required by the sql statement as the second parameter
list=session.selectList("getStudentListByBatchAndGender2",map);
//2) If you use getMapper() to execute sql statements, you need to set the parameter type of the method of the corresponding Mapper interface to Map type
//Then the Map object that contains the parameters of the sql statement is called as a parameter when it is called.
//list=session.getMapper(StudentMapper.class).getStudentListByBatchAndGender2(map);
}
System.out.println("-------------------------------------------------------------------------------------------");
System.out.println("Student number\t\t full name\t\t password\t\t Gender\t class");
System.out.println("-------------------------------------------------------------------------------------------");
for (Student s : list) {
System.out.println(s);
}
}
}Use ResultMap result mapping
In the above example, we have never paid attention to the attribute mapping between database forms and Java objects. This is because the system will automatically map when the database column name is exactly the same as the Java object attribute name. When there is a difference between the database listing and the Java object attribute name, we need to manually map the results through ResultMap to ensure the normal implementation of the function. These mainly occur in Mapper.xml
For example, the attribute naming formats of product forms and product objects are different. In the product object, the names of attributes are name, price, unit, stock and image, while in the product form, they correspond to ProductName, productprice, productunit, productstock and productimage respectively. At this time, you can only map them manually.
The following is the implementation of mapping in Mapping.xml.
<resultMap id="productMap" type="com.qdu.entity.Product">
<!--id Tags are used to map primary key columns and key attributes. For attributes with the same name, you can not explicitly write the mapping, and it will be mapped automatically-->
<id property="productId" column="productId" />
<!--result Tags are used to map non primary key columns and non key attributes-->
<result property="name" column="productName" />
<result property="price" column="productPrice" />
<result property="unit" column="productUnit" />
<result property="stock" column="productStock" />
<result property="image" column="productImg" />
</resultMap>< The resultMap > tag is used to implement mapping, and the above is its usage. Each resultMap has an identifier, i.e. id. according to this id, in the mapping interface implementation method below, declare that the result mapping is adopted to complete its use process.
The following is the call to the mapping in Mapping.xml.
<select id="getOne1" resultMap="productMap" parameterType="string">
select * from product where productId = #{productId}
</select>Addition, deletion and modification
The mapping method of addition, deletion and modification is the same as that of query. It is worth noting that when you use the session to complete the addition, deletion and modification operation, remember to submit the modification, otherwise the modification will not be saved in the database.
For example, the operation of updating student information
public class Test02_Update student information {
public static void main(String[] args) {
Student s = new Student("2019201111", "Xiaoming 2", "777777", "F", "18 Software J07");
try (SqlSession session = MybatisUtil.openSession()) {
session.getMapper(StudentMapper.class).update(s);
session.commit();
System.out.println("Modified student information!!!");
}
}
}
Don't forget the session.commit operation
Example: how to add Id automatically when inserting a new entry in a form?
In Mapping.xml, implement this operation:
<insert id="insert1" parameterType="category" useGeneratedKeys="true" keyProperty="cid" keyColumn="cid">
insert into category values(#{cid},#{cname},#{cdescription})
</insert>useGeneratedKeys attribute: Specifies that the self incrementing primary key is used. After it is adopted, the inserted object id column can be empty
keyProperty property: Specifies the primary key column
Example: how to customize an id generation method?
<insert id="insert2" parameterType="category">
<selectKey resultType="int" keyProperty="cid" order="BEFORE">
select ceiling(rand()*1000)
</selectKey>
insert into category values(#{cid},#{cname},#{cdescription})
</insert>The < selectkey > tag can be used to assign parameters.
order attribute: Specifies the execution time of the selectKey. BEFORE is BEFORE the addition, deletion and modification, and AFTER is AFTER
Associated table query: one-to-one
Four ways to implement one-to-one relationship mapping
1. Use resultMap element to map association table attributes
2. Use column alias to realize one-to-one relationship mapping
3. Use association tag to realize nested result mapping
4. Use association tag to realize nested query mapping
For example, in a model, Customer and BankCard correspond one-to-one, and they are linked through CardId
1. Use resultMap element to map association table attributes
You can use the Customer object to encapsulate the result set and use the BankCard object in the Customer object.
Of course, a prerequisite for doing so is to modify the Customer entity class. That is, directly add a BankCard attribute in the Customer entity class, and add the BankCard attribute to the Customer.BankCard after the associated table query. The following section is an addition to the content of the Customer.java entity class.
private BankCard bankCard;
public BankCard getBankCard() {
return bankCard;
}
public void setBankCard(BankCard bankCard) {
this.bankCard = bankCard;
}After completing the above operations, you can encapsulate the results of the join table query through the result set.
<resultMap id="custMap1" type="customer">
<id property="custId" column="custId" />
<result property="custName" column="custName" />
<result property="custGender" column="custGender" />
<result property="custDob" column="custDob" />
<result property="custGrade" column="custGrade" />
<result property="cardId" column="cardId" />
<!-- If the relationship is mapped, you want to encapsulate the customer and bank card information into one Customer Object -->
<!-- Then you need to encapsulate the bank card (association table) information into bankCard Attribute -->
<!-- Explain here cardId The value of the column is stored in the bankCard Attribute cardId attribute -->
<!-- cardBalance The value of the column is stored in the bankCard Attribute cardBalance column -->
<result property="bankCard.cardId" column="cardId" />
<result property="bankCard.cardBalance" column="cardBalance" />
</resultMap>After determining the above encapsulation method, you can directly encapsulate it in Mapper.xml.
<select id="query4" resultMap="custMap1">
select
a.*,
b.cardBalance
from customer a, bankcard b
where a.cardId=b.cardId and a.custId=#{id}
</select>When the test class uses this method, pay attention to the way BankCard is called
public class Test04_Join table query_Individual customer and their bank card information 2_Customer {
public static void main(String[] args) {
String custId = "U0001";
Customer customer = null;
try (SqlSession session = MybatisUtil.openSession()) {
customer = session.selectOne("query4", custId);
}
System.out.println("--------------------------------------------------------Customer information--------------------------------------------------------");
System.out.println("Customer number:" + customer.getCustId());
System.out.println("Customer Name:" + customer.getCustName());
System.out.println("Client gender:" + customer.getCustGender());
System.out.println("date of birth:" + customer.getCustDob());
System.out.println("Customer level:" + customer.getCustGrade());
System.out.println("Bank card No.:" + customer.getCardId());
System.out.println("--------------------------------------------------------Card information--------------------------------------------------------");
System.out.println("Card No.:"+customer.getBankCard().getCardId());
System.out.println("Balance:"+customer.getBankCard().getCardBalance());
System.out.println("----------------------------------------------------------------------------------------------------------------------");
}
}2. Use column alias to realize one-to-one relationship mapping
This method also requires the refactoring of the entity class Customer in the previous method. Its idea is relatively simple, that is, the column name obtained by the query is given the same name as the attribute in the entity class to directly realize the mapping. For example, we can implement the same topic in this way.
<select id="query5" resultType="customer">
select
a.*,
b.cardId as 'bankCard.cardId',
b.cardBalance as 'bankCard.cardBalance'
from customer a, bankcard b
where a.cardId=b.cardId and a.custId=#{id}
</select>Mapping the result set directly into a customer object saves the definition of result set mapping, but increases the complexity of SQL statements.
3. Use association tag to realize nested result mapping
That is to realize the nesting of result set and result set. For example, the custMap2 result set below realizes the effect of associating tables through the association with the bankCard result set.
<resultMap id="custMap2" type="customer">
<id property="custId" column="custId" />
<result property="custName" column="custName" />
<result property="custGender" column="custGender" />
<result property="custDob" column="custDob" />
<result property="custGrade" column="custGrade" />
<result property="cardId" column="cardId" />
<!-- Nested result mapping is to use association Element makes the mapping of the associated table into a nested result mapping -->
<!-- property Specifies the name of the attribute of the mapping relationship -->
<!-- javaType Specifies the data type of the attribute of the mapping relationship(Fully qualified name or alias)，This attribute can be omitted -->
<!--
<association property="bankCard" javaType="com.qdu.entity.BankCard">
<id property="cardId" column="cardId" />
<result property="cardBalance" column="cardBalance" />
</association>
-->
<!-- If an existing result map exists, you can use it resultMap Property specifies the of the result map id that will do -->
<!-- However, if the resulting mapping comes from another mapping file, you must use"Namespace.id"Only then -->
<association property="bankCard"
resultMap="com.qdu.mapper.BankCardMapper.bankCardMap" />
</resultMap>The sql statement is implemented as follows.
<select id="query8" resultMap="custMap2">
select a.*,b.cardId,b.cardBalance from customer a,bankcard b where a.cardId=b.cardId
</select>It is used in the same way as the second one.
4. Use association tag to realize nested query mapping
This method is to specify a query to query the data of the associated table
<resultMap id="custMap3" type="customer">
<id property="custId" column="custId" />
<result property="custName" column="custName" />
<result property="custGender" column="custGender" />
<result property="custDob" column="custDob" />
<result property="custGrade" column="custGrade" />
<result property="cardId" column="cardId" />
<!-- Nested query mapping is to specify a query to query the data of the associated table, and specify the full name of the query
that is"The name of the namespace.id"
column Specifies the column name of the foreign key column, that is, the name of the column that embodies the relationship
fetchType Specifies how the associated table data is loaded
eager: Load now, query customer The associated table will be queried immediately when the table data bankcard Data
lazy: Deferred loading, for associated tables bankcard The data will be queried and loaded only when it is used
Only nested query mappings can use deferred loading
-->
<association property="bankCard" column="cardId" fetchType="lazy"
select="com.qdu.mapper.BankCardMapper.query2" />
</resultMap>com.qdu.mapper.BankCardMapper.query2 is the method to query BankCard through CardId.
In this way, the writing of SQL statements can be simplified.
<select id="query9" resultMap="custMap3">
<!-- If you are using nested result mappings, you must query multiple tables for all required columns -->
<!-- If you are using nested query mapping, you only need to query one table, and the other table will execute nested queries -->
select * from customer where custId=#{id}
</select>Associated table query: one to many
Two ways to map one to many relationships
1. Use the collection tag to realize nested result mapping
2. Use the collection tag to implement nested query mapping
For example, in a model, one department corresponds to multiple employee s
As with one-to-one, one to many also requires some refactoring of entity classes. For this example, in the Department entity class, you need to add a List to access the Employee of the Department. The following is the refactoring of the added content in the Department entity class.
private List<Employee> employeeList;
public List<Employee> getEmployeeList() {
return employeeList;
}
public void setEmployeeList(List<Employee> employeeList) {
this.employeeList = employeeList;
}After completing this step, we map it.
1. Use the collection tag to realize nested result mapping
The mapping of the result set is realized by the following methods.
<resultMap id="deptMap2" type="department">
<id property="deptId" column="deptId" />
<result property="deptName" column="deptName" />
<!-- collection Property name used to specify the mapping relationship -->
<!-- * ofType:Specifies the data type of the data item in the list -->
<collection property="employeeList" javaType="list" ofType="employee">
<id property="empId" column="empId"/>
<result property="empName" column="empName"/>
<result property="empGender" column="empGender" />
<result property="deptId" column="deptId" />
</collection>
</resultMap>Then the result set can be obtained through normal query.
<select id="query7" resultMap="deptMap2">
<!--Nested result mapping requires querying multiple tables to obtain all required columns-->
select * from department a,employee b where a.deptId=b.deptId
and a.deptId=#{id}
</select>2. Use the collection tag to implement nested query mapping
Similar to association, collection can also realize the mapping of nested queries, and the idea is similar.
<resultMap id="deptMap3" type="department">
<id property="deptId" column="deptId" />
<result property="deptName" column="deptName" />
<collection property="employeeList"
javaType="list"
ofType="employee"
column="deptId"
fetchType="lazy"
select="com.qdu.mapper.EmployeeMapper.query5" />
</resultMap>Similarly, this eliminates some SQL code.
<select id="query8" resultMap="deptMap3">
<!--Nested query mapping queries only department Just watch-->
select * from department where deptId=#{id}
</select>Summarize the mapping of join table query. association implements 1-to-1 mapping, and collection implements one to many mapping. The ideas of the two are similar. Both need to reconstruct the entity class first so that it can save the results of the associated table query, and then query on this basis. Both can map the result set or query. Each method has its own advantages and disadvantages. The mapping result set needs to write the SQL statement completely, but the mapping query does not. As for how to use it, it depends on your personal preferences.
It's written that it's been an afternoon and there's really a lot of content. Please correct any mistakes. Thank Miss Anna again. The examples and codes in this article are all teacher Anna's class materials.