Update data in Output JTable
Hi, i used a GUI to Access my Database. I want to update the data in the output table, but everytime i change it, the next time i start the program it shows the old data. I used ResultSet.CONCUR_UPDATABLE but i guess it's not enough?
Could somebody help me please, thanks in advance.
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.sql.*;
import java.util.*;
publicclass OpdrachtVriendenextends JFrame
{
staticprivate Connection link;
private Statement statement;
private ResultSet results;
private JTable table;
private JScrollPane scroller;
privatefinal String[] heading =
{"Naam","Adres","Postcode","Woonplaats"};
private Vector<String> heads;
private Vector<Object> row;
private Vector<Vector><Object>> rows;
publicstaticvoid main(String[] args)
{
OpdrachtVrienden frame =new OpdrachtVrienden();
frame.setSize(600,200);
frame.setVisible(true);
frame.addWindowListener(
new WindowAdapter()
{
publicvoid windowClosing(WindowEvent winEvent)
{
try
{
link.close();
System.exit(0);
}
catch(SQLException sqlEx)
{
System.out.println(
"* Error on closing connection! *");
}
}
}
);
}
public OpdrachtVrienden()
{
setTitle("Vrienden van Silvano");
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
link = DriverManager.getConnection(
"jdbc:odbc:Vrienden","","");
statement = link.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
results = statement.executeQuery(
"SELECT * FROM NAW");
heads =new Vector<String>();
for (int i=0; i<heading.length; i++)
{
heads.add(heading[i]);
}
rows =new Vector><Vector><Object>>();
while (results.next())
{
row =new Vector<Object>();
row.add(results.getString(1));
row.add(results.getString(2));
row.add(results.getString(3));
row.add(results.getString(4));
rows.add(row);
}
table =new JTable(rows,heads);
scroller =new JScrollPane(table);
add(scroller, BorderLayout.CENTER);
}
catch(ClassNotFoundException cnfEx)
{
System.out.println("* Unable to load driver! *");
System.exit(1);
}
catch(SQLException sqlEx)
{
System.out.println("* SQL error! *");
System.exit(1);
}
}
}
Thanks for your reply awyork. After I learned about the important use of a TableModel and ActionListener to perform updates in JTable after reading a few more tutorials I reencode my program.
Unfortunately It still gives a error when i try to update, add or delete a record.
Could you please check the code again or somebody else perhaps, thanks for your time.
Here is the code:
import javax.swing.*;
import java.awt.event.*;
import java.awt.*;
import javax.swing.event.*;
import javax.swing.table.*;
public class OpdrachtVrienden extends 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()
{
public void actionPerformed(ActionEvent e)
{
tm.updateDB();
}
});
insert.addActionListener(new ActionListener()
{
public void actionPerformed(ActionEvent e)
{
tm.addRow();
myTable.setEditingRow(tm.getRowCount());
myTable.setRowSelectionInterval(tm.getRowCount()-1,tm.getRowCount()-1);
}
});
delete.addActionListener(new ActionListener()
{
public void actionPerformed(ActionEvent e)
{
int rowToDelete = myTable.getSelectedRow();
tm.deleteRow(rowToDelete);
myTable.setEditingRow(rowToDelete -1);
myTable.setRowSelectionInterval(rowToDelete -1,rowToDelete -1);
}
});
this.addWindowListener(new WindowAdapter()
{
public void windowClosing(WindowEvent e)
{
System.exit(0);
}
}); // end windowlistener
this.setContentPane(p);
this.setVisible(true);
this.pack();
} // constructor
public static void main (String args[])
{
new OpdrachtVrienden();
} // main
} //class
// This is the table model
import javax.swing.table.*;
import java.sql.*;
import java.util.Vector;
public class OpdrachtVriendenTableModel extends 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:Vrienden","","");
statement = link.createStatement();
results = statement.executeQuery("SELECT * FROM NAW"); // 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();
}
public boolean isCellEditable(int row, int col)
{
if (ibRowNew)
{
return true;
}
if (col == 0)
{
return false;
}
else
{
return true;
}
}
public String getColumnName(int col)
{
return dbColNames[col];
}
public int getRowCount()
{
return allRows.size();
}
public int getColumnCount()
{
return li_cols;
}
public Object getValueAt(int arow, int col)
{
row = (Vector) allRows.elementAt(arow);
return row.elementAt(col);
}
public void setValueAt(Object aValue, int aRow, int aCol)
{
Vector dataRow = (Vector) allRows.elementAt(aRow);
dataRow.setElementAt(aValue, aCol);
fireTableCellUpdated(aRow, aCol);
}
public void 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());
}
}
public void 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);
}
public void 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);
}
}
For example this is the error when i try to delete a record.
http://i19.tinypic.com/4lyjokj.jpg
