Database View Support
A view is a virtual table in the database which facilitate subset the data contained in a table, joining multiple tables into a single virtual view, acting as a aggregated tables which showing the calculated fields also hiding the complexity of the data. In VP Suite 2.3 SP2, we do one step forward which support reverse engineer views from the database and generating Object-Relational Mapping source code for querying the view. We also support generating views into database from the diagrams.
Reverse Existing View into ERD
During reverse database, the view will be indicated with (View). After reversing database, the view table will shows as an entity with a “View” stereotypes and there is a view icon showing in the top right hand corner of the entity.
Select table and view when reverse database
Reversed tables and view
Generate View to Database
To generate view into database, first you have to create a view in the ERD.
After creating the view, you can specify the name and datatype of the column in the view.
Now right click on the view and select
Open Specification. In the
View Specification dialog, specify the
SQL statement for the view in the
Create Statement section
CREATE OR REPLACE FORCE VIEW "PERSON_CAR_BRAND" ("NAME", "BRAND", "PERSON_ID", "CAR_ID") AS
SELECT "PERSON"."NAME" AS "NAME",
"CAR"."BRAND" AS "BRAND",
"PERSON"."ID" AS "PERSON_ID",
"CAR"."ID" AS "CAR_ID"
FROM "PERSON" "PERSON",
"CAR" "CAR"
WHERE "PERSON"."ID"="CAR"."ABCPERSONID"
You are now ready to generate the view to the database. Select
Generate Database from the menu and choose
Update Database (to make sure it won’t erase your existing tables) and
Export to database during database generation.
And you can find the view inside the database after the database generation process complete.
Generate Persistence Layer Source Code for Accessing the View
To generate persistence layer source code for accessing the view, first you have to assign the primary key column for the view. You can do this by editing the column name directly in the diagram and add a “
+” sign to the column, or specify it inside the
Column Specification dialog.
After that you have to synchronize the ERD into Class Diagram for generate the object model.
Now you can select
Generate Code from the menu to generate the persistence layer source code.
We write a simple Java program to test the generated source and you will get the following output.
package test;
import org.orm.*;
public class QueryFromView {
public static void main(String[] args) throws PersistentException {
orm.PERSON_CAR_BRAND[] ormPERSON_CAR_BRANDs = orm.PERSON_CAR_BRANDFactory.listPERSON_CAR_BRANDByQuery(null, null);
for (int i = 0; i < ormPERSON_CAR_BRANDs.length; i++) {
System.out.println(ormPERSON_CAR_BRANDs[i].getNAME() + "--" + ormPERSON_CAR_BRANDs[i].getBRAND());
}
}
}
Execution result
Downloads
Resources
