javaWeb通过jsp实现增删改查

package com.fyw.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import com.fyw.bean.Goods; import com.fyw.dao.GoodsDao; import com.fyw.util.JDBCUtil; public class GoodsDaoImpl implements GoodsDao{ private Connection con=null; private PreparedStatement pstm=null; private ResultSet rs=null; //全查 @Override public List<Goods> selectAll() { List<Goods> li=new ArrayList<Goods>(); try { //通过工具类连接数据库 con=JDBCUtil.getCon(); //执行sql语句 String sql="select * from `goods`"; pstm=con.prepareStatement(sql); //执行查询 rs=pstm.executeQuery(); //存值 while (rs.next()) { Goods g=new Goods(); g.setGoodsId(rs.getInt("goodsId")); g.setGoodsName(rs.getString("goodsName")); g.setPrice(rs.getDouble("price")); g.setStock(rs.getInt("stock")); g.setSupplier(rs.getString("supplier")); li.add(g); } } catch (Exception e) { e.printStackTrace(); }finally { JDBCUtil.close(con, pstm, rs); } return li; } //添加 @Override public int addGoods(Goods goods) { int n=0; try { //通过工具类连接数据库 con=JDBCUtil.getCon(); //执行sql语句 String sql="insert into goods(goodsName,price,stock,supplier) values(?,?,?,?)"; pstm=con.prepareStatement(sql); pstm.setObject(1,goods.getGoodsName()); pstm.setObject(2,goods.getPrice()); pstm.setObject(3, goods.getStock()); pstm.setObject(4, goods.getSupplier()); //执行查询 n=pstm.executeUpdate(); } catch (Exception e) { e.printStackTrace(); }finally { JDBCUtil.close(con, pstm); } return n; } //删除 @Override public int deleteGoods(int id) { int n=0; try { //通过工具类连接数据库 con=JDBCUtil.getCon(); //执行sql语句 String sql="DELETE FROM goods WHERE goodsId=?"; pstm=con.prepareStatement(sql); pstm.setObject(1,id); //执行查询 n=pstm.executeUpdate(); } catch (Exception e) { e.printStackTrace(); }finally { JDBCUtil.close(con, pstm); } return n; } //根据id查信息 @Override public Goods selectGoodsById(int id) { Goods g=null; try { //通过工具类连接数据库 con=JDBCUtil.getCon(); //执行sql语句 String sql="SELECT * from `goods` WHERE goodsId=?"; pstm=con.prepareStatement(sql); pstm.setObject(1,id); //执行查询 rs=pstm.executeQuery(); if (rs.next()) { g=new Goods(); g.setGoodsId(rs.getInt("goodsId")); g.setGoodsName(rs.getString("goodsName")); g.setPrice(rs.getDouble("price")); g.setStock(rs.getInt("stock")); g.setSupplier(rs.getString("supplier")); } } catch (Exception e) { e.printStackTrace(); }finally { JDBCUtil.close(con, pstm,rs); } return g; } //修改 @Override public int updateGoods(Goods goods) { int n=0; try { //通过工具类连接数据库 con=JDBCUtil.getCon(); //执行sql语句 String sql="UPDATE `goods` SET goodsName=?,price=?,stock=?,supplier=? where goodsId=?"; pstm=con.prepareStatement(sql); pstm.setObject(1,goods.getGoodsName()); pstm.setObject(2,goods.getPrice()); pstm.setObject(3, goods.getStock()); pstm.setObject(4, goods.getSupplier()); pstm.setObject(5, goods.getGoodsId()); //执行查询 n=pstm.executeUpdate(); } catch (Exception e) { e.printStackTrace(); }finally { JDBCUtil.close(con, pstm); } return n; } @Override public List<Goods> selectGoodsByGoodsNames(String names) { List<Goods> li=new ArrayList<Goods>(); try { //通过工具类连接数据库 con=JDBCUtil.getCon(); //执行sql语句 String sql="SELECT * FROM `goods` WHERE goodsName LIKE CONCAT('%',?,'%')"; pstm=con.prepareStatement(sql); pstm.setString(1,names); //执行查询 rs=pstm.executeQuery(); //存值 while (rs.next()) { Goods g=new Goods(); g.setGoodsId(rs.getInt("goodsId")); g.setGoodsName(rs.getString("goodsName")); g.setPrice(rs.getDouble("price")); g.setStock(rs.getInt("stock")); g.setSupplier(rs.getString("supplier")); li.add(g); } } catch (Exception e) { e.printStackTrace(); }finally { JDBCUtil.close(con, pstm, rs); } return li; } }