存储过程大全

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