D4mations.com

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



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

d4mer

, , , , , , ,

34 responses to “Java JDBC Tutorial – Part 6.1: Calling MySQL Stored Procedures with Java”

  3. 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. 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. 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. 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.

  22. 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. 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. 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…..

  27. 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. 🙂

  29. 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.

Leave a Reply