存储过程大全

连接数据库，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();
        }