newsdao.java

企业宣传网 用JSP+SQLSERVER开发 商品展示 在线聊天 留言板 新闻发布 后台管理

package com.accpedu.LHcompany.dao;

import java.sql.*;
import java.util.*;
import java.util.Date;

import com.accpedu.LHcompany.entity.news;
import com.accpedu.LHcompany.entity.product;
import java.text.*;

public class newsDao {

	Connection con=null;
	PreparedStatement ps=null;
	DBAccess dba=new DBAccess();
	
	public ArrayList<news> select(int page)
	{
		ArrayList<news> al=new ArrayList<news>();
		con=dba.DBOpen();
		try
		{
			ps=con.prepareStatement("select top 10* from news where newsID not in (select top "+(page-1)*10+" newsID from news)");
			ResultSet rs=ps.executeQuery();
			while(rs.next())
			{
				news n=new news();
				n.setNewsID(rs.getInt("newsId"));
				n.setTitle(rs.getString("title"));
				n.setContent(rs.getString("Content"));
				n.setWriterDate(rs.getString("WriterDate"));
				al.add(n);
			}
			rs.close();
			dba.DBClose();
		}catch(SQLException ce)
		{
			System.out.println(ce.getMessage());
		}
		
		return al;
	}
	
	public int selectPage()
	{
		int x=0;
		con=dba.DBOpen();
		try
		{
			ps=con.prepareStatement("select count(*) from news");
			ResultSet rs=ps.executeQuery();
			while(rs.next())
			{
				x=rs.getInt(1);
			}
			dba.DBClose();
		}catch(SQLException ce)
		{
			System.out.println(ce.getMessage());
		}
		if(x%10==0)
		{
			x=x/10;
			
		}else
		{
			x=x/10+1;
		}
		return x;
	}
	
	public news selectID(int id)
	{
		news n=new news();
		con=dba.DBOpen();
		try
		{
			ps=con.prepareStatement("select * from news where newsID="+id);
			ResultSet rs=ps.executeQuery();
			while(rs.next())
			{
				n.setNewsID(rs.getInt("newsId"));
				n.setTitle(rs.getString("title"));
				n.setContent(rs.getString("Content"));
				n.setWriterDate(rs.getString("WriterDate"));
			}
			rs.close();
			dba.DBClose();
		}catch(SQLException ce)
		{
			System.out.println(ce.getMessage());
		}
		
		return n;
	}

	
	public int delete(int id)
	{
		int x=0;
		con=dba.DBOpen();
		try
		{
			ps=con.prepareStatement("delete from news where newsID="+id);
			x=ps.executeUpdate();
			dba.DBClose();
		}catch(SQLException ce)
		{
			System.out.println(ce.getMessage());
		}
		return x;
	}
	
	public int insert(news p)
	{
		int x=0;
		con=dba.DBOpen();
		SimpleDateFormat sdf=new SimpleDateFormat("yy-MM-dd");
		p.setWriterDate(sdf.format(new Date()));
		
		try
		{
			ps=con.prepareStatement("insert into news values(?,?,?)");
    		ps.setString(1,p.getTitle());
    		ps.setString(2,p.getContent());
    		ps.setString(3,p.getWriterDate());
    		x=ps.executeUpdate();
    		ps.close();
			dba.DBClose();
		}catch(SQLException ce)
		{
			System.out.println(ce.getMessage());
		}
		return x;	
	}
	
	public int update(news p)
	{
		int x=0;
		con=dba.DBOpen();
		try
		{
			ps=con.prepareStatement("update news set title=?,content=?,writerDate=?)");
    		ps.setString(1,p.getTitle());
    		ps.setString(2,p.getContent());
    		ps.setString(3,p.getWriterDate());
    		x=ps.executeUpdate();
    		ps.close();
			dba.DBClose();
		}catch(SQLException ce)
		{
			System.out.println(ce.getMessage());
		}
		return x;	
	}
	
	public ArrayList<news> selects()
	{
		ArrayList<news> al=new ArrayList<news>();
		con=dba.DBOpen();
		try
		{
			ps=con.prepareStatement("select * from news");
			ResultSet rs=ps.executeQuery();
			while(rs.next())
			{
				news n=new news();
				n.setNewsID(rs.getInt("newsId"));
				n.setTitle(rs.getString("title"));
				n.setContent(rs.getString("Content"));
				n.setWriterDate(rs.getString("WriterDate"));
				al.add(n);
			}
			rs.close();
			dba.DBClose();
		}catch(SQLException ce)
		{
			System.out.println(ce.getMessage());
		}
		
		return al;
	}
	
	public ArrayList<news> selects1()
	{
		ArrayList<news> al=new ArrayList<news>();
		con=dba.DBOpen();
		try
		{
			ps=con.prepareStatement("select top 8* from news order by newsid desc");
			ResultSet rs=ps.executeQuery();
			while(rs.next())
			{
				news n=new news();
				n.setNewsID(rs.getInt("newsId"));
				n.setTitle(rs.getString("title"));
				n.setContent(rs.getString("Content"));
				n.setWriterDate(rs.getString("WriterDate"));
				al.add(n);
			}
			rs.close();
			dba.DBClose();
		}catch(SQLException ce)
		{
			System.out.println(ce.getMessage());
		}
		
		return al;
	}
}