• Wednesday , 20 March 2019

Java JDBC Tutorial – Part 6.1: Calling MySQL Stored Procedures with Java

Code Canyon



NEED TO LEARN JAVA – 5 FREE JAVA VIDEO COURSES – CLICK HERE – https://goo.gl/7i95F8 — View more videos on my “Java JDBC Tutorial” Playlist: …

Original source

3d Ocean

Related Posts

34 Comments

  1. CypherK
    January 14, 2019 at 09:44

    I liked your video. Please increase my salary by $10000.

  2. quinniel fomocod
    January 14, 2019 at 09:44

    all of your tutorial are great 🙂 … really help me a lot , thank you 🙂

  3. z图奇大帝
    January 14, 2019 at 09:44

    I don't know what the thirtieth line means. Do you mean to create a SQL statement in the database before you can execute the code? I'm using Navicat, and I've used this code, [call= (CallableStatement) conn.prepareCall ("{call increase_salary_for_department (?)});"

    Ecception:
    PROCEDURE helloworld.increase_salary_for_department does not exist

    I have the same employe database table as you do, but he's not right. Thank you

  4. shiva Rebel
    January 14, 2019 at 09:44

    i got it after removing —— myStmt.execute();————from the following.

    System.out.println("nnCalling stored procedure. increase_salaries_for_department('" + theDepartment + "', " + theIncreaseAmount + ")");

    myStmt.execute();

    System.out.println("Finished calling stored procedure");

  5. shiva Rebel
    January 14, 2019 at 09:44

    Salaries BEFORE

    Public, Mary, Engineering, 75000.00
    Johnson, Lisa, Engineering, 50000.00
    Brown, Bill, Engineering, 50000.00
    Fowler, Mary, Engineering, 65000.00

    Calling stored procedure. increase_salaries_for_department('Engineering', 10000)
    java.sql.SQLException: The user specified as a definer ('student'@'localhost') does not exist
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2490)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
    at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197)
    at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:837)
    at jdemo.IncSalaries.main(IncSalaries.java:33)

  6. NARRAVULA LAKSHMAN
    January 14, 2019 at 09:44

    In the above example, How we are using "showSalaries()" directly without creating any object for showSalaries(). when i am executing I am facing with the errors in these two lines before and after show salaries(). The same with the close keyword in finally body.

  7. Raaj Kanchan
    January 14, 2019 at 09:44

    Awesome Video, Need A Favour, I have tried it a lot to create procedure using the same code displayed in video but not able to create, Please help with this….

  8. Winsarea
    January 14, 2019 at 09:44

    Hi i want to execute procedure that i've used in sql server and get data from it in my java program

  9. Syed Noman Ahmed
    January 14, 2019 at 09:44

    amazing stuff. pretty clean and full with comments source code you have provided in your downloadable files. God bless you.

  10. BlackBrother
    January 14, 2019 at 09:44

    Sir we want more tut. if possible thanks again

  11. Hello Kitty
    January 14, 2019 at 09:44

    Clear and precise, thanks for sharing.

  12. Bisoo Lee
    January 14, 2019 at 09:44

    Like this one man..so simple and easy to understand

  13. Shashank Shivhare
    January 14, 2019 at 09:44

    Can't access the link to get database file. Please check.

  14. Aryan Azimzadeh
    January 14, 2019 at 09:44

    Hey,
    I can't find the link for the data base file , could you please send it here?

  15. Abdellaoui aicha
    January 14, 2019 at 09:44

    please how i can call a java class from a trigger in sql server

  16. avdhesh palliwal
    January 14, 2019 at 09:44

    Awesome videos with great clarity, explanation. Hope student get teachers like you everywhere.

  17. Monsieur Africain
    January 14, 2019 at 09:44

    Your videos are the best on YouTube, by far the clearest and most understandable. You make everything seem so easy.

  18. Ralph Manzano
    January 14, 2019 at 09:44

    what's the use of 'finally' and both the close() methods? please help, is it necessary?

  19. pradip318
    January 14, 2019 at 09:44

    Hello,could you please explain where and how you used showSalaries() method?or any other way to view the result through resultSet?

  20. maks burkov
    January 14, 2019 at 09:44

    Nice videos!
    Tell me please where can i get some good tutorials about stored procedures ?

  21. leyla mm
    January 14, 2019 at 09:44

    Thank you very much,I am from Azerbaijan,your lessons is really helpful for beginners 🙂

  22. Nilesh Rao
    January 14, 2019 at 09:44

    logic is same as your but quite different…..

    import java.sql.*;
    public class IncreaseSalariesForDepartment {

    public static void main(String[] args)throws SQLException {

    Connection conn = null;
    CallableStatement castmt = null;
    try{
    //get connection….
    conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/demo","student","student");
    //callable statement…..
    String the_department = "Engineering";
    int increase_amount=10000;

    System.out.println("Salaries Before…..");
    showSalaries(conn, the_department);

    castmt =conn.prepareCall("{call increase_salaries_for_department(?,?)}");

    castmt.setString(1, the_department);
    castmt.setDouble(2, increase_amount);
    // Result set……..
    System.out.println("callling stored procedure increase_fo_department_salaries_for_Department:"+the_department+" "+increase_amount);
    castmt.execute();
    System.out.println("Finished calling stored procedure……..");

    System.out.println("Salaries After………..");
    showSalaries(conn, the_department);

    }catch(Exception ex){
    ex.printStackTrace();
    }
    finally{
    if(conn!=null){
    conn.close();
    }
    if(castmt!=null){
    castmt.close();
    }
    }
    }

    private static void showSalaries(Connection conn, String the_department)throws SQLException {
    PreparedStatement pstmt = null;
    ResultSet rst = null;
    try{
    pstmt = conn.prepareStatement("select * from employees where department=?");

    pstmt.setString(1, the_department);

    rst=pstmt.executeQuery();
    while(rst.next()){
    System.out.println(rst.getInt("Id")+" "+rst.getString("last_name")+" "+rst.getString("first_name")+" "+rst.getString("email")+" "+rst.getString("department")+" "+rst.getInt("salary"));
    }

    }catch(Exception ex){
    ex.printStackTrace();
    }finally {
    if(pstmt!=null){
    pstmt.close();
    }if(rst!=null){
    rst.close();
    }
    }
    }
    }
    output……..
    Salaries Before…..
    2 Public Mary mary.public@foo.com Engineering 75000
    5 Johnson Lisa lisa.johnson@foo.com Engineering 50000
    8 Brown Bill bill.brown@foo.com Engineering 50000
    11 Fowler Mary mary.fowler@foo.com Engineering 65000
    callling stored procedure increase_fo_department_salaries_for_Department:Engineering 10000
    Finished calling stored procedure……..
    Salaries After………..
    2 Public Mary mary.public@foo.com Engineering 85000
    5 Johnson Lisa lisa.johnson@foo.com Engineering 60000
    8 Brown Bill bill.brown@foo.com Engineering 60000
    11 Fowler Mary mary.fowler@foo.com Engineering 75000

  23. Nilesh Rao
    January 14, 2019 at 09:44

    there is some error in your program while delaring the_department and increase_amount kindly replace it…. as you had mention theDepartment and theIncreaseAmount…. which showing error…
    and ur stored procedure syntax create student@localhost which is for oracle sql not for my sql stored procedure….
    kindly replace it …..
    shortcut program which give you same result ….. increase_for_department_salaries_for_Department: Engeneering 10000

    import java.sql.*;
    public class IncreaseSalariesForDepartment {

    public static void main(String[] args)throws SQLException {

    Connection conn = null;
    CallableStatement castmt = null;
    try{
    //get connection….
    conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/demo","student","student");
    //callable statement…..

    String the_department = "Engineering";
    int increase_amount=10000;

    castmt =conn.prepareCall("{call increase_salaries_for_department(?,?)}");

    castmt.setString(1, the_department);
    castmt.setDouble(2, increase_amount);
    // Result set……..
    System.out.println("callling stored procedure increase_fo_department_salaries_for_Department:"+the_department+" "+increase_amount);
    castmt.execute();
    System.out.println("Finished calling stored procedure……..");

    }catch(Exception ex){
    ex.printStackTrace();
    }
    finally{
    if(conn!=null){
    conn.close();
    }
    if(castmt!=null){
    castmt.close();
    }
    }
    }
    }

  24. The Aman
    January 14, 2019 at 09:44

    Salaries BEFORE

    Public, Mary, Engineering, 75000.00
    Johnson, Lisa, Engineering, 50000.00
    Brown, Bill, Engineering, 50000.00
    Fowler, Mary, Engineering, 65000.00

    Calling stored procedure. increase_salaries_for_department('Engineering', 10000)
    java.sql.SQLException: The user specified as a definer ('student'@'localhost') does not exist
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
    at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192)
    at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:823)
    at jdbcdemo.Driver.main(Driver.java:39)

    hello chad,
    This problem arise when i ran the code in eclipse IDE…. I had set up my database too in the workbench bt also its showing this message….. I hope u will solve it…..

  25. narri uday
    January 14, 2019 at 09:44

    Hey…!Your tutorials are really helpful.I really love to watch your videos. I have small doubt Why did you initialize null to myConn & myStmt….?

  26. vinay ganesh.d
    January 14, 2019 at 09:44

    System.out.printf() error in my ide Intellij idea you have any idea to solve

  27. Gud Jac
    January 14, 2019 at 09:44

    Was it that complicated to scroll down and show the method showSalaries just for a second? I had to visit that link, wait half a minute until it loaded, download the file, unzip it, open it. Very productive. Besides that the tutorials are really useful and informative. It could be less formal though. 🙂

  28. QIBusinessgroup
    January 14, 2019 at 09:44

    OMG this was a great tutorial.  I have search high and low for programming videos where there is not a strong language barrier.

  29. koneru srinivas
    January 14, 2019 at 09:44

    java.sql.SQLException: The user specified as a definer ('student'@'localhost') does not exist

    i am getting this exception when i excecute the myStmt.execute();

    i could not figure out why.

    the rest of the code was very clear and easy thank you.

  30. Khaled Chikh
    January 14, 2019 at 09:44

    Good tutorial, thank you.

  31. sriteshxyz
    January 14, 2019 at 09:44

    great Presentation and explanation 🙂

  32. October Sky
    January 14, 2019 at 09:44

    Awesome Explainability….!

  33. Nirosha Dogra
    January 14, 2019 at 09:44

    You're awesome. Thank you so much.

  34. baburao Bean
    January 14, 2019 at 09:44

    can we take the incresed amount from the user and add it to previous value instead of hardcoding in the sourcecode as 10000

Leave A Comment

You must be logged in to post a comment.