Mybatis注解-注解方式的动态SQL语句

• 基于手写SQL语句的注解CRUD
  
  把手写SQL语句的注解CRUD，修改为动态SQL语句方式。
• CategoryDynaSqlProvider
  
  新增CategoryDynaSqlProvider，提供CRUD对应的SQL语句。
  这里的SQL语句使用SQL类的方式构建，在 关于SQL类中有更加详细的讲解。
  

  package com.how2java;
   
  import org.apache.ibatis.jdbc.SQL;
   
  public class CategoryDynaSqlProvider {
      public String list() {
           return new SQL()
                   .SELECT("*")
                   .FROM("category_")
                   .toString();
           
      }
      public String get() {
          return new SQL()
                  .SELECT("*")
                  .FROM("category_")
                  .WHERE("id=#{id}")
                  .toString();
      }
       
      public String add(){
          return new SQL()
                  .INSERT_INTO("category_")
                  .VALUES("name", "#{name}")
                  .toString();
      }
      public String update(){
          return new SQL()
                  .UPDATE("category_")
                  .SET("name=#{name}")
                  .WHERE("id=#{id}")
                  .toString();
      }
      public String delete(){
          return new SQL()
                  .DELETE_FROM("category_")
                  .WHERE("id=#{id}")
                  .toString();
      }
       
  }

• 修改CategoryMapper
  
  把本来是手写SQL的CategoryMapper接口，修改为注解引用CategoryDynaSqlProvider类的方式
  比如增加，本来是手写SQL语句的
  

  @Insert(" insert into category_ ( name ) values (#{name}) ")  
  public int add(Category category);  

  
  修改为了注解@InsertProvider配合CategoryDynaSqlProvider的add方法
  

  @InsertProvider(type=CategoryDynaSqlProvider.class,method="add")  
  public int add(Category category);  

  
  

  package com.how2java.mapper;
    
  import java.util.List;
   
  import org.apache.ibatis.annotations.DeleteProvider;
  import org.apache.ibatis.annotations.InsertProvider;
  import org.apache.ibatis.annotations.SelectProvider;
  import org.apache.ibatis.annotations.UpdateProvider;
   
  import com.how2java.CategoryDynaSqlProvider;
  import com.how2java.pojo.Category;
    
  public interface CategoryMapper {
    
      @InsertProvider(type=CategoryDynaSqlProvider.class,method="add") 
      public int add(Category category); 
          
      @DeleteProvider(type=CategoryDynaSqlProvider.class,method="delete")
      public void delete(int id); 
          
      @SelectProvider(type=CategoryDynaSqlProvider.class,method="get") 
      public Category get(int id); 
        
      @UpdateProvider(type=CategoryDynaSqlProvider.class,method="update") 
      public int update(Category category);  
          
      @SelectProvider(type=CategoryDynaSqlProvider.class,method="list")     
      public List<Category> list(); 
  }

• 查询测试
  
  用同样的测试类，进行CRUD测试。注意，id值需要根据实际情况调整
  

  package com.how2java;
     
  import java.io.IOException;
  import java.io.InputStream;
  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.mapper.CategoryMapper;
  import com.how2java.pojo.Category;
     
  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();
          CategoryMapper mapper = session.getMapper(CategoryMapper.class);
    
  //        add(mapper);
  //        delete(mapper);
  //        get(mapper);
  //        update(mapper);
          listAll(mapper);
                
          session.commit();
          session.close();
     
      }
    
      private static void update(CategoryMapper mapper) {
          Category c= mapper.get(14);
          c.setName("修改了的Category名稱");
          mapper.update(c);
          listAll(mapper);
      }
    
      private static void get(CategoryMapper mapper) {
          Category c= mapper.get(14);
          System.out.println(c.getName());
      }
    
      private static void delete(CategoryMapper mapper) {
          mapper.delete(13);
          listAll(mapper);
      }
    
      private static void add(CategoryMapper mapper) {
          Category c = new Category();
          c.setName("新增加的Category");
          mapper.add(c);
          listAll(mapper);
      }
     
      private static void listAll(CategoryMapper mapper) {
          List<Category> cs = mapper.list();
          for (Category c : cs) {
              System.out.println(c.getName());
          }
      }
  }

• 关于SQL类
  
  SQL类是用于进行动态SQL生成的，如下代码是一个相对复杂的SQL类的使用举例，以后需要用到的时候，再来参考。
  

  private String selectPersonSql() {
    return new SQL() {{
      SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME");
      SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON");
      FROM("PERSON P");
      FROM("ACCOUNT A");
      INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");
      INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID");
      WHERE("P.ID = A.ID");
      WHERE("P.FIRST_NAME like ?");
      OR();
      WHERE("P.LAST_NAME like ?");
      GROUP_BY("P.ID");
      HAVING("P.LAST_NAME like ?");
      OR();
      HAVING("P.FIRST_NAME like ?");
      ORDER_BY("P.ID");
      ORDER_BY("P.FULL_NAME");
    }}.toString();
  }