A tip: If you want to use TableQuery with a DB view...

If you try, because views don’t have PKs and TableQuery demands it, in fetchMetaData(), it will barf

A solution is to clone TableQuery, then make fetchMetaData public

then when calling (I’m using Scaladin BTW)

val tq=new mine.TableQuery("userordersview",AdminUI.pool){
      override def fetchMetaData()={
       primaryKeyColumns=List("ID").asJava        
       super.fetchMetaData()
      } 

Its a pity TableQuery.fetchMetaData() is private, as there would be no need to create own copy. Would be nice if they could change it.

And why would I want to use a view inside a TableQuery? So that I can have a complex read only query, without having to use FreeFormQuery, which lacks sorting/filtering

You have absolutely no idea how much you just helped me. Since i started using Vaadin I always had the problem that pretty much everytime I wanted to use a Table the data backend was a View. As a result i was always stuck with using FreeformQueries and handlich sorting and filtering manually.
I didn’t even think about looking inside the TableQuery code to “fix” my problem.

Btw. The “normal” Vaadin versino looks sort of like:

TableQuery query = new TableQuery("someview",yourconnectionpool){ public void fetchMetaData() { primaryKeyColumns= new ArrayList<String>(); primaryKeyColumns.add("ID"); super.fetchMetaData(); }; } ; I had to make primaryKeyColumns public as well as fetchMetaData and for some reason i also had to clone AbstractTransactionalQuery as super(connectionPool is private in the original)

+1 for changing it in native Vaadin to allow Views as otherwise you have to always check TableQuery when upgrading versions.

I had to clone AbstractTransactionalQuery too as well as make primaryKeyColumns public. Forgot to mention.

So its not just you.

How can I use this solution in a window class?

super is unknown and when i replace this with “this” then i get the primary key error.

Here is a part of my code:

public class SucheStoff extends Window
{
public SimpleJDBCConnectionPool connPool;
public ArrayList primaryKeyColumns;
public TableQuery query;

public SucheStoff()
{
    ...
    query = new TableQuery("vsuchestoff",connPool){
        public void fetchMetaData() {
            primaryKeyColumns= new ArrayList<String>();     
            primaryKeyColumns.add("id");
            super.fetchMetaData();
        };
    };

Two things:
A: Did you properly copy both the TableQuery class and the AbstractTransactionalQuery class into a package inside your project and made fetchMetaData and primaryKeyColumns in the copied classes public?
B: Did you make sure that in your Window class you are not importing the com.vaadin TableQuery but your copied and changed version instead?

I have a problem to add this class to my project.

Sorry for the stupid question, but what make I wrong?

I have make a new package called “view” and have add the TableQuery.class, now I get an Error from Eclipse.

I have updated the project and run as Maven → install, but nothing helps.

See Attachment
21017.png

You also need the AbstractTransactionalQuery class in the same package.
Also you shouldn’t copy the .class file but the non.compiled .java Source file.
And as it seems you’re also german. Here the explanation in german:
In dem gleichen Paket, in welchem die TableQuery-Klasse liegt muss auch eine Kopie von AbstractTransactionalQuery rein. Bei dieser muss aber nichts verändert werden. Es geht nur darum, dass TableQuery nach der Klasse in diesem Paket sucht.
Außerdem brauchst du die .java Source Datei und nicht die kompilierte .class Datei. Erstell dafür einfach eine neue Klasse mit Eclipse (nenn sie z.B. auch TableQuery. Dann öffne den Source der TableQuery Klasse (Entweder über Eclipse oder von hier:
https://dev.vaadin.com/svn/addons/SQLContainer/trunk/src/com/vaadin/addon/sqlcontainer/query/TableQuery.java
). Dann mach das gleiche mit AbstractTransactionalQuery.
Dann ändere die TableQuery-Klasse ab, also das public machen der einen Variable und der Methode. Danach solltest du die neue Klasse benutzen können.

For any non-german speakers. The majority of this text is already explained above.

Sorry but in my project it doesn’t work.

when I use TabelQuery1 I get the following errors:
Aug 17, 2015 12:56:53 PM com.vaadin.server.DefaultErrorHandler doDefault
SCHWERWIEGEND:
java.lang.StackOverflowError
at java.util.Arrays.copyOf(Arrays.java:3181)
at java.util.ArrayList.grow(ArrayList.java:261)
at java.util.ArrayList.ensureExplicitCapacity(ArrayList.java:235)
at java.util.ArrayList.ensureCapacityInternal(ArrayList.java:227)
at java.util.ArrayList.add(ArrayList.java:458)

When I use TableQuery I get this errors:
Aug 17, 2015 1:03:17 PM com.vaadin.server.DefaultErrorHandler doDefault
SCHWERWIEGEND:
java.lang.StackOverflowError
at java.util.Arrays.copyOf(Arrays.java:3181)
at java.util.ArrayList.grow(ArrayList.java:261)
at java.util.ArrayList.ensureExplicitCapacity(ArrayList.java:235)
at java.util.ArrayList.ensureCapacityInternal(ArrayList.java:227)
at java.util.ArrayList.add(ArrayList.java:458)
at ego.gefahrstoffe.windows.SucheStoff$1.fetchMetaData(SucheStoff.java:73)
at ego.gefahrstoffe.windows.SucheStoff$1.fetchMetaData(SucheStoff.java:74)

The AbstractTransactionalQuery query is not used in the class TableQuery

21019.png
21020.zip (11.4 KB)

Sorry my bad. It seems like the TableQuery Source code i linked to was old…Might have been vaadin 6 Source code. I forgot that they switch their source repository to github.

https://github.com/vaadin/vaadin/tree/master/server/src/com/vaadin/data/util/sqlcontainer/query

There you can find the current version of TableQuery and AbstractTransactionalQuery…

When I use this version, i get a few errors, see attachment …

21021.png

Something is weird with what you’re doing. I just made a quick test: I copied all of the code in TableQuery in my new java class file then pressed Strg+Umschalt+O aka Ctrl+Shift+O and it worked.
Are you using the latest Vaadin 7 version? (Or at least a close-to-latest version)

I use vaadin 7.4.3, should I use another version?

Here are the pom settings:

[i]

<vaadin.version>7.4.3</vaadin.version>
<vaadin.plugin.version>${vaadin.version}</vaadin.plugin.version>
<jetty.plugin.version>9.2.3.v20140905</jetty.plugin.version>
<project.source.version>1.8</project.source.version>
<project.target.version>1.8</project.target.version>
<project.encoding>UTF-8</project.encoding>

     <maven.compiler.source>1.8</maven.compiler.source>
       <maven.compiler.target>1.8</maven.compiler.target>
</properties>

<repositories>
    <repository>
        <id>vaadin-addons</id>
        <url>http://maven.vaadin.com/vaadin-addons</url>
    </repository>
    <repository>
        <id>vaadin-snapshots</id>
        <url>https://oss.sonatype.org/content/repositories/vaadin-snapshots/</url>
        <releases>
            <enabled>false</enabled>
        </releases>
        <snapshots>
            <enabled>true</enabled>
        </snapshots>
    </repository>
</repositories>

<dependencies>
    <dependency>
        <groupId>javax.servlet</groupId>
        <artifactId>javax.servlet-api</artifactId>
        <version>3.0.1</version>
        <scope>provided</scope>
    </dependency>
    <dependency>
        <groupId>com.vaadin</groupId>
        <artifactId>vaadin-server</artifactId>
        <version>${vaadin.version}</version>
    </dependency>
    <dependency>
        <groupId>com.vaadin</groupId>
        <artifactId>vaadin-push</artifactId>
        <version>${vaadin.version}</version>
    </dependency>
    <dependency>
        <groupId>com.vaadin</groupId>
        <artifactId>vaadin-client</artifactId>
        <version>${vaadin.version}</version>
        <scope>provided</scope>
    </dependency>
    <!-- Needed when using the widgetset optimizer (custom ConnectorBundleLoaderFactory).
        For widgetset compilation, vaadin-client-compiler is automatically added
        on the compilation classpath by vaadin-maven-plugin so normally there is
        no need for an explicit dependency. -->
    <!--  <dependency> <groupId>com.vaadin</groupId> <artifactId>vaadin-client-compiler</artifactId>
        <version>${vaadin.version}</version> <scope>provided</scope> </dependency> -->
    <dependency>
        <groupId>com.vaadin.addon</groupId>
        <artifactId>vaadin-touchkit-agpl</artifactId>
        <version>LATEST</version>
    </dependency>
    <dependency>
        <groupId>com.vaadin</groupId>
        <artifactId>vaadin-themes</artifactId>
        <version>${vaadin.version}</version>
    </dependency>
    <dependency>
  <groupId>org.vaadin.addons</groupId>
  <artifactId>vaadin-sqlcontainer</artifactId>
  <version>1.1.0</version>
</dependency>
    <dependency>
        <groupId>com.vaadin.addon</groupId>
        <artifactId>jpacontainer</artifactId>
        <version>3.2.0</version>
    </dependency>
    <dependency>
        <groupId>com.vaadin</groupId>
        <artifactId>vaadin-client-compiled</artifactId>
        <version>${vaadin.version}</version>
    </dependency>
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>9.4-1201-jdbc41</version>
    </dependency>
    <dependency>
        <groupId>org.eclipse.persistence</groupId>
        <artifactId>eclipselink</artifactId>
        <version>2.6.0-RC1</version>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-validator</artifactId>
        <version>5.1.3.Final</version>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.eclipse.persistence</groupId>
        <artifactId>javax.persistence</artifactId>
        <version>2.1.0</version>
    </dependency>
    <dependency>
        <groupId>javax.validation</groupId>
        <artifactId>validation-api</artifactId>
        <version>1.1.0.Final</version>
    </dependency>
    <dependency>
        <groupId>joda-time</groupId>
        <artifactId>joda-time</artifactId>
        <version>2.7</version>
    </dependency>
    <dependency>
        <groupId>org.vaadin.addon</groupId>
        <artifactId>tableexport-for-vaadin</artifactId>
        <version>1.5.1.5</version>
    </dependency>

[/i]

Well i use 7.5.2 though I don’t really think it matters…maybe worth a shot if it doesn’t cause any other issues in your project.

If the Code block format doesn’t screw up I’m going to try to add the source of both classes here so you can just copy the finished version.

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.vaadin.data.util.sqlcontainer.connection.JDBCConnectionPool;

/**
 * Common base class for database query classes that handle connections and
 * transactions.
 *
 * @author Vaadin Ltd
 * @since 6.8.9
 */
public abstract class AbstractTransactionalQuery implements Serializable {

    /**
     *
     */
    private static final long serialVersionUID = 1L;
    private JDBCConnectionPool connectionPool;
    private transient Connection activeConnection;

    AbstractTransactionalQuery() {
    }

    AbstractTransactionalQuery(JDBCConnectionPool connectionPool) {
        this.connectionPool = connectionPool;
    }

    /**
     * Reserves a connection with auto-commit off if no transaction is in
     * progress.
     *
     * @throws IllegalStateException
     *             if a transaction is already open
     * @throws SQLException
     *             if a connection could not be obtained or configured
     */
    public void beginTransaction() throws UnsupportedOperationException,
            SQLException {
        if (isInTransaction()) {
            throw new IllegalStateException("A transaction is already active!");
        }
        activeConnection = connectionPool.reserveConnection();
        activeConnection.setAutoCommit(false);
    }

    /**
     * Commits (if not in auto-commit mode) and releases the active connection.
     *
     * @throws SQLException
     *             if not in a transaction managed by this query
     */
    public void commit() throws UnsupportedOperationException, SQLException {
        if (!isInTransaction()) {
            throw new SQLException("No active transaction");
        }
        if (!activeConnection.getAutoCommit()) {
            activeConnection.commit();
        }
        connectionPool.releaseConnection(activeConnection);
        activeConnection = null;
    }

    /**
     * Rolls back and releases the active connection.
     *
     * @throws SQLException
     *             if not in a transaction managed by this query
     */
    public void rollback() throws UnsupportedOperationException, SQLException {
        if (!isInTransaction()) {
            throw new SQLException("No active transaction");
        }
        activeConnection.rollback();
        connectionPool.releaseConnection(activeConnection);
        activeConnection = null;
    }

    /**
     * Check that a transaction is active.
     *
     * @throws SQLException
     *             if no active transaction
     */
    protected void ensureTransaction() throws SQLException {
        if (!isInTransaction()) {
            throw new SQLException("No active transaction!");
        }
    }

    /**
     * Closes a statement and a resultset, then releases the connection if it is
     * not part of an active transaction. A failure in closing one of the
     * parameters does not prevent closing the rest.
     *
     * If the statement is a {@link PreparedStatement}, its parameters are
     * cleared prior to closing the statement.
     *
     * Although JDBC specification does state that closing a statement closes
     * its result set and closing a connection closes statements and result
     * sets, this method does try to close the result set and statement
     * explicitly whenever not null. This can guard against bugs in certain JDBC
     * drivers and reduce leaks in case e.g. closing the result set succeeds but
     * closing the statement or connection fails.
     *
     * @param conn
     *            the connection to release
     * @param statement
     *            the statement to close, may be null to skip closing
     * @param rs
     *            the result set to close, may be null to skip closing
     * @throws SQLException
     *             if closing the result set or the statement fails
     */
    protected void releaseConnection(Connection conn, Statement statement,
            ResultSet rs) throws SQLException {
        try {
            try {
                if (null != rs) {
                    rs.close();
                }
            } finally {
                if (null != statement) {
                    if (statement instanceof PreparedStatement) {
                        try {
                            ((PreparedStatement) statement).clearParameters();
                        } catch (Exception e) {
                            // will be closed below anyway
                        }
                    }
                    statement.close();
                }
            }
        } finally {
            releaseConnection(conn);
        }
    }

    /**
     * Returns the currently active connection, reserves and returns a new
     * connection if no active connection.
     *
     * @return previously active or newly reserved connection
     * @throws SQLException
     */
    protected Connection getConnection() throws SQLException {
        if (activeConnection != null) {
            return activeConnection;
        }
        return connectionPool.reserveConnection();
    }

    protected boolean isInTransaction() {
        return activeConnection != null;
    }

    /**
     * Releases the connection if it is not part of an active transaction.
     *
     * @param conn
     *            the connection to release
     */
    private void releaseConnection(Connection conn) {
        if (conn != activeConnection && conn != null) {
            connectionPool.releaseConnection(conn);
        }
    }
}

and here the TableQuery though I renamed it here to TQTest for testing purposes. You can just rename the class how you want it.

import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.EventObject;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.vaadin.data.Container.Filter;
import com.vaadin.data.util.filter.Compare.Equal;
import com.vaadin.data.util.sqlcontainer.ColumnProperty;
import com.vaadin.data.util.sqlcontainer.OptimisticLockException;
import com.vaadin.data.util.sqlcontainer.RowId;
import com.vaadin.data.util.sqlcontainer.RowItem;
import com.vaadin.data.util.sqlcontainer.SQLUtil;
import com.vaadin.data.util.sqlcontainer.TemporaryRowId;
import com.vaadin.data.util.sqlcontainer.connection.JDBCConnectionPool;
import com.vaadin.data.util.sqlcontainer.query.OrderBy;
import com.vaadin.data.util.sqlcontainer.query.QueryDelegate;
import com.vaadin.data.util.sqlcontainer.query.QueryDelegate.RowIdChangeListener;
import com.vaadin.data.util.sqlcontainer.query.generator.DefaultSQLGenerator;
import com.vaadin.data.util.sqlcontainer.query.generator.MSSQLGenerator;
import com.vaadin.data.util.sqlcontainer.query.generator.SQLGenerator;
import com.vaadin.data.util.sqlcontainer.query.generator.StatementHelper;

@SuppressWarnings("serial")
public class TQTest extends AbstractTransactionalQuery implements
        QueryDelegate, QueryDelegate.RowIdChangeNotifier {

    /**
     * Table name (without catalog or schema information).
     */
    private String tableName;
    private String catalogName;
    private String schemaName;
    /**
     * Cached concatenated version of the table name.
     */
    private String fullTableName;
    /**
     * Primary key column name(s) in the table.
     */
    public List<String> primaryKeyColumns;
    /**
     * Version column name in the table.
     */
    private String versionColumn;

    /** Currently set Filters and OrderBys */
    private List<Filter> filters;
    private List<OrderBy> orderBys;

    /** SQLGenerator instance to use for generating queries */
    private SQLGenerator sqlGenerator;

    /** Row ID change listeners */
    private LinkedList<RowIdChangeListener> rowIdChangeListeners;
    /** Row ID change events, stored until commit() is called */
    private final List<RowIdChangeEvent> bufferedEvents = new ArrayList<RowIdChangeEvent>();

    /** Set to true to output generated SQL Queries to System.out */
    private final boolean debug = false;

    /**
     * Creates a new TableQuery using the given connection pool, SQL generator
     * and table name to fetch the data from. All parameters must be non-null.
     *
     * The table name must be a simple name with no catalog or schema
     * information. If those are needed, use
     * {@link #TQTest(String, String, String, JDBCConnectionPool, SQLGenerator)}
     * .
     *
     * @param tableName
     *            Name of the database table to connect to
     * @param connectionPool
     *            Connection pool for accessing the database
     * @param sqlGenerator
     *            SQL query generator implementation
     */
    public TQTest(String tableName, JDBCConnectionPool connectionPool,
            SQLGenerator sqlGenerator) {
        this(null, null, tableName, connectionPool, sqlGenerator);
    }

    /**
     * Creates a new TableQuery using the given connection pool, SQL generator
     * and table name to fetch the data from. Catalog and schema names can be
     * null, all other parameters must be non-null.
     *
     * @param catalogName
     *            Name of the database catalog (can be null)
     * @param schemaName
     *            Name of the database schema (can be null)
     * @param tableName
     *            Name of the database table to connect to
     * @param connectionPool
     *            Connection pool for accessing the database
     * @param sqlGenerator
     *            SQL query generator implementation
     * @since 7.1
     */
    public TQTest(String catalogName, String schemaName, String tableName,
            JDBCConnectionPool connectionPool, SQLGenerator sqlGenerator) {
        this(catalogName, schemaName, tableName, connectionPool, sqlGenerator,
                true);
    }

    /**
     * Creates a new TableQuery using the given connection pool and table name
     * to fetch the data from. All parameters must be non-null. The default SQL
     * generator will be used for queries.
     *
     * The table name must be a simple name with no catalog or schema
     * information. If those are needed, use
     * {@link #TQTest(String, String, String, JDBCConnectionPool, SQLGenerator)}
     * .
     *
     * @param tableName
     *            Name of the database table to connect to
     * @param connectionPool
     *            Connection pool for accessing the database
     */
    public TQTest(String tableName, JDBCConnectionPool connectionPool) {
        this(tableName, connectionPool, new DefaultSQLGenerator());
    }

    /**
     * Creates a new TableQuery using the given connection pool, SQL generator
     * and table name to fetch the data from. Catalog and schema names can be
     * null, all other parameters must be non-null.
     *
     * @param catalogName
     *            Name of the database catalog (can be null)
     * @param schemaName
     *            Name of the database schema (can be null)
     * @param tableName
     *            Name of the database table to connect to
     * @param connectionPool
     *            Connection pool for accessing the database
     * @param sqlGenerator
     *            SQL query generator implementation
     * @param escapeNames
     *            true to escape special characters in catalog, schema and table
     *            names, false to use the names as-is
     * @since 7.1
     */
    protected TQTest(String catalogName, String schemaName,
            String tableName, JDBCConnectionPool connectionPool,
            SQLGenerator sqlGenerator, boolean escapeNames) {
        super(connectionPool);
        if (tableName == null || tableName.trim().length() < 1
                || connectionPool == null || sqlGenerator == null) {
            throw new IllegalArgumentException(
                    "Table name, connection pool and SQL generator parameters must be non-null and non-empty.");
        }
        if (escapeNames) {
            this.catalogName = SQLUtil.escapeSQL(catalogName);
            this.schemaName = SQLUtil.escapeSQL(schemaName);
            this.tableName = SQLUtil.escapeSQL(tableName);
        } else {
            this.catalogName = catalogName;
            this.schemaName = schemaName;
            this.tableName = tableName;
        }
        this.sqlGenerator = sqlGenerator;
        fetchMetaData();
    }

    /*
     * (non-Javadoc)
     *
     * @see com.vaadin.addon.sqlcontainer.query.QueryDelegate#getCount()
     */
    @Override
    public int getCount() throws SQLException {
        getLogger().log(Level.FINE, "Fetching count...");
        StatementHelper sh = sqlGenerator.generateSelectQuery(
                getFullTableName(), filters, null, 0, 0, "COUNT(*)");
        boolean shouldCloseTransaction = false;
        if (!isInTransaction()) {
            shouldCloseTransaction = true;
            beginTransaction();
        }
        ResultSet r = null;
        int count = -1;
        try {
            r = executeQuery(sh);
            r.next();
            count = r.getInt(1);
        } finally {
            try {
                if (r != null) {
                    // Do not release connection, it is done in commit()
                    releaseConnection(null, r.getStatement(), r);
                }
            } finally {
                if (shouldCloseTransaction) {
                    commit();
                }
            }
        }
        return count;
    }

    /*
     * (non-Javadoc)
     *
     * @see com.vaadin.addon.sqlcontainer.query.QueryDelegate#getResults(int,
     * int)
     */
    @Override
    public ResultSet getResults(int offset, int pagelength) throws SQLException {
        StatementHelper sh;
        /*
         * If no ordering is explicitly set, results will be ordered by the
         * first primary key column.
         */
        if (orderBys == null || orderBys.isEmpty()) {
            List<OrderBy> ob = new ArrayList<OrderBy>();
            for (int i = 0; i < primaryKeyColumns.size(); i++) {
                ob.add(new OrderBy(primaryKeyColumns.get(i), true));
            }
            sh = sqlGenerator.generateSelectQuery(getFullTableName(), filters,
                    ob, offset, pagelength, null);
        } else {
            sh = sqlGenerator.generateSelectQuery(getFullTableName(), filters,
                    orderBys, offset, pagelength, null);
        }
        return executeQuery(sh);
    }

    /*
     * (non-Javadoc)
     *
     * @see com.vaadin.addon.sqlcontainer.query.QueryDelegate#
     * implementationRespectsPagingLimits()
     */
    @Override
    public boolean implementationRespectsPagingLimits() {
        return true;
    }

    /*
     * (non-Javadoc)
     *
     * @see
     * com.vaadin.addon.sqlcontainer.query.QueryDelegate#storeRow(com.vaadin
     * .addon.sqlcontainer.RowItem)
     */
    @Override
    public int storeRow(RowItem row) throws UnsupportedOperationException,
            SQLException {
        if (row == null) {
            throw new IllegalArgumentException("Row argument must be non-null.");
        }
        StatementHelper sh;
        int result = 0;
        if (row.getId() instanceof TemporaryRowId) {
            setVersionColumnFlagInProperty(row);
            sh = sqlGenerator.generateInsertQuery(getFullTableName(), row);
            result = executeUpdateReturnKeys(sh, row);
        } else {
            setVersionColumnFlagInProperty(row);
            sh = sqlGenerator.generateUpdateQuery(getFullTableName(), row);
            result = executeUpdate(sh);
        }
        if (versionColumn != null && result == 0) {
            throw new OptimisticLockException(
                    "Someone else changed the row that was being updated.",
                    row.getId());
        }
        return result;
    }

    private void setVersionColumnFlagInProperty(RowItem row) {
        ColumnProperty versionProperty = (ColumnProperty) row
                .getItemProperty(versionColumn);
        if (versionProperty != null) {
            versionProperty.setVersionColumn(true);
        }
    }

    /**
     * Inserts the given row in the database table immediately. Begins and
     * commits the transaction needed. This method was added specifically to
     * solve the problem of returning the final RowId immediately on the
     * SQLContainer.addItem() call when auto commit mode is enabled in the
     * SQLContainer.
     *
     * @param row
     *            RowItem to add to the database
     * @return Final RowId of the added row
     * @throws SQLException
     */
    public RowId storeRowImmediately(RowItem row) throws SQLException {
        beginTransaction();
        /* Set version column, if one is provided */
        setVersionColumnFlagInProperty(row);
        /* Generate query */
        StatementHelper sh = sqlGenerator.generateInsertQuery(
                getFullTableName(), row);
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet generatedKeys = null;
        connection = getConnection();
        try {
            pstmt = connection.prepareStatement(sh.getQueryString(),
                    primaryKeyColumns.toArray(new String[0]
));
            sh.setParameterValuesToStatement(pstmt);
            getLogger().log(Level.FINE, "DB -> {0}", sh.getQueryString());
            int result = pstmt.executeUpdate();
            RowId newId = null;
            if (result > 0) {
                /*
                 * If affected rows exist, we'll get the new RowId, commit the
                 * transaction and return the new RowId.
                 */
                generatedKeys = pstmt.getGeneratedKeys();
                newId = getNewRowId(row, generatedKeys);
            }
            // transaction has to be closed in any case
            commit();
            return newId;
        } finally {
            releaseConnection(connection, pstmt, generatedKeys);
        }
    }

    /*
     * (non-Javadoc)
     *
     * @see
     * com.vaadin.addon.sqlcontainer.query.QueryDelegate#setFilters(java.util
     * .List)
     */
    @Override
    public void setFilters(List<Filter> filters)
            throws UnsupportedOperationException {
        if (filters == null) {
            this.filters = null;
            return;
        }
        this.filters = Collections.unmodifiableList(filters);
    }

    /*
     * (non-Javadoc)
     *
     * @see
     * com.vaadin.addon.sqlcontainer.query.QueryDelegate#setOrderBy(java.util
     * .List)
     */
    @Override
    public void setOrderBy(List<OrderBy> orderBys)
            throws UnsupportedOperationException {
        if (orderBys == null) {
            this.orderBys = null;
            return;
        }
        this.orderBys = Collections.unmodifiableList(orderBys);
    }

    /*
     * (non-Javadoc)
     *
     * @see com.vaadin.addon.sqlcontainer.query.QueryDelegate#beginTransaction()
     */
    @Override
    public void beginTransaction() throws UnsupportedOperationException,
            SQLException {
        getLogger().log(Level.FINE, "DB -> begin transaction");
        super.beginTransaction();
    }

    /*
     * (non-Javadoc)
     *
     * @see com.vaadin.addon.sqlcontainer.query.QueryDelegate#commit()
     */
    @Override
    public void commit() throws UnsupportedOperationException, SQLException {
        getLogger().log(Level.FINE, "DB -> commit");
        super.commit();

        /* Handle firing row ID change events */
        RowIdChangeEvent[] unFiredEvents = bufferedEvents
                .toArray(new RowIdChangeEvent[] {});
        bufferedEvents.clear();
        if (rowIdChangeListeners != null && !rowIdChangeListeners.isEmpty()) {
            for (RowIdChangeListener r : rowIdChangeListeners) {
                for (RowIdChangeEvent e : unFiredEvents) {
                    r.rowIdChange(e);
                }
            }
        }
    }

    /*
     * (non-Javadoc)
     *
     * @see com.vaadin.addon.sqlcontainer.query.QueryDelegate#rollback()
     */
    @Override
    public void rollback() throws UnsupportedOperationException, SQLException {
        getLogger().log(Level.FINE, "DB -> rollback");
        super.rollback();
    }

    /*
     * (non-Javadoc)
     *
     * @see
     * com.vaadin.addon.sqlcontainer.query.QueryDelegate#getPrimaryKeyColumns()
     */
    @Override
    public List<String> getPrimaryKeyColumns() {
        return Collections.unmodifiableList(primaryKeyColumns);
    }

    public String getVersionColumn() {
        return versionColumn;
    }

    public void setVersionColumn(String column) {
        versionColumn = column;
    }

    /**
     * Returns the table name for the query without catalog and schema
     * information.
     *
     * @return table name, not null
     */
    public String getTableName() {
        return tableName;
    }

    /**
     * Returns the catalog name for the query.
     *
     * @return catalog name, can be null
     * @since 7.1
     */
    public String getCatalogName() {
        return catalogName;
    }

    /**
     * Returns the catalog name for the query.
     *
     * @return catalog name, can be null
     * @since 7.1
     */
    public String getSchemaName() {
        return schemaName;
    }

    /**
     * Returns the complete table name obtained by concatenation of the catalog
     * and schema names (if any) and the table name.
     *
     * This method can be overridden if customization is needed.
     *
     * @return table name in the form it should be used in query and update
     *         statements
     * @since 7.1
     */
    protected String getFullTableName() {
        if (fullTableName == null) {
            StringBuilder sb = new StringBuilder();
            if (catalogName != null) {
                sb.append(catalogName).append(".");
            }
            if (schemaName != null) {
                sb.append(schemaName).append(".");
            }
            sb.append(tableName);
            fullTableName = sb.toString();
        }
        return fullTableName;
    }

    public SQLGenerator getSqlGenerator() {
        return sqlGenerator;
    }

    /**
     * Executes the given query string using either the active connection if a
     * transaction is already open, or a new connection from this query's
     * connection pool.
     *
     * @param sh
     *            an instance of StatementHelper, containing the query string
     *            and parameter values.
     * @return ResultSet of the query
     * @throws SQLException
     */
    private ResultSet executeQuery(StatementHelper sh) throws SQLException {
        ensureTransaction();
        Connection connection = getConnection();
        PreparedStatement pstmt = null;
        try {
            pstmt = connection.prepareStatement(sh.getQueryString());
            sh.setParameterValuesToStatement(pstmt);
            getLogger().log(Level.FINE, "DB -> {0}", sh.getQueryString());
            return pstmt.executeQuery();
        } catch (SQLException e) {
            releaseConnection(null, pstmt, null);
            throw e;
        }
    }

    /**
     * Executes the given update query string using either the active connection
     * if a transaction is already open, or a new connection from this query's
     * connection pool.
     *
     * @param sh
     *            an instance of StatementHelper, containing the query string
     *            and parameter values.
     * @return Number of affected rows
     * @throws SQLException
     */
    private int executeUpdate(StatementHelper sh) throws SQLException {
        PreparedStatement pstmt = null;
        Connection connection = null;
        try {
            connection = getConnection();
            pstmt = connection.prepareStatement(sh.getQueryString());
            sh.setParameterValuesToStatement(pstmt);
            getLogger().log(Level.FINE, "DB -> {0}", sh.getQueryString());
            int retval = pstmt.executeUpdate();
            return retval;
        } finally {
            releaseConnection(connection, pstmt, null);
        }
    }

    /**
     * Executes the given update query string using either the active connection
     * if a transaction is already open, or a new connection from this query's
     * connection pool.
     *
     * Additionally adds a new RowIdChangeEvent to the event buffer.
     *
     * @param sh
     *            an instance of StatementHelper, containing the query string
     *            and parameter values.
     * @param row
     *            the row item to update
     * @return Number of affected rows
     * @throws SQLException
     */
    private int executeUpdateReturnKeys(StatementHelper sh, RowItem row)
            throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet genKeys = null;
        Connection connection = null;
        try {
            connection = getConnection();
            pstmt = connection.prepareStatement(sh.getQueryString(),
                    primaryKeyColumns.toArray(new String[0]
));
            sh.setParameterValuesToStatement(pstmt);
            getLogger().log(Level.FINE, "DB -> {0}", sh.getQueryString());
            int result = pstmt.executeUpdate();
            genKeys = pstmt.getGeneratedKeys();
            RowId newId = getNewRowId(row, genKeys);
            bufferedEvents.add(new RowIdChangeEvent(row.getId(), newId));
            return result;
        } finally {
            releaseConnection(connection, pstmt, genKeys);
        }
    }

    /**
     * Fetches name(s) of primary key column(s) from DB metadata.
     *
     * Also tries to get the escape string to be used in search strings.
     */
    public void fetchMetaData() {
        Connection connection = null;
        ResultSet rs = null;
        ResultSet tables = null;
        try {
            connection = getConnection();
            DatabaseMetaData dbmd = connection.getMetaData();
            if (dbmd != null) {
                tables = dbmd.getTables(catalogName, schemaName, tableName,
                        null);
                if (!tables.next()) {
                    String catalog = (catalogName != null) ? catalogName
                            .toUpperCase() : null;
                    String schema = (schemaName != null) ? schemaName
                            .toUpperCase() : null;
                    tables = dbmd.getTables(catalog, schema,
                            tableName.toUpperCase(), null);
                    if (!tables.next()) {
                        throw new IllegalArgumentException(
                                "Table with the name \""
                                        + getFullTableName()
                                        + "\" was not found. Check your database contents.");
                    } else {
                        catalogName = catalog;
                        schemaName = schema;
                        tableName = tableName.toUpperCase();
                    }
                }
                tables.close();
                rs = dbmd.getPrimaryKeys(catalogName, schemaName, tableName);
                List<String> names = new ArrayList<String>();
                while (rs.next()) {
                    names.add(rs.getString("COLUMN_NAME"));
                }
                rs.close();
                if (!names.isEmpty()) {
                    primaryKeyColumns = names;
                }
                if (primaryKeyColumns == null || primaryKeyColumns.isEmpty()) {
                    throw new IllegalArgumentException(
                            "Primary key constraints have not been defined for the table \""
                                    + getFullTableName()
                                    + "\". Use FreeFormQuery to access this table.");
                }
                for (String colName : primaryKeyColumns) {
                    if (colName.equalsIgnoreCase("rownum")) {
                        if (getSqlGenerator() instanceof MSSQLGenerator
                                || getSqlGenerator() instanceof MSSQLGenerator) {
                            throw new IllegalArgumentException(
                                    "When using Oracle or MSSQL, a primary key column"
                                            + " named \'rownum\' is not allowed!");
                        }
                    }
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                releaseConnection(connection, null, rs);
            } catch (SQLException ignore) {
            } finally {
                try {
                    if (tables != null) {
                        tables.close();
                    }
                } catch (SQLException ignore) {
                }
            }
        }
    }

    private RowId getNewRowId(RowItem row, ResultSet genKeys) {
        try {
            /* Fetch primary key values and generate a map out of them. */
            Map<String, Object> values = new HashMap<String, Object>();
            ResultSetMetaData rsmd = genKeys.getMetaData();
            int colCount = rsmd.getColumnCount();
            if (genKeys.next()) {
                for (int i = 1; i <= colCount; i++) {
                    values.put(rsmd.getColumnName(i), genKeys.getObject(i));
                }
            }
            /* Generate new RowId */
            List<Object> newRowId = new ArrayList<Object>();
            if (values.size() == 1) {
                if (primaryKeyColumns.size() == 1) {
                    newRowId.add(values.get(values.keySet().iterator().next()));
                } else {
                    for (String s : primaryKeyColumns) {
                        if (!((ColumnProperty) row.getItemProperty(s))
                                .isReadOnlyChangeAllowed()) {
                            newRowId.add(values.get(values.keySet().iterator()
                                    .next()));
                        } else {
                            newRowId.add(values.get(s));
                        }
                    }
                }
            } else {
                for (String s : primaryKeyColumns) {
                    newRowId.add(values.get(s));
                }
            }
            return new RowId(newRowId.toArray());
        } catch (Exception e) {
            getLogger()
                    .log(Level.FINE,
                            "Failed to fetch key values on insert: {0}",
                            e.getMessage());
            return null;
        }
    }

    /*
     * (non-Javadoc)
     *
     * @see
     * com.vaadin.addon.sqlcontainer.query.QueryDelegate#removeRow(com.vaadin
     * .addon.sqlcontainer.RowItem)
     */
    @Override
    public boolean removeRow(RowItem row) throws UnsupportedOperationException,
            SQLException {
        if (getLogger().isLoggable(Level.FINE)) {
            getLogger().log(Level.FINE, "Removing row with id: {0}",
                    row.getId().getId()[0]
);
        }
        if (executeUpdate(sqlGenerator.generateDeleteQuery(getFullTableName(),
                primaryKeyColumns, versionColumn, row)) == 1) {
            return true;
        }
        if (versionColumn != null) {
            throw new OptimisticLockException(
                    "Someone else changed the row that was being deleted.",
                    row.getId());
        }
        return false;
    }

    /*
     * (non-Javadoc)
     *
     * @see
     * com.vaadin.addon.sqlcontainer.query.QueryDelegate#containsRowWithKey(
     * java.lang.Object[])
     */
    @Override
    public boolean containsRowWithKey(Object... keys) throws SQLException {
        ArrayList<Filter> filtersAndKeys = new ArrayList<Filter>();
        if (filters != null) {
            filtersAndKeys.addAll(filters);
        }
        int ix = 0;
        for (String colName : primaryKeyColumns) {
            filtersAndKeys.add(new Equal(colName, keys[ix]
));
            ix++;
        }
        StatementHelper sh = sqlGenerator.generateSelectQuery(
                getFullTableName(), filtersAndKeys, orderBys, 0, 0, "*");

        boolean shouldCloseTransaction = false;
        if (!isInTransaction()) {
            shouldCloseTransaction = true;
            beginTransaction();
        }
        ResultSet rs = null;
        try {
            rs = executeQuery(sh);
            boolean contains = rs.next();
            return contains;
        } finally {
            try {
                if (rs != null) {
                    // Do not release connection, it is done in commit()
                    releaseConnection(null, rs.getStatement(), rs);
                }
            } finally {
                if (shouldCloseTransaction) {
                    commit();
                }
            }
        }
    }

    /**
     * Custom writeObject to call rollback() if object is serialized.
     */
    private void writeObject(java.io.ObjectOutputStream out) throws IOException {
        try {
            rollback();
        } catch (SQLException ignored) {
        }
        out.defaultWriteObject();
    }

    /**
     * Simple RowIdChangeEvent implementation.
     */
    public static class RowIdChangeEvent extends EventObject implements
            QueryDelegate.RowIdChangeEvent {
        private final RowId oldId;
        private final RowId newId;

        private RowIdChangeEvent(RowId oldId, RowId newId) {
            super(oldId);
            this.oldId = oldId;
            this.newId = newId;
        }

        @Override
        public RowId getNewRowId() {
            return newId;
        }

        @Override
        public RowId getOldRowId() {
            return oldId;
        }
    }

    /**
     * Adds RowIdChangeListener to this query
     */
    @Override
    public void addRowIdChangeListener(RowIdChangeListener listener) {
        if (rowIdChangeListeners == null) {
            rowIdChangeListeners = new LinkedList<QueryDelegate.RowIdChangeListener>();
        }
        rowIdChangeListeners.add(listener);
    }

    /**
     * @deprecated As of 7.0, replaced by
     *             {@link #addRowIdChangeListener(com.vaadin.data.util.sqlcontainer.query.QueryDelegate.RowIdChangeListener)}
     **/
    @Override
    @Deprecated
    public void addListener(RowIdChangeListener listener) {
        addRowIdChangeListener(listener);
    }

    /**
     * Removes the given RowIdChangeListener from this query
     */
    @Override
    public void removeRowIdChangeListener(RowIdChangeListener listener) {
        if (rowIdChangeListeners != null) {
            rowIdChangeListeners.remove(listener);
        }
    }

    /**
     * @deprecated As of 7.0, replaced by
     *             {@link #removeRowIdChangeListener(com.vaadin.data.util.sqlcontainer.query.QueryDelegate.RowIdChangeListener)}
     **/
    @Override
    @Deprecated
    public void removeListener(RowIdChangeListener listener) {
        removeRowIdChangeListener(listener);
    }

    private static final Logger getLogger() {
        return Logger.getLogger(TQTest.class.getName());
    }
}