iBATIS Framework Overview and Comparison

1. iBATIS培训

2. 培训内容 • iBATIS框架介绍 • iBATIS与其他框架的比较 • iBATIS高级话题 • iBATIS实例　

3. iBATIS简介 • iBATIS是一个持久层框架(下图红色框部分)

4. iBATIS简介 • iBATIS是Clinton Begin开发，现在由APACHE基金会支持的用于加快JDBC编程的经过泛化的框架，是一个持久化框架。 • 当前支持JAVA、.NET、RUBY三种语言 • iBATIS官方站点 http://ibatis.apache.org/ • iBATIS allows you to map relational data to primitives, maps, XML, and userdefined classes (e.g., JavaBeans).

5. iBATIS框架介绍 • 什么是iBATIS? 先看一个例子:

6. iBATIS框架介绍 实例： A sample SQL mapping descriptor <select id="getAddress" parameterClass="int" resultClass="Address"> SELECT ADR_ID as id, ADR_DESCRIPTION as description, ADR_STREET as street, ADR_CITY as city, ADR_PROVINCE as province, ADR_POSTAL_CODE as postalCode FROM ADDRESS WHERE ADR_ID = #id# </select> Java Code: Address address = (Address) sqlMap.queryForObject("getAddress",new Integer(5)); .net Code: Address address = (Address) sqlMap.QueryForObject("getAddress", 5);

7. 相应的JDBC代码 • public Employee getEmployee (int id) throws SQLException { • Employee employee = null; • String sql = "SELECT * FROM EMPLOYEE " + • "WHERE EMPLOYEE_NUMBER = ?"; • Connection conn = null; • PreparedStatement ps = null; • ResultSet rs = null; • try { • conn = dataSource.getConnection(); • ps = conn.prepareStatement(sql); • ps.setInt(1, id); • rs = ps.executeQuery(); • employee = null;

8. 相应的JDBC代码 • while (rs.next()) { • employee = new Employee(); • employee.setId(rs.getInt("ID")); • employee.setEmployeeNumber(rs.getInt("EMPLOYEE_NUMBER")); • employee.setFirstName(rs.getString("FIRST_NAME")); • employee.setLastName(rs.getString("LAST_NAME")); • employee.setTitle(rs.getString("TITLE")); • } • } finally { • try { • if (rs != null) rs.close(); • } finally { • try { • if (ps != null) ps.close(); • } finally { • if (conn != null) conn.close(); • } • } • } • return employee; • }

9. iBATIS框架介绍 • 从上面的实例，结论: • iBATIS可以简化JDBC和ADO.NET的开发工作。

10. iBATIS框架介绍 iBATIS在小型、简单的系统中的应用 小型系统的特点： 1、单个数据库。 2、相对简单的用户接口和域模型。 3、业务相对简单，能常都是CRUD((Create, Read, Update, Delete)。 iBATIS 适合小型应用的原因： 1、本身简单，易学，没有太高的学习成本。 2、对现有设计不施加任何影响。 3、可以很好适合系统的升级。

11. iBATIS框架介绍 iBATIS在大型系统中的应用 1、因为它对数据库设计、行为、依赖没有做任何假设，甚至可以数据库有一个挑战性的设计或者被政治因素所包围。 2、iBATIS 具备的特性允许它为大型数据集高效地工作 iBATIS 具备的特性允许你处理大数据时使用batch,同样也允许你只从大范围数据集中提取你绝对需要的立即数据。driver hints 3、大型系统，经常白天进行一些事务功能，而晚上进行批量功能。 iBATIS允许你同一类有多种映射方法以确保每个功都被支持而且尽可能以最高的效率。比如，你可以选择延迟加载。

12. 小结 iBATIS可以和任何大小的数据库工作得很好。两点原因: 首先，他工作得很好和小型应用数据库， 因为它简单而且容易学习。 其次,它在大型企业应用中工作得很好。因为它对数据库设计、行为、依赖没有做任何假设，甚至可以数据库有一个挑战性的设计或者被政治因素所包围。iBATIS被设计得足够灵活去处理各种情况。

13. iBATIS与其他框架的比较 为什么要使用iBATIS？ 1、简单 iBATIS被普遍认为是最简单的持久层框架。(团队目标) 工作方式更象JDBC，只是更少的代码。 iBATIS提供了更多JDBC代码不具备的框架级的益处。 iBATIS对于DBA来说，非常容易理解，配置文件可以被任何有SQL编程经验的人来理解。 2、生产效率 Fabrizio Giannesch found that iBATIS reduced the amount of code in the persistence layer by a significant 62 percent。 3、性能表现 1) iBATIS提高了大量的可选项来优化性能 2) iBATIS可以做到和JDBC一样的性能，甚至更好。何况JDBC多数情况被编 写得很糟糕 4、关注分离(Separation of concerns) 1)提供了一组接口和API来实现持久层的独立。 2)使用iBATIS ，总是使用 Object来编程， 而不再是result sets

14. iBATIS与其他框架的比较 ５、分工协作 DBA 不允许别人编写SQL来访问数据 开发团队人中有为擅长写SQL,而不是Java或C# 性能调优，DBA　：Show me the SQL.

15. iBATIS与其他框架的比较 1.JDBC(Lower-level) flexible and complete feature set they are harder andmore tedious to use. 2.object/relational mapping tools（High-level） save you a lot of work • they are built with more • assumptions and constraints that make them applicable to fewer applications. 3.iBATIS(Middle-level)

16. iBATIS和其他框架的比较 1.什么时候不用iBATIS? If you are guaranteed to have full control of your application design and database design, you have a good reason to use a full object/relational mapping solution such as Hibernate.

17. iBATIS的未来 • iBATIS 加入了Apache Software Foundation换言之, Apache license 不象其他开源软件的license那样有严格的限制，比如 GPL. • 更简单，更小，更少的依赖 • 更多的扩展和更多支持的插件 • 支持更多的语言和平台

18. iBATIS快速入门 • DDL: • # • # Table structure for table 'user' • # • CREATE TABLE USER_ACCOUNT ( • USERID INT(3) NOT NULL AUTO_INCREMENT, • USERNAME VARCHAR(10) NOT NULL, • PASSSWORD VARCHAR(30) NOT NULL, • GROUPNAME VARCHAR(10), • PRIMARY KEY (USERID) • ); • # • # Data for table 'user' • # • INSERT INTO USER_ACCOUNT (USERNAME, PASSSWORD, GROUPNAME) • VALUES ('LMEADORS', 'PICKLE', 'EMPLOYEE'); • INSERT INTO USER_ACCOUNT (USERNAME, PASSSWORD, GROUPNAME) • VALUES ('JDOE', 'TEST', 'EMPLOYEE'); • COMMIT;

19. iBATIS快速入门 • <?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="com.mysql.jdbc.Driver"/> • <property name="JDBC.ConnectionURL" value="jdbc:mysql://localhost/test"/> • <property name="JDBC.Username" value="root"/> • <property name="JDBC.Password" value="blah" /> • </dataSource> • </transactionManager> • <sqlMap resource="SqlMap.xml" /> • </sqlMapConfig>

20. iBATIS快速入门 • <?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> • <select id="getAllUsers" parameterClass="string" • resultClass="hashmap"> • SELECT * FROM USER_ACCOUNT WHERE GROUPNAME = #groupName# • </select> • </sqlMap>

21. iBATIS快速入门 • code: • import com.ibatis.sqlmap.client.*; • import com.ibatis.common.resources.Resources; • import java.io.Reader; • import java.util.List; • public class Main { • public static void main(String arg[]) throws Exception { • String resource = "SqlMapConfig.xml"; • Reader reader = Resources.getResourceAsReader (resource); • SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader); • List list = sqlMap.queryForList("getAllUsers", "EMPLOYEE"); • System.out.println("Selected " + list.size() + " records."); • for(int i = 0; i < list.size(); i++) { • System.out.println(list.get(i)); • } • } • }

22. Lazy Loading • Bytecode enhancement for lazy loading • However, if you have 1,000 customers who each have 1,000 orders with 25 line items, the combined data would consist of 25,000,000 objects.Needless to say, this has grown to a point where it is not feasible to have it all in memory at once. • So, in the previous example, you could reconfigure the SQL map to load the • related lists lazily. Therefore, when your user is looking at the list of customers, only • the list of 1,000 customers is in memory. The information to load the other lists is • kept available, but the data is not loaded until it is actually requested. In other • words, the order information is not loaded until the user clicks on a customer to • see that customer’s orders. At that point, the framework loads that customer’s list • of 1,000 orders; none of the others are loaded. If the user then clicks on an order • to drill down more, only the 25 line items on the selected order are loaded. • So, by making a configuration change and not changing a single line of code, • we have gone from 25,000,000 objects to 2,025. This means our application runs • in about one ten-thousandth of the time as it did in the original configuration.

23. Connection Pool • Jakarta Commons Database Connection Pool(DBCP)

24. Distributed caching • Caching data in a multiuser environment can be tricky. Caching data in a multiserverenvironment makes the multiuser environment look simple. • To deal with this problem, iBATIS provides an implementation of caching thatuses the OpenSymphony cache (OSCache). OSCache can be configured to clusteracross multiple servers to provide scalability(可量测的) and fail-over(故障切换) support.

25. SQL injection • 什么是SQL injection ? select * from product where id = 5. select * from product where id=5 or 1=1 select * from product where id=5; delete from orders • 但是,iBATIS 通过使用 PreparedStatements 避免了这种攻击.

26. SQL injection 只有直接使用清楚的替换语法才有风险 动态表名和动态列名: SELECT * FROM $TABLE_NAME$ WHERE $COLUMN_NAME$ = #value# SQL injection 提示：动态构造有风险

27. The SqlMap API for nonquery SQL statements 增加: Object insert(String id, Object parameterObject)throws SQLException; 更新: int update(String id, ObjeparameterObject) throws SQLException; 删除: int delete(String id, Object parameterObject)throws SQLException;

28. Autogenerated keys • Autogenerated keys API: Object insert (String id, Objecparameter Object) throws SQLException;

29. Autogenerated keys • Oracle • 映射文件: • <insert id="insert"> • <selectKey • keyProperty="accountId" • resultClass="int"> • SELECT nextVal('account_accountid_seq') • </selectKey> • INSERT INTO Account ( • accountId, username, password • ) VALUES( • #accountId#, #username#, #password#) • </insert> • －－－－－－－－－－－－－－－－－－ • Code: • Integer returnValue = (Integer) sqlMap.insert( • "Account.insert", account)

30. Autogenerated keys SQLServer <insert id="insert"> INSERT INTO Account ( username, password ) VALUES( #username#, #password#) <selectKey keyProperty="accountId" resultClass="int"> SELECT SCOPE_IDENTITY() </selectKey> </insert>

31. Handling concurrent updates • You can use one of several techniques to handle concurrent updates, such as using a timestamp or version number on rows in the database • 按照DDL创建如下表:

32. Handling concurrent updates • CREATE TABLE account ( • accountid serial NOT NULL, • username varchar(10), • passwd varchar(10), • firstname varchar(30), • lastname varchar(30), • address1 varchar(30), • address2 varchar(30), • city varchar(30), • state varchar(5), • postalcode varchar(10), • country varchar(5), • version int8, • CONSTRAINT account_pkey PRIMARY KEY (accountid) • ) • While the insert method returns an object, both the update and delete methods return a primitive integer value (or, an int value to be more correct) which indicates how many records were updated or deleted by the mapped statement.

33. Updating or deleting child records • public void saveOrder(SqlMapClient sqlMapClient, Order order) • throws SQLException { • if (null == order.getOrderId()) { • sqlMapClient.insert("Order.insert", order); • } else { • sqlMapClient.update("Order.update", order); • } • sqlMapClient.delete("Order.deleteDetails", order); • for(int i=0;i<order.getOrderItems().size();i++) { • OrderItem oi = (OrderItem) order.getOrderItems().get(i); • oi.setOrderId(order.getOrderId()); • sqlMapClient.insert("OrderItem.insert", oi); • } • }

34. Updating or deleting child records • public void saveOrder(SqlMapClient sqlMapClient, Order order) • throws SQLException { • sqlMapClient.startTransaction(); • try { • if (null == order.getOrderId()) { • sqlMapClient.insert("Order.insert", order); • } else { • sqlMapClient.update("Order.update", order); • } • sqlMapClient.startBatch(); • sqlMapClient.delete("Order.deleteDetails", order); • for (int i=0;i<order.getOrderItems().size();i++) { • OrderItem oi = (OrderItem) order.getOrderItems().get(i); • oi.setOrderId(order.getOrderId()); • sqlMapClient.insert("OrderItem.insert", oi); • } • sqlMapClient.executeBatch(); • sqlMapClient.commitTransaction(); • } finally { • sqlMapClient.endTransaction(); • } • }

35. Working with stored proceduresand function • because they are platform specific • CREATE OR REPLACE FUNCTION max_in_example • (a float4, b float4) • RETURNS float4 AS • $BODY$ • BEGIN • if (a > b) then • return a; • else • return b; • end if; • END; • $BODY$ • LANGUAGE 'plpgsql' VOLATILE;

36. 使用function • <parameterMap id="pm_in_example" class="java.util.Map"> • <parameter property="a" /> • <parameter property="b" /> • </parameterMap> • <procedure id="in_example" parameterMap="pm_in_example" • resultClass="int" > • { call max_in_example(?, ?) } • </procedure>

37. 使用function • Map m = new HashMap(2); • m.put("a", new Integer(7)); • m.put("b", new Integer(5)); • Integer val = • (Integer)sqlMap.queryForObject("Account.in_example", m);

38. stored procedures • create or replace procedure maximum • (a in integer, b in integer, c out integer) as • begin • if (a > b) then c := a; end if; • if (b >= a) then c := b; end if; • end;

39. stored procedures • <parameterMap id="maxOutProcedureMap" class="java.util.Map"> • <parameter property="a" mode="IN" /> • <parameter property="b" mode="IN" /> • <parameter property="c" mode="OUT" /> • </parameterMap> • <procedure id="maxOutProcedure" • parameterMap="maxOutProcedureMap"> • { call maximum (?, ?, ?) } • </procedure> • // Call maximum function • Map m = new HashMap(2); • m.put("a", new Integer(7)); • m.put("b", new Integer(5)); • sqlMap.queryForObject("Account.maxOutProcedure", m); • // m.get("c") should be 7 now.

40. XML parameters <parameter><accountId>3</accountId></parameter> <select id="getByXmlId" resultClass="Account" parameterClass="xml"> select accountId,username,password,firstName,lastName,address1, address2,city,state,postalCode,country from Account where accountId = #accountId# </select> code: String parameter = "<parameter><accountId>3</accountId></parameter>"; Account account = (Account) sqlMapClient.queryForObject( "Account.getByXmlId",parameter)

41. XML results <select id="getByIdValueXml" resultClass="xml" xmlResultName="account"> select accountId, username, password from Account where accountId = #value# </select> String xmlData = (String) sqlMap.queryForObject( "Account.getByIdValueXml", new Integer(1));

42. XML results <?xml version="1.0" encoding="UTF-8"?> <account> <accountid>1</accountid> <username>lmeadors</username> <password>blah</password> </account>

43. transaction Example:

44. transaction iBATIS supports: ■ Automatic—For simple, single statements that don’t require an explicitly demarcated transaction. ■ Local—A simple, narrowly scoped transaction involving many statements but only a single database. Transactions ■ Global—A complex, broadly scoped transaction involving many statements and many databases or potentially other transaction capable resources such as JMS (Java Messaging Service) queues or JCA (J2EE Connector Architecture) connections. ■ Custom—iBATIS supports user-provided connections for which you can managethe transactions however you like.

45. Atomicity • public void runStatementsUsingAutomaticTransactions() • { • SqlMapClient sqlMapClient = • SqlMapClientConfig.getSqlMapClient(); • Person p = (Person) • sqlMapClient.queryForObject("getPerson", • new Integer(9)); • Transactions • p.setLastName("Smith"); • sqlMapClient.update("updatePerson", p); • }

46. Local transactions <transactionManager type="JDBC"> <dataSource type="SIMPLE"> <property …/> <property …/> <property …/> </dataSource> </transactionManager>

47. Local transactions • public void runStatementsUsingLocalTransactions() { • SqlMapClient sqlMapClient = • SqlMapClientConfig.getSqlMapClient(); • try { • sqlMapClient.startTransaction(); • Person p = • (Person)sqlMapClient.queryForObject • ("getPerson", new Integer(9)); • p.setLastName("Smith"); • sqlMapClient.update("updatePerson", p); • Department d = • (Department)sqlMapClient.queryForObject • ("getDept", new Integer(3)); • p.setDepartment(d); • sqlMapClient.update("updatePersonDept", p); • sqlMapClient.commitTransaction(); • } finally { • sqlMapClient.endTransaction(); • } • }

48. Global transactions

49. Global transactions • <transactionManager type="JTA"> • <property name="UserTransaction" • value="java:/ctx/con/someUserTransaction"/> • <dataSource type="JNDI"> • <property name="DataSource" • value="java:comp/env/jdbc/someDataSource"/> • </dataSource> • </transactionManager> • <transactionManager type="EXTERNAL"> • <dataSource type="JNDI"> • <property name="DataSource" • value="java:comp/env/jdbc/someDataSource"/> • </dataSource> • </transactionManager>

50. Global transactions Code:与local一样 1.方便ibatis管理事务 2.可以自由地local 事务和global 事务之间自由切换