cs 157b: database management systems ii february 4 class meeting department of computer science san...

CS 157B: Database Management Systems IIFebruary 4 Class Meeting

Department of Computer ScienceSan Jose State University

Spring 2013Instructor: Ron Mak

www.cs.sjsu.edu/~mak

Department of Computer ScienceSpring 2013: January 30

CS 157B: Database Management Systems II© R. Mak

2

Referential Integrity

Referential integrity is database-speak for “No dangling pointers.” In other words, a foreign key value should always

be a valid reference to a record in the target table(or the key value should be null)._



3

Maintaining Referential Integrity

Recall our one-to-one table association:

The Teacher table has a foreign-key constraint. Field contact_id should always reference a valid record in the

Contact_Info table, or the field should be null._

Id Last First Contact_id

7003 Rogers Tom 207

7008 Thompson Art 190

7012 Lane John 458

7051 Flynn Mabel 856

Id Email_address

458 [email protected]




Teacher Contact_Info



4



7003 Rogers Tom 207


7012 Lane John 458


Id Email_address





CREATE TABLE `teacher` ( Ìd` int(10) unsigned NOT NULL auto_increment, `Last` varchar(45) NOT NULL, `First` varchar(45) NOT NULL, `contact_id` int(10) unsigned NOT NULL, PRIMARY KEY (Ìd`), KEY `FK_teacher_1` (`contact_id`), FOREIGN KEY (`contact_id`) REFERENCES `contact_info` (ìd`) )

MySQL




5


You cannot insert a new Teacher record whose contact_id value does not match a Contact_Info.id value.

You cannot change a Teacher.contact_id value to a value that does not match a Contact_Info.id value.

You cannot change a Contact_Info.id value to a value that does not match a Teacher.contact_id value.

You cannot delete a Contact_Info record without updating the Teacher table.


7003 Rogers Tom 207


7012 Lane John 458


Id Email_address






Demo



6


Update a Contact_Info.id value the corresponding Teacher.contact_info value is updated automatically.

Delete a Contact_Info record the corresponding Teacher record is deleted. What happens to Contact_Info if you delete a Teacher record?

CREATE TABLE `teacher` ( Ìd` int(10) unsigned NOT NULL auto_increment, `Last` varchar(45) NOT NULL, `First` varchar(45) NOT NULL, `contact_id` int(10) unsigned NOT NULL, PRIMARY KEY (Ìd`), KEY `FK_teacher_1` (`contact_id`), FOREIGN KEY (`contact_id`) REFERENCES `contact_info` (ìd`) ON DELETE CASCADE ON UPDATE CASCADE )

cascade policy Also: ON DELETE SET NULL

Demo



7

Hibernate Cascade Types

@Entitypublic class Student { ...

private ContactInfo contactInfo; @OneToOne(cascade=CascadeType.ALL, fetch=FetchType.LAZY) @JoinColumn(name="contact_id") public ContactInfo getContactInfo() { return contactInfo; } public void setContactInfo(ContactInfo info) { this.contactInfo = info; }

...}



8

Hibernate Cascade Types

CascadeType.PERSIST Tell a session to save() a Teacher object, and

the corresponding ContactInfo object is saved automatically. CascadeType.REMOVE

Tell a session to delete() a ContactInfo object, and the corresponding Teacher object is deleted automatically.

CascadeType.REFRESH Tell a session to refresh() a Teacher object, and

the corresponding ContactInfo object is refreshed automatically. CascadeType.MERGE

Tell a session to merge() a Teacher object, andthe corresponding ContactInfo object is refreshed automatically.

CascadeType.ALL Do all of the above.

_



9

Hibernate Reminders

Hibernate accesses the underlying database within the context of a session.

Two common errors: Attempting to access a Java object whose

corresponding database record has not yet be fetched, likely due to lazy fetching. Attach the object to a new session

to force Hibernate to fetch the record.

Taking a Java object already attached to a session and attempting to attach it to another session. Did you forget to close the first session?

_



10

One-to-Many / Many-to-One Associations

The Teacher table has a one-to-many association with the Class table.

The Class table has a many-to-one association with the Teacher table._

Id Last First

7003 Rogers Tom

7008 Thompson Art

7012 Lane John

7051 Flynn Mabel

Code Teacher_id Subject

908 7008 Data structures

926 7003 Java programming

931 7051 Compilers

951 7012 Software engineering

974 7012 Operating systems

Teacher Class



11

Teacher.java

@Entitypublic class Teacher { ... private List<Klass> klasses; ... @OneToMany(mappedBy="teacher", targetEntity=Klass.class, cascade=CascadeType.ALL, fetch=FetchType.EAGER) public List<Klass> getKlasses() { return klasses; } public void setKlasses(List<Klass> klasses) { this.klasses = klasses; } ...}



12

Klass.java

@Entity@Table(name="Class")public class Klass{ ... private Teacher teacher; ... @ManyToOne @JoinColumn(name="teacher_id") public Teacher getTeacher() { return teacher; } public void setTeacher(Teacher teacher) { this.teacher = teacher; } ...}



13

Klass.java

public static void load() { Session session = HibernateContext.getSession(); Teacher rogers = Teacher.find("Rogers"); Teacher thompson = Teacher.find("Thompson"); Teacher lane = Teacher.find("Lane"); Teacher flynn = Teacher.find("Flynn"); Klass java = new Klass("Java programming"); java.setTeacher(rogers);

Klass ds = new Klass("Data structures"); ds.setTeacher(thompson);

Klass se = new Klass("Software engineering"); se.setTeacher(lane); Klass os = new Klass("Operating systems"); os.setTeacher(lane);

Klass compilers = new Klass("Compilers"); compilers.setTeacher(flynn); ... }

Find each teacher.

Create classes andassign teachers to them.



14

Klass.java

public static void load() { ... Transaction tx = session.beginTransaction(); { session.save(java); session.save(ds); session.save(se); session.save(os); session.save(compilers); } tx.commit(); session.close(); System.out.println("Class table loaded."); }

Persist the objects.

SchoolDemo4



15

Many-to-Many Association

Id Last First

1001 Doe John

1005 Novak Tim

1009 Klein Leslie

1014 Jane Mary

1021 Smith Kim

Code Teacher_id Subject

908 7008 Data structures

926 7003 Java programming

931 7051 Compilers

951 7012 Software engineering

974 7012 Operating systems

Student_id Class_code

1001 926

1001 951

1001 908

1005 974

1005 908

1014 931

1021 926

1021 974

1021 931

Student Class

Student_Class

Class

Student

Student-Class



16

Student.java

@Entitypublic class Student { ... private List<Klass> klasses = new ArrayList<Klass>(); ... @ManyToMany @JoinTable(name="Student_Class", joinColumns={@JoinColumn(name="student_id")}, inverseJoinColumns={@JoinColumn(name="class_code")}) public List<Klass> getKlasses() { return klasses; } public void setKlasses(List<Klass> klasses) { this.klasses = klasses; } ...}

Hibernate will automatically create andpopulate the Student_Class join table.


1001 926

1001 951

... ...

Student_Class



17

Klass.java@Entity@Table(name="Class")public class Klass{ ... private List<Student> students = new ArrayList<Student>(); ... @ManyToMany @JoinTable(name="Student_Class", joinColumns={@JoinColumn(name="class_code")}, inverseJoinColumns={@JoinColumn(name="student_id")}) public List<Student> getStudents() { return students; } public void setStudents(List<Student> students) { this.students = students; } ...}


1001 926

1001 951

... ...

Student_Class



18

Klass.java

public static void load() { ... Student doe = Student.find("Doe"); Student jane = Student.find("Jane"); Student novak = Student.find("Novak"); Student smith = Student.find("Smith"); java.getStudents().add(smith); java.getStudents().add(doe); ds.getStudents().add(doe); ds.getStudents().add(novak); se.getStudents().add(doe); os.getStudents().add(novak); os.getStudents().add(smith); compilers.getStudents().add(smith); compilers.getStudents().add(jane); ... }

SchoolDemo5

Find each student.

Assign studentsto classes.



19

Hibernate Oral Presentations

Next Monday, February 11. Four teams volunteer to each give a quick

15-minute presentation and demoof its project. What data did you use. Logical design of your database. How you used Hibernate. Demo of your application. Q & A

First four teams to send me an email. Points can be added to your project score.



20

Overcoming the Paradigm Mismatch

What is a major feature of the object-oriented data model that is notin the relational data model? inheritance

How can we model an inheritance hierarchywith relational database tables? Hibernate uses three strategies:

one table per class or subclass of the inheritance hierarchy joined tables across the inheritance hierarchy a single table per inheritance hierarchy

_



21

Example Inheritance Hierarchy

firstName : StringlastName : String

Person

sjsuId : String

SjsuPerson

gpa : float

SjsuStudent



22

Person.java@Entitypublic class Person { private long id; private String firstName; private String lastName; public Person() {} @Id @GeneratedValue @Column(name="id") public long getId() { return id; } public void setId(long id) { this.id = id; } @Column(name="first_name") public String getFirstName() { return firstName; } public void setFirstName(String name) { this.firstName = name; } ...}


Person

sjsuId : String

SjsuPerson

gpa : float

SjsuStudent



23

SjsuPerson.java

@Entitypublic class SjsuPerson extends Person{ private String sjsuId; public SjsuPerson() {} @Column(name="sjsu_id") public String getSjsuId() { return sjsuId; } public void setSjsuId(String id) { this.sjsuId = id; }}


Person

sjsuId : String

SjsuPerson

gpa : float

SjsuStudent



24

SjsuStudent.java

@Entitypublic class SjsuStudent extends SjsuPerson{ private float gpa; public SjsuStudent() {} @Column(name="gpa") public float getGpa() { return gpa; } public void setGpa(float gpa) { this.gpa = gpa; }}


Person

sjsuId : String

SjsuPerson

gpa : float

SjsuStudent



25

InheritanceDemo.java

public class InheritanceDemo { public static void main(String[] args) { Class klasses[] = {Person.class, SjsuPerson.class, SjsuStudent.class}; HibernateContext.addClasses(klasses);

HibernateContext.createSchema();

... }}



26

InheritanceDemo.javapublic class InheritanceDemo { public static void main(String[] args) { ...

Person person = new Person(); person.setFirstName("John"); person.setLastName("Doe"); SjsuPerson sjsuPerson = new SjsuPerson(); sjsuPerson.setFirstName("Mary"); sjsuPerson.setLastName("Jane"); sjsuPerson.setSjsuId("001234567"); SjsuStudent sjsuStudent = new SjsuStudent(); sjsuStudent.setFirstName("Leslie"); sjsuStudent.setLastName("Smith"); sjsuStudent.setSjsuId("007654321"); sjsuStudent.setGpa(3.71f); ... }}


Person

sjsuId : String

SjsuPerson

gpa : float

SjsuStudent



27

InheritanceDemo.java

public class InheritanceDemo { public static void main(String[] args) { ...

Session session = HibernateContext.getSession(); Transaction tx = session.beginTransaction(); { session.save(person); session.save(sjsuPerson); session.save(sjsuStudent); } tx.commit(); session.close(); }}



28

Inheritance Strategy: One Table per Class@Entity@Inheritance(strategy=InheritanceType.TABLE_PER_CLASS)public class Person { private long id; private String firstName; private String lastName; public Person() {} @Id @GeneratedValue(strategy = GenerationType.TABLE) @Column(name="id") public long getId() { return id; } public void setId(long id) { this.id = id; } ...} InheritanceDemo1

Top of the inheritance hierarchy



29

Inheritance Strategy: One Table per Class

Disadvantages of one table per class strategy: Duplicated fields in the subclass tables. Changes to a superclass field value

will require multiple table updates.



30

Inheritance Strategy: Joined Tables@Entity@Inheritance(strategy=InheritanceType.JOINED)public class Person { private long id; private String firstName; private String lastName; public Person() {} @Id @GeneratedValue @Column(name="id") public long getId() { return id; } public void setId(long id) { this.id = id; } ...} InheritanceDemo2



31

Inheritance Strategy: Joined Tables

Disadvantages of the joined table strategy: Table joins are inefficient. Poor performance with deep class hierarchies.



32

Inheritance Strategy: Single Table@Entity@Inheritance(strategy=InheritanceType.SINGLE_TABLE)public class Person { private long id; private String firstName; private String lastName; public Person() {} @Id @GeneratedValue @Column(name="id") public long getId() { return id; } public void setId(long id) { this.id = id; } ...} InheritanceDemo3



33

Project #1

Now you should be able to use Hibernate to: Map Java classes to relational database tables. Specify one-to-one, one-to-many, and many-to-many

associations, and handle class hierarchies. Create and populate your database tables.

Use sample datasets downloaded from the Internet, or make up your own data.

You should not need to download and install Hibernate. NetBeans: Hibernate is built in. Eclipse: Use my zipped jar files. Command line: Put the jar files in your class path.

Why are you using the command line instead of an IDE?_

cs 157b: database management systems ii february 4 class meeting department of computer science san...

Documents