Java项目:在线新闻平台系统(java+jsp+jdbc+mysql)

package com.news.dao.impl; import com.news.dao.CmsArticleDao; import com.news.entity.Article; import com.news.utils.DateUtils; import com.news.utils.JdbcUtils; import java.sql.*; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @Author:weiming * @Date:2021/3/28 */ public class CmsArticleDaoImpl implements CmsArticleDao { private static final Connection CONNECTION = JdbcUtils.getConnection(); @Override public List<Article> getAllArticleInfo(int pageNum, int pageSize){ List<Article> list = new ArrayList<>(); String sql = "SELECT a.id,title, author, is_deleted, is_show, text_id,text,img_url,category_name,category_id,create_time FROM cms_article as a,(SELECT id FROM cms_article) as b ,cms_article_text as c \n" + "where a.id = b.id and a.text_id = c.id and a.is_deleted = 0 and is_check = 1 order by a.id desc limit ?, ? "; try { PreparedStatement pst = CONNECTION.prepareStatement(sql); pst.setInt(1, pageNum); pst.setInt(2, pageSize); ResultSet resultSet = pst.executeQuery(); while(resultSet.next()){ Article article = new Article(); article.setId(resultSet.getInt(1)); article.setTitle(resultSet.getString(2)); article.setAuthor(resultSet.getString(3)); article.setIsDeleted(resultSet.getInt(4)); article.setIsShow(resultSet.getInt(5)); article.setTextId(resultSet.getInt(6)); article.setText(resultSet.getString(7)); article.setImgUrl(resultSet.getString(8)); article.setCategoryName(resultSet.getString(9)); article.setCategoryId(resultSet.getInt(10)); // TODO 日期转换 Date sqlDate = resultSet.getDate(11); long time = sqlDate.getTime(); java.util.Date date = new java.util.Date(time); article.setCreateTime(date); article.setCreateTimeStr(DateUtils.sqlDateToString(sqlDate)); list.add(article); } } catch (SQLException throwables) { throwables.printStackTrace(); } return list; } @Override public int add(Article article) { // TODO 日期 String sql = "INSERT INTO cms_article (title, author, is_deleted, is_show, text_id, img_url, category_name, category_id, create_time, is_check, editor_id, role)" + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; try { CONNECTION.setAutoCommit(false); int textId = getTextId(article.getText()); article.setTextId(textId); PreparedStatement pst = CONNECTION.prepareStatement(sql); pst.setString(1, article.getTitle()); pst.setString(2, article.getAuthor()); pst.setInt(3, article.getIsDeleted()); pst.setInt(4, article.getIsShow()); pst.setInt(5, article.getTextId()); pst.setString(6, article.getImgUrl()); pst.setString(7, article.getCategoryName()); pst.setInt(8, article.getCategoryId()); pst.setDate(9, DateUtils.toSqlDate(new java.util.Date())); pst.setInt(10, article.getIsCheck()); pst.setInt(11, article.getEditorId()); pst.setInt(12, article.getRole()); pst.executeUpdate(); CONNECTION.commit(); CONNECTION.setAutoCommit(true); return 1; } catch (SQLException throwables) { throwables.printStackTrace(); } return 0; } private int getTextId(String text) { int textId = 0; PreparedStatement pst = null; try { pst = CONNECTION.prepareStatement("INSERT INTO cms_article_text (text) VALUE (?)"); pst.setString(1, text); int result = pst.executeUpdate(); if (result == 1){ pst = CONNECTION.prepareStatement("SELECT DISTINCT LAST_INSERT_ID() from cms_article_text"); } ResultSet resultSet = pst.executeQuery(); while (resultSet.next()){ textId = resultSet.getInt(1); } } catch (SQLException throwables) { throwables.printStackTrace(); } return textId; } @Override public int update(Article article) { int result = 0 ,result2 = 0; String sql = "UPDATE cms_article SET title = ?, author = ?, is_deleted = ?, is_show = ?, category_name = ?, category_id=?, img_url = ? WHERE id = ?"; String sql2 = "UPDATE cms_article_text SET text = ? WHERE id = ?"; try { CONNECTION.setAutoCommit(false); PreparedStatement pst = null; pst= CONNECTION.prepareStatement(sql); pst.setString(1, article.getTitle()); pst.setString(2, article.getAuthor()); pst.setInt(3, article.getIsDeleted()); pst.setInt(4, article.getIsShow()); pst.setString(5, article.getCategoryName()); pst.setInt(6, article.getCategoryId()); pst.setString(7, article.getImgUrl()); pst.setInt(8, article.getId()); ; result = pst.executeUpdate(); pst.close();// 需要关闭否则报空指针 pst= CONNECTION.prepareStatement(sql2); pst.setString(1, article.getText()); pst.setInt(2, article.getTextId()); result2 = pst.executeUpdate(); CONNECTION.commit(); CONNECTION.setAutoCommit(true); if (result == 1 && result2 == 1){ return result; } } catch (SQLException throwables) { throwables.printStackTrace(); } return 0; } @Override public int delete(Integer newsId) { int result = 0; String sql = "UPDATE cms_article SET is_deleted = ? WHERE id = ?"; try { PreparedStatement pst = CONNECTION.prepareStatement(sql); if (pst != null){ pst.setInt(1, 1); pst.setInt(2, newsId); } result = pst.executeUpdate(); } catch (SQLException throwables) { throwables.printStackTrace(); } return result; } @Override public Article getArticleById(int id) { String sql = "SELECT b.id, title, author, is_deleted, is_show, text_id, img_url,create_time,b.text " + "FROM cms_article as a inner join cms_article_text as b on a.text_id = b.id where a.id = ?"; try { PreparedStatement pst = CONNECTION.prepareStatement(sql); Article article = new Article(); pst.setInt(1, id); ResultSet rs = pst.executeQuery(); int count = 0; while(rs.next()){{ article.setId( rs.getInt(1)); article.setTitle( rs.getString(2)); article.setAuthor(rs.getString(3)); article.setIsDeleted(rs.getInt(4)); article.setIsShow( rs.getInt(5)); article.setTextId( rs.getInt(6)); article.setImgUrl(rs.getString(7)); article.setCreateTimeStr(DateUtils.sqlDateToString(rs.getDate(8))); article.setText(rs.getString(9)); count++; }} rs.close(); pst.close(); if (count != 0){ return article; } } catch (SQLException throwables) { throwables.printStackTrace(); } return null; } @Override public Map<String,Object> getArticleListByCatelogyId(int catelogyId) { Map<String,Object> map = new HashMap<>(2); String sql = "SELECT a.id,title,create_time,category_name FROM cms_article as a i