// save a new row
// answer true, if saving succeeds
public boolean saveNewRow() {
// check the fields of the primary keys whether one is empty
boolean onePKempty = false;
int tmp;
for (tmp = 0; tmp < primaryKeys.length; tmp++) {
if (komponente[pkColIndex[tmp]].getContent().equals("")) {
onePKempty = true;
break;
}
}
if (onePKempty) {
komponente[pkColIndex[tmp]].requestFocus();
ZaurusEditor.printStatus("no value for primary key " + primaryKeys[tmp]);
return false;
} // end of if (onePKempty)
// build the insert string
String insertString = "INSERT INTO " + tableName + " VALUES(";
for (int j = 0; j < columns.length; j++) {
if (j > 0) {
insertString += ", ";
}
insertString += "?";
} // end of for (int i=0; i<columns.length; i++)
insertString += ")";
// System.out.println("insert string "+insertString);
try {
// fill the question marks
PreparedStatement ps = cConn.prepareStatement(insertString);
ps.clearParameters();
int i;
for (i = 0; i < columns.length; i++) {
ps.setObject(i + 1, komponente[i].getContent());
}
ps.executeUpdate();
ZaurusEditor.printStatus("new row was saved to table " + tableName);
return true;
} catch (SQLException e) {
ZaurusEditor.printStatus("SQL Exception: " + e.getMessage());
return false;
} // end of try-catch
}
// get and show the values of the actual row in the GUI
private void showAktRow() {
try {
pStmt.clearParameters();
for (int i = 0; i < primaryKeys.length; i++) {
pStmt.setObject(i + 1, resultRowPKs[aktRowNr][i]);
} // end of for (int i=0; i<primaryKeys.length; i++)
ResultSet rs = pStmt.executeQuery();
rs.next();
for (int i = 0; i < columns.length; i++) {
komponente[i].setContent(rs.getString(i + 1));
} // end of for (int i=0; i<primaryKeys.length; i++)
rs.close();
} catch (SQLException e) {
ZaurusEditor.printStatus("SQL Exception: " + e.getMessage());
} // end of try-catch
for (int i = 0; i < columns.length; i++) {
komponente[i].clearChanges();
}
}
private void fetchPrimaryKeys() {
Vector temp = new Vector(20);
try {
if (cConn == null) {
return;
}
if (dbmeta == null) {
dbmeta = cConn.getMetaData();
}
ResultSet colList = dbmeta.getPrimaryKeys(null, null, tableName);
while (colList.next()) {
temp.addElement(colList.getString("COLUMN_NAME"));
}
colList.close();
} catch (SQLException e) {
ZaurusEditor.printStatus("SQL Exception: " + e.getMessage());
}
primaryKeys = new String[temp.size()];
temp.copyInto(primaryKeys);
pkColIndex = new int[primaryKeys.length];
for (int i = 0; i < primaryKeys.length; i++) {
pkColIndex[i] = this.getColIndex(primaryKeys[i]);
} // end of for (int i=0; i<primaryKeys.length; i++)
}
// open the panel to insert a new row into the table
public void insertNewRow() {
// reset all fields
for (int i = 0; i < komponente.length; i++) {
komponente[i].clearContent();
} // end of for (int i=0; i<komponente.length; i++)
// reset the field for the primary keys
for (int i = 0; i < primaryKeys.length; i++) {
komponente[pkColIndex[i]].setEditable(true);
}
ZaurusEditor.printStatus("enter a new row for table " + tableName);
}
// fetch all column names
private void fetchColumns() {
Vector temp = new Vector(20);
Vector tempType = new Vector(20);
try {
if (cConn == null) {
return;
}
if (dbmeta == null) {
dbmeta = cConn.getMetaData();
}
ResultSet colList = dbmeta.getColumns(null, null, tableName, "%");
while (colList.next()) {
temp.addElement(colList.getString("COLUMN_NAME"));
tempType.addElement(new Short(colList.getShort("DATA_TYPE")));
}
colList.close();
} catch (SQLException e) {
ZaurusEditor.printStatus("SQL Exception: " + e.getMessage());
}
columns = new String[temp.size()];
temp.copyInto(columns);
columnTypes = new short[temp.size()];
for (int i = 0; i < columnTypes.length; i++) {
columnTypes[i] = ((Short) tempType.elementAt(i)).shortValue();
}
}
// fetch the imported keys i.e. columns which reference to foreign keys in other tables
private void fetchImportedKeys() {
Vector imKeys = new Vector(20);
Vector imKeyNames = null;
Vector refTabs = new Vector(20);
Vector refCols = new Vector(20);
Vector refColNames = null;
try {
if (cConn == null) {
return;
}
if (dbmeta == null) {
dbmeta = cConn.getMetaData();
}
ResultSet colList = dbmeta.getImportedKeys(null, null, tableName);
String pkTable, pkColumn, fkColumn;
int keySeq;
while (colList.next()) {
pkTable = colList.getString("PKTABLE_NAME");
pkColumn = colList.getString("PKCOLUMN_NAME");
fkColumn = colList.getString("FKCOLUMN_NAME");
keySeq = colList.getInt("KEY_SEQ");
if (keySeq == 1) {
if (imKeyNames != null) {
imKeys.addElement(imKeyNames);
refCols.addElement(refColNames);
} // end of if (exKeyNames != null)
imKeyNames = new Vector(20);
refColNames = new Vector(20);
refTabs.addElement(pkTable);
} // end of if (keySeq == 1)
imKeyNames.addElement(fkColumn);
refColNames.addElement(pkColumn);
}
if (imKeyNames != null) {
imKeys.addElement(imKeyNames);
refCols.addElement(refColNames);
} // end of if (exKeyNames != null)
colList.close();
} catch (SQLException e) {
ZaurusEditor.printStatus("SQL Exception: " + e.getMessage());
}
// System.out.println("Imported Keys of "+tableName);
int numberOfConstraints = imKeys.size();
importedKeys = new String[numberOfConstraints][];
imColIndex = new int[numberOfConstraints][];
refTables = new String[numberOfConstraints];
refColumns = new String[numberOfConstraints][];
refColIndex = new int[numberOfConstraints][];
for (int i = 0; i < numberOfConstraints; i++) {
Vector keys = (Vector) imKeys.elementAt(i);
Vector cols = (Vector) refCols.elementAt(i);
int numberOfKeys = keys.size();
importedKeys[i] = new String[numberOfKeys];
imColIndex[i] = new int[numberOfKeys];
refColumns[i] = new String[numberOfKeys];
refColIndex[i] = new int[numberOfKeys];
refTables[i] = (String) refTabs.elementAt(i);
// System.out.println("reference table "+refTables[i]);
for (int j = 0; j < numberOfKeys; j++) {
importedKeys[i][j] = (String) keys.elementAt(j);
imColIndex[i][j] = this.getColIndex(importedKeys[i][j]);
refColumns[i][j] = (String) cols.elementAt(j);
refColIndex[i][j] = this.getColIndex(refColumns[i][j], refTables[i]);
// System.out.println(" importedKeys "+importedKeys[i][j]+"(Index: "+imColIndex[i][j]+")
// refColumns "+refColumns[i][j]+"(Index: "+refColIndex[i][j]+")");
} // end of for (int j=0; j<numberOfKeys; j++)
}
}
// read all primary key values into resultRowPKs for the rows which meet the search condition i.
// e.
// which contains the search words
// answer the number of found rows, -1 if there is an SQL exception
public int searchRows(
String[] words, boolean allWords, boolean ignoreCase, boolean noMatchWhole) {
// System.out.print("search in " + tableName + " for: ");
// for (int i=0; i < words.length; i++) {
// System.out.print(words[i]+", ");
// }
// System.out.println("allWords = "+allWords+", ignoreCase = "+ignoreCase+", noMatchWhole=
// "+noMatchWhole);
String where = this.generateWhere(words, allWords, ignoreCase, noMatchWhole);
Vector temp = new Vector(20);
try {
Statement stmt = cConn.createStatement();
ResultSet rs =
stmt.executeQuery("SELECT " + this.getPrimaryKeysString() + " FROM " + tableName + where);
while (rs.next()) {
Object[] pkValues = new Object[primaryKeys.length];
for (int i = 0; i < primaryKeys.length; i++) {
pkValues[i] = rs.getObject(pkColIndex[i] + 1);
} // end of for (int i=0; i<primaryKeys.length; i++)
temp.addElement(pkValues);
}
rs.close();
} catch (SQLException e) {
ZaurusEditor.printStatus("SQL Exception: " + e.getMessage());
return -1;
} // end of try-catch
resultRowPKs = new Object[temp.size()][primaryKeys.length];
numberOfResult = temp.size();
for (int i = 0; i < primaryKeys.length; i++) {
for (int j = 0; j < temp.size(); j++) {
resultRowPKs[j][i] = ((Object[]) temp.elementAt(j))[i];
} // end of for (int j=0; j<temp.size(); j++)
} // end of for (int i=0; i<primaryKeys.length; i++)
// prepare statement for fetching the result rows for later use
String stmtString = "SELECT * FROM " + tableName;
try {
pStmt = cConn.prepareStatement(stmtString + this.generatePKWhere());
} catch (SQLException e) {
System.out.println("SQL Exception: " + e.getMessage());
} // end of try-catch
// System.out.println("prepared statement: "+stmtString);
if (numberOfResult > 0) {
this.disablePKFields();
aktRowNr = 0;
this.showAktRow();
} // end of if (numberOfResult > 0)
// System.out.println("number of rows: "+numberOfResult);
return numberOfResult;
}
// save all changes which are be made in the textfelder to the database
// answer true, if the update succeeds
public boolean saveChanges() {
// the initial settings of the textfields counts with one
// so a real change by the user needs as many changes as there are columns
// System.out.print("Anderungen in den Feldern: ");
// there are changes to the database
// memorize all columns which have been changed
int[] changedColumns = new int[columns.length];
int countChanged = 0;
// build the update string
String updateString = "";
for (int i = 0; i < columns.length; i++) {
if (komponente[i].hasChanged()) {
if (updateString != "") {
updateString += ", ";
}
updateString += columns[i] + "=?";
changedColumns[countChanged++] = i;
}
} // end of for (int i=0; i<columns.length; i++)
if (countChanged > 0) {
updateString = "UPDATE " + tableName + " SET " + updateString + this.generatePKWhere();
// System.out.println("update "+updateString);
try {
// fill the question marks
PreparedStatement ps = cConn.prepareStatement(updateString);
ps.clearParameters();
int i;
for (i = 0; i < countChanged; i++) {
ps.setObject(i + 1, komponente[changedColumns[i]].getContent());
// System.out.print(" changed feld "+komponente[changedColumns[i]].getContent());
} // end of for (int i=0; i<countChanged; i++)
// System.out.println();
for (int j = 0; j < primaryKeys.length; j++) {
ps.setObject(i + j + 1, resultRowPKs[aktRowNr][j]);
} // end of for (int i=0; i<primaryKeys.length; i++)
ps.executeUpdate();
ZaurusEditor.printStatus("changed row was saved to table " + tableName);
return true;
} catch (SQLException e) {
ZaurusEditor.printStatus("SQL Exception: " + e.getMessage());
return false;
} // end of try-catch
} else {
// System.out.println("no changes");
return true;
} // end of if (changed)
}
// delete current row, answer special action codes, see comment below
public int deleteRow() {
// build the delete string
String deleteString = "DELETE FROM " + tableName + this.generatePKWhere();
// System.out.println("delete string "+deleteString);
try {
// fill the question marks
PreparedStatement ps = cConn.prepareStatement(deleteString);
ps.clearParameters();
int i;
for (int j = 0; j < primaryKeys.length; j++) {
ps.setObject(j + 1, resultRowPKs[aktRowNr][j]);
} // end of for (int i=0; i<primaryKeys.length; i++)
ps.executeUpdate();
} catch (SQLException e) {
ZaurusEditor.printStatus("SQL Exception: " + e.getMessage());
return 0;
} // end of try-catch
// delete the corresponding primary key values from resultRowPKs
numberOfResult--;
for (int i = aktRowNr; i < numberOfResult; i++) {
for (int j = 0; j < primaryKeys.length; j++) {
resultRowPKs[i][j] = resultRowPKs[i + 1][j];
}
}
// there are the following outcomes after deleting aktRowNr:
/*
A B C D E F
no rows left J N N N N N
one row left - J N J N N
deleted row was the last row - J J N N N
deleted row was the pre-last - - - - J N
first D X + D + *
. D X X D D
. D X +
last X
new numberOfResult 0 1 2 1 2 2
old aktRowNr 0 1 2 0 1 0
D - deleted row
X - any one row
+ - one or more rows
* - zero or more rows
*/
// A. return to the search panel and tell 'last row deleted' on the status line
// B. show the previous row and disable previous button
// C. show the previous row as akt row
// D. show akt row and disable next button
// E. show akt row and disable next button
// F. show akt row
// these actions reduce to the following actions for ZaurusEditor:
// 1. show search panel
// 2. disable previous button
// 3. disable next button
// 4. do nothing
// and 1,2,3,4 are the possible return codes
int actionCode;
if (numberOfResult == 0) {
// case A
actionCode = 1;
ZaurusEditor.printStatus("Last row was deleted.");
return actionCode;
} else if (numberOfResult == aktRowNr) {
// B or C
// new aktRow is previous row
aktRowNr--;
if (aktRowNr == 0) {
// B
actionCode = 2;
} else {
// C
actionCode = 4;
} // end of if (aktRowNr == 0)
} else {
// D, E, F
if (numberOfResult >= 2 && aktRowNr < numberOfResult - 1) {
// F
actionCode = 4;
} else {
actionCode = 3;
} // end of else
}
this.showAktRow();
ZaurusEditor.printStatus("Row was deleted.");
return actionCode;
}
