1. What is mybatis dynamic sql
When we see the dynamic, we should think that this is a sql statement that can be changed.
MyBatis' dynamic SQL is based on OGNL expressions, which can help us easily implement certain logic in SQL statements.
2. Preparation before using mybatis dynamic sql
a. Database table
b. Create a class
3. Using mybatis dynamic sql, you must first know some attribute values
1. Insert
selectKey: SQL statement executed before or after sql statement
keyColumn: corresponding field name or alias
keyProperty: the attribute name or map key value corresponding to the entity class
order: execute before or after the execution statement
resultType: The type of return result
The first method: the easiest dynamic sql insertion
insert into course values(seq_course.nextval,#{courseCode},#{courseName})select max(id) currId from coursetest
Test results
The second way: If I don't want to insert so many fields and I don't want to insert the courseName field, then the first way is definitely not possible.
insert into course(id,courseCode,courseName)values(seq_course.nextval,#{courseCode},#{courseName})Tests and results
From this we can see that this method can be achieved when I don't want to insert the courseCode field.
Now the title is here again. What happens if we want to turn the last field into empty, or both fields into empty
The last field becomes empty
There seems to be a comma behind the first question mark on the console
Try both fields becoming empty
There is still a comma
The reason for the above situation is that when we don't want to insert the field, we did not remove the comma. We now introduce the third method.
The third way is to remove excess commas
This method needs to introduce trim
Trim also has several properties
prefix: add prefix
prefixOverrides: Override the prefix
suffix: add suffix
suffixOverrides: Override the suffix
id,courseCode,courseNameseq_course.nextval,#{courseCode},#{courseName}Look, the problem has been solved
When working on projects, we often insert data into the database. Sometimes we want to insert multiple pieces at the same time. So, how should we insert multiple pieces of data using mybatis?
Here, we need to solve several problems
1. How do we copy the table structure
create table course02 as select * from COURSE
Is this statement very familiar? Yes, the course02 table not only copies the table structure of the course table, but also copies the content of the course table in.
2. How do we copy only the course table structure instead of the course table content
create table course03 as select * from COURSE where 1!=1;
3. So when inserting, is it the same as this?
insert into course03 select * from course;
The answer is the same
4. How to insert multiple statements
insert into course03select '6', 'yc06', 'data structure 06' from dual unionselect '7', 'yc07', 'data structure 07' from dual unionselect '8', 'yc08', 'data structure 08' from dual unionselect '8', 'yc08', 'data structure 08' from dual
Now that the SQL statement is available, we can insert it in the configuration file
insert into courseselect #{courses.id},#{courses.courseCode},#{courses.courseName} from dual2. Modify
1. Use trim. As mentioned earlier, you can use trim to defix because of the attribute suffixOverrides
update course setcourseCode=#{courseCode},courseName=#{courseName} where id=#{id}2. Use set, which can replace set in the statement, and also remove the suffix.
update coursecourseCode=#{courseCode},courseName=#{courseName} where id=#{id}3. When modifying, we will also make selective changes. Sometimes we do not want to modify the value to make it consistent with the value without modification.
Choose is equivalent to multiple if
The first one is equivalent to if, and the second one is equivalent to else if
otherwise equals else
update coursecourseCode=#{courseCode},courseCode=courseCode,courseName=#{courseName},courseName=courseName, where id=#{id}The above is the entire content of Mybatis dynamic sql introduced to you. I hope it will be helpful to everyone!