How to realize dynamic SQL in Mybatis
In this issue, the editor will bring you about how to achieve dynamic SQL in Mybatis. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.
Second, dynamic SQL
1. IF tag
In Product.xml
Select * from product_ where name like concat ('%', # {name},'%')
In TestIF.java
System.out.println ("query all"); List ps = session.selectList ("listProduct"); for (Product p: ps) {System.out.println (p);} System.out.println ("Fuzzy query"); Map params = new HashMap (); params.put ("name", "a") List ps2 = session.selectList ("listProductByName", params); for (Product p: ps2) {System.out.println (p);}
2. WHERE SET TRIM tag
(1) WHERE tag
If you want to make multi-condition judgment, there may be a contradiction of select * from product_ and price > 10.
This problem can be solved by tagging.
Select * from product_ and name like concat ('%', # {name},'%') and price > # {price}
The label will be judged automatically.
If no condition is true, the where keyword will not appear in the sql statement
If any conditions are true, the extra and or or will be removed automatically.
(2) SET tag
Similar to where tags, multiple field-related problems are encountered in update statements. In this case, you can use the set tag
Name=# {name}, price=# {price}
(3) TRIM tag, which is used to customize the desired function
For example: replace the where tag
...
Replace the set tag
...
3. CHOOSE WHEN OTHERWISE tag
(1) there is no else tag in Mybatis, but you can use when otherwise tag to achieve this effect.
In Product.xml
SELECT * FROM product_ and name like concat ('%', # {name}) '%') and price > # {price} and id > 1
Its purpose is to make a conditional query if any condition is provided, otherwise the condition id > 1 will be used.
4. FOREACH tags are usually used in grammars like in.
For example, as shown in the figure, the data that id equals 1 ~ 3 ~ 5 is queried.
In Product.xml
SELECT * FROM product_ WHERE ID in # {item}
In TestForeach.java
Package com.how2java; import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.List; import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder; import com.how2java.pojo.Product; public class TestMybatis {public static void main (String [] args) throws IOException {String resource = "mybatis-config.xml" InputStream inputStream = Resources.getResourceAsStream (resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder (). Build (inputStream); SqlSession session = sqlSessionFactory.openSession (); List ids = new ArrayList (); ids.add (1); ids.add (3); ids.add (5); List ps = session.selectList ("listProduct", ids) For (Product p: ps) {System.out.println (p);} session.commit (); session.close ();}}
5. Bind tag
Bind tags are like doing string concatenation again to facilitate subsequent use.
For example, based on the fuzzy query, the fuzzy query is changed to the bind tag.
In Product.xml
Select * from product_ where name like # {likename}
In TestMybatis.java
Package com.how2java; import java.io.IOException;import java.io.InputStream;import java.util.HashMap;import java.util.List;import java.util.Map; import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder; import com.how2java.pojo.Product; public class TestMybatis {public static void main (String [] args) throws IOException {String resource = "mybatis-config.xml" InputStream inputStream = Resources.getResourceAsStream (resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder (). Build (inputStream); SqlSession session = sqlSessionFactory.openSession (); Map params = new HashMap (); params.put ("name", "product"); List ps = session.selectList ("listProduct", params); for (Product p: ps) {System.out.println (p) } session.commit (); session.close ();}} above is how to implement dynamic SQL in the Mybatis shared by the editor. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.
