MyBatis-映射器中sql标签详解

sql标签用来定义可重复使用的sql语句片段

必要说明

• #在ibatis中表示参数引用
• $在ibatis中表示变量引用

sql标签候选属性说明

• id
sql标签的全局唯一标识;
• databaseId
  与ibatis全局配置中的databaseIdProvider标签呼应;
• lang
  可自定义的第三方语言,一般不使用;

使用规范说明

• 语法规范

  <sql id="sql_unique_id">         
  	sql_part_statement           
  	<include refid="other_var"/> 
  	...                          
  </sql>                           
  

• sql标签方案的选择

  • 直接使用字面量的方式

    <sql id="xxx">               
    	column_a,column_b,column_c
    </sql>                       
    

  • 也可在sql语句中使用变量引用;
    此处示例使用的是表别名.
    注意,声明中的prefix是可自定义的,可以是任何合法的标识符

    <sql id="sql_tag_id">                                               
    	${prefix}.column_a, ${prefix}.column_b, ${prefix}.column_c
    </sql>                                                       
    

    在增删改查标签引用使用

    <select id="xxx">                            
    	<include refid="sql_tag_id">     
    		...	               
    		<property name="prefix" value="prefix_value_inSql"/>
    		...
    	</include>                               
    </select>                                    
    

  • 嵌套使用sql标签
    这种形式会大大降低sql的可读性,一般不建议嵌套sql使用.

    <sql id="some_sql">                                      
    	...                                                  
    </sql>                                                   
    <sql id="all_sql">                                       
    	...                                                  
    	<include refid="${include_sql}" />                   
    	${prefix}                                            
    	...                                                  
    </sql>                                                   
    <select id="xxx">                                        
    	...                                                  
    	<include refid="all_sql">                            
    		<property name="include_sql" value="some_sql" /> 
    		<property name="prefix" value="prefix_value" />  
    	</include>                                           
    	...                                                  
    </select>                                                
    

代码测试sql标签说明

sql标签的使用步骤

1. entity类对象,ibatis全局配置设置省略;

      CREATE TABLE `emp` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) NOT NULL,
      `age` int(11) NOT NULL,
      `sex` int(4) NOT NULL,
      PRIMARY KEY (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
   

2. mapper映射配置文件中配置需求;
   使用sql标签定义表的列名称

   <sql id="test_sql">                                            
   	${tbl}.id, ${tbl}.name, ${tbl}.age, ${tbl}.sex             
   </sql>                                                         
   <select id="test_sql_tag0" resultType="siye.ibatis.entity.Emp">
   	SELECT                                                     
   	<include refid="test_sql">                                 
   		<property name="tbl" value="e" />                      
   	</include>                                                 
   	FROM emp e                                                 
   </select>                                                      
   

   使用sql标签定义表的表名变量

   <sql id="table_name">                                          
   	select * from ${tbl_name}                                  
   </sql>                                                         
   <select id="test_sql_tag1" resultType="siye.ibatis.entity.Emp">
   	<include refid="table_name">                               
   		<property name="tbl_name" value="emp" />               
   	</include>                                                 
   </select>                                                      
   

   嵌套sql标签的使用

   <sql id="some_sql">                                            
   	select * from                                              
   </sql>                                                         
   <sql id="all_sql">                                             
   	<include refid="${include_sql}">                           
   	</include>                                                 
   	${tbl_name}                                                
   </sql>                                                         
   <select id="test_sql_tag2" resultType="siye.ibatis.entity.Emp">
   	<include refid="all_sql">                                  
   		<property name="include_sql" value="some_sql" />       
   		<property name="tbl_name" value="emp" />               
   	</include>                                                 
   </select>                                                      
   

3. mapper接口省略;
4. 测试类中的主要代码;

   SqlSession session = sqlSessionFactory.openSession(true);
   try
   {
   	SqlTag dao = session.getMapper(SqlTag.class);
   
   	List<Emp> list0 = dao.test_sql_tag0();
   	log.info(list0.size());
   
   	List<Emp> list1 = dao.test_sql_tag1();
   	log.info(list1.size());
   
   	List<Emp> list2 = dao.test_sql_tag2();
   	log.info(list2.size());
   
   } finally
   {
   	session.close();
   }
   

5. 输出结果部分截取;

   DEBUG 2019-01-08 15:39:46,893 org.apache.ibatis.logging.jdbc.BaseJdbcLogger : ==>  Preparing: SELECT e.id, e.name, e.age, e.sex  FROM   emp e 
   DEBUG 2019-01-08 15:39:46,937 org.apache.ibatis.logging.jdbc.BaseJdbcLogger : ==> Parameters: 
   TRACE 2019-01-08 15:39:46,960 org.apache.ibatis.logging.jdbc.BaseJdbcLogger : <==    Columns: id, name, age, sex
   ...
   DEBUG 2019-01-08 15:39:46,971 org.apache.ibatis.logging.jdbc.BaseJdbcLogger : <==      Total: 7
    INFO 2019-01-08 15:39:46,971 siye.ibatis.test.Test_mapperUse : 7
   DEBUG 2019-01-08 15:39:46,972 org.apache.ibatis.logging.jdbc.BaseJdbcLogger : ==>  Preparing: select * from emp 
   DEBUG 2019-01-08 15:39:46,972 org.apache.ibatis.logging.jdbc.BaseJdbcLogger : ==> Parameters: 
   TRACE 2019-01-08 15:39:46,973 org.apache.ibatis.logging.jdbc.BaseJdbcLogger : <==    Columns: id, name, age, sex
   ...
   DEBUG 2019-01-08 15:39:46,978 org.apache.ibatis.logging.jdbc.BaseJdbcLogger : <==      Total: 7
    INFO 2019-01-08 15:39:46,978 siye.ibatis.test.Test_mapperUse : 7
   DEBUG 2019-01-08 15:39:46,978 org.apache.ibatis.logging.jdbc.BaseJdbcLogger : ==>  Preparing: select * from emp 
   DEBUG 2019-01-08 15:39:46,979 org.apache.ibatis.logging.jdbc.BaseJdbcLogger : ==> Parameters: 
   TRACE 2019-01-08 15:39:46,980 org.apache.ibatis.logging.jdbc.BaseJdbcLogger : <==    Columns: id, name, age, sex
   ...
   DEBUG 2019-01-08 15:39:46,986 org.apache.ibatis.logging.jdbc.BaseJdbcLogger : <==      Total: 7
    INFO 2019-01-08 15:39:46,986 siye.ibatis.test.Test_mapperUse : 7