Пример #1
0
  @Test
  public void retainKeysInBatch() throws SQLException {
    stat.executeUpdate("create table test (c1, c2);");
    PreparedStatement prep = conn.prepareStatement("insert into test values (?, ?);");
    prep.setInt(1, 10);
    prep.setString(2, "ten");
    prep.addBatch();
    prep.setInt(1, 100);
    prep.setString(2, "hundred");
    prep.addBatch();
    prep.setString(2, "one hundred");
    prep.addBatch();
    prep.setInt(1, 1000);
    prep.setString(2, "thousand");
    prep.addBatch();
    prep.executeBatch();
    prep.close();

    ResultSet rs = stat.executeQuery("select * from test;");
    assertTrue(rs.next());
    assertEquals(10, rs.getInt(1));
    assertEquals("ten", rs.getString(2));
    assertTrue(rs.next());
    assertEquals(100, rs.getInt(1));
    assertEquals("hundred", rs.getString(2));
    assertTrue(rs.next());
    assertEquals(100, rs.getInt(1));
    assertEquals("one hundred", rs.getString(2));
    assertTrue(rs.next());
    assertEquals(1000, rs.getInt(1));
    assertEquals("thousand", rs.getString(2));
    assertFalse(rs.next());
    rs.close();
  }
Пример #2
0
 @Test
 public void dblock() throws SQLException {
   stat.executeUpdate("create table test (c1);");
   stat.executeUpdate("insert into test values (1);");
   conn.prepareStatement("select * from test;").executeQuery().close();
   stat.executeUpdate("drop table test;");
 }
Пример #3
0
  @Test
  public void batch() throws SQLException {
    ResultSet rs;

    stat.executeUpdate("create table test (c1, c2, c3, c4);");
    PreparedStatement prep = conn.prepareStatement("insert into test values (?,?,?,?);");
    for (int i = 0; i < 10; i++) {
      prep.setInt(1, Integer.MIN_VALUE + i);
      prep.setFloat(2, Float.MIN_VALUE + i);
      prep.setString(3, "Hello " + i);
      prep.setDouble(4, Double.MAX_VALUE + i);
      prep.addBatch();
    }
    assertArrayEq(prep.executeBatch(), new int[] {1, 1, 1, 1, 1, 1, 1, 1, 1, 1});
    assertEquals(0, prep.executeBatch().length);
    prep.close();

    rs = stat.executeQuery("select * from test;");
    for (int i = 0; i < 10; i++) {
      assertTrue(rs.next());
      assertEquals(Integer.MIN_VALUE + i, rs.getInt(1));
      assertEquals(Float.MIN_VALUE + i, rs.getFloat(2));
      assertEquals("Hello " + i, rs.getString(3));
      assertEquals(Double.MAX_VALUE + i, rs.getDouble(4));
    }
    rs.close();
    stat.executeUpdate("drop table test;");
  }
Пример #4
0
  @Test
  public void timestamp() throws SQLException {
    connectWithJulianDayModeActivated();
    long now = System.currentTimeMillis();
    Timestamp d1 = new Timestamp(now);
    Date d2 = new Date(now);
    Time d3 = new Time(now);

    stat.execute("create table t (c1);");
    PreparedStatement prep = conn.prepareStatement("insert into t values (?);");
    prep.setTimestamp(1, d1);
    prep.executeUpdate();

    ResultSet rs = stat.executeQuery("select c1 from t;");
    assertTrue(rs.next());
    assertEquals(d1, rs.getTimestamp(1));

    rs = stat.executeQuery("select date(c1, 'localtime') from t;");
    assertTrue(rs.next());
    assertEquals(d2.toString(), rs.getString(1));

    rs = stat.executeQuery("select time(c1, 'localtime') from t;");
    assertTrue(rs.next());
    assertEquals(d3.toString(), rs.getString(1));

    rs = stat.executeQuery("select strftime('%Y-%m-%d %H:%M:%f', c1, 'localtime') from t;");
    assertTrue(rs.next());
    // assertEquals(d1.toString(), rs.getString(1)); // ms are not occurate...
  }
Пример #5
0
 @Test
 public void testScanUri() throws Exception {
   GroupByCaseTest gbt = new GroupByCaseTest();
   long ts = gbt.createTable();
   gbt.loadData(ts);
   Properties props = new Properties(TEST_PROPERTIES);
   props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
   Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
   Statement stmt = conn.createStatement();
   ResultSet rs = stmt.executeQuery("select uri from " + GROUPBYTEST_NAME);
   assertTrue(rs.next());
   assertEquals("Report1", rs.getString(1));
   assertTrue(rs.next());
   assertEquals("Report2", rs.getString(1));
   assertTrue(rs.next());
   assertEquals("Report3", rs.getString(1));
   assertTrue(rs.next());
   assertEquals("Report4", rs.getString(1));
   assertTrue(rs.next());
   assertEquals("SOQL1", rs.getString(1));
   assertTrue(rs.next());
   assertEquals("SOQL2", rs.getString(1));
   assertTrue(rs.next());
   assertEquals("SOQL3", rs.getString(1));
   assertTrue(rs.next());
   assertEquals("SOQL4", rs.getString(1));
   assertFalse(rs.next());
   conn.close();
 }
 @After
 public void dispose() throws SQLException, IOException, ClassNotFoundException {
   Connection connection = BddConnecteur.getConnection();
   Statement statement = connection.createStatement();
   statement.execute("DROP TABLE Creneau");
   statement.execute("DROP TABLE Atelier");
   statement.execute("DROP TABLE Labo");
   statement.execute("DROP TABLE Utilisateur");
   statement.close();
   connection.close();
   BddConnecteur.dispose();
 }
  @Test
  public void testAjoutCreneau() throws SQLException, IOException, ClassNotFoundException {
    Connection connection = BddConnecteur.getConnection();

    BddUtilisateur.ajout("*****@*****.**", "labri");
    BddLabo.ajout(1, "Labri");
    BddAtelier.ajoutAtelier(
        1,
        "A la poursuite d'ennemis invisibles",
        "Sciences de la vie ",
        "Campus Carreire (Hôpital Pellegrin)",
        "Labo MFP",
        "",
        "",
        "",
        "");

    // Insertion
    int idAtelier = 1;
    String jour = "2015-12-23";
    String heure = "13:00";
    int capacite = 20;
    BddCreneau.ajoutCreneau(1, jour, heure, capacite);

    // Vérification
    String sql = "SELECT * FROM Creneau WHERE idAtelier='" + idAtelier + "'";
    Statement statement = connection.createStatement();
    ResultSet rs = statement.executeQuery(sql);

    int count = 0;
    Date jourRecup;
    Time heureRecup;
    int capaciteRecup = 0;

    while (rs.next()) {
      jourRecup = rs.getDate("jour");
      heureRecup = rs.getTime("heure");
      capaciteRecup = rs.getInt("capacite");
      count++;
    }

    assertEquals(count, 1);
    // assertEquals(jourRecup, jour);
    // assertEquals(heureRecup, heure);
    assertEquals(capaciteRecup, capacite);

    // Fermeture
    rs.close();
    statement.close();
    connection.close();
  }
Пример #8
0
  @Test
  public void multiUpdate() throws SQLException {
    stat.executeUpdate("create table test (c1);");
    PreparedStatement prep = conn.prepareStatement("insert into test values (?);");

    for (int i = 0; i < 10; i++) {
      prep.setInt(1, i);
      prep.executeUpdate();
      prep.execute();
    }

    prep.close();
    stat.executeUpdate("drop table test;");
  }
Пример #9
0
  @Test
  public void date2() throws SQLException {
    Date d1 = new Date(1092941466000L);
    stat.execute("create table t (c1);");
    PreparedStatement prep =
        conn.prepareStatement("insert into t values (datetime(?/1000, 'unixepoch'));");
    prep.setDate(1, d1);
    prep.executeUpdate();

    ResultSet rs = stat.executeQuery("select strftime('%s', c1) * 1000 from t;");
    assertTrue(rs.next());
    assertEquals(d1.getTime(), rs.getLong(1));
    assertTrue(rs.getDate(1).equals(d1));
  }
Пример #10
0
 @Test
 public void batchOneParam() throws SQLException {
   stat.executeUpdate("create table test (c1);");
   PreparedStatement prep = conn.prepareStatement("insert into test values (?);");
   for (int i = 0; i < 10; i++) {
     prep.setInt(1, Integer.MIN_VALUE + i);
     prep.addBatch();
   }
   assertArrayEq(prep.executeBatch(), new int[] {1, 1, 1, 1, 1, 1, 1, 1, 1, 1});
   prep.close();
   ResultSet rs = stat.executeQuery("select count(*) from test;");
   assertTrue(rs.next());
   assertEquals(10, rs.getInt(1));
   rs.close();
 }
Пример #11
0
  @Test
  public void insert1000() throws SQLException {
    stat.executeUpdate("create table in1000 (a);");
    PreparedStatement prep = conn.prepareStatement("insert into in1000 values (?);");
    conn.setAutoCommit(false);
    for (int i = 0; i < 1000; i++) {
      prep.setInt(1, i);
      prep.executeUpdate();
    }
    conn.commit();

    ResultSet rs = stat.executeQuery("select count(a) from in1000;");
    assertTrue(rs.next());
    assertEquals(1000, rs.getInt(1));
    rs.close();
  }
Пример #12
0
  @Test
  public void testDeadlockTransactionRollback() throws SQLException {
    connection.setAutoCommit(false);

    // Failed statement
    mockConnection.setError(Jdbc.ErrorCode.DEADLOCK.getNumber(), "deadlock");
    Statement s = connection.createStatement();
    try {
      s.executeUpdate("DELETE bad");
      fail("expected exception");
    } catch (SQLException e) {
      assertEquals(Jdbc.ErrorCode.DEADLOCK.getNumber(), e.getErrorCode());
      assertEquals("40001", e.getSQLState());
    }
    assertNull(mockConnection.lastFinish);
  }
  @Test
  public void testSupprCreneauxByIdAtelier()
      throws SQLException, IOException, ClassNotFoundException {
    Connection connection = BddConnecteur.getConnection();

    BddUtilisateur.ajout("*****@*****.**", "labri");
    BddLabo.ajout(1, "Labri");
    BddAtelier.ajoutAtelier(
        1,
        "A la poursuite d'ennemis invisibles",
        "Sciences de la vie ",
        "Campus Carreire (Hôpital Pellegrin)",
        "Labo MFP",
        "",
        "",
        "",
        "");

    // Insertion
    int idAtelier = 1;
    String jour = "2015-12-23";
    String heure = "13:00";
    int oldCapacite = 20;
    int newCapacite = 40;
    BddCreneau.ajoutCreneau(1, jour, heure, oldCapacite);

    // Suppression
    BddCreneau.supprCreneauxByIdAtelier(1);

    // Vérification
    String sql = "SELECT * FROM Creneau WHERE idAtelier='" + idAtelier + "'";
    Statement statement = connection.createStatement();
    ResultSet rs = statement.executeQuery(sql);

    int count = 0;

    while (rs.next()) {
      count++;
    }

    assertEquals(count, 0);

    // Fermeture
    rs.close();
    statement.close();
    connection.close();
  }
Пример #14
0
  @SuppressWarnings("deprecation")
  @Test
  public void time() throws SQLException {
    connectWithJulianDayModeActivated();
    Time d1 = new Time(System.currentTimeMillis());

    stat.execute("create table t (c1);");
    PreparedStatement prep = conn.prepareStatement("insert into t values (?);");
    prep.setTime(1, d1);
    prep.executeUpdate();

    ResultSet rs = stat.executeQuery("select c1 from t;");
    assertTrue(rs.next());
    assertEquals(d1.getHours(), rs.getTime(1).getHours());
    assertEquals(d1.getMinutes(), rs.getTime(1).getMinutes());
    assertEquals(d1.getSeconds(), rs.getTime(1).getSeconds());
  }
Пример #15
0
 @Test
 public void changeSchema() throws SQLException {
   stat.execute("create table t (c1);");
   PreparedStatement prep = conn.prepareStatement("insert into t values (?);");
   conn.createStatement().execute("create table t2 (c2);");
   prep.setInt(1, 1000);
   prep.execute();
   prep.executeUpdate();
 }
Пример #16
0
  @Ignore
  @Test
  public void getObject() throws SQLException {
    stat.executeUpdate(
        "create table testobj (" + "c1 integer, c2 float, c3, c4 varchar, c5 bit, c6, c7);");
    PreparedStatement prep = conn.prepareStatement("insert into testobj values (?,?,?,?,?,?,?);");

    prep.setInt(1, Integer.MAX_VALUE);
    prep.setFloat(2, Float.MAX_VALUE);
    prep.setDouble(3, Double.MAX_VALUE);
    prep.setLong(4, Long.MAX_VALUE);
    prep.setBoolean(5, false);
    prep.setByte(6, (byte) 7);
    prep.setBytes(7, b1);
    prep.executeUpdate();

    ResultSet rs = stat.executeQuery("select c1,c2,c3,c4,c5,c6,c7 from testobj;");
    assertTrue(rs.next());

    assertEquals(Integer.MAX_VALUE, rs.getInt(1));
    assertEquals(Integer.MAX_VALUE, (int) rs.getLong(1));
    assertEquals(Float.MAX_VALUE, rs.getFloat(2));
    assertEquals(Double.MAX_VALUE, rs.getDouble(3));
    assertEquals(Long.MAX_VALUE, rs.getLong(4));
    assertFalse(rs.getBoolean(5));
    assertEquals((byte) 7, rs.getByte(6));
    assertArrayEq(rs.getBytes(7), b1);

    assertNotNull(rs.getObject(1));
    assertNotNull(rs.getObject(2));
    assertNotNull(rs.getObject(3));
    assertNotNull(rs.getObject(4));
    assertNotNull(rs.getObject(5));
    assertNotNull(rs.getObject(6));
    assertNotNull(rs.getObject(7));
    assertTrue(rs.getObject(1) instanceof Integer);
    assertTrue(rs.getObject(2) instanceof Double);
    assertTrue(rs.getObject(3) instanceof Double);
    assertTrue(rs.getObject(4) instanceof String);
    assertTrue(rs.getObject(5) instanceof Integer);
    assertTrue(rs.getObject(6) instanceof Integer);
    assertTrue(rs.getObject(7) instanceof byte[]);
    rs.close();
  }
Пример #17
0
  @Test
  public void date1() throws SQLException {
    Date d1 = new Date(987654321);

    stat.execute("create table t (c1);");
    PreparedStatement prep = conn.prepareStatement("insert into t values(?);");
    prep.setDate(1, d1);
    prep.executeUpdate();
    prep.setDate(1, null);
    prep.executeUpdate();

    ResultSet rs = stat.executeQuery("select c1 from t;");
    assertTrue(rs.next());
    assertEquals(d1.getTime(), rs.getLong(1));
    assertTrue(rs.getDate(1).equals(d1));
    assertTrue(rs.next());
    assertEquals(null, rs.getDate(1));
    rs.close();
  }
Пример #18
0
  @Test
  public void testFailedTransactionRollback() throws SQLException {
    connection.setAutoCommit(false);

    // Failed statement
    mockConnection.setError(42, "bad");
    Statement s = connection.createStatement();
    try {
      s.executeUpdate("DELETE bad");
      fail("expected exception");
    } catch (SQLException e) {
    }
    assertNull(mockConnection.lastFinish);

    // All future statements should also fail
    mockConnection.setUpdateCount(42);
    try {
      s.executeUpdate("DELETE good");
      fail("expected exception");
    } catch (SQLException e) {
    }

    connection.enableStrictMode(true);
    try {
      connection.commit();
      fail("expected exception");
    } catch (SQLException e) {
    }

    // rollback this transaction
    connection.rollback();
    assertNull(mockConnection.lastFinish);
    try {
      // Can't rollback: no transaction!
      connection.rollback();
      fail("expected exception");
    } catch (SQLException e) {
    }

    assertEquals(42, s.executeUpdate("DELETE good"));
  }
Пример #19
0
  void initWithTestData(final ConnectionManager connectionManager) {

    createSchema(connectionManager);

    Connection connection = null;
    Statement statement = null;
    try {
      connection = connectionManager.getConnection(null);
      statement = connection.createStatement();
      assertEquals(statement.executeUpdate("INSERT INTO " + TABLE_NAME + " values (1, 'abs')"), 1);
      assertEquals(statement.executeUpdate("INSERT INTO " + TABLE_NAME + " values (1, 'gps')"), 1);
      assertEquals(
          statement.executeUpdate("INSERT INTO " + TABLE_NAME + " values (2, 'airbags')"), 1);
      assertEquals(statement.executeUpdate("INSERT INTO " + TABLE_NAME + " values (3, 'abs')"), 1);
    } catch (Exception e) {
      throw new IllegalStateException("Unable to initialize test database", e);
    } finally {
      DBUtils.closeQuietly(connection);
      DBUtils.closeQuietly(statement);
    }
  }
Пример #20
0
  @Test
  public void utf() throws SQLException {
    ResultSet rs =
        stat.executeQuery(
            "select '"
                + utf01
                + "','"
                + utf02
                + "','"
                + utf03
                + "','"
                + utf04
                + "','"
                + utf05
                + "','"
                + utf06
                + "','"
                + utf07
                + "','"
                + utf08
                + "';");
    assertEquals(utf01, rs.getString(1));
    assertEquals(utf02, rs.getString(2));
    assertEquals(utf03, rs.getString(3));
    assertEquals(utf04, rs.getString(4));
    assertEquals(utf05, rs.getString(5));
    assertEquals(utf06, rs.getString(6));
    assertEquals(utf07, rs.getString(7));
    assertEquals(utf08, rs.getString(8));
    rs.close();

    PreparedStatement prep = conn.prepareStatement("select ?,?,?,?,?,?,?,?;");
    prep.setString(1, utf01);
    prep.setString(2, utf02);
    prep.setString(3, utf03);
    prep.setString(4, utf04);
    prep.setString(5, utf05);
    prep.setString(6, utf06);
    prep.setString(7, utf07);
    prep.setString(8, utf08);
    rs = prep.executeQuery();
    assertTrue(rs.next());
    assertEquals(utf01, rs.getString(1));
    assertEquals(utf02, rs.getString(2));
    assertEquals(utf03, rs.getString(3));
    assertEquals(utf04, rs.getString(4));
    assertEquals(utf05, rs.getString(5));
    assertEquals(utf06, rs.getString(6));
    assertEquals(utf07, rs.getString(7));
    assertEquals(utf08, rs.getString(8));
    rs.close();
  }
Пример #21
0
  public void assertQueryResultSet(
      final String query,
      final List<DBQueries.Row<Integer, String>> rows,
      final ConnectionManager connectionManager)
      throws SQLException {

    Connection connection = null;
    Statement statement = null;

    try {
      connection = connectionManager.getConnection(null);
      statement = connection.createStatement();
      ResultSet resultSet = statement.executeQuery(query);
      assertResultSetOrderAgnostic(resultSet, rows);

    } catch (Exception e) {
      throw new IllegalStateException("Unable to verify resultSet", e);
    } finally {
      DBUtils.closeQuietly(connection);
      DBUtils.closeQuietly(statement);
    }
  }
Пример #22
0
  void createSchema(final ConnectionManager connectionManager) {
    Connection connection = null;
    Statement statement = null;

    try {
      connection = connectionManager.getConnection(null);
      statement = connection.createStatement();
      assertEquals(
          statement.executeUpdate(
              "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (objectKey INTEGER, value TEXT)"),
          0);
      assertEquals(
          statement.executeUpdate(
              "CREATE INDEX IF NOT EXISTS " + INDEX_NAME + " ON " + TABLE_NAME + "(value)"),
          0);
    } catch (Exception e) {
      throw new IllegalStateException("Unable to create test database schema", e);
    } finally {
      DBUtils.closeQuietly(connection);
      DBUtils.closeQuietly(statement);
    }
  }
Пример #23
0
  @Test
  public void update() throws SQLException {
    assertEquals(conn.prepareStatement("create table s1 (c1);").executeUpdate(), 0);
    PreparedStatement prep = conn.prepareStatement("insert into s1 values (?);");
    prep.setInt(1, 3);
    assertEquals(1, prep.executeUpdate());
    prep.setInt(1, 5);
    assertEquals(1, prep.executeUpdate());
    prep.setInt(1, 7);
    assertEquals(1, prep.executeUpdate());
    prep.close();

    // check results with normal statement
    ResultSet rs = stat.executeQuery("select sum(c1) from s1;");
    assertTrue(rs.next());
    assertEquals(15, rs.getInt(1));
    rs.close();
  }
Пример #24
0
  @Test
  public void createEncrypted() throws SQLException, IOException {
    File tmp = File.createTempFile("sqlitetest", ".db");
    tmp.deleteOnExit();
    String url = "jdbc:sqlite:" + tmp.getAbsolutePath();

    Properties props = new Properties();
    final String password = "******"dog";
    props.put("key", password);
    Connection conn = DriverManager.getConnection(url, props);
    conn.setAutoCommit(false);

    Statement st = conn.createStatement();
    st.executeUpdate("create table ants (col int)");
    st.executeUpdate("insert into ants values( 300 )");
    st.executeUpdate("insert into ants values( 400 )");
    st.close();
    conn.commit();
    conn.close();

    // Try reading without key.
    props.remove("key");
    conn = DriverManager.getConnection(url, props);

    try {
      st = conn.createStatement();
      ResultSet rs = st.executeQuery("select count(*) from ants");
      fail("Database not encrypted.");
    } catch (SQLException ignore) {
    }

    conn.close();
    props.put("key", password);
    conn = DriverManager.getConnection(url, props);

    st = conn.createStatement();
    ResultSet rs = st.executeQuery("select count(*) from ants");
    assertTrue(rs.next());
    assertEquals(2, rs.getInt(1));
    conn.close();
  }
Пример #25
0
 @After
 public void close() throws SQLException {
   stat.close();
   conn.close();
 }