core/org.eclipse.smila.jdbc.test/code/src/org/eclipse/smila/jdbc/test/JdbcTestBase.java

/*******************************************************************************
 * Copyright (c) 2008, 2012 EMPOLIS Information Management GmbH and brox IT Solutions GmbH. All rights reserved. This
 * program and the accompanying materials are made available under the terms of the Eclipse Public License v1.0 which
 * accompanies this distribution, and is available at http://www.eclipse.org/legal/epl-v10.html
 * 
 * Contributors: Andreas Schank (EMPOLIS Information Management GmbH) - initial API and implementation
 *******************************************************************************/
package org.eclipse.smila.jdbc.test;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.eclipse.smila.jdbc.JdbcProvider;
import org.eclipse.smila.test.DeclarativeServiceTestCase;

/**
 * Base for JDBC crawler tests. Creates and fills up a database used for subsequent testing of the crawler.
 */
public abstract class JdbcTestBase extends DeclarativeServiceTestCase {

  /** has the database already been initialized? */
  protected boolean dbInitialized = false;

  /** dummy db user. */
  protected static final String DB_USER = "user1";

  /** dummy db password. */
  protected static final String DB_PASSWORD = "user1";

  /** test db name. */
  protected static final String DB_NAME = "test";

  /** test db table name. */
  protected static final String DB_TABLE_NAME = "test_table";

  /** reference to the JdbcProvider. */
  protected JdbcProvider _jdbcProvider;

  @Override
  protected void setUp() throws Exception {
    super.setUp();
    _jdbcProvider = getService(JdbcProvider.class);
    assertNotNull(_jdbcProvider);
    if (!dbInitialized) {
      createTestDatabase(_jdbcProvider);
      dbInitialized = true;
    }
  }

  /** creates the table and stores some dummy data in it. */
  protected void createTestDatabase(final JdbcProvider _jdbcProvider) throws SQLException,
    UnsupportedEncodingException {
    final Connection conn = _jdbcProvider.getConnection(getConnectUrl(), getProperties());
    final Statement s = conn.createStatement();
    try {
      s.execute("drop table " + DB_TABLE_NAME);
    } catch (final SQLException e) {
      ; // ignore
    }
    s.execute("create table "
      + DB_TABLE_NAME
      + "(int_val int, bigint_val bigint, double_val double, float_val float, varchar_val varchar(40), varbinary_val varchar(40) for bit data, repeating_int_val int)");

    final PreparedStatement psInsert =
      conn.prepareStatement("insert into " + DB_TABLE_NAME + " values (?,?,?,?,?,?,?)");
    for (int i = 0; i < getNumberOfRowsToCreate(); i++) {
      psInsert.setInt(1, i);
      psInsert.setLong(2, i * 2);
      psInsert.setDouble(3, i * 1.1);
      psInsert.setFloat(4, i * 2.2f);
      psInsert.setString(5, String.valueOf(i));
      psInsert.setBytes(6, ("Attachment:" + String.valueOf(i)).getBytes("utf-8"));
      psInsert.setInt(7, i % 5);
      psInsert.execute();
    }

    // check that the database is really created.
    final int selId = getNumberOfRowsToCreate() / 2;
    final ResultSet resultSet =
      s.executeQuery("select int_val, bigint_val, double_val, float_val, varchar_val, varbinary_val, repeating_int_val from "
        + DB_TABLE_NAME + " where int_val = " + selId);
    assertTrue(resultSet.next());
    assertEquals(selId, resultSet.getInt(1));
    assertEquals(Long.valueOf(selId * 2).longValue(), resultSet.getLong(2));
    assertEquals(selId * 1.1, resultSet.getDouble(3));
    assertEquals(selId * 2.2f, resultSet.getFloat(4));
    assertEquals(String.valueOf(selId), resultSet.getString(5));
    assertEquals("Attachment:" + String.valueOf(selId), new String(resultSet.getBytes(6), "utf-8"));
    assertEquals(selId % 5, resultSet.getInt(7));
    assertFalse(resultSet.next());
  }

  /** @return the number of rows to create. */
  protected int getNumberOfRowsToCreate() {
    return 100000;
  }

  /** @return the connect URL. */
  protected String getConnectUrl() {
    return "jdbc:derby:memory:" + DB_NAME + ";create=true";
  }

  /** @return the properties to create a connection to the DB. */
  protected Properties getProperties() {
    final Properties props = new Properties();
    props.put("user", DB_USER);
    props.put("password", DB_PASSWORD);
    return props;
  }

}