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 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) 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.getAspect(), SIZE_ASPECT));
ps.setString(12, left(media.getAspect(), SIZE_ASPECTRATIO_CONTAINER));
ps.setString(13, left(media.getAspect(), 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));
} 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.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
int id = -1;
while (rs.next()) {
id = rs.getInt(1);
}
rs.close();
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.getFlavor(), SIZE_FLAVOR));
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 (JdbcSQLException 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.getFlavor(), SIZE_FLAVOR));
insert.setInt(5, sub.getType().getStableIndex());
try {
insert.executeUpdate();
} catch (JdbcSQLException 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() == 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 ArrayList<DLNAMediaInfo> getData(String name, long modified) {
ArrayList<DLNAMediaInfo> list = new ArrayList<DLNAMediaInfo>();
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.setAspect(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.setMediaparsed(true);
PreparedStatement audios =
conn.prepareStatement("SELECT * FROM AUDIOTRACKS WHERE FILEID = ?");
audios.setInt(1, id);
ResultSet subrs = audios.executeQuery();
while (subrs.next()) {
DLNAMediaAudio audio = new DLNAMediaAudio();
audio.setId(subrs.getInt("ID"));
audio.setLang(subrs.getString("LANG"));
audio.setFlavor(subrs.getString("FLAVOR"));
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();
audios.close();
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.setFlavor(subrs.getString("FLAVOR"));
sub.setType(SubtitleType.valueOfStableIndex(subrs.getInt("TYPE")));
media.getSubtitleTracksList().add(sub);
}
subrs.close();
subs.close();
list.add(media);
}
} catch (SQLException se) {
LOGGER.error(null, se);
return null;
} finally {
close(rs);
close(stmt);
close(conn);
}
return list;
}