package com.atlassian.refapp.test.plugin.api;
   
   import com.atlassian.functest.junit.SpringAwareTestCase;
   import com.atlassian.plugin.spring.scanner.annotation.imports.ComponentImport;
   import com.atlassian.refapp.api.ConnectionProvider;
   import org.junit.Test;
   
   import java.sql.ResultSet;
   import java.sql.SQLException;
  
  import static org.hamcrest.MatcherAssert.assertThat;
  import static org.hamcrest.core.Is.is;
  import static org.junit.Assert.fail;
  
  /**
   * Ensures that the {@link ConnectionProvider#connection()} and its schema provide a valid database connection that may
   * be used for DDL and DML.
   *
   * An attempt is made to drop existing database objects, to prevent spurious faliures.
   */
  public class ConnectionProviderTest extends SpringAwareTestCase {
  
      @ComponentImport
      private ConnectionProvider connectionProvider;
  
      public void setConnectionProvider(ConnectionProvider connectionProvider) {
          this.connectionProvider = connectionProvider;
      }
  
      /**
       * Database vendor agnostic DML/DDL test
       */
      @Test
      public void pokeDatabase() {
  
          // not all databases need a schema
          final String schemaPrefix = connectionProvider.schema().map(s -> s + '.').orElse("");
  
          try {
              // drop a table if it's there
              connectionProvider.connection()
                      .prepareStatement("drop table " + schemaPrefix + "refapptest")
                      .execute();
          } catch (SQLException e) {
              // all good - no existing tables in the database
          }
  
          try {
              // create a table
              connectionProvider.connection()
                      .prepareStatement("create table " + schemaPrefix + "refapptest (refappcol varchar(255))")
                      .execute();
  
              // insert a row
              connectionProvider.connection()
                      .prepareStatement("insert into " + schemaPrefix + "refapptest (refappcol) values ('something')")
                      .execute();
  
              // select the above row
              final String select = "select refappcol from " + schemaPrefix + "refapptest";
              final ResultSet rs = connectionProvider.connection()
                      .prepareStatement(select)
                      .executeQuery();
  
              // ensure that a value is correctly returned
              assertThat("no results returned for '" + select + "'", rs.next(), is(true));
              assertThat(rs.getString("refappcol"), is("something"));
  
          } catch (SQLException e) {
              // functest-plugin 0.8.2 doesn't consider an exception from a test as a failure
              fail(e.toString());
          }
      }
  }