bbs.rar_BBS java_bbs_jsp

/* * To change this template, choose Tools | Templates * and open the template in the editor. */ package dbutil; import bean.Accessory; import bean.Admin; import bean.Message; import bean.Module; import bean.Reply; import bean.Topic; import bean.User; import java.awt.image.BufferedImage; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.io.Reader; import java.io.Writer; import java.sql.Blob; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.util.ArrayList; import java.util.Date; import java.util.GregorianCalendar; import java.util.Iterator; import java.util.TimeZone; import java.util.logging.Level; import java.util.logging.Logger; import javax.imageio.ImageIO; import javax.servlet.http.HttpServletRequest; import oracle.jdbc.OracleConnection; /** * * @author Qu Chenhao */ public class DBQuirier { private static Connection getConnection() { Connection connection = null; try { Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "system", "wq6811156"); ((OracleConnection) connection).setSessionTimeZone(TimeZone.getDefault().getID()); } catch (Exception ex) { ex.printStackTrace(); } return connection; } public static void quiryBasicInformation(HttpServletRequest request) throws IOException { Connection connection = getConnection(); ResultSet rs1 = null; ResultSet rs2 = null; Statement stmt1 = null; Statement stmt2 = null; String sql; if (connection != null) { try { ArrayList<Module> module_list = new ArrayList(); TimeZone tz = TimeZone.getTimeZone("Asia/Shanghai"); GregorianCalendar c = new GregorianCalendar(tz); stmt1 = connection.createStatement(); stmt2 = connection.createStatement(); sql = "select * from MODULE order by module_name"; rs1 = stmt1.executeQuery(sql); sql = "select module_name,count(*) as cnt from BBSTOPIC" + " where issue_time between to_date('" + c.get(GregorianCalendar.YEAR) + "-" + (c.get(GregorianCalendar.MONTH) + 1) + "-" + c.get(GregorianCalendar.DATE) + " 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('" + c.get(GregorianCalendar.YEAR) + "-" + (c.get(GregorianCalendar.MONTH) + 1) + "-" + c.get(GregorianCalendar.DATE) + " 23:59:59','yyyy-mm-dd hh24:mi:ss') group by module_name " + "order by module_name"; rs2 = stmt2.executeQuery(sql); while (rs2.next()) { String module_name1 = rs2.getString("module_name"); while (rs1.next()) { Module module = new Module(); String module_name2 = rs1.getString("module_name"); Timestamp module_time = rs1.getTimestamp("module_time"); module.setModule_name(module_name2); module.setModule_time(module_time); if (module_name1.equals(module_name2)) { module.setNumNewTopic(rs2.getInt("cnt")); module_list.add(module); break; } else { module.setNumNewTopic(0); module_list.add(module); } } } while (rs1.next()) { Module module = new Module(); module.setModule_name(rs1.getString("module_name")); module.setModule_time(rs1.getTimestamp("module_time")); module.setNumNewTopic(0); module_list.add(module); } request.setAttribute("module_list", module_list); sql = "select topic_id,topic_title,issue_time," + "topic_status,author,module_name,cnt from " + "(select BBSTOPIC.topic_id,BBSTOPIC.topic_title,BBSTOPIC.issue_time," + "BBSTOPIC.topic_status,BBSTOPIC.author,BBSTOPIC.module_name" + ",count(BBSTOPIC.topic_id) as cnt" + " from BBSTOPIC,VISITATION_RECORD where " + "BBSTOPIC.topic_id = VISITATION_RECORD.topic_id " + "group by BBSTOPIC.topic_id,BBSTOPIC.topic_title" + ",BBSTOPIC.issue_time,BBSTOPIC.topic_status,BBSTOPIC.author,BBSTOPIC.module_name order by cnt desc) " + "where ROWNUM<=10 order by ROWNUM asc"; rs1 = stmt1.executeQuery(sql); ArrayList<Topic> topic_list = new ArrayList(10); while (rs1.next()) { Topic topic = new Topic(); topic.setTopic_id(rs1.getInt("topic_id")); topic.setIssue_time(rs1.getTimestamp("issue_time")); topic.setTopic_status(rs1.getString("topic_status").charAt(0)); topic.setAuthor(rs1.getString("author")); topic.setTopic_title(rs1.getString("topic_title")); topic.setVisit_cnt(rs1.getInt("cnt")); topic.setModule_name(rs1.getString("module_name")); topic_list.add(topic); } request.setAttribute("topic_list", topic_list); connection.close(); } catch (SQLException ex) { ex.printStackTrace(); } } } public static User quiryUserInformation(String username) { Connection connection = getConnection(); ResultSet rs = null; Statement stmt = null; String sql; try { stmt = connection.createStatement(); sql = "select * from BBSUSER where username=" + "'" + username + "'"; rs = stmt.executeQuery(sql); if (rs.next()) { User user = new User(); user.setUsername(username); user.setRealname(rs.getString("realname")); user.setPassword(rs.getString("password")); user.setBirthday(rs.getDate("birthday")); user.setEmail(rs.getString("email")); user.setExperience(rs.getInt("experience")); Blob blob = rs.getBlob("photo"); user.setPhoto(blob); user.setWealth(rs.getInt("wealth")); return user; } connection.close(); } catch (SQLException ex) { ex.printStackTrace(); } return null; } public static boolean insertUser(String username, String realname, String password, String email, String birthday) { Connection connection = getConnection(); Statement stmt = null; String sql = null; try { stmt = connection.createStatement(); sql = "insert into BBSUSER(username,realname,password,email"; if (birthday != null) { sql += ",birthday"; } sql += ") values('" + username + "','" + realname + "','" + password + "','" + email + "'"; if (birthday != null) { sql += ",to_date('" + birthday + "','yyyy-mm-dd')"; } sql += ")"; stmt.execute(sql); connection.commit(); connection.close(); return true;