public void cleanup() {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
      conn = getConnection();
      ps = conn.prepareStatement("SELECT COUNT(*) FROM FILES");
      rs = ps.executeQuery();
      dbCount = 0;

      if (rs.next()) {
        dbCount = rs.getInt(1);
      }

      rs.close();
      ps.close();
      PMS.get().getFrame().setStatusLine(Messages.getString("DLNAMediaDatabase.2") + " 0%");
      int i = 0;
      int oldpercent = 0;

      if (dbCount > 0) {
        ps =
            conn.prepareStatement(
                "SELECT FILENAME, MODIFIED, ID FROM FILES",
                ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_UPDATABLE);
        rs = ps.executeQuery();
        while (rs.next()) {
          String filename = rs.getString("FILENAME");
          long modified = rs.getTimestamp("MODIFIED").getTime();
          File file = new File(filename);
          if (!file.exists() || file.lastModified() != modified) {
            rs.deleteRow();
          }
          i++;
          int newpercent = i * 100 / dbCount;
          if (newpercent > oldpercent) {
            PMS.get()
                .getFrame()
                .setStatusLine(Messages.getString("DLNAMediaDatabase.2") + newpercent + "%");
            oldpercent = newpercent;
          }
        }
      }
    } catch (SQLException se) {
      LOGGER.error(null, se);
    } finally {
      close(rs);
      close(ps);
      close(conn);
    }
  }
 public ArrayList<File> getFiles(String sql) {
   ArrayList<File> list = new ArrayList<>();
   Connection conn = null;
   ResultSet rs = null;
   PreparedStatement ps = null;
   try {
     conn = getConnection();
     ps =
         conn.prepareStatement(
             sql.toLowerCase().startsWith("select")
                 ? sql
                 : ("SELECT FILENAME, MODIFIED FROM FILES WHERE " + sql));
     rs = ps.executeQuery();
     while (rs.next()) {
       String filename = rs.getString("FILENAME");
       long modified = rs.getTimestamp("MODIFIED").getTime();
       File file = new File(filename);
       if (file.exists() && file.lastModified() == modified) {
         list.add(file);
       }
     }
   } catch (SQLException se) {
     LOGGER.error(null, se);
     return null;
   } finally {
     close(rs);
     close(ps);
     close(conn);
   }
   return list;
 }
 public ArrayList<String> getStrings(String sql) {
   ArrayList<String> list = new ArrayList<>();
   Connection conn = null;
   ResultSet rs = null;
   PreparedStatement ps = null;
   try {
     conn = getConnection();
     ps = conn.prepareStatement(sql);
     rs = ps.executeQuery();
     while (rs.next()) {
       String str = rs.getString(1);
       if (isBlank(str)) {
         if (!list.contains(NONAME)) {
           list.add(NONAME);
         }
       } else if (!list.contains(str)) {
         list.add(str);
       }
     }
   } catch (SQLException se) {
     LOGGER.error(null, se);
     return null;
   } finally {
     close(rs);
     close(ps);
     close(conn);
   }
   return list;
 }
 public synchronized void updateThumbnail(
     String name, long modified, int type, DLNAMediaInfo media) {
   Connection conn = null;
   PreparedStatement ps = null;
   try {
     conn = getConnection();
     ps = conn.prepareStatement("UPDATE FILES SET THUMB = ? WHERE FILENAME = ? AND MODIFIED = ?");
     ps.setString(2, name);
     ps.setTimestamp(3, new Timestamp(modified));
     if (media != null) {
       ps.setBytes(1, media.getThumb());
     } else {
       ps.setNull(1, Types.BINARY);
     }
     ps.executeUpdate();
   } catch (SQLException se) {
     if (se.getErrorCode() == 23001) {
       LOGGER.debug(
           "Duplicate key while inserting this entry: "
               + name
               + " into the database: "
               + se.getMessage());
     } else {
       LOGGER.error(null, se);
     }
   } finally {
     close(ps);
     close(conn);
   }
 }
 public boolean isDataExists(String name, long modified) {
   boolean found = false;
   Connection conn = null;
   ResultSet rs = null;
   PreparedStatement stmt = null;
   try {
     conn = getConnection();
     stmt = conn.prepareStatement("SELECT * FROM FILES WHERE FILENAME = ? AND MODIFIED = ?");
     stmt.setString(1, name);
     stmt.setTimestamp(2, new Timestamp(modified));
     rs = stmt.executeQuery();
     while (rs.next()) {
       found = true;
     }
   } catch (SQLException se) {
     LOGGER.error(null, se);
     return false;
   } finally {
     close(rs);
     close(stmt);
     close(conn);
   }
   return found;
 }
  public synchronized void insertData(String name, long modified, int type, DLNAMediaInfo media) {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
      conn = getConnection();
      ps =
          conn.prepareStatement(
              "INSERT INTO FILES(FILENAME, MODIFIED, TYPE, DURATION, BITRATE, WIDTH, HEIGHT, SIZE, CODECV, "
                  + "FRAMERATE, ASPECT, ASPECTRATIOCONTAINER, ASPECTRATIOVIDEOTRACK, REFRAMES, AVCLEVEL, BITSPERPIXEL, "
                  + "THUMB, CONTAINER, MODEL, EXPOSURE, ORIENTATION, ISO, MUXINGMODE, FRAMERATEMODE, STEREOSCOPY, "
                  + "MATRIXCOEFFICIENTS, TITLECONTAINER, TITLEVIDEOTRACK, VIDEOTRACKCOUNT, IMAGECOUNT, BITDEPTH) VALUES "
                  + "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
      ps.setString(1, name);
      ps.setTimestamp(2, new Timestamp(modified));
      ps.setInt(3, type);
      if (media != null) {
        if (media.getDuration() != null) {
          ps.setDouble(4, media.getDurationInSeconds());
        } else {
          ps.setNull(4, Types.DOUBLE);
        }

        int databaseBitrate = 0;
        if (type != Format.IMAGE) {
          databaseBitrate = media.getBitrate();
          if (databaseBitrate == 0) {
            LOGGER.debug("Could not parse the bitrate from: " + name);
          }
        }
        ps.setInt(5, databaseBitrate);

        ps.setInt(6, media.getWidth());
        ps.setInt(7, media.getHeight());
        ps.setLong(8, media.getSize());
        ps.setString(9, left(media.getCodecV(), SIZE_CODECV));
        ps.setString(10, left(media.getFrameRate(), SIZE_FRAMERATE));
        ps.setString(11, left(media.getAspectRatioDvdIso(), SIZE_ASPECTRATIO_DVDISO));
        ps.setString(12, left(media.getAspectRatioContainer(), SIZE_ASPECTRATIO_CONTAINER));
        ps.setString(13, left(media.getAspectRatioVideoTrack(), SIZE_ASPECTRATIO_VIDEOTRACK));
        ps.setByte(14, media.getReferenceFrameCount());
        ps.setString(15, left(media.getAvcLevel(), SIZE_AVC_LEVEL));
        ps.setInt(16, media.getBitsPerPixel());
        ps.setBytes(17, media.getThumb());
        ps.setString(18, left(media.getContainer(), SIZE_CONTAINER));
        if (media.getExtras() != null) {
          ps.setString(19, left(media.getExtrasAsString(), SIZE_MODEL));
        } else {
          ps.setString(19, left(media.getModel(), SIZE_MODEL));
        }
        ps.setInt(20, media.getExposure());
        ps.setInt(21, media.getOrientation());
        ps.setInt(22, media.getIso());
        ps.setString(23, left(media.getMuxingModeAudio(), SIZE_MUXINGMODE));
        ps.setString(24, left(media.getFrameRateMode(), SIZE_FRAMERATE_MODE));
        ps.setString(25, left(media.getStereoscopy(), SIZE_STEREOSCOPY));
        ps.setString(26, left(media.getMatrixCoefficients(), SIZE_MATRIX_COEFFICIENTS));
        ps.setString(27, left(media.getFileTitleFromMetadata(), SIZE_TITLE));
        ps.setString(28, left(media.getVideoTrackTitleFromMetadata(), SIZE_TITLE));
        ps.setInt(29, media.getVideoTrackCount());
        ps.setInt(30, media.getImageCount());
        ps.setInt(31, media.getVideoBitDepth());
      } else {
        ps.setString(4, null);
        ps.setInt(5, 0);
        ps.setInt(6, 0);
        ps.setInt(7, 0);
        ps.setLong(8, 0);
        ps.setString(9, null);
        ps.setString(10, null);
        ps.setString(11, null);
        ps.setString(12, null);
        ps.setString(13, null);
        ps.setByte(14, (byte) -1);
        ps.setString(15, null);
        ps.setInt(16, 0);
        ps.setBytes(17, null);
        ps.setString(18, null);
        ps.setString(19, null);
        ps.setInt(20, 0);
        ps.setInt(21, 0);
        ps.setInt(22, 0);
        ps.setString(23, null);
        ps.setString(24, null);
        ps.setString(25, null);
        ps.setString(26, null);
        ps.setString(27, null);
        ps.setString(28, null);
        ps.setInt(29, 0);
        ps.setInt(30, 0);
        ps.setInt(31, 0);
      }
      ps.executeUpdate();
      int id;
      try (ResultSet rs = ps.getGeneratedKeys()) {
        id = -1;
        while (rs.next()) {
          id = rs.getInt(1);
        }
      }
      if (media != null && id > -1) {
        PreparedStatement insert = null;
        if (media.getAudioTracksList().size() > 0) {
          insert =
              conn.prepareStatement(
                  "INSERT INTO AUDIOTRACKS VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
          for (DLNAMediaAudio audio : media.getAudioTracksList()) {
            insert.clearParameters();
            insert.setInt(1, id);
            insert.setInt(2, audio.getId());
            insert.setString(3, left(audio.getLang(), SIZE_LANG));
            insert.setString(4, left(audio.getAudioTrackTitleFromMetadata(), SIZE_TITLE));
            insert.setInt(5, audio.getAudioProperties().getNumberOfChannels());
            insert.setString(6, left(audio.getSampleFrequency(), SIZE_SAMPLEFREQ));
            insert.setString(7, left(audio.getCodecA(), SIZE_CODECA));
            insert.setInt(8, audio.getBitsperSample());
            insert.setString(9, left(trimToEmpty(audio.getAlbum()), SIZE_ALBUM));
            insert.setString(10, left(trimToEmpty(audio.getArtist()), SIZE_ARTIST));
            insert.setString(11, left(trimToEmpty(audio.getSongname()), SIZE_SONGNAME));
            insert.setString(12, left(trimToEmpty(audio.getGenre()), SIZE_GENRE));
            insert.setInt(13, audio.getYear());
            insert.setInt(14, audio.getTrack());
            insert.setInt(15, audio.getAudioProperties().getAudioDelay());
            insert.setString(16, left(trimToEmpty(audio.getMuxingModeAudio()), SIZE_MUXINGMODE));
            insert.setInt(17, audio.getBitRate());

            try {
              insert.executeUpdate();
            } catch (SQLException e) {
              if (e.getErrorCode() == 23505) {
                LOGGER.debug(
                    "A duplicate key error occurred while trying to store the following file's audio information in the database: "
                        + name);
              } else {
                LOGGER.debug(
                    "An error occurred while trying to store the following file's audio information in the database: "
                        + name);
              }
              LOGGER.debug("The error given by jdbc was: " + e);
            }
          }
        }

        if (media.getSubtitleTracksList().size() > 0) {
          insert = conn.prepareStatement("INSERT INTO SUBTRACKS VALUES (?, ?, ?, ?, ?)");
          for (DLNAMediaSubtitle sub : media.getSubtitleTracksList()) {
            if (sub.getExternalFile() == null) { // no save of external subtitles
              insert.clearParameters();
              insert.setInt(1, id);
              insert.setInt(2, sub.getId());
              insert.setString(3, left(sub.getLang(), SIZE_LANG));
              insert.setString(4, left(sub.getSubtitlesTrackTitleFromMetadata(), SIZE_TITLE));
              insert.setInt(5, sub.getType().getStableIndex());
              try {
                insert.executeUpdate();
              } catch (SQLException e) {
                if (e.getErrorCode() == 23505) {
                  LOGGER.debug(
                      "A duplicate key error occurred while trying to store the following file's subtitle information in the database: "
                          + name);
                } else {
                  LOGGER.debug(
                      "An error occurred while trying to store the following file's subtitle information in the database: "
                          + name);
                }
                LOGGER.debug("The error given by jdbc was: " + e);
              }
            }
          }
        }

        close(insert);
      }
    } catch (SQLException se) {
      if (se.getErrorCode() == 23505) {
        LOGGER.debug(
            "Duplicate key while inserting this entry: "
                + name
                + " into the database: "
                + se.getMessage());
      } else {
        LOGGER.error(null, se);
      }
    } finally {
      close(ps);
      close(conn);
    }
  }
  public ArrayList<DLNAMediaInfo> getData(String name, long modified) {
    ArrayList<DLNAMediaInfo> list = new ArrayList<>();
    Connection conn = null;
    ResultSet rs = null;
    PreparedStatement stmt = null;
    try {
      conn = getConnection();
      stmt = conn.prepareStatement("SELECT * FROM FILES WHERE FILENAME = ? AND MODIFIED = ?");
      stmt.setString(1, name);
      stmt.setTimestamp(2, new Timestamp(modified));
      rs = stmt.executeQuery();
      while (rs.next()) {
        DLNAMediaInfo media = new DLNAMediaInfo();
        int id = rs.getInt("ID");
        media.setDuration(toDouble(rs, "DURATION"));
        media.setBitrate(rs.getInt("BITRATE"));
        media.setWidth(rs.getInt("WIDTH"));
        media.setHeight(rs.getInt("HEIGHT"));
        media.setSize(rs.getLong("SIZE"));
        media.setCodecV(rs.getString("CODECV"));
        media.setFrameRate(rs.getString("FRAMERATE"));
        media.setAspectRatioDvdIso(rs.getString("ASPECT"));
        media.setAspectRatioContainer(rs.getString("ASPECTRATIOCONTAINER"));
        media.setAspectRatioVideoTrack(rs.getString("ASPECTRATIOVIDEOTRACK"));
        media.setReferenceFrameCount(rs.getByte("REFRAMES"));
        media.setAvcLevel(rs.getString("AVCLEVEL"));
        media.setBitsPerPixel(rs.getInt("BITSPERPIXEL"));
        media.setThumb(rs.getBytes("THUMB"));
        media.setContainer(rs.getString("CONTAINER"));
        media.setModel(rs.getString("MODEL"));
        if (media.getModel() != null && !FormatConfiguration.JPG.equals(media.getContainer())) {
          media.setExtrasAsString(media.getModel());
        }
        media.setExposure(rs.getInt("EXPOSURE"));
        media.setOrientation(rs.getInt("ORIENTATION"));
        media.setIso(rs.getInt("ISO"));
        media.setMuxingMode(rs.getString("MUXINGMODE"));
        media.setFrameRateMode(rs.getString("FRAMERATEMODE"));
        media.setStereoscopy(rs.getString("STEREOSCOPY"));
        media.setMatrixCoefficients(rs.getString("MATRIXCOEFFICIENTS"));
        media.setFileTitleFromMetadata(rs.getString("TITLECONTAINER"));
        media.setVideoTrackTitleFromMetadata(rs.getString("TITLEVIDEOTRACK"));
        media.setVideoTrackCount(rs.getInt("VIDEOTRACKCOUNT"));
        media.setImageCount(rs.getInt("IMAGECOUNT"));
        media.setVideoBitDepth(rs.getInt("BITDEPTH"));
        media.setMediaparsed(true);
        ResultSet subrs;
        try (PreparedStatement audios =
            conn.prepareStatement("SELECT * FROM AUDIOTRACKS WHERE FILEID = ?")) {
          audios.setInt(1, id);
          subrs = audios.executeQuery();
          while (subrs.next()) {
            DLNAMediaAudio audio = new DLNAMediaAudio();
            audio.setId(subrs.getInt("ID"));
            audio.setLang(subrs.getString("LANG"));
            audio.setAudioTrackTitleFromMetadata(subrs.getString("TITLE"));
            audio.getAudioProperties().setNumberOfChannels(subrs.getInt("NRAUDIOCHANNELS"));
            audio.setSampleFrequency(subrs.getString("SAMPLEFREQ"));
            audio.setCodecA(subrs.getString("CODECA"));
            audio.setBitsperSample(subrs.getInt("BITSPERSAMPLE"));
            audio.setAlbum(subrs.getString("ALBUM"));
            audio.setArtist(subrs.getString("ARTIST"));
            audio.setSongname(subrs.getString("SONGNAME"));
            audio.setGenre(subrs.getString("GENRE"));
            audio.setYear(subrs.getInt("YEAR"));
            audio.setTrack(subrs.getInt("TRACK"));
            audio.getAudioProperties().setAudioDelay(subrs.getInt("DELAY"));
            audio.setMuxingModeAudio(subrs.getString("MUXINGMODE"));
            audio.setBitRate(subrs.getInt("BITRATE"));
            media.getAudioTracksList().add(audio);
          }
          subrs.close();
        }
        try (PreparedStatement subs =
            conn.prepareStatement("SELECT * FROM SUBTRACKS WHERE FILEID = ?")) {
          subs.setInt(1, id);
          subrs = subs.executeQuery();
          while (subrs.next()) {
            DLNAMediaSubtitle sub = new DLNAMediaSubtitle();
            sub.setId(subrs.getInt("ID"));
            sub.setLang(subrs.getString("LANG"));
            sub.setSubtitlesTrackTitleFromMetadata(subrs.getString("TITLE"));
            sub.setType(SubtitleType.valueOfStableIndex(subrs.getInt("TYPE")));
            media.getSubtitleTracksList().add(sub);
          }
          subrs.close();
        }

        list.add(media);
      }
    } catch (SQLException se) {
      LOGGER.error(null, se);
      return null;
    } finally {
      close(rs);
      close(stmt);
      close(conn);
    }
    return list;
  }