存储过程的使用格式

 

连接数据库，JDBC-ODBC数据源，及java调用存储过程

需用到的包java.sql.*;

<一>java连接

1)加载驱动：Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");//java

Class.forName("sun.jdbc.odbc.jdbcodbcDriver");//jdbc-odbc数据源

2)建立连接：Connection conn=DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=数据库名","用户名","密码");//java

Connection conn=DriverManager.getConnection("jdbc;odbc;数据源名称","用户名","密码");

3）数据存放：Statement stm=conn.createStatement();

stm.executeUpdate(SQL语句);

如果有结果集：ResultSet rs=stm.executeQuery(SQL语句);

while(rs.next())

{

}

rs.getString();//取某列的值

获得列数：ResultSetMetaData meta=rs.getMetaData();

          for(int i=1;i<=meta.getColumnCount();i++)

          {

               System.out.print(rs.getString(i));

          }

4）关闭：rs.close();stm.close();conn.close();

<二>用prepareStatement代替Statament

例：添加 PreparaStatement pt=null;

       pt=conn.prepareStatement("insert into student values(?,?,?)");

       pt.setString(1,name);//为参数赋值

       pt.setInt(2,age);//同上

       pt.setString(3,sex);//同上

       int num=pt.executeUpdate();//num表示影响的行数,除查询外，都要用executeUpdate

       System.out.print(num);

<三>java调用存储过程--CallableStatement

1）无参

  CallableStatement call=null;

  call.conn.prepareCall("{call 存储过程名}");

  rs=call.executeQuery();

  while(rs.next())

  {

  }

 关闭call,rs,conn

2)带输入参数

  call=conn.prepareCall("{call prco_chaxunById(?)}");

  call.setInt(1,id);//为输入参数赋值

  rs=call.executeQuery();//查询语句要用executeQuery

  if(rs.next()){System.out.println(rs.getString(2));}

  else{System.out.println(no data);}

 关闭call,rs,conn

3)带输出参数

  call=conn.prepareCall("{call proc_getNameById(?,?)}");

  call.setInt(1,id);

  call.registerOutParameter(2,java.sql.Types.VARCHAR);//为输出参数赋值

  call.execute();带输出参数的用execute

  String name=call.getString(2);

  System.out.print(name);

 关闭call,conn

注：关闭时要将代码放入finally中

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

(转载)java调用存储过程

一、调用存储过程(无结果集返回)
        Connection connection = ConnectionHelper.getConnection();

        CallableStatement callableStatement = connection.prepareCall("{ call procedureName(?,?) }");

        callableStatement.setString(1, "xxxxxxxx");
        callableStatement.setString(2, "xxxxxxxx");

        callableStatement.execute();

    //获得sql的消息并输出，这个估计很多人都需要
        SQLWarning sqlWarning = callableStatement.getWarnings();
        while (sqlWarning != null) {
            System.out.println("sqlWarning.getErrorCode() = " + sqlWarning.getErrorCode());
            System.out.println("sqlWarning.getSQLState() = " + sqlWarning.getSQLState());
            System.out.println("sqlWarning.getMessage() = " + sqlWarning.getMessage());

            sqlWarning = sqlWarning.getNextWarning();
        }

        //close
        ConnectionHelper.closeConnection(callableStatement, connection);

二、调用存储过程，返回sql类型数据（非记录集）

        Connection connection = ConnectionHelper.getConnection();

        CallableStatement callableStatement = connection.prepareCall("{ call procedureName(?,?,?) }");

        callableStatement.setString(1, "xxxxxxxx");
        callableStatement.setString(2, "xxxxxxxx");
    //重点是这句1
    callableStatement.registerOutParameter(3, Types.INTEGER);

        callableStatement.execute();

    //取返回结果,重点是这句2
        //int rsCount = callableStatement.getInt(3);


        //close
        ConnectionHelper.closeConnection(callableStatement, connection);

三、重点来了，返回记录集，多记录集
注意，不需要注册返回结果参数，只需要在sql中select出结果即可
例如：select * from tableName
即可得到返回结果


        Connection connection = ConnectionHelper.getConnection();

        CallableStatement callableStatement = connection.prepareCall("{ call procedureName(?) }");

    //此处参数与结果集返回没有关系
        callableStatement.setString(1, "xxxxxxxx");

        callableStatement.execute();

    ResultSet resultSet = callableStatement.getResultSet();
    //以上两个语句，可以使用ResultSet resultSet = callableStatement.executeQuery();替代

    //多结果返回
        ResultSet resultSet2;
        if (callableStatement.getMoreResults()) {
            resultSet2 = callableStatement.getResultSet();
            while (resultSet2.next()) {

            }
        }

        //close
        ConnectionHelper.closeConnection(callableStatement, connection);

提示：多结果返回可以使用如下代码(以上主要让大家明白，单一结果和多结果的区别)：
        Boolean hasMoreResult = true;
        while (hasMoreResult) {
            ResultSet resultSet = callableStatement.getResultSet();
            while (resultSet.next()) {
        
            }

            hasMoreResult = callableStatement.getMoreResults();
        }

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

java调用存储过程

创建存储过程的脚本,

使用sqlserver2000 中的pubs 数据库中的 jobs表为例.

create procedure  showAll
as
select * from jobs



create procedure obtainJob_desc
@outputParam varchar(20) output,
@id int
as
select @outputParam = job_desc from jobs where job_id = @id


create procedure obtainReturn
as
declare @ret int
select @ret = count(*) from jobs
return @ret


declare @ret int
exec @ret = obtainReturn 
print @ret

用来获得连接的函数

public  Connection getConnection()...{
  Connection con = null;
try ...{
   Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
   con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;databasename=pubs","sa","");
  } catch (Exception e) ...{
   e.printStackTrace();
  }
return con ;
 }

1,调用得到结果集的存储过程

public void getResultSet()...{
//获得连接
        Connection con = this.getConnection();
try ...{
//showAll为存储过程名
            java.sql.CallableStatement cstm = con.prepareCall("{call showAll }");
            ResultSet rs = cstm.executeQuery();
while(rs.next())...{
//这里写逻辑代码。
                System.out.println(rs.getString(1));
            }
            rs.close();
            con.close();
        } catch (SQLException e) ...{
// TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

2,调用带有输入 ,输出参数的存储过程。

public void getOutParameter(int inParam)...{
        String outParam;
        Connection con = this.getConnection();

try ...{
            CallableStatement cstm = con.prepareCall("{call obtainJob_desc (?,?)}");
            cstm.registerOutParameter(1, Types.VARCHAR);
            cstm.setInt(2, inParam);
            cstm.execute();;

//得到输出参数。
            String outParma = cstm.getString(2);
            System.out.println(outParma);
            cstm.close();
            con.close();

        } catch (SQLException e) ...{
// TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

3,调用带返回值的存储过程。

public void getReturn()...{
int ret;
        Connection con = this.getConnection();
try ...{
            CallableStatement cstm = con.prepareCall("{?=call obtainReturn()}");
            cstm.registerOutParameter(1, Types.INTEGER);

            cstm.execute();
//得到返回值
             ret = cstm.getInt(1);

            System.out.println(ret);
            cstm.close();
            con.close();

        } catch (SQLException e) ...{
// TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

 

我找了一些关于数据库中存储过程的使用格式，想与大家分享！