mssqlcatalogdao.java

来自「一个免费wap站」· Java 代码 · 共 434 行

package com.eline.wap.catalog.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.eline.wap.catalog.exceptions.CatalogDAOSysException;
import com.eline.wap.catalog.model.EffectItem;
import com.eline.wap.catalog.model.Item;
import com.eline.wap.catalog.model.ItemCondition;
import com.eline.wap.catalog.model.ListItem;
import com.eline.wap.catalog.model.SingleItem;
import com.eline.wap.common.exceptions.DAOSysException;
import com.eline.wap.common.jdbc.DBCommonUtils;
import com.eline.wap.common.jdbc.DBSqlManager;
import com.eline.wap.common.model.Page;
import com.eline.wap.common.util.AppLogger;
import com.eline.wap.common.util.SerializeData;
import com.eline.wap.common.util.StringUtils;

/**
 * 
 * @author Lucifer
 *
 */
public class MSSqlCatalogDAO extends DBSqlManager implements CatalogDAO {

	static private final int TYPE_ADD		= 0;
	static private final int TYPE_UPDATE	= 1;

	public MSSqlCatalogDAO() throws DAOSysException {
		super();
	}

	/**
	 * 
	 * @param itemId
	 * @return
	 * @throws CatalogDAOSysException
	 */
	public Item getItem(int itemId) throws CatalogDAOSysException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rst = null;
		
		Item item = null;

		String sql = "SELECT IndexID, ParentID, Type, SortOrder, DateCreated, LastUpdate, IsActive, "
			+ "IsSearchable, SearchKeyWords, PropertyNames, PropertyValues "
			+ "FROM TAB_CATALOG WHERE IndexID = ? AND Deleted <> 1";
		try {
			//从连接池中得到一个连接
		    conn = super.getDBConnection();
		    stmt = conn.prepareStatement(sql);
		    stmt.setInt(1, itemId);
		    
		    rst = stmt.executeQuery();
		    
		    if (rst.next()) {
		    	item = this.populateItemFormResultSet(rst);
		    }
		} catch (Exception e) {
			throw new CatalogDAOSysException(e.getMessage());
		} finally {
			try {
				super.closeResultSet(rst);
				super.closeStatement(stmt);
				super.closeConnection(conn);
			} catch (Exception e) {
				throw new CatalogDAOSysException(e.getMessage());
			}
		}

		return item;
	}

	/**
	 * @param item
	 * @return
	 * @throws CatalogDAOSysException
	 */
	public void createItem(Item item) throws CatalogDAOSysException {
		Connection conn = null;
		PreparedStatement stmt = null;

		String sql = "INSERT INTO TAB_CATALOG(ParentID, Type, SortOrder, DateCreated, LastUpdate, "
			+ "IsActive, IsSearchable, SearchKeyWords, PropertyNames, PropertyValues) "
			+ "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		
		try {
		    conn = super.getDBConnection();
		    stmt = conn.prepareStatement(sql);
		    fillItemParameters(stmt, item, TYPE_ADD);
		    
		    int rowCount = stmt.executeUpdate();
		    
		    if (rowCount != 1)
		    	throw new CatalogDAOSysException("effected row count must be 1");

		} catch (Exception e) {
			throw new CatalogDAOSysException(e.getMessage());
		} finally {
			try {
				super.closeStatement(stmt);
				super.closeConnection(conn);
			} catch (Exception e) {
				throw new CatalogDAOSysException(e.getMessage());
			}
		}
	}

	/**
	 * @param item
	 * @return
	 * @throws CatalogDAOSysException
	 */
	public void updateItem(Item item) throws CatalogDAOSysException {
		Connection conn = null;
		PreparedStatement stmt = null;

		String sql = "UPDATE TAB_CATALOG SET LastUpdate = GETDATE(), IsActive = ?, IsSearchable = ?, "
			+"SearchKeywords = ?, PropertyNames = ?, PropertyValues = ? "
			+"WHERE IndexID = ?";
		
		try {
		    conn = super.getDBConnection();
		    stmt = conn.prepareStatement(sql);
		    fillItemParameters(stmt, item, TYPE_UPDATE);

		    int rowCount = stmt.executeUpdate();
		    
		    if (rowCount != 1)
		    	throw new CatalogDAOSysException("invalid effected row count");

		} catch (Exception e) {
			throw new CatalogDAOSysException(e.getMessage());
		} finally {
			try {
				super.closeStatement(stmt);
				super.closeConnection(conn);
			} catch (Exception e) {
				throw new CatalogDAOSysException(e.getMessage());
			}
		}
	}

	/**
	 * @param itemId
	 * @return
	 * @throws CatalogDAOSysException
	 */
	public void deleteItem(int itemId) throws CatalogDAOSysException {
		Connection conn = null;
		PreparedStatement stmt = null;

		// 不是真正从数据库删除，而是设置删除标记为1
		String sql = "UPDATE TAB_CATALOG SET Deleted = 1 WHERE IndexID = ?";
		
		try {
		    conn = super.getDBConnection();
		    stmt = conn.prepareStatement(sql);
		    stmt.setInt(1, itemId);

		    int rowCount = stmt.executeUpdate();
		    
		    if (rowCount != 1)
		    	throw new CatalogDAOSysException("invalid effected row count");

		} catch (Exception e) {
			throw new CatalogDAOSysException(e.getMessage());
		} finally {
			try {
				super.closeStatement(stmt);
				super.closeConnection(conn);
			} catch (Exception e) {
				throw new CatalogDAOSysException(e.getMessage());
			}
		}
	}

	/**
	 * @param parentId
	 * @param start
	 * @param count
	 * @return Page
	 * @throws CatalogDAOSysException
	 */
	public Page getItems(int parentId, int start, int count) throws CatalogDAOSysException {

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rst = null;

		Page page = new Page();
		int totalRecords = 0;
		
		String sql = "SELECT IndexID, ParentID, Type, SortOrder, DateCreated, LastUpdate, IsActive, "
			+ "IsSearchable, SearchKeyWords, PropertyNames, PropertyValues "
			+ "FROM TAB_CATALOG WHERE (ParentID = ?) AND (IsActive = 1) AND (Deleted <> 1) ORDER BY SortOrder";
		
		try {
			//从连接池中得到一个连接
		    conn = super.getDBConnection();
		    stmt = conn.prepareStatement(sql);
		    stmt.setInt(1, parentId);
		    
		    rst = stmt.executeQuery();
		    
		    for (totalRecords = 0; rst.next(); totalRecords ++) {
		    	if ((totalRecords >= start) && (totalRecords < (start + count))) {
		    		Item item = this.populateItemFormResultSet(rst);
		    		page.getItems().add(item);
		    	}
		    }
		    page.setTotalRecords(totalRecords);
		} catch (Exception e) {
			throw new CatalogDAOSysException(e.getMessage());
		} finally {
			try {
				super.closeResultSet(rst);
				super.closeStatement(stmt);
				super.closeConnection(conn);
			} catch (Exception e) {
				throw new CatalogDAOSysException(e.getMessage());
			}
		}

		return page;
	}

	protected static String[] SEARCH_ITEMS_STATEMENT_FRAGMENTS = {
		"SELECT IndexID, ParentID, Type, SortOrder, DateCreated, LastUpdate, IsActive, IsSearchable, SearchKeyWords, PropertyNames, PropertyValues FROM TAB_CATALOG WHERE (Deleted <> 1) ",
		"AND ParentID = ? ",
		"AND Type = ? ",
		"AND SearchKeywords LIKE ? ",
		"AND IsSearchable = 1 ",
		"AND IsActive = 1 ",
		"AND DateCreated >= ? ",
		"AND DateCreated < ? "
	};

	/**
	 * 
	 * @param searchQuery
	 * @param start
	 * @param count
	 * @return Page
	 * @throws CatalogDAOSysException
	 */
	public Page searchItems(ItemCondition condition, int start, int count) throws CatalogDAOSysException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rst = null;
		Page page = new Page();

		String sql = SEARCH_ITEMS_STATEMENT_FRAGMENTS[0];
		if (condition != null) {
			if (condition.getParentId() != -1)
				sql += SEARCH_ITEMS_STATEMENT_FRAGMENTS[1];
			if (condition.getType() != -1)
				sql += SEARCH_ITEMS_STATEMENT_FRAGMENTS[2];
			// 注意: SearchKeywords == null的情况下就不需要判断是否ForceToSearch了
			if (condition.getSearchKeywords() != null) {
				sql += SEARCH_ITEMS_STATEMENT_FRAGMENTS[3];
				if (!condition.isForceToSearch())
					sql += SEARCH_ITEMS_STATEMENT_FRAGMENTS[4];
			}
			if (condition.isActiveOnly())
				sql += SEARCH_ITEMS_STATEMENT_FRAGMENTS[5];
			if (condition.getDateCreated() != null)
				sql += SEARCH_ITEMS_STATEMENT_FRAGMENTS[6];
			if (condition.getDateCreated2() != null)
				sql += SEARCH_ITEMS_STATEMENT_FRAGMENTS[7];
		}
		sql += "ORDER BY SortOrder";
		AppLogger.debug("MSSqlCatalogDAO.searchItems().sql=" + sql);

		try {
			conn = super.getDBConnection();
			stmt = conn.prepareStatement(sql);
			
			// Set condition parameters
			if (condition != null) {
				int index = 1;
				if (condition.getParentId() != -1)
					stmt.setInt(index++, condition.getParentId());
				if (condition.getType() != -1)
					stmt.setInt(index++, condition.getType());
				if (condition.getSearchKeywords() != null)
					stmt.setString(index++, "%" + condition.getSearchKeywords() + "%");
				if (condition.getDateCreated() != null)
					stmt.setString(index++, StringUtils.toString(condition.getDateCreated()));
				if (condition.getDateCreated2() != null)
					stmt.setString(index++, StringUtils.toString(condition.getDateCreated2()));
			}
			
			rst = stmt.executeQuery();

        	int totalRecords = 0;
        	for (totalRecords = 0; rst.next(); totalRecords ++) {
        		if ((totalRecords >= start) && (totalRecords < (start + count))) {
        			Item item = populateItemFormResultSet(rst);
        			page.getItems().add(item);
        		}
        	}
        	page.setTotalRecords(totalRecords);
		} catch (Exception e) {
			AppLogger.debug("MSSqlCatalogDAO.searchItems().e.getMessage()=" + e.getMessage());
			e.printStackTrace();

			throw new CatalogDAOSysException(e.getMessage());
		} finally {
			try {
				super.closeResultSet(rst);
				super.closeStatement(stmt);
				super.closeConnection(conn);
			} catch (Exception e) {
				throw new CatalogDAOSysException(e.getMessage());
			}
		}
		return page;
	}

	
	public void setSortOrder(int catalogId, int sortOrder) throws CatalogDAOSysException {
		Connection conn = null;
		PreparedStatement stmt = null;

		String sql = "UPDATE TAB_CATALOG SET SortOrder = ? WHERE (IndexID = ?)";
		
		try {
			conn = super.getDBConnection();
			stmt = conn.prepareStatement(sql);

			stmt.setInt(1, sortOrder);
			stmt.setInt(2, catalogId);
			
			int rowCount = stmt.executeUpdate();
		    if (rowCount != 1)
		    	throw new CatalogDAOSysException("invalid effected row count");

		} catch (Exception e) {
			AppLogger.debug("MSSqlCatalogDAO.setSortOrder().e.getMessage()=" + e.getMessage());

			throw new CatalogDAOSysException(e.getMessage());
		} finally {
			try {
				super.closeStatement(stmt);
				super.closeConnection(conn);
			} catch (Exception e) {
				throw new CatalogDAOSysException(e.getMessage());
			}
		}
	}

	/**
	 * 
	 * @param stmt
	 * @param item
	 * @param type
	 * @throws SQLException
	 */
	protected void fillItemParameters(PreparedStatement stmt, Item item, int type) throws SQLException {
		int index = 1;
		try {
			if (type == TYPE_ADD) {

				stmt.setInt(index++, item.getParentId());
				stmt.setInt(index++, item.getType());
				stmt.setInt(index++, item.getSortOrder());
				stmt.setString(index++, StringUtils.toString(item.getDateCreated()));
				stmt.setString(index++, StringUtils.toString(item.getLastUpdate()));
				stmt.setBoolean(index++, item.isActive());
				stmt.setBoolean(index++, item.isSearchable());
				stmt.setString(index++, item.getSearchKey());

				// Serialize datas
				SerializeData data = item.deserialize();
				stmt.setString(index++, data.getKeys());
				stmt.setString(index++, data.getValues());

			} else if (type == TYPE_UPDATE) {

				stmt.setBoolean(index++, item.isActive());
				stmt.setBoolean(index++, item.isSearchable());
				stmt.setString(index++, item.getSearchKey());
				
				// serialization datas
				SerializeData data = item.deserialize();
				stmt.setString(index++, data.getKeys());
				stmt.setString(index++, data.getValues());
				
				// indexId
				stmt.setInt(index++, item.getIndexId());
			}
		} catch (SQLException e) {
			throw e;
		}
	}

	/**
	 * 从ResultSet获取catalog item信息 (微软的SQL Server JDBC驱动有bug, 只能从左至右获取Column)
	 * @param rst
	 * @return
	 * @throws SQLException
	 */
	protected Item populateItemFormResultSet(ResultSet rst) throws SQLException {
		Item item = null;
		try {
			int type = rst.getInt("Type");

			switch (type) {
			case	Item.TYPE_LIST:
				item = new ListItem();
				break;
			case	Item.TYPE_SINGLE:
				item = new SingleItem();
				break;
			case	Item.TYPE_EFFECT:
				item = new EffectItem();
				break;
			}
			
			// Fill section properties.
			DBCommonUtils.populateSectionFormResultSet(rst, item);

		} catch (SQLException e) {
			throw e;
		}
		return item;
	}
}