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 // the following *has* to be sychronized cause this static method
27 // could get preemted and we could end up with 2 instances of DatabaseFoo.
28 // see the "Singletons with needles and thread" article at JavaWorld -mh
29 public synchronized static DatabaseContentToMedia getInstance()
30 throws StorageObjectException {
31 if (instance == null) {
32 instance = new DatabaseContentToMedia();
33 instance.myselfDatabase = instance;
38 private DatabaseContentToMedia()
39 throws StorageObjectException {
42 this.hasTimestamp = false;
43 this.theTable="content_x_media";
44 try { this.theEntityClass = Class.forName("mir.entity.GenericEntity"); }
45 catch (Exception e) { throw new StorageObjectException(e.toString()); }
49 * get all the media-files belonging to a content entity
52 public EntityList getMedia(EntityContent content)
53 throws StorageObjectException {
54 EntityList returnList=null;
55 if (content != null) {
56 // get all to_topic from media_x_topic
57 String id = content.getId();
58 //this is not supported by mysql
59 String subselect = "id in (select media_id from " + theTable + " where content_id=" + id+")";
62 // media should stay in uploaded order. this is especially important
63 // for photo stories which require a specific chronologic order.
64 // this is why we have the the second parameter "id"
65 returnList = DatabaseMedia.getInstance().selectByWhereClause(subselect,"id",-1);
66 } catch (Exception e) {
67 theLog.printDebugInfo("-- get media failed " + e.toString());
68 throw new StorageObjectException("-- get media failed " + e.toString());
74 public boolean hasMedia(EntityContent content)
75 throws StorageObjectException {
76 String wc = "content_id="+content.getId();
77 if( content != null) {
79 if(selectByWhereClause(wc,-1).size() == 0)
83 } catch (Exception e) {
84 theLog.printError("-- hasMedia failed " + e.toString());
85 throw new StorageObjectException("-- hasMedia failed " + e.toString());
88 theLog.printError("-- hasMedia failed: content is NULL");
89 throw new StorageObjectException("-- hasMedia failed: content is NULL");
97 * get all the audio belonging to a content entity
100 public EntityList getAudio(EntityContent content)
101 throws StorageObjectException {
102 EntityList returnList=null;
103 if (content != null) {
104 // get all to_topic from media_x_topic
105 String id = content.getId();
106 //this is not supported by mysql
107 String subselect = "id in (select media_id from " + theTable + " where content_id=" + id+")";
110 // media should stay in uploaded order. this is especially important
111 // for photo stories which require a specific chronologic order.
112 // this is why we have the the second parameter "id"
113 returnList = DatabaseAudio.getInstance().selectByWhereClause(subselect,"id",-1);
114 } catch (Exception e) {
115 theLog.printDebugInfo("-- get audio failed " + e.toString());
116 throw new StorageObjectException("-- get audio failed " + e.toString());
123 * get all the video belonging to a content entity
126 public EntityList getVideo(EntityContent content)
127 throws StorageObjectException {
128 EntityList returnList=null;
129 if (content != null) {
130 // get all to_topic from media_x_topic
131 String id = content.getId();
132 //this is not supported by mysql
133 String subselect = "id in (select media_id from " + theTable + " where content_id=" + id+")";
136 // media should stay in uploaded order. this is especially important
137 // for photo stories which require a specific chronologic order.
138 // this is why we have the the second parameter "id"
139 returnList = DatabaseVideo.getInstance().selectByWhereClause(subselect,"id",-1);
140 } catch (Exception e) {
141 theLog.printDebugInfo("-- get video failed " + e.toString());
142 throw new StorageObjectException("-- get video failed " + e.toString());
149 * get all the images belonging to a content entity
152 public EntityList getImages(EntityContent content)
153 throws StorageObjectException {
154 EntityList returnList=null;
155 if (content != null) {
156 // get all to_topic from media_x_topic
157 String id = content.getId();
158 //this is not supported by mysql
159 String subselect = "id in (select media_id from " + theTable + " where content_id=" + id+")";
162 // media should stay in uploaded order. this is especially important
163 // for photo stories which require a specific chronologic order.
164 // this is why we have the the second parameter "id"
165 returnList = DatabaseImages.getInstance().selectByWhereClause(subselect,"id",-1);
166 } catch (Exception e) {
167 theLog.printDebugInfo("-- get images failed " + e.toString());
168 throw new StorageObjectException("-- get images failed " + e.toString());
176 * get all the uploaded/other Media belonging to a content entity
179 public EntityList getOther(EntityContent content)
180 throws StorageObjectException
182 /** @todo this should only fetch published media / rk */
184 EntityList returnList=null;
185 if (content != null) {
186 // get all to_topic from media_x_topic
187 String id = content.getId();
188 //this is not supported by mysql
189 String subselect = "id in (select media_id from " + theTable +
190 " where content_id=" + id+")";
193 // media should stay in uploaded order. this is especially important
194 // for photo stories which require a specific chronologic order.
195 // this is why we have the the second parameter "id"
196 returnList = DatabaseOther.getInstance().selectByWhereClause(subselect,
198 } catch (Exception e) {
200 theLog.printDebugInfo("-- get Other failed " + e.toString());
201 throw new StorageObjectException("-- get Other failed "
209 * get all the uploaded/other Media belonging to a content entity
212 public EntityList getUploadedMedia(EntityContent content)
213 throws StorageObjectException
215 /** @todo this should only fetch published media / rk */
217 EntityList returnList=null;
218 if (content != null) {
219 // get all to_topic from media_x_topic
220 String id = content.getId();
221 //this is not supported by mysql
222 String subselect = "id in (select media_id from " + theTable +
223 " where content_id=" + id+")";
226 returnList = DatabaseUploadedMedia.getInstance().selectByWhereClause(subselect,
228 } catch (Exception e) {
230 theLog.printDebugInfo("-- get uploadedMedia failed " + e.toString());
231 throw new StorageObjectException("-- get uploadedMedia failed "
239 public void setMedia(String contentId, String[] mediaId)
240 throws StorageObjectException {
241 if (contentId == null){
244 if (mediaId==null || mediaId[0]==null) {
247 //first delete all row with content_id=contentId
248 String sql = "delete from "+ theTable +" where content_id=" + contentId;
250 Connection con=null;Statement stmt=null;
252 con = getPooledCon();
253 // should be a preparedStatement because is faster
254 stmt = con.createStatement();
255 ResultSet rs = executeSql(stmt,sql);
256 } catch (Exception e) {
257 theLog.printDebugInfo("-- set media failed -- delete");
258 throw new StorageObjectException("-- set media failed -- delete"+e.toString());
260 freeConnection(con,stmt);
264 //first delete all row with content_id=contentId
265 for (int i=0;i<mediaId.length;i++) {
266 sql = "insert into "+ theTable +" (content_id,media_id) values ("
267 + contentId + "," + mediaId[i] + ")";
269 con = getPooledCon();
270 // should be a preparedStatement because is faster
271 stmt = con.createStatement();
272 int rs = executeUpdate(stmt,sql);
273 } catch (Exception e) {
274 theLog.printDebugInfo("-- set topics failed -- insert");
275 throw new StorageObjectException("-- set topics failed -- insert "+e.toString());
277 freeConnection(con,stmt);
282 public void addMedia(String contentId, String mediaId)
283 throws StorageObjectException {
284 if (contentId == null && mediaId == null) {
288 Connection con=null;Statement stmt=null;
291 String sql = "insert into "+ theTable +" (content_id,media_id) values ("
292 + contentId + "," + mediaId + ")";
294 con = getPooledCon();
295 // should be a preparedStatement because is faster
296 stmt = con.createStatement();
297 int rs = executeUpdate(stmt,sql);
298 } catch (Exception e) {
299 theLog.printDebugInfo("-- add media failed -- insert");
300 throw new StorageObjectException("-- add media failed -- insert "
303 freeConnection(con,stmt);
307 public void setMedia(String contentId, String mediaId)
308 throws StorageObjectException {
309 if (contentId == null && mediaId == null) {
312 //first 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("-- set media failed -- delete");
323 throw new StorageObjectException("-- set media failed -- delete "
326 freeConnection(con,stmt);
330 //first delete all row with content_id=contentId
332 sql = "insert into "+ theTable +" (content_id,media_id) values ("
333 + contentId + "," + mediaId + ")";
335 con = getPooledCon();
336 // should be a preparedStatement because is faster
337 stmt = con.createStatement();
338 int rs = executeUpdate(stmt,sql);
339 } catch (Exception e) {
340 theLog.printDebugInfo("-- set media failed -- insert");
341 throw new StorageObjectException("-- set media failed -- insert "
344 freeConnection(con,stmt);
348 public void deleteByContentId(String contentId)
349 throws StorageObjectException {
350 if (contentId == null) {
351 //theLog.printDebugInfo("-- delete topics failed -- no content id");
354 //delete all row with content_id=contentId
355 String sql = "delete from "+ theTable +" where content_id=" + contentId;
357 Connection con=null;Statement stmt=null;
359 con = getPooledCon();
360 // should be a preparedStatement because is faster
361 stmt = con.createStatement();
362 int rs = executeUpdate(stmt,sql);
363 } catch (Exception e) {
364 theLog.printDebugInfo("-- delete by contentId failed ");
365 throw new StorageObjectException("-- delete by content id failed -- delete "
368 freeConnection(con,stmt);
372 public void deleteByMediaId(String mediaId)
373 throws StorageObjectException {
374 if (mediaId == null) {
375 //theLog.printDebugInfo("-- delete topics failed -- no topic id");
378 //delete all row with content_id=contentId
379 String sql = "delete from "+ theTable +" where media_id=" + mediaId;
381 Connection con=null;Statement stmt=null;
383 con = getPooledCon();
384 // should be a preparedStatement because is faster
385 stmt = con.createStatement();
386 int rs = executeUpdate(stmt,sql);
387 theLog.printDebugInfo("-- delete media success ");
388 } catch (Exception e) {
389 theLog.printDebugInfo("-- delete media failed ");
390 throw new StorageObjectException("-- delete by media id failed -- "
393 freeConnection(con,stmt);
397 public void delete(String contentId, String mediaId)
398 throws StorageObjectException {
399 if (mediaId == null || contentId==null) {
400 theLog.printDebugInfo("-- delete media failed -- missing parameter");
403 //delete all row with content_id=contentId and media_id=mediaId
404 String sql = "delete from "+ theTable +" where media_id=" + mediaId + " and content_id= "+contentId;
406 Connection con=null;Statement stmt=null;
408 con = getPooledCon();
409 // should be a preparedStatement because is faster
410 stmt = con.createStatement();
411 int rs = executeUpdate(stmt,sql);
412 theLog.printDebugInfo("-- delete content_x_media success ");
413 } catch (Exception e) {
414 theLog.printDebugInfo("-- delete content_x_media failed ");
415 throw new StorageObjectException("-- delete content_x_media failed -- "
418 freeConnection(con,stmt);
423 public EntityList getContent(EntityMedia media)
424 throws StorageObjectException {
425 EntityList returnList=null;
427 String id = media.getId();
428 String select = "select content_id from " + theTable + " where media_id=" + id;
430 // execute select statement
431 Connection con=null;Statement stmt=null;
433 con = getPooledCon();
434 // should be a preparedStatement because is faster
435 stmt = con.createStatement();
436 ResultSet rs = executeSql(stmt,select);
438 String mediaSelect= "id IN (";
441 if (first==false) mediaSelect+=",";
442 mediaSelect += rs.getString(1);
447 returnList = DatabaseContent.getInstance().selectByWhereClause(mediaSelect,-1);
450 catch (Exception e) {
451 theLog.printDebugInfo("-- get content failed");
452 throw new StorageObjectException("-- get content failed -- "
455 finally { freeConnection(con,stmt);}
461 * Returns a EntityList with all content-objects having a relation to a media
464 public EntityList getContent()
465 throws StorageObjectException {
466 EntityList returnList=null;
468 String select = "select distinct content_id from " + theTable;
469 // execute select statement
470 Connection con=null;Statement stmt=null;
472 con = getPooledCon();
473 // should be a preparedStatement because is faster
474 stmt = con.createStatement();
475 ResultSet rs = executeSql(stmt,select);
477 String mediaSelect= "id IN (";
480 if (first==false) mediaSelect+=",";
481 mediaSelect += rs.getString(1);
486 returnList = DatabaseContent.getInstance().selectByWhereClause(mediaSelect,"webdb_lastchange desc");
489 catch (Exception e) {
490 theLog.printDebugInfo("-- get content failed");
491 throw new StorageObjectException("-- get content failed -- "
494 finally { freeConnection(con,stmt);}