Can I use MyBatis to generate Dynamic SQL without executing it?

 

Although MyBatis was designed to execute the query after it builds it, you can make use of it's configuration and a little bit of "inside knowledge" to get to what you need.

MyBatis is a very nice framework, unfortunately it lacks on the documentations side so the source code is you friend. If you dig around you should bump into these classes: org.apache.ibatis.mapping.MappedStatement and org.apache.ibatis.mapping.BoundSql which are key players into building the dynamic SQL. Here is a basic usage example:

MySQL table user with this data in it:

name    login
-----   -----
Andy    a
Barry   b
Cris    c

User class:

package pack.test;
public class User {
    private String name;
    private String login;
    // getters and setters ommited
}

UserService interface:

package pack.test;
public interface UserService {
    // using a different sort of parameter to show some dynamic SQL
    public User getUser(int loginNumber);
}

UserService.xml mapper file:

<mapper namespace="pack.test.UserService">
    <select id="getUser" resultType="pack.test.User" parameterType="int">
       <!-- dynamic change of parameter from int index to login string -->
       select * from user where login = <choose>
                                           <when test="_parameter == 1">'a'</when>
                                           <when test="_parameter == 2">'b'</when>
                                           <otherwise>'c'</otherwise>
                                        </choose>
    </select>
</mapper>

sqlmap-config.file:

<configuration>
    <settings>
        <setting name="lazyLoadingEnabled" value="false" />
    </settings>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost/test"/>
                <property name="username" value="..."/>
                <property name="password" value="..."/>
            </dataSource>
        </environment>
      </environments>
    <mappers>
        <mapper resource="pack/test/UserService.xml"/>
    </mappers>
</configuration>

AppTester to show the result:

package pack.test;

import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class AppTester {
    private static String CONFIGURATION_FILE = "sqlmap-config.xml";

    public static void main(String[] args) throws Exception {
        Reader reader = null;
        SqlSession session = null;
        try {

            reader = Resources.getResourceAsReader(CONFIGURATION_FILE);
            session = new SqlSessionFactoryBuilder().build(reader).openSession();
            UserService userService = session.getMapper(UserService.class);

            // three users retreived from index
            for (int i = 1; i <= 3; i++) {
                User user = userService.getUser(i);
                System.out.println("Retreived user: " + user.getName() + " " + user.getLogin());

                // must mimic the internal statement key for the mapper and method you are calling
                MappedStatement ms = session.getConfiguration().getMappedStatement(UserService.class.getName() + ".getUser");
                BoundSql boundSql = ms.getBoundSql(i); // parameter for the SQL statement
                System.out.println("SQL used: " + boundSql.getSql());
                System.out.println();
            }

        } finally {
            if (reader != null) {
                reader.close();
            }
            if (session != null) {
                session.close();
            }
        }
    }
}

And the result:

Retreived user: Andy a
SQL used: select * from user where login =  'a'

Retreived user: Barry b
SQL used: select * from user where login =  'b'

Retreived user: Cris c
SQL used: select * from user where login =  'c'

http://stackoverflow.com/questions/13195144/can-i-use-mybatis-to-generate-dynamic-sql-without-executing-it

 https://my.oschina.net/lichhao/blog/114311

时间: 2024-11-02 12:23:57

Can I use MyBatis to generate Dynamic SQL without executing it?的相关文章

【mybatis深度历险系列】mybatis中的动态sql

最近一直做项目,博文很长时间没有更新了,今天抽空,学习了一下mybatis,并且总结一下.在前面的博文中,小编主要简单的介绍了mybatis中的输入和输出映射,并且通过demo简单的介绍了输入映射和输出映射,今天这篇博文,小编主要来简单的介绍一下mybatis中的动态sql,有的小伙伴会问,既然有动态sql,那是不是也应该存在静态sql,答案是肯定的.那么什么是静态sql呢,静态sql语句一般用于嵌入式sql应用中,在程序运行之前,sql语句必须是确定的,例如sql语句中涉及的列名和表名必须是存

MyBatis实践之动态SQL及关联查询_MsSql

序言 MyBatis,大家都知道,半自动的ORM框架,原来叫ibatis,后来好像是10年apache软件基金组织把它托管给了goole code,就重新命名了MyBatis,功能相对以前更强大了.它相对全自动的持久层框架Hibernate,更加灵活,更轻量级,这点我还是深有体会的. MyBatis的一个强大特性之一就是动态SQL能力了,能省去我们很多串联判断拼接SQL的痛苦,根据项目而定,在一定的场合下使用,能大大减少程序的代码量和复杂程度,不过还是不是过度太过复杂的使用,以免不利于后期的维护

MyBatis实践之动态SQL及关联查询

序言 MyBatis,大家都知道,半自动的ORM框架,原来叫ibatis,后来好像是10年apache软件基金组织把它托管给了goole code,就重新命名了MyBatis,功能相对以前更强大了.它相对全自动的持久层框架Hibernate,更加灵活,更轻量级,这点我还是深有体会的. MyBatis的一个强大特性之一就是动态SQL能力了,能省去我们很多串联判断拼接SQL的痛苦,根据项目而定,在一定的场合下使用,能大大减少程序的代码量和复杂程度,不过还是不是过度太过复杂的使用,以免不利于后期的维护

java利用mybatis拦截器统计sql执行时间示例_java

可以根据执行时间打印sql语句,打印的sql语句是带参数的,可以拷贝到查询分析器什么的直接运行 复制代码 代码如下: package mybatis; import java.text.DateFormat;import java.util.Date;import java.util.List;import java.util.Locale;import java.util.Properties; import org.apache.ibatis.executor.Executor;import

MyBatis之一对多映射查询sql配置文件。

学生---文章的模型一对多模型 学生student.java类 1 package com.bjsxt.sxf.po; 2 3 import java.util.Date; 4 import java.util.List; 5 import java.util.Set; 6 7 /** 8 * 学生 9 * @ClassName: Student 10 * @Description: TODO(这里用一句话描述这个类的作用) 11 * @author 尚晓飞 12 * @date 2014-11

MySQL Stored Procedure Prepared Statement (Dynamic SQL) Parameterized

类似于SQL Server中的:sp_executesql sql server script: --- 涂聚文 20160906 IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_DuDeptUserCount') DROP PROCEDURE proc_Select_DuDeptUserCount GO CREATE PROCEDURE proc_Select_DuDeptUserCount ( @where NV

一些SQL Server存储过程参数及例子_MsSql

Microsoft included several hundred stored procedures in the various versions of Microsoft SQL Server and it has documented a good percentage of them. But many stored procedures remain undocumented. Some are used within the Enterprise Manager GUI in S

一些SQLServer存储过程参数及举例_MsSql

Microsoft included several hundred stored procedures in the various versions of Microsoft SQL Server and it has documented a good percentage of them. But many stored procedures remain undocumented. Some are used within the Enterprise Manager GUI in S

一些SQLServer存储过程参数及举例

Microsoft included several hundred stored procedures in the various versions of Microsoft SQL Server and it has documented a good percentage of them. But many stored procedures remain undocumented. Some are used within the Enterprise Manager GUI in S