MyBatis using dynamic SQL on annotations (@select using IF)

1. script Surround with tags and write like XML syntax

@Select ({"<script>",    "select * from Tbl_order",        "WHERE 1=1", "< When test= ' title!=null ' > ',    ' andmydate = #{mydate}    ', ' </when> ',    "</script>"})

2, use Provider to achieve SQL splicing, for example:

 Public classOrderprovider {Private FinalString Tbl_order = "Tbl_order";  PublicString queryorderbyparam (orderpara param) {SQL SQL=NewSQL (). Select ("*").        From (Tbl_order); String=Param.getroom (); if(Stringutils.hastext ()) {SQL. WHERE ("#{room}"); } Date mydate=param.getmydate (); if(MyDate! =NULL) {sql. WHERE ("MyDate like #{mydate}"); }        returnsql.tostring (); }} Public InterfaceOrderdao {@SelectProvider (type= Orderprovider.class, method = "Queryorderbyparam") List<Order>queryorderbyparam (Orderparam param);}

Note: One of the pitfalls of mode 1 is that when an incoming parameter is empty, it may cause a full table query.

Complex SQL 2 is more flexible (of course, it is not recommended to write complex SQL), and can be abstracted into a common base class, so that each DAO can implement basic generic queries through this base class, similar in principle Spring JDBC Template .

Reference:

https://segmentfault.com/q/1010000006875476 (the above content is transferred from this answer)

https://stackoverflow.com/questions/6685655/ How-to-use-dynamic-sql-query-in-mybatis-with-annotationhow-to-use-selectprovide

MyBatis using dynamic SQL on annotations (@select using IF)