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 EntityList returnList=null;
49 if (content != null) {
50 // get all to_topic from media_x_topic
51 String id = content.getId();
52 //this is not supported by mysql
53 String subselect = "id in (select media_id from " + theTable + " where content_id=" + id+")";
56 returnList = DatabaseMedia.getInstance().selectByWhereClause(subselect,-1);
57 } catch (Exception e) {
58 theLog.printDebugInfo("-- get media failed " + e.toString());
65 * get all the images belonging to a content entity
68 public EntityList getImages(EntityContent content) {
69 EntityList returnList=null;
70 if (content != null) {
71 // get all to_topic from media_x_topic
72 String id = content.getId();
73 //this is not supported by mysql
74 String subselect = "id in (select media_id from " + theTable + " where content_id=" + id+")";
77 returnList = DatabaseImages.getInstance().selectByWhereClause(subselect,-1);
78 } catch (Exception e) {
79 theLog.printDebugInfo("-- get images failed " + e.toString());
87 * get all the uploaded Media belonging to a content entity
90 public EntityList getUploadedMedia(EntityContent content) {
91 EntityList returnList=null;
92 if (content != null) {
93 // get all to_topic from media_x_topic
94 String id = content.getId();
95 //this is not supported by mysql
96 String subselect = "id in (select media_id from " + theTable + " where content_id=" + id+")";
99 returnList = DatabaseUploadedMedia.getInstance().selectByWhereClause(subselect,-1);
100 } catch (Exception e) {
101 theLog.printDebugInfo("-- get uploadedMedia failed " + e.toString());
108 public void setMedia(String contentId, String[] mediaId) {
109 if (contentId == null){
112 if (mediaId==null || mediaId[0]==null) {
115 //first delete all row with content_id=contentId
116 String sql = "delete from "+ theTable +" where content_id=" + contentId;
118 Connection con=null;Statement stmt=null;
120 con = getPooledCon();
121 // should be a preparedStatement because is faster
122 stmt = con.createStatement();
123 ResultSet rs = executeSql(stmt,sql);
124 } catch (Exception e) {
125 theLog.printDebugInfo("-- set media failed -- delete");
127 freeConnection(con,stmt);
131 //first delete all row with content_id=contentId
132 for (int i=0;i<mediaId.length;i++) {
133 sql = "insert into "+ theTable +" (content_id,media_id) values ("
134 + contentId + "," + mediaId[i] + ")";
136 con = getPooledCon();
137 // should be a preparedStatement because is faster
138 stmt = con.createStatement();
139 int rs = executeUpdate(stmt,sql);
140 } catch (Exception e) {
141 theLog.printDebugInfo("-- set topics failed -- insert");
143 freeConnection(con,stmt);
148 public void addMedia(String contentId, String mediaId) {
149 if (contentId == null && mediaId == null) {
153 Connection con=null;Statement stmt=null;
156 String sql = "insert into "+ theTable +" (content_id,media_id) values ("
157 + contentId + "," + mediaId + ")";
159 con = getPooledCon();
160 // should be a preparedStatement because is faster
161 stmt = con.createStatement();
162 int rs = executeUpdate(stmt,sql);
163 } catch (Exception e) {
164 theLog.printDebugInfo("-- add media failed -- insert");
166 freeConnection(con,stmt);
170 public void setMedia(String contentId, String mediaId) {
171 if (contentId == null && mediaId == null) {
174 //first delete all row with content_id=contentId
175 String sql = "delete from "+ theTable +" where content_id=" + contentId;
177 Connection con=null;Statement stmt=null;
179 con = getPooledCon();
180 // should be a preparedStatement because is faster
181 stmt = con.createStatement();
182 int rs = executeUpdate(stmt,sql);
183 } catch (Exception e) {
184 theLog.printDebugInfo("-- set media failed -- delete");
186 freeConnection(con,stmt);
190 //first delete all row with content_id=contentId
192 sql = "insert into "+ theTable +" (content_id,media_id) values ("
193 + contentId + "," + mediaId + ")";
195 con = getPooledCon();
196 // should be a preparedStatement because is faster
197 stmt = con.createStatement();
198 int rs = executeUpdate(stmt,sql);
199 } catch (Exception e) {
200 theLog.printDebugInfo("-- set media failed -- insert");
202 freeConnection(con,stmt);
206 public void deleteByContentId(String contentId) {
207 if (contentId == null) {
208 //theLog.printDebugInfo("-- delete topics failed -- no content id");
211 //delete all row with content_id=contentId
212 String sql = "delete from "+ theTable +" where content_id=" + contentId;
214 Connection con=null;Statement stmt=null;
216 con = getPooledCon();
217 // should be a preparedStatement because is faster
218 stmt = con.createStatement();
219 int rs = executeUpdate(stmt,sql);
220 } catch (Exception e) {
221 //theLog.printDebugInfo("-- delete topics failed ");
223 freeConnection(con,stmt);
227 public void deleteByMediaId(String mediaId) {
228 if (mediaId == null) {
229 //theLog.printDebugInfo("-- delete topics failed -- no topic id");
232 //delete all row with content_id=contentId
233 String sql = "delete from "+ theTable +" where media_id=" + mediaId;
235 Connection con=null;Statement stmt=null;
237 con = getPooledCon();
238 // should be a preparedStatement because is faster
239 stmt = con.createStatement();
240 int rs = executeUpdate(stmt,sql);
241 theLog.printDebugInfo("-- delete media success ");
242 } catch (Exception e) {
243 theLog.printDebugInfo("-- delete media failed ");
245 freeConnection(con,stmt);
249 public void delete(String contentId, String mediaId) {
250 if (mediaId == null || contentId==null) {
251 theLog.printDebugInfo("-- delete media failed -- missing parameter");
254 //delete all row with content_id=contentId and media_id=mediaId
255 String sql = "delete from "+ theTable +" where media_id=" + mediaId + " and content_id= "+contentId;
257 Connection con=null;Statement stmt=null;
259 con = getPooledCon();
260 // should be a preparedStatement because is faster
261 stmt = con.createStatement();
262 int rs = executeUpdate(stmt,sql);
263 theLog.printDebugInfo("-- delete content_x_media success ");
264 } catch (Exception e) {
265 theLog.printDebugInfo("-- delete content_x_media failed ");
267 freeConnection(con,stmt);
272 public EntityList getContent(EntityMedia media) {
273 EntityList returnList=null;
275 String id = media.getId();
276 String select = "select content_id from " + theTable + " where media_id=" + id;
278 // execute select statement
279 Connection con=null;Statement stmt=null;
281 con = getPooledCon();
282 // should be a preparedStatement because is faster
283 stmt = con.createStatement();
284 ResultSet rs = executeSql(stmt,select);
286 String mediaSelect= "id IN (";
289 if (first==false) mediaSelect+=",";
290 mediaSelect += rs.getString(1);
295 returnList = DatabaseContent.getInstance().selectByWhereClause(mediaSelect,-1);
298 catch (Exception e) {theLog.printDebugInfo("-- get content failed");}
299 finally { freeConnection(con,stmt);}
305 * Returns a EntityList with all content-objects having a relation to a media
308 public EntityList getContent() {
309 EntityList returnList=null;
311 String select = "select distinct content_id from " + theTable;
312 // execute select statement
313 Connection con=null;Statement stmt=null;
315 con = getPooledCon();
316 // should be a preparedStatement because is faster
317 stmt = con.createStatement();
318 ResultSet rs = executeSql(stmt,select);
320 String mediaSelect= "id IN (";
323 if (first==false) mediaSelect+=",";
324 mediaSelect += rs.getString(1);
329 returnList = DatabaseContent.getInstance().selectByWhereClause(mediaSelect,"webdb_lastchange desc");
332 catch (Exception e) {theLog.printDebugInfo("-- get content failed");}
333 finally { freeConnection(con,stmt);}