基于jsp+mysql的二手物品交易系统

package dao; import java.sql.SQLException; import java.util.Date; import java.util.List; import bean.AdminAD; import bean.AdminUser; import bean.BackColor; import bean.Goods; import bean.Operation; import bean.Order; import bean.User; import bean.Logo; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import utils.JDBCUtils; import utils.MD5; public class DaoImpl implements Dao { @Override public User getByName(String name) { try { QueryRunner qn = new QueryRunner(JDBCUtils.getDataSource()); String sql = "select * from sg_user where username=?"; return qn.query(sql, new BeanHandler<User>(User.class), name); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("数据库连接失败！getByName"); } } @Override public void saveUser(User u) { try { QueryRunner qn = new QueryRunner(JDBCUtils.getDataSource()); String sql = "insert into sg_user (username,password,email, realname,sex,xueyuan, grade,clazz,tel, qq,weixin,quanxian)" + "values(?,?,?,?,?,?,?,?,?,?,?,?);"; Object[] params = {u.getUsername(),MD5.GetMD5Code(u.getPassword()),u.getEmail(),u.getRealname(),u.getSex(), u.getXueyuan(),u.getGrade(),u.getClazz(),u.getTel(),u.getQq(),u.getWeixin(),u.getQuanxian()}; qn.update(sql, params); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("数据库连接失败!saveUser"); } } @Override public List<User> getAll() { try { QueryRunner qn = new QueryRunner(JDBCUtils.getDataSource()); String sql = "select * from sg_user"; return qn.query(sql, new BeanListHandler<User>(User.class)); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("数据库连接失败！getAll"); } } @Override public void saveGood(Goods good) { QueryRunner qn = new QueryRunner(JDBCUtils.getDataSource()); try { String sql = "insert into sg_goods (title,leibie,xinjiu,price,oldPrice,contactName,contactTel,img1,img2,img3,my_describe,my_owner,bigleibie,smallleibie,isfabu) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"; Object[] params = {good.getTitle(),good.getLeibie(),good.getXinjiu(),good.getPrice(),good.getOldPrice(),good.getContactName(),good.getContactTel(),good.getImg1(), good.getImg2(),good.getImg3(),good.getMy_describe(),good.getOwner(),good.getBigleibie(),good.getSmallleibie(),good.getIsfabu()}; qn.update(sql, params); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("数据库连接失败！saveGood"); } } @Override public List<Goods> getGoodsByBigLeibie(int bigLeibie) { try { QueryRunner qn = new QueryRunner(JDBCUtils.getDataSource()); String sql = "select * from sg_goods where bigleibie=? and isfabu=1 and canuse=1 limit 0,4;"; return qn.query(sql, new BeanListHandler<Goods>(Goods.class), bigLeibie); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("数据库连接失败！getGoodsByBigLeibie"); } } @Override public Goods getGoodById(int id) { try { QueryRunner qn = new QueryRunner(JDBCUtils.getDataSource()); String sql = "select * from sg_goods where num=?"; return qn.query(sql, new BeanHandler<Goods>(Goods.class), id); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("数据库连接失败！getGoodById"); } } @Override public AdminUser getAaminUserByUsernane(String username) { try { QueryRunner qn = new QueryRunner(JDBCUtils.getDataSource()); String sql = "select * from admin_user where username=?;"; return qn.query(sql, new BeanHandler<AdminUser>(AdminUser.class), username); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("数据库连接失败！getAdminUserByUsername"); } } @Override public List<AdminAD> getAllAD() { try { QueryRunner qn = new QueryRunner(JDBCUtils.getDataSource()); String sql = "select * from admin_ad limit 0,5;"; return qn.query(sql, new BeanListHandler<AdminAD>(AdminAD.class)); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("数据库连接失败！getAllAD"); } } @Override public void saveAD(String imgName, String imgURL, int imgNum ,String imgtitle) { QueryRunner qn = new QueryRunner(JDBCUtils.getDataSource()); try { String sql = "update admin_ad set imgname=? , imgurl = ? ,imgtitle = ? where num = ?;"; qn.update(sql,imgName,imgURL,imgtitle,imgNum); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("数据库连接失败！saveAD"); } } @Override public void updateUserLogin(int snum, int svalue,int admin_user ,String time) { QueryRunner qn = new QueryRunner(JDBCUtils.getDataSource()); try { String sql = "update sg_user set canlogin=? where num = ? ;"; qn.update(sql, svalue,snum); sql = "insert into admin_operate (admin_user,leibie,content,content2,time) values(?,?,?,?,?);"; qn.update(sql, admin_user,svalue+3,snum,null,time); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("数据库连接失败！updateUserLogin"); } } @Override public void deleteUserByNum(int snum,int admin_user ,String time) { QueryRunner qn = new QueryRunner(JDBCUtils.getDataSource()); try { String sql = "select * from sg_user where num=?"; User u = qn.query(sql, new BeanHandler<User>(User.class), snum); String sname = u.getUsername(); sql = "delete from sg_user where num = ? ;"; qn.update(sql,snum); sql = "insert into admin_operate (admin_user,leibie,content,content2,time) values(?,?,?,?,?);"; qn.update(sql, admin_user,5,sname,null,time); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("数据库连接失败！deleteUserByNum"); } } @Override public void updateUserPassByNum(int snum, String password,int admin_user ,String time) { QueryRunner qn = new QueryRunner(JDBCUtils.getDataSource()); try { String sql = "update sg_user set password=? where num = ? ;"; qn.update(sql, MD5.GetMD5Code(password),snum); sql = "insert into admin_operate (admin_user,leibie,content,content2,time) values(?,?,?,?,?);"; qn.update(sql, admin_user,2,snum,null,time); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("数据库连接失败！updateUserPassByNum"); } } @Override public List<AdminUser> getAllAdminUser() { try { QueryRunner qn = new QueryRunner(JDBCUtils.getDataSource()); String sql = "select * from admin_user;"; return qn.query(sql, new BeanListHandler<AdminUser>(AdminUser.class)); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("数据库连接失败！getAllAdminUser"); } } @Override public void updateAdminUserIsUsingByNum(int num, int value,int admin_user ,String time) { QueryRunner qn = new QueryRunner(JDBCUtils.getDataSource()); try { String sql = "update admin_user set isusing=? where num = ? ;"; qn.update(sql, value,num); sql = "insert into admin_operate (admin_user,leibie,content,content2,time) values(?,?,?,?,?);"; qn.update(sql, admin_user,value+10,num,null,time); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("数据库连接失败！updateAdminUserIsUsingByNum"); }