Java超市账单管理系统.docx

Java北大青鸟（天津）第二单元超市账单管理系统项目整体：项目dao包:项目entity包: 项目filter包:项目servlet包:项目test包:项目jar包:项目jsp:package dao;/* * Dao父类 */import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class BaseDao / 连接数据库public Connection getConnection() throws SQLException try Class.forName(oracle.jdbc.OracleDriver); catch (ClassNotFoundException e) / TODO Auto-generated catch blocke.printStackTrace();/ 加载驱动Connection connection = DriverManager.getConnection(jdbc:oracle:thin:localhost:1521:orcl, scott, lch920211);return connection;/增删改支持方法public int update(String sql,Object params) throws SQLExceptionConnection connection = getConnection();PreparedStatement statement = connection.prepareStatement(sql);for(int i = 0;iparams.length;i+)statement.setObject(i+1, paramsi);int i = statement.executeUpdate();connection.close();return i;/查询单个支持方法public Object query(String sql,Object params,RowMapper rowMapper) throws SQLExceptionConnection connection = getConnection();PreparedStatement statement = connection.prepareStatement(sql);for(int i = 0;iparams.length;i+)statement.setObject(i+1, paramsi);ResultSet resultSet = statement.executeQuery();Object object = null;if(resultSet.next()object = rowMapper.mapRow(resultSet); connection.close();return object;package dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import entity.Bill;import entity.Provider;import entity.User;/* * 账单Dao * * author li * */public class BillDao extends BaseDao/ 映射器private RowMapper rowMapper = new RowMapper() Overridepublic Object mapRow(ResultSet resultSet) throws SQLException Bill bill = new Bill(resultSet.getInt(1), resultSet.getDouble(2),resultSet.getString(3), resultSet.getInt(4),resultSet.getString(5), resultSet.getString(6),resultSet.getDate(7),resultSet.getString(8),resultSet.getString(9);return bill;/查供应商名称public List loadProviderName() throws SQLException Connection connection = getConnection();String sql = select * from market_provider;PreparedStatement statement = connection.prepareStatement(sql);ResultSet resultSet = statement.executeQuery();ArrayList list = new ArrayList();while (resultSet.next() Provider provider = new Provider(resultSet.getInt(1),resultSet.getString(2), resultSet.getString(3),resultSet.getString(4), resultSet.getString(5),resultSet.getString(6), resultSet.getString(7);list.add(provider);connection.close();return list;/ 查所有public List loadAll() throws SQLException Connection connection = getConnection();String sql = select * from market_bill;PreparedStatement statement = connection.prepareStatement(sql);ResultSet resultSet = statement.executeQuery();ArrayList list = new ArrayList();while (resultSet.next() Bill bill = new Bill(resultSet.getInt(1), resultSet.getDouble(2),resultSet.getString(3), resultSet.getInt(4),resultSet.getString(5), resultSet.getString(6),resultSet.getDate(7), resultSet.getString(8),resultSet.getString(9);list.add(bill);connection.close();return list;/增public int save(Bill bill) throws SQLException String sql = insert into market_bill(id,price,unit,account,goodsname,goodsdescription,billdate,providername,ispayed) + values (seq.nextval,?,?,?,?,?,?,?,?);Object params = bill.getPrice(),bill.getUnit(),bill.getAccount(),bill.getGoodsname(),bill.getGoodsdescription(),new java.sql.Timestamp(bill.getBilldate().getTime(),bill.getProvidername(),bill.getIspayed();return update(sql, params); / 删 public int delete(String id) throws SQLException String sql = delete market_bill where id=?;Object params = id;return update(sql, params); / 改 public int modify(String id,Bill bill) throws SQLException String sql = update market_bill set price=?,unit=?,account=?,goodsname=?,goodsdescription=?,providername=?,ispayed=? where id=?;Object params = bill.getPrice(),bill.getUnit(),bill.getAccount(),bill.getGoodsname(),bill.getGoodsdescription(),bill.getProvidername(),bill.getIspayed(),id;return update(sql, params); / 查单个 public Object load(String id) throws SQLExceptionString sql = select * from market_bill where id=?;Object params=id;return query(sql,params,rowMapper); / 模糊查询 public List likeQuery(String goodsname,String ispayed) throws SQLException Connection connection = getConnection(); String sql = select * from market_bill where goodsname like ? and ispayed like ?; PreparedStatement statement = connection.prepareStatement(sql);statement.setObject(1, % +goodsname+ %);statement.setObject(2, %+ispayed+%);ResultSet resultSet = statement.executeQuery(); ArrayList list = new ArrayList(); while (resultSet.next() Bill bill = new Bill(resultSet.getInt(1), resultSet.getDouble(2),resultSet.getString(3), resultSet.getInt(4),resultSet.getString(5), resultSet.getString(6),resultSet.getDate(7), resultSet.getString(8),resultSet.getString(9);list.add(bill); connection.close(); return list; package dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import entity.Provider;import entity.User;/* * 供应商DAO * * author li * */public class ProviderDao extends BaseDao / 映射器private RowMapper rowMapper = new RowMapper() Overridepublic Object mapRow(ResultSet resultSet) throws SQLException Provider provider = new Provider(resultSet.getInt(1),resultSet.getString(2), resultSet.getString(3),resultSet.getString(4), resultSet.getString(5),resultSet.getString(6), resultSet.getString(7);return provider;/ 增public int save(Provider provider) throws SQLException String sql = insert into market_provider(providerid,providername,providerdescription,linkman,providerphone,provideradd,providerfax)+ values (seq.nextval,?,?,?,?,?,?);Object params = provider.getProvidername(),provider.getProviderdescription(), provider.getLinkman(),provider.getProviderphone(), provider.getProvideradd(),provider.getProviderfax() ;return update(sql, params);/ 删public int delete(String id) throws SQLException String sql = delete market_provider where providerid=?;Object params = id ;return update(sql, params);/ 改public int modify(String id, Provider provider) throws SQLException String sql = update market_provider set providername=?,providerdescription=?,linkman=?,providerphone=?,provideradd=?,providerfax=? where providerid=?;Object params = provider.getProvidername(),provider.getProviderdescription(), provider.getLinkman(),provider.getProviderphone(), provider.getProvideradd(),provider.getProviderfax(), id ;return update(sql, params);/ 查单个public Object load(String id) throws SQLException String sql = select * from market_provider where providerid=?;Object params = id ;return query(sql, params, rowMapper);/ 模糊查询public List likeQuery(String providername,String providerdesc) throws SQLException Connection connection = getConnection();String sql = select * from market_provider where providername like ? and providerdescription like ?;PreparedStatement statement = connection.prepareStatement(sql);statement.setObject(1, % + providername + %);statement.setObject(2, % + providerdesc + %);ResultSet resultSet = statement.executeQuery();ArrayList list = new ArrayList();while (resultSet.next() Provider provider = new Provider(resultSet.getInt(1),resultSet.getString(2), resultSet.getString(3),resultSet.getString(4), resultSet.getString(5),resultSet.getString(6), resultSet.getString(7);list.add(provider);connection.close();return list;/ 查所有public List loadAll() throws SQLException Connection connection = getConnection();String sql = select * from market_provider;PreparedStatement statement = connection.prepareStatement(sql);ResultSet resultSet = statement.executeQuery();ArrayList list = new ArrayList();while (resultSet.next() Provider provider = new Provider(resultSet.getInt(1),resultSet.getString(2), resultSet.getString(3),resultSet.getString(4), resultSet.getString(5),resultSet.getString(6), resultSet.getString(7);list.add(provider);connection.close();return list;package dao;import java.sql.ResultSet;import java.sql.SQLException;/* * 映射器 * author li * */public interface RowMapper /把查询结果映射到实体对象里 public abstract Object mapRow(ResultSet resultSet) throws SQLException;package dao;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;import entity.User;/* * 用户Dao * * author li * */public class UserDao extends BaseDao / 映射器private RowMapper rowMapper = new RowMapper() Overridepublic Object mapRow(ResultSet resultSet) throws SQLException User user = new User(resultSet.getInt(1), resultSet.getString(2),resultSet.getString(3), resultSet.getString(4),resultSet.getString(5), resultSet.getString(6),resultSet.getString(7), resultSet.getString(8);return user;/ 登录验证public User checkLogin(String name, String pwd)throws SQLException Connection connection = getConnection();String sql = select * from market_user where username=+name+;PreparedStatement statement = connection.prepareStatement(sql);ResultSet resultSet = statement.executeQuery();User user=null; if(resultSet.next() user = new User(resultSet.getInt(1), resultSet.getString(2),resultSet.getString(3), resultSet.getString(4),resultSet.getString(5), resultSet.getString(6),resultSet.getString(7), resultSet.getString(8); connection.close();return user;/ 增public int save(User user) throws SQLException String sql = insert into market_user(id,username,password,gender,age,phone,address,role) + values (seq.nextval,?,?,?,?,?,?,?);Object params = user.getUsername(), user.getPassword(),user.getGender(), user.getAge(), user.getPhone(),user.getAddress(), user.getRole() ;return update(sql, params);/ 删public int delete(String id) throws SQLException String sql = delete market_user where id=?;Object params = id;return update(sql, params);/ 改 public int modify(String id,User user) throws SQLException String sql = update market_user set username=?,gender=?,age=?,phone=?,address=? where id=?;Object params = user.getUsername(),user.getGender(),user.getAge(),user.getPhone(),user.getAddress(),id;return update(sql, params); /修改密码 public int modifyPwd(String id,String password,User user) throws Exception int id1 = Integer.parseInt(id);Connection connection = getConnection(); String sql1 = select * from market_user where id=+id1+;PreparedStatement statement = connection.prepareStatement(sql1);ResultSet resultSet = statement.executeQuery();int i = 1;String rs = null;if(resultSet.next() rs = resultSet.getString(3); if(password.equals(rs) String sql = update market_user set password=? where id=?; Object params = user.getPassword(),id; return update(sql,params); else return i; / 查单个 public Object load(String id) throws SQLExceptionString sql = select * from market_user where id=?;Object params=id;return query(sql,params,rowMapper); / 模糊查询 public List likeQuery(String username) throws SQLException Connection connection = getConnection(); String sql = select * from market_user where username like ?; PreparedStatement statement = connection.prepareStatement(sql);statement.setObject(1, % +username+ %);ResultSet resultSet = statement.executeQuery(); ArrayList list = new ArrayList(); while (resultSet.next() User user = new User(resultSet.getInt(1), resultSet.getString(2),resultSet.getString(3), resultSet.getString(4),resultSet.getString(5), resultSet.getString(6),resultSet.getString(7), resultSet.getString(8);list.add(user); connection.close(); return list; / 查所有public List loadAll() throws SQLException Connection connection = getConnection();String sql = select * from market_user;PreparedStatement statement = connection.prepareStatement(sql);ResultSet resultSet = statement.executeQuery();ArrayList list = new ArrayList();while (resultSet.next() User user = new User(resultSet.getInt(1), resultSet.getString(2),resultSet.getString(3), resultSet.getString(4),resultSet.getString(5), resultSet.getString(6),resultSet.getString(7), resultSet.getString(8);list.add(user);connection.close();return list;package entity;import java.util.Date;/* * 账单实体类 * * author li * */public class Bill private int id;/ 账单编号private double price;/ 交易金额private String unit;/ 交易单位private int account;/ 数量private String goodsname;/ 商品名称private String goodsdescription;/ 商品描述private Date billdate;/ 账单日期private String providername;/ 供应商名称private String ispayed;/ 是否付款public int getId() return id;public void setId(int id) this.id = id;public double getPrice() return price;public void setPrice(double price) this.price = price;public String getUnit() return unit;public void setUnit(String unit) this.unit = unit;public int getAccount() return account;public void setAccount(int account) this.account = account;public String getGoodsname() return goodsname;public void setGoodsname(String goodsname) this.goodsname = goodsname;public String getGoodsdescription() return goodsdescription;public void setGoodsdescription(String goodsdescription) this.goodsdescription = goodsdescription;public Date getBilldate() return billdate;public void setBilldate(Date billdate) this.billdate = billdate;public String getProvidername() return providername;public void setProvidername(String providername) vidername = providername;public String getIspayed() return ispayed;public void setIspayed(String ispayed) this.ispayed = ispayed;public Bill(int id, double price, String unit, int account,String goodsname, String goodsdescription, Date billdate,String providername, String ispayed) super();this.id = id;this.price = price;this.unit = unit;this.account = account;this.goodsname = goodsname;this.goodsdescription = goodsdescription;this.billdate = billdate;vidername = providername;this.ispayed = ispayed;public Bill() super();package entity;/* * 供应商实体类 * * author li * */public class Provider private int providerId;/ 供应商编号private String providername;/ 供应商名称private String providerdescription;/ 供应商描述private String linkman;/ 联系人private String providerphone;/ 供应商电话private String provideradd;/ 供应商地址private String providerfax;/ 供应商电话public int getProviderId() return providerId;public void setProviderId(int providerId) viderId = providerId;public String getProvidername() return providername;public void setProviderame(String providername) vidername = providername;public String getProviderdescription() return providerdescription;public void setProviderdescription(String providerdescription) viderdescription = providerdescription;public String getLinkman() return linkman;public void setLinkman(String linkman) this.linkman = linkman;public String getProviderphone() return providerphone;public void setProviderphone(String providerphone) viderphone = providerphone;public String getProvideradd() return provideradd;public void setProvideradd(String provideradd) videradd = provideradd;public String getProviderfax() return providerfax;public void setProvide