iBATIS测试
一,准备
(1) 下载 ibatis-2.3.0.677。解压之后有
几个文件。其中lib文件夹中含有所需要的jar包,simple_example文件夹包含了一个简单crud操作的事例项目。
(2) 下面演示使用的项目,结构如下
(3) Ibatis配置:
1.导入dtd
其中在SqlMapConfig.xml中有这样的代码
<!DOCTYPE sqlMapConfig
PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
为了提示的方便,通过工具来获得提示:我们将"-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"复制下来,然后打开myeclipse中Windows-->Preferences-->XML Catalog-->add
在 Key Type中选择 URI。
Key为 -//ibatis.apache.org//DTD SQL Map Config 2.0//EN(将上面代码粘贴到此)
Location中选择 FileSystem 找到sql-map-config-2.dtd文件
(3)同上 打开Account.xml中,有
<!DOCTYPE sqlMap
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
添加 sql-map-2.dtd
注:两个dtd文件路径:src\com\ibatis\sqlmap\engine\builder\xml
(4)添加 log4j-1.2.14.jar 以及jdbc驱动需要的 ojdbc14.jar
(5)配置log4j.properties.
内容如下:
log4j.rootLogger=DEBUG, stdout
#log4j.appender.stdout=org.apache.log4j.ConsoleAppender
#log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
#log4j.appender.stdout.layout.ConversionPattern=%c{1} - %m%n
#log4j.logger.java.sql.PreparedStatement=DEBUG
#log4j.rootLogger=ERROR, stdout
# SqlMap logging configuration...
log4j.logger.com.ibatis=DEBUG
log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=DEBUG
log4j.logger.com.ibatis.common.jdbc.ScriptRunner=DEBUG
log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUG
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
二,简单的CRUD操作
项目简介[b1] :
1. 有两个实体类:
Room(班级) 和 Student(学生) 他们之间为 1对多关系。
以下所有信息都会以这个项目为示例来介绍ibatis的基本使用。
2. ibatis需要一个SqlMapConfig.xml和一个或多个Bean.xml(一个实体类对应配置一个bean.xml)(例如这里的Room.xml, Student.xml)。SqlMapConfig.xml是ibatis的配置文件,用来配置数据源、数据连接方式以及实体(javabean)与数据字段映射关系等。Ibatis会加载SqlMapConfig.xml,然后根据配置来反射实体与数据之间的关系,以及数据库的连接池和事务处理等。
3. 在 SqlMapConfig.xml中主要的标签为:
<properties/> 用于properties文件的引入
<settings/>用于配置和优化SqlMapClient实例的各选项
<transactionManager/> 用于配置事务管理服务 属性type有三种:JDBC,JTA和EXTERNAL
<sqlMap>用于引入bean.xml(一个sqlMap标签对应一个bean.xml,这里有两个实体,所以是两个sqlMap标签分别引入Room.xml和Student.xml)
<dataSourc/> 有SIMPLE,DBCP,JNDI三种方式
<sqlMap> 用于引入声明所有的SQL Map配置文件(比如这里的Room.xml, Student.xml)
3.1. SqlMapConfig.xml配置如下:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property name="JDBC.Driver"value="oracle.jdbc.driver.OracleDriver"/>
<property name="JDBC.ConnectionURL" value="jdbc:oracle:thin:@172.17.217.249:1521:ORACLE"/>
<property name="JDBC.Username" value="oms"/>
<property name="JDBC.Password" value="oms"/>
</dataSource>
</transactionManager>
<sqlMap resource="com/isoftstone/dto/Student.xml"/>
<sqlMap resource="com/isoftstone/dto/Room.xml"/>
</sqlMapConfig>[b2]
Student实体类:
Student.java 代码:
private int id;
private String name;
private int age;
private String sex;
private Room room;
数据库表中 标名:student
Student.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="student">
<!-- Use type aliases to avoid typing the full classname every time. -->
<typeAlias alias="Student" type="com.isoftstone.dto.Student"/>
<typeAlias alias="Room" type="com.isoftstone.dto.Room"/>
<!-- Result maps describe the mapping between the columns returned
from a query, and the class properties. A result map isn't
necessary if the columns (or aliases) match to the properties
exactly. -->
<resultMap id="StudentResult" class="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="age" column="sage"/>
<result property="sex" column="sex"/>
<result property="room" column="rid" select="getRoomByRoomId"/>
</resultMap>
<resultMap id="RoomResult" class="Room">
<result property="id" column="rid"/>
<result property="name" column="rname"/>
<result property="buildDate" column="buildDate"/>
<!--<result property="studentList" select="student.selectStudentByRoomId"/>-->
</resultMap>[b3]
<select id="selectAllStudents" resultMap="StudentResult">
<![CDATA[
select *
from student
]]>
</select>
<parameterMap id="procPageMap" class="java.util.HashMap">
<parameter property="iSql" jdbcType="varchar" javaType="String" mode="IN"/>
<parameter property="iStart" jdbcType="NUMERIC" javaType="int" mode="IN"/>
<parameter property="iSize" jdbcType="smallInt" javaType="int" mode="IN"/>
<parameter property="oNote" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet"mode="OUT"resultMap="StudentResult"/>
<parameter property="oResultCount" jdbcType="NUMERIC" javaType="int" mode="OUT"/>[b4]
</parameterMap>
<procedure id="procPage" parameterMap="procPageMap">
<![CDATA[
{call proc_page_separate(?,?,?,?,?)}
]]>
</procedure>
<select id="getRoomByRoomId" resultMap="RoomResult">
<![CDATA[
select *
from room
where rid = #rid#
]]>
</select>
<!-- A simpler select example without the result map. Note the
aliases to match the properties of the target result class. -->
<select id="selectStudentById" parameterClass="int"resultMap="StudentResult" >
<![CDATA[
select *
from student
where sid = #id#
]]>
</select>
<!--<select id="selectStudentByRoomId" parameterClass="int" resultClass="Student">
select sid as id, sname as name, sage as age, sex as sex from student where rid = #room.id#
</select>
-->
<select id="orderQuery" parameterClass="String" resultClass="Student">
<![CDATA[
select sid as id, sname as name, sage as age, sex as sex
from student
order by $name$
]]>
</select>
<select id="dynamicQuery" parameterClass="Student" resultClass="Student">
<![CDATA[
select sid as id, sname as name, sage as age, sex as sex
from student
]]>
<dynamic prepend="where" open="(" close=")">
<isNotEmpty property="name" prepend="and">
sname like '%'|| #name# ||'%'
</isNotEmpty>
<isGreaterThan property="age" compareValue="18" prepend="and">
sage = #age#
</isGreaterThan>
</dynamic>
</select>
<select id="blurQuery" parameterClass="String" resultMap="StudentResult">
<![CDATA[
select *
from student
where sname like '%'|| #name# ||'%'
]]>
<!--
sname like '%$name$%'
sname like '%'|| #name# ||'%' 推荐使用后一种 避免sql注入
-->[b5]
</select>
<!-- Insert example, using the Account parameter class -->
<insert id="addStudent" parameterClass="Student">
<selectKey keyProperty="id" resultClass="int">
select teststu.nextval from dual
</selectKey>
<![CDATA[
insert into student ( sid, sname, sage, sex, rid )
values ( #id#, #name#, #age#, #sex#, #room.id#)
]]>
</insert>
<!-- Update example, using the Account parameter class -->
<update id="updateStudent" parameterClass="Student">
<![CDATA[
update student
set sname = #name#,sage = #age#,sex = #sex#
where sid = #id#
]]>
</update>
<!-- Delete example, using an integer as the parameter class -->
<delete id="deleteStudentById" parameterClass="int">
<![CDATA[
delete from student
where sid = #id#
]]>
</delete>
</sqlMap>
在Student.xml中主要标签:
<typeAlias> 用于避免每次写完整类名
通过<typeAlias alias="Student" type="com.isoftstone.dto.Student"/>
下面的返回(resultMap标签)均可以用Student代替com.isoftstone.dto.Student
<resultMap>用于表中字段与类名中的属性不一致
例如:<resultMap id="StudentResult" class="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="age" column="sage"/>
<result property="sex" column="sex"/>
<result property="room" column="rid" select="getRoomByRoomId"/>
</resultMap>
这样表中的sid与类中的id就相对应了。
注:(1)下面sql语句代码均用<![CDATA[
<!-- 各种sql代码-->
]]>
包含,作用是为了避免sql语句中的<,>等等与xml的标签相冲突。
(2)resultMap,resultClass的使用:比如按照编号来查询Student,表中字段与类名中的属性不一致,则使用resultMap,否则可以用resultClass.例如student表中字段为sid 而在Student.java中为private int id;需要sid转换为id[b6]
(1)添加(自动生成的主键 )
步骤:(1).建立oracle需要的自增长的teststu(sequence名称[b7] )
(2).在Student.xml中使用insert标签:首先通过<selectKey>来获得下一个[b8] sequence的值,然后向表中添加一条记录.
(3)在StudentService.java 中,使用sqlMapper.insert("addStudent", student);其中insert()方法的第一个参数是Student.xml中insert标签的id.
在Student.xml中为:
<insert id="addStudent" parameterClass="Student">
<selectKey keyProperty="id" resultClass="int">
select teststu.nextval
from dual
</selectKey>
<![CDATA[
insert into student ( sid, sname, sage, sex, rid )
values ( #id#, #name#, #age#, #sex#, #room.id#)
]]>
</insert>
1.
其中teststu为sequence,脚本如下:
create sequence TESTSTU
minvalue 1
maxvalue 1000
start with 1
increment by 1
cache 20
cycle;
在StudentService.java中代码为:
private static SqlMapClient sqlMapper;[b9]
static {
try {
Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");
sqlMapper = SqlMapClientBuilder.buildSqlMapClient(reader);
reader.close();
} catch (IOException e) {
throw new RuntimeException("Something bad happened while building the SqlMapClient instance." + e, e);
}
}
//添加部分代码
public static Object addStudent(Student student) throws SQLException {
return sqlMapper.insert("addStudent", student);
}
其中 <insert>标签中的id需与“sqlMapper.insert("addStudent", student)”中的第一个参数相同,“addStudent”就是在<insert>标签中id的名字。
//测试代码
Student student = new Student();//添加学生
student.setAge(12);
student.setName("s1");
student.setSex("female");
try
{
System.out.println(StudentService.addStudent(student));
}
catch (SQLException e)
{
e.printStackTrace();
}
从打印结果得知:返回的结果为当前添加成功的编号(id).
(2)修改
在Student.xml中为:
<update id="updateStudent" parameterClass="Student">
<![CDATA[
update student
set sname = #name#,sage = #age#,sex = #sex#
where sid = #id#
]]>
</update>
在StudentService.java中代码为:
//update修改部分代码
public static int updateStudent(Student student) throws SQLException {
return sqlMapper.update("updateStudent", student);
}
//测试代码
Student student = new Student();
student.setId(251);
student.setAge(20);
student.setName("s6");
student.setSex("male");
try
{
System.out.println(StudentService.updateStudent(student));
}
catch (SQLException e)
{
e.printStackTrace();
}
返回的结果为 改变数据库中的记录数(这里为1)
(3)查询
1.查询所有
在Student.xml中为:
<select id="selectAllStudents" resultMap="StudentResult">
select *
from student
</select>
在StudentService.java中:
@SuppressWarnings("unchecked")
public static List<Student> selectAllStudents() throws SQLException
{
return sqlMapper.queryForList("selectAllStudents");
}
返回结果为 查询到的学生列表list
2.查询单个(根据主键查询单个)
在Student.xml中为:
<select id="selectStudentById"parameterClass="int" resultMap="StudentResult" >
<![CDATA[
select *
from student
where sid = #id#
]]>
</select>
在StudentService.java中:
public static Student selectStudentById(int id) throws SQLException {
return (Student)sqlMapper.queryForObject("selectStudentById", id);
}
返回结果为 查询到的学生对象
3.模糊查询(like)//这里以sname作为条件
在Student.xml中为:
<select id="blurQuery"parameterClass="String"resultMap="StudentResult">
<![CDATA[
select *
from student
where sname like '%$name$%'
]]>
<!--
sname like '%$name$%'
sname like '%'|| #name# ||'%' 推荐使用后一种避免sql注入
--></select>
在StudentService.java中:
@SuppressWarnings("unchecked")
public static List<Student> selectStudentBlur(String name) throws SQLException
{
return (List<Student>)sqlMapper.queryForList("blurQuery", name);
}
测试代码:
try {
List<Student> list = StudentService.selectStudentBlur("m");
for (Student student : list)
{
System.out.println(student);
}
}
catch (SQLException e)
{
e.printStackTrace();
}
返回结果为 查询到的学生列表list
注:(1)模糊查询有两种方式:sname like '%$name$%' 以及
sname like '%'|| #name# ||'%'
推荐使用后一种。原因:
使用前一种(sname like '%$name$%'),log4j中打印信息:
DEBUG [main] - {conn-100000} Connection
DEBUG [main] - {conn-100000} Preparing Statement: select * from student where sname like '%m%'
DEBUG [main] - {pstm-100001} Executing Statement: select * from student where sname like '%m%'
DEBUG [main] - {pstm-100001} Parameters: []
使用后一种(sname like '%'|| #name# ||'%'),log4j中打印信息:
DEBUG [main] - {conn-100000} Preparing Statement: select * from student where sname like '%'|| ? ||'%'
DEBUG [main] - {pstm-100001} Executing Statement: select * from student where sname like '%'|| ? ||'%'
DEBUG [main] - {pstm-100001} Parameters: [m]
很容易得知:使用后一种,可以有效的避免sql注入。
4.排序(order by)//这里根据name属性来排序
在Student.xml中为:
<select id="orderQuery" parameterClass="String" resultClass="Student">
<![CDATA[
select sid as id, sname as name, sage as age, sex as sex
from student
order by $name$
]]>
</select>
在StudentService.java中:
@SuppressWarnings("unchecked")
public static List<Student> selectStudentOrder(String orderStr) throws SQLException
{
return (List<Student>)sqlMapper.queryForList("orderQuery", orderStr);
}
测试代码:
try {
String orderStr = "name";
List<Student> list = StudentService.selectStudentOrder(orderStr);
for (Student student : list)
{
System.out.println(student);
}
}
catch (SQLException e)
{
e.printStackTrace();
}
返回结果为 查询到的学生列表list(根据name属性来升序排列)
注:(1)此时log4j中sql代码:
DEBUG [main] - {conn-100000} Preparing Statement: select sid as id, sname as name, sage as age, sex as sex from student order by name
DEBUG [main] - {pstm-100001} Executing Statement: select sid as id, sname as name, sage as age, sex as sex from student order by name
DEBUG [main] - {pstm-100001} Parameters: []
因此要是把 String orderStr = "aaa";则log4j中sql代码
DEBUG [main] - {conn-100000} Preparing Statement: select sid as id, sname as name, sage as age, sex as sex from student order by aaa
DEBUG [main] - {pstm-100001} Executing Statement: select sid as id, sname as name, sage as age, sex as sex from student order by aaa
DEBUG [main] - {pstm-100001} Parameters: []
Cause: java.sql.SQLException: ORA-00904: "AAA": invalid identifier
但要是把 Student.xml中sql代码改为//(String orderStr = "name")
select sid as id, sname as name, sage as age, sex as sex from student order by $aaaaaaa$
则log4j中sql代码打印:
DEBUG [main] - {conn-100000} Preparing Statement: select sid as id, sname as name, sage as age, sex as sex from student order by name
DEBUG [main] - {pstm-100001} Executing Statement: select sid as id, sname as name, sage as age, sex as sex from student order by name
DEBUG [main] - {pstm-100001} Parameters: []
结论1: $aaaaaaa$只是相当于一个占位符,传入什么,就是神马;
(2)要是把Student.xml中sql代码改为(把$该为了#)
select sid as id, sname as name, sage as age, sex as sex from student order by #name#
此时不会排序
执行sql代码为
select sid as id, sname as name, sage as age, sex as sex from student order by ‘name’
结论2:#总是把传入的数据当成字符串,而$会原样输出
总结(#与$的区别)
(1)#总是把传入的数据当成字符串,如#field#传入的是id,则sql语句会生成
order by ‘id’
(2)$传入的数据直接生成在sql中,如#field#传入的是id,则sql语句会生成
order by id
(3) #方法能够防止sql注入,而$不能(通过上面模糊查询的log4j可以看出)
(4) $一般用于传入数据库对象,如表名
(5)一般能用#的就不用$
5.动态查询(dynamic)
在Student.xml中为:
<select id="dynamicQuery" parameterClass="Student" resultClass="Student">
<![CDATA[
select sid as id, sname as name, sage as age, sex as sex
from student
]]>
<dynamic prepend="where" open="(" close=")">
<isNotEmpty property="name" prepend="and">
sname like '%'|| #name# ||'%'
</isNotEmpty>
<isGreaterThan property="age" compareValue="18" prepend="and">
sage = #age#
</isGreaterThan>
</dynamic>
</select>
在StudentService.java中:
@SuppressWarnings("unchecked")
public static List<Student> selectStudentDynamic(Student student) throws SQLException
{
return (List<Student>)sqlMapper.queryForList("dynamicQuery", student);
}
测试代码:
Student student = new Student();
student.setName("m");
student.setAge(19);
try {
StudentService.selectStudentDynamic(student);
} catch (SQLException e) {
e.printStackTrace();
}
Log4j打印语句:
DEBUG [main] - {conn-100000} Preparing Statement: select sid as id, sname as name, sage as age, sex as sex from student where( sname like '%'|| ? ||'%' and sage = ? )
DEBUG [main] - {pstm-100001} Executing Statement: select sid as id, sname as name, sage as age, sex as sex from student where( sname like '%'|| ? ||'%' and sage = ? )
DEBUG [main] - {pstm-100001} Parameters: [m, 19]
返回结果为 查询到的学生对象。
注:(1)在Student.xml动态查询中,property为实体类的属性(这里property="name"),ibatis会自动判定是否需要追加prepend前缀,比如这里的name不为空,则会省略prepend="and"。
(2)ibatis中isNull,isEmpty以及isNotNull,isNotEmpty的区别:
isNull用于判断参数是否为null,
而isEmpty判断参数是否为null或者 空,满足一个条件即成立。(check to see if the value of a collection ,String or String.valueOf() property is null or empty("" or size() < 1)).
(4)删除(根据主键删除)
在Student.xml中为:
<delete id="deleteStudentById" parameterClass="int">
<![CDATA[
delete from student
where sid = #id#
]]>
</delete>
StudentService.java代码为:
public static int deleteStudent(int id) throws SQLException
{
return sqlMapper.delete("deleteStudentById", id);
}
注:删除成功,返回的对象为改变的记录数(此处返回值为1)
三,存储过程 多表查询 事务管理
(1)调用存储过程
例:分页存储过程(proc_page_separate) 代码:
create or replace procedure proc_page_separate
(
/*
查询结果进行分页
create by: chengaoyun
输入参数:pSql
输入参数: pStart
输入参数: pSize
输出参数:oNote
输出参数:oResultCount
*/
iSql in varchar2,
iStart in number,
iSize in number,
oNote out oms_pak.OMS_CUS,
oResultCount out number
)
as
v_sql varchar2(3000);
v_countSql varchar2(2000);
--v_count number;
BEGIN
-- 判断必须从不等于0时开始查询
if (iStart <= 0 and iSize <= 0) then
v_sql := 'select *
from (select rownum rn, t.*
from (' || iSql || ') t )';
elsif (iStart <= 0) then
v_sql := 'select *
from (select rownum rn, t.*
from (' || iSql || ') t )
where rn between 1 and ' ||iSize;
elsif (iSize <= 0) then
v_sql := 'select *
from (select rownum rn, t.*
from (' || iSql || ') t )
where rn >= ' || iStart;
else
v_sql := 'select *
from (select rownum rn, t.*
from (' || iSql || ') t )
where rn between ' || iStart || ' and ' || (iStart + iSize - 1);
end if;
v_countSql := 'select count(*) oResultCount from (' || iSql || ')';
execute immediate v_countSql into oResultCount;
--oResultCount := v_count;
open oNote for v_sql;
exception
when others then
rollback;
end proc_page_separate;
/
这里对student表测验
Student.xml 代码:
<parameterMap id="procPageMap" class="java.util.HashMap">
<parameter property="iSql" jdbcType="varchar" javaType="String" mode="IN"/>
<parameter property="iStart" jdbcType="NUMERIC" javaType="int" mode="IN"/>
<parameter property="iSize" jdbcType="NUMERIC" javaType="int" mode="IN"/>
<parameter property="oNote" jdbcType="ORACLECURSOR"
javaType="java.sql.ResultSet" mode="OUT" resultMap="StudentResult"/>
<parameter property="oResultCount" jdbcType="NUMERIC" javaType="int" mode="OUT"/>
</parameterMap>
<procedure id="procPage" parameterMap="procPageMap">
{call proc_page_separate(?,?,?,?,?)}
</procedure>
studentService.java中代码为:
@SuppressWarnings("unchecked")
public static void procStudent(String iSql, int iStart, int iSize) throws SQLException
{
Map<String,Object> pageMap = new HashMap<String,Object>();
pageMap.put("iSql", iSql);
pageMap.put("iStart", iStart);
pageMap.put("iSize", iSize);
sqlMapper.queryForObject("procPage", pageMap);
List<Student> studentList = (List<Student>) pageMap.get("oNote");
for (Student student : studentList)
{
System.out.println(student);
}
int result = (Integer)pageMap.get("oResultCount");
System.out.println("total result is : " + result);
}
测试代码为:
try {
StudentService.procStudent("select * from student", 0, 0);// 调用存储过程
}
catch (SQLException e)
{
e.printStackTrace();
}
说明:(1)在student.xml中,<procedure parameterMap="procPageMap">
传入的参数是map集合,因此需在java代码中封装map对象;
(2)存储过程中,输入参数用 mode="IN",输出参数用 mode="OUT"
(3)需注意在bean.xml(例如student.xml)中
(4)javaType:
1.返回是游标时:javaType可以是“java.sql.ResultSet”.也可以是 “cursor”或者“CURSOR”
(5)jdbcType:
1.对于输入参数:数字对应可以为 int,numeric,smallInt.并且不区分大 小写;String可以对应varchar,varchar2.同样不区分大小写
2.对于作为输出参数:数字类型只能是NUMERIC,且区分大小写。
游标类型只能是ORACLECURSOR,且区分大小写。
(2)多表查询
添加一张表room,dto中的为
public class Room implements Serializable{
private static final long serialVersionUID = -6565897867137991793L;
private int id;
private String name;
private Date buildDate;
private List<Student> studentList;
//省略了get,set方法
@Override
public String toString() {
return "Room [buildDate=" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(buildDate) + ", id=" + id + ", name=" + name
+ ", studentList=" + studentList + "]";
}
表中为:
其中room与student为一对多关系 。
例:通过room编号来查询room以及当前room关联的student
在Room.xml中为:
代码段2:<resultMap id="RoomResult" class="Room">
<result property="id" column="rid"/>
<result property="name" column="rname"/>
<result property="buildDate"column="buildDate" javaType="java.util.Date" jdbcType="Date"/>
<result property="studentList" select="selectStudentByRoomId" column="rid"/>
</resultMap>
代码段3:<select id="selectStudentByRoomId" parameterClass="int" resultClass="Student">
<![CDATA[
select sid as id, sname as name, sage as age, sex as sex
from student
where rid = #id#
]]>
</select>
代码段1: <select id="selectRoomById" parameterClass="int" resultMap="RoomResult">
<![CDATA[
select rid, rname, buildDate
from Room
where rid = #id#
]]>
</select>
在Room与Student的关系中,存在多表查询,比如查询一个班级中所有的学生:
首先配置代码段1(通过班级编号来获得room表中的基本信息:rid, rname, buildDate);
返回为代码段2,通过代码段2中<Result>中的select属性,来获得当前room编号所关联的student(代码段3).
RoomService.java代码为:
public static Room selectRoomById(int id) throws SQLException
{
return (Room)sqlMapper.queryForObject("selectRoomById", id);
}
测试代码:
Room room = RoomService.selectRoomById(2);
System.out.println(room);
Log4j打印信息:
DEBUG [main] - {conn-100000} Preparing Statement: select rid, rname, buildDate from Room where rid = ?
DEBUG [main] - {pstm-100001} Executing Statement: select rid, rname, buildDate from Room where rid = ?
DEBUG [main] - {pstm-100001} Parameters: [2]
DEBUG [main] - {pstm-100001} Types: [java.lang.Integer]
DEBUG [main] - {rset-100002} ResultSet
DEBUG [main] - {conn-100000} Preparing Statement: select sid as id, sname as name, sage as age, sex as sex from student where rid = ?
DEBUG [main] - {pstm-100003} Executing Statement: select sid as id, sname as name, sage as age, sex as sex from student where rid = ?
DEBUG [main] - {pstm-100003} Parameters: [2]
DEBUG [main] - {pstm-100003} Types: [java.lang.Integer]
DEBUG [main] - {rset-100004} ResultSet
DEBUG [main] - {rset-100004} Header: [ID, NAME, AGE, SEX]
DEBUG [main] - {rset-100004} Result: [22, aa, 10, male]
DEBUG [main] - {rset-100004} Result: [24, bb, 30, female]
DEBUG [main] - {rset-100004} Result: [25, dd, 22, male]
DEBUG [main] - {rset-100004} Result: [26, cc, 25, female]
DEBUG [main] - {rset-100004} Result: [28, am, 50, female]
DEBUG [main] - {rset-100002} Header: [rid, rname, buildDate, rid]
DEBUG [main] - {rset-100002} Result: [2, room2, 2011-06-20 09:21:00.0, 2]
DEBUG [main] - Returned connection 24093812 to pool.
Room [buildDate=2011-06-20 09:21:00, id=2, name=room2, studentList=[Student : age = 10 , id= 22 , name= aa , sex= male, Student : age = 30 , id= 24 , name= bb , sex= female, Student : age = 22 , id= 25 , name= dd , sex= male, Student : age = 25 , id= 26 , name= cc , sex= female, Student : age = 50 , id= 28 , name= am , sex= female]]
(3)事务管理
说明:(1).在 AutoCommit为true的情况下,JDBC会对我们的操作进行自动提交,此时,每
个JDBC操作都是一个独立的任务。
(2).为了实现整体事务的原子性,我们需要将AutoCommit 设为false,并结合
Connection.commit/rollback方法进行事务的提交/回滚操作。
(3).如果代码没有显式的调用SqlMapClient.startTransaction()方法,则ibatis
会将当前的数据库操作视为自动提交模式(AutoCommit=true).
studentService.java中代码为:
public static void transactionManager()
{
try {
sqlMapper.startTransaction();
Student stu1 = new Student();
stu1.setId(22);
stu1.setAge(22);
stu1.setName("stu1");
stu1.setSex("male");
sqlMapper.update("updateStudent", stu1);
/*try
{
int i = 1/0;
}
catch (Exception e)
{
e.printStackTrace();
}*/
Student stu2 = new Student();
stu2.setId(23);
stu2.setName("stu2");
stu2.setAge(23);
stu2.setSex("female");
sqlMapper.update("updateStudent", stu2);
sqlMapper.commitTransaction();
}
catch (SQLException e)
{
try
{
sqlMapper.endTransaction();
}
catch (SQLException e1)
{
e1.printStackTrace();
}
}
}
而要是把注释部分打开,则会作为一个完整的事物回滚。
将项目代码以及文件上传到配置库!小心别漏传文件
修改记录:把批注修改好之后就上传.
引用的代码段,要用不同于说明的字体颜色
此处排版太乱,请修改
此处排版太乱,请修改
此处的说明不够详细,容易引起误解!应该解释成:在like语句中,最好以##将变量引入,然后再举例说明。
如果将需要重点说明的配置copy出来在下面重点说明它的使用,我觉得更好。
注:此处只是xml文件的全部内容,sql注入问题会在模糊查询中详细说明.
描述不够详细
注:已修改
这个字符串代表什么,是sequence的名字吗?如果是写清楚
注:已修改
怎么能是获得当前sequence的值呢?应该是下一个的值!
注:已修改
为什么要将SqlMapClient放在StudentService.java文件中?SqlMapClient在RoomService中就用不到了?
注:每一个*Service.java中都有一个SqlMapClient ,来进行crud等等操作