SQL Map XML Files
ibatis最大的优点就是可以把sql语句移到xml文件中,SQL Map XML Files包含如下几个元素:
cache:配置命名空间内的缓存机制
cache-ref:引用两一个命名空间的缓存配置
resultMap:获取数据库结果集时转化为对象的映射
sql:sql命令
insert:insert语句配置
update:update语句配置
delete:delete语句配置
select:select语句配置
select
<select id=”selectPerson” parameterType=”int” resultType=”hashmap”>
SELECT * FROM PERSON WHERE ID = #{id}
select>
定义了一个名称为selectPerson的方法,包含一个int类型的形参,返回一个由列名和值生成的哈希表
注意这里的#{id},实际上ibatis创建了一个PreparedStatement去处理这种参数,类似的jdbc代码如下:
// Similar JDBC code, NOT iBATIS…
String selectPerson = “SELECT * FROM PERSON WHERE ID=?”;
PreparedStatement ps = conn.prepareStatement(selectPerson);
ps.setInt(1,id);
select 标签还有很多的可配置属性:
<select
id=”selectPerson”
parameterType=”int”
parameterMap=”deprecated”
resultType=”hashmap”
resultMap=”personResultMap”
flushCache=”false”
useCache=”true”
timeout=”10000”
fetchSize=”256”
statementType=”PREPARED”
resultSetType=”FORWARD_ONLY”
>
属性 | 描述 |
id | 命名空间中唯一标识sql语句的标识符 |
parameterType | sql语句中的参数类型 |
resultType | sql语句返回类型(与resultMap只能使用一个) |
resultMap | 引用外部resultMap(与resultType只能使用一个) |
flushCache | 设置立即输出缓存中数据 默认 false |
useCache | 设置是否使用缓存 默认 true |
timeout | 设置数据库超时时间 默认 根据数据库驱动而定 |
fetchSize | 一次抓取数据量 默认 根据数据库驱动而定 |
statementType | 可以设置为STATEMENT,PREPARED,CALLABLE,默认为PREPARED,像在调用存储过程时就可以使用CALLABLE类型 |
resultSetType | 结果集类型,主要是说游标,具有方向性,可以去FORWARD_ONLY、SCROLL_SENSITIVE、SCROLL_INSENSITIVE 默认根据数据库驱动而定 |
insert,update,delete
<insert id="insertAuthor" parameterType="domain.blog.Author" flushCache="true" statementType="PREPARED" keyProperty="" useGeneratedKeys="" timeout="20000"> <update id="insertAuthor" parameterType="domain.blog.Author" flushCache="true" statementType="PREPARED" timeout="20000"> <delete id="insertAuthor" parameterType="domain.blog.Author" flushCache="true" statementType="PREPARED" timeout="20000">
属性 | 描述 |
id | 命名空间中唯一标识sql语句的标识符 |
parameterType | sql语句中的参数类型 |
flushCache | 设置立即输出缓存中数据 默认 false |
timeout | 设置数据库超时时间 默认 根据数据库驱动而定 |
useGenerateKeys(insert) | 是否使用数据库自动生成的键(如自增列) 默认 false |
statementType | 可以设置为STATEMENT,PREPARED,CALLABLE,默认为PREPARED,像在调用存储过程时就可以使用CALLABLE类型 |
keyProperty(insert) | 将数据库自动生成的键值赋值给哪一个字段值 |
<insert id="insertAuthor" parameterType="domain.blog.Author"> insert into Author (id,username,password,email,bio) values (#{id},#{username},#{password},#{email},#{bio}) insert>
<insert id="insertAuthor" parameterType="domain.blog.Author" useGeneratedKeys=”true” keyProperty=”id”> insert into Author (username,password,email,bio) values (#{username},#{password},#{email},#{bio}) insert> <update id="updateAuthor" parameterType="domain.blog.Author"> update Author set username = #{username}, password = #{password}, email = #{email}, bio = #{bio} where id = #{id} update> <delete id="deleteAuthor” parameterType="int"> delete from Author where id = #{id} delete>
ibatis还对不支持自动生成主键值的数据库提供了主键值生成方案,如下是一个随机数主键值:
<insert id="insertAuthor" parameterType="domain.blog.Author"> <selectKey keyProperty="id" resultType="int" order="BEFORE"> select CAST(RANDOM()*1000000 as INTEGER) a from SYSIBM.SYSDUMMY1 selectKey> insert into Author (id, username, password, email,bio, favourite_section) values (#{id}, #{username}, #{password}, #{email}, #{bio}, #{favouriteSection,jdbcType=VARCHAR} ) insert>
关于selectKey详细属性说明如下:
<selectKey
keyProperty="id"
resultType="int"
order="BEFORE"
statementType="PREPARED">
属性 | 描述 |
keyProperty | 语句返回值的赋值对象 |
resultType | 返回类型 |
order | 可以设置为BEFORE、AFTER。如果是BEFORE,会先select键值,赋值键值然后执行insert语句。如果是AFTER,那么会先执行insert语句之后再select。默认AFTER |
statementType | 可以设置为STATEMENT,PREPARED,CALLABLE,默认为PREPARED,像在调用存储过程时就可以使用CALLABLE类型 |
sql
定义重用的sql代码
<sql id=”userColumns”> id,username,password sql>
<select id=”selectUsers” parameterType=”int” resultType=”hashmap”> select <include refid=”userColumns”/> from some_table where id = #{id} select>
Parameters
简单类型传参
传递一个int值给sql语句
<select id=”selectUsers” parameterType=”int” resultType=”User”>
select id, username, password
from users
where id = #{id}
select>
对象传参
将user对象的id username password属性传递给sql语句
<insert id=”insertUser” parameterType=”User” >
insert into users (id, username, password)
values (#{id}, #{username}, #{password})
insert>
定义特殊传参类型
#{property,javaType=int,jdbcType=NUMERIC}
注意,使用这种自定义类型转换时,jdbcType必须传递非空字段的值。
(网上查阅了一下,很多资料说ibatis对于null处理不好,发现select语句数据库中有null时基本没问题,而insert语句参数不能为null,原因是ibatis的赋值时有个java类型到数据库类型的转换,根据参数调用不同的转换器类型,null的话ibatis就无法识别参数类型,需要在sqlMap配置文件中显式说明参数类型,如:#{score:INTEGER}或者#{score,jdbcType=INTEGER})
对于NUMERIC类型,还有numericScale可以设置
#{height,javaType=double,jdbcType=NUMERIC,numericScale=2}
可以设置IN、OUT、INOUT参数类型
#{department,
mode=OUT,
jdbcType=CURSOR,
javaType=Department,
resultMap=departmentResultMap}
resultMap
<select id=”selectUsers” parameterType=”int” resultType=”hashmap”> select id, username, hashedPassword from some_table where id = #{id} sql> 这种hashmap的返回类型将所有的字段以字段名为key的hashMap返回,但是这样子并不是很好的对域中的对象进行映射,我们考虑下面这个POJO: public class Student { private int id; private String name; private String major; private Date birth; private double score; } 我们之前使用的select语句配置是: <select id="queryStudentById" parameterType="int" resultType="cn.pf.ibatis.domain.Student"> select * from Student where id = #{id} select> 这里ibatis默认使用表列名和POJO属性名对应的规则给属性赋值,我们也可以指定这种映射关系 <select id="queryStudentById" parameterType="int" resultType="cn.pf.ibatis.domain.Student"> select table_id as "id", table_name as "name", table_major as "major", table_birth as "birth", table_score as "score" from Student where id = #{id} select>这里我们引进resultmap,可以对这种映射关系进行复用
< resultMap id= "studentResultMap" type= "cn.pf.ibatis.domain.Student" >
< id property= "id" column= "id" />
< result property= "name" column= "name" />
< result property= "major" column= "major" />
< result property= "birth" column= "birth" />
< result property= "score" column= "score" />
resultMap >
< select id= "queryStudentById" parameterType= "int" resultMap= "studentResultMap" >
select
id as "id",
name as "name",
major as "major",
birth as "birth",
score as "score"
from Student where id = #{id}
select >
注意如果是查询返回了多个结果集合,那么ibatis会返回一个集合,如:
<select id="queryAllStudent" resultMap="studentResultMap">
select * from Student
select>
可以使用List接受ibatis返回的结果集合
List<Student> studentList = studentDAO.queryAllStudent();
< select id= "selectBlogDetails" parameterType= "int" resultMap= "detailedBlogResultMap" >
select
B.id as blog_id,
B.title as blog_title,
B.author_id as blog_author_id,
A.id as author_id,
A.username as author_username,
A.password as author_password,
A.email as author_email,
A.bio as author_bio,
A.favourite_section as author_favourite_section,
P.id as post_id,
P.blog_id as post_blog_id,
P.author_id as post_author_id,
P.created_on as post_created_on,
P.section as post_section,
P.subject as post_subject,
P.draft as draft,
P.body as post_body,
C.id as comment_id,
C.post_id as comment_post_id,
C.name as comment_name,
C.comment as comment_text,
T.id as tag_id,
T.name as tag_name
from Blog B
left outer join Author A on B.author_id = A.id
left outer join Post P on B.id = P.blog_id
left outer join Comment C on P.id = C.post_id
left outer join Post_Tag PT on PT.post_id = P.id
left outer join Tag T on PT.tag_id = T.id
where B.id = #{id}
select >
< resultMap id= "detailedBlogResultMap" type= "Blog" >
< constructor >
< idArg column= "id" javaType= "int" />
constructor >
< result property= "title" column= "blog_title" />
< association property= "author" column= "blog_author_id" javaType= " Author" >
< id property= "id" column= "author_id" />
< result property= "username" column= "author_username" />
< result property= "password" column= "author_password" />
< result property= "email" column= "author_email" />
< result property= "bio" column= "author_bio" />
< result property= "favouriteSection" column= "author_favourite_section" />
association >
< collection property= "posts" ofType= "Post" >
< id property= "id" column= "post_id" />
< result property= "subject" column= "post_subject" />
< association property= "author" column= "post_author_id" javaType= "Author" />
< collection property= "comments" column= "post_id" ofType= " Comment" >
< id property= "id" column= "comment_id" />
collection >
< collection property= "tags" column= "post_id" ofType= " Tag" >
< id property= "id" column= "tag_id" />
collection >
< discriminator javaType= "int" column= "draft" >
< case value= "1" resultType= "DraftPost" />
discriminator >
collection >
resultMap >
constructor: 用于在类构造函数中注入参数
idArg:ID 参数 用于提高整体性能
arg:普通参数
id:ID 结果
result:注入POJO属性的结果值
association:复杂的类型关联
collection:复杂类型集合
discriminator:根据结果值决定使用哪个resultMap
case:判断条件
详细说明各个元素的作用:
id,result:
<id property="id" column="post_id"/>
<result property="subject" column="post_subject"/>
id,result均将单个列的值映射到POJO中的单个简单类型属性(如int,double,Date等等)
id映射主键,result映射普通属性
属性 | 描述 |
property | POJO中对应的属性 |
column | 数据库中列名 |
javaType | java类型 |
jdbcType | jdbc类型 遇到空值时需要显示指明 |
typeHandler | 类型转换器 |
支持的jdbcTypes
BIT | FLOAT | CHAR | TIMESTAMP | OTHER | UNDEFINED |
TINYINT | REAL | VARCHAR | BINARY | BLOB | NVARCHAR |
SMALLINT | DOUBLE | LONGVARCHAR | VARBINARY | CLOB | NCHAR |
INTEGER | NUMBERIC | DATE | LONGVARBINARY | BOOLEAN | NCLOB |
BIGINT | DECIMAL | TIME | NULL | CURSOR |
constructor:
<constructor>
<idArg column="id" javaType="int"/>
<arg column=”username” javaType=”String”/>
constructor>
constructor属性可以配置通过类构造函数注入属性的一些参数。考虑User类这个构造函数
public class User {
//…
public User(int id, String username) {
//…
}
//…
}
为了实现这种注入,ibatis需要表明构造函数的各个参数类型,正如上面的xml代码段所示。保证配置参数按照构造函数中参数的顺序进行配置参数类型
属性 | 描述 |
column | 数据库中列名 |
javaType | java类型 |
jdbcType | jdbc类型 遇到空值时需要显示指明 |
typeHandler | 类型转换器 |
association:
<association property="author" column="blog_author_id" javaType=" Author">
<id property="id" column="author_id"/>
<result property="username" column="author_username"/>
association>
association标签定义了实体之间的关联,比如blog拥有一个作者属性,那么这来两个实体之间就可以用上面那个关系来定义。
属性 | 描述 |
property | 列映射的POJO属性 |
column | 关联的列名 |
javaType | java类型 |
jdbcType | jdbc类型 遇到空值时需要显示指明 |
typeHandler | 类型转换器 |
ibatis有两种加载关联关系的方法:
Nested Select:
属性 | 描述 |
select | 另一个加载数据的sql语句id |
例如:
<resultMap id=”blogResult” type=”Blog”>
<association property="author" column="blog_author_id" javaType="Author"
select=”selectAuthor”/>
resultMap>
<select id=”selectBlog” parameterType=”int” resultMap=”blogResult”>
SELECT * FROM BLOG WHERE ID = #{id}
select>
<select id=”selectAuthor” parameterType=”int” resultType="Author">
SELECT * FROM AUTHOR WHERE ID = #{id}
select>
在这里我们有两个select语句,一个去加载blog数据,另一个去加载author数据,在blog定义的resultMap中使用了加载author的select语句,对blog中的author属性进行赋值。也就是说,这种简单的一对一关系,执行了两条select语句。
使用nested select会产生“N+1”问题,非常常见,性能消耗相当大,可以使用懒加载来解决,或者使用下面一种方法。
Nested Result:
属性 | 描述 |
resultMap | 映射关联属性字段的resultMap id值,采用join的方式一次查询得出结果 |
例如:
<resultMap id="blogResult" type="Blog">
<id property=”blog_id” column="id" />
<result property="title" column="blog_title"/>
<association property="author" column="blog_author_id" javaType="Author"
resultMap=”authorResult”/>
resultMap>
<resultMap id="authorResult" type="Author">
<id property="id" column="author_id"/>
<result property="username" column="author_username"/>
<result property="password" column="author_password"/>
<result property="email" column="author_email"/>
<result property="bio" column="author_bio"/>
resultMap>
或者:
<resultMap id="blogResult" type="Blog"> <id property=”blog_id” column="id" /> <result property="title" column="blog_title"/> <association property="author" column="blog_author_id" javaType="Author"> <id property="id" column="author_id"/> <result property="username" column="author_username"/> <result property="password" column="author_password"/> <result property="email" column="author_email"/> <result property="bio" column="author_bio"/> association> resultMap>
在select语句中使用:
<select id="selectBlog" parameterType="int" resultMap="blogResult">
select
B.id as blog_id,
B.title as blog_title,
B.author_id as blog_author_id,
A.id as author_id,
A.username as author_username,
A.password as author_password,
A.email as author_email,
A.bio as author_bio
from Blog B left outer join Author A on B.author_id = A.id
where B.id = #{id}
select>
collection:
<collection property="posts" ofType="domain.blog.Post"> <id property="id" column="post_id"/> <result property="subject" column="post_subject"/> <result property="body" column="post_body"/> collection> collection对应POJO中的集合属性,如: private List posts;
collection元素的关联加载
Nested select for Collection:
<resultMap id=”blogResult” type=”Blog”> <collection property="posts" javaType=”ArrayList” column="blog_id" ofType="Post" select=”selectPostsForBlog”/> resultMap> <select id=”selectBlog” parameterType=”int” resultMap=”blogResult”> SELECT * FROM BLOG WHERE ID = #{id} select> <select id=”selectPostsForBlog” parameterType=”int” resultType="Author"> SELECT * FROM POST WHERE BLOG_ID = #{id} select> Nested results for Collection:
<resultMap id="blogResult" type="Blog"> <id property=”id” column="blog_id" /> <result property="title" column="blog_title"/> <collection property="posts" ofType="Post"> <id property="id" column="post_id"/> <result property="subject" column="post_subject"/> <result property="body" column="post_body"/> collection> resultMap>
<select id="selectBlog" parameterType="int" resultMap="blogResult"> select B.id as blog_id, B.title as blog_title, B.author_id as blog_author_id, P.id as post_id, P.subject as post_subject, P.body as post_body, from Blog B left outer join Post P on B.id = P.blog_id where B.id = #{id} select>
<discriminator javaType="int" column="draft"> <case value="1" resultType="DraftPost"/> discriminator>
有时候我们从数据库中得到的值不仅仅对应一种POJO,可能根据参数不同对应不懂的POJO,如车辆的属性根据卡车、汽车而不同,那么可以使用以下配置
<resultMap id="vehicleResult" type="Vehicle"> <id property=”id” column="id" /> <result property="vin" column="vin"/> <result property="year" column="year"/> <result property="make" column="make"/> <result property="model" column="model"/> <result property="color" column="color"/> <discriminator javaType="int" column="vehicle_type"> <case value="1" resultMap="carResult"/> <case value="2" resultMap="truckResult"/> <case value="3" resultMap="vanResult"/> <case value="4" resultMap="suvResult"/> discriminator> resultMap>
cache:
ibatis中可以使用cache提高效率,默认不开启cache配置,需要使用标签,其默认含义为:
所有select语句返回的数据将被缓存
insert update delete语句将会强制输出缓存
使用LRU算法
没有强制输出的周期(No Flush Interval)
缓存区大小为1024条记录或者对象的引用
可读写的缓存,缓存中的数据可以被调用它的函数修改
也可以手动设置这些配置属性,例如:
<cache
eviction="FIFO"
flushInterval="60000"
size="512"
readOnly="true"/>
上面的配置说明了使用FIFO缓存机制,每60秒输出一次缓存,缓存区大小为512,缓存中的数据是只读的。
缓存过期算法:
LRU 最近最少使用
FIFO 先进先出
SOFT 只有当目前内存不足的情况下,JVM 在垃圾回收时才会收回其包含的引用
WEAK 只要JVM 启动了垃圾回收机制,那么WeakReference 所对应的对象就将被JVM 回收
cache的使用机制和sql Map File的命名空间是绑定的。