title: javaEE(SSM) learning notes 4: dynamic SQL
Dynamic SQL import
[main function]: splice SQL clauses according to different conditions or different data to achieve similar programming effect in XML files.
[main element (mark)]:
Query criteria: single branch if; Multi branch choose... when... other
Special treatment: where /trim
Update data: set
Complex query and update: foreach
1. Condition query
1.1 < if > elements
Splice SQL statements according to conditions. If conditions are met, SQL clauses are included.
(1) Grammar
<if test="Conditional expression">
SQL clause
</if>
1. Conditional expression: it is basically a comparison operation or logical operation, and the result is true or false
2. Operator: greater than; Greater than or equal to; be equal to; Not equal to; Less than; Less than or equal to. Since < and > are special characters of XML and cannot be used directly, mybatis uses the following corresponding operators:
Comparison operators: gt, gte, = =,! = lt , lte ；
Logical operators: use and and or instead of & & and||
(2) Example 1: fuzzy matching
[demand] fuzzy query the customer information in the customer table according to the entered name or occupation:
1. If the name is not empty, query by name;
2. If occupation is not blank, query by occupation;
3. If both are not empty, the conditions for specifying name and occupation must be met;
4. If both are empty, all data will be displayed;
[analysis]: this requirement is applicable to the following situations
Condition 1: select * from customer
Condition 2: select * from customer where username like concat('%',#{username},'%')
Condition 3: select * from customer where jobs like concat('%',#{jobs},'%')
Condition 4: select * from customer where username like concat('%',#{username},'%')
AND jobs like concat('%',#{jobs},'%')
Case 1: unconditional query, no user name and occupation entered;
Case 2: the user name is entered but the occupation is not entered
Case 3: the occupation is entered but the user name is not entered
Case 4: both have input, and one of them can be found
[specific implementation]:
1. Create the database mybatis and add the data table customer
2. Create maven project and add two necessary dependencies: mybatis and connectorJ; To facilitate debugging, add the log4j dependency, and add log4j in the src/main/java root directory of the project Properties file. In the future, this is the standard configuration dependency of mybatis framework project See javaEE(SSM) learning note 3: mybatis core objects and configuration documents for specific operations.
3. Add mybatis XML configuration file and dependent properties file dB properties (src/main/resource)
4. Add POJO class: customer Java (Location: src/main/java/pojo package)
5. Add customermapper XML Mapping file (Location: src/main/java/mapper package)
6. Test run
7. Extension: execute mapping statements using interface mode
CustomerMapper.xml content
<select id="getCustomer" parameterType="customer" resultType="customer">
select * from customer where 1=1
<if test="username!=null and username!=''">
and username like concat('%',#{username},'%')
</if>
<if test="jobs!=null and jobs!=''">
and jobs like concat('%',#{jobs},'%')
</if>
</select>
[note] the parameter type is pojo class, and its attributes are directly used in test
[test core code]:
//1 parameter is null: display all data
//sqlSession.selectList("getCustomer");
//2. Name cannot be blank
cust.setUsername("Zhang San");
sqlSession.selectList("getCustomer",cust);
//3 property is not empty
cust.setUsername("");
cust.setJobs("Property");
sqlSession.selectList("getCustomer",cust);
//4. Both cannot be empty
cust.setUsername("Zhang San");
cust.setJobs("boss");
sqlSession.selectList("getCustomer",cust);
[operation results, similar to the following]:
DEBUG [main] - ==> Preparing: select * from customer where 1=1 and username like concat('%',?,'%')
DEBUG [main] - ==> Parameters: Zhang San(String)
TRACE [main] - <== Columns: id, username, jobs, phone, createtime
TRACE [main] - <== Row: 58, Test Zhang San, Property, 136, 2022-01-09 09:42:57
TRACE [main] - <== Row: 59, Test Zhang San, worker, 136, 2022-01-16 19:57:16
DEBUG [main] - <== Total: 2
DEBUG [main] - ==> Preparing: select * from customer where 1=1 and jobs like concat('%',?,'%')
DEBUG [main] - ==> Parameters: Property(String)
TRACE [main] - <== Columns: id, username, jobs, phone, createtime
TRACE [main] - <== Row: 58, Test Zhang San, Property, 136, 2022-01-09 09:42:57
DEBUG [main] - <== Total: 1
DEBUG [main] - ==> Preparing: select * from customer where 1=1 and username like concat('%',?,'%') and jobs like concat('%',?,'%')
DEBUG [main] - ==> Parameters: Zhang San(String), boss(String)
DEBUG [main] - <== Total: 0(No data)
Extension: use interface mode.
(3) Example 2: test operator and sql operator
[demand]: enter id. if id < 20, select data output with id < 5
[note]: how to use operators in test and xml
CustomerMapper.xml content
<!-- If id Parameter is not empty(null) ，And less than or equal to 20, then select 5-Data for this value -->
<select id="getCustomerById" parameterType="int" resultType="customer">
select * from customer where 1=1
<if test="_parameter !=null and _parameter lte 20">
and id >= 5 and id <= #{id}
</if>
</select>
Use a common data type in test_ Parameter to get the parameter; If you use multiple parameters and POJO type parameters, you can directly use the attribute name in test (in this case, if it is a range value, you can use the array foreach complex query later, or you can use the interface mode and specify the parameters with the annotation @ param);
2. Use operators in test attribute: GT (>), GTE (> =), = =,! = lt(<) , lte (<= )
Logical operation: use and and or instead of & & and||
3. When used in sql (in XML document), the escape character of HTML should be used;
> Indicates greater than, then greater than or equal to: & gt; =& lt; Indicates less than, then less than or equal to is & lt= Pay attention to the semicolon
=The equal sign is not a special character.
[test results, similar to the following]:
DEBUG [main]==> Preparing: select * from customer where 1=1 and id >= 5 and id <= ? DEBUG [main] - ==> Parameters: 20(Integer) TRACE [main] - <== Columns: id, username, jobs, phone TRACE [main] - <== Row: 8, Sun Liuliu, administrators, 133333334444 TRACE [main] - <== Row: 9, Sun Liu, worker, 44444444444444444444 TRACE [main] - <== Row: 10, Sun Liu, worker, 44444444444444444444 DEBUG [main] - <== Total: 3
1.2 < choose > elements
Function: in the given conditions, only the first clause that meets the conditions is spliced.
(1) Grammar
<choose>
<when test="Conditional expression 1"> SQL Clause 1</when>
<when test="Conditional expression 2"> SQL Clause 2</when>
...
<otherwise>
SQL Clause 3
</otherwise>
</choose>
Judge in order. As long as one of the conditions is met, no further judgment will be made; When all conditions are not met, the clauses in otherwise are executed.
(2) Example: query customer information
[demand]:
1. If the customer name is given, fuzzy query will be carried out according to the customer name;
2. If the customer's occupation is given, fuzzy query is carried out according to the customer's occupation;
3. If neither is given, query all customer information whose contact number is not empty.
CustomerMapper.xml content
<select id="findCustomer" parameterType="customer" resultType="customer">
select * from customer
where 1=1
<choose>
<when test="username!=null and username!=''">
and username like concat('%',#{username},'%')
</when>
<when test="jobs!=null and jobs!=''">
and jobs like concat('%',#{jobs},'%')
</when>
<otherwise>
and phone is not null
</otherwise>
</choose>
</select>
Different from if, choose is to stop judging when the first condition is met; And if will continue to judge the next if
otherwise, it can also be and ID / & gt; 0
1.3 where element
[function] splicing is performed according to whether there are conditional clauses. Whether to use the where keyword to splice query clauses is determined according to whether the tag contains SQL clauses. At the same time, it will remove redundant join operators, such as AND and OR. With this tag, the clause in the above example should not be written like this: where 1=1
Example: re implement the above example using the where element
<select id="findCustomer2" parameterType="customer"
resultType="customer">
select * from customer
<where>
<choose>
<when test="username!=null and username!=''">
and username like concat('%',#{username},'%')
</when>
<when test="jobs!=null and jobs!=''">
and jobs like concat('%',#{jobs},'%')
</when>
<otherwise>
and phone is not null
</otherwise>
</choose>
</where>
</select>
When the user name and occupation are not entered, the and in otherwise will be automatically removed to form a complete sql statement.
1.4 trim element
[function] it can automatically add prefixes and remove redundant and specified connecting characters (strings) in front of clauses; Or add a suffix to automatically remove the redundant and specified connecting characters or strings after the clause. The usage is very flexible.
It contains the following attributes:
1.prefix: prefix added to clauses, such as where. It is actually arbitrary. As long as you specify anything, it will be added to the front of the clause
2.prefixOverrides: remove the redundant characters in front of the clause, such as and, or, which will be deleted as long as you specify
3.suffix: the suffix added to the clause, such as the right parenthesis), will be added after the clause as long as you specify anything.
4.suffixOverrides: remove redundant characters after the clause, such as commas. In fact, whatever you want to delete will delete the content at the end of the clause
In fact, it is whether to add SQL connection keywords before and after the clause, and whether to delete redundant keywords before and after the clause, in order to automatically form a correct SQL statement.
Example 1: use trim instead of where element
If there are clauses, automatically add the specified where prefix, and then delete the redundant and specified connectors
[demand]: query customer information according to customer name or occupation
[idea]: add WHERE before the clause and remove the redundant OR in front of the clause
CustomerMapper.xml content
<!-- use trim To replace where -->
<select id="getCustomer_trim" parameterType="customer"
resultType="customer">
select * from customer
<trim prefix="WHERE" prefixOverrides="OR">
<if test="username!=null and username!=''">
OR username like concat('%',#{username},'%')
</if>
<if test="jobs!=null and jobs!=''">
OR jobs like concat('%',#{jobs},'%')
</if>
</trim>
</select>
[test]:
@Test
public void test3() {
sqlSession = ssf.openSession();
Customer c=new Customer();
c.setJobs("worker");
c.setUsername("Sun");
sqlSession.selectList("getCustomer_trim",c);
}
[similar results]:
DEBUG [main] - ==> Preparing: select * from customer WHERE username like concat('%',?,'%') or jobs like concat('%',?,'%')
DEBUG [main] - ==> Parameters: Sun(String), worker(String)
TRACE [main] - <== Columns: id, username, jobs, phone, createtime
TRACE [main] - <== Row: 8, Sun Liuliu, administrators, 133333334444, 2019-10-16 20:59:08
TRACE [main] - <== Row: 9, Sun Liu1, worker, 44444444444444444444, 2022-01-16 23:09:50
TRACE [main] - <== Row: 10, Sun Liu2, worker, (Null), 2022-01-16 23:10:57
TRACE [main] - <== Row: 59, Test Zhang San, worker, 136, 2022-01-16 19:57:16
DEBUG [main] - <== Total: 4
If the clause does not exist, the prefix and suffix will not be added, even if prefix and suffix are set.
Example 2: use trim instead of update clause set
If there are clauses, automatically add the specified set prefix, and then delete the redundant and specified connectors
Demand: update the customer information according to the id. if the customer name exists in the parameter, update the customer name; If there are occupations, update them at the same time. That is, only fields containing values are updated.
[note]: in this case, a value cannot be absent, which cannot form a correct SQL statement (the front end will judge).
<!-- use trim replace set -->
<update id="updateCustomer" parameterType="customer">
update customer
<trim prefix="set" suffixOverrides=",">
<if test="username!=null and username!=''">
username=#{username},
</if>
<if test="jobs!=null and jobs!=''">
jobs=#{jobs},
</if>
</trim>
where id=#{id}
</update>
[note]: the last comma needs to be removed: suffixOverrides = ","
The transfer parameter must have data, and the caller (service layer or application layer) must verify the correctness of the data before calling the mapping statement. You should not validate data in XML.
[test]
@Test
public void test4() {
sqlSession = ssf.openSession();
Customer c=new Customer();
c.setId(8);
c.setJobs("worker");
c.setUsername("Sun Ming");
sqlSession.update("updateCustomer",c);
sqlSession.commit();//Need to submit
}
[similar results]
DEBUG [main] - ==> Preparing: update customer set username=?, jobs=? where id=? DEBUG [main] - ==> Parameters: Sun Ming(String), worker(String), 8(Integer) DEBUG [main] - <== Updates: 1
1.5 complex query: foreach
List each element of the input parameter. Similar to the circular result of java, take out the data from the incoming Array array, list or Map in turn and splice them into SQL clauses.
[suggestion]: write a complete SQL in MySQL first, and then modify the corresponding dynamic SQL.
[application scenario]:
1. Query customer information from a set of specified subscripts, and use array or list as input parameters.
2. Input parameters contain multiple types at the same time, such as common type, POJO type, array or list type, that is, multi parameter query, using map. For example, use user name and occupation for fuzzy query, and use a group of subscripts to query customer information.
3. Insert a set of data.
(1) Syntax 1: use arrays and lists
<select id="" parameterType="arraylist|list" resultType="Return result type">
...
<foreach item="Cyclic variable" collection="array/list" index="Current cyclic subscript"
open="Prefix of the entire clause" close="Suffix of the entire clause" seperator="Separator" >
SQL Sub statement
</foreach>
</select>
[function]: cycle according to the number of array or list elements. Each cycle saves the elements in the item attribute variable, splices the marked contents into clauses according to the specified separator, and finally adds the specified prefix and suffix characters before and after the clauses.
1. For arrays and lists, the specified input parameter type parameterType can be arraylist or list. It is a built-in alias of mybatis and is not case sensitive
2. The item attribute value is similar to a circular variable. Note that the value is the variable name.
3.collection: parameter type. If the parameter is an array, the collection value uses array; If the parameter is list, the collection must use list
4.index the subscript of the current array or list, starting from 0; open is the prefix symbol, such as the left bracket, and close is the suffix symbol, such as the right bracket
5.separator: specify the symbol used to separate the clauses of each loop, such as comma
(2) Syntax 1 application example: find data corresponding to a group of IDS
[requirement]: query customer information from a set of specified IDS, using array and list respectively
CustomerMapper.xml content
<!-- Use array -->
<select id="byArrays" parameterType="arraylist" resultType="customer">
select * from customer where id in
<foreach item="id" collection="array" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
<!--Use list -->
<select id="byList" parameterType="list" resultType="customer">
select * from customer where id in
<foreach item="id" collection="list" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
[test]
@Test
public void test5() {
sqlSession = ssf.openSession();
int[] ids= {5,8,10};
sqlSession.selectList("byArrays",ids);
}
@Test
public void test6() {
sqlSession = ssf.openSession();
List<Integer> ids=new ArrayList<Integer>();
ids.add(5);
ids.add(8);
ids.add(10);
sqlSession.selectList("byList",ids);
}
[the results are similar as follows]:
DEBUG [main] - ==> Preparing: select * from customer where id in ( ? , ? , ? ) DEBUG [main] - ==> Parameters: 5(Integer), 8(Integer), 10(Integer) TRACE [main] - <== Columns: id, username, jobs, phone, createtime TRACE [main] - <== Row: 8, Sun Ming, worker, 133333334444, 2022-01-17 07:14:33 TRACE [main] - <== Row: 10, Sun Liu2, worker, (Null), 2022-01-16 23:10:57 DEBUG [main] - <== Total: 2 DEBUG [main] - ==> Preparing: select * from customer where id in ( ? , ? , ? ) DEBUG [main] - ==> Parameters: 5(Integer), 8(Integer), 10(Integer) TRACE [main] - <== Columns: id, username, jobs, phone, createtime TRACE [main] - <== Row: 8, Sun Ming, worker, 133333334444, 2022-01-17 07:14:33 TRACE [main] - <== Row: 10, Sun Liu2, worker, (Null), 2022-01-16 23:10:57 DEBUG [main] - <== Total: 2
(3) Syntax 2: use Map
When you need to query according to multiple different types of parameters, you can use map type parameters
[syntax]:
<select id="" parameterType="map" resultType="">
...
<foreach item="Cyclic variable" collection="key name" index="Current cyclic subscript"
open="prefix" close="suffix" seperator="Separator" >
Combined SQL sentence
</foreach>
</select>
map is a built-in alias of mybatis. It does not need a fully qualified name. The alias can be case sensitive.
Unlike syntax 1, collection = "collection of key keyword names",
(4) Syntax 2 application example: query based on a set of IDS and other fields
[demand]: query customer information according to a set of specified IDs and occupations
CustomerMapper.xml content
<select id="byMap" parameterType="map" resultType="customer">
select * from customer
where jobs like concat('%',#{jobs},'%')
and id in
<foreach item="id" collection="ids" open="(" close=")" separator="," >
#{id}
</foreach>
</select>
Collection = "ids" here, ids is the keyword of the Map collection element. index here is also a subscript, starting from 0
[test]
@Test
public void test7() {
sqlSession = ssf.openSession();
List<Integer> ids=new ArrayList<Integer>();
ids.add(5);
ids.add(8);
ids.add(10);
Map<String,Object> map=new HashMap<String,Object>();
map.put("jobs", "worker");//Normal string
map.put("ids", ids);//Set or array int[] ids= {5,8,10};
sqlSession.selectList("byMap",map);
}
[Extension]
Map can be a common type, which can be obtained by using the map keyword in the SQL main sentence; For example, jobs – > in the above example can pass multiple parameters, such as maximum value and minimum value, so as to replace the acyclic array.
map.put("min", 1);
map.put("max",100);
select * from customer id > #{min} and id < #{max}
It can also be an object pojo type, which is obtained by using "object name. Attribute name" in the main sentence of SQL, and the object name is the map keyword
map.put("cust", customer);
select * from customer where username like concat('%',#{cust.username},'%')
1.6 foreach extended application
[extended content] mainly to understand the usage of foreach.
(1) Use array to pass array range
[requirement]: pass the range id (two integer IDS, upper and lower limits), and query the customer information whose id is in the range
[analysis]: use the array int[] ={1,10} to encapsulate data. In foreach, splice SQL clauses according to different subscripts
<select id="byRange" parameterType="int[]" resultType="customer">
select * from customer
<where>
<foreach item="id" collection="array" index="index">
<if test="index==0">
and id >= #{id}
</if>
<if test="index==1">
and id <=#{id}
</if>
</foreach>
</where>
</select>
For ordinary type arrays, you can use arraylist and list, or directly use the type corresponding to the array;
In the foreach tag, you can still use other elements as needed; Note how operators are written in XML.
[test]
@Test
public void test8() {
sqlSession = ssf.openSession();
int[] ids= {1,10};
sqlSession.selectList("byRange",ids);
}
[similar results]
DEBUG [main] - ==> Preparing: select * from customer WHERE id >= ? and id <=? DEBUG [main] - ==> Parameters: 1(Integer), 10(Integer) TRACE [main] - <== Columns: id, username, jobs, phone, createtime TRACE [main] - <== Row: 2, Li Si, boss, 111111111111111, 2022-01-16 20:28:23 TRACE [main] - <== Row: 3, Wang Wu, manager, 111111111111111, 2022-01-16 20:28:33 TRACE [main] - <== Row: 8, Sun Ming, worker, 133333334444, 2022-01-17 07:14:33 TRACE [main] - <== Row: 9, Sun Liu1, worker, 44444444444444444444, 2022-01-16 23:09:50 TRACE [main] - <== Row: 10, Sun Liu2, worker, (Null), 2022-01-16 23:10:57 DEBUG [main] - <== Total: 5
(2) Insert a set of customer information at the same time
[requirement]: insert multiple records at the same time
[analysis]: multiple records can use List to encapsulate data. In mapping SQL, use foreach to obtain object by object, and then splice SQL clauses
<insert id="addCustomers" parameterType="list">
insert into customer(username,jobs,phone) values
<foreach item="c" collection="list" separator=",">
( #{c.username},#{c.jobs},#{c.phone} )
</foreach>
</insert>
We should first understand that the SQL writing method for inserting multiple data is:
insert into customer(username,jobs,phone) values(Record 1), (record 2)
The item attribute can be a normal type or an object
[test]
@Test
public void test9() {
sqlSession = ssf.openSession();
List<Customer> cs=new ArrayList<Customer>();
Customer c=new Customer();
c.setUsername("A1");
c.setJobs("Managing workers");
c.setPhone("1111");
cs.add(c);//Insert record 1
c=new Customer();
c.setUsername("A2");
c.setJobs("Administration");
c.setPhone("1111");
cs.add(c);//Insert record 2
sqlSession.insert("addCustomers",cs);
sqlSession.commit();//Don't forget
}
The results are similar
DEBUG [main] - ==> Preparing: insert into customer(username,jobs,phone) values ( ?,?,? ) , ( ?,?,? ) DEBUG [main] - ==> Parameters: A1(String), Managing workers(String), 1111(String), A2(String), Administration(String), 1111(String) DEBUG [main] - <== Updates: 2
[key points to remember]
1.parameterType:
1. Common type
Po2.jo type
3.List type: list and array
4.Map: map type
2. In foreach
collection: key name of array / list / map