This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes problem with sql statement in my setValueAt method Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "problem with sql statement in my setValueAt method" Watch "problem with sql statement in my setValueAt method" New topic
Author

problem with sql statement in my setValueAt method

randy butler
Greenhorn

Joined: Jan 20, 2009
Posts: 27
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);
Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
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

Joined: Jan 20, 2009
Posts: 27
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
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

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

Joined: Jan 20, 2009
Posts: 27
I'm trying to use a where clause on the id field but I am coming up short somewhere.
randy butler
Greenhorn

Joined: Jan 20, 2009
Posts: 27
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
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

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

Joined: Jan 20, 2009
Posts: 27
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: problem with sql statement in my setValueAt method
 
Similar Threads
IllegalStateException
database connection
properties file with SQL statements
Requesting Focus to next column in JTable
SDO and auto increment keys in DB2