电子会议厅web开发项目

package table.affair; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import database.DatabaseFactory; public class AffairHandle { Connection con; Statement stmt; ResultSet rs; public int recordCount() { int num = 0; try { con = DatabaseFactory.getConnection(); stmt = con.createStatement(); String str = "select * from affair"; rs = stmt.executeQuery(str); while (rs.next()) { num++; } } catch (Exception e) { e.printStackTrace(); } finally { try { rs.close(); stmt.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } return num; } public boolean save(Affair affair) { boolean boo = false; int num = affair.getAffairId(); String title = affair.getTitle(); String content = affair.getContent(); String fbTime = affair.getFbTime(); int sortId = affair.getSortId(); try { con = DatabaseFactory.getConnection(); stmt = con.createStatement(); String str = "insert into affair values("; str += num + ",'" + title + "','" + content + "','" + fbTime + "'," + sortId + ")"; stmt.executeUpdate(str); boo = true; } catch (Exception e) { e.printStackTrace(); } finally { try { stmt.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } return boo; } public List<Affair> affairsList(Integer sortId) { List<Affair> list = new ArrayList<Affair>(); Affair affair; try { con = DatabaseFactory.getConnection(); stmt = con.createStatement(); String str = "select * from affair where sortId=" + sortId; rs = stmt.executeQuery(str); while (rs.next()) { affair = new Affair(); affair.setAffairId(rs.getInt("affairId")); affair.setTitle(rs.getString("title")); affair.setContent(rs.getString("content")); affair.setFbTime(rs.getString("fbTime")); affair.setSortId(rs.getInt("sortId")); list.add(affair); } } catch (Exception e) { e.printStackTrace(); } finally { try { rs.close(); stmt.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } return list; } public Affair getAffair(Integer affairId) { Affair affair = null; try { con = DatabaseFactory.getConnection(); stmt = con.createStatement(); String str = "select * from affair where affairId=" + affairId; rs = stmt.executeQuery(str); while (rs.next()) { affair = new Affair(); affair.setAffairId(rs.getInt("affairId")); affair.setTitle(rs.getString("title")); affair.setContent(rs.getString("content")); affair.setFbTime(rs.getString("fbTime")); affair.setSortId(rs.getInt("sortId")); } } catch (Exception e) { e.printStackTrace(); } finally { try { rs.close(); stmt.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } return affair; } public boolean update(Affair affair, Integer affairId) { boolean boo = false; // int num=affair.getAffairId(); String title = affair.getTitle(); String content = affair.getContent(); String fbTime = affair.getFbTime(); int sortId = affair.getSortId(); try { con = DatabaseFactory.getConnection(); stmt = con.createStatement(); String str = "update affair set title='" + title + "' ,content='" + content + "',fbTime='" + fbTime + "',sortId=" + sortId + " where affairId=" + affairId; stmt.executeUpdate(str); boo = true; } catch (Exception e) { e.printStackTrace(); } finally { try { stmt.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } return boo; } public boolean delete(Integer affairId) { boolean boo = false; try { con = DatabaseFactory.getConnection(); stmt = con.createStatement(); String str = " delete from affair where affairId=" + affairId; stmt.executeUpdate(str); boo = true; } catch (Exception e) { e.printStackTrace(); } finally { try { stmt.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } return boo; } public void reSorting(Integer affairId) { int num = recordCount(); String str; try { con = DatabaseFactory.getConnection(); stmt = con.createStatement(); for (int temp = affairId + 1; temp <= num + 1; temp++) { str = "update affair set affairId=" + (temp - 1) + " where affairId=" + temp; stmt.executeUpdate(str); } } catch (Exception e) { e.printStackTrace(); } finally { try { rs.close(); stmt.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } } public List<Affair> queryByTitle(String keyTitle) { List<Affair> list = new ArrayList<Affair>(); Affair affair; try { con = DatabaseFactory.getConnection(); stmt = con.createStatement(); String str = "select * from affair where title like '%" + keyTitle + "%'"; rs = stmt.executeQuery(str); while (rs.next()) { affair = new Affair(); affair.setAffairId(rs.getInt("affairId")); affair.setTitle(rs.getString("title")); affair.setContent(rs.getString("content")); affair.setFbTime(rs.getString("fbTime")); affair.setSortId(rs.getInt("sortId")); list.add(affair); } } catch (Exception e) { e.printStackTrace(); } finally { try { rs.close(); stmt.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } return list; } public List<Affair> queryByTime(String year, String month) { List<Affair> list = new ArrayList<Affair>(); Affair affair; int temp = Integer.parseInt(month); String str = null; try { con = DatabaseFactory.getConnection(); stmt = con.createStatement(); switch (temp) { case 0: str = "select * from affair where left(fbTime,4)='" + year + "'"; break; case 1: str = "select * from affair where mid(fbTime,6,2) between '01' and '06' and left(fbTime,4)='" + year + "'"; break; case 2: str = "select * from affair where mid(fbTime,6,2) between '07' and '12' and left(fbTime,4)='" + year + "'"; break; } rs = stmt.executeQuery(str); while (rs.next()) { affair = new Affair(); affair.setAffairId(rs.getInt("affairId")); affair.setTitle(rs.getString("title")); affair.setContent(rs.getString("content")); affair.setFbTime(rs.getString("fbTime")); affair.setSortId(rs.getInt("sortId")); list.add(affair); } } catch (Exception e) { e.printStackTrace(); } finally { try { rs.close(); stmt.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } return list; } public List<String> getYears() { List<String> list = new ArrayList<String>(); String temp = null; try { con = DatabaseFactory.getConnection(); stmt = con.createStatement(); String str = "select left(fbTime,4) from affair group by left(fbTime,4)"; rs = stmt.executeQuery(str); while (rs.next()) { temp = (rs.getString(1)); list.add(temp); } } catch (Exception e) { e.printStackTrace(); } finally { try { rs.close(); stmt.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } return list; } }