SQL元素
sql元素用来定义sql语句通用部分,当两个select的查询字段很多并且相同时,sql就派上了用场。
<resultMap type="Role" id="roleResultMap">
<id property="id" column="id"/>
<result property="roleName" column="role_name"/>
<result property="note" column="note"/>
</resultMap>
<sql id="roleColumns">
id, role_name, note
</sql>
<select id="getRole" parameterType="java.lang.Integer" resultMap="roleResultMap">
select <include refid="roleColumns"/> from role where id = #{id}
</select>
<select id="getRoleByRoleName" parameterType="java.lang.String" resultMap="roleResultMap">
select <include refid="roleColumns"/> from role where role_name like #{roleName}
</select>
sql字符串
#{param}引用的是参数值,用来填充PreparedStatement中SQL语句中的?。${str}可以把传进来的字符串直接作为sql语句的一部分直接执行SQL。
RoleMapper接口
List<Role> getRoles(@Param("condition") String condition);
roleMapper.xml
<select id="getRoles" parameterType="java.lang.String" resultMap="roleResultMap">
select <include refid="roleColumns"/> from role where ${condition}
</select>
注意定义接口方法时,使用了@Param注解,给参数命名,然后在sql语句中使用${参数名},不使用@Param会报错。
可以配置mybatis log4j查看$和#的区别
log4j.properties
log4j.rootLogger=DEBUG, stdout
log4j.logger.org.mybatis=DEBUG
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p %d %C: %m%n
[DEBUG] 2017-03-21 09:53:15,468 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Preparing: select id, role_name, note from role where id < 10005
order by role_name
[DEBUG] 2017-03-21 09:53:15,517 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters:
[DEBUG] 2017-03-21 09:53:15,553 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <== Total: 3
#
[DEBUG] 2017-03-21 09:55:58,597 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Preparing: select id, role_name, note from role where ?
[DEBUG] 2017-03-21 09:55:58,647 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: id < 10005 order by role_name(String)
[DEBUG] 2017-03-21 09:55:58,665 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <== Total: 0