package demo.data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DAL {

	static String connectURL = "jdbc:mysql://localhost:3306/northwind?useSSL=false";

	public static List<Shipper> listAllShippers() {

		List<Shipper> shippers = new ArrayList<>();
		try (Connection con = DriverManager.getConnection(connectURL, "root", "password")) {
			try (PreparedStatement pst = con.prepareStatement("select * from shippers")) {
				try (ResultSet rs = pst.executeQuery()) {
					while (rs.next()) {
						shippers.add(new Shipper(rs.getInt("ShipperID"), rs.getString("CompanyName"),
								rs.getString("Phone")));
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return shippers;
	}

	public static int addShipper(String compName, String phone) {
		int post = 0;
		try (Connection con = DriverManager.getConnection(connectURL, "root", "password")) {
			try (PreparedStatement pst = con
					.prepareStatement("insert into shippers(CompanyName, Phone) values (?,?)")) {
				pst.setString(1, compName);
				pst.setString(2, phone);
				post = pst.executeUpdate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return post;
	}
}