3：Dao层和Service层

Dao层

Dao接口

public interface GoodsDao {
    /**
     * 查询所有的商品
     * @return
     * @throws SQLException
     */
    List<Goods> selectAllGoods() throws SQLException;

    /**
     * 根据id查询商品的详细信息
     * @param id
     * @return
     * @throws SQLException
     */
    Goods seelctGoodsById(Integer id) throws SQLException;
}

Dao接口实现类

public class GoodsDaoImpl implements GoodsDao {

    @Override
    public List<Goods> selectAllGoods() throws SQLException {
        List res = new ArrayList();
        Connection conn = JdbcUtil.getConnection();
        String sql = "select * from tb_goods";
        final PreparedStatement ps = conn.prepareStatement(sql);
        final ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            final int id = rs.getInt("id");
            final String name = rs.getString("name");
            final double price1 = rs.getDouble("price1");
            final double price2 = rs.getDouble("price2");
            final int amount = rs.getInt("amount");
            final Goods goods = new Goods(id, name, price1, price2, amount);
            res.add(goods);
        }
        JdbcUtil.release(rs, ps, conn);

        return res;
    }

    @Override
    public Goods seelctGoodsById(Integer id) throws SQLException {
        Goods goods = null;
        Connection conn = JdbcUtil.getConnection();
        String sql = "select * from tb_goods where id = ?";
        final PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        final ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            final String name = rs.getString("name");
            final double price1 = rs.getDouble("price1");
            final double price2 = rs.getDouble("price2");
            final int amount = rs.getInt("amount");
            goods = new Goods(id, name, price1, price2, amount);
        }
        JdbcUtil.release(rs, ps, conn);
        return goods;
    }
}

测试代码：

class GoodsDaoImplTest {

    private GoodsDao goodsDao = new GoodsDaoImpl();

    @Test
    void selectAllGoods() throws SQLException {
        final List<Goods> goodsList = goodsDao.selectAllGoods();
        goodsList.forEach(System.out::println);
    }

    @Test
    void seelctGoodsById() throws SQLException {
        final Goods goods = goodsDao.seelctGoodsById(1);
        System.out.println(goods);
    }
}

Service层

Service层接口

public interface GoodsService {
    /**
     * 查询出所有的商品
     * @return
     * @throws SQLException
     */
    List<Goods> listAllGoods() throws SQLException;

    /**
     * 查寻指定id的商品的详细信息
     * @param id
     * @return
     * @throws SQLException
     */
    Goods getGoodsById(Integer id) throws SQLException;
}

Service层实现类

public class GoodsServiceImpl implements GoodsService {
    private GoodsDao goodsDao = new GoodsDaoImpl();

    @Override
    public List<Goods> listAllGoods() throws SQLException {
        final List<Goods> goodsList = goodsDao.selectAllGoods();
        return goodsList;
    }

    @Override
    public Goods getGoodsById(Integer id) throws SQLException {
        final Goods goods = goodsDao.seelctGoodsById(id);
        return goods;
    }
}

测试代码

class GoodsServiceImplTest {

    private GoodsService goodsService = new GoodsServiceImpl();

    @Test
    void listAllGoods() throws SQLException {
        final List<Goods> goodsList = goodsService.listAllGoods();
        goodsList.forEach(System.out::println);
    }
    @Test
    void getGoodsById() throws SQLException {
        final Goods goods = goodsService.getGoodsById(1);
        System.out.println(goods);
    }
}