templatedaoimpl.java

这是一个工作流管理的后端EJB实现

package com.coshare.joyteam.projectMgr.dao.mssql;

import java.io.StringReader;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.coshare.joyteam.projectMgr.dao.DAOException;
import com.coshare.joyteam.projectMgr.dto.TemplateDTO;
import com.coshare.joyteam.util.ID;

public class TemplateDAOImpl extends AbstractMssqlDAO implements
		com.coshare.joyteam.projectMgr.dao.TemplateDAO {
	
	static String SQL_INSERT = "insert into [WfTemplate] ([TemplateName], [Version], [Creator], [CreatorType], [CreatingTime], [theDescription]) values (?,?,?,?,?,?)";
	static String SQL_UPDATE = "update [WfTemplate] set [TemplateName] = ?, [Version] = ?, [Creator] = ?, [CreatorType] = ?, [CreatingTime] = ?, [theDescription] = ? where [templateId] = ?";
	static String SQL_DELETE = "delete from [WfTemplate] where [templateId] = ?";
	static String SQL_SELECT_ByTemplateId = "select * from [WfTemplate] where [templateId] = ?";
	static String SQL_SELECT_ByNameVersion = "select * from [WfTemplate] where [TemplateName] = ? and [Version] = ?";
	static String SQL_SELECT_ALL = "select * from [WfTemplate]";
	static String SQL_SELECT_ByTemplateName = "select * from [WfTemplate] where [TemplateName] = ?";

	public boolean AddNew(TemplateDTO dtoObj) throws DAOException {
		PreparedStatement ps = da.prepareStatement(SQL_INSERT);		
		boolean rt = false;
		try {
			ps.setString(1, dtoObj.getTemplateName());
			ps.setInt(2, dtoObj.getVersion());
			ps.setString(3, dtoObj.getCreator());
			ps.setInt(4, dtoObj.getCreatorType());
			ps.setTimestamp(5, new Timestamp(dtoObj.getCreatingTime().getTime()));
			ps.setCharacterStream(6, new StringReader(dtoObj.getTheDescription()), dtoObj.getTheDescription().length());
			rt = ps.executeUpdate() > 0;
		} catch (SQLException e) {
			throw new DAOException("insert failed.", e);
		}
		finally {
			try {
				ps.close();
			} catch (SQLException e) {
			}
		}
		return rt;
	}

	public boolean Update(TemplateDTO dtoObj) throws DAOException {
		PreparedStatement ps = da.prepareStatement(SQL_UPDATE);		
		boolean rt = false;
		try {
			ps.setString(1, dtoObj.getTemplateName());
			ps.setInt(2, dtoObj.getVersion());
			ps.setString(3, dtoObj.getCreator());
			ps.setInt(4, dtoObj.getCreatorType());
			ps.setTimestamp(5, new Timestamp(dtoObj.getCreatingTime().getTime()));
			ps.setCharacterStream(6, new StringReader(dtoObj.getTheDescription()), dtoObj.getTheDescription().length());
			ps.setInt(7, dtoObj.getTemplateId().getId());
			rt = ps.executeUpdate() > 0;
		} catch (SQLException e) {
			throw new DAOException("update failed.", e);
		}
		finally {
			try {
				ps.close();
			} catch (SQLException e) {
			}
		}
		return rt;
	}

	public boolean Delete(TemplateDTO dtoObj) throws DAOException {
		PreparedStatement ps = da.prepareStatement(SQL_DELETE);		
		boolean rt = false;
		try {
			ps.setInt(1, dtoObj.getTemplateId().getId());
			rt = ps.executeUpdate() > 0;
		} catch (SQLException e) {
			throw new DAOException("delete failed.", e);
		}
		finally {
			try {
				ps.close();
			} catch (SQLException e) {
			}
		}
		return rt;
	}

	public TemplateDTO getTemplate(ID templateId) throws DAOException {
		PreparedStatement ps = da.prepareStatement(SQL_SELECT_ByTemplateId);
		TemplateDTO rt = null;
		try {
			ps.setInt(1, templateId.getId());
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				rt = getDTOFromRs(rs);
			}
			else {
				throw new DAOException("not found.");
			}
		} catch (SQLException e) {
			throw new DAOException("select failed.", e);
		}
		finally {
			try {
				ps.close();
			} catch (SQLException e) {
			}
		}
		return rt;
	}

	public List getTemplates(String templateName) throws DAOException {
		PreparedStatement ps = da.prepareStatement(SQL_SELECT_ByTemplateName);
		ArrayList rt = new ArrayList();
		try {
			ps.setString(1, templateName);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				rt.add(getDTOFromRs(rs));
			}
		} catch (SQLException e) {
			throw new DAOException("select failed.", e);
		}
		finally {
			try {
				ps.close();
			} catch (SQLException e) {
			}
		}
		return rt;
	}

	public TemplateDTO getTemplate(String templateName, int version)
			throws DAOException {
		PreparedStatement ps = da.prepareStatement(SQL_SELECT_ByNameVersion);
		TemplateDTO rt = null;
		try {
			ps.setString(1, templateName);
			ps.setInt(2, version);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				rt = getDTOFromRs(rs);
			}
			else {
				throw new DAOException("not found.");
			}
		} catch (SQLException e) {
			throw new DAOException("select failed.", e);
		}
		finally {
			try {
				ps.close();
			} catch (SQLException e) {
			}
		}
		return rt;
	}

	public List getTemplates() throws DAOException {
		PreparedStatement ps = da.prepareStatement(SQL_SELECT_ALL);
		ArrayList rt = new ArrayList();
		try {
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				rt.add(getDTOFromRs(rs));
			}
		} catch (SQLException e) {
			throw new DAOException("select failed.", e);
		}
		finally {
			try {
				ps.close();
			} catch (SQLException e) {
			}
		}
		return rt;
	}
	
	private TemplateDTO getDTOFromRs(ResultSet rs) throws SQLException {
		TemplateDTO dto = new TemplateDTO();
		dto.setTemplateId(rs.getInt("TemplateId"));
		dto.setTemplateName(rs.getString("TemplateName"));
		dto.setVersion(rs.getInt("Version"));
		dto.setCreator(rs.getString("Creator"));
		dto.setCreatorType(rs.getInt("CreatorType"));
		dto.setCreatingTime(rs.getTimestamp("CreatingTime"));
		dto.setTheDescription(rs.getString("theDescription"));	
		return dto;
	}

}