CODEWITHSUNDEEP
javamybatis
IgorZ
IgorZ

Reputation: 1164

Dynamic SQL request with MyBatis Velocity

Here is an example that works:

public class Parameter {
  private final String name;
  private final Long Id;
  ... constructor and getters
}

@Mapper
public interface UserMyBatisRepository {
    @Select("select * from users WHERE users.name=@{name} #if($_parameter.id) AND users.id=@{id}#end")
    public List<User> find(Parameter p);
}

I want to add the field sql into the Parameter and to use the Mapper like this:

@Select("@{sql}")
public List<User> find(Parameter p);
or
@Select("$_parameter.sql")
public List<User> find(Parameter p);

It seems that it does not apply the formatting in such case because I keep getting the errors like:

### SQL: ?
### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''select * from users WHERE users.name=@{name} #if($_parameter.id) AND u' at line 1

### SQL: select * from users WHERE users.name=@{name} #if($_parameter.id) AND users.id=@{id}#end
### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{name} #if($_parameter.id) AND users.id=@{id}#end' at line 1

Is there any proper way to implement it?

Upvotes: 0

Views: 287

Answers (1)

ave
ave

Reputation: 3594

You need to use SQL Provider.

// other imports...
import org.apache.ibatis.annotations.Lang;
import org.apache.ibatis.annotations.SelectProvider;
import org.mybatis.scripting.velocity.VelocityLanguageDriver;

@Mapper
public interface UserMyBatisRepository {

  @Lang(VelocityLanguageDriver.class)
  @SelectProvider(type = Provider.class, method = "find")
  List<User> find(Parameter p);

  class Provider {
    public String find(Parameter p) {
      return p.getSql();
    }
  }

}

@Lang is unnecessary if you specify defaultScriptingLanguage in the config.

p.s.
For the default XML scripting, see this answer.

Upvotes: 1

Related Questions