1 package mircoders.storage;
8 import freemarker.template.*;
14 import mircoders.entity.*;
17 * <b>Diese Klasse implementiert die Datenbankverbindung zur MetaObjekt-Tabelle
22 public class DatabaseContentToMedia extends Database implements StorageObject{
24 private static DatabaseContentToMedia instance;
26 public static DatabaseContentToMedia getInstance()
27 throws StorageObjectException {
28 if (instance == null) {
29 instance = new DatabaseContentToMedia();
30 instance.myselfDatabase = instance;
35 private DatabaseContentToMedia()
36 throws StorageObjectException {
39 this.hasTimestamp = false;
40 this.theTable="content_x_media";
44 * get all the media-files belonging to a content entity
47 public EntityList getMedia(EntityContent content)
48 throws StorageObjectException {
49 EntityList returnList=null;
50 if (content != null) {
51 // get all to_topic from media_x_topic
52 String id = content.getId();
53 //this is not supported by mysql
54 String subselect = "id in (select media_id from " + theTable + " where content_id=" + id+")";
57 returnList = DatabaseMedia.getInstance().selectByWhereClause(subselect,-1);
58 } catch (Exception e) {
59 theLog.printDebugInfo("-- get media failed " + e.toString());
60 throw new StorageObjectException("-- get media failed " + e.toString());
67 * get all the audio belonging to a content entity
70 public EntityList getAudio(EntityContent content)
71 throws StorageObjectException {
72 EntityList returnList=null;
73 if (content != null) {
74 // get all to_topic from media_x_topic
75 String id = content.getId();
76 //this is not supported by mysql
77 String subselect = "id in (select media_id from " + theTable + " where content_id=" + id+")";
80 returnList = DatabaseAudio.getInstance().selectByWhereClause(subselect,-1);
81 } catch (Exception e) {
82 theLog.printDebugInfo("-- get audio failed " + e.toString());
83 throw new StorageObjectException("-- get audio failed " + e.toString());
90 * get all the video belonging to a content entity
93 public EntityList getVideo(EntityContent content)
94 throws StorageObjectException {
95 EntityList returnList=null;
96 if (content != null) {
97 // get all to_topic from media_x_topic
98 String id = content.getId();
99 //this is not supported by mysql
100 String subselect = "id in (select media_id from " + theTable + " where content_id=" + id+")";
103 returnList = DatabaseVideo.getInstance().selectByWhereClause(subselect,-1);
104 } catch (Exception e) {
105 theLog.printDebugInfo("-- get video failed " + e.toString());
106 throw new StorageObjectException("-- get video failed " + e.toString());
113 * get all the images belonging to a content entity
116 public EntityList getImages(EntityContent content)
117 throws StorageObjectException {
118 EntityList returnList=null;
119 if (content != null) {
120 // get all to_topic from media_x_topic
121 String id = content.getId();
122 //this is not supported by mysql
123 String subselect = "id in (select media_id from " + theTable + " where content_id=" + id+")";
126 returnList = DatabaseImages.getInstance().selectByWhereClause(subselect,-1);
127 } catch (Exception e) {
128 theLog.printDebugInfo("-- get images failed " + e.toString());
129 throw new StorageObjectException("-- get images failed " + e.toString());
137 * get all the uploaded/other Media belonging to a content entity
140 public EntityList getOther(EntityContent content)
141 throws StorageObjectException
143 /** @todo this should only fetch published media / rk */
145 EntityList returnList=null;
146 if (content != null) {
147 // get all to_topic from media_x_topic
148 String id = content.getId();
149 //this is not supported by mysql
150 String subselect = "id in (select media_id from " + theTable +
151 " where content_id=" + id+")";
154 returnList = DatabaseOther.getInstance().selectByWhereClause(subselect,
156 } catch (Exception e) {
158 theLog.printDebugInfo("-- get Other failed " + e.toString());
159 throw new StorageObjectException("-- get Other failed "
167 * get all the uploaded/other Media belonging to a content entity
170 public EntityList getUploadedMedia(EntityContent content)
171 throws StorageObjectException
173 /** @todo this should only fetch published media / rk */
175 EntityList returnList=null;
176 if (content != null) {
177 // get all to_topic from media_x_topic
178 String id = content.getId();
179 //this is not supported by mysql
180 String subselect = "id in (select media_id from " + theTable +
181 " where content_id=" + id+")";
184 returnList = DatabaseUploadedMedia.getInstance().selectByWhereClause(subselect,
186 } catch (Exception e) {
188 theLog.printDebugInfo("-- get uploadedMedia failed " + e.toString());
189 throw new StorageObjectException("-- get uploadedMedia failed "
197 public void setMedia(String contentId, String[] mediaId)
198 throws StorageObjectException {
199 if (contentId == null){
202 if (mediaId==null || mediaId[0]==null) {
205 //first delete all row with content_id=contentId
206 String sql = "delete from "+ theTable +" where content_id=" + contentId;
208 Connection con=null;Statement stmt=null;
210 con = getPooledCon();
211 // should be a preparedStatement because is faster
212 stmt = con.createStatement();
213 ResultSet rs = executeSql(stmt,sql);
214 } catch (Exception e) {
215 theLog.printDebugInfo("-- set media failed -- delete");
216 throw new StorageObjectException("-- set media failed -- delete"+e.toString());
218 freeConnection(con,stmt);
222 //first delete all row with content_id=contentId
223 for (int i=0;i<mediaId.length;i++) {
224 sql = "insert into "+ theTable +" (content_id,media_id) values ("
225 + contentId + "," + mediaId[i] + ")";
227 con = getPooledCon();
228 // should be a preparedStatement because is faster
229 stmt = con.createStatement();
230 int rs = executeUpdate(stmt,sql);
231 } catch (Exception e) {
232 theLog.printDebugInfo("-- set topics failed -- insert");
233 throw new StorageObjectException("-- set topics failed -- insert "+e.toString());
235 freeConnection(con,stmt);
240 public void addMedia(String contentId, String mediaId)
241 throws StorageObjectException {
242 if (contentId == null && mediaId == null) {
246 Connection con=null;Statement stmt=null;
249 String sql = "insert into "+ theTable +" (content_id,media_id) values ("
250 + contentId + "," + mediaId + ")";
252 con = getPooledCon();
253 // should be a preparedStatement because is faster
254 stmt = con.createStatement();
255 int rs = executeUpdate(stmt,sql);
256 } catch (Exception e) {
257 theLog.printDebugInfo("-- add media failed -- insert");
258 throw new StorageObjectException("-- add media failed -- insert "
261 freeConnection(con,stmt);
265 public void setMedia(String contentId, String mediaId)
266 throws StorageObjectException {
267 if (contentId == null && mediaId == null) {
270 //first delete all row with content_id=contentId
271 String sql = "delete from "+ theTable +" where content_id=" + contentId;
273 Connection con=null;Statement stmt=null;
275 con = getPooledCon();
276 // should be a preparedStatement because is faster
277 stmt = con.createStatement();
278 int rs = executeUpdate(stmt,sql);
279 } catch (Exception e) {
280 theLog.printDebugInfo("-- set media failed -- delete");
281 throw new StorageObjectException("-- set media failed -- delete "
284 freeConnection(con,stmt);
288 //first delete all row with content_id=contentId
290 sql = "insert into "+ theTable +" (content_id,media_id) values ("
291 + contentId + "," + mediaId + ")";
293 con = getPooledCon();
294 // should be a preparedStatement because is faster
295 stmt = con.createStatement();
296 int rs = executeUpdate(stmt,sql);
297 } catch (Exception e) {
298 theLog.printDebugInfo("-- set media failed -- insert");
299 throw new StorageObjectException("-- set media failed -- insert "
302 freeConnection(con,stmt);
306 public void deleteByContentId(String contentId)
307 throws StorageObjectException {
308 if (contentId == null) {
309 //theLog.printDebugInfo("-- delete topics failed -- no content id");
312 //delete all row with content_id=contentId
313 String sql = "delete from "+ theTable +" where content_id=" + contentId;
315 Connection con=null;Statement stmt=null;
317 con = getPooledCon();
318 // should be a preparedStatement because is faster
319 stmt = con.createStatement();
320 int rs = executeUpdate(stmt,sql);
321 } catch (Exception e) {
322 theLog.printDebugInfo("-- delete by contentId failed ");
323 throw new StorageObjectException("-- delete by content id failed -- delete "
326 freeConnection(con,stmt);
330 public void deleteByMediaId(String mediaId)
331 throws StorageObjectException {
332 if (mediaId == null) {
333 //theLog.printDebugInfo("-- delete topics failed -- no topic id");
336 //delete all row with content_id=contentId
337 String sql = "delete from "+ theTable +" where media_id=" + mediaId;
339 Connection con=null;Statement stmt=null;
341 con = getPooledCon();
342 // should be a preparedStatement because is faster
343 stmt = con.createStatement();
344 int rs = executeUpdate(stmt,sql);
345 theLog.printDebugInfo("-- delete media success ");
346 } catch (Exception e) {
347 theLog.printDebugInfo("-- delete media failed ");
348 throw new StorageObjectException("-- delete by media id failed -- "
351 freeConnection(con,stmt);
355 public void delete(String contentId, String mediaId)
356 throws StorageObjectException {
357 if (mediaId == null || contentId==null) {
358 theLog.printDebugInfo("-- delete media failed -- missing parameter");
361 //delete all row with content_id=contentId and media_id=mediaId
362 String sql = "delete from "+ theTable +" where media_id=" + mediaId + " and content_id= "+contentId;
364 Connection con=null;Statement stmt=null;
366 con = getPooledCon();
367 // should be a preparedStatement because is faster
368 stmt = con.createStatement();
369 int rs = executeUpdate(stmt,sql);
370 theLog.printDebugInfo("-- delete content_x_media success ");
371 } catch (Exception e) {
372 theLog.printDebugInfo("-- delete content_x_media failed ");
373 throw new StorageObjectException("-- delete content_x_media failed -- "
376 freeConnection(con,stmt);
381 public EntityList getContent(EntityMedia media)
382 throws StorageObjectException {
383 EntityList returnList=null;
385 String id = media.getId();
386 String select = "select content_id from " + theTable + " where media_id=" + id;
388 // execute select statement
389 Connection con=null;Statement stmt=null;
391 con = getPooledCon();
392 // should be a preparedStatement because is faster
393 stmt = con.createStatement();
394 ResultSet rs = executeSql(stmt,select);
396 String mediaSelect= "id IN (";
399 if (first==false) mediaSelect+=",";
400 mediaSelect += rs.getString(1);
405 returnList = DatabaseContent.getInstance().selectByWhereClause(mediaSelect,-1);
408 catch (Exception e) {
409 theLog.printDebugInfo("-- get content failed");
410 throw new StorageObjectException("-- get content failed -- "
413 finally { freeConnection(con,stmt);}
419 * Returns a EntityList with all content-objects having a relation to a media
422 public EntityList getContent()
423 throws StorageObjectException {
424 EntityList returnList=null;
426 String select = "select distinct content_id from " + theTable;
427 // execute select statement
428 Connection con=null;Statement stmt=null;
430 con = getPooledCon();
431 // should be a preparedStatement because is faster
432 stmt = con.createStatement();
433 ResultSet rs = executeSql(stmt,select);
435 String mediaSelect= "id IN (";
438 if (first==false) mediaSelect+=",";
439 mediaSelect += rs.getString(1);
444 returnList = DatabaseContent.getInstance().selectByWhereClause(mediaSelect,"webdb_lastchange desc");
447 catch (Exception e) {
448 theLog.printDebugInfo("-- get content failed");
449 throw new StorageObjectException("-- get content failed -- "
452 finally { freeConnection(con,stmt);}