/** * 设置邮件状态 * * @param minfo 邮件信息对象 * @return 操作结果,-1-失败 * @throws SQLException 数据库异常 */ public static int setMailStatus(MailInfo minfo) throws SQLException { if (minfo == null) return -1; DBSource dbsrc = new DBSource(); try { /** 修改学校信息表中记录 */ String sql = "update mailbox set mstatus=? where id=? and receiverid=?"; dbsrc.prepareStatement(sql); dbsrc.setInt(1, minfo.getStatus()); dbsrc.setInt(2, minfo.getId()); dbsrc.setInt(3, minfo.getReceiverId()); return dbsrc.executeUpdate(); } finally { if (dbsrc != null) dbsrc.close(); } }
/** * 获取送件箱邮件信息对象 * * @param uinfo 读邮件的用户对象 * @param mid 邮件ID * @return MailInfo 邮件信息对象 */ public static MailInfo getSendInfo(UserInfo uinfo, int mid) throws SQLException { if (uinfo == null) return null; DBSource dbsrc = new DBSource(); try { /** 从数据库中检索邮件信息 */ String sql = "select id,receiverid,receivername,sub,content,sendtime from sendbox where id=? and senderid=?"; dbsrc.prepareStatement(sql); dbsrc.setInt(1, mid); dbsrc.setInt(2, uinfo.getId()); dbsrc.executeQuery(); if (dbsrc.next()) { MailInfo minfo = new MailInfo(); minfo.setId(dbsrc.getInt("id")); minfo.setReceiverId(dbsrc.getInt("receiverid")); minfo.setReceiverName(dbsrc.getString("receivername")); minfo.setSub(dbsrc.getString("sub")); minfo.setContent(dbsrc.getString("content")); minfo.setSendTime(dbsrc.getTimestamp("sendtime")); return minfo; } } finally { if (dbsrc != null) dbsrc.close(); } return null; }
/** * 获取邮件信息对象 * * @param uinfo 读邮件的用户对象 * @param mid 邮件ID * @return MailInfo 邮件信息对象 */ public static MailInfo getMailInfo(UserInfo uinfo, int mid) throws SQLException { if (uinfo == null) return null; DBSource dbsrc = new DBSource(); MailInfo minfo = null; try { /** 从数据库中检索邮件信息 */ String sql = "select id,senderid,sendername,sub,content,mstatus,sendtime from mailbox where id=? and receiverid=?"; dbsrc.prepareStatement(sql); dbsrc.setInt(1, mid); dbsrc.setInt(2, uinfo.getId()); dbsrc.executeQuery(); if (dbsrc.next()) { minfo = new MailInfo(); minfo.setId(dbsrc.getInt("id")); minfo.setSenderId(dbsrc.getInt("senderid")); minfo.setSenderName(dbsrc.getString("sendername")); minfo.setSub(dbsrc.getString("sub")); minfo.setContent(dbsrc.getString("content")); minfo.setStatus(dbsrc.getInt("mstatus")); minfo.setSendTime(dbsrc.getTimestamp("sendtime")); } if (minfo != null) { sql = "update mailbox set mstatus=? where id=? and receiverid=?"; dbsrc.prepareStatement(sql); dbsrc.setInt(1, SysUtils.MAIL_STATUS_READ); dbsrc.setInt(2, minfo.getId()); dbsrc.setInt(3, uinfo.getId()); int ret = dbsrc.executeUpdate(); if (ret != 1) { return null; } return minfo; } } finally { if (dbsrc != null) dbsrc.close(); } return null; }
/** * 获取用户邮件的分页列表 * * @param uid 用户ID * @param curPage 当前页面 * @param pageSize 分页大小 * @return 页面对象(含分页信息和当前页面的数据记录) * @throws SQLException 数据库异常 */ public static Page getPageMails(int uid, int curPage, int pageSize) throws SQLException { if (pageSize <= 0) { pageSize = SysUtils.PAGE_SIZE; } DBSource dbsrc = new DBSource(); Page page = null; MailInfo mail = null; try { /** 从数据库中检索邮件信息 */ String sql = "select id,senderid,sendername,sub,mstatus,sendtime from mailbox where receiverid=? order by id desc"; dbsrc.prepareStatement( sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); dbsrc.setInt(1, uid); dbsrc.executeQuery(); dbsrc.setPageSize(pageSize); dbsrc.setPageNo(curPage); page = new Page(dbsrc.getRowNum(), dbsrc.getPageSize(), dbsrc.getPageNo()); while (dbsrc.pageNext()) { mail = new MailInfo(); mail.setId(dbsrc.getInt("id")); mail.setSenderId(dbsrc.getInt("senderid")); mail.setSenderName(dbsrc.getString("sendername")); mail.setSub(dbsrc.getString("sub")); mail.setStatus(dbsrc.getInt("mstatus")); mail.setSendTime(dbsrc.getTimestamp("sendtime")); page.addData(mail); } } finally { if (dbsrc != null) dbsrc.close(); } return page; }
/** * 发送邮件 * * @param minfo 邮件信息对象 * @return int 邮件的ID值 * @throws SQLException 数据库异常 */ public static int sendMail(MailInfo minfo) throws SQLException, KeyException { if (minfo == null) return -1; /** 获取收件箱邮件ID值 */ SimpleKeyGen keygen = SimpleKeyGen.getInstance(KeyInit.KEY_MAILID); int mid = keygen.getNextKey(); /** 获取系统当前时间 */ Timestamp now = new Timestamp(System.currentTimeMillis()); DBSource dbsrc = new DBSource(); try { /** 在收件箱信息表中插入新记录 */ String sql = "insert into mailbox (id,receiverid,receivername,senderid,sendername,sub,content,mstatus,sendtime) values(?,?,?,?,?,?,?,?,?)"; dbsrc.prepareStatement(sql); dbsrc.setInt(1, mid); dbsrc.setInt(2, minfo.getReceiverId()); dbsrc.setString(3, minfo.getReceiverName()); dbsrc.setInt(4, minfo.getSenderId()); dbsrc.setString(5, minfo.getSenderName()); dbsrc.setString(6, minfo.getSub()); dbsrc.setString(7, minfo.getContent()); dbsrc.setInt(8, minfo.getStatus()); dbsrc.setTimestamp(9, now); int affectedRows = dbsrc.executeUpdate(); /** 插入新记录是否成功 */ if (affectedRows < 1) { return -1; } return mid; } finally { if (dbsrc != null) dbsrc.close(); } }
/** * 发送邮件,并保存邮件到送件箱 * * @param minfo 邮件信息对象 * @return int 邮件的ID值 * @throws SQLException 数据库异常 */ public static int ssMail(MailInfo minfo) throws SQLException, KeyException { if (minfo == null) return -1; /** 获取收件箱邮件ID值 */ SimpleKeyGen keygen = SimpleKeyGen.getInstance(KeyInit.KEY_MAILID); int mid = keygen.getNextKey(); /** 获取送件箱邮件ID值 */ keygen = SimpleKeyGen.getInstance(KeyInit.KEY_SENDID); int sid = keygen.getNextKey(); /** 获取系统当前时间 */ Timestamp now = new Timestamp(System.currentTimeMillis()); DBTransaction dbtrac = new DBTransaction(); boolean dbErrors = false; int affectedRows = 0; String sql = null; try { /** 在收件箱信息表中插入新记录 */ sql = "insert into mailbox (id,receiverid,receivername,senderid,sendername,sub,content,mstatus,sendtime) values(?,?,?,?,?,?,?,?,?)"; dbtrac.prepareStatement(sql); dbtrac.setInt(1, mid); dbtrac.setInt(2, minfo.getReceiverId()); dbtrac.setString(3, minfo.getReceiverName()); dbtrac.setInt(4, minfo.getSenderId()); dbtrac.setString(5, minfo.getSenderName()); dbtrac.setString(6, minfo.getSub()); dbtrac.setString(7, minfo.getContent()); dbtrac.setInt(8, minfo.getStatus()); dbtrac.setTimestamp(9, now); affectedRows = dbtrac.executeUpdate(); /** 插入新记录是否成功 */ if (affectedRows < 1) { dbErrors = true; return -1; } /** 在送件箱信息表中插入新记录 */ sql = "insert into sendbox (id,receiverid,receivername,senderid,sendername,sub,content,sendtime) values(?,?,?,?,?,?,?,?)"; dbtrac.prepareStatement(sql); dbtrac.setInt(1, mid); dbtrac.setInt(2, minfo.getReceiverId()); dbtrac.setString(3, minfo.getReceiverName()); dbtrac.setInt(4, minfo.getSenderId()); dbtrac.setString(5, minfo.getSenderName()); dbtrac.setString(6, minfo.getSub()); dbtrac.setString(7, minfo.getContent()); dbtrac.setTimestamp(8, now); affectedRows = dbtrac.executeUpdate(); /** 插入新记录是否成功 */ if (affectedRows < 1) { dbErrors = true; return -1; } return mid; } catch (SQLException ex) { dbErrors = true; throw ex; } finally { // 判断是否出现数据库错误,如果出现,回滚所有数据库操作,否则执行 if (dbErrors) { dbtrac.rollback(); } else { dbtrac.commit(); } if (dbtrac != null) dbtrac.close(); } }