准备表和存储过程
在jdbc中调用存储过程之前先准备好数据库的表author、book、以及将要调用的存储过程
author表结构：
mysql> describe author;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| author_id | int | NO | PRI | NULL | |
| name | varchar(45) | YES | | NULL | |
| email | varchar(45) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
book表结构：
mysql> describe book;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| book_id | int | NO | PRI | NULL | |
| title | varchar(128) | YES | | NULL | |
| description | varchar(512) | YES | | NULL | |
| published | date | YES | | NULL | |
| author_id | int | YES | MUL | NULL | |
| price | float | YES | | NULL | |
| rating | int | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
存储过程：
-- 定义一个分界符
delimiter //
-- 创建一个存储过程，该存储过程接收两个参数name,email
CREATE PROCEDURE create_author (IN name VARCHAR(45), email VARCHAR(45))
BEGIN
-- 定义一个变量newAuthorId
DECLARE newAuthorID INT;
INSERT INTO author (name, email) VALUES (name, email);
-- 为变量赋值,将刚刚插入的姓名为name的author的id返回
SET newAuthorID = (SELECT author_id FROM author a WHERE a.name = name);
INSERT INTO book (title, description, published, author_id, price, rating)
VALUES (CONCAT('Life Story of ', name),
CONCAT('Personal Stories of ', name),
date('2016-12-30'), newAuthorID, 10.00, 0);
END
//
Delimiter ;
在JDBC中直接调用数据库中的IN模式存储过程
现在，让我们看看如何使用JDBC调用此存储过程。
在jdbc中，存储过程的参数使用问号表示,然后使用语句对象的方法设置问号所表示的参数值，就像为PreparedStatement设置参数一样。
Invoking execute() method on the statement object will run the specified stored procedure. This method returns true if the stored procedure returns a result set, false if not, and throw SQLException in cases of an error occurred.
public static void main(String[] args) {
String databaseURL = "jdbc:mysql://localhost:3306/library?serverTimezone=UTC";
String user = "root";
String password = "admin";
try (
Connection connection = DriverManager.getConnection(databaseURL, user, password);
//创建调用存储过程对象
CallableStatement statement = connection.prepareCall("{call create_author(?,?)}");
) {
statement.setString(1, "Bill Gates");
statement.setString(2, "bill@microsoft.com");
// 执行存储过程
statement.execute();
statement.close();
System.out.println("Stored procedure called successfully!");
} catch (SQLException e) {
e.printStackTrace();
}
}
使用java代码创建存储过程
java代码执行Sql语句创建存储过程
以下Java程序创建了一个简单的MySQL存储过程，称为delete_book，该存储过程根据指定的bookID 从表中删除一行：
public static void main(String[] args) {
String databaseURL = "jdbc:mysql://localhost:3306/library?serverTimezone=UTC";
String user = "root";
String password = "admin";
try (
Connection connection = DriverManager.getConnection(databaseURL, user, password);
//创建调用存储过程对象
// CallableStatement statement = connection.prepareCall("{call create_author(?,?)}");
Statement statement = connection.createStatement();
) {
String queryDrop = "DROP PROCEDURE IF EXISTS delete_book";
String queryCreate = "CREATE PROCEDURE delete_book (IN bookID INT) ";
queryCreate += "BEGIN ";
queryCreate += "DELETE FROM book WHERE book_id = bookID; ";
queryCreate += "END";
// drops the existing procedure if exists,如果存储过程已经存在则删除，execute是执行sql语句的方法
statement.execute(queryDrop);
// then creates a new stored procedure，创建一个新的存储过程
statement.execute(queryCreate);
statement.close();
System.out.println("Stored procedure created successfully!");
} catch (SQLException e) {
e.printStackTrace();
}
}
请注意，我们必须执行两个查询：第一个查询是删除存储过程（如果存在）； 第二个实际上创建存储过程。
jdbc调用复杂存储过程（具有IN,OUT,INOUT多个模式）
CREATE PROCEDURE `summary_report`(
IN title VARCHAR(45),
OUT totalBooks INT,
OUT totalValue DOUBLE,
INOUT highPrice DOUBLE
)
BEGIN
DECLARE maxPrice DOUBLE;
--条件是根据book表的书名来模糊查询满足条件的书一共有多少本，以及满足条件的书的总价是多少
-- 把查询到的结果分别赋值给变量totalBooks,totalValue;
-- totalBooks表示书的总数，totlaValue表示书的总价值，也就是书的总价
SELECT COUNT(*) AS bookCount, SUM(price) as total
FROM book b JOIN author a ON b.author_id = a.author_id
AND b.title LIKE CONCAT('%', title, '%')
INTO totalBooks, totalValue;
--查询到的最大的价格，把他存进相应的变量maxPrice中,创建一个简单的存储过程测试这一条语句
SELECT MAX(price) FROM book WHERE price INTO maxPrice;
IF (maxPrice > highPrice) THEN
SET highPrice = maxPrice;
END IF;
END
存储过程分析：
以上存储过程使用到了多表查询，变量赋值，条件查询等,函数Concat（拼接函数），通配符%（%：匹配任何字符出现任意次数）；
Max()；函数
- IN标题VARCHAR（45）：输入参数。 该过程搜索标题包含此参数指定的单词的书籍。
- OUT totalBooks INT：该过程对匹配的书籍总数进行计数，并将值存储到此输出参数中。
- OUT totalValue DOUBLE：该过程对匹配书籍的总价值进行计数，并将该价值存储到此输出参数中。
- INOUT highPrice DOUBLE：这即是输入参数也是输出参数。 该过程将在所有书籍中选择最高价格，如果最高价格大于输入的参数值，则将其分配给参数。
jdbc执行复杂存储过程的过程：
- jdbc要检索OUT和INOUT参数的值，JDBC要求在调用存储过程之前必须通过在CallableStatementobject上调用以下方法来注册这些参数：(IN模式参数不需要注册)
- 注册参数后执行存储过程
- 执行存储过程后就可以获取执行存储过程返回的参数
JDBC调用复杂存储过程代码:
import java.lang.reflect.Type;
import java.sql.*;
public class StoredProcedureCallExample2 {
public static void main(String[] args) {
String databaseURL = "jdbc:mysql://localhost:3306/library?serverTimezone=UTC";
String user = "root";
String password = "admin";
try (
Connection connection = DriverManager.getConnection(databaseURL, user, password);
//创建调用存储过程对象
CallableStatement statementBack = connection.prepareCall("{call summary_report(?,?,?,?)}");
Statement statement = connection.createStatement();
) {
/*
设置IN参数
设置OUT参数
*/
// 设置第一个参数IN模式作为模糊查询的搜索关键字
statementBack.setString(1, "java");
// 设置第四个INOUT模式下的IN模式参数
statementBack.setDouble(4, 50);
//注册第二第三第四个OUT，INOUT参数
statementBack.registerOutParameter(2, Types.INTEGER);
statementBack.registerOutParameter(3, Types.DOUBLE);
statementBack.registerOutParameter(4, Types.DOUBLE);
// 执行存储过程
statementBack.execute();
//得到第二个OUT模式参数，数据类型为INT
int totalBook = statementBack.getInt(2);
// Integer totalBook = statementBack.getObject(2, Integer.class);
Double totalvalue = statementBack.getDouble(3);
Double highPrice = statementBack.getDouble(4);
System.out.println("Total books:" + totalBook);
System.out.println("Total value:" + totalvalue);
System.out.println("High Price:" + highPrice);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/*
program result:
Total books:7
Total value:245.79000091552734
High Price:122.3499984741211
Process finished with exit code 0
*/
jdbc调用返回结果集的存储过程
先准备存储过程
存储过程可以返回结果集。 例如以下过程：
-- 创建存储过程：根据评分参数查询大于某一评分的数据
delimiter //
create procedure get_books(IN rate INT)
begin
select * from book where rating>=rate;
end
//
delimiter ;
-- 测试查询所有评分数据大于等于4的数据总量
select count(*) from book where rating>=4;
-- 测试调用存储过程
call get_books(4);
使用JDBC代码检索和处理从存储过程返回的结果集:
import java.sql.*;
public class StoredProcedureCallExample3 {
public static void main(String[] args) {
String databaseURL = "jdbc:mysql://localhost:3306/library?serverTimezone=UTC";
String user = "root";
String password = "admin";
try (
Connection connection = DriverManager.getConnection(databaseURL, user, password);
//创建调用存储过程对象
CallableStatement statement = connection.prepareCall("{call get_books(?)}");
) {
// 查询所有评分大于等于5分的书
statement.setInt(1, 5);
// 执行存储过程
boolean hadResults = statement.execute();
// print headings
System.out.println("| Title | Description | Rating |");
System.out.println("================================");
while (hadResults) {
ResultSet resultSet = statement.getResultSet();
// process result set
while (resultSet.next()) {
String title = resultSet.getString("title");
String description = resultSet.getString("description");
int rating = resultSet.getInt("rating");
System.out.println(
"| " + title + " | " + description + " | " + rating + " |");
}
hadResults = statement.getMoreResults();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/*
program result:
| Title | Description | Rating |
================================
| Thinking in java | Teach you core java in depth | 5 |
| java Puzzlers | java Traps,Pitfals,and Comer cases | 5 |
| Thinging in C++ | Mastering C++ | 5 |
Process finished with exit code 0
*/
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
