实现新闻与评论的增删改查
package project.dao.impl;
import project.dao.BaseDao;
import project.dao.NewsDao;
import project.entity.News;
import project.entity.NewsContent;
import java.io.IOException;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class NewsDaoImpl extends BaseDao implements NewsDao {
@Override
//分页查询全部(模糊查询)新闻 根据时间降序排列
public List selectAllNewsPageing(String title,int pageNo,int pageSize) throws IOException, SQLException {
List list = new ArrayList<>();
String sql = “”;
ResultSet resultSet = null;
if (title=="" ||title==null ){
sql = “SELECT id,title,summary,author,createdate FROM news_detail " +
“ORDER BY createdate DESC LIMIT ?,?”;
Object[]params = {(pageNo-1)*pageSize,pageSize};
resultSet = excuteSql(sql,params);
}else {
sql=“SELECT id,title,summary,author,createdate FROM news_detail WHERE title LIKE ? " +
“ORDER BY createdate DESC LIMIT ?,?”;
Object[]params = {”%”+title+"%",(pageNo-1)*pageSize,pageSize};
resultSet = excuteSql(sql,params);
}
News news = null;
while (resultSet.next()){
int id = resultSet.getInt(“id”);
String newstitle = resultSet.getString(“title”);
String summary = resultSet.getString(“summary”);
String author = resultSet.getString(“author”);
Date createdate = resultSet.getDate(“createdate”);
news = new News(id,newstitle,summary,author,createdate);
list.add(news);
}
return list;
}
@Override
//分页查看评论 通过新闻编号 根据时间降序排列
public List<NewsContent> selectAllContentByNewsId(int newsid,int pageNo,int pageSize) throws IOException, SQLException {
List<NewsContent> list = new ArrayList<>();
String sql = "SELECT id,content,author ,createdate FROM news_commment WHERE newsid = ? ORDER BY createdate DESC LIMIT ?,?";
Object[]params = {newsid,(pageNo-1)*pageSize,pageSize};
ResultSet resultSet = excuteSql(sql,params);
while (resultSet.next()){
int id = resultSet.getInt("id");
String content = resultSet.getString("content");
String author = resultSet.getString("author");
Date createdate = resultSet.getDate("createdate");
NewsContent newsContent = new NewsContent(id,newsid,content,author,createdate);
list.add(newsContent);
}
return list;
}
@Override
//增加评论
public boolean addContent(NewsContent newsContent) throws IOException {
String sql = "INSERT INTO news_commment (newsid,content,author,createdate) VALUE(?,?,?,?) ";
Object[]params = {newsContent.getNewsid(),newsContent.getContent(),newsContent.getAuthor(),newsContent.getCreatedate()};
int line = excuteUpdate(sql,params);
boolean flag = false;
if (line>0){
flag = true;
System.out.println("添加成功!");
}
return flag;
}
@Override
//删除新闻功能(包括评论) 根据主键
public boolean deleteNewsById(int id) throws IOException, SQLException {
String sql = "DELETE FROM news_detail WHERE id = ? ";
Object[]params = {id};
int line = excuteUpdate(sql,params);
String sql2 = "select count(1) from news_commment where newsid = ?";
Object [] params2 = {id};
ResultSet resultSet = excuteSql(sql2,params2);
int i = 0;
while (resultSet.next()){
i = resultSet.getInt(1);
}
boolean b = this.deleteContentByNewsid(id);
boolean flag = false;
if (i>0){
if (line>0&&b==true){
flag=true;
}
}else {
if (line>0){
flag=true;
}
}
return flag;
}
@Override
public boolean deleteContentByNewsid(int newsid) throws IOException {
String sql = " DELETE FROM news_commment WHERE newsid=?";
Object []params = {newsid};
int line = excuteUpdate(sql,params);
boolean flag = false;
if (line>0){
flag=true;
}
return flag;
}
@Override
//查询新闻总数
public int CountNews(String title) throws IOException, SQLException {
String sql = "";
int count = 0;
ResultSet resultSet = null;
if (title=="" ||title==null){
sql = "select count(1) from news_detail";
Object[]params = {};
resultSet = excuteSql(sql,params);
}else {
sql="select count(1) from news_detail where title like ?";
Object[]params = {"%"+title+"%"};
resultSet = excuteSql(sql,params);
}
while (resultSet.next()){
count = resultSet.getInt(1);
}
return count;
}
@Override
//查询评论总数
public int CountContentByNewid(int newsid) throws IOException, SQLException {
String sql = "SELECT COUNT(1)FROM news_commment WHERE newsid = ?";
Object[]params = {newsid};
ResultSet resultSet = excuteSql(sql,params);
int count = 0;
while (resultSet.next()){
count = resultSet.getInt(1);
}
return count;
}
public static void main(String[] args) throws IOException, SQLException {
NewsDao newsDao = new NewsDaoImpl();
}
}