java+oracle的存储过程开发案例（包含了oracle存储过程的通用分页方法、java的工厂类）

深圳装修网

 --创建表
create table rights_menu_data  (
  id number(11) not null primary key,
  mid number(11) not null,
  pid number(11) not null,
  name nvarchar2(30) not null,
  layout number(1) not null,
  src nvarchar2(200) null,
  mgrsrc nvarchar2(200) null,
  del number(1) default 1 not null,
  release number(1) default 1 not null
);

 

--创建存储过程
create or replace package pkg_rights is
       -- Author : Keleesy
       -- Created : 2011-10-12
type ResultData is ref cursor;
  --添加菜单
procedure addMenu(
  p_pid rights_menu_data.pid%type,
  p_name rights_menu_data.name%type,
  p_layout rights_menu_data.layout%type,
  p_src rights_menu_data.src%type,
  p_mgrsrc rights_menu_data.mgrsrc%type,
  p_mid out number
);
  --更新菜单
procedure updateMenu(
  p_mid rights_menu_data.mid%type,
  p_pid rights_menu_data.pid%type,
  p_name rights_menu_data.name%type,
  p_layout rights_menu_data.layout%type,
  p_del rights_menu_data.del%type,
  p_release rights_menu_data.release%type,
  p_src rights_menu_data.src%type,
  p_mgrsrc rights_menu_data.mgrsrc%type
);

  --按id(通用)查询
procedure findDataByid(entity nvarchar2,mid number,p_OutCursor out ResultData);

  --(通用)根据实体名，实体id名,实体id值和del值进行删除或恢复数据
procedure delreByid(entity nvarchar2,mid number,del int);

  --(通用)分页查询
procedure sp_Page(
  p_PageSize int,--每页记录数
  p_PageNo int, --当前页码,从 1 开始                 
  p_SqlSelect varchar2,    --查询语句,含排序部分                 
  p_SqlCount varchar2,     --获取记录总数的查询语句                 
  p_OutRecordCount out int,--返回总记录数                 
  p_OutCursor out ResultData
);
end pkg_rights;

/

 

create or replace package body pkg_rights is
  --添加菜单
procedure addMenu(
  p_pid rights_menu_data.pid%type,
  p_name rights_menu_data.name%type,
  p_layout rights_menu_data.layout%type,
  p_src rights_menu_data.src%type,
  p_mgrsrc rights_menu_data.mgrsrc%type,
  p_mid out number
)as
begin
  select rights_menu_id_seq.nextval into p_mid from dual;
  insert into rights_menu_data values (p_mid,p_mid,p_pid,p_name,p_layout,p_src,p_mgrsrc,1,1);
end addMenu;
  --更新菜单

procedure updateMenu(
  p_mid rights_menu_data.mid%type,
  p_pid rights_menu_data.pid%type,
  p_name rights_menu_data.name%type,
  p_layout rights_menu_data.layout%type,
  p_del rights_menu_data.del%type,
  p_release rights_menu_data.release%type,
  p_src rights_menu_data.src%type,
  p_mgrsrc rights_menu_data.mgrsrc%type
)as
begin
  update rights_menu_data m set m.pid=p_pid,m.name=p_name,m.layout=p_layout,m.del=p_del,m.release=p_release,m.src=p_src,m.mgrsrc=p_mgrsrc where m.mid=p_mid;
end updateMenu;
  --根据实体名,id和del值进行删除或恢复数据
procedure delreByid(entity nvarchar2,mid number,del int)is
  v_sql varchar2(1000);
  begin
    v_sql:='update '||entity||' set del ='|| del ||'where mid ='||mid;
    execute immediate v_sql;
end delreByid;
  --按id通用查询
procedure findDataByid(entity nvarchar2,mid number,p_OutCursor out ResultData)as
v_sql varchar2(1000);
begin
  v_sql:='select * from '||entity||' where mid = '||mid||' and del=1';
  open p_OutCursor for v_sql;   
end findDataByid;

  --通用分页查询
procedure sp_Page(
  p_PageSize int,--每页记录数
  p_PageNo int, --当前页码,从 1 开始                 
  p_SqlSelect varchar2,    --查询语句,含排序部分                 
  p_SqlCount varchar2,     --获取记录总数的查询语句                 
  p_OutRecordCount out int,--返回总记录数                 
  p_OutCursor out ResultData
) as     
v_count int;     
v_heiRownum int;     
v_lowRownum int;
v_sql varchar2(3000);  
begin    ----取记录总数   
execute immediate p_SqlCount into v_count;   
p_OutRecordCount := v_count;    ----执行分页查询   
v_heiRownum := p_PageNo * p_PageSize;  --第p_PageNo页的最后一条记录
v_lowRownum := v_heiRownum - p_PageSize +1;  --第p_PageNo页的第一条记录 
v_sql := 'SELECT B.* FROM (SELECT A.*, rownum rn FROM ('|| p_SqlSelect ||') A ) B WHERE B.rn <= '|| v_heiRownum ||' and B.rn >= ' || v_lowRownum;   
OPEN p_OutCursor FOR  v_sql; 
end sp_Page;
end pkg_rights;

 

--java代码
package sini.dal.rights.impl;

import sini.dal.rights.IBase;

public class ImplFactory {
 /**
  * 根据java反射获得IBase的对象，工厂类
  * @param className
  * @return 一个类的接口
  * @throws InstantiationException
  * @throws IllegalAccessException
  * @throws ClassNotFoundException
  */
 public static IBase getInstance(String className)throws InstantiationException, IllegalAccessException, ClassNotFoundException {
  return (IBase)Class.forName(className).newInstance();
 }
}

 

package sini.dal.rights;

public interface IBase {

}

package sini.dal.rights.impl;

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

public class BaseImpl {
 public Connection conn=null;
 public CallableStatement sta=null;
 public ResultSet rs=null;
 /**
  *
  * @throws SQLException
  */
 public void close()throws SQLException{
  if(conn!=null){
   conn.close();
   conn=null;
  }
  if(sta!=null){
   sta.close();
   sta=null;
  }
  if(rs!=null){
   rs.close();
   rs=null;
  }
 }
}

 

package sini.dal.rights;

import java.util.List;

import sini.model.rights.MenuBean;

public interface MenuDao extends IBase{
    /**
     *
     * @param menu
     * @return
     * @throws Exception
     */
    public int add(MenuBean menu)throws Exception;
   
    /**
     *
     * @param menu
     * @return
     * @throws Exception
     */
    public boolean update(MenuBean menu)throws Exception;
   /**
    *
    * @param entity
    * @param id
    * @return
    * @throws Exception
    */
    public MenuBean findById(String entity,int id)throws Exception;
    /**
     *
     * @param pagesize(每页记录数)
     * @param pageNow(当前页码)
     * @param sqlselect(查询语句,含排序部分)
     * @param sqlCount(获取记录总数的查询语句)
     * @return
     * @throws Exception
     */
    public List<MenuBean> menupage(int pagesize,int pageNow,String sqlselect,String sqlCount)throws Exception;
}

package sini.dal.rights.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import oracle.jdbc.OracleTypes;
import sini.conn.rights.DbConnection;
import sini.dal.rights.MenuDao;
import sini.model.rights.MenuBean;

public class MenuDaoImpl extends BaseImpl implements MenuDao{

 public int add(MenuBean menu)throws Exception{
  int menuid=0;
  try{
   conn=DbConnection.getConnection();
   sta=conn.prepareCall("{call pkg_rights.addMenu(?,?,?,?,?,?)}");
   sta.setLong(1, menu.getPid());
   sta.setString(2, menu.getName());
   sta.setShort(3, menu.getLayout());
   sta.setString(4, menu.getSrc());
   sta.setString(5, menu.getMgrsrc());
   sta.registerOutParameter(6, OracleTypes.INTEGER);
   sta.execute();
   menuid=sta.getInt(6);
  }catch(Exception e){
   e.printStackTrace();
  }finally{
   close();
  }
  return menuid;
 }

 public MenuBean findById(String entity,int id)throws Exception{
  MenuBean menu=new MenuBean();
  try{
   conn=DbConnection.getConnection();
   sta=conn.prepareCall("{call pkg_rights.findDataByid(?,?,?)}");
   sta.setString(1, entity);
   sta.setInt(2, id);
   sta.registerOutParameter(3, OracleTypes.CURSOR);
   sta.execute();
   rs=(ResultSet)sta.getObject(3);
   while(rs.next()){
    menu.setMid(rs.getLong(2));
    menu.setPid(rs.getLong(3));
    menu.setName(rs.getString(4));
    menu.setLayout(rs.getShort(5));
    menu.setSrc(rs.getString(6));
    menu.setMgrsrc(rs.getString(7));
    menu.setDel(rs.getShort(8));
    menu.setRelease(rs.getShort(9));
   }
  }catch(Exception e){
   e.printStackTrace();
  }finally{
   close();
  }
  return menu;
 }

 public boolean update(MenuBean menu)throws Exception{
  boolean flag=false;
  try{
   conn=DbConnection.getConnection();
   sta=conn.prepareCall("{call pkg_rights.updateMenu(?,?,?,?,?,?,?,?)}");
   sta.setLong(1, menu.getMid());
   sta.setLong(2,menu.getPid());
   sta.setString(3, menu.getName());
   sta.setShort(4, menu.getLayout());
   sta.setShort(5, menu.getDel());
   sta.setShort(6, menu.getRelease());
   sta.setString(7, menu.getSrc());
   sta.setString(8, menu.getMgrsrc());
   sta.execute();
   flag=true;
  }catch(Exception e){
   e.printStackTrace();
  }finally{
   close();
  }
  return flag;
 }

 public List<MenuBean> menupage(int pagesize, int pageNow, String sqlselect,
   String sqlCount) throws Exception {
  List<MenuBean> list=new ArrayList<MenuBean>();
  int count=0;
  try{
   conn=DbConnection.getConnection();
   sta=conn.prepareCall("{call pkg_rights.sp_Page(?,?,?,?,?,?)}");
   sta.setInt(1,pagesize);
   sta.setInt(2,pageNow);
   sta.setString(3, sqlselect);
   sta.setString(4,sqlCount);
   sta.registerOutParameter(5, OracleTypes.INTEGER);
   sta.registerOutParameter(6, OracleTypes.CURSOR);
   sta.execute();
   count=sta.getInt(5);
   rs=(ResultSet)sta.getObject(6);
   while(rs.next()){
    MenuBean menu=new MenuBean();
    menu.setMid(rs.getLong(2));
    menu.setPid(rs.getLong(3));
    menu.setName(rs.getString(4));
    menu.setLayout(rs.getShort(5));
    menu.setSrc(rs.getString(6));
    menu.setMgrsrc(rs.getString(7));
    menu.setDel(rs.getShort(8));
    menu.setRelease(rs.getShort(9));
    list.add(menu);
   }
  }catch(Exception e){
   e.printStackTrace();
  }finally{
   close();
  }
  return list;
 }

}

 

//测试类
package test;

import java.util.List;

import org.junit.Test;

import sini.dal.rights.GeneralDao;
import sini.dal.rights.MenuDao;
import sini.dal.rights.impl.GeneralDaoImpl;
import sini.dal.rights.impl.ImplFactory;
import sini.dal.rights.impl.MenuDaoImpl;
import sini.model.rights.MenuBean;

public class MenuDaoImplTest {
 @Test
 public void testadd()throws Exception{
  MenuBean menu=new MenuBean((long)0,(long)-1,"权限管理",(short)1,"http://www.baidu.com","http://www.hao123.com");
  Dao().add(menu);
 }
 @Test
 public void testupdate()throws Exception{
  MenuBean menu=Dao().findById("rights_menu_data",22);
  if(menu!=null){
   menu.setName("权限管理1");
   menu.setRelease((short)1);
  }
  Dao().update(menu);
 }
 @Test
 public void testdelre()throws Exception{
  GeneralDao general=new GeneralDaoImpl();
  general.delre("rights_menu_data", 22, 0);
 }
 @Test
 public void testfindByid()throws Exception{
  MenuBean menu=Dao().findById("rights_menu_data", 2);
  if(menu!=null)
   System.out.println(menu.getMid()+","+menu.getPid()+","+menu.getName()+","+menu.getSrc()+","+menu.getMgrsrc());
 }
 @Test
 public void testmenupage()throws Exception{
  String sqlselect="select * from rights_menu_data order by id";
  String sqlCount="select count(*) from rights_menu_data";
  List<MenuBean> list=Dao().menupage(2, 1, sqlselect, sqlCount);
  if(list.size()>0){
   for(int i=0;i<list.size();i++)
    System.out.println(list.get(i).getName()+","+list.get(i).getMgrsrc());
  }
 }
 public static MenuDao Dao()throws Exception{
  return (MenuDao)ImplFactory.getInstance(MenuDaoImpl.class.getName());
 }
}