DatabaseHelper.java

package com.vaadin.tests.containers.sqlcontainer;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import com.vaadin.v7.data.util.sqlcontainer.SQLContainer;
import com.vaadin.v7.data.util.sqlcontainer.SQLTestsConstants;
import com.vaadin.v7.data.util.sqlcontainer.connection.JDBCConnectionPool;
import com.vaadin.v7.data.util.sqlcontainer.connection.SimpleJDBCConnectionPool;
import com.vaadin.v7.data.util.sqlcontainer.query.TableQuery;

class DatabaseHelper {

    private JDBCConnectionPool connectionPool = null;
    private SQLContainer testContainer = null;
    private static final String TABLENAME = "testtable";
    private SQLContainer largeContainer = null;
    private static final String LARGE_TABLENAME = "largetable";

    public DatabaseHelper() {
        initConnectionPool();
        initDatabase();
        initContainers();
    }

    private void initDatabase() {
        try {
            Connection conn = connectionPool.reserveConnection();
            Statement statement = conn.createStatement();
            try {
                statement.execute("drop table " + TABLENAME);
            } catch (SQLException e) {
                // Will fail if table doesn't exist, which is OK.
                conn.rollback();
            }
            try {
                statement.execute("drop table " + LARGE_TABLENAME);
            } catch (SQLException e) {
                // Will fail if table doesn't exist, which is OK.
                conn.rollback();
            }
            switch (SQLTestsConstants.db) {
            case HSQLDB:
                statement.execute("create table " + TABLENAME
                        + " (id integer GENERATED BY DEFAULT AS IDENTITY, field1 varchar(100), field2 boolean, primary key(id))");
                statement.execute("create table " + LARGE_TABLENAME
                        + " (id integer GENERATED BY DEFAULT AS IDENTITY, field1 varchar(100), primary key(id))");
                break;
            case MYSQL:
                statement.execute("create table " + TABLENAME
                        + " (id integer auto_increment not null, field1 varchar(100), field2 boolean, primary key(id))");
                statement.execute("create table " + LARGE_TABLENAME
                        + " (id integer auto_increment not null, field1 varchar(100), primary key(id))");
                break;
            case POSTGRESQL:
                statement.execute("create table " + TABLENAME
                        + " (\"id\" serial primary key, \"field1\" varchar(100), \"field2\" boolean)");
                statement.execute("create table " + LARGE_TABLENAME
                        + " (\"id\" serial primary key, \"field1\" varchar(100))");
                break;
            }
            statement.executeUpdate("insert into " + TABLENAME
                    + " values(default, 'Kalle', 'true')");
            statement.executeUpdate("insert into " + TABLENAME
                    + " values(default, 'Ville', 'true')");
            statement.executeUpdate("insert into " + TABLENAME
                    + " values(default, 'Jussi', 'true')");

            for (int i = 0; i < 400; ++i) {
                statement.executeUpdate("insert into " + LARGE_TABLENAME
                        + " values(default, 'User " + i + "')");
            }

            statement.close();
            conn.commit();
            connectionPool.releaseConnection(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void initContainers() {
        try {
            TableQuery q1 = new TableQuery(TABLENAME, connectionPool);
            q1.setVersionColumn("id");
            testContainer = new SQLContainer(q1);

            TableQuery q2 = new TableQuery(LARGE_TABLENAME, connectionPool);
            q2.setVersionColumn("id");
            largeContainer = new SQLContainer(q2);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void initConnectionPool() {
        try {
            connectionPool = new SimpleJDBCConnectionPool(
                    SQLTestsConstants.dbDriver, SQLTestsConstants.dbURL,
                    SQLTestsConstants.dbUser, SQLTestsConstants.dbPwd, 2, 5);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public SQLContainer getTestContainer() {
        return testContainer;
    }

    public SQLContainer getLargeContainer() {
        return largeContainer;
    }
}