【IT168 技术】上次我们介绍了DB2数据库创建触发器的实现过程,本文我们来介绍一下DB2数据库对存储过程的调用,接下来就让我们来一起了解一下这部分内容吧。
一、对存储过程的调用分三部分
1.连接(与数据库建立连接)
Class.forName("COM.ibm.db2.jdbc.net.DB2Driver").newInstance();
Connection con=DriverManager.getConnection(url,user,password);
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();
}
}
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
{
// Get connection to the database
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();
// Access query results
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);
}
if(m_password.equals(m_pwd.trim()))
{
result[0] =1;
}
else
{
result[0] =0;
}
// close open resources
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (con != null) con.close();
// set return parameter
//result[0] = result[0];
}
}
public class Pro_yhdl1
{
public static void pro_yhdl1 ( String m_id,
String m_pwd,
int[] result ) throws SQLException, Exception
{
// Get connection to the database
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();
// Access query results
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);
}
if(m_password.equals(m_pwd.trim()))
{
result[0] =1;
}
else
{
result[0] =0;
}
// close open resources
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (con != null) con.close();
// set return parameter
//result[0] = result[0];
}
}
关于DB2数据库调用存储过程的知识就介绍到这里了,希望本次的介绍能够对您有所帮助。