Win a copy of JDBC Workbook this week in the JDBC and Relational Databases forum
or A Day in Code in the A Day in Code forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • salvin francis
  • fred rosenberger

problem with sql statement in my setValueAt method

 
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The following sql statement changes every entry in the current column of the cell selected by the user. I want only the current cell being edited to change.

String sql = "update Parts set "+this.getColumnName(column)+" = "+dbRepresentation(column, value)+" where id = " + this.getColumnName(0);
 
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry, your question is too vague. I have no idea what you mean with "setValueAt method" and with "current cell". Please elaborate the technical problem. What's the functional requirement? What happens? What happens not?
 
randy butler
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
functional requirement
for the user to be able to edit a cell in the JTable
JTable gets its data from mysql database and I am able to view the data from the database with no problem
I use the setValueAt() method in my tablemodel to update the database



public void setValueAt(Object value, int row, int column) {
((String[]) ResultSets.get(row)) [column] = (String) value;
mdbc = new MyDBConnectiion();
mdbc.init();
Connection con = mdbc.getMyConnection();
try{
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
}catch(SQLException e){
e.printStackTrace();
}

try{

String sql = "update Parts set "+this.getColumnName(column)+" = "+dbRepresentation(column, value)+" where id = " + this.getColumnName(0);
int update = stmt.executeUpdate(sql);

}catch(SQLException e){
e.printStackTrace();
System.err.println("Update failed");
}

}

What happens

is when the user edits one cell the entire column of the edited cell changes to the edit.

What I want to happen

is when the user selects a cell is being edited only that cell value changes in the database.

I hope this is clearer and thanks
 
Marshal
Posts: 25594
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, that's right, the UPDATE statement you have there will update every row of the table. If you want to update specific rows, use a WHERE clause to specify which rows.

Edit: and you should really use a PreparedStatement so you don't have to mess with string concatenations (and later with escaping of quotes when you find they are a problem).
 
randy butler
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm trying to use a where clause on the id field but I am coming up short somewhere.
 
randy butler
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
what if I use the ResultSet to update a row in my database something like this but I'm not sure were to go from here
 
Paul Clapham
Marshal
Posts: 25594
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're coming up short by just plain failing to use a WHERE clause. At least in that last post, you don't have any WHERE in your SQL.

The first thing that needs to happen is for each row in your table to correspond to a row in the database. If you don't have that connection then your design is broken. So let's assume you have that. The next thing you need is to be able to find the primary key for the row in the database table, given the row in the GUI table. If you can't do that then, again, your design is broken. A common way to do that is to have the key field (or fields) be a column of your GUI table.

Now generate an SQL update which looks roughly like this:

Modify that to suit your actual data. And do use a PreparedStatement, you'll find it a lot easier to deal with.
 
randy butler
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok so
PreparedStatement updateP = con.prepareStatement("update Parts set column2 = value where id = currentrow);

is this what your talking about?

connection does work i am able to insert and delete rows from the database.

thank you
 
There's a way to do it better - find it. -Edison. A better tiny ad:
Devious Experiments for a Truly Passive Greenhouse!
https://www.kickstarter.com/projects/paulwheaton/greenhouse-1
    Bookmark Topic Watch Topic
  • New Topic