jsp新闻发布系统

package com.cjp.verify; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; public class ConnectionSql { private static Connection con = GetConnection.getConnection(); public static Member selectMember(int id) { Statement s = null; Member member = null; try { s = con.createStatement(); String sql = "select * from member where id=" + id + ";"; ResultSet rs = s.executeQuery(sql); while (rs.next()) { int mem_id = rs.getInt(1); String mem_name = rs.getString(2); String mem_password = rs.getString(3); int mem_grade = rs.getInt(4); member = new Member(mem_id, mem_name, mem_password, mem_grade); } } catch (SQLException e) { e.printStackTrace(); } finally { try { if (s != null) { s.close(); } } catch (SQLException e) { } } return member; } public static int addMember(int id, String password, String name) { int num = 0; try { String sql = "insert member values(?,?,?,?)" + ";"; PreparedStatement ps = con.prepareStatement(sql); ps.setInt(1, id); ps.setString(2, name); ps.setString(3, password); ps.setInt(4, 1); num = ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } return num; } public static boolean addColumn(int order, String name, String description) { Statement s = null; int num = 0; try { s = con.createStatement(); String sql = "insert columns values(?,?,?)" + ";"; PreparedStatement ps = con.prepareStatement(sql); ps.setInt(1, order); ps.setString(2, name); ps.setString(3, description); num = ps.executeUpdate(); if (num != 0) { return true; } } catch (SQLException e) { e.printStackTrace(); } finally { if (s != null) { try { s.close(); } catch (SQLException e) { e.printStackTrace(); } } } return false; } public static ArrayList<Columns> showAllColumns() { Statement s = null; ResultSet rs = null; ArrayList<Columns> list = new ArrayList<Columns>(); int num = 0; Columns columns = null; try { s = con.createStatement(); String sql = "select * from columns"; rs = s.executeQuery(sql); while (rs.next()) { int order = rs.getInt(1); String name = rs.getString(2); String description = rs.getString(3); columns = new Columns(order, name, description); if (columns != null) { list.add(columns); } columns = null; } } catch (SQLException e) { e.printStackTrace(); } finally { try { s.close(); } catch (SQLException e) { e.printStackTrace(); } } return list; } public static boolean addClass(String className, int columnOrder, String classDescription, int classOrder) { int num = 0; try { String sql = "insert classes values(?,?,?,?)" + ";"; PreparedStatement ps = con.prepareStatement(sql); ps.setInt(1, classOrder); ps.setString(2, className); ps.setString(3, classDescription); ps.setInt(4, columnOrder); num = ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } if (num != 0) { return true; } return false; } public static ArrayList<Classes> showAllClass() { Statement s = null; ResultSet rs = null; ArrayList<Classes> list = new ArrayList<Classes>(); int num = 0; Classes classes = null; try { s = con.createStatement(); String sql = "select c1.*,c2.naeme from classes as c1 left join columns as c2 on c1.col_order=c2.orderNum "; rs = s.executeQuery(sql); while (rs.next()) { int order = rs.getInt(1); String name = rs.getString(2); String description = rs.getString(3); int colOrder = rs.getInt(4); String colName = rs.getString(5); classes = new Classes(order, name, description, colOrder, colName); if (classes != null) { list.add(classes); } classes = null; } } catch (SQLException e) { e.printStackTrace(); } finally { try { s.close(); } catch (SQLException e) { e.printStackTrace(); } } return list; } public static boolean addNews(String title, int property, String news_Proper, String new_From_Time, String keyWord, String news_From, String content, int mem_id) { int num = 0; try { String sql = "insert news(news_tittle,col_order,issuer,release_time,key_word,source,content,mem_id) value(?,?,?,?,?,?,?,?)"; PreparedStatement ps = con.prepareStatement(sql); ps.setString(1, title); ps.setInt(2, property); ps.setString(3, news_Proper); ps.setString(4, new_From_Time); ps.setString(5, keyWord); ps.setString(6, news_From); ps.setString(7, content); ps.setInt(8, mem_id); num = ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } if (num != 0) { return true; } return false; } public static ArrayList<News> showAllNews() { Statement s = null; ResultSet rs = null; ArrayList<News> list = new ArrayList<News>(); News news = null; try { s = con.createStatement(); String sql = "select c1.*,c2.naeme from news as c1 left join columns as c2 on c1.col_order=c2.orderNum "; rs = s.executeQuery(sql); while (rs.next()) { int num = rs.getInt(1); String title = rs.getString(2); int colOrder = rs.getInt(3); String issur = rs.getString(4); String releaseTime = rs.getString(5); String keyWords = rs.getString(6); String source = rs.getString(7); String content = rs.getString(8); int memId = rs.getInt(9); String columnName = rs.getString(10); news = new News(num,title, colOrder, issur, releaseTime, keyWords, source, content, memId, columnName); if (news != null) { list.add(news); } news = null; } } catch (SQLException e) { e.printStackTrace(); } finally { try { s.close(); } catch (SQLException e) { e.printStackTrace(); } } return list; } // 修改栏目信息 public static boolean alterColumn(int order, String name, String description) { int num = 0; try { String sql = "update columns set naeme=?,describes=? where orderNum=?"; PreparedStatement ps = con.prepareStatement(sql); ps.setString(1, name); ps.setString(2, description); ps.setInt(3, order); num = ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } if (num != 0) { return true; } return false; } // 删除栏目 public static void delete(int order, String name ) { int num = 0; try { String sql = "delete from "+name+" where orderNum=?"; PreparedStatement ps = con.prepareStatement(sql); ps.setInt(1, order); num = ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } } //修改子类信息 public static boolean alterClass(int order, String className, String description, int colOrder) { int num = 0; try { String sql = "update classes set name=?,describes=? ,col_order=? where orders=?"; PreparedStatement ps = con.prepareStatement(sql); ps.setString(1, className); ps.setString(2, description); ps.setInt(3, colOrder); ps.setInt(4, order); num = ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } if (num != 0) { return true; } return false; } }