iBatis 执行查询,sqlMap.xml中的元素为<select>
SqlMapClient的查询API:
输入用parameterClass或者parameterMap指定,输出用resultMap或者resultClass指定。
一.使用resultClass进行结果映射:
1.使用resultClass,查询结果映射为Map
举例:查询年龄为19的学生信息,输入参数为int,输出参数为HashMap,这里使用queryForObject方法。
JAVA代码:
public static void main(String[] args) throws IOException, SQLException {
String resource = "sqlMapConfig.xml";
Reader reader = Resources.getResourceAsReader(resource);
SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
Map map = (Map) sqlMap.queryForObject("getStudents", 19);
System.out.println(map);
}
sqlMap.xml:
<select id="getStudents" parameterClass="int" resultClass="hashmap">
select * from student where sage=#age#
</select>
注意:如果查询语句中存在特殊符号,比如小于号<,这样会与xml元素冲突而导致程序运行出错,需要使用转义符<代替,当然还有一种更好的方式是使用CDATA段。
不正确的写法:
<select id="getStudentsBean2" parameterClass="int" resultMap="StudentResult">
select Sname,Ssex,sage from student where sage < #age#
</select>
采用CDATA段避免冲突:
<select id="getStudentsBean2" parameterClass="int" resultMap="StudentResult">
<![CDATA[
select Sname,Ssex,sage from student where sage < #age#
]]>
</select>
输出:
{Sage=19, Ssex=女, Sdept=IS, Sname=李四, Sno=95002}
这里查询参数在xml中被包围在##中:select * from student where sage=#age#,
执行sql时相当于这条prepareStatement:
select * from student where sage= ?
然后将age传入。
如果要改为模糊查询,可以修改xml如下:
<select id="getStudents" parameterClass="int" resultClass="hashmap">
select * from student where sage like '%$age$%'
</select>
这时执行的语句相当于Statement:
select * from student where sname like '%$age$%'
但是要小心SQL注入的情况,如果用户输入:
JIM';drop table A--
那么这条语句会变成
select * from student where sname like '%JIM';drop table A--%'
--符号表示忽略后面的%'。这样执行完模糊查询后会删除表A
2.使用resultClass,查询结果映射为基本类型
举例2:查询所有学生,仅返回年龄信息,无输入参数,输出参数为int的List,这里使用queryForList方法。
java:
List<Integer> list = sqlMap.queryForList("getAges", null);
for (int i = 0; i < list.size(); i++) {
System.out.println(list.get(i));
}
xml:
<select id="getAges" resultClass="int">
select sage as value from student
</select>
结果:
17
19
22
20
3.使用resultClass,查询结果映射为JavaBean
xml:
<sqlMap>
<typeAlias alias="StudentBean" type="com.bean.StudentBean"/>
<select id="getStudentsBean" parameterClass="int" resultClass="StudentBean">
select * from student where sage = #age#
</select>
</sqlMap>
StudentBean:
public class StudentBean {
private String sno;
private String sname;
private String ssex;
private String sage;
private String sdept;
public String getSno() {
return sno;
}
public void setSno(String sno) {
this.sno = sno;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getSsex() {
return ssex;
}
public void setSsex(String ssex) {
this.ssex = ssex;
}
public String getSage() {
return sage;
}
public void setSage(String sage) {
this.sage = sage;
}
public String getSdept() {
return sdept;
}
public void setSdept(String sdept) {
this.sdept = sdept;
}
public String toString() {
return "Sno="+sno+" Sname="+sname+" Ssex=" + ssex + " Sage="+sage
+ " Sdept=" + sdept;
}
}
Java:
StudentBean stuBean = (StudentBean) sqlMap.queryForObject("getStudentsBean", 19);
System.out.println(stuBean);
输出:
Sno=95002 Sname=李四 Ssex=女 Sage=19 Sdept=IS
以上3个例子分别展示了resultClass的基本类型,Map和JavaBean的查询结果映射。
二.使用resultMap进行结果映射:
xml:
<sqlMap>
<typeAlias alias="StudentBean" type="com.bean.StudentBean"/>
<resultMap id="StudentResult" class="StudentBean">
<result property="sname" column="Sname" />
<result property="ssex" column="Ssex" />
</resultMap>
<select id="getStudentsBean2" parameterClass="int" resultMap="StudentResult">
select Sname,Ssex from student where sage = #age#
</select>
</sqlMap>
Java:
StudentBean stuBean = (StudentBean) sqlMap.queryForObject("getStudentsBean2", 19);
System.out.println(stuBean);
输出:
Sno=null Sname=李四 Ssex=女 Sage=null Sdept=null