Geek Answers Handbook

How to call a stored procedure in JDBC

For homework, I need to create a pl / sql stored procedure to add a factorized item to the database

CREATE OR REPLACE PROCEDURE ADDFACULTYDEPTSAL (facid IN NUMBER, facname IN VARCHAR, depID IN NUMBER) AS sal NUMBER; BEGIN CALCSALDEPT(depID, sal); IF sal >= 50000 THEN sal := sal*.9; ELSE IF sal >= 30000 THEN sal := sal*.8; END IF; END IF; INSERT INTO FACULTY(fid, fname, deptid, salary) VALUES(facid, facname, depID, sal); END ADDFACULTYDEPTSAL;

Having done this, I need to make a java call for the specified procedure, from which I am tired:

 Statement stmt = dbConnection.createStatement(); String in; if(a == 1){ in = "ADDFACULTYDEPTSAL(" + fid.getText() + "','" + fname.getText() + "','" + did.getText() + "')"; } else { in = "ADDFACULTYUNISAL(" + fid.getText() + "','" + fname.getText() + "','" + did.getText() + "')"; } stmt.executeQuery(in);

I have above in a catch try block that keeps throwing an error. I tried several options in the "in" line depending on what I saw on other sites: in = "{call ADDFACULTYDEPSAL ... in =" call ADDFACULTYDEPSAL ...

here: MySQL Connection Guide I also tried changing stmt to the called statement as such:

 CallableStatement stmt; if(a == 1){ stmt = dbConnection.prepareCall("{call ADDFACULTYDEPTSAL(?,?,?)}"); } else { stmt = dbConnection.prepareCall("{call ADDFACULTYUNISAL(?,?,?)}"); }

However, trying this method does not work, because I need to pass more than two variables to the procedure.

Can someone tell me what I am doing wrong?

+8
source share
2 answers

You were almost there:

 String call = (a == 1 ? "{call ADDFACULTYDEPTSAL(?,?,?)}" : "{call ADDFACULTYUNISAL(?,?,?)}"); try (CallableStatement stmt = dbConnection.prepareCall(call)) { stmt.setInt(1, Integer.parseInt(fid.getText())); stmt.setString(2, fname.getText()); stmt.setInt(3, Integer.parseInt(did.getText())); stmt.execute(); }
+13
source

https://www.tutorialspoint.com/jdbc/jdbc-statements.htm contains simple and effective documentation for use

0
source

More articles:


All Articles