public static void initPostgres() throws SQLException, ClassNotFoundException {
SQLTemplates templates = new PostgresTemplates(true);
// NOTE : unquoted identifiers are converted to lower case in Postgres
Connection c = getPostgres();
connHolder.set(c);
Statement stmt = c.createStatement();
stmtHolder.set(stmt);
if (postgresInited) {
return;
}
// survey
dropTable(templates, "SURVEY");
try {
stmt.execute("drop sequence SURVEY_SEQ");
} catch (SQLException e) {
if (!e.getMessage().contains("does not exist")) {
throw e;
}
}
stmt.execute("create sequence SURVEY_SEQ");
stmt.execute(
"create table \"SURVEY\"("
+ "\"ID\" int DEFAULT NEXTVAL('SURVEY_SEQ'), "
+ "\"NAME\" varchar(30), \"NAME2\" varchar(30))");
stmt.execute("insert into \"SURVEY\" values (1, 'Hello World')");
// test
dropTable(templates, "TEST");
stmt.execute(quote(CREATE_TABLE_TEST, "TEST", "NAME"));
String sql = quote(INSERT_INTO_TEST_VALUES, "TEST");
PreparedStatement pstmt = c.prepareStatement(sql);
try {
for (int i = 0; i < TEST_ROW_COUNT; i++) {
pstmt.setString(1, "name" + i);
pstmt.addBatch();
}
pstmt.executeBatch();
} finally {
pstmt.close();
}
// employee
// stmt.execute("drop table employee if exists");
dropTable(templates, "EMPLOYEE");
createEmployeeTable(templates);
addEmployees(
"insert into \"EMPLOYEE\" "
+ "(\"ID\", \"FIRSTNAME\", \"LASTNAME\", \"SALARY\", \"DATEFIELD\", \"TIMEFIELD\", \"SUPERIOR_ID\") "
+ "values (?,?,?,?,?,?,?)");
// date_test and time_test
dropTable(templates, "TIME_TEST");
dropTable(templates, "DATE_TEST");
stmt.execute(quote(CREATE_TABLE_TIMETEST, "TIME_TEST"));
stmt.execute(quote(CREATE_TABLE_DATETEST, "DATE_TEST"));
postgresInited = true;
}
public static void initDerby() throws SQLException, ClassNotFoundException {
SQLTemplates templates = new DerbyTemplates();
Connection c = getDerby();
connHolder.set(c);
Statement stmt = c.createStatement();
stmtHolder.set(stmt);
if (derbyInited) {
return;
}
// survey
dropTable(templates, "SURVEY");
stmt.execute(
"create table SURVEY("
+ "ID int generated by default as identity(start with 1, increment by 1), "
+ "NAME varchar(30),"
+ "NAME2 varchar(30))");
stmt.execute("insert into SURVEY values (1,'Hello World','Hello')");
// test
dropTable(templates, "TEST");
stmt.execute(CREATE_TABLE_TEST);
stmt.execute("create index test_name on test(name)");
PreparedStatement pstmt = c.prepareStatement(INSERT_INTO_TEST_VALUES);
try {
for (int i = 0; i < TEST_ROW_COUNT; i++) {
pstmt.setString(1, "name" + i);
pstmt.addBatch();
}
pstmt.executeBatch();
} finally {
pstmt.close();
}
// employee
dropTable(templates, "EMPLOYEE");
createEmployeeTable(templates);
addEmployees(INSERT_INTO_EMPLOYEE);
// date_test and time_test
dropTable(templates, "TIME_TEST");
stmt.execute(CREATE_TABLE_TIMETEST);
dropTable(templates, "DATE_TEST");
stmt.execute(CREATE_TABLE_DATETEST);
derbyInited = true;
}
public static void initHSQL() throws SQLException, ClassNotFoundException {
SQLTemplates templates = new HSQLDBTemplates();
Connection c = getHSQL();
connHolder.set(c);
Statement stmt = c.createStatement();
stmtHolder.set(stmt);
if (hsqlInited) {
return;
}
// survey
stmt.execute("drop table SURVEY if exists");
// stmt.execute(CREATE_TABLE_SURVEY);
stmt.execute(
"create table SURVEY("
+ "ID int generated by default as identity, "
+ "NAME varchar(30),"
+ "NAME2 varchar(30))");
stmt.execute("insert into SURVEY values (1, 'Hello World', 'Hello')");
// test
stmt.execute("drop table TEST if exists");
stmt.execute(CREATE_TABLE_TEST);
PreparedStatement pstmt = c.prepareStatement(INSERT_INTO_TEST_VALUES);
try {
for (int i = 0; i < TEST_ROW_COUNT; i++) {
pstmt.setString(1, "name" + i);
pstmt.addBatch();
}
pstmt.executeBatch();
} finally {
pstmt.close();
}
// employee
stmt.execute("drop table EMPLOYEE if exists");
createEmployeeTable(templates);
stmt.execute("alter table EMPLOYEE alter column id int generated by default as identity");
addEmployees(INSERT_INTO_EMPLOYEE);
// date_test and time_test
stmt.execute("drop table TIME_TEST if exists");
stmt.execute("drop table DATE_TEST if exists");
stmt.execute(CREATE_TABLE_TIMETEST);
stmt.execute(CREATE_TABLE_DATETEST);
hsqlInited = true;
}
public static void initH2() throws SQLException, ClassNotFoundException {
SQLTemplates templates = new H2Templates();
Connection c = getH2();
connHolder.set(c);
Statement stmt = c.createStatement();
stmtHolder.set(stmt);
if (h2Inited) {
return;
}
// qtest
stmt.execute("drop table QTEST if exists");
stmt.execute("create table QTEST (ID int IDENTITY(1,1) NOT NULL, C1 int NULL)");
// survey
stmt.execute("drop table SURVEY if exists");
stmt.execute(CREATE_TABLE_SURVEY);
stmt.execute("insert into SURVEY values (1, 'Hello World','Hello');");
stmt.execute("alter table SURVEY alter column id int auto_increment");
// test
stmt.execute("drop table TEST if exists");
stmt.execute(CREATE_TABLE_TEST);
PreparedStatement pstmt = c.prepareStatement(INSERT_INTO_TEST_VALUES);
try {
for (int i = 0; i < TEST_ROW_COUNT; i++) {
pstmt.setString(1, "name" + i);
pstmt.addBatch();
}
pstmt.executeBatch();
} finally {
pstmt.close();
}
// employee
stmt.execute("drop table EMPLOYEE if exists");
createEmployeeTable(templates);
stmt.execute("alter table EMPLOYEE alter column id int auto_increment");
addEmployees(INSERT_INTO_EMPLOYEE);
// date_test and time_test
stmt.execute("drop table TIME_TEST if exists");
stmt.execute("drop table DATE_TEST if exists");
stmt.execute(CREATE_TABLE_TIMETEST);
stmt.execute(CREATE_TABLE_DATETEST);
h2Inited = true;
}
public static void initSQLServer() throws SQLException, ClassNotFoundException {
SQLTemplates templates = new SQLServerTemplates();
Connection c = getSQLServer();
connHolder.set(c);
Statement stmt = c.createStatement();
stmtHolder.set(stmt);
if (sqlServerInited) {
return;
}
// survey
dropTable(templates, "SURVEY");
stmt.execute(CREATE_TABLE_SURVEY);
stmt.execute("insert into SURVEY values (1, 'Hello World')");
// test
dropTable(templates, "TEST");
stmt.execute(CREATE_TABLE_TEST);
PreparedStatement pstmt = c.prepareStatement(INSERT_INTO_TEST_VALUES);
try {
for (int i = 0; i < TEST_ROW_COUNT; i++) {
pstmt.setString(1, "name" + i);
pstmt.addBatch();
}
pstmt.executeBatch();
} finally {
pstmt.close();
}
// employee
dropTable(templates, "EMPLOYEE");
createEmployeeTable(templates);
addEmployees(INSERT_INTO_EMPLOYEE);
// date_test and time_test
dropTable(templates, "TIME_TEST");
dropTable(templates, "DATE_TEST");
stmt.execute(CREATE_TABLE_TIMETEST);
stmt.execute(CREATE_TABLE_DATETEST);
sqlServerInited = true;
}
