Java: 使用jdbc 连接MySQL数据库(三) 调用存储
默认分类
2020-01-15
276
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); } }
0条评论