help updating database!!
Hi Gud PM
i would like to ask some question about the code below.
why is it when i click theupdate button after i add the second data it does not update the database but the first data i add can be update.
heres the code:
import javax.swing.*;
import java.awt.event.*;
import java.awt.*;
import javax.swing.event.*;
import javax.swing.table.*;
publicclass OpdrachtVriendenextends JFrame
{
JTable myTable;
JButton update;
JButton insert;
JButton delete;
JPanel p;
OpdrachtVriendenTableModel tm;
JScrollPane myPane;
OpdrachtVrienden()
{
try
{
UIManager.setLookAndFeel("com.sun.java.swing.plaf.windows.WindowsLookAndFeel");
}
catch(Exception e)
{
System.out.println("Error on look and feel");
}
update =new JButton("Update");
insert =new JButton("Add");
delete =new JButton("Delete");
p =new JPanel();
tm =new OpdrachtVriendenTableModel();
myTable =new JTable(tm);
myPane =new JScrollPane(myTable,
JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED,
JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED);
myTable.setSelectionForeground(Color.white);
myTable.setSelectionBackground(Color.red);
myTable.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
p.add(myPane);
p.add(update);
p.add(insert);
p.add(delete);
update.addActionListener(new ActionListener()
{
publicvoid actionPerformed(ActionEvent e)
{
tm.updateDB();
}
});
insert.addActionListener(new ActionListener()
{
publicvoid actionPerformed(ActionEvent e)
{
tm.addRow();
myTable.setEditingRow(tm.getRowCount());
myTable.setRowSelectionInterval(tm.getRowCount()-1,tm.getRowCount()-1);
}
});
delete.addActionListener(new ActionListener()
{
publicvoid actionPerformed(ActionEvent e)
{
int rowToDelete = myTable.getSelectedRow();
tm.deleteRow(rowToDelete);
myTable.setEditingRow(rowToDelete -1);
myTable.setRowSelectionInterval(rowToDelete -1,rowToDelete -1);
}
});
this.addWindowListener(new WindowAdapter()
{
publicvoid windowClosing(WindowEvent e)
{
System.exit(0);
}
});// end windowlistener
this.setContentPane(p);
this.setVisible(true);
this.pack();
}// constructor
publicstaticvoid main (String args[])
{
new OpdrachtVrienden();
}// main
}//class
-
import javax.swing.table.*;
import java.sql.*;
import java.util.Vector;
publicclass OpdrachtVriendenTableModelextends AbstractTableModel
{
Connection link;
Statement statement;
ResultSet results;
int li_cols = 0;
Vector allRows;
Vector row;
Vector newRow;
Vector colNames;
String dbColNames[];
String pkValues[];
String tableName;
ResultSetMetaData myM;
String pKeyCol;
Vector deletedKeys;
Vector newRows;
boolean ibRowNew =false;
boolean ibRowInserted =false;
OpdrachtVriendenTableModel()
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e)
{
System.out.println("Cannot Load Driver!");
}
try
{
link = DriverManager.getConnection("jdbc:odbc:Enroll","root","");
statement = link.createStatement();
results = statement.executeQuery("SELECT * FROM studentinfo");// Eventueel nog aanpassen
deletedKeys =new Vector();
newRows =new Vector();
myM = results.getMetaData();
tableName = myM.getTableName(1);
li_cols = myM.getColumnCount();
dbColNames =new String[li_cols];
for(int col = 0; col < li_cols; col ++)
{
dbColNames[col] = myM.getColumnName(col + 1);
}
allRows =new Vector();
while(results.next())
{
newRow =new Vector();
for(int i = 1; i <= li_cols; i++)
{
newRow.addElement(results.getObject(i));
}// for
allRows.addElement(newRow);
}// while
}
catch(SQLException e)
{
System.out.println(e.getMessage());
}
}
public Class getColumnClass(int col)
{
return getValueAt(0,col).getClass();
}
publicboolean isCellEditable(int row,int col)
{
if (ibRowNew)
{
returntrue;
}
if (col == 0)
{
returnfalse;
}
else
{
returntrue;
}
}
public String getColumnName(int col)
{
return dbColNames[col];
}
publicint getRowCount()
{
return allRows.size();
}
publicint getColumnCount()
{
return li_cols;
}
public Object getValueAt(int arow,int col)
{
row = (Vector) allRows.elementAt(arow);
return row.elementAt(col);
}
publicvoid setValueAt(Object aValue,int aRow,int aCol)
{
Vector dataRow = (Vector) allRows.elementAt(aRow);
dataRow.setElementAt(aValue, aCol);
fireTableCellUpdated(aRow, aCol);
}
publicvoid updateDB()
{
String updateLine[] =new String[dbColNames.length];
try
{
DatabaseMetaData dbData = link.getMetaData();
String catalog;// Get the name of all of the columns for this table
String curCol;
colNames =new Vector();
ResultSet rset1 = dbData.getColumns(null,null,tableName,null);
while (rset1.next())
{
curCol = rset1.getString(4);
colNames.addElement(curCol);
}
rset1.close();
pKeyCol = colNames.firstElement().toString();
// Go through the rows and perform INSERTS/UPDATES/DELETES
int totalrows;
totalrows = allRows.size();
String dbValues[];
Vector currentRow =new Vector();
pkValues =new String[allRows.size()];
// Get column names and values
for(int i=0;i < totalrows;i++)
{
currentRow = (Vector) allRows.elementAt(i);
int numElements = currentRow.size();
dbValues =new String[numElements];
for(int x = 0; x < numElements; x++)
{
String classType = currentRow.elementAt(x).getClass().toString();
int pos = classType.indexOf("String");
if(pos > 0)
{// we have a String
dbValues[x] ="'" + currentRow.elementAt(x) +"'";
updateLine[x] = dbColNames[x] +" = " +"'" + currentRow.elementAt(x) +"',";
if (dbColNames[x].toUpperCase().equals(pKeyCol.toUpperCase()))
{
pkValues[i] = currentRow.elementAt(x).toString() ;
}
}
pos = classType.indexOf("Integer");
if(pos > 0)// we have an Integer
{
dbValues[x] = currentRow.elementAt(x).toString();
if (dbColNames[x].toUpperCase().equals(pKeyCol.toUpperCase()))
{
pkValues[i] = currentRow.elementAt(x).toString();
}
else
{
updateLine[x] = dbColNames[x] +" = " + currentRow.elementAt(x).toString() +",";
}
}
pos = classType.indexOf("Boolean");
if(pos > 0)// we have a Boolean
{
dbValues[x] = currentRow.elementAt(x).toString();
updateLine[x] = dbColNames[x] +" = " + currentRow.elementAt(x).toString() +",";
if (dbColNames[x].toUpperCase().equals(pKeyCol.toUpperCase()))
{
pkValues[i] = currentRow.elementAt(x).toString() ;
}
}
}// For Loop
// If we are here, we have read one entire row of data. Do an UPDATE or an INSERT
int numNewRows = newRows.size();
int insertRow = 0;
boolean newRowFound;
for (int z = 0;z < numNewRows;z++)
{
insertRow = ((Integer) newRows.get(z)).intValue();
if(insertRow == i+1)
{
StringBuffer InsertSQL =new StringBuffer();
InsertSQL.append("INSERT INTO " + tableName +" (");
for(int zz=0;zz<=dbColNames.length-1;zz++)
{
if (dbColNames[zz] !=null)
{
InsertSQL.append(dbColNames[zz] +",");
}
}// Strip out last comma
InsertSQL.replace(InsertSQL.length()-1,InsertSQL.length(),")");
InsertSQL.append(" VALUES(" + pkValues[i] +",");
for(int c=1;c < dbValues.length;c++)
{
InsertSQL.append(dbValues[c] +",");
}
InsertSQL.replace(InsertSQL.length()-1,InsertSQL.length(),")");
statement.executeUpdate(InsertSQL.toString());
ibRowInserted=true;
}
}// End of INSERT Logic
// If row has not been INSERTED perform an UPDATE
if(ibRowInserted ==false)
{
StringBuffer updateSQL =new StringBuffer();
updateSQL.append("UPDATE " + tableName +" SET ");
for(int z=0;z<=updateLine.length-1;z++)
{
if (updateLine[z] !=null)
{
updateSQL.append(updateLine[z]);
}
}
// Replace the last ',' in the SQL statement with a blank. Then add WHERE clause
updateSQL.replace(updateSQL.length()-1,updateSQL.length()," ");
updateSQL.append(" WHERE " + pKeyCol +" = " + pkValues[i] );
statement.executeUpdate(updateSQL.toString());
}//for
}
}
catch(Exception ex)
{
System.out.println("SQL Error! Cannot perform SQL UPDATE " + ex.getMessage());
}
// Delete records from the DB
try
{
int numDeletes = deletedKeys.size();
String deleteSQL;
for(int i = 0; i < numDeletes;i++)
{
deleteSQL ="DELETE FROM " + tableName +" WHERE " + pKeyCol +" = " +
((Integer) deletedKeys.get(i)).toString();
System.out.println(deleteSQL);
statement.executeUpdate(deleteSQL);
}
// Assume deletes where successful. Recreate Vector holding PK Keys
deletedKeys =new Vector();
}
catch(Exception ex)
{
System.out.println(ex.getMessage());
}
}
publicvoid deleteRow(int rowToDelete)
{
// Mark row for a SQL DELETE from the Database
Vector deletedRow = (Vector) allRows.get(rowToDelete);
Integer pkKey = (Integer) deletedRow.get(0);
deletedKeys.add(pkKey);
allRows.remove(rowToDelete);
fireTableRowsDeleted(rowToDelete,rowToDelete);
}
publicvoid addRow()
{
// Mark the row for a SQL INSERT in the Database
newRows.add(new Integer(allRows.size() +1));
// Get the total number of rows in the Vector
int rowNumber = allRows.size();
int pos;
// Get what a row looks like
int numElements = newRow.size();
Vector newRowVect =new Vector();
for(int i = 0; i < numElements; i++)
{
String classType = newRow.elementAt(i).getClass().toString();
pos = classType.indexOf("String");
if(pos > 0)// we have an String
{
String blankString =new String();
newRowVect.addElement(blankString);
}
pos = classType.indexOf("Integer");
if(pos > 0)// we have a String
{
Integer blankInt =new Integer("0");
newRowVect.addElement(blankInt);
}
pos = classType.indexOf("Boolean");
if(pos > 0)// we have an Boolean
{
Boolean blankBool =new Boolean(false);
newRowVect.addElement(blankBool);
}
}
allRows.addElement(newRowVect);
ibRowNew =true;
this.isCellEditable(allRows.size(),0);
System.out.println(allRows.size());
fireTableRowsInserted(rowNumber,rowNumber);
}
}
Message was edited by:
lesbon
