企业门户网站（利用jsp技术并连接数据库）

package cn.edu.hbcf.connection; import java.sql.*; import java.util.List; import java.util.ArrayList; import cn.edu.hbcf.dao.Category; import cn.edu.hbcf.dao.News; import cn.edu.hbcf.dao.Product; import cn.edu.hbcf.dao.User; public class DBConn { String DRIVER="com.microsoft.jdbc.sqlserver.SQLServerDriver"; String URL="jdbc:microsoft:sqlserver://localhost:1433;databasename=db_net"; String USERNAME="sa"; String PWD="123"; Connection con=null; Statement stat=null; PreparedStatement pstat=null; ResultSet rs=null; public Object getConnetion; public Connection getConnection(){ try{ Class.forName(DRIVER); con=DriverManager.getConnection(URL,USERNAME,PWD); }catch(ClassNotFoundException e){ System.out.println("未成功注册"+e.toString()); }catch(SQLException e){ System.out.println("数据库连接失败"+e.toString()); } return con; } /** * 查询用户 * @param user * @return true/false */ public boolean check(User user){ boolean flag=false; try{ this.getConnection(); String sql="select * from tb_usertable where name=? and password=?"; pstat=con.prepareStatement(sql); pstat.setString(1, user.getName()); pstat.setString(2, user.getPassword()); rs=pstat.executeQuery(); if(rs.next()){ flag=true; } this.closeAll(); }catch(SQLException e){ System.out.println("用户查询失败"+e.toString()); } return flag; } /** *查找全部新闻 * @return news */ public List<News> queryAllNews(){ List<News> list=new ArrayList<News>(); try{ this.getConnection(); String sql="select * from tb_news"; pstat=con.prepareStatement(sql); rs=pstat.executeQuery(); while(rs.next()){ News news=new News(); news.setId(rs.getInt("id")); news.setAuthor(rs.getString("author")); news.setContent(rs.getString("content")); news.setTitle(rs.getString("title")); news.setSubmittime(rs.getString("submittime")); list.add(news); } this.closeAll(); }catch(SQLException e){ } return list; } /** * * @param news * @return flag * 如果成功更新一条新闻，则返回1，否则返回0； */ public int newsUpdate(News news){ int flag=0; try{ this.getConnection(); String sql="update tb_news set title=?,content=?,author=?,submittime=? where id=?"; pstat=con.prepareStatement(sql); pstat.setString(1, news.getTitle()); pstat.setString(2, news.getContent()); pstat.setString(3, news.getAuthor()); pstat.setString(4, news.getSubmittime()); pstat.setInt(5,news.getId()); flag=pstat.executeUpdate(); this.closeAll(); }catch(SQLException e){ System.out.println("更新新闻产生异常"+e.toString()); } return flag; } /** * * @param id * @return flag * 如果删除新闻成功，返回1，否则返回0； */ public int newsDel(String id){ int flag=0; News news=new News(); news.setId(Integer.parseInt(id)); try{ this.getConnection(); String sql="delete from tb_news where id=?"; pstat=con.prepareStatement(sql); pstat.setInt(1, news.getId()); flag=pstat.executeUpdate(); this.closeAll(); }catch(SQLException e){ System.out.println("删除新闻产生异常"+e.toString()); } //System.out.println(flag); return flag; } /** * 依据id查询新闻 * @param id * @return news */ public News queryNewsById(String id){ News news=null; try{ this.getConnection(); String sql="select * from tb_news where id=?"; pstat=con.prepareStatement(sql); pstat.setInt(1,Integer.parseInt(id)); rs=pstat.executeQuery(); if(rs.next()){ news=new News(); news.setId(rs.getInt("id")); news.setAuthor(rs.getString("author")); news.setContent(rs.getString("content")); news.setTitle(rs.getString("title")); news.setSubmittime(rs.getString("submittime")); } this.closeAll(); }catch(SQLException e){ System.out.println("查询一条新闻出错"+e.toString()); } return news; } /** * 添加一条新闻 * @param news * @param * @return flag * 如果成功插入一条新闻，则返回1，否则返回0 */ public int addNews(News news){ int flag=0; try{ this.getConnection(); String sql="insert into tb_news values(?,?,?,?)"; pstat=con.prepareStatement(sql); pstat.setString(1, news.getTitle()); pstat.setString(2, news.getContent()); pstat.setString(3, news.getAuthor()); pstat.setString(4, news.getSubmittime()); flag=pstat.executeUpdate(); this.closeAll(); }catch(SQLException e){ System.out.println("添加一条新闻异常"+e.toString()); } return flag; } /** * 查询所有类别 * @return category */ public List<Category> queryAllCategory(){ List<Category> list=new ArrayList<Category>(); try{ this.getConnection(); String sql="select * from tb_category"; pstat=con.prepareStatement(sql); rs=pstat.executeQuery(); while(rs.next()){ Category category=new Category(); category.setId(rs.getInt("id")); category.setCategoryname(rs.getString("categoryname")); category.setSubmittime(rs.getString("submittime")); list.add(category); } this.closeAll(); }catch(SQLException e){ System.out.println("查询类别失败"+e.toString()); } return list; } /** * 依据id查询类别 * @param id * @return category */ public Category queryCategoryById(String id){ Category category=null; try{ this.getConnection(); String sql="select * from tb_category where id=?"; pstat=con.prepareStatement(sql); pstat.setInt(1,Integer.parseInt(id)); rs=pstat.executeQuery(); if(rs.next()){ category=new Category(); category.setId(rs.getInt(1)); category.setCategoryname(rs.getString("categoryname")); category.setSubmittime(rs.getString("submittime")); } this.closeAll(); }catch(SQLException e){ System.out.println("查询类别失败"+e.toString()); } return category; } /** * 添加类别 * return flag */ public int categoryAdd(Category category){ int flag=0; try{ this.getConnection(); String sql="insert into tb_category values(?,?)"; pstat=con.prepareStatement(sql); pstat.setString(1, category.getCategoryname()); pstat.setString(2, category.getSubmittime()); flag=pstat.executeUpdate(); this.closeAll(); }catch(SQLException e){ System.out.print("添加类别失败"+e.toString()); } return flag; } /** * 修改类别 * @param category * @return 0/1 */ public int updateCategory(Category category){ int flag=0; try{ this.getConnection(); String sql="update tb_category set categoryname=?,submittime=? where id=?"; pstat=con.prepareStatement(sql); pstat.setString(1, category.getCategoryname()); pstat.setStri