newsmanager.java

精品课程的java源码

package com.zzx.manager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

import com.zzx.bean.News;
import com.zzx.util.DataConn;

public class NewsManager {

	Connection conn = null;
	PreparedStatement psmt = null;

	// 对消息进行删除的操作
	public void delete(int id) throws Exception {

		String sql = "delete from news where id=?";
		try {
			conn = new DataConn().getConn();
			psmt = conn.prepareStatement(sql);
			psmt.setInt(1, id);
			psmt.executeUpdate();
			psmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// 对消息进行插入的操作
	public void insert(News news) {
		String sql = "insert into news(title,content,type)values(?,?,?)";
		try {
			conn = new DataConn().getConn();
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, news.getTitle());
			psmt.setString(2, news.getContent());
			psmt.setInt(3, news.getType());
			psmt.executeUpdate();
			psmt.close();
			System.out.println("news insert successfully!");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	// 对消息进行查询的操作
	public List queryNews(int currentPage, int lineSize,int type) {

		int count = currentPage*lineSize;
		String sql  = "select TOP "+lineSize+" * FROM ( SELECT TOP "+count+"  * from news where type=" +type+" ORDER BY id ASC ) as aSysTable ORDER BY id DESC";
		
		//String sql = "select * from teacher limit " +(currentPage-1)*lineSize+"," +lineSize ;
		
		List all = new LinkedList();
		ResultSet rs = null;
		try {
			conn = new DataConn().getConn();
			psmt = conn.prepareStatement(sql);
			rs = psmt.executeQuery();

			while (rs.next()) {
				News news = new News();
                news.setId(rs.getInt(1));
				news.setTitle(rs.getString(2));
				news.setContent(rs.getString(3));
				news.setType(rs.getInt(4));
				all.add(news);
			}
			rs.close();
			psmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return all;

	}

	// 查询消息的总数
	public int queryCount(int type) {
		int count = 0;
		ResultSet rs = null;
		String sql = "SELECT count(*) FROM news where type ="+type;
		try {
			conn = new DataConn().getConn();
			psmt = conn.prepareStatement(sql);
			rs = psmt.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		//System.out.println(count);
		return count;
	}

	public News queryById(int id) {
		
		String sql = "SELECT id,title,content,type FROM news WHERE id=?";	
		ResultSet rs = null;
		News news = new News();
		try {
			conn = new DataConn().getConn();		
			psmt = conn.prepareStatement(sql);
			psmt.setInt(1, id);
			rs = psmt.executeQuery();			
			if(rs.next()){
				news.setId(rs.getInt(1));
				news.setTitle(rs.getString(2));
				news.setContent(rs.getString(3));
				news.setType(rs.getInt(4));
				}
			rs.close();
			psmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return news;
	}

    public News queryByName(String name,int type) {
		
		String sql = "SELECT id,title,content,type FROM news WHERE title=? and type=?";	
		ResultSet rs = null;
		News news = new News();
		try {
			conn = new DataConn().getConn();		
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, name);
			psmt.setInt(2, type);
			rs = psmt.executeQuery();			
			if(rs.next()){
				news.setId(rs.getInt(1));
				news.setTitle(rs.getString(2));
				news.setContent(rs.getString(3));
				news.setType(rs.getInt(4));
				}
			rs.close();
			psmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return news;
	}

	// 对消息进行更新的操作
     public void updateNews(News news){
    	 String sql = "UPDATE news SET title=?,content=?,type=? WHERE id=?" ;		 
		 
		 try {
			   conn = new DataConn().getConn();
				psmt = conn.prepareStatement(sql);					
				psmt.setString(1, news.getTitle());
				psmt.setString(2, news.getContent());
				psmt.setInt(3, news.getType());
				psmt.setInt(4, news.getId());
		
				psmt.executeUpdate();
				psmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}			 
     }
	
	
	
	
	
	 public static void main(String[] args){
		  
	 News news = new News();
	
	 //测试插入方法
//	  news.setContent("ceshi11");
//	  news.setTitle("标题22222");
//	  news.setType(2);
//	  new NewsManager().insert(news);
		
	 //测试查询方法
	 NewsManager nm = new NewsManager();
	 Iterator itor = nm.queryNews(3,2,3).iterator();
	 while(itor.hasNext()){
	 news =(News)itor.next();
	 System.out.println(news.getTitle());
	 System.out.println(news.getContent());
	 //System.out.println(news.getType());
	 }
	 
	 //测试查询数目
//	  NewsManager nm = new NewsManager();
//	  System.out.println(nm.queryCount(2));
		   
	 
	 //测试数据更新
//	 news.setTitle("更新");
//	 news.setContent("ceshi");
//	 news.setType(1);
//	 news.setId(7);
//	 NewsManager nm = new NewsManager();
//	 nm.updateNews(news);
	 
	 //测试根据id查询
//	 NewsManager nm = new NewsManager();
//	 news=nm.queryById(7);
//	 System.out.println(news.getTitle());
	 
	 }

}