Package com.gnizr.db.dao.folder

Source Code of com.gnizr.db.dao.folder.FolderDBDao

/*
* gnizr is a trademark of Image Matters LLC in the United States.
*
* The contents of this file are subject to the Mozilla Public License Version
* 1.1 (the "License"); you may not use this file except in compliance with
* the License. You may obtain a copy of the License at
* http://www.mozilla.org/MPL/
*
* Software distributed under the License is distributed on an "AS IS" basis,
* WITHOUT WARRANTY OF ANY KIND, either expressed or implied. See the License
* for the specific language governing rights and limitations under the License.
*
* The Initial Contributor of the Original Code is Image Matters LLC.
* Portions created by the Initial Contributor are Copyright (C) 2007
* Image Matters LLC. All Rights Reserved.
*/
package com.gnizr.db.dao.folder;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.log4j.Logger;

import com.gnizr.db.dao.Bookmark;
import com.gnizr.db.dao.DBUtil;
import com.gnizr.db.dao.DaoResult;
import com.gnizr.db.dao.Folder;
import com.gnizr.db.dao.FolderTag;
import com.gnizr.db.dao.Tag;
import com.gnizr.db.dao.User;
import com.gnizr.db.dao.bookmark.BookmarkDBDao;
import com.gnizr.db.dao.tag.TagDBDao;
import com.gnizr.db.dao.user.UserDBDao;
import com.gnizr.db.vocab.FolderSchema;
import com.gnizr.db.vocab.FolderTagSchema;

public class FolderDBDao implements FolderDao{

  /**
   *
   */
  private static final long serialVersionUID = 8291307434207262302L;

  private static final Logger logger = Logger.getLogger(FolderDBDao.class);
  private static final String MEMBER_TAG_COL = "mem_tag";
  private static final String GROUP_TAG_COL = "grp_tag";
  private static final String FOLDER_TAG_IDX_COL = "fti";
 
  private DataSource dataSource;
 
  public FolderDBDao(DataSource ds){
    this.dataSource = ds;
  }
 
  public boolean[] addBookmarks(Folder folder, List<Bookmark> bookmarks, Date timestamp) {
    logger.debug("addBookmarks: folder="+folder+",bookmarks="+bookmarks);
    Connection conn = null;
    CallableStatement cStmt = null;
    boolean[] opOkay = new boolean[bookmarks.size()];
    try {
      conn = dataSource.getConnection();
      cStmt = conn.prepareCall("{call addBookmarkToFolder(?,?,?)};");
      Timestamp tsmp = new Timestamp(timestamp.getTime());
      for(Bookmark bm : bookmarks){
        cStmt.setInt(1,folder.getId());
        cStmt.setInt(2,bm.getId());
        cStmt.setTimestamp(3,tsmp);
        cStmt.addBatch();       
      }     
      int result[] = cStmt.executeBatch();
      for(int i = 0; i < result.length; i++){
        if(result[i] >= 0 ){
          opOkay[i] = true;
        }else{
          opOkay[i] = false;
        }
      }   
    } catch (SQLException e) {
      logger.fatal(e);
    }finally{
      try {
        DBUtil.cleanup(conn, cStmt);
      } catch (SQLException e) {
        logger.fatal(e);
      }
    }
    return opOkay;
  }

  public int createFolder(Folder folder) {
    logger.debug("createFolder: folder=" + folder);
    Connection conn = null;
    CallableStatement cStmt = null;
    int id = -1;
    try {
      conn = dataSource.getConnection();
      cStmt = conn.prepareCall("{call createFolder(?,?,?,?,?)}");
      cStmt.setInt(1,folder.getUser().getId());
      cStmt.setString(2,folder.getName());
      cStmt.setString(3,folder.getDescription());
      cStmt.setTimestamp(4,new Timestamp(folder.getLastUpdated().getTime()));
      cStmt.registerOutParameter(5,Types.INTEGER);
      cStmt.execute();
      id = cStmt.getInt(5);
    } catch (Exception e) {
      logger.fatal(e);
    } finally{
      try {
        DBUtil.cleanup(conn, cStmt);
      } catch (SQLException e) {
        logger.fatal(e);
      }
    }
    return id;
  }

  public boolean deleteFolder(User owner, String folderName) {
    logger.debug("deleteFolder: owner=" + owner+",folderName="+folderName);
    Connection conn = null;
    PreparedStatement stmt = null;
    boolean deleted = false;
    try {
      conn = dataSource.getConnection();
      stmt = conn.prepareStatement("call deleteFolderByOwnerIdFolderName(?,?)");
      stmt.setInt(1,owner.getId());
      stmt.setString(2,folderName);
      if(stmt.executeUpdate() > 0){
        logger.debug("# row deleted=" + stmt.getUpdateCount());
        deleted = true;
      }
    } catch (SQLException e) {
      logger.fatal(e);
    } finally{
      try {
        DBUtil.cleanup(conn, stmt);
      } catch (SQLException e) {
        logger.fatal(e);
      }
    }
    return deleted;
  }
 
  public static Folder createFolderObject(ResultSet rs) throws SQLException{
    if(rs == null) return null;
    Folder folder = new Folder();
    folder.setId(rs.getInt(FolderSchema.ID));
    folder.setName(rs.getString(FolderSchema.FOLDER_NAME));
    folder.setDescription(rs.getString(FolderSchema.DESCRIPTION));
    folder.setLastUpdated(rs.getTimestamp(FolderSchema.LAST_UPDATED));
    folder.setSize(rs.getInt(FolderSchema.SIZE));
    User user = UserDBDao.createUserObject("owner",rs);
    folder.setUser(user);
   
    return folder;   
  }
 
  public DaoResult<Folder> pageFolders(User owner, int offset, int count) {
    logger.debug("pageFolders: owner="+owner + ",offset="+offset+",count="+count);   
    List<Folder> folders = new ArrayList<Folder>();
    DaoResult<Folder> result = null;
    CallableStatement cStmt = null;
    Connection conn = null;
    try{           
      conn = dataSource.getConnection();
      cStmt = conn.prepareCall("call pageFoldersByOwnerId(?,?,?,?);");
      cStmt.setInt(1,owner.getId());
      cStmt.setInt(2,offset);
      cStmt.setInt(3,count);
      cStmt.registerOutParameter(4,Types.INTEGER);
      ResultSet rs = cStmt.executeQuery();
      while(rs.next()){
        Folder folder = createFolderObject(rs);       
        folders.add(folder);
      }
      int size = cStmt.getInt(4);
      if(size < 0){
        size = 0;
      }
      result = new DaoResult<Folder>(folders,size);
      logger.debug("DaoResult: folders="+folders+",size="+size);
    }catch(Exception e){   
      logger.fatal(e);
    }finally{
      try {
        DBUtil.cleanup(conn,cStmt);
      } catch (SQLException e) {
        logger.fatal(e);
      }
    }
    return result;
  }

  public Folder getFolder(int id) {
    logger.debug("getFolder: id="+id);
    Folder folder = null;
    PreparedStatement stmt = null;
    Connection conn = null;
    try{           
      conn = dataSource.getConnection();
      stmt = conn.prepareStatement("call getFolderById(?);");
      stmt.setInt(1,id);
      ResultSet rs = stmt.executeQuery();
      if(rs.next()){
        folder = createFolderObject(rs);
        logger.debug("found: " + folder);
      }else{
        logger.debug("found no matching folder");
      }
    }catch(Exception e){   
      logger.fatal(e);
    }finally{
      try {
        DBUtil.cleanup(conn,stmt);
      } catch (SQLException e) {
        logger.fatal(e);
      }
    }
    return folder;
  }

  public Folder getFolder(User owner, String folderName) {
    logger.debug("getFolder: owner="+owner + ",folderName="+folderName);
    Folder folder = null;
    PreparedStatement stmt = null;
    Connection conn = null;
    try{           
      conn = dataSource.getConnection();
      stmt = conn.prepareStatement("call getFolderByOwnerIdFolderName(?,?);");
      stmt.setInt(1,owner.getId());
      stmt.setString(2,folderName);
      ResultSet rs = stmt.executeQuery();
      if(rs.next()){
        folder = createFolderObject(rs);
        logger.debug("found: " + folder);
      }else{
        logger.debug("found no matching folder");
      }
    }catch(Exception e){   
      logger.fatal(e);
    }finally{
      try {
        DBUtil.cleanup(conn,stmt);
      } catch (SQLException e) {
        logger.fatal(e);
      }
    }
    return folder;
  }

  public int removeAllBookmarks(Folder folder) {
    logger.debug("removeAllBookmarks: folder="+folder);
    Connection conn = null;
    CallableStatement cstmt = null;
    int upCnt = 0;
    try {
      conn = dataSource.getConnection();
      cstmt = conn.prepareCall("call removeAllBookmarksFromFolder(?)");
      cstmt.setInt(1,folder.getId());
      ResultSet rs = cstmt.executeQuery();
      if(rs.next()){
        upCnt = rs.getInt(1);
      }
    }catch(SQLException e){
      logger.fatal(e);     
    }finally{
      try {
        DBUtil.cleanup(conn, cstmt);
      } catch (SQLException e) {
        logger.fatal(e);
      }
    }
    return upCnt;
  }

  public boolean[] removeBookmarks(Folder folder, List<Bookmark> bookmarks) {
    logger.debug("removeBookmarkFromFolder: folder="+folder + ",boomarks="+bookmarks);
    Connection conn = null;
    CallableStatement cstmt = null;
    boolean[] opOkay = new boolean[bookmarks.size()];
    try {
      conn = dataSource.getConnection();
      cstmt = conn.prepareCall("call removeBookmarkFromFolder(?,?)");
      int folderId = folder.getId();   
      for(Bookmark bm : bookmarks){
        cstmt.setInt(1,folderId);
        cstmt.setInt(2,bm.getId());       
        cstmt.addBatch();
      }
      int result[] = cstmt.executeBatch();
      for(int i = 0; i < result.length; i++){
        if(result[i] >= 0){
          opOkay[i] = true;
        }else{
          opOkay[i] = false;
        }
      }
    } catch (SQLException e) {
      logger.fatal(e);
    } finally{
      try {
        DBUtil.cleanup(conn, cstmt);
      } catch (SQLException e) {
        logger.fatal(e);
      }
    }
    return opOkay;
  }

  public boolean updateFolder(Folder folder) {
    logger.debug("updateFolder: folder=" + folder);
    Connection conn = null;
    PreparedStatement stmt = null;
    boolean isChanged = false;
    try {
      conn = dataSource.getConnection();
      stmt = conn.prepareStatement("call updateFolder(?,?,?,?,?)");
      stmt.setInt(1,folder.getId());
      stmt.setInt(2,folder.getUser().getId());
      stmt.setString(3,folder.getName());
      stmt.setString(4,folder.getDescription());     
      stmt.setTimestamp(5, new Timestamp(folder.getLastUpdated().getTime()));
      stmt.execute();
      if(stmt.getUpdateCount()>0){
        logger.debug("updateCount="+stmt.getUpdateCount());
        isChanged = true;
      }
      stmt.getResultSet();
    } catch (SQLException e) {
      logger.fatal(e);
    } finally{
      try {
        DBUtil.cleanup(conn, stmt);
      } catch (SQLException e) {
        logger.fatal(e);
      }
    }   
    return isChanged;
  }

  public DaoResult<Bookmark> pageBookmarks(Folder folder, int offset, int count) {
    logger.debug("pageBookmarks: folder="+folder + ",offset="+offset+",count="+count);   
    return pageBookmarks(folder, offset, count, FolderDao.SORT_BY_BMRK_FLDR_LAST_UPDATED,FolderDao.DESCENDING);
  }

  public DaoResult<Folder> pageContainedInFolders(Bookmark bookmark, int offset, int count) {
    logger.debug("pageContainedInFolders: bookmark="+bookmark + ",offset="+offset+",count="+count);   
    List<Folder> folders = new ArrayList<Folder>();
    DaoResult<Folder> result = null;
    CallableStatement cStmt = null;
    Connection conn = null;
    try{           
      conn = dataSource.getConnection();
      cStmt = conn.prepareCall("call pageContainedInFolders(?,?,?,?);");
      cStmt.setInt(1,bookmark.getId());
      cStmt.setInt(2,offset);
      cStmt.setInt(3,count);
      cStmt.registerOutParameter(4,Types.INTEGER);     
      ResultSet rs = cStmt.executeQuery();
      int size = cStmt.getInt(4);
      if(size < 0){
        size = 0;
     
      while(rs.next()){
        Folder aFolder = createFolderObject(rs);       
        folders.add(aFolder);
      }
      result = new DaoResult<Folder>(folders,size);
      logger.debug("DaoResult: folders="+folders+",size="+size);
    }catch(Exception e){   
      logger.fatal(e);
    }finally{
      try {
        DBUtil.cleanup(conn,cStmt);
      } catch (SQLException e) {
        logger.fatal(e);
      }
    }
    return result;
  }

  public DaoResult<Bookmark> pageBookmarks(Folder folder, Tag tag, int offset, int count) {
    logger.debug("pageBookmarks: folder="+folder + ", tag=" + tag +",offset="+offset+",count="+count);   
    return pageBookmarks(folder, tag, offset, count, FolderDao.SORT_BY_BMRK_FLDR_LAST_UPDATED, FolderDao.DESCENDING);
  }

  public DaoResult<Bookmark> pageBookmarks(Folder folder, int offset, int count, int sortBy, int order) {
    List<Bookmark> bmarks = new ArrayList<Bookmark>();
    DaoResult<Bookmark> result = null;
    CallableStatement cStmt = null;
    Connection conn = null;
    try{           
      conn = dataSource.getConnection();
      cStmt = conn.prepareCall("call pageBookmarksByFolderId(?,?,?,?,?,?);");
      cStmt.setInt(1,folder.getId());
      cStmt.setInt(2,offset);
      cStmt.setInt(3,count);
      cStmt.registerOutParameter(4,Types.INTEGER);     
      cStmt.setInt(5,sortBy);
      cStmt.setInt(6,order);
      ResultSet rs = cStmt.executeQuery();
      int size = cStmt.getInt(4);
      if(size < 0){
        size = 0;
     
      while(rs.next()){
        Bookmark bookmark = BookmarkDBDao.createBookmarkObject2(rs);       
        bmarks.add(bookmark);
      }
      result = new DaoResult<Bookmark>(bmarks,size);
      logger.debug("DaoResult: bookmarks="+bmarks+",size="+size);
    }catch(Exception e){   
      logger.fatal(e);
    }finally{
      try {
        DBUtil.cleanup(conn,cStmt);
      } catch (SQLException e) {
        logger.fatal(e);
      }
    }
    return result;
  }

  public DaoResult<Bookmark> pageBookmarks(Folder folder, Tag tag, int offset, int count, int sortBy, int order) {
    List<Bookmark> bmarks = new ArrayList<Bookmark>();
    DaoResult<Bookmark> result = null;
    CallableStatement cStmt = null;
    Connection conn = null;
    try{           
      conn = dataSource.getConnection();
      cStmt = conn.prepareCall("call pageBookmarksByFolderIdTagId(?,?,?,?,?,?,?);");
      cStmt.setInt(1,folder.getId());
      cStmt.setInt(2,tag.getId());
      cStmt.setInt(3,offset);
      cStmt.setInt(4,count);
      cStmt.registerOutParameter(5,Types.INTEGER);     
      cStmt.setInt(6,sortBy);
      cStmt.setInt(7,order);
      ResultSet rs = cStmt.executeQuery();
      int size = cStmt.getInt(5);
      if(size < 0){
        size = 0;
     
      while(rs.next()){
        Bookmark bookmark = BookmarkDBDao.createBookmarkObject2(rs);       
        bmarks.add(bookmark);
      }
      result = new DaoResult<Bookmark>(bmarks,size);
      logger.debug("DaoResult: bookmarks="+bmarks+",size="+size);
    }catch(Exception e){   
      logger.fatal(e);
    }finally{
      try {
        DBUtil.cleanup(conn,cStmt);
      } catch (SQLException e) {
        logger.fatal(e);
      }
    }
    return result;
  }
 
  public boolean hasFolderTag(Folder folder, Tag tag){
    logger.debug("hasFolderTag: folder=" + folder+",tag="+tag);
    Connection conn = null;
    CallableStatement cStmt = null;   
    boolean exists = false;
    try {
      conn = dataSource.getConnection();
      cStmt = conn.prepareCall("{call hasFolderTag(?,?)}");
      cStmt.setInt(1,folder.getId());
      cStmt.setInt(2,tag.getId());
      ResultSet rs = cStmt.executeQuery();     
      if(rs.next()){
        exists = true;
      }
    } catch (Exception e) {
      logger.fatal(e);     
    } finally{
      try {
        DBUtil.cleanup(conn, cStmt);
      } catch (SQLException e) {
        logger.fatal(e);
      }
    }
    return exists;
  }
 
  public List<FolderTag> findTagsInFolder(Folder folder, int minFreq, int sortBy, int order) {
    logger.debug("findTagsInFolder: folder="+folder + ",minFreq=" + minFreq+",sortBy="+sortBy+",order="+order);   
    List<FolderTag> folderTags = new ArrayList<FolderTag>();
    CallableStatement cStmt = null;
    Connection conn = null;
    try{           
      conn = dataSource.getConnection();
      cStmt = conn.prepareCall("call findAllTagsInFolder(?,?,?,?);");
      cStmt.setInt(1,folder.getId());
      cStmt.setInt(2,minFreq);
      cStmt.setInt(3,sortBy);
      cStmt.setInt(4,order);     
      ResultSet rs = cStmt.executeQuery();
      while(rs.next()){
        FolderTag ft = new FolderTag();
        Tag t = TagDBDao.createTagObject(rs);
        ft.setTag(t);
        ft.setCount(rs.getInt(FolderTagSchema.COUNT));
        folderTags.add(ft);
      }
      if(folderTags.isEmpty() == false){
        setFolderOnFolderTags(folderTags,folder);
      }     
    }catch(Exception e){   
      logger.fatal(e);
    }finally{
      try {
        DBUtil.cleanup(conn,cStmt);
      } catch (SQLException e) {
        logger.fatal(e);
      }
    }
    return folderTags;
  }
 
  private void setFolderOnFolderTags(List<FolderTag> folderTags, Folder f){
    logger.debug("setFolderOnFolderTags: folderTags=" + folderTags + ",folder="+f);
    Folder folder = getFolder(f.getId());
    if(folder != null){
      for(FolderTag ft : folderTags){
        ft.setFolder(new Folder(folder));
      }
    }
  }

  public Map<String, List<FolderTag>> listTagGroups(Folder folder, int minFreq, int sortBy, int order) {
    logger.debug("listTagGroups: folder="+folder + ",minFreq=" + minFreq+",sortBy="+sortBy+",order="+order);   
    Map<String, List<FolderTag>> tagGroups = new HashMap<String, List<FolderTag>>();
    CallableStatement cStmt = null;
    Connection conn = null;
    try{           
      conn = dataSource.getConnection();
      cStmt = conn.prepareCall("call findTagGroupsInFolder(?,?,?,?);");
      cStmt.setInt(1,folder.getId());
      cStmt.setInt(2,minFreq);
      cStmt.setInt(3,sortBy);
      cStmt.setInt(4,order);     
      ResultSet rs = cStmt.executeQuery();
      while(rs.next()){
        FolderTag ft = new FolderTag();
        Tag memTag = TagDBDao.createNamedTagObject(MEMBER_TAG_COL, rs, true);
        Tag grpTag = TagDBDao.createNamedTagObject(GROUP_TAG_COL, rs, true);
        ft.setTag(memTag);
        ft.setCount(rs.getInt(FOLDER_TAG_IDX_COL+"_count"));
        List<FolderTag> folderTags = tagGroups.get(grpTag.getLabel());
        if(folderTags == null){
          folderTags = new ArrayList<FolderTag>();
          tagGroups.put(grpTag.getLabel(),folderTags);
        }
        folderTags.add(ft);
      }
      if(tagGroups.isEmpty() == false){
        for(List<FolderTag> ftags: tagGroups.values()){
          setFolderOnFolderTags(ftags,folder);
        }
      }     
    }catch(Exception e){   
      logger.fatal(e);
    }finally{
      try {
        DBUtil.cleanup(conn,cStmt);
      } catch (SQLException e) {
        logger.fatal(e);
      }
    }
    return tagGroups;
  }
 
}
TOP

Related Classes of com.gnizr.db.dao.folder.FolderDBDao

TOP
Copyright © 2018 www.massapi.com. All rights reserved.
All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.