DB2数据库调用存储过程的方法及实例

5544992

一、对存储过程的调用分三部分

1.连接(与数据库建立连接)

Class.forName(COM.ibm.db2.jdbc.net.DB2Driver).newInstance();

Connection con=DriverManager.getConnection(url,user,password);

2.注册输出参数

cs.registerOutParameter (3, Types.INTEGER);

3.调用存储过程:

CallableStatement cs=con.prepareCall({call store_name(参数,参数,参数)});

二、调用举例:

import java.net.URL;

import java.sql.*;

class test2

public static void main(String args[])

String url = jdbc:db2://wellhope/sample;

String user=db2admin;

String password=db2admin;

try

Class.forName(COM.ibm.db2.jdbc.net.DB2Driver).newInstance(); //与数据库建立连接

Connection con=DriverManager.getConnection(url,user,password);

checkForWarning(con.getWarnings());

DatabaseMetaData dma=con.getMetaData();

String str=This is a string;

//int hashcode=str.hashCode();

//System.out.println(Hashcode +hashcode);

//创建Statement对象,用于执行SQL语句

Statement stmt=con.createStatement();

//创建CallableStatement对象,用于执行存储过程

CallableStatement cs=con.prepareCall({call PRO_YHDL1(?,?,?)});

//注册输出参数

cs.registerOutParameter (3, Types.INTEGER);

int result = 0;

cs.setString(1,123);

cs.setString(2,123);

cs.execute();

result = cs.getInt (3);

dispResultSet(result);

cs.close();

con.close();

catch(SQLException ex)

System.out.println( * * * SQLException caught * * * );

while(ex!=null)

System.out.println(SQLState: +ex.getSQLState());

System.out.println(Message: +ex.getMessage());

System.out.println(Vendor: +ex.getErrorCode());

exex=ex.getNextException();

System.out.println();

catch(java.lang.Exception ex)

ex.printStackTrace();


三、存储过程举例:

Pro_yhdl1是一个存储过程，它的功能是从数据库表YHDL中取出PWD：



import java.sql.*;

public class Pro_yhdl1

public static void pro_yhdl1 ( String m_id,

String m_pwd,

int[] result ) throws SQLException, Exception

Connection con = DriverManager.getConnection(jdbc:default:connection);

PreparedStatement stmt = null;

ResultSet rs = null;

String sql;

String m_password=;

sql = SELECT

+ DB2ADMIN.YHDL.PWD

+ FROM

+ DB2ADMIN.YHDL

+ WHERE

+ (

+ (

+ DB2ADMIN.YHDL.ID = +m_id.trim() +

+ )

+ );

stmt = con.prepareStatement( sql );

rs = stmt.executeQuery();


while (rs.next())

m_password=rs.getString(1);

m_passwordm_password=m_password.trim();

if (rs.wasNull())

System.out.print(NULL);

else

System.out.print(m_password);

result[0] =1;

else

result[0] =0;


if (rs != null) rs.close();

if (stmt != null) stmt.close();

if (con != null) con.close();