Java và MySQL – OrderItem DAO

Model

package binh.dev.data.model;

import binh.dev.data.dao.DatabaseDao;

public class OrderItem {

    private int id;
    private int quantity;
    private double price;
    private int orderId;
    private int productId;

    public OrderItem(int quantity, double price, int orderId, int productId) {
        this.quantity = quantity;
        this.price = price;
        this.orderId = orderId;
        this.productId = productId;
    }

    public OrderItem(int id, int quantity, double price, int orderId, int productId) {
        this.id = id;
        this.quantity = quantity;
        this.price = price;
        this.orderId = orderId;
        this.productId = productId;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getQuantity() {
        return quantity;
    }

    public void setQuantity(int quantity) {
        this.quantity = quantity;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public int getOrderId() {
        return orderId;
    }

    public void setOrderId(int orderId) {
        this.orderId = orderId;
    }

    public int getProductId() {
        return productId;
    }

    public void setProductId(int productId) {
        this.productId = productId;
    }
    
    public Product getProduct(){
        return DatabaseDao.getInstance().getProductDao().find(this.productId);
    }
    
    public Order getOrder(){
        return DatabaseDao.getInstance().getOrderDao().find(this.orderId);
    }
}

DAO Interface

package binh.dev.data.dao;

import java.util.List;
import binh.dev.data.model.OrderItem;

public interface OrderItemDao {

    public boolean insert(OrderItem orderItem);

    public boolean update(OrderItem orderItem);

    public boolean delete(int id);

    public OrderItem find(int id);

    public List<OrderItem> findAll();

    public List<OrderItem> findByOder(int id);

    public List<OrderItem> findByProduct(int id);

}

DAO Implement

package binh.dev.data.impl;

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 binh.dev.data.dao.OrderItemDao;
import binh.dev.data.driver.MySQLDriver;
import binh.dev.data.model.OrderItem;

public class OrderItemImpl implements OrderItemDao {
	Connection con = MySQLDriver.getInstance().getConnection();

	@Override
	public boolean insert(OrderItem orderItem) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO ORDER_ITEMS(ID, QUANTITY, PRICE, ORDER_ID, PRODUCT_ID) VALUES(NULL, ?, ?, ?, ?)";
		try {
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setInt(1, orderItem.getQuantity());
			stmt.setDouble(2, orderItem.getPrice());
			stmt.setInt(3, orderItem.getOrderId());
			stmt.setInt(4, orderItem.getProductId());
			
			stmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public boolean update(OrderItem orderItem) {
		// TODO Auto-generated method stub
		String sql = "UPDATE ORDER_ITEMS SET quantity = ?, price = ?, order_id = ?, product_id = ? WHERE id = ?";
		try {
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setInt(1,orderItem.getQuantity());
			stmt.setDouble(2, orderItem.getPrice());
			stmt.setInt(3, orderItem.getOrderId());
			stmt.setInt(4, orderItem.getProductId());
			return stmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public boolean delete(int id) {
		// TODO Auto-generated method stub
		String sql = "DELETE FROM ORDER-ITEMS WHERE ID = ?";
		try {
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setInt(1, id);
			
			return stmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public OrderItem find(int id) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM ORDER-ITEMS" ;
		try {
			PreparedStatement stmt = con.prepareStatement(sql);
			
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				int quantity = rs.getInt("quantity");
				double price = rs.getDouble("price");
				int orderId = rs.getInt("order_id");
				int productId = rs.getInt("product_id");
				
				return new OrderItem(quantity, price, orderId, productId);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<OrderItem> findAll() {
		// TODO Auto-generated method stub
		List<OrderItem> orList = new ArrayList<>();
		String sql = "SELECT * FROM CATEGORIES" ;
		try {
			PreparedStatement stmt = con.prepareStatement(sql);
			
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("id");
				int quantity = rs.getInt("quantity");
				double price = rs.getDouble("price");
				int orderId = rs.getInt("order_id");
				int productId = rs.getInt("product_id");
				
				orList.add(new OrderItem(quantity, price, orderId, productId));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return orList;
	}

	@Override
	public List<OrderItem> findByOder(int orderId) {
	List<OrderItem> orderItemList = new ArrayList<>();
        String sql = "SELECT * FROM ORDER_ITEMS WHERE order_id = ?";
        try {
            PreparedStatement stmt = con.prepareStatement(sql);
            stmt.setInt(1, orderId);

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                int quantity = rs.getInt("quantity");
                double price = rs.getDouble("price");
                int productId = rs.getInt("product_id");
                orderItemList.add( new OrderItem(id, quantity, price, orderId, productId));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return orderItemList;
	}

	@Override
	public List<OrderItem> findByProduct(int id) {
		// TODO Auto-generated method stub
		return null;
	}

}