Ejemplo n.º 1
0
  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;
  }
Ejemplo n.º 2
0
  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;
  }
Ejemplo n.º 3
0
  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;
  }
Ejemplo n.º 4
0
  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;
  }