SQLContainer add/remove item with autoCommit==false

I’ve got a Table/SQLContainer which initially populates with two rows of data. If I add new rows, and then delete either of the original rows, all the new entries disappear and the vaadin client/server gets in a loop of continued updates – around 300 requests per second. The same thing can happen if I first delete an original row, then add a new row. My impression of autoCommit=false was that rows could be added/modified/removed and then committed at once later on.

Any help would be appreciated.

Code (Scala) below. I have also attached the source code.

Table/SQLContainer setup


lazy val table = new Table with ListableValue {

      val tq = new TableQuery("user", DB.connectionPool)
      tq.setVersionColumn("VERSION")
      val sqlContainer = new SQLContainer(tq)
      sqlContainer.setAutoCommit(false)
      setContainerDataSource(sqlContainer)

      setHeight("200px")
      setWidth("600px")
      setSelectable(true)
      setMultiSelect(true)
    }

Adding a row:


val addRowButton = new Button {
      setCaption("Add Row")
      addListener(new com.vaadin.ui.Button.ClickListener {
        def buttonClick(e: com.vaadin.ui.Button#ClickEvent) = {
          val rowId = table.sqlContainer.addItem()
          val rowItem = table.sqlContainer.getItem(rowId)
          rowItem.getItemProperty("EMAIL").setValue("someone@example.com")
          rowItem.getItemProperty("NAME").setValue("someone")
        }
      })
    }

Deleting a row:


  val deleteRowButton = new Button {
      setCaption("Delete Row")
      addListener(new com.vaadin.ui.Button.ClickListener {
        def buttonClick(e: com.vaadin.ui.Button#ClickEvent) = {
          table.valueList.foreach { v =>
            table.removeItem(v)
          }
        }
      })
    }

DB Setup


val connectionPool = new SimpleJDBCConnectionPool(
    "org.hsqldb.jdbc.JDBCDriver",
    "jdbc:hsqldb:mem:sqlcontainer/test",
    "SA", "", 2, 5)

  def initDatabase(): Unit = {

    if (dbCreated) {
      return
    }

    withStatement { s =>
      try {
        s.executeUpdate(createTable)
      } catch {
        case e: Exception => println(e.getMessage())
      }
    }

    try {
      create("one@example.com", "Person 1")
      create("two@example.com", "Person 2")
    } catch {
      case e: Exception => e.printStackTrace()
    }

    dbCreated = true
  }

  def create(email: String, name: String): Unit = {
    val sql = """
      insert into user(email, name,version)
      values (?,?,0)
      """
    val params = List(email, name)

    withPreparedStatement(sql, params) { s =>
      s.executeUpdate()
    }
  }

  val createTable =
    """
        CREATE TABLE user (
            id          BIGINT IDENTITY NOT NULL,
            email       VARCHAR(255) NOT NULL,
            name        VARCHAR(255) NOT NULL,
            version     INT
        );
      """

12709.txt (2.85 KB)
12710.txt (3.48 KB)

I got delete and add working this way in java:

DB Setup:

SimpleJDBCConnection = null;
TableQuery tableQuery = null;
SQLContainer container = null;

try {
   connection = new SimpleJDBCConnectionPool( Connection Params);
   tableQuery = new TableQuery("tablename", connection);
   container = new SQLContainer(tq);
} catch {
   //Error
}

Table table = new Table();
//table.setWidth("100%");
table.setContainerDataSource(container);

Save, edit, delete:

 /**
   * Saves changes made to the database
   */
    public void save() throws Exception {
        try {
            container.commit();
            tableQuery.beginTransaction();
            tableQuery.commit();
        } catch (UnsupportedOperationException ex) {
            Logger.getLogger(EditableDatabaseTableGenerator.class.getName()).
                    log(Level.SEVERE, "At Save", ex);
            throw new  Exception(ex.getMessage());
        } catch (SQLException ex) {
            Logger.getLogger(EditableDatabaseTableGenerator.class.getName()).
                    log(Level.SEVERE, "At Save", ex);
            throw new Exception(ex.getMessage());
        }
    }

    /**
     * Adds an item to the container
     */
    public void add() {
        container.addItem();
    }
    
    public void delete(Object rowId) {
        container.removeItem(rowId);
    }

My table is editable and user can edit data. I didn’t set autoCommit false either, but nothing got saved to the Database until save was called. I also had
this problem
.

From SQLContainer.addItem()
javadoc
:

I would check what row ID is returned by printing some logs.