package com.vaadin.tests.containers.sqlcontainer;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import com.vaadin.data.util.sqlcontainer.SQLContainer;
import com.vaadin.data.util.sqlcontainer.SQLTestsConstants;
import com.vaadin.data.util.sqlcontainer.connection.JDBCConnectionPool;
import com.vaadin.data.util.sqlcontainer.connection.SimpleJDBCConnectionPool;
import com.vaadin.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;
}
}