*
* @author Waqhussain
* @date December 2002
*/
class JdbcEdit extends JDialog {
private JdbcCW parent;
private Container cp;
private Vector columnNames;
private Vector dataTypes;
private Vector editFields;
private Vector rows;
private String table;
private Vector PKresults;
private int noOfColumns;
private int currentRow = 0;
//jd. have declared buttons and labels outside of constructor
//jd. so I can change there status within methods.
private JButton updateButton = new JButton("Update");
private JButton exitButton = new JButton("Exit");
private JButton nextButton = new JButton("Next");
private JButton prevButton = new JButton("Prev");
private JLabel lblInfo = new JLabel("");//jd. label to inform user of foreign keys
private JLabel lblDate = new JLabel("");//jd. label to inform user of date format
/**
* JdbcEdit constructor method
* the parameter true makes the dialog window modal -
* the parent frame is inactive as long as this window is displayed
*/
public JdbcEdit (JdbcCWc9953547 parent, String tableName, ResultSet results) throws Exception {
super(parent,"Table Editor " + tableName, true);
this.parent = parent;
columnNames = new Vector();
dataTypes = new Vector();
editFields = new Vector();
//jd. have assigned global varialbe with tableName
//jd. so it is avaialbe to methods.
table = tableName;
JPanel mainPanel = new JPanel();
mainPanel.setLayout(new BoxLayout(mainPanel,BoxLayout.Y_AXIS));
// boxLayout - components are added to mainPanel in a vertical stack
try {
boolean anyRecords = results.next();
if ( ! anyRecords ) {
JOptionPane.showMessageDialog (this, "No Detail for " +
tableName+ " in the database");
return;
}
do {
String columnName = results.getString(1);
String dataType = (String)results.getString(2);
JPanel editPanel = new JPanel();
JLabel colNameLabel = new JLabel(columnName);
JTextField dataField = new JTextField(20);
editPanel.add(colNameLabel);
editPanel.add(dataField);
// this would be a good place to add an Update button
//jd. add a listner to the TextField to activate the
//jd. update button if any changes are made
dataField.addKeyListener(new KeyListener () {
public void keyTyped(KeyEvent evt) {updateButton.setEnabled(true);}
public void keyPressed(KeyEvent evt) {}
public void keyReleased(KeyEvent evt) {}
});
mainPanel.add(editPanel);
// now store the columnName, dataType and data text field
// in vectors so other methods can access them
// at this point in time the text field is empty
//jd. added colNameLabel to vector columnNames so that
//jd. can change the label red if it is a Foreign Key
columnNames.add(colNameLabel);
dataTypes.add(dataType);
editFields.add(dataField);
}while (results.next() ) ;
} catch (java.sql.SQLException e) {
System.out.println("SQL Exception on query ");
return;
}
// get the data from the Oracle table and put the first
// row of data in the text fields in the dialog window
populateData(tableName);
// find out which column(s) are part of the primary key and make these
// textfields non-editable so the values can't be changed
findPKConstraints(tableName);
// this would be a good place to discover any foreign key constraints
//jd. find the foreign keys and make the textfield and the label
//jd. have red text, and show a label to tell the user this
findFKConstraints(tableName);
JPanel buttonsPanel = new JPanel();
exitButton.addActionListener(new ActionListener () {
public void actionPerformed(ActionEvent evt) {
closeWindow();
}
}
);
nextButton.addActionListener(new ActionListener () {
public void actionPerformed(ActionEvent evt) {
showRow(true);
}
}
);
prevButton.addActionListener(new ActionListener () {
public void actionPerformed(ActionEvent evt) {
showRow(false);
}
}
);
updateButton.addActionListener(new ActionListener () {
public void actionPerformed(ActionEvent evt) {
update(table);
}
}
);
buttonsPanel.add(exitButton);
buttonsPanel.add(nextButton);
buttonsPanel.add(prevButton);
buttonsPanel.add(updateButton);
//jd. inactivate the buttons, but if
//jd. there is more than one row in the
//jd. table activate the next button
if (rows == null) {nextButton.setEnabled(false);}
else if (rows.size() > 1)
{nextButton.setEnabled(true);}
else
{nextButton.setEnabled(false);}
updateButton.setEnabled(false);
prevButton.setEnabled(false);
JPanel infoPanel = new JPanel();
infoPanel.setLayout(new BoxLayout(infoPanel,BoxLayout.Y_AXIS));
infoPanel.add(lblInfo);
infoPanel.add(lblDate);
cp = getContentPane();
cp.add(mainPanel,BorderLayout.NORTH);
cp.add(buttonsPanel,BorderLayout.SOUTH);
cp.add(infoPanel,BorderLayout.CENTER);
pack();
}//jd. end of constructor
private void closeWindow() {
dispose();
}
private void populateData(String tableName) throws Exception {
int noOfColumns;
// have to access the Statement object in the parent frame
ResultSet tableData = parent.makeQuery("select * from " + tableName);
try {
boolean anyRecords = tableData.next();
if ( ! anyRecords ) {
JOptionPane.showMessageDialog (this, "No data in " +
tableName);
return;
}
rows = new Vector();
noOfColumns = columnNames.size();
do {
String [] row = new String[noOfColumns];
for (int i = 0; i < noOfColumns; i++)
{
String types = (String)dataTypes.get(i);
//jd. if data type is date, change the format to dd-MMM-yy
//jd. updates can only be done in this format so it
//jd. it is wise to show the date in the update format as well.
if (types.compareTo("DATE") == 0)
{
row[i] = DateFormat(tableData.getString(i+1));
lblDate.setText("Date field's format = dd-mmm-yy e.g. 28-feb-02");}
else
{row[i] = tableData.getString(i+1);}
} //jd. end of for loop
rows.add(row);
}
while (tableData.next() ) ;
} //jd end of try
catch (java.sql.SQLException e) {
JOptionPane.showMessageDialog(null,"***SQL Exception on query !!***" , "Error Message",
JOptionPane.ERROR_MESSAGE);
System.out.println("SQL Exception on query ");
return;}
// Put the first row of data from the table into the test fields;
String [] rowData = (String[]) rows.get(0);
for (int i = 0; i < noOfColumns; i++)
{
// get the reference to a text field in the dialog window
JTextField textField = (JTextField) editFields.get(i);
// vector editFields holds a reference to each JTextField
// in the visible dialog window
// hence the next line of code puts the data retrieved from
// the table into the relevant text field in the GUI interface
textField.setText(rowData[i]);
} // jd. end of for loop
} //jd. end of method populateData()
//jd. method to convert string/dates from yyyy-mm-dd to dd-mmm-yy format
public String DateFormat (String textDate) throws Exception
{
SimpleDateFormat sdfInput =
new SimpleDateFormat( "yyyy-MM-dd" );
SimpleDateFormat sdfOutput =
new SimpleDateFormat ( "dd-MMM-yy" );
Date date = sdfInput.parse( textDate );
return sdfOutput.format( date );
} // end of method DateFormat
// method showRow updates the textfields in the GUI interface
// with the next row of data from the table ( if next is true)
// or with the previous row of data from the table ( if next is false)
private void showRow(boolean next) {
if ( rows == null ) {
System.out.println("table is empty");
getToolkit().beep();
return;}
//jd. have changed code slightly to enable/disable next/prev buttons
//jd. rather than using System.out.println warning.
if (next && currentRow < rows.size() - 1) {
currentRow++;
prevButton.setEnabled(true);}
else if (!next && currentRow > 0) {
currentRow--;
nextButton.setEnabled(true);}
else
{getToolkit().beep();
return;}
if (currentRow ==0)
{prevButton.setEnabled(false);}
if (currentRow == rows.size()-1)
{nextButton.setEnabled(false);}
String [] rowData = (String[]) rows.get(currentRow);
System.out.println("currentRow = " + currentRow);
int noOfAttributes = dataTypes.size();
for (int i = 0; i < noOfAttributes; i++) {
JTextField textField = (JTextField) editFields.get(i);
textField.setText(rowData[i]);}
} //jd. end of method showRow()
//jd. method to update the database. It checks if any fields have been
//jd. changed first, if they have an update query is performed.
//jd. if the update fails a message is displayed.
private void update(String tableName) {
String [] rowData = (String[]) rows.get(currentRow);
String[] label = new String[dataTypes.size()];
for (int i = 0; i < dataTypes.size(); i++) {
JTextField TextField = (JTextField) editFields.get(i);
String edited = String.valueOf(TextField.getText());//jd. the value on the screen
JLabel labelText = (JLabel) columnNames.get(i);
label[i] = String.valueOf(labelText.getText());
//jd. need to compare edited with rowData[i] if they are different
//jd. try to update the database.
if (edited.compareTo(String.valueOf(rowData[i])) != 0)
{
String Querya ="";
Querya = "update " + tableName + " set "
+ label[i] + " = '" + edited + "' where ";
if (PKresults.size() >0)
{
String Query1 = "";
for (int c=0; c<(PKresults.size()/2);c++)
{
if (c>0)
{Query1 = Query1 + " AND ";}
Query1 = Query1 + label[c] + " = '" + rowData[c] + "'";
} //jd. end of for loop
Query1 = Querya + Query1;
ResultSet result;
try{
result = parent.makeQuery(Query1);
}
catch (Exception e) {
System.out.println("Exception on query " + e);
return;}
if (result == null)
{
JOptionPane.showMessageDialog(null,"***Update Query failed!!***" , "Error Message",
JOptionPane.ERROR_MESSAGE);
TextField.setText(rowData[i]);
//jd. change the value displayed back to its orignal value.
}
else
{ //jd. change the value in the rowData Vector to the edited value
//jd. so that if the user moves between records, the updated
//jd. value appears. Also make the Update button inactive
rowData[i] = edited;
System.out.println("rows updated " );
}
}//jd.end of if
}//jd. end of if
}//jd. end of for loop
//jd. Finally make the Update button inactive
updateButton.setEnabled(false);
}//jd. end of method update()
private void findPKConstraints(String tableName){
String PK_ConstraintName = "none";
String pkquery =
"select owner,constraint_name from user_constraints " +
"where table_name = '" + tableName + "' and constraint_type = 'P'";
// have to access the Statement object in the parent frame
ResultSet results = parent.makeQuery(pkquery);
try {
boolean anyRecords = results.next();
if ( ! anyRecords ) {
// if none just return - but print a debug message
System.out.println("No primary key constraint found");
return;
} else {
// There should only be one
System.out.println("Owner = " + results.getString(1) +
" name = " + results.getString(2));
PK_ConstraintName = results.getString(2);
// Now find out which columns
pkquery =
"select Column_name, position from user_cons_columns"
+ " where constraint_name = '" + PK_ConstraintName
+ "'";
// have to access the Statement object in the parent frame
results = parent.makeQuery(pkquery);
PKresults = new Vector();
anyRecords = results.next();
if ( ! anyRecords ) {
// if none just return - but there must be at least one
System.out.println("no columns found");
return;
} else {
do {
System.out.println("PK Column Name " + results.getString(1) +
" position " + results.getString(2));
//jd. add results to PKresults Vector so that it can be used
//jd. to get the correct update query in the update() method.
PKresults.add(results.getString(1));
PKresults.add(results.getString(2));
int position = Integer.parseInt(results.getString(2));
JTextField primarykey =
(JTextField)editFields.get(position - 1);
primarykey.setEditable(false);
} while (results.next());
}
}
} catch (java.sql.SQLException e) {
System.out.println("SQL Exception on query " + pkquery);
return;
}
}//jd. end of findPKConstraints() method
private void findFKConstraints(String tableName){
//jd. foreign key query finds the column name and
//jd. column no. of any foriegn keys in the table.
String fkquery ="SELECT COLUMN_NAME AS FKEY, COLUMN_ID FROM USER_TAB_COLUMNS "+
"WHERE TABLE_NAME = '" + tableName + "' AND COLUMN_NAME IN "+
"(select column_name from user_cons_columns where constraint_name IN "+
"(SELECT constraint_name from user_constraints where "+
"table_name = '" + tableName + "' and constraint_type = 'R'))";
//jd. have to access the Statement object in the parent frame
ResultSet results = parent.makeQuery(fkquery);
//jd. first test if there are any foreign keys from the
//jd. results of the query.
try {
boolean anyRecords = results.next();
if ( ! anyRecords ) {
// jd. if none just return
System.out.println("no foreign keys found");
return;
} else
{
//jd. if there are, find the column number and change the corresponding
//jd. text field and label to have red text.
//jd. I had to change the columnNames vector to hold the JLabel-colNameLabel
//jd. not the string columnName in order to make this work.
lblInfo.setText("***Red text indicates a Foreign Key***");
do {
int position = Integer.parseInt(results.getString(2));
JTextField foreignkey = (JTextField)editFields.get(position-1);
foreignkey.setForeground(Color.red);
JLabel foreignLabel = (JLabel)columnNames.get(position-1);
foreignLabel.setForeground(Color.red);
} while (results.next());
}
} catch (java.sql.SQLException e) {
System.out.println("SQL Exception on query " + fkquery);
return;
}
}//jd. end of method findFKConstraints() method
}//jd. end of class JdbcEdit
Task 2 – Investigation of object-relational databases
2.1 Identify, read and summarise three sources of information.
Object-Relational DBMSs: Tracking the Next Great Wave
Michael Stonebraker and Paul Brown with Dorothy Moore – Published by Morgan Kaufmann, 1999
The book is written by Michael Stonebraker a professor at the University of California and is the founder of Illutra Information Systems (acquired by Informix Corp) and Paul Brown who works within the Chief Technology Office if Informix Software. Understandably this book has many references to both Illustra and Informix.
Chapter 1 – The DBMS Matrix
This chapter starts by classifying DBMS’s into a two by two matrix:
1. Simple Data without queries is illustrated by a text editor where simple data is copied to the virtual memory, editions are made to the virtual memory copy and then stored back into memory.
2. Simple data with queries is illustrated by the simple storage of employee’s information and department information. All the data is of standard data types found in SQL and it is easy to ask questions like; find all the names of employees who started after 1980 and earn more than $40,000.
This type of simple data and queries are identified as ‘business data processing’ applications. The existing market is discussed and is currently worth approximately $10 billion a year growing by 20% per year.
3. Complex data without queries – a floor planner example is used to illustrate this type of DBMS. The data types are more complex and a process of ‘garbage collection’ of free space is required. The problems of using traditional file systems are discussed and the solution of persistent storage is explained. i.e. variable values are automatically saved when the program terminates and are still available when the program is restarted.
The existing market for these systems is still a niche and is currently worth about $150 million per year and growing at a healthy rate.
4. Complex Data with Queries – an example of the State of California Department of Water Resources (DWR) is used to illustrate these object relational DBMS’s. The complex data types include photo_cd_images and positions and user defined queries include find pictures of sunsets i.e. those with orange at the top of the picture.
The query language needs to support user defined functions and operators as well as clauses expressible in SQL-92. The first standard version of SQL that supports user defined functions is SQL3. The client tools required for the DWR example is the display of maps that the user can circle with a pointing device, on output the user wants to see an image positioned at its geographic location, and features such as pan and zoom are required, but the book says ‘there is not a business form in sight’.
High performance for queries is required, some user defined functions may use more than 100 million instructions, so the query optimisers need to be smart. Two-dimensional queries cannot be speeded up by traditional B-tree indexes and hashing, so spatial clauses need spatial access methods such as a grid file, R-tree or K-D-B-tree.
Security is also important in these types of applications, but because the queries are complex, performance is not greatly improved when relinquishing security.
These upper right quadrant applications are known as object relational DBMS’s – relational because they support SQL and object oriented because they support complex data. Early OR vendors were start up companies, but recently relational vendors have announced plans for OR capabilities and O Vendors have been adding SQL engines to their products.
The next section of this chapter discusses four examples of applications that have characteristics of Relational DBMS’s and OR DBMS’s.
- Risk assessment and fraud detection in the insurance industry
- Modernising a traditional Human Resources application
- Multimedia and GIS data enhance travel reservation systems
- Removing the mapping layer: Tracking part numbers.
The final part of the chapter describes the two forces driving the database market.
- Business data processing applications will become universal applications
- New multimedia applications will drive the market, e.g. Digital slide management and global positioning systems.
The chapter summary states that Object Relational DBMS’s will be the largest database market by the year 2005 and that all existing relational database vendors are scrambling to include object relational capabilities.
DBMS April 1998 – Modelling Object/Relational Databases
CASE Vendors are beginning to support Universal Servers, but the tools are still immature – by Seth Grimes.
This article discusses whether database design tools have been adequately adapted to help designers build database objects rather than just data structures. It describes what features are needed and then analysis three tools with object/relational modelling capabilities.
Object Relational DBMS’s are an incremental upgrade to Relational DBMS’s. The most important new features being; user-defined types, user-defined functions, infrastructures (indexing/access methods) and optimiser enhancements.
User defined types are described in three categories; distinct, opaque and rows.
Distinct types are those derived from other types but have their own domains, operations, functions and casts. Opaque types internal structures are defined to the DBMS along with their operations, functions and casts – once defined they can be used as a source type for row and distinct types. A row type is a collection of fields of other types and can include other row types nested among its fields. Collection types – Sets/Lists of built in types or user-defined types are another feature of object/relational innovation.
Modelling Problems – Object Relational models include both data and processes so now tools must model both the data and the processes with an ability to work with built in types and methods as well as user defined types and functions.
Modelling Methodologies – models help bridge the gap between business conceptual models, logical models and database implementations.
Relational and Object/Relational Modelling – Entity Relation is the traditional modelling approach which has been adopted for modelling object/relational databases but is week because it doesn’t capture processes.
Object Role Modelling – The formal object-role modelling language (FORMAL) takes a systematic approach to capturing business concepts.
Unified Modelling Language – Is a new modelling alternative. It is a complex approach and covers conceptual modelling with mappings into classes, components and distribution in the system and detailed design phase.
The three tools then described are OR-Compass, InfoModeller and Universal Modeller. The author then goes on to say that he believes ‘that InfoModeller is by far the best conceptual modelling tool’.
Introduction to Database Systems
Object Oriented and Object Relational DBMS’s
Module 9, Lecture 3
This presentation does not indicate who the author is nor the date it was written. It starts with motivation – the relational model is not good for non-administrative data, eg multimedia. Object Orientation models are complicated, but have some good points. The solution being to build DBMS’s based on the OO model.
Standard RDBMS can store binary large objects (BLOB’s) but queries are inefficient. OODBMS’s have persistence and collections but have no query support and ‘bugs’ destroy the persistent data.
Object-Relational – add object oriented features to SQL e.g. columns can be of new types (ADT’s), user defined methods and old SQL schemas still work etc. Relational vendors are moving to SQL3.
Complex Types – the user uses type constructors to create new types eg Setof(0, Arrayof() etc.
ADT’: User Defined Atomic Types – Built in types are limited, ORDBMS’s user defined types and methods are a built in type that cannot naturally be defined, these ADT’s need input and output methods.
Reference Types and Deref – every object has an OID that points to objects/references types e.g. ref(theatre_t).
A couple of SQL3 examples are then discussed and the notation explained.
New Features in SQL3 DML – Built in operations, operators for reference types, shorthand and user defined methods. But the final syntax has not yet been decided.
Path Expression – Nested row types, e.g. emp.spouse.name, can combine reference and row types – the path expressions describe the path to the data and can be rewritten as joins.
User Defined Methods - The ADT’s need methods to manipulate them. users write methods in languages such as C, compile them then register them with the ORDBMS to link the functions to the server.
Inheritance – Specialises types – methods also apply to subtypes, but can be redefined and/or define additional methods.
Modifications needed to support ORDBMS – parsing, query rewriting (e.g. turning paths into joins), optimisation (new algabra needed, WHERE clause expensive ect) OID generation, dymanic linking, support for untrusted methods and large objects, methods caching, Access Methods (e.g. indexing) and data layout (e.g. clustering of nested objects).
Stonebrakers Application Matrix is then discussed.
The summary says traditional SQL is limited, OODBMS’s are difficult to use, but ORDBMS’s have the best of both worlds – its catching on in industry, its easy for SQL folks to pick up, but still has growing pains. ORDBMS’s have many new features and the writer predicts ‘you will use an ORDBMS in the future!’
2.2 Conclusion
The first summary by Stonebraker is a good theoretical description of an Object Relational DBMS and is a good starting point for anyone wanting to learn about them, it is referenced in the majority of texts/articles that I have read on Object Relational DBMS. The second summary is about the products available (in 1998) to model/design object relational DBMS’s, however, it only has a small paragraph about the Unified Modelling Language (UML), which appears to me to have become the prominent language used to model DBMS’s and applications today. The final summary is more technical/practical description of object relational DBMS’s and the SQL3 language.
The purpose of producing these summaries is to learn more about DBMS’s, so I chose these articles/texts because they follow on from each other i.e. firstly Object Relational DBMS theory, then Object Relational DBMS design then finally Object Relational DBMS implementation.
