If you're an Oracle user, you'll know that it's possible to update views with insteadOf triggers. An insteadOf trigger will do the CRUD operation(s), and it will be called with new/changed values like any other trigger. It's like a procedure that does CRUD programatically. It's a simple concept but not available in all databases.
With JVx' DBStorage, it's super easy to use insteadOf triggers in your business logic independent of the database.
We have a short example for you. Imagine you have an activity table with columns: name, cost, valid_from, valid_to, etc. The table also has a foreign key to a contract. One contract has one or more activities:
Our GUI shows a list of all available activities and it should be possible to change the contract. If the contract is available, this would be a straightforward implementation because JVx supports everything out of the box. However, we want to insert a new contract if it isn't available without additional popups.
We'll use an insteadOf trigger to solve this problem.
In the above screenshot, the “New contract” wasn't available in the contract table, but we did the insert during inserting a new activity. Here's the whole code:
- TestInsteadOf.java
/* * Copyright 2016 SIB Visions GmbH * * Licensed under the Apache License, Version 2.0 (the "License"); you may not * use this file except in compliance with the License. You may obtain a copy of * the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the * License for the specific language governing permissions and limitations under * the License. * * * History * * 11.08.2016 - [JR] - creation */ package com.sibvisions.forum; import jvx.rad.application.SimpleTestLauncher; import jvx.rad.genui.UIFactoryManager; import jvx.rad.genui.container.UIPanel; import jvx.rad.genui.layout.UIBorderLayout; import jvx.rad.type.bean.IBean; import jvx.rad.ui.celleditor.ILinkedCellEditor; import org.junit.Before; import org.junit.BeforeClass; import org.junit.Test; import com.sibvisions.apps.components.NavigationTable; import com.sibvisions.apps.server.util.LifeCycleUtil; import com.sibvisions.rad.persist.StorageDataBook; import com.sibvisions.rad.persist.event.IStorageListener; import com.sibvisions.rad.persist.event.StorageEvent; import com.sibvisions.rad.persist.jdbc.DBAccess; import com.sibvisions.rad.persist.jdbc.DBStorage; import com.sibvisions.rad.ui.swing.impl.SwingFactory; import com.sibvisions.util.type.CommonUtil; public class TestInsteadOf extends UIPanel { /** the database access. */ private DBAccess dba; /** the contract storage. */ private DBStorage dbsContract; /** the activity storage. */ private DBStorage dbsActivity; /** * Initializes the unit test. * * @throws Exception if initialization fails */ @BeforeClass public static void beforeClass() { UIFactoryManager.getFactoryInstance(SwingFactory.class); } /** * Initializes the test case. * * @throws Exception if initialization fails */ @Before public void before() throws Exception { dba = DBAccess.getDBAccess("jdbc:oracle:thin:@localhost:1521:XE", "username", "password"); dba.open(); } /** * Cleanup the test case. */ @After public void after() { CommonUtil.close(dbsActivity, dbsContract, dba); } /** * Tests application start with a custom UI. * * @throws Exception if app start fails */ @Test public void testInsteadOf() throws Exception { DBStorage dbsActivities = new DBStorage(); dbsActivities.setAutoLinkReference(false); dbsActivities.setFromClause("V_ACTIVITIES"); dbsActivities.setWritebackTable("ACTIVITY"); //manual autolink dbsActivities.createAutomaticLinkReference(new String[] {"CONT_ID", "CONT_TITLE"}, "CONTRACT", new String[] {"ID", "TITLE"}); dbsActivities.eventInsteadOfInsert().addListener(new IStorageListener() { public void storageChanged(StorageEvent pStorageEvent) throws Throwable { boolean bAutoCommit = dba.isAutoCommit(); try { dba.setAutoCommit(false); DBStorage contract = getContract(); IBean newRecord = pStorageEvent.getNew(); //check if a new contract should be created for the activity if (newRecord.get("CONT_ID") == null) { IBean bnContract = contract.createEmptyBean(); bnContract.put("TITLE", newRecord.get("CONT_TITLE")); bnContract = contract.insert(bnContract); //we need the new id in our activity record! newRecord.put("CONT_ID", bnContract.get("ID")); } //updates newRecord, because we need the updated columns (e.g. ID) newRecord.putAll(getActivity().insert(newRecord)); dba.commit(); } catch (Throwable th) { dba.rollback(); throw th; } finally { dba.setAutoCommit(bAutoCommit); } } }); dbsActivities.setDBAccess(dba); dbsActivities.open(); StorageDataBook sdbActivities = new StorageDataBook(dbsActivities); sdbActivities.open(); ((ILinkedCellEditor)sdbActivities.getRowDefinition().getColumnDefinition("CONT_TITLE"). getDataType().getCellEditor()).setValidationEnabled(false); setLayout(new UIBorderLayout()); NavigationTable nav = new NavigationTable(); nav.setDataBook(sdbActivities); //show all columns //nav.setColumnView(new ColumnView(sdbActivities.getRowDefinition().getColumnNames())); add(nav); new SimpleTestLauncher(this); } /** * Gets the contract storage. * * @return the contract storage. * @throws Exception if creation fails */ public DBStorage getContract() throws Exception { if (dbsContract == null) { dbsContract = LifeCycleUtil.createWriteBackStorage(dba, "CONTRACT"); } return dbsContract; } /** * Gets the activity storage. * * @return the contract storage. * @throws Exception if creation fails */ public DBStorage getActivity() throws Exception { if (dbsActivity == null) { dbsActivity = LifeCycleUtil.createWriteBackStorage(dba, "ACTIVITY"); } return dbsActivity; } } // TestInsteadOf
The important things are:
...setValidationEnabled(false); eventInsteadOfInsert()
The first method allows inserting new contracts because the cell editor won't check for existing contracts. The second method implements the insteadOf trigger.
The trigger itself checks if the contract ID is empty and creates a new contract in this case. The activity will be inserted with the new contract id. The eventInsteadOfUpdate is missing in our example, but it works the same way.
You'll find the implementation of SimpleTestLauncher here.
The database objects (Oracle):
- create.sql
CREATE TABLE CONTRACT ( id NUMBER(16) NOT NULL, title VARCHAR2(1000), state NUMBER(1) DEFAULT 1 ) ; COMMENT ON COLUMN CONTRACT.id IS 'PK'; COMMENT ON COLUMN CONTRACT.title IS 'Contract title'; COMMENT ON COLUMN CONTRACT.state IS 'Contract state'; ALTER TABLE CONTRACT ADD CONSTRAINT CONT_PK PRIMARY KEY (ID); CREATE TABLE ACTIVITY ( id NUMBER(16) NOT NULL, cont_id NUMBER(16), name VARCHAR2(1000), cost NUMBER(10,2), valid_from DATE, valid_to DATE, responsible_user_id NUMBER(16), validated CHAR(1) DEFAULT 'Y' ) ; COMMENT ON COLUMN ACTIVITY.id IS 'PK'; COMMENT ON COLUMN ACTIVITY.cont_id IS 'Contract FK'; COMMENT ON COLUMN ACTIVITY.name IS 'Activity name'; COMMENT ON COLUMN ACTIVITY.cost IS 'Activity costs'; COMMENT ON COLUMN ACTIVITY.valid_from IS 'Valid from'; COMMENT ON COLUMN ACTIVITY.valid_to IS 'Valid to'; COMMENT ON COLUMN ACTIVITY.responsible_user_id IS 'Responsible User FK'; COMMENT ON COLUMN ACTIVITY.validated IS 'Whether the activity is valid'; ALTER TABLE ACTIVITY ADD CONSTRAINT ACTI_PK PRIMARY KEY (ID); ALTER TABLE ACTIVITY ADD CONSTRAINT ACTI_CONT_FK FOREIGN KEY (CONT_ID) REFERENCES CONTRACT (ID); ALTER TABLE ACTIVITY ADD CONSTRAINT ACTI_RESP_USER_FK FOREIGN KEY (RESPONSIBLE_USER_ID) REFERENCES USERS (ID); CREATE SEQUENCE SEQ_ACTIVITY_ID minvalue 1 maxvalue 999999999999999999999999999 START WITH 41 INCREMENT BY 1 cache 20 ORDER; CREATE SEQUENCE SEQ_CONTRACT_ID minvalue 1 maxvalue 999999999999999999999999999 START WITH 21 INCREMENT BY 1 cache 20 ORDER; CREATE OR REPLACE force VIEW v_activities AS SELECT a.id ,a.cont_id ,c.title cont_title ,a.name ,a.cost ,a.valid_from ,a.valid_to FROM activity a ,contract c WHERE a.cont_id = c.id; CREATE OR REPLACE TRIGGER TR_ACTIVITY_BR_IU BEFORE INSERT OR UPDATE ON activity FOR each ROW BEGIN IF (:NEW.id IS NULL) THEN SELECT seq_activity_id.NEXTVAL INTO :NEW.id FROM dual; END IF; IF (:NEW.validated = 'N') THEN IF (:NEW.valid_from IS NULL) THEN raise_application_error(-20000, 'Valid from can''t be null!'); END IF; END IF; END TR_ACTIVITY_BR_IU; / CREATE OR REPLACE TRIGGER TR_CONTRACT_BR_IU BEFORE INSERT OR UPDATE ON contract FOR each ROW BEGIN IF (:NEW.id IS NULL) THEN SELECT seq_contract_id.NEXTVAL INTO :NEW.id FROM dual; END IF; IF (:NEW.state = 4) THEN UPDATE activity SET validated = 'N' WHERE cont_id = id AND validated = 'Y'; END IF; END TR_CONTRACT_BR_IU; / prompt Disabling triggers FOR CONTRACT... ALTER TABLE CONTRACT disable ALL triggers; prompt Disabling triggers FOR ACTIVITY... ALTER TABLE ACTIVITY disable ALL triggers; prompt Disabling FOREIGN KEY constraints FOR ACTIVITY... ALTER TABLE ACTIVITY disable CONSTRAINT ACTI_CONT_FK; ALTER TABLE ACTIVITY disable CONSTRAINT ACTI_RESP_USER_FK; prompt Loading CONTRACT... INSERT INTO CONTRACT (id, title, state) VALUES (1, 'Air', 1); COMMIT; prompt 1 records loaded prompt Loading ACTIVITY... INSERT INTO ACTIVITY (id, cont_id, name, cost, valid_from, valid_to, responsible_user_id, validated) VALUES (1, 1, 'Part 1', .2, NULL, NULL, NULL, 'Y'); COMMIT; prompt 1 records loaded prompt Enabling FOREIGN KEY constraints FOR ACTIVITY... ALTER TABLE ACTIVITY enable CONSTRAINT ACTI_CONT_FK; ALTER TABLE ACTIVITY enable CONSTRAINT ACTI_RESP_USER_FK; prompt Enabling triggers FOR CONTRACT... ALTER TABLE CONTRACT enable ALL triggers; prompt Enabling triggers FOR ACTIVITY... ALTER TABLE ACTIVITY enable ALL triggers;