Blog Image

Jdbc+StoredProcedures  

This blog explains about what is stored procedures, how to call stored procedure using JDBC methods and using jdbc api. How to create a stored procedure with example.

Stored Procedure:
A stored procedure is nothing but a group of SQL statements compiled into a single execution

  • Most of the major DBM's supports stored procedures.
  • Stored procedures can be compiled and executed with different parameters and results, and they can have any combination of input, output, and input/output parameters.
  • Using Stored Procedures we can reduce network traffic between clients and servers.
JDBC provided methods, based on characteristic of data we can use Stored Procedures,
  1. Stored Procedure requires no Input and output parameters then we can use Statement .
  2. Stored Procedure requires only Input parameters then we can use Prepared Statement .
  3. Stored Procedure requires Input and output parameters then we can use Callable Statement.
example for create a procedure,

DELIMITER $$
DROP PROCEDURE IF EXISTS `srimanjavagroupdb`.`getcityName` $$
CREATE PROCEDURE `srimanjavagroupdb`.`getcityName`
  (IN CITY_ID INT, OUT CITY_NAME VARCHAR(255))
BEGIN
select c.city_name from srimanjavagroupdb.city c where c.city_id=CITY_ID;
END $$
DELIMITER

  • Above procedure srimanjavagroupdb is database name and srimanjavagroupdb.city is table name.
  • Just copy the above procedure in
  •  open MYSQL -- open new sql tab-- paste--execute procedure .
Syntax used for execution of stored procedures using callable statements having output parameters  
  1. {call procedure-name(?,?,...)}
  • You must follow the correct order for the parameter (IN and OUT). Values for OUT parameters must be registered using the registerOutParameter() method of the CallableStatement class. Each OUT parameter must be specified in the correct order.(see createProcedure() method in below example)
Before running the project add the mysql-connector-java-5.1.29.jar into your project libraries (fallow below steps for adding the a jar i)
(BuildPath--Configure BuilPath--select Libraries--click Add External Jar's--Add jar from your file location)

Below example specify how to create the procedures using JDBC:

public class StoredProcedures {

    private static Connection getConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/srimanjavagroupdb", "root",
                    "welcome1");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    private static void dropEmployees() {
        Connection con = getConnection();
        Statement stmt = null;
        String createString;
        createString = "DROP TABLE IF EXISTS `srimanjavagroupdb`.`employees`;";
        try {
            stmt = con.createStatement();
            stmt.executeUpdate(createString);
            System.out.println("Table is deleted successfully");
        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
    }

    public static void createEmployees() {
        Connection con = getConnection();
        Statement stmt = null;
        String createString;
        createString = "CREATE TABLE `srimanjavagroupdb`.`employees` ("
                + "`EmployeeID` int(10) unsigned NOT NULL default '0',"
                + "`Name` varchar(45) collate utf8_unicode_ci NOT NULL default '',"
                + "`Office` varchar(10) collate utf8_unicode_ci NOT NULL default '',"
                + "`CreateTime` timestamp NOT NULL default CURRENT_TIMESTAMP," + "PRIMARY KEY (`EmployeeID`)" + ")";
        try {
            stmt = con.createStatement();
            stmt.executeUpdate(createString);
            System.out.println("Table created successfully");
        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
    }

    private static void insertEmployee() {
        Connection con = getConnection();
        PreparedStatement ps = null;

        try {
            ps = con.prepareStatement("INSERT INTO Employees(EmployeeID, name, Office) VALUES (?, ?, ?)");
            ps.setInt(1, 42);
            ps.setString(2, "ramesh");
            ps.setString(3, "11c21A0");
            ps.executeUpdate();
            ps.clearParameters();
            ps.setInt(1, 43);
            ps.setString(2, "suresh");
            ps.setString(3, "11c21A0");
            ps.executeUpdate();
            System.out.println("records are inserted successfully");
        } catch (SQLException e) {
            System.err.println("SQLException: " + e.getMessage());
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
    }

    private static void callProcedure() {
        try (Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/srimanjavagroupdb", "root",
                "welcome1"); CallableStatement stmt = conn.prepareCall("{call getcityName (?,?)}");) {
            int city_id = 8;
            stmt.setInt(1, city_id);
            stmt.registerOutParameter(2, Types.CHAR);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString("city_name"));
            }

            stmt.close();

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

    public static void main(String[] args) {
        createProcedure();
        dropEmployees();
        createEmployees();
        insertEmployee();
        /* dropEmployees(); */

    }
}


About author

User Image
sagarreddy

my self is SagarReddy. I from bhadrachalam and i am working StackSpace software solutions pvt. Ltd. has a java developer. Learning the new things from authors to update my self to build my career Better Way.

0

-Comments

Be the first person to write a comment for this Blog
Load More

No More Comments

Leave a Comment

Your comment has been posted and will appear soon.