Data Layer

From Skyway Wiki

Jump to: navigation, search

Contents

Overview

To understand how persistence and relationships work within Skyway, you need to know the basics of how relationships work within Java and how persistence works within Relational Database Management Systems (RDBMS). While Skyway abstracts the complexity of much of the development process, it does not attempt to do things out of the norm for how Java and RDBMS work. With that in mind, there are 2 key rules to remember when thinking about object and data persistence:

  • Records referenced by foreign keys must exist.
  • You must set both sides of a bidirectional relationship.

To articulate examples of these rules, let's use the following setup.

Tables: 

 CREATE TABLE "City" (
   "name" character varying(50) NOT NULL,
   state character varying(50) NOT NULL,
   CONSTRAINT "CityPK" PRIMARY KEY (name),
   CONSTRAINT "StateFK" FOREIGN KEY (state)
     REFERENCES "State" (state) MATCH SIMPLE
     ON UPDATE NO ACTION ON DELETE NO ACTION
 )

 CREATE TABLE "State" (
   state character varying(50) NOT NULL,
   abbreviation character varying(5),
   CONSTRAINT "StatePK" PRIMARY KEY (state)
 )

Java Code: 

 @Entity
 public class City implements Serializable {
   private static final long serialVersionUID = 1L;
 
   @Id
   private String name;
   
   @ManyToOne
   @JoinColumn(name="state")
   private State state;
   
   public City() {
       super();
   }
 
   public String getName() {
       return this.name;
   }
 
   public void setName(String name) {
       this.name = name;
   }
   
   public State getState() {
       return this.state;
   }
   
   public void setState(State state) {
       this.state = state;
   }
 }



 @Entity
 public class State implements Serializable {
   private static final long serialVersionUID = 1L;
   @Id
   private String state;
   private String abbreviation;
 
   @OneToMany(mappedBy="state")
   private Set<City> cityCollection;
 
   public State() {
     super();
   }
   
   public String getState() {
     return this.state;
   }
   
   public void setState(String state) {
     this.state = state;
   }
   
   public String getAbbreviation() {
     return this.abbreviation;
   }
   
   public void setAbbreviation(String abbreviation) {
     this.abbreviation = abbreviation;
   }
   
   public Set<City> getCityCollection() {
     return this.cityCollection;
   }
   
   public void setCityCollection(Set<City> cityCollection) {
     this.cityCollection = cityCollection;
   }
 }

Foreign Keys

In a RDBMS, a foreign key is a reference from a record in one table to a record in another table. Foreign keys are used to enforce referential integrity. Consider the above tables, you cannot set City.state to a value that does not exist in the State.state table.

Foreign keys manifest themselves in Java when you go to persist an object. Sticking with the above example, if you have a City object that is persisted to the City table, and you do not have it referencing a persisted State object, then you will get an exception when you attempt to save the City object.

Bidirectional Relationships

If you are using bidirectional relationships between objects, then you must set both sides of the relationship. While there are exceptions to this rule, where you could get away with setting only 1 side of the relationship due to the way your tables are structured or that you load data, it is a best practice to set both sides of the relationship if using a bidirectional relationship.

In the above sample code, if you wished to make a new City object and relate it to a state, you would need to do the following: 

 State florida = new State();
 florida.setName("Florida");
 florida.setAbbreviation("FL");
 
 City tampa = new City();
 tampa.setName("Tampa");

 // setting both sides of the relationship here
 tampa.setState(florida);
 florida.getCityCollection.add(tampa);

Skyway enables you to do exactly these same things through modeling rather than writing this code. But Skyway does not make it so that you do not have to set these relationships.

Implement a one-to-many relationship using foreign keys

JPA (Java Persistence API) by default uses a join table to store association records between two entities. In many cases we have to deal with an existing database in which foreign keys are used to implement associations. Here we describe how to create data types that generate tables with a one-to-many foreign key relationship from scratch with Skyway 6.1.

We'll use the archetypal Company -> Employee relationship. Company has companyId and companyName fields, and Employee has employeeId and employeeName fields. companyId and employeeId are primary keys. We'll use company_fk as the foreign key column in the Employee Data Type.


Create the Skyway Artifacts

  • Create a Skyway Project.
  • Create the Company and Employee Data Types.
  • In Company Data Type Overview tab, add the Employee Relationship. Set to Many. Save.
  • In Employee Data Type Overview tab, add the Company Relationship. Set the Inverse Relationship to employee. Save.
  • Close the Company Data Type, and re-open it. Set the Inverse Relationship to company. Save.


Persistence Mapping & Setting the Join

  • In the Employee Data Type Persistence Mapping Tab, Expand the Relationship Mapping area.
  • Verify this is owned by Employee and the MANY_TO_ONE Cardinality is set.
  • In the Join Table area, uncheck "Use Default".
  • Set the "Table Name:" field to the parent table name as it appears in the Persistence Mapping table, In this case: Company.
  • Add a Join Column: Set "Column Name:" to the foreign key column that will exist on the Child, (Employee) table. In this case: company_fk.
  • Set "Referenced Column" to the field on the Parent (Company) table. In this case: companyId.
  • Add Both Data Types to a Data Store and do CRUD Scaffolding so that the project can be deployed to a server.
  • Additionally, if you want to have all related Employees deleted when the Company is deleted:
    • in the Cascade section check 'Cascade = ALL'


Now we'll describe how to perform CRUD operations on the Company and Employee entities.
We'll assume there is a Conversation with company and employee variables.
To Add a new Company and an Employee

  • Using a Groovy or Variable Editor step set company.companyId
  • persist company to the Data Store (select the option to modify the underlying database immediately)
  • set employee.employeeId
  • add employee to company
    • company.employees.add(employee)
  • set company in employee
    • employee.setEmployedBy(company)
  • update company in the Data Store


To Add an Employee to an existing Company:

  • use a Search Data Store or Execute named Query step to load the company
  • set employee.employeeId
  • add employee to company
    • company.employees.add(employee)
  • set company in employee
    • employee.setEmployedBy(company)
  • update company in the Data Store


To Remove Company (and all its Employees if Cascade = ALL is set):

  • use a Search Data Store or Execute named Query step to load the company
  • remove company from the Data Store


To Remove an Employee from the database:

  • use a Search Data Store or Execute named Query step to load the company
  • use a Search Collection step to find the employee to be deleted
  • remove employee from company.employees collection
  • set company to null in employee.employedBy attribute
  • remove employee from the Data Store


To Remove an Employee from a Company:

  • use a Search Data Store or Execute named Query step to load the company
  • use a Search Collection step to find the employee to be removed from the company
  • set company to null in employee.employedBy attribute
  • update employee in the Data Store

Implement a self relationship using a foreign key

This is a special case of setting up a one-to-many relationship using a foreign key.

An example is the Manager -> Employee relationship in which a Manager manages many Employees, but is also an Employee.

Assume the database has an EMPLOYEE table with the columns: employee_id, employee_name and manager_id - which is a foreign key to the employee_id column.

Let's assume this table has been mapped to a Skyway entity Employee, so there will be a one-to-many bi-directional relationship between Employee and itself, namely manager and employees. So, Employee has one manager and an Employee (or the Manager in this case) has many employees.

To Add a new Employee and Manager

  • create two variables: employeeVar and managerVar of type Employee
  • Using a Groovy or Variable Editor step set managerVar.employeeId
  • persist managerVar to the Data Store (select the option to modify the underlying database immediately)
  • set employeeVar.employeeId
  • add employeeVar to managerVar
    • managerVar.employees.add(employeeVar)
  • set managerVar in employee
    • employee.setManager(managerVar)
  • update managerVar in the Data Store

The important point to note is that in the case of the self-relationship there are two distinct variables for the employee and manager, even though they are of the same Employee type.

The other CRUD steps to manage the Employee - Manager relationship are similar to those for the Company - Employee relationship above.

Known Issues

When using Toplink as the object-relational mapping framework certain types of entity relationships need special handling in Skyway to persist properly.

TopLink: 1-M relationship where the foreign key column is part of a compound primary key – how to persist the relationship

Assume that there is a geographic database with CITY and STREET tables. CITY has CITY_ID and CITY_NAME columns. STREET has a compound primary key with CITY_ID and STREET_NAME columns. CITY_ID also is a foreign key to the CITY table.

After reverse engineering these tables into Skyway the entity model is going to reflect a one-to-many bidirectional relationship between City and Street entities. However, when you try to persist this relationship, Toplink throws an exception like the following: 

Exception [TOPLINK-48] (Oracle TopLink Essentials - 2.0 (Build b41-beta2 (03/30/2007))):
 oracle.toplink.essentials.exceptions.DescriptorException
Exception Description: Multiple writable mappings exist for the field [SCHEMA.STREET.CITY_ID].  
 Only one may be defined as writable, all others must be specified read-only.
Mapping: oracle.toplink.essentials.mappings.OneToOneMapping[city]

Descriptor: RelationalDescriptor(data.projectname.servicename.Streets --> [DatabaseTable(SCHEMA.STREET)])

The fix is to make one of the mappings - the relationship in this case - read-only. Two attributes must be added to the annotation for the relationship in the generated Street.java file. The file can be found at generated/data/projectname/servicename/Street.java.

  1. Open the file and find the definition:
    private City city;
  2. In the @JoinColumns annotation, add :
    insertable = false, updatable = false
  3. Since you are modifying this field definition, remove the @generated annotation from the comment above the field.
    The final definition and annotations should look like this:
/**
 * Removed generated annotation and added insertable and updatable values.
 */
@ManyToOne(cascade = { CascadeType.ALL }, fetch = FetchType.LAZY)
@JoinColumns( { @JoinColumn(name = "CITY_ID", referencedColumnName = "CITY_ID", insertable = false, updatable = false)})
private City city;


TopLink: 1-M Foreign Key Relationship where the Foreign Key column has a NOT NULL Constraint – How to delete the record on the Many side

Assume that there is a geographic database in which table A has a 1-Many relationship to table B. B contains the foreign key column and the column has a NOT NULL constraint.

Normally when trying to delete an object, you need to break the relationships first, then delete the object. If you try to break the relationships in the normal fashion (remove B from A’s collection of B, then set B.A = null, then delete B), you will get a SQL exception because the foreign key column in B gets set to null, violating the NOT NULL constraint.

In TopLink, the way delete the object from the many side is to:

  • Remove B from A’s collection of B.
  • Delete B.

Cascading Deletes – Don’t use them in this Scenario The Not Null constraint on the foreign key column in B means:

  • A can exist without B
  • B cannot exist without A

B owns the relationship, if you delete B, you don’t want to cascade the delete to A.

The relationship is imported with Cascade deletes turned on. Turn them off in the Persistence Mapping for B (use Cascade.PERSIST, Cascade.MERGE, and Cascade.REFRESH).

SQL Server Timestamp vs. Java Timestamp types

The TIMESTAMP of SQL Server is not the same as the Java Timestamp. It's a read-only binary column automatically updated by SQL Server when the row is altered (it's used for replication and optimistic concurrency among others).

In order to make use of a java Timestamp the user will need to make the column a DATETIME data type.

Data Type Importing

Importing a Data Type from an existing database table is a very powerful feature, however, there are a few caveats to be aware. When importing an Oracle table, the user is advised to use only the Oracle "Numeric" data type for any numeric-type fields. The persistence model tends to map Oracle types such as: long, float, etc to types that are either non-numeric or with improper precision.

If your table uses the Oracle types of NCHAR or NVARCHAR (for international-character support), the user may have to choose a "MetaData Dialect" from the second drop-down box on the Import Wizard. See screen-capture below:

Image:GenDT.png

Retrieved from "http://www.skywayperspectives.org/wiki/index.php/Data_Layer"
Personal tools