超市管理项目dao层实现类

订单详情表

package com.qy38.edu.One.XiangMu.dao.impl;

import com.qy38.edu.One.XiangMu.dao.DetailsDao;
import com.qy38.edu.One.XiangMu.entity.Details;
import com.qy38.edu.One.day_12_3_basic.jdbcs.DbUtils;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

public class DetailsDaoImpl implements DetailsDao {
    private QueryRunner queryRunner = new QueryRunner();
    /**
     * 添加
     * @param details
     * @return
     */
    @Override
    public int insertDetails(Details details) throws SQLException, ClassNotFoundException {
        String sql = "INSERT INTO t_tt (form,goods,sum)VALUES(?,?,?)";
        int update = DbUtils.update(sql,
                details.getForm(),
                details.getGoods(),
                details.getSum());
        return update;
    }

    /**
     * 删除
     * @param detailsId
     * @return
     */
    @Override
    public int deleteDetails(int detailsId) throws SQLException, ClassNotFoundException {
        String sql = "DELETE FROM t_tt WHERE id=?";
        int update = queryRunner.update(DbUtils.getConn(),sql,detailsId);
        return update;
    }

    /**
     * 修改
     * @param details
     * @return
     */
    @Override
    public int updateDetails(Details details) throws SQLException, ClassNotFoundException {
        String sql = "UPDATE t_tt SET form=?,goods=?,sum=? WHERE id=?";
        int update = DbUtils.update(sql,
                details.getForm(),
                details.getGoods(),
                details.getSum(),
                details.getId());
        return update;
    }

    /**
     * 查看
     * @return
     */
    @Override
    public List<Details> selectAllDetails() throws SQLException, ClassNotFoundException {
        String sql = "SELECT * FROM t_tt";
        BeanListHandler<Details> beanListHandler = new
                BeanListHandler<>(Details.class);
        List<Details> query = queryRunner.query(DbUtils.getConn(), sql, beanListHandler);
        return query;
    }

    /**
     * 查看指定
     * @param detailsId
     * @return
     */
    @Override
    public Details selectById(int detailsId) throws SQLException, ClassNotFoundException {
        String sql = "SELECT * FROM t_tt WHERE id=?";
        BeanHandler<Details> beanHandler = new BeanHandler<>(Details.class,
                new BasicRowProcessor(new GenerousBeanProcessor()));
        Details query = queryRunner.query(DbUtils.getConn(), sql, beanHandler, detailsId);
        return query;
    }
}

 

 

商品详情表

package com.qy38.edu.One.XiangMu.dao.impl;

import com.qy38.edu.One.XiangMu.dao.GoodsDao;
import com.qy38.edu.One.XiangMu.entity.Goods;
import com.qy38.edu.One.day_12_3_basic.jdbcs.DbUtils;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

public class GoodsDaoImpl implements GoodsDao {
    private QueryRunner queryRunner = new QueryRunner();
    /**
     * 添加
     * @param goods
     * @return
     */
    @Override
    public int insertGoods(Goods goods) throws SQLException, ClassNotFoundException {
        String sql = "INSERT INTO t_st (name,type,sum,price,states,discount,creat,modify)VALUES(?,?,?,?,?,?,null,now())";
        int update = DbUtils.update(sql,
                goods.getName(),
                goods.getType(),
                goods.getSum(),
                goods.getPrice(),
                goods.getStates(),
                goods.getDiscount());
        return update;
    }

    /**
     * 删除
     * @param goodsId
     * @return
     */
    @Override
    public int deleteGoods(int goodsId) throws SQLException, ClassNotFoundException {
        String sql = "DELETE FROM t_st WHERE id=?";
        int update = queryRunner.update(DbUtils.getConn(), sql, goodsId);
        return update;
    }

    /**
     * 修改
     * @param goods
     * @return
     */
    @Override
    public int updateGoods(Goods goods) throws SQLException, ClassNotFoundException {
        String sql = "UPDATE t_st SET name=?,type=?,sum=?,price=?,states=?,discount=? WHERE id=?";
        int update = DbUtils.update(sql,
                goods.getName(),
                goods.getType(),
                goods.getSum(),
                goods.getPrice(),
                goods.getStates(),
                goods.getDiscount(),
                goods.getId());
        return update;
    }

    /**
     * 查看
     * @return
     */
    @Override
    public List<Goods> selectAllGoods() throws SQLException, ClassNotFoundException {
        String sql = "SELECT * FROM t_st;";
        BeanListHandler<Goods> beanListHandler =
                new BeanListHandler<>(Goods.class);
        List<Goods> query = queryRunner.query(DbUtils.getConn(),sql,beanListHandler);
        return query;
    }

    /**
     * 查看指定
     * @param goodsId
     * @return
     */
    @Override
    public Goods selectById(int goodsId) throws SQLException, ClassNotFoundException {
        String sql = "SELECT * FROM t_st WHERE id=?";
        BeanHandler<Goods> beanHandler = new BeanHandler<>(Goods.class,
                new BasicRowProcessor(new GenerousBeanProcessor()));
        Goods query = queryRunner.query(DbUtils.getConn(), sql, beanHandler, goodsId);

        return query;
    }

    /**
     * 查看子类
     * @return
     * @throws SQLException
     */
    @Override
    public List<Goods> selectZiLei(int type) throws SQLException, ClassNotFoundException {
        String sql = "SELECT id,name,type,sum,price,states,discount,creat,modify FROM t_st  WHERE type=?;";
        BeanListHandler<Goods> beanListHandler =
                new BeanListHandler<>(Goods.class);
        List<Goods> query = queryRunner.query(DbUtils.getConn(),sql,beanListHandler,type);
        return query;
    }

    /**
     * 更新商品表
     * @param goodsId
     * @param BuySum
     * @return
     * @throws SQLException
     */
    @Override
    public int updateStore(int goodsId, int BuySum) throws SQLException, ClassNotFoundException {
        String sql = "UPDATE t_st SET sum=sum-? WHERE id=?";
        int update = queryRunner.update(DbUtils.getConn(),
                sql,
                BuySum,
                goodsId);
        return update;
    }
}

 

 

订单信息表

package com.qy38.edu.One.XiangMu.dao.impl;

import com.qy38.edu.One.XiangMu.dao.OrderDao;
import com.qy38.edu.One.XiangMu.entity.Order;
import com.qy38.edu.One.day_12_3_basic.jdbcs.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

public class OrderDaoImpl implements OrderDao {
    private QueryRunner queryRunner = new QueryRunner();
    @Override//添加
    public int insertOrder(Order order) throws SQLException, ClassNotFoundException {
        String sql = "INSERT INTO t_dt (user,money,time,pay)VALUES(?,?,now(),?)";
        int update = DbUtils.update(sql,
                order.getUser(),
                order.getMoney(),
                order.getPay());
        return update;
    }

    @Override//删除
    public int deleteOrder(int orderId) throws SQLException, ClassNotFoundException {
        String sql ="DELETE FROM t_dt WHERE id=?";
        int update = queryRunner.update(DbUtils.getConn(), sql, orderId);
        return update;
    }

    @Override//修改
    public int updateOrder(Order order) throws SQLException, ClassNotFoundException {
        String sql = "UPDATE t_dt SET money=?,time=now(),pay=? WHERE id=?";
        int update = DbUtils.update(sql,
                order.getMoney(),
                order.getPay(),
                order.getUser());
        return update;
    }

    @Override//查看
    public List<Order> selectAllOrder() throws SQLException, ClassNotFoundException {
        String sql = "SELECT * FROM t_dt";
        BeanListHandler<Order> orderBeanListHandler = new BeanListHandler<>(Order.class);
        List<Order> query = queryRunner.query(DbUtils.getConn(), sql, orderBeanListHandler);
        return query;
    }

    /**
     * 更新信息表
     * @param user
     * @param money
     * @param time
     * @param pay
     * @return
     * @throws SQLException
     */
    @Override
    public int updateStore(int user, double money, Date time, int pay) throws SQLException, ClassNotFoundException {
        String sql = "UPDATE t_dt SET user=?,money=?,time=?,pay=? WHERE id=?";
        int update = DbUtils.update(sql,
                user,money,time,pay);
        return update;
    }
}

 

会员表

package com.qy38.edu.One.XiangMu.dao.impl;

import com.qy38.edu.One.XiangMu.dao.VipDao;
import com.qy38.edu.One.XiangMu.entity.Vip;
import com.qy38.edu.One.day_12_3_basic.jdbcs.DbUtils;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

public class VipDaoImpl implements VipDao {
private QueryRunner queryRunner = new QueryRunner();

    /**
     * 添加
     * @param vip
     * @return
     */
    @Override
    public int insertVip(Vip vip) throws SQLException, ClassNotFoundException {
        String sql = "INSERT INTO t_at (name,password,phone,integral,balance,creat,modify)VALUES(?,?,?,?,?,now(),now())";
        int update = DbUtils.update(sql,
                vip.getName(),
                vip.getPassword(),
                vip.getPhone(),
                vip.getIntegral(),
                vip.getBalance());
        return update;
    }

    /**
     * 删除
     * @param vipId
     * @return
     */
    @Override
    public int deleteVip(int vipId) throws SQLException, ClassNotFoundException {
        String sql = "DELETE FROM t_at WHERE id=?";
        int update = queryRunner.update(DbUtils.getConn(), sql, vipId);
        return update;
    }

    /**
     * 修改
     * @param vip
     * @return
     */
    @Override
    public int updateVip(Vip vip) throws SQLException, ClassNotFoundException {
        String sql = "UPDATE t_at SET name=?,password=?,phone=?,integral=?,balance=? WHERE id=?";
        int update = DbUtils.update(sql,
                vip.getName(),
                vip.getPassword(),
                vip.getPhone(),
                vip.getIntegral(),
                vip.getBalance(),
                vip.getId());
        return update;
    }

    /**
     * 查看
     * @return
     */
    @Override
    public List<Vip> selectAllVip() throws SQLException, ClassNotFoundException {
        String sql = "SELECT * FROM t_at;";
        BeanListHandler<Vip> beanListHandler =
                new BeanListHandler<>(Vip.class);
        List<Vip> query = queryRunner.query(DbUtils.getConn(),sql,beanListHandler);
        return query;
    }

    /**
     * 查看指定
     * @param vipId
     * @return
     */
    @Override
    public Vip selectById(int vipId) throws SQLException, ClassNotFoundException {
        String sql = "SELECT * FROM t_at WHERE id=?";
        BeanHandler<Vip> beanHandler = new BeanHandler<>(Vip.class,
                new BasicRowProcessor(new GenerousBeanProcessor()));
        Vip query = queryRunner.query(DbUtils.getConn(), sql, beanHandler, vipId);
        return query;
    }


    /**
     * 用户输入对比
     * @param vipId
     * @param password
     * @return
     */
    @Override
    public Vip selectByID(int vipId, String password) throws SQLException, ClassNotFoundException {
        String sql = "SELECT * FROM t_at WHERE id=? AND password=?";
        BeanHandler<Vip> beanHandler = new BeanHandler<>(Vip.class,
                new BasicRowProcessor(new GenerousBeanProcessor()));
        Vip query = queryRunner.query(DbUtils.getConn(),
                sql,
                beanHandler,
                vipId,
                password);
        return query;
    }

    /**
     * 更新VIP表
     * @param vipId
     * @param integral
     * @param balance
     * @return
     */
    @Override
    public int updateStore(int vipId, double integral, double balance) throws SQLException, ClassNotFoundException {
        String sql = "UPDATE t_at SET integral=integral+?,balance=balance-? WHERE id=?";
        int update = queryRunner.update(DbUtils.getConn(), sql,
                balance,
                balance,
                vipId);
        return update;
    }
}