存储过程的使用格式
发布时间：2020-12-12 15:37:44 所属栏目：MsSql教程 来源：网络整理
导读：? 连接数据库，JDBC-ODBC数据源，及java调用存储过程 需用到的包java.sql.*; 一java连接 1) 加载驱动：Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");//java Class.forName("sun.jdbc.odbc.jdbcodbcDriver");//jdbc-odbc 数据源 2) 建立连
|
? 连接数据库，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,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(?,"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(); ????????} ????} ? 我找了一些关于数据库中存储过程的使用格式，想与大家分享！ （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |