CS 580 Client-Server Programming

Spring Semester, 2005

Doc 19 Databases & Architecture

Contents

Databases & Architecture    

Organizing Domain Logic    

Transaction Script    

Domain Model    

Table Module    

Organizing Access to Database    

Table Data Gateway    

Row Data Gateway    

Active Record    

Object-Relational Mapping Layers    

Hibernate Simple Example    

Object Databases    

 

References

 

Patterns of Enterprise Application Architecture, Martin Folwer, Addison-Wesley, 2003

 

CS 683 Lecture document 27 Hibernate Example, Fall 2005, http://www.eli.sdsu.edu/courses/fall04/cs683/notes/hibernate/hibernate.html

 

Copyright ©, All rights reserved. 2005 SDSU & Roger Whitney, 5500 Campanile Drive, San Diego, CA 92182-7700 USA. OpenContent ( http://www.opencontent.org/opl.shtml ) license defines the copyright on this document.

 

 

Databases & Architecture

 

How to keep SQL isolated?

 

How to isolate database connection details?

 

How to keep dealing with the database under control?

 

How to structure programs that use databases?

 

Example – Office Hours

 

Common Operations

 

1. Find Office hours for instructor X

2. Find office hours of any graduate advisor

3. Find office hours of any undergraduate advisor

4. Find office hours of any TA

5. Who has office hours at time X

6. What times are there no office hours

7. Add office hours

8. Modify office hours

 

Tables

 

 

 

 

 

 

 

 

 

 

 

DatabaseConnector

Hides username and password

Can we hide the connections completely?

Should we hide connections?

 

public class DatabaseConnector {

    private String databaseUrl;

    private String user;

    private String password;

    private ArrayList connectionPool;

 

    private static DatabaseConnector instance =

        DatabaseConnector(“filename”);

 

    public static DatabaseConnector instance() {

        return instance;

    }

 

    private DatabaseConnector(String filename) {

        read file for database info

        set private fields

    }

 

    public ResultSet executeQuery( String sql ) {

        return getStatement().executeQuery( sql);

    }

 

    public Statement getStatement() {

        return getConnection().createStatement();

    }

    private Connection getConnection() { return a connection}

    

    etc

}

 

 

 

Organizing Domain Logic

 

How to organize an application that uses a database

 

Fowler provides the following methods

 

9. Transaction Script

10. Domain Model

11. Table Module

12. Service Layer

 

 

 

Transaction Script

 

Identify different transactions to be performed by the application

 

Each transaction is handled by a separate method

 

 

Consequences

 

13. Very simple to implement

14. As application grows in complexity, becomes overly complex and hard to manage

 

 

public class TorrentData {

    public MetaData getFile(String id) {

        code & SQl to get file from database }

 

    public byte[] getPiece(String fileId, int pieceIndex) {

         code & SQl to get piece from database }

 

     public void s etPiece(String fileId, int pieceIndex,

            byte[] peice) {

        code & SQl to put piece into d atabase}

 

    etc.

 

 

Domain Model

 

Implement classes that incorporates both behavior & data

 

Classes represent objects in the domain

 

Program becomes collection of interacting objects

 

Objects map to tables

 

15. A single object may span many tables

16. A table row may contain multiple objects

 

 

Consequences

 

Overly complex for simple applications

 

Scales well to complex applications

 

Database organizes data differently

 

 

Table Module

 

For each table (or view) implement a class

 

Each class holds the business logic related to the data in the table

 

 

Consequences

 

Classes are organized around database structure rather than OO principles

 

Handles more complex situations than Transaction Script

 

Not as scalable as Domain Model

 

 

Organizing Access to Database

 

17. Table Data Gateway

18. Row Data Gateway

19. Active Record

20. Data Mapper

 

 

Table Data Gateway

 

One object handles all the rows in a table or view

 

Each table has one class that knows the table

 

One object represents the table – all the rows

 

Gateway hides all the Sql from the rest of the program

 

 

Works well with

21. Table Module

22. Transaction Script

23. CS580 Spring 2005

    Doc 19, Databases & Architecture Slide # 12

25.  

OfficeHoursGateway

 

public class OfficeHoursGateway {

 

    private static     String addOfficeHoursSql =

            “INSERT

              INTO officeHours ( startTime, endTime, day, facultyId )

              VALUES ( ? , ?, ‘?’, ?)”;

 

    Private static String officeHoursSql =

            “SELECT startTime, endTime, day

              FROM officeHours

              WHERE facultyId = ?”;

 

    public ResultSet officeHoursFor(int facultyId,) {

        Statement hoursStatement = DatabaseConnector.instance().

                prepareStatement(officeHoursSql);

        hoursStatement.setObject( 1, facultyId);

        return hoursStatement.executeQuery();

    }

 

    public int setOfficeHoursFor(int facultyId, Time start, Time end, String day) {

    

        Statement addOfficeHours = DatabaseConnector.instance().

            prepareStatement(addOfficeHoursSql);

        

        addOfficeHours.setObject(1, start);

        addOfficeHours.setObject(2, end);

        addOfficeHours.setObject(3, day);

        addOfficeHours.setObject(4, facutlyId);

        return addOfficeHours.executeQuery();

    }

 

Transaction Script + Table Gateway

 

public class OfficeHoursServer {

    private OfficeHoursGateway officeHours;

    private FacultyGateway faculty;

    etc.

 

    public Vector officeHoursFor(String facultyName) {

 

        int facultyId = faculty.idFor(facultyName,);

 

        ResultSet officeHoursRows = officeHours.officeHoursFor( facultyId);

        Vector officeHours = new Vector();

        while (officeHoursRows.next() ) {

                Dictionary officeHour = new Dictionary();

                officeHour.put( “start”, officeHoursRows.getObject( “start”));

                officeHour.put( “end”, officeHoursRows.getObject( “end”));

                officeHour.put( “day”, officeHoursRows.getObject( “day”));

                officeHours.add( officeHour);

        }

        officeHoursRows.close();

        return officeHours;

    }

 

    etc.

}

 

 

 

Row Data Gateway

 

One object handles or represents a single row in a table or view

 

Each table has one class that knows the table

 

Gateway hides all the Sql from the rest of the program

 

A class provides just accessor methods to data in a row

 

Works well with Transaction script

 

sdsu.sql.DatabaseTable

Utility for Row Access

 

Part of SDSU Java library

 

Some Creation methods

 

Connection db;

db = DriverManager.getConnection( dbUrl, user, password);

 

DatabaseTable rows;

 

//Get rows from table Faculty with column Name = Donald

rows = DatabaseTable.getRow("Faculty", "Name", "Donald", db );

rows.elementAt(rowIndex, "Office");

 

// Get rows returned from a SQL select statment

rows = DatabaseTable.fromSQL("a SQL select", db );

 

 

Active Record

 

Each domain object know how add/remove/find it state in the database

 

In simple cases

 

26. Class for each table

27. An object represents one row in the table

28. Similar to Row Data Gateway with domain logic

 

 

 

Faculty

 

public class Facutly {

    String name;

    String phoneNumber;

    int id;

    etc.

 

    private final static String findByNameSql =

        “SELECT *

          FROM faculty

         WHERE name = ‘?’”;

 

    public static Faculty findByName(String name ) {

            Statement find =

                dabaseConnnector.prepareStatement(findByNameSql);

            find.setObject( 1, name);

            ResultSet facultyRow = find.executeQuery();

            return load(facultyRow);

    }

 

    public static Faculty load( ResultSet facultyRow) {

        create faculty object.        

        get data out of Resultset.

        Put data into faculty object.

        Return faculty object.

    }

 

 

    public boolean hasOfficeHoursAt(Time anHour) {

        Iterator hours = officeHours().iterator();

        while (hours.hasNext() ) {

            OfficeHour officeHour = (OfficeHour) hours.next();

            if (officeHour.contains( anHour) ) return true;

        }

        return false;

    }

 

    public ArrayList officeHours() {

        if( officeHours = nil ) {

            officeHours = OfficeHour.findFor( id );

        }

        return officeHours;

    }

 

 

 

Domain Model + Active Record

 

            

public class OfficeHoursServer {

 

    public Vector officeHoursFor(String facultyName) {

 

        Faculty X = Faculty. findByName (facultyName,);

 

        ArrayList officeHours = X.officeHours();

        

        Convert contents of officeHOurs to XML-RPC acceptable types

        return vector of valid XML-RPC types;

    }

 

    etc.

}

 

 

Object-Relational Mapping Layers

 

Implementing a good object-relational layer is a lot of work

 

Use existing tools to save a lot of time

 

Read/Write objects from tables without SQL

 

Some existing object-relational layers

 

JDO – Java Data Object (Java framework)

TopLink (Commercial - Java)

Hibernate (Open source - Java)

Cayenne (Open source - Java)

GLORP (Open source - Smalltalk)

 

 

 

Hibernate Simple Example

 

Storing Person objects in table

 

Database

Table

 

 

 

SQL Used to Create Table

 

CREATE TABLE PEOPLE

    (FIRST_NAME varchar(50) NULL ,

    LAST_NAME varchar(50) NULL ,

    ID int NOT NULL ,

    PRIMARY KEY  (id));

 

 

People Class

package sample;

 

public class People {

    String firstName;

    String lastName;

    long id;

 

    public People () {super(); }

 

    public People(String first, String last) {

        firstName = first;

        lastName = last;

    }

    

    public String getLastName() { return lastName; }

    

    public String getFirstName() { return firstName; }

    

    public void setFirstName( String name) { firstName = name; }

    

    public void setLastName( String name) { lastName = name; }

    

    public long getId() { return id; }

    

    public void setId(long l) {id = l; }

    

    public String toString() {return firstName + " " + lastName + id;}

}

 

 

 

Mapping – People.hbm.xml

 

Indicates how to map object fields to table columns

 

<?xml version= "1.0" ?>

<!DOCTYPE hibernate-mapping PUBLIC

     "-//Hibernate/Hibernate Mapping DTD//EN"

     " http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd " >

 

<hibernate-mapping package= "sample" >

     <class

         name= "People"

         table= "people" >

         <id

             name= "id"

             type= "java.lang.Long"

             column= "id" >

             <generator class= "assigned" />

         </id>

 

         <property

             name= "lastName"

             column= "last_name"

             type= "string"

             not-null= "false"

             length= "50" />

         <property

             name= "firstName"

             column= "first_name"

             type= "string"

             not-null= "false"

             length= "50" />

     </class>

</hibernate-mapping>

 

 

Sample Connection

 

import lots of stuff;

 

public class Main {

    public static void main(String[] args) throws Exception {

        sampleRead();

        sampleWrite();

    }

 

    static Session getHibernateSession() throws

            MappingException, HibernateException, Exception {

        some code to get HibernateSession

    }

 

    static void sampleWrite() throws

            MappingException, HibernateException, Exception {

        Session session = getHibernateSession();    

        Transaction save = session.beginTransaction();

        People newPerson = new People("Jack", "Frost");

        newPerson.setId(1);

        session.save(newPerson);

        newPerson = new People("Jack", "Ripper");

        newPerson.setId(2);

        session.save(newPerson);

        save.commit();

        session.close();

    }

 

 

Sample Connection Continued

 

    static void sampleRead() throws

            MappingException, HibernateException, Exception {

        Session session = getHibernateSession();    

        Query getByLastName =

            session.createQuery(

                "from People p where p.lastName = :var");

        getByLastName.setString("var", "Frost");

        List result = getByLastName.list();

        System.out.println("Number of Objects: " + result.size());

        People frost = (People) result.get( 0);

        System.out.println(frost);

        session.close();

    }

 

}

 

 

Object Databases

 

29. Stores objects without tables

30. Stores references to objects

 

 

A partial list

 

31. Gemstone ( http://www.gemstone.com/ )

32. Objectivity ( http://www.objectivity.com/ )

33. Matisse ( http://www.matisse.com/ )

34. OmniBase ( http://www.gorisek.com/homepage/index.html )

35. Versant ( http://www.versant.com/index )

36. ObjectStore ( http://www.objectstore.net/index.ssp )

37. Zope Object Database ( http://zope.org/Wikis/ZODB/FrontPage )

 

 

 

No need to convert between objects and sql

 

 

OmniBase Example

 

Create the Database

database :=OmniBase createOn: 'bittorrent'.

 

[OmniBase root

    at: 'files'

    put: Set newPersistent

] evaluateAndCommitIn: database newTransaction.

 

OmniBase root is a Dictionary

 

Entry point to data

 

Adding a new file

Server>>add: aMetaFile

 

    [types := OmniBase root at: 'files'.

    types add: aMetaFile .

    types markDirty] evaluateAndCommitIn: database newTransaction.

 

Finding files by name

Server>>filesWithName: aString

 

    [^(OmniBase root at: 'files')

        select: [:each | aString match: each name ]

        evaluateIn: database newTransaction.