This website requires JavaScript.

    Java: 使用jdbc 连接MySQL数据库(三) 调用存储

    默认分类

    2020-01-15

    336

    0

    JDBC API 提供了CallableStatement 对象来访问存储,格式如下:

    CallableStatement myCall= myConn.prepareCall(“{call some_stored_proc()}”); …

    myCall.execute();

    JDBC API同时也提供了不同的参数,IN(默认)、INOUT、OUT三者差异请看这里

    准备代码

    create database if not exists demo;

use demo;

drop table if exists employees;

CREATE TABLE `employees` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `last_name` varchar(64) DEFAULT NULL,
   `first_name` varchar(64) DEFAULT NULL,
   `email` varchar(64) DEFAULT NULL,
   `department` varchar(64) DEFAULT NULL,
   `salary` DECIMAL(10,2) DEFAULT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (1,'Doe','John','john.doe@foo.com', 'HR', 55000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (2,'Public','Mary','mary.public@foo.com', 'Engineering', 75000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (3,'Queue','Susan','susan.queue@foo.com', 'Legal', 130000.00);

INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (4,'Williams','David','david.williams@foo.com', 'HR', 120000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (5,'Johnson','Lisa','lisa.johnson@foo.com', 'Engineering', 50000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (6,'Smith','Paul','paul.smith@foo.com', 'Legal', 100000.00);

INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (7,'Adams','Carl','carl.adams@foo.com', 'HR', 50000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (8,'Brown','Bill','bill.brown@foo.com', 'Engineering', 50000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (9,'Thomas','Susan','susan.thomas@foo.com', 'Legal', 80000.00);

INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (10,'Davis','John','john.davis@foo.com', 'HR', 45000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (11,'Fowler','Mary','mary.fowler@foo.com', 'Engineering', 65000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (12,'Waters','David','david.waters@foo.com', 'Legal', 90000.00);

--
-- DEFINE STORED PROCEDURES
--

DELIMITER $$
DROP PROCEDURE IF EXISTS `get_count_for_department`$$

CREATE DEFINER=`student`@`localhost` PROCEDURE `get_count_for_department`(IN the_department VARCHAR(64), OUT the_count INT)
BEGIN

     SELECT COUNT(*) INTO the_count FROM employees where department=the_department;

END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS `get_employees_for_department`$$

CREATE DEFINER=`student`@`localhost` PROCEDURE `get_employees_for_department`(IN the_department VARCHAR(64))
BEGIN

    SELECT * from employees where department=the_department;

END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS `greet_the_department`$$

CREATE DEFINER=`student`@`localhost` PROCEDURE `greet_the_department`(INOUT department VARCHAR(64))
BEGIN

    SET department = concat('Hello to the awesome ', department, ' team!');

END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS `increase_salaries_for_department`$$

CREATE DEFINER=`student`@`localhost` PROCEDURE `increase_salaries_for_department`(IN the_department VARCHAR(64), IN increase_amount DECIMAL(10,2))
BEGIN

    UPDATE employees SET salary= salary + increase_amount where department=the_department;

END$$
DELIMITER ;
     

    IN 参数

    import java.sql.*;

/**
 * Test calling stored procedure with IN parameters
 *  
 * @author www.luv2code.com
 *
 */
public class IncreaseSalariesForDepartment {

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

      Connection myConn = null;
      CallableStatement myStmt = null;

      try {
         // Get a connection to database
         myConn = DriverManager.getConnection(
               "jdbc:mysql://localhost:3306/demo", "student", "student");

         String theDepartment = "Engineering";
         int theIncreaseAmount = 10000;

         // Show salaries BEFORE
         System.out.println("Salaries BEFORE\n");
         showSalaries(myConn, theDepartment);

         // Prepare the stored procedure call
         myStmt = myConn
               .prepareCall("{call increase_salaries_for_department(?, ?)}");

         // Set the parameters
         myStmt.setString(1, theDepartment);
         myStmt.setDouble(2, theIncreaseAmount);

         // Call stored procedure
         System.out.println("\n\nCalling stored procedure.  increase_salaries_for_department('" + theDepartment + "', " + theIncreaseAmount + ")");
         myStmt.execute();
         System.out.println("Finished calling stored procedure");

         // Show salaries AFTER
         System.out.println("\n\nSalaries AFTER\n");
         showSalaries(myConn, theDepartment);

      } catch (Exception exc) {
         exc.printStackTrace();
      } finally {
         close(myConn, myStmt, null);
      }
   }

   private static void showSalaries(Connection myConn, String theDepartment) throws SQLException {
      PreparedStatement myStmt = null;
      ResultSet myRs = null;

      try {
         // Prepare statement
         myStmt = myConn
               .prepareStatement("select * from employees where department=?");

         myStmt.setString(1, theDepartment);

         // Execute SQL query
         myRs = myStmt.executeQuery();

         // Process result set
         while (myRs.next()) {
            String lastName = myRs.getString("last_name");
            String firstName = myRs.getString("first_name");
            double salary = myRs.getDouble("salary");
            String department = myRs.getString("department");

            System.out.printf("%s, %s, %s, %.2f\n", lastName, firstName, department, salary);
         }
      } catch (Exception exc) {
         exc.printStackTrace();
      } finally {
         close(myStmt, myRs);
      }

   }

   private static void close(Connection myConn, Statement myStmt,
         ResultSet myRs) throws SQLException {
      if (myRs != null) {
         myRs.close();
      }

      if (myStmt != null) {
         myStmt.close();
      }

      if (myConn != null) {
         myConn.close();
      }
   }

   private static void close(Statement myStmt, ResultSet myRs)
         throws SQLException {

      close(null, myStmt, myRs);
   }
}

    INOUT 参数

    import java.sql.*;

/**
  * Test calling stored procedure with INOUT parameters
  * 
  * @author www.luv2code.com
  *
  */
public class GreetTheDepartment {

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

        Connection myConn = null;
         CallableStatement myStmt = null;

        try {
             // Get a connection to database
             myConn = DriverManager.getConnection(
                     "jdbc:mysql://localhost:3306/demo", "student", "student");

            String theDepartment = "Engineering";

             // Prepare the stored procedure call
             myStmt = myConn
                     .prepareCall("{call greet_the_department(?)}");

            // Set the parameters
             myStmt.registerOutParameter(1, Types.VARCHAR);
             myStmt.setString(1, theDepartment);

            // Call stored procedure
             System.out.println("Calling stored procedure.  greet_the_department('" + theDepartment + "')");
             myStmt.execute();
             System.out.println("Finished calling stored procedure");            

             // Get the value of the INOUT parameter
             String theResult = myStmt.getString(1);

             System.out.println("\nThe result = " + theResult);

        } catch (Exception exc) {
             exc.printStackTrace();
         } finally {
             close(myConn, myStmt);
         }
     }

    private static void close(Connection myConn, Statement myStmt) throws SQLException {
         if (myStmt != null) {
             myStmt.close();
         }

        if (myConn != null) {
             myConn.close();
         }
     }
}
     

    OUT 参数

    import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

/**
  * Test calling stored procedure with OUT parameters
  * 
  * @author www.luv2code.com
  *
  */
public class GetCountForDepartment {

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

        Connection myConn = null;
         CallableStatement myStmt = null;

        try {
             // Get a connection to database
             myConn = DriverManager.getConnection(
                     "jdbc:mysql://localhost:3306/demo", "student", "student");

            String theDepartment = "Engineering";

             // Prepare the stored procedure call
             myStmt = myConn
                     .prepareCall("{call get_count_for_department(?, ?)}");

            // Set the parameters
             myStmt.setString(1, theDepartment);
             myStmt.registerOutParameter(2, Types.INTEGER);

             // Call stored procedure
             System.out.println("Calling stored procedure.  get_count_for_department('" + theDepartment + "', ?)");
             myStmt.execute();
             System.out.println("Finished calling stored procedure");            

             // Get the value of the OUT parameter
             int theCount = myStmt.getInt(2);

             System.out.println("\nThe count = " + theCount);

        } catch (Exception exc) {
             exc.printStackTrace();
         } finally {
             close(myConn, myStmt);
         }
     }

    private static void close(Connection myConn, Statement myStmt) throws SQLException {
         if (myStmt != null) {
             myStmt.close();
         }

        if (myConn != null) {
             myConn.close();
         }
     }    
}
     

    获取结果集

    import java.sql.*;

public class GetEmployeesForDepartment {

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

        Connection myConn = null;
         CallableStatement myStmt = null;
         ResultSet myRs = null;

        try {
             // Get a connection to database
             myConn = DriverManager.getConnection(
                     "jdbc:mysql://localhost:3306/demo", "student", "student");

            String theDepartment = "Engineering";

            // Prepare the stored procedure call
             myStmt = myConn
                     .prepareCall("{call get_employees_for_department(?)}");

            // Set the parameter
             myStmt.setString(1, theDepartment);

            // Call stored procedure
             System.out.println("Calling stored procedure.  get_employees_for_department('" + theDepartment + "')");
             myStmt.execute();
             System.out.println("Finished calling stored procedure.\n");

            // Get the result set
             myRs = myStmt.getResultSet();

            // Display the result set
             display(myRs);

         } catch (Exception exc) {
             exc.printStackTrace();
         } finally {
             close(myConn, myStmt, myRs);
         }
     }

    private static void display(ResultSet myRs) throws SQLException {
         // Process result set
         while (myRs.next()) {
             String lastName = myRs.getString("last_name");
             String firstName = myRs.getString("first_name");
             double salary = myRs.getDouble("salary");
             String department = myRs.getString("department");

            System.out.printf("%s, %s, %s, %.2f\n", lastName, firstName, department, salary);
         }

    }

    private static void close(Connection myConn, Statement myStmt,
             ResultSet myRs) throws SQLException {
         if (myRs != null) {
             myRs.close();
         }

        if (myStmt != null) {
             myStmt.close();
         }

        if (myConn != null) {
             myConn.close();
         }
     }

    private static void close(Statement myStmt, ResultSet myRs)
             throws SQLException {

        close(null, myStmt, myRs);
     }
}
     

     

     

    

     
     #Java8
      
    0条评论 
    avatar
     
     
      
     
       
       
       
      
    © 2014 - present | Haseo Chen | Proudly powered by
    Dice
       
      沪ICP备20002437号