随时随地阅读更多技术实战干货,获取项目源码、学习资料,请关注源代码社区公众号(ydmsq666)
存储过程是一SQL语句和可选控制流语句的预编译集合。编译完成后存放在数据库中,这样就省去了执行SQL语句时对SQL语句进行编译所花费的时间。在执行存储过程时只需将参数传递到数据库中,而不需要将整条SQL语句传递到数据库中,从而减少了网络传输的流量,提高了程序的运行速度。
在JDBC中通过CallableStatement对象进行操作,示例如下:
存储过程:
BEGIN
SELECT * FROM t_books order by id desc;
END
FindBook:
package com.home.web.manager;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.home.web.dto.Book;
/**
*
* @description 用于执行查询存储过程的类
* @author fj
* @createTime 2017年3月1日
*/
public class FindBook {
public Connection getConnection() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test";
conn = DriverManager.getConnection(url, "root", "123456");
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public List<Book> findAll() {
List<Book> list = new ArrayList<>();
Connection conn = getConnection();
try {
CallableStatement cs = conn.prepareCall("{call findAllBook()}");
ResultSet rs = cs.executeQuery();
while (rs.next()) {
Book book = new Book();
book.setId(rs.getInt("id"));
book.setName(rs.getString("name"));
book.setPrice(rs.getDouble("price"));
book.setBookCount(rs.getInt("bookCount"));
book.setAuthor(rs.getString("author"));
list.add(book);
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
}
index.jsp:
<%@page import="java.net.URLDecoder"%>
<%@ page language="java" import="java.util.*" import="com.home.web.dto.*" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>添加图书</title>
</head>
<body>
<jsp:useBean id="findBook" class="com.home.web.manager.FindBook"></jsp:useBean>
<table align="center" width="450" border="1">
<tr>
<td colspan="5" align="center">
<h2>所有图书信息</h2>
</td>
</tr>
<tr align="center">
<td><b>ID</b></td>
<td><b>图书名称</b></td>
<td><b>价格</b></td>
<td><b>数量</b></td>
<td><b>作者</b></td>
</tr>
<%
List<Book> list = findBook.findAll();//获取图书信息集合
if (list == null || list.size() < 1) {
out.println("没有数据!");
} else {
for (Book book : list) {
%>
<tr align="center">
<td><%=book.getId()%></td>
<td><%=book.getName()%></td>
<td><%=book.getPrice()%></td>
<td><%=book.getBookCount()%></td>
<td><%=book.getAuthor()%></td>
</tr>
<%
}
}
%>
</table>
</body>
</html>