东北大学软件学院JSP大作业

package db_Service; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import model.Message; public class MessageService { private Connection conn; private PreparedStatement pstmt; public MessageService(){ conn = new DBRead().getCon(); } public void recall(int id){ updateStauts(id,"recall"); int itemId=0; try { pstmt=conn.prepareStatement("select itemId from message where id=?"); pstmt.setInt(1, id); ResultSet rs =pstmt.executeQuery(); if(rs.next()){ itemId=rs.getInt(1); System.out.println("itemId="+itemId); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } ItemService is=new ItemService(); is.updateFind(itemId, "1", null); } public void updatesItem(Message message){ try { pstmt=conn.prepareStatement("update Message set status=?,message=?,isread=0 where itemId=? and listener=?"); pstmt.setString(1, message.getStatus()); pstmt.setString(2, message.getMessage()); pstmt.setInt(3, message.getItemId()); pstmt.setString(4, message.getListener()); pstmt.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } public void updateStauts(int id, String status){ try { pstmt= conn.prepareStatement("update Message set status=? where id =?"); pstmt.setString(1,status); pstmt.setInt(2, id); pstmt.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public int[] countAll(String userid){ int[] i=new int[4]; try { pstmt=conn.prepareStatement("select count(*) from Message where listener=? and isread=0"); pstmt.setString(1, userid); ResultSet rs=pstmt.executeQuery(); if(rs.next()) i[0]=rs.getInt(1); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { pstmt=conn.prepareStatement("select count(*) from Message where listener=? and isread=0 and status in('agreed','refused','send')"); pstmt.setString(1, userid); ResultSet rs=pstmt.executeQuery(); if(rs.next()) i[1]=rs.getInt(1); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { pstmt=conn.prepareStatement("select count(*) from Message where listener=? and isread=0 and status in('agree','refuse','wait','check')"); pstmt.setString(1, userid); ResultSet rs=pstmt.executeQuery(); if(rs.next()) i[2]=rs.getInt(1); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { pstmt=conn.prepareStatement("select count(*) from Message where listener=? and isread=0 and status in('check')"); pstmt.setString(1, userid); ResultSet rs=pstmt.executeQuery(); if(rs.next()) i[3]=rs.getInt(1); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return i; } public Message selectMessageByItem(int id){ Message message=new Message(); try { pstmt= conn.prepareStatement("select * from Message where itemId=? and status='send'"); pstmt.setInt(1, id); ResultSet rs =pstmt.executeQuery(); if(rs.next()){ message.setId(rs.getInt(1)); message.setMessage(rs.getString(2)); message.setTalker(rs.getString(3)); message.setListener(rs.getString(4)); message.setTime(rs.getString(5)); message.setIsRead(rs.getInt(6)); message.setStatus(rs.getString(7)); message.setItemId(rs.getInt(8)); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } return message; } public Message selectMessageById(int id){ Message message=new Message(); try { pstmt= conn.prepareStatement("select * from Message where id=?"); pstmt.setInt(1, id); ResultSet rs =pstmt.executeQuery(); if(rs.next()){ message.setId(rs.getInt(1)); message.setMessage(rs.getString(2)); message.setTalker(rs.getString(3)); message.setListener(rs.getString(4)); message.setTime(rs.getString(5)); message.setIsRead(rs.getInt(6)); message.setStatus(rs.getString(7)); message.setItemId(rs.getInt(8)); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } return message; } public int noRead(List<Message> messages){ int number=0; for(int i=0;i<messages.size();i++){ if(!(messages.get(i).getIsRead()==1)){ number++; } } return number; } public List<Message> serverMessage(String userId){ List<Message> messages=new ArrayList<Message>(); try { pstmt= conn.prepareStatement("select * from Message where listener=? and talker='gm'"); pstmt.setString(1, userId); ResultSet rs =pstmt.executeQuery(); while(rs.next()){ Message message=new Message(); message.setId(rs.getInt(1)); message.setMessage(rs.getString(2)); message.setListener(userId); message.setTime(rs.getString(5)); message.setIsRead(rs.getInt(6)); message.setStatus(rs.getString(7)); message.setItemId(rs.getInt(8)); messages.add(message); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } return messages; } public List<Message> receiveMessage(String userId){ List<Message> messages=new ArrayList<Message>(); try { pstmt= conn.prepareStatement("select * from Message where listener=? and status in('agreed','refused','send')"); pstmt.setString(1, userId); ResultSet rs =pstmt.executeQuery(); while(rs.next()){ Message message=new Message(); message.setId(rs.getInt(1)); message.setMessage(rs.getString(2)); message.setListener(userId); message.setTime(rs.getString(5)); message.setIsRead(rs.getInt(6)); message.setStatus(rs.getString(7)); message.setItemId(rs.getInt(8)); messages.add(message); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { pstmt= conn.prepareStatement("update Message set isread=1 where listener=? and status in('agreed','refused','send')"); pstmt.setString(1, userId); pstmt.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return messages; } public List<Message> checkMessage(String userId){ List<Message> messages=new ArrayList<Message>(); try { pstmt= conn.prepareStatement("select * from Message where listener=? and status in('check')"); pstmt.setString(1, userId); ResultSet rs =pstmt.executeQuery(); while(rs.next()){ Message message=new Message(); message.setId(rs.getInt(1)); message.setMessage(rs.getString(2)); message.setListener(userId); message.setTime(rs.getString(5)); message.setIsRead(rs.getInt(6)); message.setStatus(rs.getString(7)); message.setItemId(rs.getInt(8)); messages.add(message); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { pstmt= conn.prepareStatement("update Message set isread=1 where listener=? an