CODEWITHSUNDEEP
javadatabasemybatis
eric2323223
eric2323223

Reputation: 3628

How to run arbitrary sql with mybatis?

I've an application that use mybatis for object persistence. But there are chances I need to run arbitrary sql(from user). Can I do it with mybatis?

Update:

I choose to use dbutils (JDBC) to run user-defined sql, but I need a instance of DataSource to create QueryRunner. Is there any way I can get datasource from mybatis?

Upvotes: 14

Views: 25783

Answers (5)

Geoffrey Ritchey
Geoffrey Ritchey

Reputation: 217

For testing I use

import org.apache.ibatis.jdbc.ScriptRunner;
import java.io.Reader;
import java.io.StringReader;
    
public class test {
        private static final String conf = "mybatis.conf.xml";

        private SqlSessionFactoryBuilder builder;
        private SqlSessionFactory sessionFactory;
        Reader reader;
        private SqlSession session;
        private ScriptRunner runner;
       
    @Before
    public void before() {
        builder = new SqlSessionFactoryBuilder();

        try {
            reader = Resources.getResourceAsReader(conf);
        } catch (IOException e) {
            e.printStackTrace();
        }

        sessionFactory = builder.build(reader);

        session = sessionFactory.openSession();
        runner = new ScriptRunner(session.getConnection());
        runner.setAutoCommit(true);
        runner.setStopOnError(true);
    }

    @Test   
    public void testSelectChapelStatus() {  
        Reader populate = new StringReader("insert into person values (7553,0,'201002496','Wish','Jill','Rain',1,0,NULL,'[email protected]');\r\n"
                + "");
         runner.runScript(populate);
    }

Upvotes: 1

Ben
Ben

Reputation: 149

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" />

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

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

  2. call this select method like

String _sql = "select * from table where... order by... limit...";
xxxxx.findRecords(new SQLAdapter(_sql));
  1. Things have been all done. you can no longer write complex sql language in the xml file. Good Luck.

Upvotes: 5

Harry
Harry

Reputation: 85

Reusable fragment of SQL can be used to create select part of query dynamically. In you mapper pass query as normal parameter:

@Param("sql")String sql

In your query just access the parameter using ${sql} instead of #{sql}. Value in parameter sql can be a fully valid sql query or a fragment of sql query.

Upvotes: 2

cinqS
cinqS

Reputation: 1223

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.

Upvotes: 2

Italo Borssatto
Italo Borssatto

Reputation: 15709

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);
}

Upvotes: 17

Related Questions