Java的MyBatis框架中对数据库进行动态SQL查询的教程

  select * from (
   
  ) t 
  
    limit #{pageStart}, #{pageSize}
  


  select * from Visitor where
  status>0


  order by ${orderFieldStr} ${orderDirectionStr}

package david.mybatis.demo;

import java.util.List;

import david.mybatis.model.BasicQueryArgs;
import david.mybatis.model.PagenateArgs;
import david.mybatis.model.Visitor;
import david.mybatis.model.VisitorWithRn;

public interface IVisitorOperation {
  /*
   * 添加访问者
   */
  public int add(Visitor visitor);
  
  /*
   * 删除访问者
   */
  public int delete(int id);
  
  /*
   * 更新访问者
   */
  public int update(Visitor visitor);
  
  /*
   * 查询访问者
   */
  public Visitor query(int id);
  
  /*
   * 查询List
   */
  public List getList();
  
  /*
   * 分页查询List
   */
  public List getListByPagenate(PagenateArgs args);
  
  /*
   * 分页查询List（包含Rownum）
   */
  public List getListByPagenateWithRn(PagenateArgs args);
  
  /*
   * 基础查询
   */
  public Visitor basicQuery(int id);
  
  /*
   * 动态条件查询(choose,when)实例
   */
  public List getListChooseWhenDemo(BasicQueryArgs args);
  
  /*
   * 动态条件查询(where,if)实例
   */
  public List getListWhereDemo(BasicQueryArgs args);
  
  /*
   * 动态查询(foreach)实例
   */
  public List getListForeachDemo(List ids);
  
}

<&＃63;xml version="1.0" encoding="UTF-8"&＃63;>

  
  
    
    
    
    
    
  
  
    select * from Visitor
  
  
  
    
    
      
        status=#{queryStatus}
      
      
        
          and id=#{queryId}
        
        
          and name like #{queryName}
        
        
          and createTime>= #{queryTime}
        
      
    
  

 SELECT * FROM BLOG
 WHERE
 
  state = #{state}
 
 
  AND title like #{title}
 
 
  AND author_name like #{author.name}
 

 SELECT * FROM BLOG
 WHERE

  SELECT * FROM BLOG
  WHERE
  AND title like ‘someTitle'

<&＃63;xml version="1.0" encoding="UTF-8"&＃63;>

  
  
    select * from Visitor
  
  
  
    
    
     
       
        status>0 
       
       
        and id=#{queryId} 
       
       
        and name like=#{queryName} 
       
       
        and createTime>=#{queryTime} 
      
    
    
  

<&＃63;xml version="1.0" encoding="UTF-8"&＃63;>

  
  
    select * from Visitor
  
  
  
    
    where status>0 and id in 
    
      ${item}
    
  

/*
   * 动态查询foreach实例
   */
  public static void getListForeachDemo(List ids) {
    SqlSession session = MybatisUtils.getSqlSession();
    IVisitorOperation vOperation = session.getMapper(IVisitorOperation.class);
    List ls = vOperation.getListForeachDemo(ids);
    for (Visitor visitor : ls) {
      System.out.println(visitor);
    }
  }
  
  /*
   * 动态查询where if实例
   */
  public static void getListWhereCondition(int id, String name, Date createTime) {
    name = name == "" &＃63; null : name;
    SqlSession session = MybatisUtils.getSqlSession();
    BasicQueryArgs args = new BasicQueryArgs(id, name, createTime);
    IVisitorOperation vOperation = session.getMapper(IVisitorOperation.class);
    List ls = vOperation.getListWhereDemo(args);
    if (ls.size() == 0)
      System.out.println("查无匹配！");
    else {
      for (Visitor visitor : ls) {
        System.out.println(visitor);
      }
    }
  }

  /*
   * 动态查询choose when实例
   */
  public static void getListChooseWhenDemo(int id, String name, Date createTime) {
    name = name == "" &＃63; null : name;
    SqlSession session = MybatisUtils.getSqlSession();
    BasicQueryArgs args = new BasicQueryArgs(id, name, createTime);
    IVisitorOperation vOperation = session.getMapper(IVisitorOperation.class);
    List ls = vOperation.getListChooseWhenDemo(args);
    if (ls.size() == 0)
      System.out.println("查无匹配！");
    else {
      for (Visitor visitor : ls) {
        System.out.println(visitor);
      }
    }
  }