polldaoimpl.java

近几年来

package tarena.business;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;

import org.hibernate.*;
import org.hibernate.criterion.Expression;
import org.hibernate.criterion.Order;

import tarena.pojo.Poll;
import tarena.pojo.PollLine;
import tarena.pojo.Statistics;
import tarena.util.ContextUtil;
import tarena.util.HibernateCallback;
import tarena.util.HibernateTemplate;

public class PollDAOImpl {
	public boolean save(final Session session,final Poll poll) {
		return new HibernateTemplate().save(session, new HibernateCallback() {
			public Object execute(Session session) throws HibernateException {
				session.save(poll);
				return null;
			};
		});
	}

	public boolean save(Connection conn, List allPoll) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn.setAutoCommit(false);

			// 为poll和pollLine准备好主键值
			int pollId = 1;
			int pollLineId = 1;

			int startPollId = 1;

			pstmt = conn.prepareStatement("select max(id) id from poll");
			rs = pstmt.executeQuery();
			if (rs.next()) {
				pollId = rs.getInt(1);
				startPollId = pollId;
			}
			pstmt = conn.prepareStatement("select max(id) id from poll_line");
			rs = pstmt.executeQuery();
			if (rs.next()) {
				pollLineId = rs.getInt(1);
			}

			// 插入poll
			String pollSql = "insert into poll(id, teacher_id, course_id, classes_id, "
					+ "created_date, checked_date, checked_person_id, amount, available_num,"
					+ "totalScore,avg_score, status) values(?,?,?,?,?,?,?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(pollSql);
			for (Iterator it = allPoll.iterator(); it.hasNext();) {
				Poll poll = (Poll) it.next();
				pstmt.setInt(1, (++pollId));
				pstmt.setInt(2, poll.getTeacher().getId().intValue());
				pstmt.setInt(3, poll.getCourse().getId().intValue());
				pstmt.setInt(4, poll.getClasses().getId().intValue());
				pstmt.setDate(5, (java.sql.Date) poll.getCreatedDate());
				pstmt.setDate(6, (java.sql.Date) poll.getCheckedDate());
				if (poll.getCheckedPerson() != null)
					pstmt.setInt(7, poll.getCheckedPerson().getId().intValue());
				else
					pstmt.setInt(7, 1);
				pstmt.setInt(8, poll.getAmount().intValue());
				pstmt.setInt(9, poll.getAvailableNum().intValue());
				pstmt.setInt(10, poll.getTotalscore().intValue());
				pstmt.setDouble(11, poll.getAvgScore().doubleValue());
				pstmt.setString(12, poll.getStatus());
				pstmt.addBatch();
			}
			pstmt.executeBatch();

			//插入pollLine
			String pollLineSql = "insert into poll_line(id, poll_id, c1, c2,c3, c4,c5,"
					+ "c6,c7, c8, c9, c10,c11, c12,c13, c14,q1, q2,avgscore,isvalid) "
					+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(pollLineSql);
			for (Iterator it = allPoll.iterator(); it.hasNext();) {
				Poll poll = (Poll) it.next();
				// Set pollLineSet = find(poll.getId()).getPollLineSet();
				Set pollLineSet = poll.getPollLineSet();
				++startPollId;
				for (Iterator pit = pollLineSet.iterator(); pit.hasNext();) {
					PollLine pollLine = (PollLine) pit.next();
					pstmt.setInt(1, ++pollLineId);
					pstmt.setInt(2, startPollId);
					pstmt.setInt(3, pollLine.getC1().intValue());
					pstmt.setInt(4, pollLine.getC2().intValue());
					pstmt.setInt(5, pollLine.getC3().intValue());
					pstmt.setInt(6, pollLine.getC4().intValue());
					pstmt.setInt(7, pollLine.getC5().intValue());
					pstmt.setInt(8, pollLine.getC6().intValue());
					pstmt.setInt(9, pollLine.getC7().intValue());
					pstmt.setInt(10, pollLine.getC8().intValue());
					pstmt.setInt(11, pollLine.getC9().intValue());
					pstmt.setInt(12, pollLine.getC10().intValue());
					pstmt.setInt(13, pollLine.getC11().intValue());
					pstmt.setInt(14, pollLine.getC12().intValue());
					pstmt.setInt(15, pollLine.getC13().intValue());
					pstmt.setInt(16, pollLine.getC14().intValue());
					pstmt.setString(17, list2String(pollLine.getQ1()));
					pstmt.setString(18, list2String(pollLine.getQ2()));
					pstmt.setDouble(19, pollLine.getAvgScore());
					pstmt.setString(20, pollLine.getIsValid());
					pstmt.addBatch();
				}
			}
			pstmt.executeBatch();

			conn.commit();
			return true;
		} catch (SQLException e) {
			if (conn != null)
				try {
					conn.rollback();
				} catch (SQLException e1) {
				}
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e1) {
			}
		}
	}

	private String list2String(List l) {
		StringBuffer str = new StringBuffer();
		for (int i = 0; i < l.size() - 1; i++) {
			str.append(l.get(i).toString() + ContextUtil.DILIM);
		}
		str.append(l.get(l.size() - 1));
		return str.toString();
	}

	public boolean delete(final Session session,final Poll poll) {
		return new HibernateTemplate().delete(session, new HibernateCallback() {
			public Object execute(Session session) throws HibernateException {
				session.delete(poll);
				return null;
			};
		});
	}

	public boolean delete(final Session session,final List allPoll) {
		return new HibernateTemplate().delete(session, new HibernateCallback() {
			public Object execute(Session session) throws HibernateException {
				for (Iterator it = allPoll.iterator(); it.hasNext();) {
					Poll poll = (Poll) it.next();
					session.delete(poll);
				}
				return null;
			};
		});
	}

	public boolean update(final Session session,final Poll poll) {
		return new HibernateTemplate().delete(session, new HibernateCallback() {
			public Object execute(Session session) throws HibernateException {
				session.update(poll);
				return null;
			};
		});
	}

	public Poll find(final Session session,final Integer id) {
		return (Poll) new HibernateTemplate().find(session, new HibernateCallback() {
			public Object execute(Session session) throws HibernateException {
				return session.load(Poll.class, id);
			};
		});
	}

	public List find(final Session session) {
		return (List) new HibernateTemplate().find(session, new HibernateCallback() {
			public Object execute(Session session) throws HibernateException {
				List result = new ArrayList();
				List list = session.createQuery("from Poll").list();
				for (Iterator it = list.iterator(); it.hasNext();) {
					Poll poll = (Poll) it.next();
					Hibernate.initialize(poll);
				}
				result.addAll(list);
				return result;
			};
		});
	}

	public List find(final Session session, final Poll poll, final Date startDate, final Date endDate,
			final String orderby) {
		return (List) new HibernateTemplate().find(session, new HibernateCallback() {
			public Object execute(Session session) throws HibernateException {
				List list = new ArrayList();
				Map results = new TreeMap();
				Criteria criteria = session.createCriteria(Poll.class);
				if (poll != null && poll.getStatus() != null) {
					criteria.add(Expression.eq("status", poll.getStatus()));
				}
				if (poll != null && poll.getTeacher() != null) {
					criteria.add(Expression.eq("teacher", poll.getTeacher()));
				}
				if (poll != null && poll.getClasses() != null) {
					criteria.add(Expression.eq("classes", poll.getClasses()));
				}
				if (poll != null && poll.getCourse() != null) {
					criteria.add(Expression.eq("course", poll.getCourse()));
				}
				if (startDate != null)
					criteria.add(Expression.ge("createdDate", startDate));
				if (endDate != null)
					criteria.add(Expression.le("createdDate", endDate));
				if (orderby.equals("asc"))
					criteria.addOrder(Order.asc("createdDate"));
				else
					criteria.addOrder(Order.desc("createdDate"));

				for (Iterator it = criteria.list().iterator(); it.hasNext();) {
					Poll poll = (Poll) it.next();
					if (!results.containsKey(poll.getId())) {
						results.put(poll.getId(), "");
						list.add(poll);
					}
				}
				return list;
			};
		});
	}

	public List find(final Session session,final Date startDate, final Date endDate,
			final String orderby) {
		return (List) new HibernateTemplate().find(session, new HibernateCallback() {
			public Object execute(Session session) throws HibernateException {
				Map result = new TreeMap();
				List resultList = new ArrayList();

				String sql = "from Poll p where p.status = 'y' and p.createdDate between :startDate and :endDate order by :orderby";
				System.out.println(session+"  fff ");
				Iterator it = session.createQuery(sql).setDate("startDate",
						startDate).setDate("endDate", endDate).setString(
						"orderby", orderby).iterate();
				
				while(it.hasNext()) {
					Poll p = (Poll) it.next();
					Statistics statistics = null;
					double avgScore = 0.0d;

					if (result.get(p.getTeacher().getId())!=null)
						statistics = (Statistics) result.get(p.getTeacher().getId());
					else {
						statistics = new Statistics();
						statistics.setTeacher(p.getTeacher());
						result.put(p.getTeacher().getId(), statistics);
					}

					statistics.getPollList().add(p);
					if (statistics.getAvgScore() != null)
						avgScore = statistics.getAvgScore().doubleValue();
					statistics.setAvgScore(new Double(avgScore
							+ p.getAvgScore().doubleValue()));
				}

				for (Iterator iter = result.values().iterator(); iter.hasNext();) {
					Statistics statistics = (Statistics) iter.next();
					statistics.setAvgScore(new Double(statistics
							.getAvgScore().doubleValue()
							/ statistics.getPollList().size()));
					resultList.add(statistics);
				}
				System.out.println(resultList);
				return resultList;
			};
		});
	}
}