package mircoders.storage;
-import java.sql.Connection;\r
-import java.sql.ResultSet;\r
-import java.sql.Statement;\r
-\r
-import mir.entity.EntityList;\r
-import mir.log.LoggerWrapper;\r
-import mir.storage.Database;\r
-import mir.storage.StorageObject;\r
-import mir.storage.StorageObjectExc;\r
-import mir.storage.StorageObjectFailure;\r
-import mircoders.entity.EntityComment;\r
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.Statement;
+import java.util.Vector;
+
+import mir.entity.EntityList;
+import mir.log.LoggerWrapper;
+import mir.storage.Database;
+import mir.storage.StorageObject;
+import mir.storage.StorageObjectExc;
+import mir.storage.StorageObjectFailure;
+import mircoders.entity.EntityComment;
import mircoders.entity.EntityUploadedMedia;
/**
* <b>implements abstract DB connection to the comment_x_media SQL table
*
* @author RK, mir-coders group
- * @version $Id: DatabaseCommentToMedia.java,v 1.4 2003/09/03 18:29:05 zapata Exp $
+ * @version $Id: DatabaseCommentToMedia.java,v 1.5 2004/11/06 20:10:37 idfx Exp $
*
*/
private DatabaseCommentToMedia() {
super();
-
logger = new LoggerWrapper("Database.CommentToMedia");
-
hasTimestamp = false;
- theTable = "comment_x_media";
+ mainTable = "comment_x_media";
theEntityClass = mir.entity.GenericEntity.class;
}
StorageObjectExc {
if (comment != null) {
try {
- if (selectByWhereClause("comment_id=" + comment.getId(), -1).size() ==
- 0)
- return false;
- else
- return true;
+ return (getSize("comment_id=" + comment.getId()) == 0) ? false:true;
}
catch (Exception e) {
logger.error("DatabaseCommentToMedia.hasMedia: " + e.toString());
}
}
+ private EntityList getMultiMediaForComment(StorageObject store, EntityComment comment)
+ throws StorageObjectFailure {
+
+ EntityList returnList = null;
+ if (comment != null) {
+ String id = comment.getId();
+ try {
+ Vector extraTable = new Vector();
+ extraTable.add(mainTable+" cxm");
+ // media should stay in uploaded order. this is especially important
+ // for photo stories which require a specific chronologic order.
+ // this is why we have the the second parameter "id"
+ store.selectByWhereClause("m", extraTable,
+ "m.id=cxm.media_id and cxm.comment_id="+id, "id", -1, -1);
+
+ } catch (Exception e) {
+ logger.error("DatabaseCommenttToMedia.getMultiMediaForComment: " + e.toString());
+ throw new StorageObjectFailure("DatabaseContentToMedia.etMultiMediaForComment: " +
+ e.toString(), e);
+ }
+ }
+ return returnList;
+ }
+
/**
* get all the audio belonging to a comment entity
- *
*/
public EntityList getAudio(EntityComment comment) throws StorageObjectFailure {
- EntityList returnList = null;
- if (comment != null) {
- // get all to_topic from media_x_topic
- String id = comment.getId();
- //this is not supported by mysql
- String subselect = "id in (select media_id from " + theTable +
- " where comment_id=" + id + ")";
-
- try {
- // media should stay in uploaded order. this is especially important
- // for photo stories which require a specific chronologic order.
- // this is why we have the the second parameter "id"
- returnList = DatabaseAudio.getInstance().selectByWhereClause(subselect,
- "id", -1);
- }
- catch (Exception e) {
- logger.error("DatabaseCommentToMedia.getAudio: " + e.toString());
- throw new StorageObjectFailure("DatabaseCommentToMedia.getAudio: " +
- e.toString(), e);
- }
- }
- return returnList;
+ return getMultiMediaForComment(DatabaseAudio.getInstance(), comment);
}
/**
*
*/
public EntityList getVideo(EntityComment comment) throws StorageObjectFailure {
- EntityList returnList = null;
- if (comment != null) {
- // get all to_topic from media_x_topic
- String id = comment.getId();
- //this is not supported by mysql
- String subselect = "id in (select media_id from " + theTable +
- " where comment_id=" + id + ")";
-
- try {
- // media should stay in uploaded order. this is especially important
- // for photo stories which require a specific chronologic order.
- // this is why we have the the second parameter "id"
- returnList = DatabaseVideo.getInstance().selectByWhereClause(subselect,
- "id", -1);
- }
- catch (Exception e) {
- logger.error("DatabaseCommentToMedia.getVideo: " + e.toString());
- throw new StorageObjectFailure("DatabaseCommentToMedia.getVideo: " +
- e.toString(), e);
- }
- }
- return returnList;
+ return getMultiMediaForComment(DatabaseVideo.getInstance(), comment);
}
/**
* get all the images belonging to a comment entity
- *
*/
- public EntityList getImages(EntityComment comment) throws
- StorageObjectFailure {
- EntityList returnList = null;
- if (comment != null) {
- // get all to_topic from media_x_topic
- String id = comment.getId();
- //this is not supported by mysql
- String subselect = "id in (select media_id from " + theTable +
- " where comment_id=" + id + ")";
-
- try {
- // media should stay in uploaded order. this is especially important
- // for photo stories which require a specific chronologic order.
- // this is why we have the the second parameter "id"
- returnList = DatabaseImages.getInstance().selectByWhereClause(subselect,
- "id", -1);
- }
- catch (Exception e) {
- logger.error("DatabaseCommentToMedia.getImages: " + e.toString());
- throw new StorageObjectFailure("DatabaseCommentToMedia.getImages: " +
- e.toString(), e);
- }
- }
- return returnList;
+ public EntityList getImages(EntityComment comment) throws StorageObjectFailure {
+ return getMultiMediaForComment(DatabaseImages.getInstance(), comment);
}
/**
* get all the uploaded/other Media belonging to a comment entity
- *
*/
public EntityList getOther(EntityComment comment) throws StorageObjectFailure {
- /** @todo this should only fetch published media / rk */
-
- EntityList returnList = null;
- if (comment != null) {
- // get all to_topic from media_x_topic
- String id = comment.getId();
- //this is not supported by mysql
- String subselect = "id in (select media_id from " + theTable +
- " where comment_id=" + id + ")";
-
- try {
- // media should stay in uploaded order. this is especially important
- // for photo stories which require a specific chronologic order.
- // this is why we have the the second parameter "id"
- returnList = DatabaseOther.getInstance().selectByWhereClause(subselect,
- "id");
- }
- catch (Exception e) {
- logger.error("DatabaseCommentToMedia.getOther: " + e.toString());
- throw new StorageObjectFailure("DatabaseCommentToMedia.getOther: " + e.toString(), e);
- }
- }
- return returnList;
+ return getMultiMediaForComment(DatabaseOther.getInstance(), comment);
}
/**
* get all the uploaded/other Media belonging to a comment entity
- *
*/
- public EntityList getUploadedMedia(EntityComment comment) throws
- StorageObjectFailure {
- /** @todo this should only fetch published media / rk */
-
- EntityList returnList = null;
- if (comment != null) {
- // get all to_topic from media_x_topic
- String id = comment.getId();
- //this is not supported by mysql
- String subselect = "id in (select media_id from " + theTable +
- " where comment_id=" + id + ")";
-
- try {
- returnList = DatabaseUploadedMedia.getInstance().selectByWhereClause(
- subselect,
- "id");
- }
- catch (Exception e) {
- logger.error("DatabaseCommentToMedia.getUploadedMedia: " + e.toString());
- throw new StorageObjectFailure(
- "DatabaseCommentToMedia.getUploadedMedia: " + e.toString(), e);
- }
- }
- return returnList;
+ public EntityList getUploadedMedia(EntityComment comment) throws StorageObjectFailure {
+ return getMultiMediaForComment(DatabaseUploadedMedia.getInstance(), comment);
}
public void setMedia(String commentId, String[] mediaId) throws
return;
}
//first delete all row with comment_id=commentId
- String sql = "delete from " + theTable + " where comment_id=" + commentId;
+ String sql = "delete from " + mainTable + " where comment_id=" + commentId;
Connection con = null;
Statement stmt = null;
try {
- con = getPooledCon();
+ con = obtainConnection();
// should be a preparedStatement because is faster
stmt = con.createStatement();
ResultSet rs = executeSql(stmt, sql);
//now insert
//first delete all row with comment_id=commentId
for (int i = 0; i < mediaId.length; i++) {
- sql = "insert into " + theTable + " (comment_id,media_id) values ("
+ sql = "insert into " + mainTable + " (comment_id,media_id) values ("
+ commentId + "," + mediaId[i] + ")";
try {
- con = getPooledCon();
+ con = obtainConnection();
// should be a preparedStatement because is faster
stmt = con.createStatement();
int rs = executeUpdate(stmt, sql);
Statement stmt = null;
//now insert
- String sql = "insert into " + theTable + " (comment_id,media_id) values ("
+ String sql = "insert into " + mainTable + " (comment_id,media_id) values ("
+ commentId + "," + mediaId + ")";
try {
- con = getPooledCon();
+ con = obtainConnection();
// should be a preparedStatement because is faster
stmt = con.createStatement();
int rs = executeUpdate(stmt, sql);
return;
}
//first delete all row with comment_id=commentId
- String sql = "delete from " + theTable + " where comment_id=" + commentId;
+ String sql = "delete from " + mainTable + " where comment_id=" + commentId;
Connection con = null;
Statement stmt = null;
try {
- con = getPooledCon();
+ con = obtainConnection();
// should be a preparedStatement because is faster
stmt = con.createStatement();
int rs = executeUpdate(stmt, sql);
//now insert
//first delete all row with comment_id=commentId
- sql = "insert into " + theTable + " (comment_id,media_id) values ("
+ sql = "insert into " + mainTable + " (comment_id,media_id) values ("
+ commentId + "," + mediaId + ")";
try {
- con = getPooledCon();
+ con = obtainConnection();
// should be a preparedStatement because is faster
stmt = con.createStatement();
int rs = executeUpdate(stmt, sql);
return;
}
//delete all row with comment_id=commentId
- String sql = "delete from " + theTable + " where comment_id=" + commentId;
+ String sql = "delete from " + mainTable + " where comment_id=" + commentId;
Connection con = null;
Statement stmt = null;
try {
- con = getPooledCon();
+ con = obtainConnection();
// should be a preparedStatement because is faster
stmt = con.createStatement();
int rs = executeUpdate(stmt, sql);
return;
}
//delete all row with comment_id=commentId
- String sql = "delete from " + theTable + " where media_id=" + mediaId;
+ String sql = "delete from " + mainTable + " where media_id=" + mediaId;
Connection con = null;
Statement stmt = null;
try {
- con = getPooledCon();
+ con = obtainConnection();
// should be a preparedStatement because is faster
stmt = con.createStatement();
int rs = executeUpdate(stmt, sql);
return;
}
//delete all row with comment_id=commentId and media_id=mediaId
- String sql = "delete from " + theTable + " where media_id=" + mediaId +
+ String sql = "delete from " + mainTable + " where media_id=" + mediaId +
" and comment_id= " + commentId;
Connection con = null;
Statement stmt = null;
try {
- con = getPooledCon();
+ con = obtainConnection();
// should be a preparedStatement because is faster
stmt = con.createStatement();
int rs = executeUpdate(stmt, sql);
public EntityList getComment(EntityUploadedMedia media) throws
StorageObjectFailure {
+
EntityList returnList = null;
if (media != null) {
+
String id = media.getId();
- String select = "select comment_id from " + theTable + " where media_id=" +
- id;
+ Vector extraTables = new Vector();
+ extraTables.add(mainTable + " cxm");
- // execute select statement
- Connection con = null;
- Statement stmt = null;
+ String mediaSelect = "cxm.comment_id=c.id and cxm.media_id="+id;
try {
- con = getPooledCon();
- // should be a preparedStatement because is faster
- stmt = con.createStatement();
- ResultSet rs = executeSql(stmt, select);
- if (rs != null) {
- String mediaSelect = "id IN (";
- boolean first = true;
- while (rs.next()) {
- if (first == false)
- mediaSelect += ",";
- mediaSelect += rs.getString(1);
- first = false;
- }
- mediaSelect += ")";
- if (first == false)
- returnList = DatabaseComment.getInstance().selectByWhereClause(
- mediaSelect, -1);
- }
+ returnList = DatabaseComment.getInstance().selectByWhereClause("c",
+ extraTables, mediaSelect, "c.id" );
+
}
catch (Exception e) {
logger.error("-- get comment failed");
throw new StorageObjectFailure("-- get comment failed -- ", e);
}
- finally {
- freeConnection(con, stmt);
- }
}
return returnList;
}
/**
- * Returns a EntityList with all comment-objects having a relation to a media
+ * Returns a EntityList with all comment-objects having
+ * a relation to a media
*/
public EntityList getComment() throws StorageObjectFailure {
EntityList returnList = null;
- String select = "select distinct comment_id from " + theTable;
- // execute select statement
- Connection con = null;
- Statement stmt = null;
+ Vector extraTables = new Vector();
+ extraTables.add(mainTable + " cxm");
+
+ String mediaSelect = "cxm.comment_id=c.id";
try {
- con = getPooledCon();
- // should be a preparedStatement because is faster
- stmt = con.createStatement();
- ResultSet rs = executeSql(stmt, select);
- if (rs != null) {
- String mediaSelect = "id IN (";
- boolean first = true;
- while (rs.next()) {
- if (first == false)
- mediaSelect += ",";
- mediaSelect += rs.getString(1);
- first = false;
- }
- mediaSelect += ")";
- if (first == false)
- returnList = DatabaseComment.getInstance().selectByWhereClause(
- mediaSelect, "webdb_lastchange desc");
- }
+ returnList = DatabaseComment.getInstance().selectByWhereClause("c",
+ extraTables, mediaSelect, "c.webdb_lastchange desc" );
+
}
catch (Exception e) {
logger.error("-- get comment failed");
throw new StorageObjectFailure("-- get comment failed -- ", e);
}
- finally {
- freeConnection(con, stmt);
- }
-
return returnList;
+
}
}
projects / mir.git / blobdiff