How to run arbitrary sql with mybatis?

Based on the answers provided, they both are good. But both of them required an Adapter class to be used.

Using Mybatis version 3, I succeeded using a HashMap<String, String> to keep and pass the SQL.

See the codes below.

in Mapper class

final String sql = "${sql}";

@Select(sql)
void execute(HashMap<String, String> m);

when invoke the method:

String sql = "SELECT * FROM record limit 1";
HashMap<String, String> map = new HashMap<String, String>();
map.put("sql", sql);
mapper.execute(map);

HashMap provides a way that you don't have to define the Class properties, or fields in code, you can use a Map to define it redomly.

Thanks.

I use this utilitary class:

import java.util.List;
import org.apache.ibatis.annotations.SelectProvider;

public interface SqlMapper {
    static class PureSqlProvider {
        public String sql(String sql) {
            return sql;
        }

        public String count(String from) {
            return "SELECT count(*) FROM " + from;
        }
    }

    @SelectProvider(type = PureSqlProvider.class, method = "sql")
    public List<?> select(String sql);

    @SelectProvider(type = PureSqlProvider.class, method = "count")
    public Integer count(String from);

    @SelectProvider(type = PureSqlProvider.class, method = "sql")
    public Integer execute(String query);
}

Your question is similar to How to exequte query directly from java code using mybatis?

I have already given the answer to that question. But I hope this solution will help you.

Mybatis has already this function, but you must use the adapter as follows.

1. create an adapter class;

    public class SQLAdapter {  
    String sql;  
   
    public SQLAdapter(String sql) {  
        this.sql = sql;  
    }  
   
    public String getSql() {  
        return sql;  
    }  
   
    public void setSql(String sql) {  
        this.sql = sql;  
    }   }
   

2. create typeAlias of class SQLAdapter

<typeAlias alias="sqladapter" type="com.zj.xxx.xxx.SQLAdapter" />

3. put select tag in each object xml where you need to execute the sql directly.

    <select id="findRecords" parameterType="SQLAdapter" resultMap="xxxxxResultMap">  
        ${sql}  
    </select> 
   

4. call this select method like

String _sql = "select * from table where... order by... limit...";
xxxxx.findRecords(new SQLAdapter(_sql));

5. Things have been all done. you can no longer write complex sql language in the xml file. Good Luck.