mssqlringdao.java

一个免费wap站

package com.eline.wap.resource.dao;


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

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;
import com.eline.wap.resource.exceptions.ResourceDAOSysException;
import com.eline.wap.resource.model.RingEntity;
import com.eline.wap.resource.model.Ring;
import com.eline.wap.resource.model.RingCondition;


public class MSSqlRingDAO extends DBSqlManager implements RingDAO {

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

	public Ring getRing(int ringId) throws ResourceDAOSysException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rst = null;
		Ring item = null;
		
		String sql = "SELECT IndexID, ParentID, SortOrder, DateCreated, LastUpdate, IsActive, IsSearchable, " 
			+ "SearchKeywords, PropertyNames, PropertyValues FROM TAB_RING "
			+ "WHERE IndexID = ?";
		
		try {
			conn = super.getDBConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, ringId);
			
			rst = stmt.executeQuery();
			
			if (rst.next())
				item = populateRingFormResultSet(rst);

		} catch (Exception e) {
			AppLogger.debug("MSSqlRingDAO.getRing().e.getMessage()=" + e.getMessage());
			throw new ResourceDAOSysException(e.getMessage());
		} finally {
			try {
				super.closeResultSet(rst);
				super.closeStatement(stmt);
				super.closeConnection(conn);
			} catch (Exception e) {
				throw new ResourceDAOSysException(e.getMessage());
			}
		}

		return item;
	}

	public Page getRing(int parentId, int start, int count)
			throws ResourceDAOSysException {
		// TODO Auto-generated method stub
		return null;
	}

	protected static String[] SEARCH_RING_STATEMENT_FRAGMENTS = {
		"SELECT IndexID, ParentID, SortOrder, DateCreated, LastUpdate, IsActive, IsSearchable, SearchKeywords, PropertyNames, PropertyValues FROM TAB_RING WHERE Deleted <> 1 ",
		"AND ParentID = ? ",
		"AND SearchKeywords = ? ",
		"AND IsSearchable = 1 ",
		"AND IsActive = 1 ",
		"AND DateCreated >= ? ",
		"AND DateCreated < ? "
	};
	
	public Page searchRing(RingCondition condition, int start, int count)
			throws ResourceDAOSysException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rst = null;
		
		Page page = new Page();
		String sql = SEARCH_RING_STATEMENT_FRAGMENTS[0];
		if (condition != null) {
			if (condition.getParentId() != -1)
				sql += SEARCH_RING_STATEMENT_FRAGMENTS[1];
			// 注意: SearchKeywords == null的情况下就不需要判断是否ForceToSearch了
			if (condition.getSearchKeywords() != null) {
				sql += SEARCH_RING_STATEMENT_FRAGMENTS[2];
				if (!condition.isForceToSearch())
					sql += SEARCH_RING_STATEMENT_FRAGMENTS[3];
			}
			if (condition.isActiveOnly())
				sql += SEARCH_RING_STATEMENT_FRAGMENTS[4];
			if (condition.getDateCreated() != null)
				sql += SEARCH_RING_STATEMENT_FRAGMENTS[5];
			if (condition.getDateCreated2() != null)
				sql += SEARCH_RING_STATEMENT_FRAGMENTS[6];
		}
		
		sql += "ORDER BY SortOrder";
		AppLogger.debug("MSSqlRingDAO.searchRing().sql=" + sql);
		System.out.println("condition.isActiveOnly()=" + condition.isActiveOnly());
		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.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))) {
        			Ring item = populateRingFormResultSet(rst);
        			page.getItems().add(item);
        		}
        	}
        	page.setTotalRecords(totalRecords);
		} catch (Exception e) {
			AppLogger.debug("MSSqlRingDAO.searchRing().e.getMessage()=" + e.getMessage());

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

	/**
	 * 
	 */
	public void createRing(Ring item) throws ResourceDAOSysException {
		Connection conn = null;
		PreparedStatement stmt = null;

		String sql = "INSERT INTO TAB_RING (ParentID, SortOrder, DateCreated, LastUpdate, IsActive, IsSearchable," 
			+ "SearchKeywords, PropertyNames, PropertyValues) "
			+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

		AppLogger.debug("MSSqlRingDAO.createRing().sql=" + sql);

		try {
			conn = super.getDBConnection();
			stmt = conn.prepareStatement(sql);
			fillRingParameters(stmt, item, TYPE_ADD);

		    int rowCount = stmt.executeUpdate();
		    
		    if (rowCount != 1)
		    	throw new ResourceDAOSysException("effected row count must be 1");


		} catch (Exception e) {
			AppLogger.debug("MSSqlRingDAO.createRing().e.getMessage()=" + e.getMessage());

			throw new ResourceDAOSysException(e.getMessage());

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

	public void updateRing(Ring item) throws ResourceDAOSysException {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement stmt = null;

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

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

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

	public void deleteRing(int ringId) throws ResourceDAOSysException {
	
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement stmt = null;
			// 不是真正从数据库删除，而是设置删除标记为1
		String sql = "UPDATE TAB_RING SET Deleted = 1 WHERE IndexID = ?";
		
		try {
		    conn = super.getDBConnection();
		    stmt = conn.prepareStatement(sql);
		    stmt.setInt(1, ringId);
			    int rowCount = stmt.executeUpdate();
		    
		    if (rowCount != 1)
		    	throw new ResourceDAOSysException("invalid effected row count");
			} catch (Exception e) {
			throw new ResourceDAOSysException(e.getMessage());
		} finally {
			try {
				super.closeStatement(stmt);
				super.closeConnection(conn);
			} catch (Exception e) {
				throw new ResourceDAOSysException(e.getMessage());
			}
		}
	}


	
	protected Ring populateRingFormResultSet(ResultSet rst) throws SQLException {
		Ring item = new Ring();
		
		// Fill section properties.
		DBCommonUtils.populateSectionFormResultSet(rst, item);
		
		return item;
	}
	
	protected void fillRingParameters(PreparedStatement stmt, Ring item, int type) throws SQLException {
		int index = 1;
		try {
			if (type == TYPE_ADD) {
				stmt.setInt(index++, item.getParentId());
				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());
				

				// Serialize datas
				SerializeData data = item.deserialize();
				stmt.setString(index++, data.getKeys());
				stmt.setString(index++, data.getValues());
				stmt.setInt(index++, item.getIndexId());
				
			}
		} catch (SQLException e) {
			throw e;
		}
	}
/////////////////////////////////////////////////////////////////////////////
	
	public void createRingEntity(RingEntity item) throws ResourceDAOSysException {
		Connection conn = null;
		PreparedStatement stmt = null;

		String sql = "INSERT INTO TAB_RING_ENTITY (RingID, FileSize, Dimension, FilePath, RingMime) VALUES (?, ?, ?, ?, ?)";

		AppLogger.debug("MSSqlRingDAO.createRingEntity().sql=" + sql);

		try {
			conn = super.getDBConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, item.getRingId());
			stmt.setInt(2, item.getFileSize());
			stmt.setInt(3, item.getDimension());
			stmt.setString(4, item.getFilePath());
			stmt.setString(5, item.getRingMime());
			
		    int rowCount = stmt.executeUpdate();
		    
		    if (rowCount != 1)
		    	throw new ResourceDAOSysException("effected row count must be 1");


		} catch (Exception e) {
			AppLogger.debug("MSSqlRingDAO.createRingEntity().e.getMessage()=" + e.getMessage());

			throw new ResourceDAOSysException(e.getMessage());

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

	public Page getRingEntities(int ringId, int start, int count) throws ResourceDAOSysException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rst = null;
		
		Page page = new Page();
		String sql = "SELECT IndexID, RingID, FileSize, Dimension, FilePath, RingMime FROM TAB_RING_ENTITY WHERE RingID = ? ORDER BY IndexID DESC";
		AppLogger.debug("MSSqlRingDAO.getRingEntities().sql=" + sql);
		
		try {
			conn = super.getDBConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, ringId);

			rst = stmt.executeQuery();

        	int totalRecords = 0;
        	
        	// 获取所需要的数据
        	for (totalRecords = 0; rst.next(); totalRecords ++) {
        		if ((totalRecords >= start) && (totalRecords < (start + count))) {
        			RingEntity item = new RingEntity();

        			item.setIndexId(rst.getInt("IndexID"));
        			item.setRingId(rst.getInt("RingID"));
        			item.setDimension(rst.getInt("Dimension"));
        			item.setFilePath(rst.getString("FilePath"));
        			item.setFileSize(rst.getInt("FileSize"));
        			item.setRingMime(rst.getString("RingMime"));
        			page.getItems().add(item);
        		}
        	}
        	page.setTotalRecords(totalRecords);
		} catch (Exception e) {
			e.printStackTrace();
			AppLogger.debug("MSSqlRingDAO.getRingEntities().e.getMessage()=" + e.getMessage());

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

		return page;
	}
}