品红企业宣传网站+sql+jsp

package com.company.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import com.company.dao.BaseDao; import com.company.dao.NewsDao; import com.company.entity.News; public class NewsDaoImpl extends BaseDao implements NewsDao { private Connection con = null; private PreparedStatement pstmt = null; private ResultSet rs = null; /** * 添加新闻 * @param news * @return */ public int addNews(News news) { String sql ="insert into NEWS values(?,?,?)"; SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String date = sdf.format(new Date()); String[] param = {news.getTitle(),news.getContent(),date}; return this.executeSQL(sql, param); } /** * 删除新闻 * @param news * @return */ public int deleteNews(News news) { String sql = "delete from NEWS where newsID="+news.getNewsId(); String[] param ={}; return this.executeSQL(sql, param); } /** * 根据新闻Id查询相关新闻信息 * @param newsId * @return */ public News findNewsById(int newsId) { String sql = "select * from NEWS where newsID=?"; News news = null; try{ con = this.getConn(); pstmt = con.prepareStatement(sql); pstmt.setInt(1, newsId); rs = pstmt.executeQuery(); while(rs.next()){ news = new News(); news.setNewsId(rs.getInt("newsID")); news.setTitle(rs.getString("title")); news.setContent(rs.getString("content")); news.setWriterDate(rs.getString("writerDate")); } }catch(Exception ex){ ex.printStackTrace(); }finally{ this.closeAll(rs, pstmt, con); } return news; } /** * 查询新闻列表 * @return */ public List<News> findNewsList() { String sql = "select * from news order by newsID desc"; List<News> list = new ArrayList<News>(); try{ con = this.getConn(); pstmt = con.prepareStatement(sql); rs = pstmt.executeQuery(); while(rs.next()){ News news = new News(); news.setNewsId(rs.getInt("newsID")); news.setTitle(rs.getString("title")); news.setContent(rs.getString("content")); news.setWriterDate(rs.getString("writerDate")); list.add(news); } }catch(Exception ex){ ex.printStackTrace(); }finally{ this.closeAll(rs, pstmt, con); } return list; } /** * 分页查询新闻列表 * @param page 页数，每页显示10条 * @return */ public List<News> findNewsByPage(int page) { int rowBegin = 0; if(page>1){ rowBegin = 10*(page-1); } String sql = "select top 10 * from news where newsID not in(select top "+ rowBegin +" newsID from news order by writerDate desc) order by writerDate desc"; List<News> list = new ArrayList<News>(); try{ con = this.getConn(); pstmt = con.prepareStatement(sql); rs = pstmt.executeQuery(); while(rs.next()){ News news = new News(); news.setNewsId(rs.getInt("newsID")); news.setTitle(rs.getString("title")); news.setContent(rs.getString("content")); news.setWriterDate(rs.getString("writerDate")); list.add(news); } }catch(Exception ex){ ex.printStackTrace(); }finally{ this.closeAll(rs, pstmt, con); } return list; } /** * 分页查询新闻列表1 * @param page 页数，每页显示7条 * @return */ public List<News> findNewsTop() { String sql = "select top 7 * from news order by writerDate desc"; List<News> list = new ArrayList<News>(); try{ con = this.getConn(); pstmt = con.prepareStatement(sql); rs = pstmt.executeQuery(); while(rs.next()){ News news = new News(); news.setNewsId(rs.getInt("newsID")); news.setTitle(rs.getString("title")); news.setContent(rs.getString("content")); news.setWriterDate(rs.getString("writerDate")); list.add(news); } }catch(Exception ex){ ex.printStackTrace(); }finally{ this.closeAll(rs, pstmt, con); } return list; } /** * 批量删除1 * @param newsID * @return */ public int deleteNews(String newsID) { String sql = "delete from NEWS where newsID in("+ newsID +") "; String[] param ={}; return this.executeSQL(sql, param); } /** * 使用Statement接口的addBatch()进行批量处理然后调用executeBatch()执行批量删除2 * @param newsID * @return */ public int deleteNews(String[] newsID){ int[] num = null; Statement stmt = null; try{ con = this.getConn(); stmt = con.createStatement(); for(int i=0;i<newsID.length;i++){ String sql = "delete from News where newsID = "+newsID[i]; stmt.addBatch(sql); } num = stmt.executeBatch(); }catch(Exception ex){ ex.printStackTrace(); }finally{ this.closeAll(null, null, con); } return num.length; } /** * 查询所有的新闻 * @return 返回所有的新闻 */ public int findAllNews() { int num = 0; String sql = "select count(*) from news"; try{ con = getConn(); pstmt = con.prepareStatement(sql); rs = pstmt.executeQuery(); while(rs.next()){ num = rs.getInt(1); } }catch(Exception ex){ ex.printStackTrace(); }finally{ closeAll(rs, pstmt, con); } return num; } }