这里是一份经过再三调试测试而成功的postgres数据库单表crud存储过程，请注意，对于多结果的返回方式，请查看getPageByCondition的书写方式，用的是refcursor，返回一个cursor，同时可以返回其他out，inout参数，但是refcursor必须在事务中调用，所以java端的调用过程需要注意，好吧，我同时放出一份dal样板，大家可以直接copy来用。
/****************************************************************** * 表名：test3 * Made by 码农下的天桥 ******************************************************************/ --use MYDB;--你可以指定自己的数据库 /****************************************************************** ****************************各种常用查询*************************** ******************************************************************/ ------------------------------------ --用途：复杂形式的查询语句，用于查询分页数据。 --这个是泛用型的，假如你要根据用户输入去查询，那么最好不要用这个了， --以免出现sql注入。 --参数说明： ---_offset int 需要取的记录的开始位置 ---_limit int 需要获取记录的总条数，针对分页而言，就是分页的pagesize。 ---_columns varchar(800) 需要获取的字段 ---_where varchar(800) 需要过滤的条件譬如： where id<10 可以带where，不过建议不要带。 ---_orderby varchar(800) 需要进行排序的提交，譬如：order by id ---_totalCount int 返回总共记录条数。 ---_totalPages int 返回总共页数。 ------------------------------------ create or replace function test3_getListByCondition( INOUT pageindex INT,INOUT pagesize INT,IN _columns VARCHAR(800),IN _where VARCHAR(800),IN _orderby VARCHAR(800),out _totalCount INT,out _totalPages INT) returns SETOF record AS $$ DECLARE condition_columns VARCHAR(800); DECLARE condition_where varchar(800); DECLARE condition_orderby VARCHAR(800); DECLARE _dymatic_sql VARCHAR(1600); DECLARE _beginNO INT; DECLARE _dynamic_getCount varchar(1600); DECLARE _theOffset INT; DECLARE _tmpInt1 INT; BEGIN condition_where:=ltrim(rtrim(COALESCE(_where,''))); condition_orderby:=ltrim(rtrim(COALESCE(_orderby,'order by t3id'))); condition_columns:=ltrim(rtrim(COALESCE(_columns,'*'))); --分析传过来的参数，构造动态sql语句。 IF "character_length"(condition_where)>0 THEN IF strpos(condition_where,'where ')!=1 THEN condition_where:='where ' || condition_where; END IF; END IF; --order by 语句构造 IF "character_length"(condition_orderby)>0 THEN IF strpos(condition_orderby,'order ')!=1 THEN condition_orderby:='order by '||condition_orderby; END IF; END IF; --判断pageindex是否合法及pagesize是否合法 IF pageindex<1 THEN pageindex:=1; END IF; IF pagesize<1 THEN pagesize:=20; END IF; _dynamic_getCount:='select count(*) from test3 '||condition_where|| ' ' ; EXECUTE _dynamic_getCount INTO _totalCount; IF _totalCount<1 THEN pageindex:=1; RETURN; END IF; --计算总共页数 _tmpInt1:=_totalCount%pagesize; IF _tmpInt1=0 THEN _totalPages:=_totalCount / pagesize; ELSE _totalPages:=(_totalCount-_tmpInt1)/pagesize+1; END IF; IF _totalPages < pageindex then pageindex:=_totalPages; END IF; _theOffset:=(pageindex-1) * pagesize+1; _dymatic_sql:='select '||condition_columns||' from test3 '||condition_where||' '||condition_orderby||' limit '||pagesize||' '|| ' offset '||_theOffset||' '; --raise info '动态构造语句为：%',_dymatic_sql; return query EXECUTE _dymatic_sql; END; $$ language plpgsql VOLATILE; ------------------------------------ --用途：复杂形式的查询语句，用于查询多条记录数据。 --这个是泛用型的，假如你要根据用户输入去查询，那么最好不要用这个了， --以免出现sql注入。 --参数说明： ---_offset int 需要取的记录的开始位置 ---_limit int 需要获取记录的总条数，针对分页而言，就是分页的pagesize。 ---_columns varchar(800) 需要获取的字段 ---_where varchar(800) 需要过滤的条件譬如： where id<10 可以带where，不过建议不要带。 ---_orderby varchar(800) 需要进行排序的提交，譬如：order by id ---_totalCount int 返回总共记录条数。 ------------------------------------ create or replace function test3_getPageByCondition( INOUT pageindex INT,out _totalPages INT,out _refcursor refcursor ) returns SETOF record AS $$ DECLARE condition_columns VARCHAR(800); DECLARE condition_where varchar(800); DECLARE condition_orderby VARCHAR(800); DECLARE _dymatic_sql VARCHAR(1600); DECLARE _beginNO INT; DECLARE _dynamic_getCount varchar(1600); DECLARE _theOffset INT; DECLARE _tmpInt1 INT; BEGIN condition_where:=ltrim(rtrim(COALESCE(_where,_dymatic_sql; open _refcursor for EXECUTE _dymatic_sql; RETURN NEXT; END; $$ language plpgsql VOLATILE; ------------------------------------ --用途：获取其中一条记录 ------------------------------------ create or replace function test3_getRecord(in _id integer) returns SETOF test3 AS $$ BEGIN return query select * from test3 where t3id=_id LIMIT 1 OFFSET 0; END; $$ LANGUAGE plpgsql VOLATILE; ------------------------------------ --用途：复杂形式的查询语句，用于查询前面第几条记录，这个就相当好了 --这个是泛用型的，假如你要根据用户输入去查询，那么最好不要用这个了， --以免出现sql注入。 --参数说明： ---_topN int 需要取的topN条记录。 ---_columns varchar(800) 需要获取的字段 ---_where varchar(800) 需要过滤的条件譬如： where id<10 可以带where，不过建议不要带。 ---_orderby varchar(800) 需要进行排序的提交，譬如：order by id ------------------------------------ create or replace function test3_getTopNbyCondition(IN _topN int,IN _orderby VARCHAR(800)) returns SETOF test3 AS $$ DECLARE condition_columns VARCHAR(800); DECLARE condition_where varchar(800); DECLARE condition_orderby VARCHAR(800); DECLARE _dymatic_sql VARCHAR(1600); BEGIN condition_where:=ltrim(rtrim(COALESCE(_where,'*'))); --分析传过来的参数，构造动态sql语句。 IF "character_length"(condition_where)>0 THEN IF strpos(condition_where,'order ')!=1 THEN condition_orderby:='order by '||condition_orderby; END IF; END IF; _dymatic_sql:='select '||condition_columns||' from test2 '||condition_where||' '||condition_orderby||' limit '||CAST(_topN as VARCHAR)|| ' offset 0 '; --raise info '动态构造语句为：%',_dymatic_sql; return query EXECUTE _dymatic_sql; END; $$ language plpgsql VOLATILE; /****************************************************************** *****************************记录删除****************************** ******************************************************************/ ------------------------------------ --用途：删除多条记录 ------------------------------------ create or replace function test3_DeleteList(in ids VARCHAR(800),out status boolean,out msg VARCHAR(200)) returns record AS $$ DECLARE _arr_ids int[]; DECLARE _str_ids "text"; DECLARE _str_sql VARCHAR(1600); DECLARE _effects int; BEGIN IF "character_length"(ids)<1 THEN status:=false; msg:='没有指定需要删除的数据！'; return; end if; _arr_ids:=tools_str2intarray(ids,','); _str_ids:=tools_stringify(_arr_ids,'); --pkey为主键，自增的整数， <@ 表示判断pkey是不是在数组里面。是不是很方便？ /*动态构造执行*/ --_str_sql:='DELETE FROM test3 where t3id in ('||_str_ids||') ;'; --EXECUTE _str_sql; /*直接执行*/ delete from test3 where t3id =ANY( _arr_ids); GET DIAGNOSTICS _effects = ROW_COUNT; IF _effects>0 THEN status:=true; msg:='成功删除'||_effects||'条记录！'; ELSE status:=false; msg:='没有删除任何记录！'; end if; END $$ LANGUAGE plpgsql VOLATILE; /****************************************************************** ****************************添加及编辑***************************** ******************************************************************/ ------------------------------------ --用途：增加一条记录 ------------------------------------ create or replace function test3_Insert( in __t3name varchar(400),in __t_birthday date,in __myage smallint,in __isadmin boolean,in __myintro text,in __price float,out __t3id integer,out _status boolean,out _msg varchar(200)) returns record AS $$ BEGIN Insert into test3 ( "t3name","t_birthday","myage","isadmin","myintro","price" ) values( __t3name,__t_birthday,__myage,__isadmin,__myintro,__price ); /*判断添加记录是否成功。*/ if FOUND then _status:=true; _msg:='成功添加记录.'; __t3id:=currval(pg_get_serial_sequence('test3','t3id')); else _status:=false; _msg:='无法添加记录!'; end if; end; $$ LANGUAGE plpgsql VOLATILE; ------------------------------------ --用途：修改一条记录 ------------------------------------ create or replace function test3_Update( in __t3name varchar(400),in __t3id integer,out _msg varchar(200)) returns record AS $$ BEGIN update test3 set "t3name"=__t3name,"t_birthday"=__t_birthday,"myage"=__myage,"isadmin"=__isadmin,"myintro"=__myintro,"price"=__price where t3id=__t3id; /*判断保存记录是否成功。*/ if FOUND then _status:=true; _msg:='成功保存记录.'; else _status:=false; _msg:='无法保存记录!'; end if; end; $$ LANGUAGE plpgsql VOLATILE;
package EasisWeb.DAL; import EasisWeb.config.DBPool; import Easis.Common.StringUtil; import Easis.util.DataRow; import Easis.util.DataTable; import Easis.util.DataTableHelper; import java.util.Date; import Easis.DBUtility.PooledConnection; import java.sql.*; import java.util.List; import java.util.ArrayList; import Easis.util.OperationResult; import Easis.util.PagerResult; import EasisWeb.Model.test3Model; /** * 这是利用CodeGen工具生成的自动访问数据库的一个模板，作者为“码农下的天桥” *生成的类名称： * @author 码农下的天桥 * @version 1.00 */ public class test3DAL { /*表格各种column*/ public static final String col_t3id="test3"; public static final String col_t3name="test3"; public static final String col_t_birthday="test3"; public static final String col_myage="test3"; public static final String col_isadmin="test3"; public static final String col_myintro="test3"; public static final String col_price="test3"; public static final String PKColumn="t3id"; /** *存储过程名称：test3_ListByCondition *存储过程参数： *@param pageindex *@param pagesize *@param columns 需要获取的字段 *@param condition where条件语句 *@param orderColumn order by排序语句 * *@return 分页对象 */ public PagerResult getPageListByCondition(int pageindex,int pagesize,String columns,String condition,String orderColumn){ PagerResult pres=new PagerResult(); //output参数定义 int _total = 0 ; int _pagesize = 0 ; int _pageindex = 0 ; int _totalpages = 0 ; //output参数定义结束 //调用存储过程 DataTable res__datatable=new DataTable(); try{ PooledConnection __myconn=DBPool.getConnection(); __myconn.setAutoCommit(false); // return refcursor must within a transaction CallableStatement _stmt=__myconn.prepareCall("{ call test3_getPageByCondition( ?,?,?)}"); _stmt.setInt(1,pageindex); _stmt.setInt(2,pagesize); _stmt.registerOutParameter(1,Types.INTEGER); _stmt.registerOutParameter(2,Types.INTEGER); _stmt.setString(3,columns); _stmt.setString(4,condition); _stmt.setString(5,orderColumn); _stmt.registerOutParameter(6,Types.INTEGER); _stmt.registerOutParameter(7,Types.INTEGER); _stmt.registerOutParameter(8,Types.OTHER); _stmt.execute(); ResultSet __rslist=(ResultSet)_stmt.getObject(8); res__datatable=DataTableHelper.rs2datatable(__rslist); //取回参数 _total=_stmt.getInt(6); pres.totalrecords=_total; _pageindex=_stmt.getInt(1); pres.totalrecords=_total; _pagesize=_stmt.getInt(2); pres.pageindex=_pageindex; pres.pagesize=_pagesize; _totalpages=_stmt.getInt(7); pres.totalpages=_totalpages; pres.datasource=res__datatable; //--提交并还原 __myconn.commit(); __myconn.setAutoCommit(true); //返回游标必须在一个事务中，提交完以后将autocommit还原。 //释放资源 __rslist.close(); _stmt.close(); __myconn.close();} catch (Exception __e){ System.out.println("在运行[test3DAL]的List_Condition时候出现错误。"); __e.printStackTrace(); } return pres; } /** *存储过程名称：test3_Insert *存储过程参数： *param t3id 【主键】 t3name t_birthday myage isadmin myintro price * *@return */ public OperationResult Insert(test3Model model){ OperationResult __ores=new OperationResult(); /*output参数定义*/ int t3id = 0 ; DataRow returnInfo=new DataRow(); boolean status = false ; String message = "" ; /*output参数定义结束*/ /*调用存储过程*/ try{ PooledConnection __myconn=DBPool.getConnection(); CallableStatement _stmt=__myconn.prepareCall("{call test3_Insert(?,?)}"); _stmt.setObject(1,model.t3name,Types.VARCHAR); _stmt.setObject(2,new Timestamp(model.t_birthday.getTime()),Types.DATE); _stmt.setObject(3,model.myage,Types.SMALLINT); _stmt.setObject(4,model.isadmin,Types.BOOLEAN); _stmt.setObject(5,model.myintro,Types.VARCHAR); _stmt.setObject(6,model.price,Types.FLOAT); _stmt.registerOutParameter(7,Types.INTEGER,-1); _stmt.registerOutParameter(8,Types.BOOLEAN,1); _stmt.registerOutParameter(9,Types.VARCHAR,200); _stmt.execute(); /*取回参数*/ t3id=_stmt.getInt(7); status=_stmt.getBoolean(8); message=_stmt.getString(9); __ores.id= t3id; __ores.status=status; __ores.message=message; /*释放资源*/ _stmt.close(); __myconn.close();} catch (Exception __e){ __e.printStackTrace(); __ores.message=__e.toString(); } return __ores; } /** *存储过程名称：test3_Update *存储过程参数： * t3id【主键】 t3name t_birthday myage isadmin myintro price * *@return */ public OperationResult Update(test3Model model){ OperationResult __ores=new OperationResult(); /*output参数定义*/ boolean status = false ; String message = "" ; /*output参数定义结束*/ /*调用存储过程*/ DataTable res__datatable=new DataTable(); try{ PooledConnection __myconn=DBPool.getConnection(); CallableStatement _stmt=__myconn.prepareCall("{ call test3_Update( ?,?)}"); _stmt.setObject(1,Types.FLOAT); _stmt.setInt(7,model.t3id); _stmt.registerOutParameter(8,400); _stmt.execute(); /*取回参数*/ status=_stmt.getBoolean(8); message=_stmt.getString(9); __ores.status=status; __ores.message=message; /*释放资源*/ _stmt.close(); __myconn.close();} catch (Exception __e){ __e.printStackTrace(); } return __ores; } /** *存储过程名称：test3_DeleteList *存储过程参数： *@param ids 【参数名称：ids 参数类型:nvarchar 对应java类型：String 长度:400 】 * *@return */ public OperationResult DeleteList( String ids){ /*output参数定义*/ OperationResult __ores=new OperationResult(); boolean status = false ; String message = "" ; /*output参数定义结束*/ /*调用存储过程*/ DataTable res__datatable=new DataTable(); try{ PooledConnection __myconn=DBPool.getConnection(); CallableStatement _stmt=__myconn.prepareCall("{ call test3_DeleteList( ?,?)}"); _stmt.setString(1,ids); _stmt.registerOutParameter(2,1); _stmt.registerOutParameter(3,400); _stmt.execute(); /*取回参数*/ status=_stmt.getBoolean(2); message=_stmt.getString(3); __ores.status=status; __ores.message=message; /*释放资源*/ _stmt.close(); __myconn.close();} catch (Exception __e){ __e.printStackTrace(); } return __ores; } /** *存储过程名称：test3_GetRecord *存储过程参数： *@param t3id 【参数名称：id 参数类型:int 对应java类型：int 长度:非字符类型 】 * *@return DataTable对象。 */ public test3Model GetRecord( int t3id ){ /*调用存储过程*/ DataTable res__datatable=new DataTable(); test3Model model=new test3Model(); try{ PooledConnection __myconn=DBPool.getConnection(); CallableStatement _stmt=__myconn.prepareCall("{ call test3_GetRecord( ?)}"); _stmt.setInt(1,t3id); ResultSet __rslist =_stmt.executeQuery(); res__datatable=DataTableHelper.rs2datatable(__rslist); model=tryParseModel(res__datatable.get(0)); /*释放资源*/ __rslist.close(); _stmt.close(); __myconn.close();} catch (Exception __e){ __e.printStackTrace(); } return model; } /** *存储过程名称：test3_Top_Condition *存储过程参数： *@param topN 【参数名称：topN 参数类型:int 对应java类型：int 长度:非字符类型 】 *@param columns 【参数名称：columns 参数类型:nvarchar 对应java类型：String 长度:800 】 *@param condition 【参数名称：condition 参数类型:nvarchar 对应java类型：String 长度:800 】 *@param orderColumn 【参数名称：orderColumn 参数类型:nvarchar 对应java类型：String 长度:800 】 * *@return DataTable对象。 */ public DataTable Top_Condition( int topN,String orderColumn ){ /*调用存储过程*/ DataTable res__datatable=new DataTable(); try{ PooledConnection __myconn=DBPool.getConnection(); CallableStatement _stmt=__myconn.prepareCall("{ call test3_Top_Condition( ?,?)}"); _stmt.setInt(1,topN); _stmt.setString(2,columns); _stmt.setString(3,condition); _stmt.setString(4,orderColumn); ResultSet __rslist =_stmt.executeQuery(); res__datatable=DataTableHelper.rs2datatable(__rslist); /*释放资源*/ __rslist.close(); _stmt.close(); __myconn.close();} catch (Exception __e){ __e.printStackTrace(); } return res__datatable; } public test3Model tryParseModel(DataRow drow){ test3Model model=new test3Model(); if(drow==null){ return model; } /* return "boolean"; return "Date"; return "double"; return "float"; return "int"; return "long"; return "String"; return "Object"; */ /*尝试赋值*/ model.t3id = drow.get("t3id").toInt(); model.t3name = drow.get("t3name").toString(); model.t_birthday = drow.get("t_birthday").toDate(); model.myage = drow.get("myage").toInt(); model.isadmin = drow.get("isadmin").toBoolean(); model.myintro = drow.get("myintro").toString(); model.price = drow.get("price").toFloat(); return model; } public List<test3Model> tryParseList(List<DataRow> dataList){ List<test3Model> modellist=new ArrayList<test3Model>(); if(dataList==null){ return modellist; } for(DataRow drow :dataList){ modellist.add(tryParseModel(drow)); } return modellist; } }这只是一份模板而已。
