Java JDBC数据库 之 存储过程调用

http://www.verejava.com/?id=16998480035641

下载 Oracle 或者 Mysql Jar 驱动 

mysql-connector-java-3.0.10-stable-bin.jar 
ojdbc14.jar 

package com.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBUtil
{
	private Connection conn;//数据库的链接对象
	
	public DBUtil()
	{
		//加载驱动
		try
		{
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		}
	}
	
	//打开数据的链接
	public Connection openConnection()
	{
		try
		{
			conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "myspace", "myspace123");
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return conn;
	}
	
	//执行 INSERT DELETE UPDATE 的方法
	public boolean executeUpdate(String sql)
	{
		//打开数据库
		openConnection();
		//创建执行 sql 的对象
		try
		{
			Statement stmt=conn.createStatement();
			//执行sql 语句
			if(stmt.executeUpdate(sql)>0)
			{
				return true;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			DBClose();
		}
		
		return false;
	}
	
	//执行 sql 的 查找 SELECT 的方法
	public ResultSet executeQuery(String sql)
	{
		//打开数据库链接
		openConnection();
		//创建执行sql 的对象
		Statement stmt=null;
		ResultSet rs=null;
		try
		{
			stmt = conn.createStatement();
			rs=stmt.executeQuery(sql);
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		
		return rs;
		
	}
	
	//绑定的sql INSERT DELETE UPDATE 方法
	public boolean executeUpdate(String sql,Object[] params)
	{
		//打开数据库
		openConnection();
		//创建绑定对象
		try
		{
			PreparedStatement pstmt=conn.prepareStatement(sql);
			
			//填充 ？ 的值
			for(int i=0;params!=null&&i<params.length;i++)
			{
				if(params[i] instanceof Integer)
				{
					pstmt.setInt(i+1, Integer.parseInt(params[i].toString()));
					continue;
				}
				if(params[i] instanceof Float)
				{
					pstmt.setFloat(i+1, Float.parseFloat(params[i].toString()));
					continue;
				}
				if(params[i] instanceof Double)
				{
					pstmt.setDouble(i+1, Double.parseDouble(params[i].toString()));
					continue;
				}
				if(params[i] instanceof String)
				{
					pstmt.setString(i+1, params[i].toString());
				}
				
				//java.sql.Date,   java.util.Date
				if(params[i] instanceof java.util.Date)
				{
					//将 java.util.Date 转成  java.sql.Date
					java.util.Date javaDate=(java.util.Date)params[i];
					java.sql.Date sqlDate=new java.sql.Date(javaDate.getTime());
					pstmt.setDate(i+1, sqlDate);
					continue;
				}

				if(params[i] instanceof java.util.Timestamp)
				{
					//将 java.util.Date 转成  java.sql.Date
					java.util.Date javaDate=(java.util.Date)params[i];
					java.sql.Timestamp timestamp=new java.sql.Timestamp(javaDate.getTime());
					pstmt.setTimestamp(i+1, timestamp);
					continue;
				}
			}
			//执行 绑定的 sql 语句
			if(pstmt.executeUpdate()>0)
			{
				return true;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			DBClose();
		}
		
		return false;
	}
	
	//绑定的sql 查找 SELECT 方法
		public ResultSet executeQuery(String sql,Object[] params)
		{
			//打开数据库
			openConnection();
			//创建绑定对象
			try
			{
				PreparedStatement pstmt=conn.prepareStatement(sql);
				
				//填充 ？ 的值
				for(int i=0;params!=null&&i<params.length;i++)
				{
					if(params[i] instanceof Integer)
					{
						pstmt.setInt(i+1, Integer.parseInt(params[i].toString()));
						continue;
					}
					if(params[i] instanceof Float)
					{
						pstmt.setFloat(i+1, Float.parseFloat(params[i].toString()));
						continue;
					}
					if(params[i] instanceof Double)
					{
						pstmt.setDouble(i+1, Double.parseDouble(params[i].toString()));
						continue;
					}
					if(params[i] instanceof String)
					{
						pstmt.setString(i+1, params[i].toString());
					}
					
					//java.sql.Date,   java.util.Date
					if(params[i] instanceof java.util.Date)
					{
						//将 java.util.Date 转成  java.sql.Date
						java.util.Date javaDate=(java.util.Date)params[i];
						java.sql.Date sqlDate=new java.sql.Date(javaDate.getTime());
						pstmt.setDate(i+1, sqlDate);
						continue;
					}

					if(params[i] instanceof java.util.Timestamp)
					{
						//将 java.util.Date 转成  java.sql.Date
						java.util.Date javaDate=(java.util.Date)params[i];
						java.sql.Timestamp timestamp=new java.sql.Timestamp(javaDate.getTime());
						pstmt.setTimestamp(i+1, timestamp);
						continue;
					}
				}
				//执行 绑定的 sql 语句
				return pstmt.executeQuery();
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			
			
			return null;
		}
	
	//关闭数据库链接
	public void DBClose()
	{
		if(conn!=null)
		{
			try
			{
				conn.close();
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
	}
}





package com.sp.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

import com.db.DBUtil;

public class TestAdd
{
	public static void main(String[] args)
	{
		DBUtil db=new DBUtil();
		//打开数据库链接
		Connection conn=db.openConnection();
		//定义存储过程的 sql
		String sql="call pgk_dept.sp_add_dept(?,?)";
		//执行 sql 存储过程的对象
		try
		{
			CallableStatement cstmt=conn.prepareCall(sql);
			//绑定值
			cstmt.setInt(1, 1);
			cstmt.setString(2, "IT");
			//执行
			cstmt.executeUpdate();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			db.DBClose();
		}
	}
}





package com.sp.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

import com.db.DBUtil;

public class TestDelete
{
	public static void main(String[] args)
	{
		DBUtil db=new DBUtil();
		//打开数据库链接
		Connection conn=db.openConnection();
		//定义存储过程的 sql
		String sql="call pgk_dept.sp_delete_dept(?)";
		//执行 sql 存储过程的对象
		try
		{
			CallableStatement cstmt=conn.prepareCall(sql);
			//绑定值
			cstmt.setInt(1, 1);
			//执行
			cstmt.executeUpdate();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			db.DBClose();
		}
	}
}





package com.sp.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

import com.db.DBUtil;

public class TestUpdate
{
	public static void main(String[] args)
	{
		DBUtil db=new DBUtil();
		//打开数据库链接
		Connection conn=db.openConnection();
		//定义存储过程的 sql
		String sql="call pgk_dept.sp_update_dept(?,?)";
		//执行 sql 存储过程的对象
		try
		{
			CallableStatement cstmt=conn.prepareCall(sql);
			//绑定值
			cstmt.setInt(1, 1);
			cstmt.setString(2, "测试");
			//执行
			cstmt.executeUpdate();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			db.DBClose();
		}
	}
}





package com.sp.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.OracleTypes;

import com.db.DBUtil;

public class TestFind
{
	public static void main(String[] args)
	{
		DBUtil db=new DBUtil();
		//打开数据库链接
		Connection conn=db.openConnection();
		//定义存储过程的 sql
		String sql="call pgk_dept.sp_find_dept(?)";
		//执行 sql 存储过程的对象
		try
		{
			CallableStatement cstmt=conn.prepareCall(sql);
			//绑定值  设置传出来的参数
			cstmt.registerOutParameter(1, OracleTypes.CURSOR);
			//执行 存储过程才调用
			cstmt.executeUpdate();
			
			//获得游标参数
			ResultSet rs=(ResultSet)cstmt.getObject(1);
			
			while(rs.next())
			{
				int id=rs.getInt("id");
				String name=rs.getString("name");
				System.out.println(id+","+name);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			db.DBClose();
		}
	}
}

http://www.verejava.com/?id=16998480035641