4. 使用已映射语句

1 基础

1.1 创建JavaBean

一个可复用组件

1. bean组成
2. bean导航 点记法 x.y.z

输出bean所有特性的名称：

</pre><pre name="code" class="java">import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;

</pre><pre name="code" class="java">public void listPropertyNames(Class c) throws IntrospectionException {
		PropertyDescriptor[] pd;
		pd = Introspector.getBeanInfo(c).getPropertyDescriptors();
		for (int i = 0; i < pd.length; i++) {
			System.out.println(pd[i].getName() + " (" + pd[i].getPropertyType().getName() + ")");
		}
	}

1.2 SqlMap API

SqlMapClient

• queryForObject() 方法，从数据库中获取一条记录
  

■ Object queryForObject(String id, Object parameter) throws SQLException;
■ Object queryForObject(String id, Object parameter, Object result)throws SQLException;   

如果查询返回不止一行，该方法就会抛出异常

• queryForList() 方法

■ List queryForList(String id, Object parameter) throws SQLException;
■ List queryForList(String id, Object parameter, int skip, int max)throws SQLException;    

那么取前10条就是(0,10)，取11~20条就是(10,10)

• queryForMap() 方法

■ Map queryForMap(String id, Object parameter, String key) throws SQLException; Map的键为key，值为对象
■ Map queryForMap(String id, Object parameter, String key, String value) 键为key,值为value
throws SQLException;

1.3 已映射语句的类型

<sql id="select-order">
	select * from order
</sql>
<sql id="select-count">
	select count(*) as value from order
</sql>
<sql id="where-shipped-after-value"><span style="white-space:pre">			</span>// sql 语句片段， 代码片段复用
<![CDATA[
where shipDate > #value:DATE#
]]>
</sql>
<select id="getOrderShippedAfter" resultClass="map">
	<include refid="select-order" />
	<include refid="where-shipped-after-value" />
</select>
<select id="getOrderCountShippedAfter" resultClass="int">
	<include refid="select-count" />
	<include refid="where-shipped-after-value" />
</select>

2. <select>

2.1  使用内联参数（用#做占位符）

第一种方法使用散列（#）符号

<select id="getByIdValue" resultClass="Account">
select
accountId,
username,
password,
firstName,
lastName,
address1,
address2,
city,
state,
postalCode,
country
from Account
where accountId = #value#
</select>

=>

select
accountId,
username,
password,
firstName,
lastName,
address1,
address2,
city,
state,
postalCode,
country
from Account
where accountId = ?

使用如下方式调用

account = (Account) sqlMap.queryForObject(
"Account.getByIdValue",
new Integer(1));

“How do I use LIKE in my WHERE clauses?”

There are three possible solutions to that dilemma:
■ The value of the parameter passed in has to have the SQL wildcard characters
in it.
■ The text to search for has to be part of a SQL expression (e.g., '%' ||
#value# || '%') that can be parameterized.
■ The substitution syntax (which is the next topic, in section 4.2.2) has to be
used instead.

2.2 使用内联参数（用$做占位符）

另一种方式是使用代替（$）语法

<select id="getByLikeCity" resultClass="Account">
	select
	accountId,
	username,
	password,
	firstName,
	lastName,
	address1,
	address2,
	city,
	state,
	postalCode,
	country
	from Account
	where city like '%$value$%'
</select>

调用方式：

accountList = sqlMap.queryForList(
"Account.getByLikeCity",
"burg");

=>

select
accountId,
username,
password,
firstName,
lastName,
address1,
address2,
city,
state,
postalCode,
country
from Account
where city like '%burg%'

2.3 SQL注入

burg'; drop table Account;--

select
accountId,
username,
password,
firstName,
lastName,
address1,
address2,
city,
state,
postalCode,
country
from Account
where city like '%burg';drop table Account;--%'

2.4 自动结果映射

在已映射语句第一次被执行时，迅速地自动创建一个结果映射，然后将它应用于这条已映射语句。

有3种方式来使用这个特征：单列选择，固定多列选择和动态多列选择

动态结果映射：

<select id="getAccountRemapExample" remapResults="true"<span style="white-space:pre">			</span>//当已映射语句执行时重新映射结果
<span style="white-space:pre">	</span>resultClass="java.util.HashMap"><span style="white-space:pre">		</span>
<span style="white-space:pre">	</span>select
<span style="white-space:pre">	</span>accountId,
<span style="white-space:pre">	</span>username,
<span style="white-space:pre">	</span><dynamic>
<span style="white-space:pre">		</span><isEqual property="includePassword" compareValue="true">
<span style="white-space:pre">			</span>password,
<span style="white-space:pre">		</span></isEqual>
<span style="white-space:pre">	</span></dynamic>
<span style="white-space:pre">	</span>firstName,
<span style="white-space:pre">	</span>lastName
<span style="white-space:pre">	</span>from Account
<span style="white-space:pre">	</span><dynamic prepend=" where ">
<span style="white-space:pre">		</span><isNotEmpty property="city">
<span style="white-space:pre">			</span>city like #city#
<span style="white-space:pre">		</span></isNotEmpty>
<span style="white-space:pre">		</span><isNotNull property="accountId" prepend=" and ">
<span style="white-space:pre">			</span>accountId = #accountId#
<span style="white-space:pre">		</span></isNotNull>
<span style="white-space:pre">	</span></dynamic>
</select>

2.5 联接相关数据

ibatis将 SQL语句映射为对象

3. 映射参数

3.1 外部参数映射

3.2 再论内联参数映射

可以

在内联参数映射中提供一些外部参数映射所允许的特性，例如jdbcType，nullValue，只需用冒号将参数名，数据库类型，和空值占位符分隔即可

当数据库允许为空的列时，数据库类型必设，因为使用下面ＪＤＢＣ　ＡＰＩ把空值发送到数据库：

public void setNull(
int parameterIndex,
int sqlType);

<select id="getOrderShippedAfter"
resultClass="java.util.HashMap">
select *
from order
where shipDate > #value:DATE#
</select>

还可以使用name=vaule 语法来设定参数映射，如下与前一示例等价：

<select id="getOrderShippedAfter" resultClass="hashmap">
select *
from "order"
where shipDate > #value,jdbcType=DATE#
</select>

3.3 基本类型参数

java 5 自动装箱

5以前 包装类

3.4 JavaBean参数和Map参数

如果使用bean 创建一个参数映射，并试图引用该bean中实际上不存在的特性，当ibatis加载该参数时，马上报错

如果使用Map，ibatis无法知道该特性是否存在。

4 使用内联结果映射和显示结果映射

4.1 基本类型结果

boolean char byte short int long float double

ibatis允许获得基本类型的 包装结果

Integer count = (Integer)sqlMap.queryForObject(
"Account.getOrderCountByAccount",
new Integer(1));

<select
id="getOrderCountByAccount"
resultClass="java.lang.Integer" >
select count(*) as value
from order
where accountId = #value#
</select>

当使用bean 来获取结果，bean中必须包含基本类型：

public class PrimitiveResult {
private int orderCount;
public int getOrderCount() {
return orderCount;
}
public void setOrderCount(int orderCount) {
this.orderCount = orderCount;
}
}

<resultMap id="primitiveResultMapExample"
class="PrimitiveResult">
<result property="orderCount"
column="orderCount" />
</resultMap>

<select id="getPrimitiveById"
resultMap="primitiveResultMapExample">
select count(*) as orderCount
from order
where accountId = #accountId#
</select>

4.2 javaBean结果和Map结果