Hello Sir, I have developed a swing application in which database table is shown in the jtable.. of my jframe window.Now as per my requirement i have to add ,update,delete database values from jtable only so i added three buttons add,update,delete .. mydatabase contains five columns id,name,address,contact,email
Now, delete button is working properly. but add button is not working properly .
when we add new records into the database then last last record of particular row is not getting added into the database whereas all other records are getting added successfully..everytime...
and update button is not working atall..
Sir, I am posting my codes ... Plz have look and give me the solution of my problem...Thank you Sir.
print("code sample");
import javax.swing.*;
import java.awt.event.*;
import java.awt.*;
import javax.swing.event.*;
import javax.swing.table.*;
public class MyTableApp extends JFrame{
JTable myTable;
JButton update;
JButton insert;
JButton delete;
JPanel p;
MyTableModel tm;
JScrollPane myPane;
MyTableApp(){
try{
UIManager.setLookAndFeel("Interactive Jtable In Java Swing");
}
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 MyTableModel();
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 MyTableApp();
} // main
} //class
print("code sample");
// new class. This is the table model
import javax.swing.table.*;
import java.sql.*;
import java.util.Vector;
public class MyTableModel extends AbstractTableModel {
Connection con;
Statement stat;
ResultSet rs;
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;
MyTableModel(){
try{
Class.forName("com.mysql.jdbc.Driver");
}
catch (ClassNotFoundException e){
System.out.println("Cannot Load Driver!");
}
try{
String url = "jdbc:mysql://localhost:3306/techsoft";
String user = "root";
String pass = "techsoft";
con = DriverManager.getConnection(url,user,pass);
stat = con.createStatement();
rs = stat.executeQuery("SELECT * from pandey");
deletedKeys = new Vector();
newRows = new Vector();
myM = rs.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(rs.next()){
newRow = new Vector();
for(int i = 1; i <= li_cols; i++){
newRow.addElement(rs.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 = con.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);
System.out.println(curCol);
colNames.addElement(curCol);
// System.out.println(colNames);
}
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] + ",");
//System.out.println( InsertSQL.append(" VALUES(" + pkValues[i] + ","));
for(int c=1;c<dbValues.length;c++){
InsertSQL.append(dbValues[c]+",");
// System.out.println(InsertSQL.append(dbValues[c] + ","));
}
InsertSQL.replace(InsertSQL.length()-1,InsertSQL.length(),")");
// System.out.println(InsertSQL.replace(InsertSQL.length()-1,InsertSQL.length(),")"));
stat.executeUpdate(InsertSQL.toString());
ibRowInserted=true;
// System.out.println(InsertSQL);
}
} // 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] );
stat.executeUpdate(updateSQL.toString());
System.out.println(updateSQL);
} //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);
stat.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 a String
String blankString = new String();
newRowVect.addElement(blankString);
}
pos = classType.indexOf("Integer");
if(pos > 0){ // we have an Integer
Integer blankInt = new Integer("0");
newRowVect.addElement(blankInt);
}
pos = classType.indexOf("Boolean");
if(pos > 0){ // we have a 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);
}
}
Ads
