private WebSocketChannelDTO getChannel(int channelId) throws SQLException, DatabaseException {
   if (!channelCache.containsKey(channelId)) {
     WebSocketChannelDTO criteria = new WebSocketChannelDTO();
     criteria.id = channelId;
     List<WebSocketChannelDTO> channels = getChannels(criteria);
     if (channels.size() == 1) {
       channelCache.put(channelId, channels.get(0));
     } else {
       throw new SQLException("Channel '" + channelId + "' not found!");
     }
   }
   return (WebSocketChannelDTO) channelCache.get(channelId);
 }
  private List<WebSocketChannelDTO> buildChannelDTOs(ResultSet rs) throws SQLException {
    ArrayList<WebSocketChannelDTO> channels = new ArrayList<>();
    try {
      while (rs.next()) {
        WebSocketChannelDTO channel = new WebSocketChannelDTO();
        channel.id = rs.getInt("channel_id");
        channel.host = rs.getString("host");
        channel.port = rs.getInt("port");
        channel.url = rs.getString("url");
        channel.startTimestamp = rs.getTimestamp("start_timestamp").getTime();

        Time endTs = rs.getTime("end_timestamp");
        channel.endTimestamp = (endTs != null) ? endTs.getTime() : null;

        channel.historyId = rs.getInt("history_id");

        channels.add(channel);
      }
    } finally {
      rs.close();
    }

    channels.trimToSize();

    return channels;
  }
  public void insertOrUpdateChannel(WebSocketChannelDTO channel) throws DatabaseException {
    try {
      synchronized (this) {
        if (getConnection().isClosed()) {
          // temporarily buffer channels and insert/update later
          channelsBuffer.offer(channel);
          return;
        }

        do {
          PreparedStatement stmt;
          boolean addIdOnSuccess = false;

          // first, find out if already inserted
          if (channelIds.contains(channel.id)) {
            // proceed with update
            stmt = psUpdateChannel;
          } else {
            // proceed with insert
            stmt = psInsertChannel;
            addIdOnSuccess = true;
            logger.info("insert channel: " + channel.toString());
          }

          if (logger.isDebugEnabled()) {
            logger.debug("url (length " + channel.url.length() + "):" + channel.url);
          }

          stmt.setString(1, channel.host);
          stmt.setInt(2, channel.port);
          stmt.setString(3, channel.url);
          stmt.setTimestamp(
              4, (channel.startTimestamp != null) ? new Timestamp(channel.startTimestamp) : null);
          stmt.setTimestamp(
              5, (channel.endTimestamp != null) ? new Timestamp(channel.endTimestamp) : null);
          stmt.setNull(6, Types.INTEGER);
          stmt.setInt(7, channel.id);

          stmt.execute();
          if (addIdOnSuccess) {
            channelIds.add(channel.id);
          }

          if (channel.historyId != null) {
            psUpdateHistoryFk.setInt(1, channel.historyId);
            psUpdateHistoryFk.setInt(2, channel.id);
            try {
              psUpdateHistoryFk.execute();
            } catch (SQLException e) {
              // safely ignore this exception
              // on shutdown, the history table is cleaned before
              // WebSocket channels are closed and updated
              if (logger.isDebugEnabled()) {
                logger.debug(e.getMessage(), e);
              }
            }
          }

          channel = channelsBuffer.poll();
        } while (channel != null);
      }
    } catch (SQLException e) {
      throw new DatabaseException(e);
    }
  }