1 / 20

Data Access Layers

Data Access Layers. Technion – Institute of Technology 236700. 1. Overview. We’ll focus on SQL-DAL today. 2. Evolution. 3. Relational DB. SQL stands for Structured Query Language An external Domain Specific Language (DSL) for data retrieval

clarklaura
Download Presentation

Data Access Layers

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. Data Access Layers Technion – Institute of Technology 236700 1

  2. Overview We’ll focus on SQL-DAL today 2

  3. Evolution 3

  4. Relational DB • SQL stands for Structured Query Language • An external Domain Specific Language (DSL) for data retrieval • First published at 1974 by Donald D. Chamberlin & Raymond F. Boyce (IBM) • Data is organized in Tables • A table column is associated to a name and a type • A table row holds the actual data • Table may have relations between them: Students Courses Registration 4

  5. SQL (basic) Queries • The 4 basic queries are: • Select – Retrieves row(s) from a table(s) • Insert – Inserts data into a table(s) • Update – Updates existing row(s) in a table(s) • Delete – Deletes row(s) from a table • Different SQL dialects • Slight differences between implementations • MySQL (Oracle), SQL Server (Microsoft), DB2 (IBM), Derby (Apache), etc. 5

  6. Java Database Connectivity (JDBC) • An SPI developed by Sun Microsystems (rip), as part of JDK 1.1 (1997) • Intends to supply a unified DB access • Strives to make the underlying SQL-DB platform transparent (Implementation-wise) • Not always possible, due to the syntax dialects 6

  7. Java Database Connectivity (JDBC) • Was a good start but: • Developers basically needs to maintain two data models: DB Tables + Code objects • A lot of “translation” code is needed: • Objects to SQL • Queries results to Objects • Abstraction level is very shallow • Complicated error-path • Queries may vary between DB implementations • API induces complicated code ! • Hard to keep it DRY 7

  8. JDBC - Demo • Example • Students / Courses • Registration of students to courses • Take aways • Use try-with-resources block (Using in C#) • Note resources are actually final • ResultSet is a resource! (As well as Connection and Statement) • Still lots of boilerplate / use patterns • Preparing statements • Deciphering the results • Handling exceptions • Be careful of different SQL dialects • Join is hard 8

  9. Things to lookout for • Inheritance • How do we maintain inheritance in Databases? • Objects relations • What happens if an object holds a list of objects? Objects to Relational translation is hard 9

  10. Things to lookout for (cont.) • Entities Equality • How can we say if a row’s entity is the same as a certain Java object? • Pointers cannot be used as entities identifiers in the DB! • Example: Suppose multiple people with the same name exists. how can we tell them apart? • ID is not always available / makes sense • In a purely Java world this is easy: we have pointer to their objects • When DB are involved this is simply not true 10

  11. Spring framework – JDBC template • A set of classes built on-top of JDBC to provide: • Resource creation / release • Better iteration over ResultSet • Parameter resolution from objects • Mapping rows to objects 11

  12. Spring framework – JDBC template • A big step towards the right direction • API is much better • Less error handling • Short & Simple code • Framework does most of the “heavy lifting”: • Parameters from Java Beans • Java objects resolution using custom mappers 12

  13. Spring - Demo 13

  14. Spring framework – JDBC template • This is an example for an API evolution • First version introduced JdbcTemplate • Later, classes like NamedParameterJdbcTemplate wrapped JdbcTemplate but delegated most of the work to it • But still: • Queries may vary between different DB impl. • Manual objects resolution • Entity equality is still a concern 14

  15. Object Relational Mapping • ORM Frameworks will: • Manage the DB structure • Map Classes to Tables • Map Objects to Rows • Maintain at most one object in memory per DB row • We will: • Not worry about Inheritance • Not worry about Entity Equality • Write less code • Write simpler code 15

  16. Hibernate • Provides a declarative way of mapping objects to their corresponding data • Defines an external DSL named HQL, which is similar to SQL, only it deals with objects • Meaning, instead of querying tables for rows, we now query Classes for instances • For example, getting all students born after 1985: dob is a class member of type Date Student is a Java class 16

  17. Hibernate • Simple configuration via Annotations • Specification via XML is also supported • Java Beans defines tables • Can also explicitly define (table/column names) • Subclasses objects extends the tables • Complex objects relations • Objects which points to other objects • Automatically maintains the relational tables • No need to worry about SQL dialects • As powerful as JDBC 17

  18. Hibernate – Entity Equality • Mapped objects now need to declare an ID member • Hibernate resolves row-object identity 18

  19. Hibernate – API • Common actions are part of the API: • Adding an object: • Getting result objects as a list: • Fluent API 19

  20. Hibernate - Demo 20

More Related