newsdao.java

有发布

package DB;

import bean.*;
import java.sql.*;
import java.sql.PreparedStatement;
import java.util.ArrayList;

public class newsDAO {
    public newsDAO() {
    }

    //向数据库中插入新闻的信息,并返回一个 int 值
    public boolean instersecond(newsBean sTitle) {
        boolean host = false;
        Connection con = null;
        PreparedStatement stmt = null;

        try {
            ConnectionBean dbcon = new ConnectionBean();
            con = dbcon.getConnection();
            stmt = con.prepareStatement(
                    "insert into newsall(Newsname,Newspath,Newsouter,kindID) values(?,?,?,?)");
            stmt.setString(1, sTitle.getNewsname());
            stmt.setString(2, sTitle.getNewspath());
            stmt.setString(3, sTitle.getNewsouter());
            stmt.setString(4, sTitle.getKindID());
            stmt.executeUpdate();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                stmt.close();
                con.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }

    //获取所有新闻的信息
    public ArrayList getnewstitle() {
        ArrayList list = new ArrayList();
        ConnectionBean dbcon = new ConnectionBean();
        Connection con = null;
        ResultSet ret = null;
        Statement pre = null;

        try {
            con = dbcon.getConnection();
            //设置结果集的属性
            pre = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.TYPE_FORWARD_ONLY);

            ret = pre.executeQuery( "select * from newsall order by Newstime desc");
            //让游标指到最后
            ret.last();


            //得到一共有多少行
            int allrow = ret.getRow();

            PageBean page = new PageBean();
            allrow =  page.getAllRows() ;
            int rowpage = page.getRowsPages();     //每页有多少行
            int allpage = page.getAllPages();      //一共有多少页
            int curpage = page.getCurPage();       //第几页
            if(allrow % rowpage == 0)
            {
                //求总页数
                allpage = allrow / rowpage;
            }
            else
            {
                allpage = allrow / rowpage + 1;
            }

            //如果当前页为第一页,把游标移回
            if(curpage == 1)
            {
                ret.beforeFirst();
            }
            else
            {
                ret.absolute((curpage - 1) * rowpage);
            }

            //循环取数据放到 ArrayList 对象里
            int i = 0;
            while (ret.next() && i < rowpage) {
                newsBean sTitle = new newsBean();
                sTitle.setID(ret.getString("ID"));
                sTitle.setNewsname(ret.getString("Newsname"));
                sTitle.setNewspath(ret.getString("Newspath"));
                sTitle.setNewstime(ret.getString("Newstime"));
                sTitle.setNewsouter(ret.getString("Newsouter"));
                sTitle.setKindID(ret.getString("kindID"));
                list.add(sTitle);

                i ++;    //防止死循环
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                //关闭连接
                ret.close();
                pre.close();
                con.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        return list;
    }

    //根据 type 的值从数据库中取出相应的数据
    public ArrayList selecttype(String type) {
        ArrayList list = new ArrayList();
        ConnectionBean dbcon = new ConnectionBean();

        Connection con = null;
        ResultSet ret = null;
        PreparedStatement pre = null;

        try {
            con = dbcon.getConnection();
            pre = con.prepareStatement(
                    "select * from newsall where kindID = '" + type + "'");
            ret = pre.executeQuery();

            //循环取数据放到 ArrayList 对象里
            while (ret.next()) {
                newsBean sTitle = new newsBean();
                sTitle.setID(ret.getString("ID"));
                sTitle.setNewsname(ret.getString("Newsname"));
                sTitle.setNewspath(ret.getString("Newspath"));
                sTitle.setNewstime(ret.getString("Newstime"));
                sTitle.setNewsouter(ret.getString("Newsouter"));
                sTitle.setKindID(ret.getString("kindID"));
                list.add(sTitle);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                //关闭连接
                pre.close();
                con.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        return list;
    }

    //根据 key 的值从数据库中取出相应的数据
    public ArrayList selectkey(String key) {
        ArrayList list = new ArrayList();
        ConnectionBean dbcon = new ConnectionBean();

        Connection con = null;
        ResultSet ret = null;
        PreparedStatement pre = null;

        try {
            con = dbcon.getConnection();
            String sql = "select * from newsall where Newsname like" + "'%" +
                         key +
                         "%'" + "or Newsouter like " + "'%" + key + "%'" +
                         "or kindID like " + "'%" + key + "%'";
            pre = con.prepareStatement(sql);
            ret = pre.executeQuery();

            //循环取数据放到 ArrayList 对象里
            while (ret.next()) {
                newsBean sTitle = new newsBean();
                sTitle.setID(ret.getString("ID"));
                sTitle.setNewsname(ret.getString("Newsname"));
                sTitle.setNewspath(ret.getString("Newspath"));
                sTitle.setNewstime(ret.getString("Newstime"));
                sTitle.setNewsouter(ret.getString("Newsouter"));
                sTitle.setKindID(ret.getString("kindID"));
                list.add(sTitle);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                //关闭连接
                pre.close();
                con.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        return list;
    }


    //删除新闻信息
    public boolean delectnews(newsBean bean) {
        //boolean flat = false;
        ConnectionBean dbcon = new ConnectionBean();

        Connection con = null;
        PreparedStatement pre = null;

        try {
            con = dbcon.getConnection();

            //删除新闻表中本条新闻的记录
            pre = con.prepareStatement(
                    "delete from newsall where ID = '" + bean.getID() + "'");
            pre.executeUpdate();

            return true;

        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                //关闭所有连接
                pre.close();
                con.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }

    }

}