Thomas Penney
Thomas Penney

Reputation: 11

Java input to SQL to screen

Im having trouble calling and printing a stored procedure in SQLyog from Java.

What should happen is the user should input the record they want, the query show execute, and the result set should be printed to the screen. Below is the code I'm using. I also included the stored procedure code, this executes fine but maybe I am missing something.

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();

        }
    }
}

------Stored Procedure-----

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 ;

Upvotes: 1

Views: 30

Answers (0)

Related Questions