Documentation for mybatis3 version -- > mybatis document
Write in front
Ok, which is also the part of dynamic sql;
It should be noted that later, I added a column recording age to the employee table. Of course, I also added attributes and its getset method to the Employee class; Construction method; toString(); These are also added with this attribute
1. if tag and where tag
if tag can be used to judge the condition;
It is generally used in combination with other labels;
The where tag can dynamically add the where keyword, and automatically remove the and or keyword when it does not meet the conditions
The < where > element will judge. If it contains a return value in the tag, it will insert a where.
If the content returned by the tag starts with AND OR, it will automatically remove AND OR.
For example, I want to set two query criteria, name and gender, to filter the information of query personnel, but I can also query without these two criteria, or I can query with one criteria instead of the other;
Of course, you can write 4 SQL statements for processing;
However, it is more convenient to use dynamic SQL query at this time. You only need to write SQL statement once to take into account these four situations at the same time
See the effect in the example
Ok, write a method in the persistence layer interface of the EmployeeMapper Employee class; Dynamically query employee information according to age and gender;
//Dynamically query the employee list according to conditions; Name / gender@ Param() annotates the parameter, and then the SQL side will use the name in the annotation;
List<Employee> getEmpByNameOrAge(@Param("name") String name, @Param("sex")String sex);
Add the corresponding sql in EmployeeMapper.xml;
<?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">
<!--Note that the corresponding space here is the persistence layer mapping interface-->
<mapper namespace="com.xiaozhi.mapper.EmployeeMapper">
<!--Define mapping relationships map-->
<resultMap id="empmap" type="employee">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="sex" property="sex"/>
<!--Table of related departments-->
<association property="dept" javaType="dept">
<result column="dname" property="name"/>
</association>
<!--Associated user management table-->
<association property="user" javaType="user">
<result column="account" property="account"/>
</association>
</resultMap>
<!--By name/Gender dynamic query employee-->
<select id="getEmpByNameOrAge" resultMap="empmap">
SELECT
e.`id`,
e.`name`,
e.`sex`,
e.`age`,
d.`name` dname ,
u.`account`
FROM t_employee e
LEFT JOIN t_dept d ON e.`deptID`=d.`id`
LEFT JOIN t_user u ON u.`id`=e.`optionId`
<where>
<if test="name != null & name !='' ">
e.name = #{name}
</if>
<if test="sex != null & sex !='' ">
and e.sex= #{sex}
</if>
</where>
</select>
</mapper>
of course; In the core configuration file mybatis-config.xml, the mapper mapping file address of employees should also be configured;
(I configured this before)
Test use;
Here, the query box of the last page is directly changed;
Mybatis framework learning notes (5) - [multi table Association query connected to front page display]
OK, just modify it step by step from the EmployeeService under the service package;
public class EmployeeService {
//Dynamically query employees according to name / gender;
public List<Employee> getEmpByNameOrAge(String name,String sex){
//Call tool class;
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//Get the proxy object;
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
//Call method;
List<Employee> list = mapper.getEmpByNameOrAge(name, sex);
//Close sqlSession;
sqlSession.close();
return list;
}
}
EmployeeServlet under servlet package
public class EmployeeServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
PrintWriter out=null;
//Get tag parameters;
String mark = req.getParameter("mark");
if(mark.equals("empList")){
try{
//Request code; Response decoding;
resp.setContentType("text/html;charset=utf-8");
//It will return in the form of stream;
out = resp.getWriter();
//Obtain parameters;
String name = req.getParameter("name");
String sex = req.getParameter("sex");
System.out.println("Querying for name--:"+name+"Gender==>"+sex);
//Call service layer processing;
EmployeeService employeeService = new EmployeeService();
List<Employee> empByNameOrAge = employeeService.getEmpByNameOrAge(name, sex);
//Console output test;
//empByNameOrAge.forEach(System.out::println);
//Send if it is not empty;
if(!empByNameOrAge.isEmpty()){
out.print(new Gson().toJson(empByNameOrAge));
}else {
out.print(0);//-->Does not exist, prompt message
}
}catch (Exception e){
e.printStackTrace();
out.print(500);//-->Server error;
}
}
}
}
Of course, if you don't use annotations, you have to configure them manually here; web.xml
<!--Configure employees servlet-->
<servlet>
<servlet-name>empServlet</servlet-name>
<servlet-class>com.xiaozhi.servlet.EmployeeServlet</servlet-class>
</servlet>
<!--servlet mapping-->
<servlet-mapping>
<servlet-name>empServlet</servlet-name>
<url-pattern>/do/emp</url-pattern>
</servlet-mapping>
employee.html list access page of employees;
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Employee list</title>
<!--deploy jquery-->
<script src="js/jquery.1.8.3.min.js" type="text/javascript"></script>
<script type="text/javascript">
//Send a request when the page is opened; Name and gender query criteria are not passed here;
$(function () {
$.get("do/emp", {mark:"empList"}, function (res) {
//Test data obtained;
//console.log(res)
if(res==500){
alert("i 'm sorry,There is something wrong with the server");
}else if(res==0){
//Pop up prompt;
alert("i 'm sorry,There are no employees at the moment!")
}else {
//Splicing and storage form;
var str2="";
for (var i = 0; i < res.length; i++) {
str2+="<tr align='center'>";
str2+="<td>"+(i+1)+"</td>";
str2+="<td>"+res[i].name+ "</td>";
str2+="<td>"+res[i].sex+ "</td>";
str2+="<td>"+res[i].dept.name+ "</td>";
str2+="<td>"+res[i].user.account+ "</td>";
str2+="</tr>";
}
$("#table").append(str2);
}
}, "json");
});
//Click search to query employees;
function getEmp(){
//Get the value of the input box first
var name = $("input[name='name']").val();
var sex = $("input[name='sex']").val();
//Send request; Query; Pay attention to your name or gender
$.get("do/emp",{mark:"empList",name:name,sex:sex},function (res){
//Test response data;
console.log(res)
if(res==500){
alert("i 'm sorry,There is something wrong with the server");
}else if(res==0){
//Clear the information of the form;
$("tr:gt(0)").remove();
//Pop up prompt;
alert("The employee does not exist!!!");
}else {
//Similarly, clear all the information first;
$("tr:gt(0)").remove();
//Splicing display data;
var str4="";
for (var i = 0; i < res.length; i++) {
str4 += "<tr align='center'>";
str4 += "<td>" + (i+1) + "</td>";
str4 += "<td>" + res[i].name + "</td>";
str4 += "<td>" + res[i].sex + "</td>";
str4 += "<td>" + res[i].dept.name + "</td>";
str4 += "<td>" + res[i].user.account + "</td>";
str4 += "</tr>";
}
$("#table").append(str4);
}
},"json");
}
//Click all employees to refresh the page;
function getAll(){
location.reload();
}
</script>
</head>
<body>
<form style="width: 1080px" id="form">
<label>
full name:<input type="text" name="name" placeholder="Please enter the name of the employee to query:"/>
Gender:<input type="text" name="sex" placeholder="Please enter the employee gender to query:"/>
</label>
<input type="button" value="search" onclick="getEmp()"/>
<!--Of course, in order to be able to transfer back all the former employees,Load the page again-->
<input type="button" value="Show all employees" onclick="getAll()">
</form>
<br/>
<!--Table displayed-->
<table width="100%" border="1" cellspacing="0" id="table">
<th>number</th>
<th>Employee name</th>
<th>Employee gender</th>
<th>Department</th>
<th>Operator</th>
</table>
</body>
</html>
After configuring the tomcat server, Ok starts to see the effect
(1) When accessing for the first time, the query is unconditional;
Therefore, the null value received here is also null, so the where clause will not be spliced
Trial name query;
(1) The name and empty string gender are received here; Inquired by name; However, gender is not spliced during query;
At this time, conditional judgment was added to the < if > tag when writing SQL statements
(3) Try entering only gender for query
The received name is an empty string; When querying, query only according to gender conditions, and the and keyword is removed from the < where > tag
(4) Query by name and gender
Of course, both name and gender are queried as conditions
(5) After opening the page, you can query directly without entering query criteria. At this time, you will directly query all employees, because the input boxes of name and gender received are empty strings
Since an empty string is received, the following where query condition is automatically removed
In fact, dynamic SQL query can be achieved without using the < where > tag; It is also a kind of strange skill;
Directly splice a valid condition in the query condition, for example, splice 1 = 1 after the where query condition; Prevent querying and outputting all data if the following conditions are not met;
Then try it;
Modify the SQL statement in EmployeeMapper.xml; Comment out the previous temporarily;
<!--By name/Gender dynamic query employee skills-->
<select id="getEmpByNameOrAge" resultMap="empmap">
SELECT
e.`id`,
e.`name`,
e.`sex`,
e.`age`,
d.`name` dname ,
u.`account`
FROM t_employee e
LEFT JOIN t_dept d ON e.`deptID`=d.`id`
LEFT JOIN t_user u ON u.`id`=e.`optionId`
where 1=1
<if test="name != null & name !='' ">
and e.name = #{name}
</if>
<if test="sex != null & sex !='' ">
and e.sex= #{sex}
</if>
</select>
Query and try;
For example, query only by gender
The query effect is still good
2.trim label
Customize the trim element to customize the where element
Using trim tags can also complete the splicing of where clauses and the removal of and/or keywords
In this case, prefix="where" indicates a spliceable prefix; prefixOverrides= "and|or" indicates that if these keywords appear, they will be removed automatically according to the situation
OK, give it a try and comment out the previous query SQL;
<!--use trim label -->
<!--By name/Gender dynamic query employee-->
<select id="getEmpByNameOrAge" resultMap="empmap">
SELECT
e.`id`,
e.`name`,
e.`age`,
e.`sex`,
d.`name` dname ,
u.`account`
FROM t_employee e
LEFT JOIN t_dept d ON e.`deptID`=d.`id`
LEFT JOIN t_user u ON u.`id`=e.`optionId`
<trim prefix="where" prefixOverrides="and|or">
<if test="name != null & name !='' ">
and e.name = #{name}
</if>
<if test="sex != null & sex !='' ">
and e.sex= #{sex}
</if>
</trim>
</select>
You can try the effect of query;
For example, I want to query by name
Well, the and before the name condition is automatically removed and the prefix where is added
3.choose label
The advantage of using the choose tag is that it can add default conditions;
Use the when and otherwise tags in the choose tag,
Condition judgment will be carried out in the when tag. If it meets the requirements, the conditions under the when tag will be executed; If the conditions are not met, execute the conditions provided in the otherwise tag;
Or use the case of query based on name and gender just now; Note out the previous;
In the EmployeeMapper.xml file;
I let it default to query employees whose name is No. 4 and gender is male
<!--use choose Implement default query-->
<select id="getEmpByNameOrAge" resultMap="empmap">
SELECT
e.`id`,
e.`name`,
e.`age`,
e.`sex`,
d.`name` dname ,
u.`account`
FROM t_employee e
LEFT JOIN t_dept d ON e.`deptID`=d.`id`
LEFT JOIN t_user u ON u.`id`=e.`optionId`
<trim prefix="where" prefixOverrides="and|or">
<choose>
<when test="name != null & name !='' ">
and e.name = #{name}
</when>
<otherwise>
and e.name ='4 number'
</otherwise>
</choose>
<choose>
<when test="sex != null & sex !=''">
and e.sex = #{sex}
</when>
<otherwise>
and e.sex = 'male'
</otherwise>
</choose>
</trim>
</select>
When I first visited the page, he checked according to the default query criteria
The null value received here is null, which is queried according to the default conditions
4.set label
Use the < set > tag to automatically add the set keyword according to the situation; And automatically delete the redundant in the set statement,
This benefit may not be realized in a single modification scenario;
Take a case; Several people need to modify a table at the same time, but due to different permissions, the fields that each person is responsible for modifying are not the same;
For example, I have a user's data table. A is responsible for modifying the name and gender in the user table; B is responsible for modifying the address and telephone number in the user table; C is responsible for modifying the remarks in the user table;
At this time, let's ask ABC three people to write three modification statements respectively; But I just want them to take A path; We should not influence each other's modification; We all know that when modifying, set condition = XXX, condition 2 =XXX, and the condition will be followed by A comma. When ABC operates on one SQL, A only needs to modify the name and gender, and he does not need to modify other information; So how to remove the redundant comma? At this time, you have to find the < set > tag element
Without much to say, the case begins
Define a modification method in the persistence layer interface of the EmployeeMapper Employee class
//Update employee information according to Id; void toUpdateEmp(Employee employee);
Write the corresponding SQL in the corresponding EmployeeMapper.xmlSQL mapping file;
My modification here mainly modifies the employee's name, gender and age according to Id; Department number, operator Id
<!--according to Id Modify employee information directly set label-->
<update id="toUpdateEmp" parameterType="employee">
update t_employee
<set>
<if test="name !=null & name !=''">
name =#{name},
</if>
<if test="sex !=null & sex!=''">
sex =#{sex},
</if>
<if test="age !=null & age!=''">
age =#{age},
</if>
<if test="dept.id !=null">
deptID=#{dept.id},
</if>
<if test="user.id !=null">
optionId=#{user.id}
</if>
</set>
where id=#{id}
</update>
First go to the database to see the original data;
Testing;
Test in the TestEmployee class under the test package
//Test and modify employee implementation; The set tag is used directly
@Test
public void updateEmp() {
//Call tool class;
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//Get the proxy object;
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee();
//Modify the information of employee No. 2;
employee.setId(2);
employee.setName("Revised employee No. 2 name");
//Gender is not modified here; Null values are also passed
//Modify the age of employee No. 2;
employee.setAge(100);
//Modify Department Id
Dept dept = new Dept();
dept.setId(2);
employee.setDept(dept);
//The ID of the operator is not modified here, so the ID value is not set;
User user = new User();
employee.setUser(user);
//Implement the modification method;
mapper.toUpdateEmp(employee);
//Note that transactions need to be committed;
sqlSession.commit();
//Close sqlSession;
sqlSession.close();
}
Look at the log information;
The modification of gender and the modification of operator Id are not spliced here;
In fact, you can also add a set with a custom trim tag and delete redundant commas
Add the set keyword with prefix; suffixOverrides tail overrides to remove redundant commas,
Ok, comment out the previous ones;
<!--according to Id User defined for modifying employee information trim label-->
<update id="toUpdateEmp" parameterType="employee">
update t_employee
<trim prefix="set" suffixOverrides=",">
<if test="name !=null & name !=''">
name =#{name},
</if>
<if test="sex !=null & sex!=''">
sex =#{sex},
</if>
<if test="age !=null & age!=''">
age =#{age},
</if>
<if test="dept.id !=null">
deptID=#{dept.id},
</if>
<if test="user.id !=null">
optionId=#{user.id}
</if>
</trim>
where id=#{id}
</update>
Test use
//Test and modify employee implementation; Use custom trim tags;
@Test
public void updateEmp1() {
//Call tool class;
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//Get the proxy object;
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee();
//Modify the information of employee No. 3;
employee.setId(3);
employee.setName("Revised employee No. 3 name");
//Gender is not modified here; Null values are also passed
//Modify or not modify the age, and pass a null value;
//Modify Department Id
Dept dept = new Dept();
dept.setId(1);
employee.setDept(dept);
//The Id of the operator is modified here,;
User user = new User();
user.setId(35);
employee.setUser(user);
//Implement the modification method;
mapper.toUpdateEmp(employee);
//Note that transactions need to be committed;
sqlSession.commit();
//Close sqlSession;
sqlSession.close();
}
View the log information during modification; There was no splicing of gender and age
5.Foreach label
The keyword foreach is common in many grammars and represents loop traversal;
The main attributes are item, index, collection, open, separator and close.
- item represents the alias of each element in the collection during iteration,
- index specifies a name to indicate the position of each iteration in the iteration process,
- open indicates what the statement starts with,
- Separator indicates what symbol is used as the separator between each iteration,
- close means what to end with,
- When using foreach, the most critical and error prone is collection
Property, which must be specified- When a single parameter is passed in and the parameter type is a List, collection belongs to
The property value is list - When a single parameter is passed in and the parameter type is an array,
The attribute value of collection is array
- When a single parameter is passed in and the parameter type is a List, collection belongs to
- When using a Map object (or a collection of Map.Entry objects), index is the key and item is the value.
OK, here is a case for operation;
For example, I have a need to query employees according to several age ranges; But these ages are dynamic;
(1) Define methods in the persistence layer interface of the EmployeeMapper Employee class;
First, use the set as the container;
//Query employees of multiple age ranges; Set container for parameters; List<Employee> getEmpByAgeList(List<Integer> ageList);
Write the corresponding SQL in EmployeeMapper.xml;
<!--Query the employee list collection according to the age list as a container-->
<select id="getEmpByAgeList" resultType="employee">
select * from t_employee where age in
<foreach collection="list" item="age" open="(" separator="," close=")">
#{age}
</foreach>
</select>
test
//Test batch age query; Collection as container
@Test
public void getEmpByAgeList(){
//Call tool class;
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//Get the proxy object;
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
//Call method;
List<Integer> ageList = new ArrayList<>();
ageList.add(20);
ageList.add(21);
ageList.add(22);
List<Employee> empByAgeList = mapper.getEmpByAgeList(ageList);
empByAgeList.forEach(System.out::println);
//Close sqlSession;
sqlSession.close();
}
Test results;
(2) It is also feasible to use arrays as parameter containers;
Define methods in the persistence layer interface of the EmployeeMapper Employee class
//Query employees of multiple age ranges; Array container for parameters; List<Employee> getEmpByAgeArray(Integer[] ageArray);
Configure the file in EmployeeMapper.xml
<!--Query the employee list array according to the age list as a container-->
<select id="getEmpByAgeArray" resultType="employee">
select * from t_employee where age in
<foreach collection="array" item="age" open="(" separator="," close=")">
#{age}
</foreach>
</select>
test
//Test batch age query; Collection as container
@Test
public void getEmpByAgeArray(){
//Call tool class;
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//Get the proxy object;
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
//Call method;
Integer[] ageArray = {20,22,24};
List<Employee> empByAgeList = mapper.getEmpByAgeArray(ageArray);
empByAgeList.forEach(System.out::println);
//Close sqlSession;
sqlSession.close();
}
test result
6. Several escape symbols to remember; Use in xml files
When writing mybatis normally, errors will be reported, and these symbols need to be escaped.
- < after Escape & lt;
- >After Escape & gt;
- "After Escape & quot;
- ’After Escape & apos;
- && amp; after escape;
If you don't want to use such escape symbols; use <! [CDATA []] > to include these symbols
<! [CDATA []] > is an XML syntax. Everything inside CDATA is ignored by the parser.For example, to query employees older than a certain age group;
Define methods in EmployeeMapper
//Query by age greater than a certain range; List<Employee> getEmpByExceedAge(Integer age);
Write the corresponding SQL in EmployeeMapper.xml
<!--Query by age greater than a certain range;-->
<select id="getEmpByExceedAge" resultType="employee">
select * from t_employee where age<![CDATA[ > ]]> #{age};
</select>
//Test employees older than a certain age;
@Test
public void getEmpByExceedAge(){
//Call tool class;
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//Get the proxy object;
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
//Call the method to query employees older than 20;
List<Employee> empByExceedAge = mapper.getEmpByExceedAge(20);
//Console output;
empByExceedAge.forEach(System.out::println);
//Close sqlSession;
sqlSession.close();
}
