Dynamic SQL Learning

• <select id= "Finduser"  parametertype= "Map"  resulttype= "User" >         Select * from User where 1=1          <if test= "Id>0" >        and  id = #{id}        </if>         <if test= "Name!=null" >          and name like #{name}        </if>         <if test= "Pwd!=null" >         and pwd = #{pwd}        </if>         <if test= "Address_id>0" >         and address_id = #{address_id}        </if>     </select>     
• private sqlsession sqlsession ;        private  persondao persondao;        private logger log =  logger.getlogger (junittest.class);         @Before      public void setup ()  throws Exception {         sqlsession = sqlsessionfactoryutil.opensession ();         persondao = sqlsession.getmapper (Persondao.class);    }       @After     public void teardown ()  throws  Exception {        sqlsession.close ();     }       @Test     public void finduser ()  {          Map<string, object> map = new hashmap<string, object> ();//         map.put ("id",  1);         map.put ("name",  "Zhang%");//        map.put ("pwd",  "111111 ");//        map.put (" address_id ",  1);          list<user> list =    persondao.finduser (map) ;         for  (user user : list)  {              log.info (user);//              system.out.println (user);         }     }   
• additions and deletions are returned by the affected line number
• /**     * by  usage      */    @ Test    public void finduser2 ()  {         Map<String, Object> map = new HashMap<String, Object> ();         map.put ("by",  "name");         map.put ("id",  1);         map.put ("name",  "Zhang%");         map.put ("pwd",  "111111");         map.put ("address_id",  1);         list<user > list = persondao.finduser2 (map);        for  ( User user : list)  {             // log.info (user);             system.out.println (user);          }    }  
• <select id= "FindUser2"  parametertype= "Map"  resulttype= "User" >         select * from User where 1=1         <choose>            < when test= "by== ' id '" >                 and id = #{id}             </when>            <when  test= "by== ' name '" >                 and name like #{name}             </when>            < when test= "by== ' pwd '" >                and pwd =  #{pwd}            </when>             <otherwise>                 and address_id = #{address_id }            </otherwise>         </choose>    </select> 
• Where Usage
• Select id= "FindUser3"  parametertype= "Map"  resulttype= "User" >         select * from user        <where >            <if test= "Id>0" >                 and id  = #{id}            </if>              <if test= "Name!=null" >                 and name  like #{name}            </if>              <if test= "Pwd!=null" >                 and pwd = #{pwd}             </if>         </where>    </select> 
• Trim Usage
• <select id= "FindUser4"  parametertype= "Map"  resulttype= "User" >         select * from User        < Trim prefix= "where"  prefixoverrides= "And|or" >             <if test= "Id>0" >                 and id = #{id}             </if>             <if test= "Name!=null" >                 and name like #{name}             </if>             <if test= "Pwd!=null ">                and  pwd = #{pwd}            </if >        </trim> 
•    foreach Usage
• <select id= "FindUser5"  parametertype= "Map"  resulttype= "User" >         select * from user        < Where>            <if test= "Ids!=null" >                and  id in                < Collection name for!--  Loop traversal  ids -->                 <foreach collection= "IDs"  item= "id"  open= "("  close= ")"                      separator= "," >                     #{id}                 </foreach>            </if>         </where>     </select>  
• @Test public void FindUser5 () {map< string,object> Map = new hashmap<string, object> ();        list<integer> list = Arrays.aslist (1,2,3,4,5);        Map.put ("IDs", list);        List<user> List1 = persondao.finduser5 (map);            for (user user:list1) {//Log.info (user);        SYSTEM.OUT.PRINTLN (user); }    }
• Limit usage
• SELECT * from ' user ' LIMIT start,size
• /**     *  Logical Paging processing      */    @ Test    public void test ()  {         //pass-through paging parameters,offset :  start  limite:  page size          Rowbounds bounds = new rowbounds (0,10);         List<user> list = pagedao.finduserbypage (bounds);         for  (user user : list)  {             system.out.println (user);        }     } 
• /**     *  Physical Paging processing      */    @ Test    public void test2 ()  {         //pass-through paging parameters,offset :  start  limite:  page size          Map<string, object> map = new hashmap<string, object> ();         map.put ("offset",  1);         Map.put ("Limit",  10);         list<user> list =  pagedao.finduserbypage2 (map);        for  (User user  : list)  {             SYSTEM.OUT.PRINTLN (user);         }    } 
• <!--all data in a physical paging query table--<select id= "FindUserByPage2" resulttype= "User" parametertype= "Map" > select *    From user <if test= "offset!=null and Limit!=null" > Limit #{offset}, #{limit} </if> </select>

Dynamic SQL Learning