这些天头头常在唠叨,存储过程的重要。由于手上的任务已经完成(头头没有反馈要求修改,嘿嘿),时间充裕, 便投入存储过程的学习。今天做了一个超级简单的例子,测试成功 :)
新建表:
CREATE
TABLE
[
mytest
]
(
[ id ] [ int ] NOT NULL ,
[ name ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ phone ] [ varchar ] ( 13 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ addr ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL
) ON [ PRIMARY ]
GO
[ id ] [ int ] NOT NULL ,
[ name ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ phone ] [ varchar ] ( 13 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ addr ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL
) ON [ PRIMARY ]
GO
新建存储过程:
create
procedure
get_info
@name
varchar
(
50
),
@phone
varchar
(
13
),
@addr
varchar
(
50
)
as
select *
from mytest
go
as
select *
from mytest
go
JSP代码:
<%
@ page language
=
"
java
"
pageEncoding
=
"
UTF-8
"
import
=
"
java.sql.*,tools.Database
"
%>
<% @ taglib uri = " http://jakarta.apache.org/struts/tags-bean " prefix = " bean " %>
<% @ taglib uri = " http://jakarta.apache.org/struts/tags-html " prefix = " html " %>
<% @ taglib uri = " http://jakarta.apache.org/struts/tags-logic " prefix = " logic " %>
<% @ taglib uri = " http://jakarta.apache.org/struts/tags-tiles " prefix = " tiles " %>
<! DOCTYPE HTML PUBLIC " -//W3C//DTD HTML 4.01 Transitional//EN " >
< html:html locale = " true " >
< head >
< html:base />
< title > proc_test.jsp </ title >
< meta http - equiv = " pragma " content = " no-cache " >
< meta http - equiv = " cache-control " content = " no-cache " >
< meta http - equiv = " expires " content = " 0 " >
< meta http - equiv = " keywords " content = " keyword1,keyword2,keyword3 " >
< meta http - equiv = " description " content = " This is my page " >
</ head >
< body >
This is a test for procedure. < br >
<%
String username = " sa " ;
String password = " 123456 " ;
String url = " jdbc:microsoft:sqlserver://192.168.1.112:1433;DatabaseName=test " ;
Class.forName( " com.microsoft.jdbc.sqlserver.SQLServerDriver " ).newInstance();
Connection conn = DriverManager.getConnection(url, username, password);
String sql = " execute get_info " ;
// 创建一个CallableStatement 对象来调用数据库存储过程
// CallableStatement comm = conn.prepareCall(sql);
Statement stmt = conn.createStatement();
ResultSet res = stmt.executeQuery(sql);
while (res.next()) {
%>
< table >
< tr >
< td ><%= res.getString( " id " ) %></ td >
< td ><%= res.getString( " name " ) %></ td >
< td ><%= res.getString( " phone " ) %></ td >
< td ><%= res.getString( " addr " ) %></ td >
</ tr >
</ table >
<% }
res.close();
stmt.close();
conn.close();
%>
</ body >
</ html:html >
<% @ taglib uri = " http://jakarta.apache.org/struts/tags-bean " prefix = " bean " %>
<% @ taglib uri = " http://jakarta.apache.org/struts/tags-html " prefix = " html " %>
<% @ taglib uri = " http://jakarta.apache.org/struts/tags-logic " prefix = " logic " %>
<% @ taglib uri = " http://jakarta.apache.org/struts/tags-tiles " prefix = " tiles " %>
<! DOCTYPE HTML PUBLIC " -//W3C//DTD HTML 4.01 Transitional//EN " >
< html:html locale = " true " >
< head >
< html:base />
< title > proc_test.jsp </ title >
< meta http - equiv = " pragma " content = " no-cache " >
< meta http - equiv = " cache-control " content = " no-cache " >
< meta http - equiv = " expires " content = " 0 " >
< meta http - equiv = " keywords " content = " keyword1,keyword2,keyword3 " >
< meta http - equiv = " description " content = " This is my page " >
</ head >
< body >
This is a test for procedure. < br >
<%
String username = " sa " ;
String password = " 123456 " ;
String url = " jdbc:microsoft:sqlserver://192.168.1.112:1433;DatabaseName=test " ;
Class.forName( " com.microsoft.jdbc.sqlserver.SQLServerDriver " ).newInstance();
Connection conn = DriverManager.getConnection(url, username, password);
String sql = " execute get_info " ;
// 创建一个CallableStatement 对象来调用数据库存储过程
// CallableStatement comm = conn.prepareCall(sql);
Statement stmt = conn.createStatement();
ResultSet res = stmt.executeQuery(sql);
while (res.next()) {
%>
< table >
< tr >
< td ><%= res.getString( " id " ) %></ td >
< td ><%= res.getString( " name " ) %></ td >
< td ><%= res.getString( " phone " ) %></ td >
< td ><%= res.getString( " addr " ) %></ td >
</ tr >
</ table >
<% }
res.close();
stmt.close();
conn.close();
%>
</ body >
</ html:html >
执行结果:
This is a test for procedure.
1 | cc | 1234 | cs |
2 | dd | 4567 | bj |
3 | ee | 1245 | ca |
总结:
使用存储过程还可做更多的事,现在已经会应用,所以再复杂也不怕了:P
有一个疑惑:
// CallableStatement comm = conn.prepareCall(sql); 这个不创建也可以执行,好像是因为sql已经执行了的样子