先在sqlServer写一个查询全部数据的存储过程。
if exists(select * from sysobjects where name='usp_selectAllInfo')
drop proc usp_selectAllInfo
go
--创建存储过程
create procedure usp_selectAllInfo
as
select id, name, age, birthday, email, phone from Goddess
go
再在Java中写一个泛型的方法调用
// 存储过程查询全部信息
public List<Girl> queryAll() {
List<Girl> list = new ArrayList<Girl>();
Connection conn = DbConn.getConnection();
try {// id, name, age, birthday, email, phone
CallableStatement cs = conn
.prepareCall("execute usp_selectAllInfo");
ResultSet rs = cs.executeQuery();
while (rs.next()) {
Girl girl = new Girl();
girl.setId(rs.getInt(1));
girl.setName(rs.getString(2));
girl.setAge(rs.getInt(3));
girl.setBirthday(rs.getDate(4));
girl.setPhone(rs.getString(5));
list.add(girl);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
调用带参数的存储过程。
if exists(select * from sysobjects where name='usp_searchByName')
drop proc usp_searchByName
go
--创建存储过程输入参数。
create proc usp_searchByName
@name nvarchar(50)
as
select id, name, age, birthday, email, phone
from Goddess
where name like '%'+@name+'%'
go
在Java中对应的方法代码
// 存储过程根据参数查询
public List<Girl> queryByName(String name) {
List<Girl> list = new ArrayList<Girl>();
Connection conn = DbConn.getConnection();
try {// id, name, age, birthday, email, phone
// CallableStatement cs = conn.prepareCall("execute usp_searchByName "
// + name);
CallableStatement cs = conn.prepareCall("execute usp_searchByName ?");
cs.setString(1, name);
ResultSet rs = cs.executeQuery();
while (rs.next()) {
Girl girl = new Girl();
girl.setId(rs.getInt(1));
girl.setName(rs.getString(2));
girl.setAge(rs.getInt(3));
girl.setBirthday(rs.getDate(4));
girl.setPhone(rs.getString(5));
list.add(girl);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
还有就是带输出参数的
--创建存储过程输出参数。
if exists(select * from sysobjects where name='usp_query_num')
drop proc usp_query_num
go
create proc usp_query_num
@num int output
as
select @num=count(1) from Goddess
go
在Java中对应方法代码:
// 存储过程输出参数
public int queryNum() {
Connection conn = DbConn.getConnection();
int num = -1;
try {
CallableStatement cs = conn.prepareCall("execute usp_query_num ?");
cs.registerOutParameter(1, Types.INTEGER);
// cs.execute();
// num=cs.getInt(1);
cs.executeUpdate();
num = cs.getInt(1);
} catch (SQLException e) {
e.printStackTrace();
}
return num;
}
就写这么多吧。每天努力一点点。