Enterprise Java Development@TOPIC@
An Introduction to DAOs, DB Schema, and SQL
Built on: 2014-03-06 23:57 EST
Copyright © 2014 jim stafford (jcstaff@apl.jhu.edu)
Abstract
This presentation provides and introduction to the Data Access Object (DAO) pattern to encapsulate access to data, database schema used to design data organization in RDBMS systems, and SQL to access the data.
At the completion of this topic, the student shall
have an understanding of:
The role played by a DAO and business tier as it relates to accessing data
Core concepts to relational tables
How to define database tables and relationships using RDBMS schema
How to access data within the database using SQL
be able to:
Design the interface for a DAO
Create a schema in the database
Implement a basic DAO using EntityManager and native SQL commands
Interfaces to data sources vary
Relational Database Management Systems (RDBMS)
NoSQL Solutions
Flat Files
Backend Systems
Even standard RDBMS/SQL interfaces can vary
Many components within application need access to data
Interfaces to data vary by technology and vendor
Least common denominator option for portability may not be feasible in all cases
May make use of vendor extensions
Impact of unique interfaces significant when exposed to many components and component types
Components need more abstraction and shielding from the details of the persistent store
Use a Data Access Object (DAO) to abstract and encapsulate access to business objects in the data source
Business LogicObject within the business domain that needs access to data (e.g., session bean)
Knows when/why data is needed, but not how
Data Access ObjectAbstracts the access details from the business object
Knows where/how data is accessed, but not when/why
Business Object (Entity)An entity within the business logic
Encapsulates information and data business rules within application
A data carrier of information to/from the DAO
Data SourcePhysically stores the data (e.g., database)
Key point: hide details from business logic and other interfacing components
Centralizes All Data Access into a Separate Layer
Easier to maintain
Enables Transparency
Access to implementation details hidden within DAO
Enables Easier Migration
Client layers encapsulated from changes
Reduces Code Complexity in Business Logic
No details, such as SQL, in business logic
Harder to abstract with EJB2.x Container Managed Persistence (CMP) frameworks
EJB3 Java Persistence API provides a significant amount of abstraction
Technology agnostic and business object-focused
No mention of Connection or EntityManager in methods
Ability to at least CRUD (with possible options)
Aggregate data functions added when behavior better performed at data source
Extensions added to address data access details for specific use cases (e.g., LAZY/EAGER load)
...
public interface BookDAO {
Book create(Book book) throws PersistenceException;
Book update(Book book) throws PersistenceException;
Book get(long id) throws PersistenceException;
void remove(Book book) throws PersistenceException;
List<Book> findAll(int start, int count) throws PersistenceException;
}
Note
The declaration of the unchecked/runtime exception PersistenceException is not required and is only being done here for extra clarity
Runtime ExceptionsUsed to report unexpected issues (e.g., no connection)
Extends java.lang.RuntimeException
ex. javax.persistence.PersistenceException
Checked ExceptionsUsed to report anticipated errors mostly have to do with input
Extends java.lang.Exception
Adds implementation out-of-band from DAO interface
...
public class JPABookDAOImpl implements BookDAO {
private EntityManager em;
public void setEntityManager(EntityManager em) {
this.em = em;
}
@Override
public Book create(Book book) { ... }
@Override
public Book update(Book book) { ... }
@Override
public Book get(long id) { ... }
@Override
public void remove(Book book) { ... }
@Override
public List<Book> findAll(int offset, int limit) { ... }
}
Demonstrates how technology-neutral DAO clients can be when dao implementation is injected into client.
...
public class BookDAOTestBase {
protected BookDAO dao; //sub-classes will provide an implementation.
protected Book makeBook() {
Random random = new Random();
Book book = new Book();
book.setTitle("GWW-" + random.nextInt());
...
return book;
}
@Test
public void testCreate() {
Book book = makeBook();
assertEquals("id not assigned", 0, book.getId());
book = dao.create(book);
assertTrue("id not assigned", book.getId()>0);
}
@Test
public void testGet() {... }
@Test
public void testUpdate() { ... }
@Test
public void testDelete() { ... }
@Test
public void testFindAll() { ... }
}
Business Objects represent too much information or behavior to transfer to remote client
Client may get information they don't need
Client may get information they can't handle
Client may get information they are not authorized to use
Client may get too much information/behavior to be useful (e.g., entire database serialized to client)
Some clients are local and can share object references with business logic
Handling specifics of remote clients outside of core scope of business logic
Layer a Remote Facade over Business Logic
Remote Facade constructs Data Transfer Objects (DTOs) from Business Objects that are appropriate for remote client view
Remote Facade uses DTOs to construct or locate Business Objects to communicate with Business Logic
Data Transfer ObjectRepresents a subset of the state of the application at a point in time
Not dependent on Business Objects or server-side technologies
Doing so would require sending Business Objects to client
XML and JSON provide the “ultimate isolation” in DTO implementation/isolation
Remote FacadeUses Business Logic to perform core business logic
Layered on top of Business Logic to translate between Business Objects and DTOs
Business LogicContinues to perform core duties as described in DAO Pattern
Business Object (Entity)Continues to perform core duties as described in DAO Pattern
May have more server-side-specific logic when DTOs are present in the design
Clients only get what they need
Clients only get what they understand
Clients only get what they are authorized to use
Remote and Local interfaces to services are different
Makes it harder to provide location transparency
Lightweight Business Objects can be used as DTOs
Remote Facade must make sure they are “pruned” of excess related items before transferring to client
Remote Facade must make sure they are “cleaned” of DAO persistence classes before transferring to client
4.1.1. Background[1]
DBMS based on a relational model
Introduced by E. F. Codd in 1970s
Some challenges by other forms but still remains a standard for corporate data stores
TableGroup of columns
Represents a type
Commonly mapped to a Java class
ColumnSingle piece of data
Represents a property
Commonly mapped to a Java class attribute
Sample (H2) Column Types[2]
INTEGER
DECIMAL
TIME
DATE
TIMESTAMP
VARCHAR
BLOB
CLOB
NOT NULLRow cannot exist without this column value supplied
UNIQUENo other row may have a column with this value
FOREIGN KEYIf supplied, must reference matching column(s) of existing row
Foreign Key JoinColumn within child table references parent
Primary Key JoinForeign key column within child table is child's primary key column. Parent and child table primary keys must match.
Link Table JoinForeign keys to parent/child expressed in separate table
Foreign keys may be defined in child table or link table
Link tables can always be used (at an extra cost) no matter the cardinality
Foreign keys cannot be defined on "one side" of a one-to-many relationship
Link table must be used if foreign key cannot be placed on many side
Link tables must be used in many-to-many relationships
Used to manipulate schema in RDBMS
create table JPADAO_AUTHOR (
ID integer generated by default as identity,
FIRST_NAME varchar(16) not null,
LAST_NAME varchar(32) not null,
primary key (ID)
);
create table JPADAO_BOOK (
ID bigint generated by default as identity,
DESCRIPTION varchar(1000),
PAGES integer,
TITLE varchar(32) not null,
AUTHOR_ID integer,
primary key (ID)
);alter table JPADAO_BOOK
add constraint JPADAO_BOOK_AUTHOR_FK
foreign key (AUTHOR_ID)
references JPADAO_AUTHORcreate index JPADAO_BOOK_AUTHOR_FKX on JPADAO_BOOK(AUTHOR_ID); create unique index JPADAO_BOOK_TITLE_IDX on JPADAO_BOOK(TITLE);
src/main/resources/
`-- ddl
|-- book-create.ddl
|-- book-drop.ddl
|-- book-tuningadd.ddl
`-- book-tuningremove.ddl
target/classes/
`-- ddl
|-- book-create.ddl
|-- book-drop.ddl
|-- book-tuningadd.ddl
`-- book-tuningremove.ddl
insert
into
JPADAO_BOOK
(ID, DESCRIPTION, PAGES, TITLE)
values
(null, 'this and that', 1037, 'gww')Get the generated primary key value
call IDENTITY()
public class JDBCBookDAOImpl implements BookDAO {
private Connection connection;
public void setConnection(Connection connection) {
this.connection = connection;
}
@Override
public Book create(Book book) throws PersistenceException {
PreparedStatement statement=null;
ResultSet rs = null;
try {
statement=connection.prepareStatement(
"insert into JPADAO_BOOK (ID, DESCRIPTION, PAGES, TITLE) " +
"values (null, ?, ?, ?)");
statement.setString(1, book.getDescription());
statement.setInt(2, book.getPages());
statement.setString(3, book.getTitle());
statement.execute();
statement.close();
Field id = Book.class.getDeclaredField("id");
id.setAccessible(true);
statement = connection.prepareStatement("call identity()");
rs = statement.executeQuery();
if (rs.next()) {
id.set(book, rs.getLong(1));
}
return book;
} catch (SQLException ex) {
throw new PersistenceException("SQL error creating book", ex);
} catch (NoSuchFieldException ex) {
throw new RuntimeException("Error locating id field", ex);
} catch (SecurityException ex) {
throw new RuntimeException("Security error setting id", ex);
} catch (IllegalArgumentException ex) {
throw new RuntimeException("Error setting id", ex);
} catch (IllegalAccessException ex) {
throw new RuntimeException("Access error setting id", ex);
} finally {
try { rs.close(); } catch (Exception ex){}
try { statement.close(); } catch (Exception ex){}
}
}
...
}
public class JPANativeSQLBookDAO implements BookDAO {
private EntityManager em;
public void setEntityManager(EntityManager em) {
this.em = em;
}
@Override
public Book create(Book book) throws PersistenceException {
em.createNativeQuery(
"insert into JPADAO_BOOK (ID, DESCRIPTION, PAGES, TITLE) " +
"values (null, ?1, ?2, ?3)")
.setParameter(1, book.getDescription())
.setParameter(2, book.getPages())
.setParameter(3, book.getTitle())
.executeUpdate();
int idVal = ((Number)em.createNativeQuery("call identity()")
.getSingleResult()).intValue();
try {
Field id = Book.class.getDeclaredField("id");
id.setAccessible(true);
id.set(book, idVal);
} catch (Exception ex) {
throw new RuntimeException("Error setting id", ex);
}
return book;
}
...
}