企业宣传网jsp+sql2005

package impl; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import dao.BaseDao; import dao.ProductDao; import entity.Product; public class ProductDaoImpl implements ProductDao { public int addProduct(Product product) { Connection con=BaseDao.getConnection(); int i=0; Statement st=null; ResultSet rs=null; String sql="insert into product values('"+product.getSerialNumber()+"','"+product.getName()+"','"+product.getBrand()+"','"+product.getModel()+"','"+product.getPrice()+"','"+product.getPicture()+"','"+product.getDescription()+"')"; try { st=con.createStatement(); i=st.executeUpdate(sql); if(i!=0){ System.out.println("ok"); } } catch (SQLException e) { e.printStackTrace(); } finally { BaseDao.closeAll(con, st, rs); } return i; } public int deleteProduct(int productID) { Connection con=BaseDao.getConnection(); int i=0; Statement st=null; ResultSet rs=null; String sql="delete from product where productID='"+productID+"'"; try { st=con.createStatement(); i=st.executeUpdate(sql); if(i!=0){ System.out.println("ok"); } } catch (SQLException e) { e.printStackTrace(); }finally{ BaseDao.closeAll(con, st, rs); } return i; } public List findAllProducts() { List list=new ArrayList(); Connection con=BaseDao.getConnection(); Statement st=null; ResultSet rs=null; String sql="select * from product"; try { st=con.createStatement(); rs=st.executeQuery(sql); while(rs.next()){ Product product=new Product(); product.setProductID(rs.getInt("productID")); product.setSerialNumber(rs.getString("serialNumber")); product.setName(rs.getString("name")); product.setBrand(rs.getString("brand")); product.setModel(rs.getString("model")); product.setPrice(rs.getFloat("price")); product.setPicture(rs.getString("picture")); product.setDescription(rs.getString("description")); list.add(product); } } catch (SQLException e) { e.printStackTrace(); }finally{ BaseDao.closeAll(con, st, rs); } return list; } public Product findProductByID(int productID) { Connection con=BaseDao.getConnection(); Product product=new Product(); Statement st=null; ResultSet rs=null; String sql="select * from product where productID='"+productID+"'"; try { st=con.createStatement(); rs=st.executeQuery(sql); if(rs.next()){ product.setProductID(rs.getInt("productID")); product.setSerialNumber(rs.getString("serialNumber")); product.setName(rs.getString("name")); product.setBrand(rs.getString("brand")); product.setModel(rs.getString("model")); product.setPrice(rs.getFloat("price")); product.setPicture(rs.getString("picture")); product.setDescription(rs.getString("description")); } } catch (SQLException e) { e.printStackTrace(); }finally{ BaseDao.closeAll(con, st, rs); } return product; } public List getListDataByPage(int page) { List list=new ArrayList(); Connection con=BaseDao.getConnection(); CallableStatement call=null; ResultSet rs=null; String sql="exec showAllProduct '"+page+"'"; try { call=con.prepareCall(sql); rs=call.executeQuery(); while(rs.next()){ Product product=new Product(); product.setProductID(rs.getInt("productID")); product.setSerialNumber(rs.getString("serialNumber")); product.setName(rs.getString("name")); product.setBrand(rs.getString("brand")); product.setModel(rs.getString("model")); product.setPrice(rs.getFloat("price")); product.setPicture(rs.getString("picture")); product.setDescription(rs.getString("description")); list.add(product); } } catch (SQLException e) { e.printStackTrace(); } return list; } public int getPageMaxSize() { int n=0; Connection con=BaseDao.getConnection(); Statement st=null; ResultSet rs=null; String sql="select count(*) from product"; try { st=con.createStatement(); rs=st.executeQuery(sql); rs.next(); n=rs.getInt(1); if(n%5==0){ n=n/5; }else{ n=n/5+1; } } catch (SQLException e) { e.printStackTrace(); }finally{ BaseDao.closeAll(con, st, rs); } return n; } public int updateProduct(Product product) { Connection con=BaseDao.getConnection(); int i=0; Statement st=null; ResultSet rs=null; String sql="update product set serialNumber='"+product.getSerialNumber()+"'," + "name='"+product.getName()+"',brand='"+product.getBrand()+"',model='"+product.getModel()+"'," + "price='"+product.getPrice()+"',picture='"+product.getPicture()+"',description='"+product.getDescription()+"'" + "where productID='"+product.getProductID()+"'"; try { st=con.createStatement(); i=st.executeUpdate(sql); if(i!=0){ System.out.println("ok"); } } catch (SQLException e) { e.printStackTrace(); }finally{ BaseDao.closeAll(con, st, rs); } return i; } }