/**
   * Inserts a new subscription for the given user (subscriber ID) to the given feed ID.
   *
   * @return the ID of the new subscription
   */
  public int insert(Connection cnn, int subscriberId, int feedId) throws SQLException {
    final PreparedStatement stmt =
        cnn.prepareStatement(
            "insert into feedreader.FeedSubscriptions (subscriber, feedId) values (?, ?) returning id");
    try {
      stmt.setInt(1, subscriberId);
      stmt.setInt(2, feedId);

      if (stmt.execute()) {
        final ResultSet rst = stmt.getResultSet();
        try {
          if (rst.next()) {
            return rst.getInt(1);
          } else {
            throw new IllegalStateException("Unable to insert FeedSubscription");
          }

        } finally {
          DbUtils.close(rst);
        }
      } else {
        throw new IllegalStateException("Unable to insert FeedSubscription");
      }

    } finally {
      DbUtils.close(stmt);
    }
  }
  private int insertUserSession(Connection cnn, int userId) throws SQLException {
    Preconditions.checkArgument(cnn != null, "cnn should not be null");
    final PreparedStatement stmt =
        cnn.prepareStatement(
            "insert into feedreader.UserSessions (userId) values (?) returning id");
    try {
      stmt.setInt(1, userId);

      if (stmt.execute()) {
        final ResultSet rst = stmt.getResultSet();
        try {
          if (rst.next()) {
            return rst.getInt("id");
          } else {
            throw new IllegalStateException("Error while inserting user");
          }
        } finally {
          DbUtils.close(rst);
        }
      } else {
        throw new IllegalStateException("Error while inserting User");
      }
    } finally {
      DbUtils.close(stmt);
    }
  }
  @Test
  public void testLoadByIdWithInvalidId() throws Exception {
    final Connection cnn = databaseTestUtils.getConnection();
    try {
      // attempt to load the user using the handler
      final UserEntityHandler handler = new UserEntityHandler();
      final User user = handler.loadUserById(cnn, -1);

      assertNull("Expected get to return null since the ID could not be found", user);
    } finally {
      DbUtils.close(cnn);
    }
  }
  @Test
  public void testInsert() throws Exception {
    final Connection cnn = databaseTestUtils.getConnection();
    try {
      final int userId = databaseTestUtils.ensureTestUser(cnn);

      // insert the entity
      final UserSessionEntityHandler handler = new UserSessionEntityHandler();
      final int userSessionId = handler.insert(cnn, userId);

      assertTrue("Expected the insert to return a valid ID", userSessionId > 0);
    } finally {
      DbUtils.close(cnn);
    }
  }
  @Test
  public void testLoadById() throws Exception {
    final Connection cnn = databaseTestUtils.getConnection();
    try {
      // insert a test user into the database
      int testUserId = databaseTestUtils.insertTestUser(cnn);

      // attempt to load the user using the handler
      final UserEntityHandler handler = new UserEntityHandler();
      final User user = handler.loadUserById(cnn, testUserId);

      assertTrue("Expected get to return a valid user account", user != null);
      assertEquals(
          "Expected email to be the one specified in the database",
          "*****@*****.**",
          user.getEmail());
    } finally {
      DbUtils.close(cnn);
    }
  }
  @Test
  public void testFindUserSessionById() throws Exception {
    final Connection cnn = databaseTestUtils.getConnection();
    try {
      int userId = databaseTestUtils.ensureTestUser(cnn);
      int sessionId = insertUserSession(cnn, userId);

      // insert the entity
      final UserSessionEntityHandler handler = new UserSessionEntityHandler();
      final UserSession userSession = handler.findUserSessionById(cnn, sessionId);

      assertNotNull("Expected a session to be retrieved", userSession);
      assertEquals(
          "Expected the ID of the loaded session to be the same",
          Integer.valueOf(sessionId),
          userSession.getId());
    } finally {
      DbUtils.close(cnn);
    }
  }
  @Test
  public void testInsert() throws Exception {
    final Connection cnn = databaseTestUtils.getConnection();
    try {
      int recordCountBefore = countUsers(cnn);

      // persist the user
      final UserEntityHandler handler = new UserEntityHandler();
      final int userId = handler.insert(cnn, "test@test" + (new Random().nextInt()) + ".com");

      assertTrue("Expected the user to have an ID after a persist", userId > 0);

      int recordCountAfter = countUsers(cnn);
      assertEquals(
          "Expected there to be only one record in the database after the persist",
          recordCountBefore + 1,
          recordCountAfter);
    } finally {
      DbUtils.close(cnn);
    }
  }
 private int countUsers(Connection cnn) throws SQLException {
   return DbUtils.executeAggregate(cnn, "select count(id) from feedreader.Users");
 }