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 initMySQL() throws SQLException, ClassNotFoundException {
// SQLTemplates templates = new MySQLTemplates();
Connection c = getMySQL();
connHolder.set(c);
Statement stmt = c.createStatement();
stmtHolder.set(stmt);
if (mysqlInited) {
return;
}
// survey
stmt.execute("drop table if exists SURVEY");
stmt.execute(
"create table SURVEY(ID int primary key auto_increment, "
+ "NAME varchar(30),"
+ "NAME2 varchar(30))");
stmt.execute("insert into SURVEY values (1,'Hello World','Hello');");
// test
stmt.execute("drop table if exists 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
stmt.execute("drop table if exists EMPLOYEE");
// createEmployeeTable(templates);
stmt.execute(
"create table EMPLOYEE ( "
+ "ID INT PRIMARY KEY AUTO_INCREMENT, "
+ "FIRSTNAME VARCHAR(50), "
+ "LASTNAME VARCHAR(50), "
+ "SALARY DECIMAL, "
+ "DATEFIELD DATE, "
+ "TIMEFIELD TIME, "
+ "SUPERIOR_ID INT, "
+ "CONSTRAINT FK_SUPERIOR FOREIGN KEY(SUPERIOR_ID) REFERENCES EMPLOYEE(ID) "
+ ")");
addEmployees(INSERT_INTO_EMPLOYEE);
// date_test and time_test
stmt.execute("drop table if exists TIME_TEST");
stmt.execute("drop table if exists DATE_TEST");
stmt.execute(CREATE_TABLE_TIMETEST);
stmt.execute(CREATE_TABLE_DATETEST);
mysqlInited = 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;
}
public static void initOracle() throws SQLException, ClassNotFoundException {
SQLTemplates templates = new OracleTemplates();
Connection c = getOracle();
connHolder.set(c);
Statement stmt = c.createStatement();
stmtHolder.set(stmt);
if (oracleInited) {
return;
}
// survey
dropTable(templates, "SURVEY");
stmt.execute(
"create table SURVEY (ID number(10,0), "
+ "NAME varchar(30 char),"
+ "NAME2 varchar(30 char))");
try {
stmt.execute("drop sequence survey_seq");
} catch (SQLException e) {
if (!e.getMessage().contains("sequence does not exist")) {
throw e;
}
}
stmt.execute("create sequence survey_seq");
stmt.execute(
"create or replace trigger survey_trigger\n"
+ "before insert on survey\n"
+ "for each row\n"
+ "when (new.id is null)\n"
+ "begin\n"
+ " select survey_seq.nextval into :new.id from dual;\n"
+ "end;\n");
stmt.execute("insert into SURVEY values (1,'Hello World','Hello')");
// test
dropTable(templates, "TEST");
stmt.execute("create table TEST(name varchar(255))");
String sql = "insert into TEST values(?)";
PreparedStatement pstmt = c.prepareStatement(sql);
for (int i = 0; i < TEST_ROW_COUNT; i++) {
pstmt.setString(1, "name" + i);
pstmt.addBatch();
}
pstmt.executeBatch();
// employee
dropTable(templates, "EMPLOYEE");
stmt.execute(
"create table EMPLOYEE ( "
+ "ID NUMBER(10,0), "
+ "FIRSTNAME VARCHAR2(50 CHAR), "
+ "LASTNAME VARCHAR2(50 CHAR), "
+ "SALARY DOUBLE PRECISION, "
+ "DATEFIELD DATE, "
+ "TIMEFIELD TIMESTAMP, "
+ "SUPERIOR_ID NUMBER(10,0), "
+ "CONSTRAINT PK_EMPLOYEE PRIMARY KEY(ID), "
+ "CONSTRAINT FK_SUPERIOR FOREIGN KEY(SUPERIOR_ID) REFERENCES EMPLOYEE(ID)"
+ ")");
addEmployees(INSERT_INTO_EMPLOYEE);
// date_test and time_test
dropTable(templates, "DATE_TEST");
stmt.execute("create table date_test(date_test date)");
oracleInited = true;
}