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

/*********************************************************************************************************************
 * Copyright (c) 2008, 2013 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
 *********************************************************************************************************************/
package org.eclipse.smila.jdbc.test;

import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.List;
import java.util.Properties;

import org.eclipse.smila.common.logging.MessageCollector;
import org.eclipse.smila.datamodel.DataFactory;
import org.eclipse.smila.datamodel.Record;
import org.eclipse.smila.datamodel.Value;
import org.eclipse.smila.jdbc.JdbcAccessService;
import org.eclipse.smila.jdbc.SqlExecutor;

/** Test for {@link JdbcAccessService} class. */
public class TestJdbcAccessService extends JdbcTestBase {

  private static final int NO_OF_ROWS = 5;

  private static final MessageCollector IGNORE = new MessageCollector.Ignore();

  @Override
  protected int getNumberOfRowsToCreate() {
    return NO_OF_ROWS;
  }

  /** tests OSGI service. */
  public void testService() throws Exception {
    final JdbcAccessService service = getService(JdbcAccessService.class);
    assertNotNull(service);
  }

  /** tests executeSQL() method. */
  public void testExecuteSql() throws Exception {
    final JdbcAccessService dbAccess = getService(JdbcAccessService.class);
    final String url = getConnectUrl();
    final Properties props = getProperties();
    final String sql = "SELECT * FROM " + DB_TABLE_NAME;

    final Collection<Record> result = dbAccess.executeSql(url, props, sql, Long.MAX_VALUE, IGNORE);
    assertEquals(NO_OF_ROWS, result.size());
  }

  /** tests executePrepared() method with single parameter for PreparedStatement. */
  public void testExecutePreparedWithSingleParam() throws Exception {
    final JdbcAccessService dbAccess = getService(JdbcAccessService.class);
    final String url = getConnectUrl();
    final Properties props = getProperties();
    final String testColumn = "INT_VAL";
    final String sql = "SELECT * FROM " + DB_TABLE_NAME + " WHERE " + testColumn + " = ?";

    try (SqlExecutor sqlExecutor = dbAccess.executePrepared(url, props, sql, Long.MAX_VALUE, IGNORE)) {
      final Record r1 = sqlExecutor.executeAndMerge(Arrays.asList(DataFactory.DEFAULT.createLongValue(2)));
      assertEquals(new Long(2), r1.getMetadata().getLongValue(testColumn));

      final Collection<Record> rc1 = sqlExecutor.execute(Arrays.asList(DataFactory.DEFAULT.createLongValue(2)));
      for (final Record rec : rc1) {
        assertEquals(new Long(2), rec.getMetadata().getLongValue(testColumn));
      }

      final Record r2 = sqlExecutor.executeAndMerge(Arrays.asList(DataFactory.DEFAULT.createLongValue(3)));
      assertEquals(new Long(3), r2.getMetadata().getLongValue(testColumn));

      final Collection<Record> rc2 = sqlExecutor.execute(Arrays.asList(DataFactory.DEFAULT.createLongValue(3)));
      for (final Record rec : rc2) {
        assertEquals(new Long(3), rec.getMetadata().getLongValue(testColumn));
      }
    }
  }

  /** tests executePrepared() method with multiple parameters for PreparedStatement. */
  public void testExecutePreparedWithMultipleParams() throws Exception {
    final JdbcAccessService dbAccess = getService(JdbcAccessService.class);
    final String url = getConnectUrl();
    final Properties props = getProperties();
    final String testColumn1 = "INT_VAL";
    final String testColumn2 = "BIGINT_VAL";
    final String sql =
      "SELECT * FROM " + DB_TABLE_NAME + " WHERE " + testColumn1 + "= ? AND " + testColumn2 + "= ?";

    try (SqlExecutor sqlExecutor = dbAccess.executePrepared(url, props, sql, Long.MAX_VALUE, IGNORE)) {
      final List<Value> params1 = new ArrayList<>();
      params1.add(DataFactory.DEFAULT.createLongValue(2));
      params1.add(DataFactory.DEFAULT.createLongValue(4));
      final Record r1 = sqlExecutor.executeAndMerge(params1);
      assertEquals(new Long(2), r1.getMetadata().getLongValue(testColumn1));
      assertEquals(new Long(4), r1.getMetadata().getLongValue(testColumn2));

      final Collection<Record> rc1 = sqlExecutor.execute(params1);
      for (final Record rec : rc1) {
        assertEquals(new Long(2), rec.getMetadata().getLongValue(testColumn1));
        assertEquals(new Long(4), rec.getMetadata().getLongValue(testColumn2));
      }

      final List<Value> params2 = new ArrayList<>();
      params2.add(DataFactory.DEFAULT.createLongValue(3));
      params2.add(DataFactory.DEFAULT.createLongValue(6));
      final Record r2 = sqlExecutor.executeAndMerge(params2);
      assertEquals(new Long(3), r2.getMetadata().getLongValue(testColumn1));
      assertEquals(new Long(6), r2.getMetadata().getLongValue(testColumn2));

      final Collection<Record> rc2 = sqlExecutor.execute(params2);
      for (final Record rec : rc2) {
        assertEquals(new Long(3), rec.getMetadata().getLongValue(testColumn1));
        assertEquals(new Long(6), rec.getMetadata().getLongValue(testColumn2));
      }
    }
  }

  public void testExecutePreparedWithNullParam() throws Exception {
    final JdbcAccessService dbAccess = getService(JdbcAccessService.class);
    final String url = getConnectUrl();
    final Properties props = getProperties();
    final String testColumn = "INT_VAL";
    final String sql = "SELECT * FROM " + DB_TABLE_NAME + " WHERE " + testColumn + " = ?";

    try (SqlExecutor sqlExecutor = dbAccess.executePrepared(url, props, sql, Long.MAX_VALUE, IGNORE)) {
      try {
        sqlExecutor.executeAndMerge(Arrays.asList((Value) null));
        fail("should not work.");
      } catch (final IllegalArgumentException ex) {
        ; // expected exception.
      }

      try {
        sqlExecutor.execute(Arrays.asList((Value) null));
        fail("should not work.");
      } catch (final IllegalArgumentException ex) {
        ; // expected exception.
      }
    }
  }

  public void testExecutePreparedUpdate() throws Exception {
    final JdbcAccessService dbAccess = getService(JdbcAccessService.class);
    final String url = getConnectUrl();
    final Properties props = getProperties();
    final String dbTableName = "testExecutePreparedUpdate";
    final List<Value> noParams = Collections.emptyList();
    final String createSql =
      "create table " + dbTableName + "(int_val int,  double_val double, varchar_val varchar(40))";
    try (SqlExecutor sqlExecutor = dbAccess.executePrepared(url, props, createSql, Long.MAX_VALUE, IGNORE)) {
      assertEquals(0, sqlExecutor.executeUpdate(noParams));
    }
    final String insertSql = "insert into " + dbTableName + " values (?,?,?)";
    try (SqlExecutor sqlExecutor = dbAccess.executePrepared(url, props, insertSql, Long.MAX_VALUE, IGNORE)) {
      final List<Value> params = new ArrayList<>();
      params.add(DataFactory.DEFAULT.createLongValue(42));
      params.add(DataFactory.DEFAULT.createDoubleValue(Math.E));
      params.add(DataFactory.DEFAULT.createStringValue("towel"));
      assertEquals(1, sqlExecutor.executeUpdate(params));
    }
    final String deleteSql = "delete from " + dbTableName;
    try (SqlExecutor sqlExecutor = dbAccess.executePrepared(url, props, deleteSql, Long.MAX_VALUE, IGNORE)) {
      assertEquals(1, sqlExecutor.executeUpdate(noParams));
    }
  }

  public void testExecutePreparedUpdateWithNull() throws Exception {
    final JdbcAccessService dbAccess = getService(JdbcAccessService.class);
    final String url = getConnectUrl();
    final Properties props = getProperties();
    final String dbTableName = "testExecutePreparedUpdateWithNull";
    final List<Value> noParams = Collections.emptyList();
    final String createSql =
      "create table " + dbTableName + "(int_val int,  double_val double, varchar_val varchar(40))";
    try (SqlExecutor sqlExecutor = dbAccess.executePrepared(url, props, createSql, Long.MAX_VALUE, IGNORE)) {
      assertEquals(0, sqlExecutor.executeUpdate(noParams));
    }
    final String insertSql = "insert into " + dbTableName + " values (?,?,?)";
    try (SqlExecutor sqlExecutor = dbAccess.executePrepared(url, props, insertSql, Long.MAX_VALUE, IGNORE)) {
      final List<Value> params = new ArrayList<>();
      params.add(DataFactory.DEFAULT.createLongValue(42));
      params.add(DataFactory.DEFAULT.createDoubleValue(Math.E));
      params.add(null);
      assertEquals(1, sqlExecutor.executeUpdate(params, Types.VARCHAR));
    }
    final String deleteSql = "delete from " + dbTableName;
    try (SqlExecutor sqlExecutor = dbAccess.executePrepared(url, props, deleteSql, Long.MAX_VALUE, IGNORE)) {
      assertEquals(1, sqlExecutor.executeUpdate(noParams));
    }
  }
}