Variable parameters
The location of method parameters in java supports one syntax
Method name ([type]... Variable name)
The above expression is variable parameter
//Declare a method with variable parameters
public void sum(int... nums){
//Parameter... Indicates that the variables specified by this parameter are processed as an array
//Only one method can appear in this declaration
// And it must be the last parameter
//The and output of all elements in nums
int sum=0;
for(int i=0;i<nums.length;i++){
sum+=nums[i];
}
System.out.println(sum);
}
@Test
void param(){
//Call the method of variable parameters and directly pass in multiple parameters with matching types
//There is no limit to 0~ more parameters
sum(5,9,7,6);
sum();
}
Precautions for using variable parameters
When defining a method:
The variable parameter must be the last of all parameters of the method
Method treats variable parameters as an array
Method call:
When calling a method with variable parameters, the number of parameters that can be passed in is unlimited, and the type can match
An unlimited number of parameters means you can not transfer parameters
You can also directly pass in an array with matching type
Mybatis implements dynamic deletion
There is a requirement in our project to delete rows with multiple specified IDS at one time
For example, we want to delete the row with id 2,4,7,11
The sql code is as follows: delete from t_user where id in(?,?,?,?)
However, each deletion is not necessarily 4
So we need to generate the same number of IDS according to the array of IDS to be deleted passed in by the user?
Such requirements can only be realized through dynamic sql generation in xml files
Next, we add a method in the UserMapperPlus interface to correspond to this dynamic deletion
You can use the variable parameters learned above to declare
The code is as follows
//Method for realizing dynamic deletion Integer deleteByIds(Integer... ids);
Go back to the UserMapperPlus.xml file and write the code corresponding to this deletion
<!--
Delete operation use delete label id Method name in corresponding interface
Because it is an addition, deletion and modification operation,You do not need to declare the return value type resultType,Default is int
As for parameter types,Common parameters of the system can automatically identify basic data types,String,Array, etc
-->
<!--
collection Specifies the array to traverse array Represents the array received by the parameter
item Represents a single element in the array during traversal,id Is the name of this element
separator Specifies the separator that appears at each cycle interval during the cycle
foreach Write in label#{} placeholder. The content in the placeholder is the "id" specified by the item attribute
-->
<delete id="deleteByIds">
delete from t_user where id in(
<foreach collection="array" item="id" separator=",">
#{id}
</foreach>
)
</delete>
Test class startup log
private static Logger log=
LoggerFactory.getLogger(MybatisPlusTest.class);
Test class
@Autowired
UserMapperPlus userMapper;
@Test
void deleteIds(){
int num=userMapper.deleteByIds(2,4,7,11);
System.out.println(num);
}
Mybatis implements dynamic modification operations
During the development of the project, there may be the following requirements:
Modify user information
The user may specify some information to modify
User name and age may be modified
You may also modify your phone and email,
This requirement may modify any line of the user
If the whole row modification needs to be queried first and then modified, it will consume performance. We need to dynamically generate sql statements that only modify the attributes entered by the user according to the attributes entered by the user
analysis:
If everything can be modified, the parameter is better of User type
We can judge which attributes of User type objects are assigned and which attributes need to be modified. If they are null, they will not be modified
Declare the methods in the interface first
The code is as follows
//Method for realizing dynamic modification Integer updateUserInfo(User user);
Add dynamically modified code to the xml file
<!-- Dynamic modification method -->
<!-- Because the parameter of this method is not a common type provided by the system
Need to use parameterType To specify User Full class name of -->
<!-- if Required in label test To specify a boolean Type results
If the result is true Will if The content in the middle of the label is generated in sql Statement
The specific logic here is:as long as user A property of the object is not empty,
Generate and modify this property sql sentence-->
<!-- <set>Role of labels
1.stay<set>Label location generation set keyword
2.<set>The tag removes the last redundant","complete sql Statement generation
-->
<update id="updateUserInfo"
parameterType="cn.tedu.mybatis.model.User">
update t_user
<set>
<if test="username != null">
username=#{username},
</if>
<if test="password != null">
password=#{password},
</if>
<if test="age != null">
age=#{age},
</if>
<if test="phone != null">
phone=#{phone},
</if>
<if test="email != null">
email=#{email}
</if>
</set>
where id=#{id}
</update>
Test class
@Test
void update(){
User user=new User();
user.setId(16);
//user.setUsername("Marshal Tianpeng");
user.setPassword("666");
user.setAge(800);
int num=userMapper.updateUserInfo(user);
System.out.println(num);
}
Mybatis implements dynamic query
We often encounter this or similar requirements in actual development
There are several situations as follows
1. Query directly without entering criteria
select * from t_user
2. Enter a bar to query
select * from t_user where username like ?
3. Enter two items to query
select * from t_user where username like ? and age=?
4. Enter three entries to query
select * from t_user where username like ? and age=? and phone like ?
In fact, the above query is also a process of dynamically generating sql statements according to the conditions entered by the user
Method of adding dynamic query in UserMapperPlus interface
The code is as follows
List<User> findUsersByParam(
@Param("username") String username,
@Param("age") Integer age,
@Param("phone") String phone);
The xml file code is as follows
<!-- Dynamic query method -->
<!-- A query must declare a return value type resultType -->
<!-- Mybatis The type of value returned by an object or collection in a query
Specify the specific entity class type -->
<!-- <where>Labels and<set>Label similar
When no condition is where Keywords will not appear
When conditions are<where>The label is responsible for handling excess and-->
<select id="findUsersByParam"
resultType="cn.tedu.mybatis.model.User" >
select
id,username,password,age,phone,email
from
t_user
<where>
<if test="username != null">
username like #{username}
</if>
<if test="age != null">
and age = #{age}
</if>
<if test="phone != null">
and phone like #{phone}
</if>
</where>
</select>
Test class
@Test
void select(){
List<User> users=userMapper.findUsersByParam(
null,24,null);
for(User u:users){
System.out.println(u);
}
}
Use of parameterType and resultType
resultType is a property that specifies the return value of the current method
All select tags must be specified for both query operations
If you are adding, deleting or modifying, you do not need to specify the default int
parameterType is a property that specifies the parameter type of the current method
Any addition, deletion, modification and query operation may be required
If the parameter type is not the basic data type, it is not String, array or collection
It must be written. For example, the entity class created by our own declaration needs to be written
Mybatis implements Association query
Association relationship in database
There are several relationships between relational database tables
one-on-one
One to many (many to one)
Many to many
Taking one to many as an example, let's explain the solution for processing associated queries in the Mybatis framework
We will t_ The user table is regarded as an employee table
We will design a department table to establish a one to many relationship between the Department and the employee table
Since a department has multiple employees, the Department is one party of one department, and each employee can only belong to one department, the employee is one party of many
To realize such a relationship, we must first establish such a structure in the database
Perform the following operations in sequence
-- establish t_dept Table department table
create table t_dept(
id int primary key auto_increment comment 'department id',
name varchar(50) not null comment 'Department name'
)default charset=utf8
-- t_user A table needs to be added dept_id List your department
alter table t_user add column dept_id int
You can do the same: ALTER TABLE t_user ADD COLUMN dept_id INT NOT NULL AFTER email
-- Add 3 rows of data to the Department table to represent 3 departments
insert into t_dept(name) values('java'),('c++'),('linux')
-- take id Less than 10 user of dept_id Assign a value of 1
update t_user set dept_id=1 where id<10
-- take id 10 or more user of dept_id The value assigned is 2
update t_user set dept_id=2 where id>=10
The relationship between the two tables depends on t_ Dept of user table_ ID column
If the value of this column is 1, it means that it belongs to the java department
However, this value can now be modified at will. If it is modified at will, the user's dept cannot be guaranteed_ The value of the ID column can correspond to a real department, which is not possible
To set Dept_ The value of ID column can only be in the existing department, so you need to set foreign key constraints
-- Create foreign key constraints -- constraint t_user Tabular dept_id Columns can only be from t_dept Tabular id Value in column alter table t_user add constraint dept_user foreign key (dept_id) references t_dept(id) -- constraint(constraint) references(quote)
The creation of foreign keys will prevent error data when referring to data
Make the data in the database more accurate and reliable
However, because foreign keys bring a lot of maintenance operations, it is more troublesome
Now some companies do not create foreign keys when developing software, which makes it easier to maintain
sql statement for connection query
If we want to query the results as follows
The sql statement of the above query should be written like this
– query user's ID, user's username and dept's name
select
u.id,username,name
from t_dept d
join t_user u
on d.id=u.dept_id
What should I do if I want to execute such a connection query and return the results in java
The simplest way is to create a new entity class to complete the connection query
Create a UserVo class with the following code (VO: Value Object)
@Data
public class UserVo {
private Integer id;
private String username;
private String deptname;
}
Create a new interface in the mapper package. The XmlMapper code is as follows
@Repository
public interface XmlMapper {
//Method of connection query
List<UserVo> findUserDept();
}
This interface should correspond to a new Xml file
The code is as follows
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.tedu.mybatis.mapper.XmlMapper">
<select id="findUserDept"
resultType="cn.tedu.mybatis.vo.UserVo">
select
u.id,username,name deptname Alias to correspond pojo Name in
from t_dept d
join t_user u
on d.id=u.dept_id
</select>
</mapper>
Test code
@Autowired
XmlMapper xmlMapper;
@Test
void join(){
List<UserVo> users=xmlMapper.findUserDept();
for(UserVo user: users){
System.out.println(user);
}
}
Use the Mybatis query to return the associated query results
If you encounter more connection queries in actual development
If different Vo classes are created for each connection query, there will be more Vo classes
Look at the structure of this diagram
The meaning of this figure is that we can create a dept type entity class corresponding to t_dept table
In this entity class, we can declare a collection to hold the information of several user \ employees
The code of Dept class is as follows
@Data
public class Dept {
private Integer id;
private String name;
//Declare a collection that holds employee objects
private List<User> users;
}
We write an sql statement to query the Department information and the information of all employees contained in the department according to the specified Department id
The code is as follows
select
*
from
t_dept d
left join
t_user u
on
d.id=u.dept_id
where d.id=1
To implement the above query, we need to add a method in the interface
Continue adding methods to the XmlMapper interface
//How to query employees in related departments Dept findDeptWithUserById(Integer id);
Add the following code to the XmlMapper.xml file
Note the use of resultMap
<!-- We need a declaration of structure
This structure is a Dept Structure of type,Which contains Dept Medium id and name
Also include Dept Saved in List<User>users Information about
There's more right here User Each column of(include sql Alias column set in statement)
Make corresponding statements
So that we can Mybatis Return a Dept Objects can also be Dept Medium User In collection
Assign value to each employee
-->
<!-- resultMap It is specifically used to set the mapping relationship between each column and each attribute in the object
It supports mapping columns in a collection in an object
id yes resultMap Unique identification of,Which query requires which query to be specified
type Specifies the carrier of the return value of this result,This refers to the employee information Dept object-->
<resultMap id="deptMap" type="cn.tedu.mybatis.model.Dept">
<!-- One result The label represents a corresponding relationship,
finger java Which attribute in corresponds to which column in the database
column Specifies the column name of the database query result,Corresponding database ,property Specifies the property name in the instance class-->
<result column="id" property="id" />
<result column="name" property="name"/>
<!-- collection It means collection,Represents the current Dept class
except id and name There is also a collection
property Specifies the property name of the collection
ofType Specifies the generic type of the collection-->
<collection property="users"
ofType="cn.tedu.mybatis.model.User">
<!-- Here, you can specify which column corresponds to this query user Which property of the object -->
<result column="userId" property="id" />
<result column="username" property="username" />
<result column="password" property="password" />
<result column="age" property="age" />
<result column="phone" property="phone" />
<result column="email" property="email" />
</collection>
</resultMap>
<select id="findDeptWithUserById"
resultMap="deptMap">
select
d.id,
name,
u.id userId,
username,
password,
age,
phone,
email
from
t_dept d
left join
t_user u
on
d.id=u.dept_id
where d.id=#{id}
</select>
Test code
@Test
void dept(){
Dept d=xmlMapper.findDeptWithUserById(3);
System.out.println(d);
}
code
Query diagram
