Java中调用sqlServer的存储过程的几种简单情况

来源：互联网发布：青少年编程网编辑：程序博客网时间：2024/06/11 06:44

先在sqlServer写一个查询全部数据的存储过程。

if exists(select * from sysobjects where name='usp_selectAllInfo')drop proc usp_selectAllInfogo--创建存储过程create procedure usp_selectAllInfoasselect id, name, age, birthday, email, phone from Goddessgo

再在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_searchByNamego--创建存储过程输入参数。create proc usp_searchByName@name nvarchar(50)asselect  id, name, age, birthday, email, phonefrom  Goddesswhere 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_numgocreate proc usp_query_num@num int  outputasselect @num=count(1) from Goddessgo

在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;    }

就写这么多吧。每天努力一点点。

0 0