title: Update table based on join query using cacheRowSet?
body: <p>Is it possible to update a junction table by using a join query as refrernce?</p>
  
  <p>I'm using a Jtable to display data from the database. The jtable is loaded on a Jframe using the CacheRowSet</p>
  
  <pre><code>jframeconstructor(){
   ...
   CachedRowSet myCachedRowSet = getContentsOfTable();// performs query to load table
   myTableModel = new TheTableModel(myCachedRowSet);// class extends the AbstractTableModel to enable editing 
   jtable.setModel(TheTableModel);
   ...
   }
  
  
  
  public CachedRowSet getContentsTable() throws SQLException {
  CachedRowSet crs = null;
  int [] key={1,2};
  try {
    conn = s.getConnection(); //get connection from session
    conn.setAutoCommit(false);//set auto commit to false 
    crs = new CachedRowSetImpl();
    crs.setType(ResultSet.TYPE_SCROLL_INSENSITIVE);
    crs.setConcurrency(ResultSet.CONCUR_UPDATABLE);
    crs.setUsername(s.getM_sappuser());
    crs.setPassword(s.getM_spassword());
  
    if (this.s.getM_database().equals("mysql")) {
      crs.setUrl(s.getM_surl()
              + "?relaxAutoCommit=true");
    } else
    { 
      crs.setUrl(s.getM_surl());
  
    }
  
  
    crs.setCommand("SELECT TICKET_id, line TICKETNumber, item_id, price, Units FROM TICKETLINES ;");
    crs.setKeyColumns(key);
    crs.execute();
  
  } catch (SQLException e) {
   JOptionPane.showMessageDialog(null, e);
  }
  return crs;
  </code></pre>
  
  <p>}</p>
  
  <p>I'm able to edit the product_id from the swing UI using this method in the TheTableModel class: </p>
  
  <pre><code>   public void setValueAt(Object value, int row, int column) {
     System.out.println("Calling setValueAt row " + row + ", column " +    column + ", Value :"+ value);
     try {
     //get the value  and update cacheRowSet
        this.coffeesRowSet.absolute( row + 1 );
        this.coffeesRowSet.updateObject((column + 1), value);
        this.coffeesRowSet.updateRow();
        this.coffeesRowSet.setTableName("TICKETLINES");
  
        this.coffeesRowSet.acceptChanges(s.getConnection());
        this.coffeesRowSet.refreshRow();
        } catch (SQLException ex) {
   Logger.getLogger(CoffeesTableModel.class.getName()).log(Level.SEVERE, null, ex);
     }
    fireTableCellUpdated(row, column);
    }
  </code></pre>
  
  <p>I want the table to display the item names rather than the item id . So if I perform a join on three tables is it possible to edit (update) records from the result table such that they are relected on the juction table? </p>
  
  <p>I mean I have this junction table</p>
  
  <pre><code>TICKETLINES
  -----------------------------------------------------------
  Ticketid    |line number |item_id |description |units price
  ---+--------+------------+--------+------------+----------- 
     |        |            |        |            |
  </code></pre>
  
  <p>and two other tables  </p>
  
  <p>ITEM</p>
  
  <hr>
  
  <pre><code>id |name |description |price 
  ---+-----+------------+-----
     |     |            |     
  
  TICKET
  -------------------
  id |ticketnumber 
  ---+-----+--------- 
     |     |        
  </code></pre>
  
  <p>After performing an inner join on the tables </p>
  
  <pre><code> SELECT t.ticketnumber AS TicketNumber, i.name as ProductName, tl.price as PRICE, tl.line FROM TICKETS AS T JOIN TICKETLINES AS tl ON tl.TICKET= t.id JOIN ITEM AS i on tl.product=i.id ORDER BY t.ticketnumber,tl.line ;
  </code></pre>
  
  <p>I get a result table, if the first record is </p>
  
  <pre><code>ticketnumber ProductName price   line
  ------------+-----------+-------+-------
     1        | unicorn   |     0 |    0
  </code></pre>
  
  <p>can I change the product name to say "pink elephant" (assuming pink elephant has an id in the item table) on this resultSet and then have my ticketline item_id updated ?  (Actually, I tried it and I got conflicts while sync so I'm just trying to know whether such a thing is actually possible) </p>