Connect to database and call stored procedure

This code example shows how to connect to a database (in this case MS SQL Server) and call a stored procedure.
To call the stored procedure you create an instance of the class CallableStatement by calling the method prepareCall() on the Connection object.
In the first example below we assume that the name of the stored procedure is "generateID" and it takes one string argument, and returns an Id which depends on the argument (in the example it returns employeeId).
The value of the argument is set by calling the method setString() on the CallableStatement object. Since we want a return value from the stored procedure we have to call the executeQuery() method, which returns a ResultSet.

        Connection con = null;
        CallableStatement proc_stmt = null;
        ResultSet rs = null;

        try {

            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

            con = DriverManager.getConnection("jdbc:sqlserver://MYSERVER;databaseName=MYDATABASE", "USERID", "PASSWORD");

            proc_stmt = con.prepareCall("{ call generateID(?) }");

            proc_stmt.setString(1, "employee");
            rs = proc_stmt.executeQuery();

            if (rs.next()) {
                int employeeId = rs.getInt(1);
                System.out.println("Generated employeeId: " + employeeId);
            } else {
                System.out.println("Stored procedure couldn't generate new Id");
            }
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {

            try {

                rs.close();
                proc_stmt.close();
                con.close();

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

        }
        //Coded by www.javadb.com

In the second example we do about just the same as above except that this time we don't provide an argument to the stored procedure and we don't receive any return value.

        Connection con = null;
        CallableStatement proc_stmt = null;

        try {

            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection("jdbc:sqlserver://MYSERVER;databaseName=MYDATABASE", "USERID", "PASSWORD");
            proc_stmt = con.prepareCall("{ call someStoredProc() }");

            proc_stmt.executeQuery();

        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {

            try {

                proc_stmt.close();
                con.close();

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

        }
        //Coded by www.javadb.com

Search for code examples on this site