Shulou(Shulou.com)06/02 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.