1 / 52

Clinton R. Begin cbegin@apache

Clinton R. Begin cbegin@apache.org. Dealing with Enterprise Database Challenges. Featuring the Apache iBATIS Data Mapping Framework Clinton Begin cbegin@thoughtworks.com. Demo…. Just move data from here to there. Scope. The Challenges

uriah-reilly
Download Presentation

Clinton R. Begin cbegin@apache

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Clinton R. Begin cbegin@apache.org

  2. Dealing with Enterprise Database Challenges Featuring the Apache iBATIS Data Mapping Framework Clinton Begin cbegin@thoughtworks.com

  3. Demo… Just move data from here to there.

  4. Scope • The Challenges • Ownership, access, complexity, normalization, skinny design. • iBATIS Data Mapping Framework • Introduction, SQL Mapping defined, examples. • SQL • Is it still relevant? • iBATIS is a hybrid solution • Features, qualities, competition, other solutions.

  5. The Challenges “Welcome, to the real world.” – go for the red pill

  6. Challenges of Politics • Database ownership and control • Change management • Cost allocation – who pays for changes? • No “developer” access to design • Proprietary/3rd Party – legal issues • Agile methods vs. legacy methods

  7. iBATIS isolates the data model, the SQL, the work and the responsibility. In doing so, it isolates much of the politics surrounding the enterprise database as well.

  8. Challenges of Imperfection • Denormalized models • Super-normalized models • Modeled Value Entities • Thin data models (rows vs. columns) • Implicit relationships • Overcomplicated relationships • Null Values (?)

  9. Thin data model

  10. iBATIS works even when the data model does not map to the object model.

  11. Challenges of Legacy and complexity • Too many tables to map • Encoded Fields • No primary keys • Hierarchical • ERP systems • Temporal databases • “600 tables” – moderate size (?)

  12. iBATIS allows you to build modern applications on top of legacy databases by allowing you more freedom to define and tune your mappings that deal with “unique” databases.

  13. Challenges of Technology • Distributed transactions • Distributed caching • Vendor specific database features • Multiple databases w/ single object model

  14. iBATIS supports advanced enterprise features and allows you to take full advantage of the investment you’ve made in your relational database management system.

  15. An Introduction to Apache iBATIS SQL Mapping defined…

  16. SQL Mapping Concept

  17. SQL as a Black Box SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEE WHERE EMPLOYEE_NUMBER = ‘1234’ OUTPUT OUTPUT INPUT

  18. SQL Mapping • Maps objects to SQL statements • NOT Classes to Tables • Fully functional SQL via named statements • NOT generated SQL (although that’s possible) For example…

  19. Order Product LineItem CLASSES Order Product id : int total : BigDecimal pst : BigDecimal gst : BigDeciml lineItems : Collection date : Date LineItem id : int name : String description : String cost : BigDecimal retail : BigDeciml id : int product : Product order : Order cost : BigDecimal retail : BigDecimal Mapping Layer TABLES

  20. Order Product LineItem CLASSES Order Product id : int total : BigDecimal pst : BigDecimal gst : BigDeciml lineItems : Collection date : Date LineItem id : int name : String description : String cost : BigDecimal retail : BigDeciml id : int product : Product order : Order cost : BigDecimal retail : BigDecimal Mapping Layer SQL TABLES

  21. The Product Table

  22. The Product Class public class Product { private int id; private String name; private String description; private BigDecimal cost; private BigDecimal retail; //…getters/setters implied }

  23. The SQL <select id=“getProduct" parameterClass=“int”resultClass="examples.domain.Product"> SELECT PRODUCT_ID as id, NAME, DESCRIPTION, COST, RETAIL, FROM PRODUCT WHERE PRODUCT_ID = #id# </select>

  24. Simple Configuration String resource = “SqlMapConfig.xml”; Reader reader = Resources.getResourceAsReader (resource); SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);

  25. Executing the Query Product product = (Product) sqlMap.queryForObject (“getProduct”, 5);

  26. …Audience: OMG! Did you just hand code SQL?Clinton: Yes.…

  27. SQL Is it still relevant?

  28. SQL • Structured Query Language • Introduced in 1973 by IBM • Based on relational model of 1970 • Based on sound mathematical principles • Significant industry investment • Has withstood the test of time • Nothing else has ever come close

  29. “SQL is much more than a simple data update and retrieval mechanism. SQL's query processing can perform many tasks. By hiding SQL, application developers are excluding a powerful tool.” http://www.martinfowler.com/articles/dblogic.html

  30. Writing SQL is NOT a Sin!

  31. Is SQL what we want to avoid? SELECT * FROM EMPLOYEE WHERE EMPLOYEE_NUMBER = 99

  32. Or is it JDBC? employee.setFirstName (rs.getString("EMP_FIRST_NAME")); employee.setLastName (rs.getString("EMP_LAST_NAME")); employee.setTitle (rs.getString("EMP_TITLE")); } } finally { try { if (rs != null) rs.close(); } finally { try { if (ps != null) ps.close(); } finally { if (conn != null) conn.close(); } } } return employee; } 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; while (rs.next()) { employee = new Employee(); employee.setId (rs.getInt("EMP_ID")); employee.setEmployeeNumber (rs.getInt("EMP_NUMBER"));

  33. iBATIS is a hybrid solution Qualities, features and competition.

  34. Options… • Stored procedures • Inline SQL • Dynamic SQL • Object Relational Mapping •  iBATIS IS A HYBRID!

  35. A Hybrid? • Full support for stored procedures • SQL is written just like inline SQL • “Inline SQL for XML” • Advanced dynamic SQL definition features • A big problem even for the best ORM tools • Shares many features with ORM • Caching, lazy loading, join mapping, bytecode enhancement etc.

  36. Apache iBATIS… • Is tolerant of complex/bad database designs • Isolates the data model • Separates concerns • Divides labor • Saves time • Reduces code

  37. Advanced iBATIS Features • Supports all types • Objects, Primitives, Arrays, Collections • Caching (use case vs. holistic) • Lazy Loading or Join Mapping (1:1, 1:M, M:N) • Bytecode enhancement (ifaces AND classes) • XML parameters and results • Transaction Management (Local/Global)

  38. But yes…SQL Coding is Required • Many people consider this an advantage • Many others eventually realize it’s required • Few people consider it a problem • SQL can be done fast and done well • Use good tools (there are plenty) • Generation is an option (but not a best practice) • The SQL may already exist (consider app rewrites or ports)

  39. Other “SQL Mapper-Like” Tools • JDBC PreparedStatement • Embedded SQL as a language feature • Pro*C, Forte TOOL, SQLJ • Spring framework • Voruta • SQLC (iBATIS inspired?) • O/R Broker (iBATIS inspired) • Mr. Persister (iBATIS inspired) • Aximol SQL Library (iBATIS inspired)

  40. iBATIS 3 Years ago • JPetStore posted on TSS • iBATIS noticed in the persistence layer • Architectural reviewer said: “Use Torque” • Why not just use ORM for everything? • What kept it going…?

  41. What drives open source? Frustration / Anger / Need Confidence / Ego Pride Duty Community

  42. iBATIS Today • ~10,000 users • ~1.2 Million DTD hits per month from tools • Apache Software Foundation • 12 Developers • Java, .NET, Ruby

  43. Audience Response Questions?

  44. More simple examples… To inspire questions or support answers.

  45. <select id="getProduct" parameterClass=“int” resultClass=“com.domain.Product"> select PRODUCT_ID as id, NAME as name, DESCN as description, CATEGORY as category from PRODUCT where PRODUCTID = #id# </select>

  46. <resultMap id="productResult“ class="com.domain.Product"> <result property="id" column="PRODUCTID"/> <result property="name" column="NAME"/> <result property="description" column="DESCN"/> <result property="categoryId" column="CATEGORY"/></resultMap> <select id="getProduct“ parameterClass=“int” resultMap="productResult"> select * from PRODUCT where PRODUCTID = #id# </select>

  47. <parameterMap id="productParameter“ class=“int"> <parameter property=“id"/></parameterMap> <select id="getProduct“ parameterMap=“productParameter” resultClass=“com.domain.Product“> select PRODUCT_ID as id, NAME as name, DESCN as description, CATEGORY as category from PRODUCT where PRODUCTID = ? </select>

  48. <statement id=“insertProduct“ parameterClass=“com.domain.Product”> insert into PRODUCT values (#id#,#name#,#description#,#category#) </statement> <statement id=“updateProduct“ parameterClass=“com.domain.Product”> update PRODUCT set NAME = #name#,DESC = #description#,CATEGORY=#category# where PRODUCT_ID = #id# </statement> <statement id=“deleteProduct“ parameterClass=“com.domain.Product”> delete PRODUCT where PRODUCT_ID = #id# </statement>

  49. Simple Query Product product =(Product) sqlMap.queryForObject (“getProduct”, 23);

  50. Insert, Update, Delete Product product = new Product(); product.setId(324); product.setName(“Shih Tzu”); Product.setDescription(“Some longer description.”); product.setCategory(“DOG”); sqlMap.insert(“insertProduct”, product); product.setCategory(“CAT”); sqlMap.update(“updateProduct”, product); product.delete(“deleteProduct”, product);

More Related