Firebird SQLGenerator

Vaadin 6 / 7 / 8 Framework Data Binding
1

I created a SQLGenerator for use with the Firebird database. Sharing for anyone who might find it useful:


public class FirebirdGenerator extends DefaultSQLGenerator {

    public enum Version {
        V1_0,
        V1_5,
        V2_0,
        V2_1,
        V2_5
    };
    
    private Version version;
    
    public FirebirdGenerator() {
        int latest = Version.values().length - 1;
        version = Version.values()[latest]
;
        
    }

    public FirebirdGenerator(Version version) {
        this.version = version;
    }

    /**
     * Construct a FirebirdGenerator with the specified identifiers for start and
     * end of quoted strings. The identifiers may be different depending on the
     * database engine and it's settings.
     * 
     * @param quoteStart
     *            the identifier (character) denoting the start of a quoted
     *            string
     * @param quoteEnd
     *            the identifier (character) denoting the end of a quoted string
     */
    public FirebirdGenerator(String quoteStart, String quoteEnd) {
        super(quoteStart, quoteEnd);
    }
    
    public Version getVersion() {
        return version;
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.vaadin.addon.sqlcontainer.query.generator.DefaultSQLGenerator#
     * generateSelectQuery(java.lang.String, java.util.List,
     * com.vaadin.addon.sqlcontainer.query.FilteringMode, java.util.List, int,
     * int, java.lang.String)
     */
    @Override
    public StatementHelper generateSelectQuery(String tableName, List<Filter> filters, List<OrderBy> orderBys, int offset, int pagelength, String toSelect) {
        if (tableName == null || tableName.trim().equals("")) {
            throw new IllegalArgumentException("Table name must be given.");
        }
        
        toSelect = toSelect == null ? "*" : toSelect;
        StatementHelper sh = new StatementHelper();
        StringBuffer query = new StringBuffer();

        /* Row count request is handled here */
        
        if ("COUNT(*)".equalsIgnoreCase(toSelect)) {
            boolean derivedTablesSupported = false;
            switch (version) {
                case V2_5:
                case V2_1:
                case V2_0:
                    derivedTablesSupported = true;
                    query.append(String.format("SELECT COUNT(*) AS \"rowcount\" FROM (SELECT * FROM %s", tableName));
                    break;
                case V1_5:
                case V1_0:
                default:
                    /* THIS COULD BE INCREDIBLY SLOW 
                     * See http://www.firebirdfaq.org/faq5/ and http://www.firebirdfaq.org/faq198/ */
                    query.append(String.format("SELECT COUNT(*) AS \"rowcount\" FROM %s", tableName));
                    break;
            }
            
            if (filters != null && !filters.isEmpty()) {
                query.append(QueryBuilder.getWhereStringForFilters(filters, sh));
            }
            if (derivedTablesSupported) {
                query.append(")");
            }
            sh.setQueryString(query.toString());
            return sh;
        }

        if (offset == 0 && pagelength == 0) {
            query.append("SELECT ").append(toSelect).append(" FROM ").append(tableName);
        } else {
            query.append(String.format("SELECT FIRST %d SKIP %d %s FROM %s", pagelength, offset, toSelect, tableName));
        }
        if (filters != null) {
            query.append(QueryBuilder.getWhereStringForFilters(filters, sh));
        }
        if (orderBys != null) {
            for (OrderBy o : orderBys) {
                generateOrderBy(query, o, orderBys.indexOf(o) == 0);
            }
        }
        sh.setQueryString(query.toString());
        return sh;
    }

}
2

You should consider bundling this into a Vaadin Add-on, and upload it into
Vaadin Directory
. Even though it’s a small tool, it’s easier to take it into use, and you can bundle it with a manual and whatnot.

3

Ok, I’ll take a look at that. I’m brand-new to Vaadin, and only just discovered the Directory while looking into Comet support (found ICEPush and got it working – quite slick). The Directory seems to be a nice resource.

4

Hi
I created simple application from AddressBook tutorial with Firebird database.

Populating table with this class works like a charm, but I have problem with CRUD.
I created Table in Firebird 2.5 Database like this:


CREATE TABLE TEST_B (
    ID      INTEGER NOT NULL,
    TEKST   VARCHAR(10) COLLATE PXW_PLK,
    TEKST2  VARCHAR(10) COLLATE PXW_PLK,
    TEKST3  VARCHAR(10) COLLATE PXW_PLK,
    SYMBOL  VARCHAR(4) COLLATE PXW_PLK,
    FIRMA   VARCHAR(10)
);

ALTER TABLE TEST_B ADD CONSTRAINT PK_TEST_B PRIMARY KEY (ID);

SET TERM ^ ;

CREATE OR ALTER TRIGGER TEST_B_BI FOR TEST_B
ACTIVE BEFORE INSERT POSITION 0
AS
begin
   new.id=gen_id(gen_test_b_id,1);
end
^

SET TERM ; ^

And this is my Database provider class:


import java.sql.SQLException;
import com.vaadin.data.util.sqlcontainer.SQLContainer;
import com.vaadin.data.util.sqlcontainer.connection.JDBCConnectionPool;
import com.vaadin.data.util.sqlcontainer.connection.SimpleJDBCConnectionPool;
import com.vaadin.data.util.sqlcontainer.query.TableQuery;


public class DataSourceContainers {
	private JDBCConnectionPool connectionPool = null;
	private SQLContainer kontrahContainer = null;

	public DataSourceContainers() {
		this.initConnectionPool();
		this.initContainer();
	}
	
	private void initConnectionPool() {
		try {
			connectionPool  = new SimpleJDBCConnectionPool(
					"org.firebirdsql.jdbc.FBDriver",
					"jdbc:firebirdsql:localhost/3050:c:/TOP_BAZY/E_FIRMA.GDB", 
					"sysdba",
					"masterkey");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private void initContainer() {
		try {			
			FirebirdGenerator generator = new FirebirdGenerator(FirebirdGenerator.Version.V2_5);
			TableQuery tqKontrah = new TableQuery("TEST_B", connectionPool, generator);
			kontrahContainer = new SQLContainer(tqKontrah);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public SQLContainer getKontrahContainer() {
		return kontrahContainer;
	}
}

When I populate Form with and make Commit I’m getting exception:


org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544665. violation of PRIMARY or UNIQUE KEY constraint "PK_TEST_B" on table "TEST_B"