package MySQLConnection;

import java.sql.*;
import java.util.Scanner;
import static java.lang.System.out;

public class UserInput {
    public static void main(String[] args) {
        Scanner myScanner = new Scanner(System.in);
        out.println("Enter Department #:");
        int uDeptno = myScanner.nextInt();
        out.println(uDeptno);

        try {
            Connection myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/uml","root","Greenmonster12");

            //create type of statement (statement,prepared statement, callable statement)
            Statement myStmt = myConn.createStatement();

            ResultSet myRS = myStmt.executeQuery("{call user_show_dept(uDeptno)}");

            while (myRS.next()) {
                out.println(myRS.getString("deptno"+" - "));
                out.println(myRS.getString("dname"+" - "));
                out.println(myRS.getString("loc"+"."));
            }



        } catch (SQLException e) {
            e.printStackTrace();

        }
    }
}

DELIMITER $$

USE `uml`$$

DROP PROCEDURE IF EXISTS `user_show_dept`$$
CREATE DEFINER = `tpenney`@`%` PROCEDURE `user_show_dept`(uDeptno INT(2))

    BEGIN
        SELECT *
        FROM dept
        WHERE deptno=uDeptno;
    END$$

DELIMITER ;