Dynamic sql using @SelectProvider

[hj005zy]

I'm trying to implement "common crud mapper" and I checked #320 the idea is similar.

Basicly, I want to do something like

public interface BaseMapper<T> {
    @SelectProvider(type = BaseMapperProvider.class, method="selectOne")
    List<T> select(@Param("id") Long id, @Param("columns") String[] columns);
}

public class BaseMapperProvider {
    public String selectOne(Method mapperMethod, Long id, String[] columns) {
        Class<?> entityClass = mapperMethod.getReturnType();
        String tableName = getTableName(entityClass);
        BEGIN();
        SELECT(String.joins(columns, ","));
        FROM(tableName);
        WHERE("id = " + id);
    }
}

Also we can generate sql in JPA way

public interface UserMapper {
    @SelectProvider(type = BaseMapperProvider.class, method="selectByExample")
    User findByUsername(String username);
}

public class BaseMapperProvider {
    public String selectByExample(Method mapperMethod, Object param) {
        Class<?> entityClass = mapperMethod.getReturnType();
        String tableName = getTableName(entityClass);
        String conditionColumn = StringUtils.substringAfter(mapperMethod.getName(), "findBy");
        BEGIN();
        SELECT("*");
        FROM(tableName);
        WHERE(conditionColumn + " = " + param);
    }
}

I checked source code and seems we can just add a Method type parameter to ProviderSqlSource construct like

public ProviderSqlSource(Configuration config, Object provider, Method mapperMethod)

And in MapperAnnotationBuilder

Annotation sqlProviderAnnotation = method.getAnnotation(sqlProviderAnnotationType);
return new ProviderSqlSource(assistant.getConfiguration(), sqlProviderAnnotation, method);

Any ideas?

[huayanYu]

I want this also. In my company,i change some sourcecode to resove it and support JPA; If original mybatis can support Dynamic, it will useful for me.

@Data
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "sys_user")
public class H2User implements Serializable {

  private static final long serialVersionUID = 1L;

  @Id
  private Integer id;

  @Column(nullable = false)
  private String name;

  @Column(name = "t_age", nullable = false)
  private Integer age;

  private Double salary;

  @Version
  private Integer version;

  @LogicDelete
  private Integer isDelete;

  @Transient
  private List<String> articals;

  @Column(name = "create_user", updatable = false)
  private String createUser;

  @Column(name = "create_time", updatable = false)
  private Date createTime;

  @Column(name = "last_updateUser")
  private String lastUpdateUser;

  @Column(name = "last_updateTime")
  private Date lastUpdateTime;

  public H2User(String name) {
    super();
    this.name = name;
  }
}

BaseMapper like this

public interface BaseMapper<T extends Serializable, PK extends Serializable> {

  @SelectProvider(type = SelectMapperHandler.class, method = "selectById")
  T _selectById(PK id);

  @SelectProvider(type = SelectMapperHandler.class, method = "selectBatchIds")
  List<T> _selectBatchIds(List<? extends Serializable> ids);

Common CRUD provider like this

public String selectById(Serializable id, MappingInfo info) {
    SQL sql = new SQL().SELECT(info.getSelectBody()).FROM(info.getTableName());
    PrimaryKey primaryKey = info.getPrimaryKey();
    if (primaryKey != null) {
      if (id == null) {
        sql.WHERE(primaryKey.getColumnName() + " IS NULL");
      } else {
        sql.WHERE(primaryKey.getColumnName() + " = #{" + primaryKey.getPropertyName() + "}");
      }
    }
    handleLogic(sql, info);
    return sql.toString();
  }

  public String selectBatchIds(List<Serializable> params, MappingInfo info) {
    if (params == null || params.isEmpty()) {
      return this.selectById(null, info);
    }
    StringBuilder ids = new StringBuilder(params.size());
    for (int i = 0; i < params.size(); i++) {
      ids.append("#{list[" + i + "]},");
    }
    SQL sql = new SQL().SELECT(info.getSelectBody()).FROM(info.getTableName())
        .WHERE(info.getPrimaryKey().getColumnName() + " in (" + ids.deleteCharAt(ids.length() - 1) + ")");
    handleLogic(sql, info);
    return sql.toString();
  }

I must override ProviderSqlSource's createSqlSource

 @SuppressWarnings("unchecked")
  private SqlSource createSqlSource(Object parameterObject) {
    String name = providerMethod.getName();
    try {
      Class<?>[] parameterTypes = providerMethod.getParameterTypes();
      String sql;
      //MapingInfo is a POJO to describe TABLEINFO
      MappingInfo mappingInfo = MappingHelper.getMapping(namespace);
      if (parameterTypes.length == 1) {
        sql = (String) providerMethod.invoke(newInstance(providerType), mappingInfo);
      } else if (parameterTypes.length == 2) {
        if (parameterObject instanceof Map) {
          Map<String, Object> params = (Map<String, Object>) parameterObject;
          if (params.size() == 2 && params.containsKey("list") && params.containsKey("collection")) {
            sql = (String) providerMethod.invoke(newInstance(providerType), params.get("list"), mappingInfo);
          } else {
            sql = (String) providerMethod.invoke(newInstance(providerType), parameterObject, mappingInfo);
          }
        } else {
          sql = (String) providerMethod.invoke(newInstance(providerType), parameterObject, mappingInfo);
        }
      } else if (parameterObject instanceof Map) {
        Map<String, Object> params = (Map<String, Object>) parameterObject;
        if (params.containsKey("condition") && params.containsKey("targetObj")) {
          sql = (String) providerMethod.invoke(newInstance(providerType), params.get("targetObj"),
              params.get("condition"), mappingInfo);
        } else {
          sql = (String) providerMethod.invoke(providerType.newInstance(),
              extractProviderMethodArguments((Map<String, Object>) parameterObject, providerMethodArgumentNames));
        }
      } else {
        throw new BuilderException("Error invoking SqlProvider method (" + providerType.getName() + "." + name
            + "). Cannot invoke a method that holds "
            + "multiple arguments using a specifying parameterObject. In this case, please specify a 'java.util.Map' object.");
      }
      Class<?> parameterType = parameterObject == null ? Object.class : parameterObject.getClass();
      return sqlSourceParser.parse(sql, parameterType, new HashMap<>());
    } catch (BuilderException e) {
      throw e;
    } catch (Exception e) {
      throw new BuilderException(
          "Error invoking SqlProvider method (" + providerType.getName() + "." + name + ").  Cause: " + e, e);
    }
  }

[w6et]

I want this also. @Table(name = "t_child") public class Child implements Serializable { @One(alias="p") private Parent parent; }

@Table(name = "t_parent") public class Child implements Serializable { @Many(alias="c") private List childList; } case @One（FetchType=join\select）,default is join： if sql contains("left join"), owner's Entity auto assign an alias(first char of Entity'name,like c) sql will be dynamic generate "select c.first_name as firstName,c.last_name as lastName,p.topic_name as pTopicName left join xxxx",and the "parent" Property will be init from resultSet

case @Many（FetchMode=eager\lazy）,default is eager： 1）no @Many annotation or fetchMode=lazy： "childList" not need load right now,developer can populate it with self business code; 2）has @Many annotation： "childList" need load after " select * from parent"

============================================== like https://github.com/abel533/Mapper maybe XMLMapperBuilder的configurationElement sqlElement(context.evalNodes("/mapper/sql")); can add dynamicSql such as dynamic generate by “/abel533/Mapper”‘,and with an id assigned by framework ; so the xxxMapper.xml can by buildin id.

[jeffgbutler]

I've been working on a new library for dynamic SQL that works directly with the existing MyBatis annotations. It is more typesafe, more extensible, and more expressive, then the built in MyBatis SQL builder. It doesn't support joins yet, but that is the next thing I am working on. My plan is that this will replace the code generated by the generator with something much better, but it does not require the use of the generator. You can see it here: https://github.com/jeffgbutler/mybatis-dynamic-sql. I would be interested in your comments about it and if you think it might have value for your use cases.

One question I have for this thread - if you want to use annotations that look like JPA, why not just use JPA directly?

[harawata]

Just for the record, the only enhancement I would consider at this point is to pass java.lang.reflect.Method as a parameter and I will consult with other committers before working on it.

[sdcuike]

@harawata @hj005zy @svatiYu @weishuwei @jeffgbutler why not use java SPI to locate user's SqlSource implement

[kazuki43zoo]

Hi guys,

I've tried to support following types on sql provider method via gh-1055.

• Mapper interface : Allow accessing to a type annotation, parameterized type or etc ...
• Mapper method : Allow accessing to a method annotation, return type or etc ...

What do you think ?

[huayanYu]

It is not enough for me,it is too simple. it can only get tableName and loggic? Look my sqlCreater;

  public String selectById(Serializable id, MappingInfo info) {
    SQL sql = new SQL().SELECT(info.getSelectBody()).FROM(info.getTableName());
    PrimaryKey primaryKey = info.getPrimaryKey();
    if (primaryKey != null) {
      if (id == null) {
        sql.WHERE(primaryKey.getColumnName() + " IS NULL");
      } else {
        sql.WHERE(primaryKey.getColumnName() + " = #{" + primaryKey.getPropertyName() + "}");
      }
    }
    handleLogic(sql, info);
    return sql.toString();
  }

what is MappingInfo?

It is a pojo to describe All tableInfo and columnInfo; like this

public final class MappingInfo {

  /**
   * 全局配置
   */
  private MybatisConfiguration configuration;

  /**
   * 表名
   */
  private String tableName;

  /**
   * 查询字段
   */
  private String selectBody;

  /**
   * 主键
   */
  private PrimaryKey primaryKey;

  /**
   * 字段信息列表
   */
  private Set<FieldInfo> fieldInfos;

  /**
   * 逻辑删除值
   */
  private LogicField logicField;

}

public final class PrimaryKey {

  /**
   * 主键Field
   */
  private Field field;

  /**
   * 主键在java中的名称
   */
  private String propertyName;

  /**
   * 主键在db中的名称
   */
  private String columnName;

  /**
   * 主键类型
   */
  private GenerationType generationType;

  /**
   * 序列主键配置
   */
  private SequenceGenerator sequenceGenerator;

  /**
   * 表主键配置
   */
  private TableGenerator tableGenerator;

  private AutoKeyGenerator<?> autoKeyGenerator;

}

Now I hava to do create a new ProvideSqlSource of my own and changge method createSqlSource;

private SqlSource createSqlSource(Object parameterObject) {
  String name = providerMethod.getName();
    try {
      Class<?>[] parameterTypes = providerMethod.getParameterTypes();
      String sql;
/*notice here,i get current MappingInfo by currentNamespace*/
     MappingInfo mappingInfo = MappingHelper.getMapping(namespace);
      if (parameterTypes.length == 1) {
/* here to invoke mappring info*/
        sql = (String) providerMethod.invoke(newInstance(providerType), mappingInfo);
      } else if (parameterTypes.length == 2) {
        if (parameterObject instanceof Map) {
          Map<String, Object> params = (Map<String, Object>) parameterObject;
          if (params.size() == 2 && params.containsKey("list") && params
              .containsKey("collection")) {
            sql = (String) providerMethod
                .invoke(newInstance(providerType), params.get("list"), mappingInfo);
.........................................

Do u hava any Idea? @kazuki43zoo could ProviderSqlSource filds be protected so that I can override createSqlSource? or other ways to resove it? What i want is to invoke my MappingInfo.

[harawata]

Hi @huayanYu ,

There is no plan to allow customizing or overriding ProviderSqlSource at the moment.

You should be able to get the namespace from the mapper interface class (it's the fully-qualified name of the class) and build MappingInfo in the provider method.

[hj005zy]

@kazuki43zoo Great job! This is what I want.

@jeffgbutler JPA is a bit cumbersome for me, and I want something in middle, just generates some simple sqls.

[harawata]

Hi all,

@kazuki43zoo 's patch has been merged. Please try the latest 3.4.5-SNAPSHOT and give us your feedback before we release the final version. Thank you!

[kazuki43zoo]

Hi all, I've created the simple demo for common mapper pattern using mybatis-spring-boot-starter(+mybatis 3.4.5-SNAPSHOT).