前言
本文的目的在于解决web开发也好,系统开发也好那60%左右的单表增删改查的问题,
至于多表联查---相信我,自己手写sql比什么都强.
ps:我这里提一下,那就是类型转换的问题以及json取值问题
这是下面这些文章没有想过或者没有解决的问题.
1/类型转换问题
sql命令有几种默认转换关系.如果是timestamp,那么写 '2010-10-20'这种格式的字符或默认转换为timestamp日期,不需要额外转的.
但是,如果字段是 int,bigint,float,double,decimal这些数字,那么postgresql里面是不会自动将字符串转为对应类型,到时候会报错,说类型不对建议转换类型什么的.
2/json取值问题
在postgresql的存储过程里面可以传入json数据---json格式字符串也可以直接转为json数据,它有两种取值方式,
譬如对于:
{"word":"明珠斗士","createTime":1582081087851}
来说,
可以通过:
json->'createTime' 返回类型 json json->>'createTime' 返回类型 text
来取值,但是留意到没有?就是postgresql返回来的取值 没有做类型适配,但你要执行:
update table01 set "createTime"=json->'createTime'的时候
无论用哪一种取值方式都会提示错误的.所以,在写通用update存储过程时候必须要考虑到类型适配问题.
参考自:
https://www.cnblogs.com/ssqhan/p/7399789.html
其中该文章提及到一个存储过程:
create or replace function f_update_all( tablename text, update_feilds text, condition_feilds text, out return_value text ) as $$ declare ex_sql text; recs record; _key text ; _value text; begin ex_sql:='update '||quote_ident(tablename)||' set '; --setting values for updated table for recs in select * from json_array_elements(update_feilds::json) loop _key := recs.value ->> 'feild_name'; _value := recs.value ->> 'feild_value' ; if json_typeof(recs.value -> 'feild_value') ='number' then ex_sql:=ex_sql|| _key || '=' || _value ||','; else ex_sql:=ex_sql|| _key || '='''|| (recs.value ->> 'feild_value') || ''','; end if; end loop; ex_sql:= substring(ex_sql from 0 for length(ex_sql)); --setting condition in where ex_sql:=ex_sql||' where 1=1'; for recs in select * from json_array_elements(condition_feilds::json) loop _key := recs.value ->> 'feild_name'; _value := recs.value ->> 'feild_value' ; if json_typeof(recs.value -> 'feild_value') ='number' then ex_sql:=ex_sql|| ' and ' || _key || '=' || _value ||','; else ex_sql:=ex_sql|| ' and ' || _key || '='''|| (recs.value ->> 'feild_value') || ''','; end if; end loop; ex_sql:= substring(ex_sql from 0 for length(ex_sql)); return_value:=ex_sql; end; $$ language plpgsql;
重点是这个存储过程是用了jsonarray遍历然后获得字段名称以及字段值的,可以在此基础上进行改造.
参考2:
https://blog.csdn.net/kmblack1/article/details/82704844
里面提到的存储过程是:
drop function if exists exe_dynamic_sql(bigint); drop function if exists exe_dynamic_count(bigint); --返回记录集 create or replace function exe_dynamic_sql(ival bigint) returns table(objectid bigint,name varchar(128)) as $$ declare begin return query execute 'select objectid,name from dictionarys where parentid=$1 order by parentid,sort' using $1; end; $$ language plpgsql; --赋值给变量 create or replace function exe_dynamic_count(ival bigint) returns bigint as $$ declare v_count bigint; begin execute 'select count(*) from dictionarys where parentid=$1' using $1 into v_count; return v_count; end; $$ language plpgsql; --测试 select * from exe_dynamic_sql(26); select exe_dynamic_count(26);
可以看到这里是需要使用到参数 $1 这种形式,可以避免sql注入.
合并优化
下面就是通用的存储过程,上面都有注解,基本上这些都是在实际生产环境中使用过的,插入记录与更新记录的字段参数使用的是json格式的字符串数据,
想必对于任何一种语言都不是大问题, 而搜索出来的列表也是直接返回的是json格式的字符串数据,也符合规范.
当然,使用的是动态sql语句,在自定where条件语句搜索列表时候,不要作死使用用户输入的任何数据.
/*** * 单表crud通用存储过程 * 单表记录更新. * @param tablename 表名称 * @param updateparameter 需要更新的字段以及对应的value值,注意,请使用json格式字符串,例如:{"column1":"value4t1","column2":"value4t2","birthday":12548902} * @param whereCondition where 条件只能是内部人员使用,不能开放给客户端! 例子: where id= 1; * @param out status_code output类型数据,0 表示 失败,1表示成功,-1 表示发生异常. * @param out return_message output类型数据, 这是提示,譬如,当tableName为空的时候会提示"tableName不能为空" **/ create or replace function common_proc_update_record( tablename varchar(100), updateparameter varchar, -- 注意,直接使用json字符串--key-value形式即可,例如:{"column1":"value4t1","column2":"value4t2"} whereCondition varchar, -- where 条件只能是内部人员使用,不能开放给客户端! 例子: where id= 1; out status_code int ,-- 0 表示 失败,1表示成功,-1 表示发生异常. out return_message varchar -- ) as $$ declare ex_sql text; recs record; _key text ; declare _whereCondition varchar; declare _smallletter_where varchar; declare _paras varchar; declare _tableName varchar; declare item_key varchar; item_value json; loopIndex int; _tmpParaHolder varchar; _paras_values jsonb;-- 字段的值都放到这里. json_value_type varchar; begin -- 设置中国时区 set time zone 'PRC'; _paras_values:=(updateparameter)::jsonb; _whereCondition=coalesce(whereCondition,''); _whereCondition:=trim(_whereCondition); _paras:=trim(coalesce(updateparameter,'')); _tableName:=trim(coalesce(tablename,'')); if char_length(_tableName) <=0 then status_code:=-1; return_message:='未知表名!'; return; end if; -- 大小写敏感. -- --pg的字符串位置是从1开始的. -- if position('"' in _tableName) < 1 then -- _tableName:='"'||_tableName||'"'; -- end if; if _whereCondition is null or char_length(_whereCondition) <= 0 then status_code:=-1; return_message:='请明确需要更新哪一条数据记录!'; return; end if; if char_length(_paras) <=0 then status_code:=-1; return_message:='请明确需要更新的字段列表以及对应的值的json字符串.'; return; end if; _smallletter_where:=lower(_whereCondition); -- 检查一下where关键字是不是在条件语句,如果不在,就补充where 关键字. --pg的字符串位置是从1开始的. if position('where ' in _smallletter_where) <> 1 then _whereCondition:='where '||_whereCondition; end if; ex_sql:='update '||quote_ident(_tableName) || ' set '; loopIndex:=0; -- 好了,遍历json里面的键值对. for item_key,item_value in select * from json_each(updateparameter::json) loop loopIndex:=coalesce(loopIndex,0)+1; _key:=trim(item_key); json_value_type:=json_typeof(item_value); -- item_value 在使用->引用的时候是json/jsonb类型,在使用 ->>引用的时候是text类型, -- 无论哪一个直接用来update或者insert字段都会提示 错误,叫你cast转换类型的,下面就直接判断是什么类型,然后 -- 自动转换. -- 类型有 string, -- number 对应于:numeric -- ps:--json字符串最经常的就两种,string和数字,其他的以后看到了再考虑,譬如,数组如何处理. if position('"' in _key ) < 1 then _key:= format('"%s"',_key); -- '"'||_key||'"'; end if; if json_value_type='number' then _tmpParaHolder:= format('($1->>''%s'')::numeric' ,trim(item_key)); else _tmpParaHolder:= format('($1->>''%s'')' ,trim(item_key)); end if; -- '$'||cast(loopIndex as varchar);-- 参数的占位符号. ex_sql:=format('%s %s=%s,',ex_sql,_key,_tmpParaHolder); -- ex_sql:=ex_sql|| _key || '=' || _tmpParaHolder ||','; -- _tmpJsonItem:='{}'; -- _tmpJsonItem['value']:=(item_value); raise notice '已经将 相关 item value 读取出来了:%,类型是:%',item_value,json_typeof(item_value); end loop; -- 去掉最后一个逗号. ex_sql:=substr(ex_sql,0,char_length(ex_sql)); ex_sql:= ex_sql||' '|| _whereCondition; raise notice '动态sql是:%',ex_sql; -- raise notice '值的数组为:%',_paras_values; -- 执行sql -- dynamic_execute_with_paras:= 'execute ex_sql '; -- execute dynamic_execute_with_paras; execute ex_sql using _paras_values; -- exec 'execute ex_sql using _paras_values'; status_code:=1; return_message:=''; -- execute 'select count(*) from dictionarys where parentid=$1' using $1 into v_count; end; $$ language plpgsql; /*** * 单表crud通用存储过程 * 单表记录添加. * @param tablename 表名称 * @param insertparas 需要添加的字段以及对应的value值,注意,请使用json格式字符串,例如:{"column1":"value4t1","column2":"value4t2","birthday":12548902} * @param fetchRecordByAutoIncrementPrimaryKeyName 是否有自增主键,如果有自增主键譬如:userid,那么就将userid传过来, * --然后系统会在插入记录之后,试获取最近一条记录,在return_autopk_record会返回新插入记录的json格式字符串.如果没有的话就不进行处理. * @param out status_code output类型数据,0 表示 失败,1表示成功,-1 表示发生异常. * @param out return_message output类型数据, 这是提示,譬如,当tableName为空的时候会提示"tableName不能为空" * @param out return_autopk_record 在给定了自增主键以后,系统将尝试获取当前插入的自增主键对应的记录,然后json格式化赋值给该参数. **/ create or replace function common_proc_insert_record( tablename varchar(100), insertparas varchar, -- 注意,直接使用json字符串--key-value形式即可,例如:{"column1":"value4t1","column2":"value4t2"} fetchRecordByAutoIncrementPrimaryKeyName varchar,-- 是否有自增主键,如果有自增主键譬如:userid,那么就将userid传过来,然后系统会在插入记录之后 -- 尝试获取最近一条记录,在return_autopk_record会返回新插入记录的json格式字符串.如果没有的话就不进行处理. out status_code int ,-- 0 表示 失败,1表示成功,-1 表示发生异常. out return_message varchar, --, out return_autopk_record varchar -- ) as $$ declare ex_sql text; recs record; _key text ; declare _paras varchar; declare _tableName varchar; declare item_key varchar; item_value json; loopIndex int; _tmpParaHolder varchar; _paras_values jsonb;-- 字段的值都放到这里. json_value_type varchar; _arr_sql_columns varchar[]; _arr_sql_vals varchar[]; _auto_icr_pkey varchar; begin -- 设置中国时区. set time zone 'PRC'; _auto_icr_pkey:=trim(coalesce(fetchRecordByAutoIncrementPrimaryKeyName)); _paras_values:=(insertparas)::jsonb; _paras:=trim(coalesce(insertparas,'')); _tableName:=trim(coalesce(tablename,'')); if char_length(_tableName) <=0 then status_code:=-1; return_message:='未知表名!'; return; end if; -- 大小写敏感. -- --pg的字符串位置是从1开始的. -- if position('"' in _tableName) < 1 then -- _tableName:='"'||_tableName||'"'; -- end if; if char_length(_paras) <=0 then status_code:=-1; return_message:='请明确需要字段列表以及对应的值的json字符串.'; return; end if; ex_sql:='insert into '||quote_ident(_tableName) || ' '; loopIndex:=0; -- 好了,遍历json里面的键值对. for item_key,item_value in select * from json_each(insertparas::json) loop loopIndex:=coalesce(loopIndex,0)+1; _key:=trim(item_key); json_value_type:=json_typeof(item_value); -- item_value 在使用->引用的时候是json/jsonb类型,在使用 ->>引用的时候是text类型, -- 无论哪一个直接用来update或者insert字段都会提示 错误,叫你cast转换类型的,下面就直接判断是什么类型,然后 -- 自动转换. -- 类型有 string, -- number 对应于:numeric -- ps:--json字符串最经常的就两种,string和数字,其他的以后看到了再考虑,譬如,数组如何处理. if position('"' in _key ) < 1 then _key:= format('"%s"',_key); -- '"'||_key||'"'; end if; if json_value_type='number' then _tmpParaHolder:= format('($1->>''%s'')::numeric' ,trim(item_key)); else _tmpParaHolder:= format('($1->>''%s'')' ,trim(item_key)); end if; _arr_sql_columns:=_arr_sql_columns||(_key)::varchar; _arr_sql_vals:=_arr_sql_vals||(_tmpParaHolder)::VARCHAR; raise notice '已经将 相关 item value 读取出来了:%,类型是:%',item_value,json_typeof(item_value); end loop; raise notice '数组 columns:%',array_to_string(_arr_sql_columns,'|'); raise notice '数组 参数:%',array_to_string(_arr_sql_vals,'|'); ex_sql:=ex_sql||format(' (%s) ',array_to_string(_arr_sql_columns,',')); ex_sql:=ex_sql||format(' values (%s) ; ',array_to_string(_arr_sql_vals,',')); -- 去掉最后一个逗号. raise notice '动态sql是:%',ex_sql; execute ex_sql using _paras_values; -- 判断有没有影响行数 if FOUND then status_code:=1; return_message:='成功添加记录'; else status_code:=-1; return_message:='无法添加数据记录!'; return; end if; -- 判断是不是需要获取自增主键然后返回当前记录的json格式数据. if char_length(_auto_icr_pkey)>0 then if position('"' in _auto_icr_pkey) < 1 then _auto_icr_pkey:=format('"%s"',_auto_icr_pkey); end if; -- 获取自增数据的记录. -- currval(pg_get_serial_sequence('"s_praise"', 'praiseId')); -- rv_recordId:=currval(pg_get_serial_sequence('"s_msg"', 'msgId')); -- 好了,再构造一个动态语句. ex_sql=format('select row_to_json(t) from (select * from %s where %s=currval(pg_get_serial_sequence(''%s'', ''%s''))) t' ,quote_ident(_tableName) ,_auto_icr_pkey ,quote_ident(_tableName) ,trim(fetchRecordByAutoIncrementPrimaryKeyName) ); raise notice '获取自增记录的动态sql是:%',ex_sql; execute ex_sql into return_autopk_record; end if; end; $$ language plpgsql; /*** * 单表crud通用存储过程 * 单表条件搜索 * [按相等条件搜索] ps:这个存储过程用于搜索相同字段条件的记录列表,譬如: name='t1' id=5 通常用于确定的,包含有主键的记录. * @param tablename 表名称 * @param equalConditionJson 搜索条件,json格式字符串,例如:{"userId":21458},或者{"wxOpenId":"dfdfsdfdsfd"} 等,也可以同时传递多个条件. -- 条件为空字符串的话就是单纯搜索列表了 * @param orderByStr 排序条件语句. 例如: order by id desc 等等. 如果为空的话就不 * @param pageIndex 分页,页码,以1作为第一页 * @param pageSize 分页,每页限制多少条记录,默认为20. * @param out status_code output类型数据,0 表示 失败,1表示成功,-1 表示发生异常. * @param out return_message output类型数据, 这是提示,譬如,当tableName为空的时候会提示"tableName不能为空" * @param out total_records 当前条件下面总共有多少条记录 * @param out return_list_json 系统在搜索过程中将结果集序列化为json字符串,赋值给这个参数. **/ create or replace function common_proc_search_by_eqCnd( tablename varchar(100), equalConditionJson varchar, -- 注意,直接使用json字符串--key-value形式即可,例如:{"column1":"value4t1","column2":"value4t2"} orderByStr varchar, pageIndex int, pageSize int, out status_code int ,-- 0 表示 失败,1表示成功,-1 表示发生异常. out return_message varchar, --, out total_records int, out return_list_json varchar -- ) as $$ declare _key text ; declare _paras varchar; declare _tableName varchar; declare item_key varchar; item_value json; loopIndex int; _tmpParaHolder varchar; _paras_values jsonb;-- 字段的值都放到这里. json_value_type varchar; _eq_json varchar; _order_by varchar; _sql_select_count varchar; _sql_select_list varchar; _arr_column_cnd varchar[]; declare rv_offset int; declare rv_limit int; begin -- 设置中国时区. set time zone 'PRC'; rv_offset:=1; rv_limit:=20; _tableName:=trim(coalesce(tablename,'')); if char_length(_tableName) <=0 then status_code:=-1; return_message:='未知表名!'; return; end if; status_code:=1; return_message:=''; _eq_json:=trim(coalesce(equalConditionJson,'')); _order_by:=trim(coalesce(orderByStr)); _sql_select_count:= format('select count(*) from %s ',quote_ident(_tableName)); _sql_select_list:= format('select * from %s ',quote_ident(_tableName)); if char_length(_eq_json) > 0 then -- 包含了 where condition loopIndex:=0; for item_key,item_value in select * from json_each(_eq_json::json) loop loopIndex:=coalesce(loopIndex,0)+1; _key:=trim(item_key); json_value_type:=json_typeof(item_value); -- item_value 在使用->引用的时候是json/jsonb类型,在使用 ->>引用的时候是text类型, -- 无论哪一个直接用来update或者insert字段都会提示 错误,叫你cast转换类型的,下面就直接判断是什么类型,然后 -- 自动转换. -- 类型有 string, -- number 对应于:numeric -- ps:--json字符串最经常的就两种,string和数字,其他的以后看到了再考虑,譬如,数组如何处理. if position('"' in _key ) < 1 then _key:= format('"%s"',_key); -- '"'||_key||'"'; end if; if json_value_type='number' then _tmpParaHolder:= format('($1->>''%s'')::numeric' ,trim(item_key)); else _tmpParaHolder:= format('($1->>''%s'')' ,trim(item_key)); end if; _arr_column_cnd:=_arr_column_cnd|| format('%s=%s',_key,_tmpParaHolder)::varchar; raise notice '已经将 相关 item value 读取出来了:%,类型是:%',item_value,json_typeof(item_value); end loop; -- 将where 条件添加到后面. _sql_select_count:=_sql_select_count || ' where ' ||(array_to_string(_arr_column_cnd,' and ')::varchar); _sql_select_list:= _sql_select_list || ' where ' || (array_to_string(_arr_column_cnd,' and ')::varchar); end if; if char_length(_order_by)>0 then -- 如果有order by if position('order by ' in lower(_order_by)) < 1 then -- 开头并无order by 关键字,添加上去. _order_by:='order by '||_order_by; end if; -- _sql_select_list:=format() _sql_select_list:=_sql_select_list||' '||_order_by; end if; -- 计算分页. if pageSize is null or pageSize < 1 then rv_limit := 20; else rv_limit := pageSize; end if; if rv_limit > 1000 then rv_limit:=1000; end if; if pageIndex is null or pageIndex < 1 then rv_offset := 0; else rv_offset := (pageIndex - 1) * rv_limit; end if; -- sql list === select array_to_json(array_agg(row_to_json(___tbl_middle))) into list_result from ( xxxx limit 20 offset 0 ) as ___tbl_middle ; _sql_select_list:=format('select array_to_json(array_agg(row_to_json(___tbl_middle))) from ( %s limit %s offset %s ) as ___tbl_middle ;' ,_sql_select_list ,rv_limit ,rv_offset ); raise notice '获取总数量的的sql:%',_sql_select_count; raise notice '获取列表的的sql:%',_sql_select_list; execute _sql_select_count using (_eq_json)::json into total_records; execute _sql_select_list using (_eq_json)::json into return_list_json; end; $$ language plpgsql; /*** * 单表crud通用存储过程 * 单表条件搜索 * [自由构造条件语句进行搜索] ps:这个存储过程用里面的条件不能使用任何客户端输入的数据,切记了. * @param tablename 表名称 * @param whereCondition 搜索条件,自行构建语句: 譬如: where id=1 * @param orderByStr 排序条件语句. 例如: order by id desc 等等. 如果为空的话就不 * @param pageIndex 分页,页码,以1作为第一页 * @param pageSize 分页,每页限制多少条记录,默认为20. * @param out status_code output类型数据,0 表示 失败,1表示成功,-1 表示发生异常. * @param out return_message output类型数据, 这是提示,譬如,当tableName为空的时候会提示"tableName不能为空" * @param out total_records 当前条件下面总共有多少条记录 * @param out return_list_json 系统在搜索过程中将结果集序列化为json字符串,赋值给这个参数. **/ create or replace function common_proc_search_table( tablename varchar(100), whereCondition varchar, -- 注意,直接使用json字符串--key-value形式即可,例如:{"column1":"value4t1","column2":"value4t2"} orderByStr varchar, pageIndex int, pageSize int, out status_code int ,-- 0 表示 失败,1表示成功,-1 表示发生异常. out return_message varchar, --, out total_records int, out return_list_json varchar -- ) as $$ declare _key text ; declare _paras varchar; declare _tableName varchar; declare item_key varchar; item_value json; loopIndex int; _tmpParaHolder varchar; _paras_values jsonb;-- 字段的值都放到这里. json_value_type varchar; _whereCondition varchar; _order_by varchar; _sql_select_count varchar; _sql_select_list varchar; _arr_column_cnd varchar[]; declare rv_offset int; declare rv_limit int; begin -- 设置中国时区. set time zone 'PRC'; rv_offset:=1; rv_limit:=20; _tableName:=trim(coalesce(tablename,'')); if char_length(_tableName) <=0 then status_code:=-1; return_message:='未知表名!'; return; end if; status_code:=1; return_message:=''; _whereCondition:=trim(coalesce(whereCondition,'')); _order_by:=trim(coalesce(orderByStr)); _sql_select_count:= format('select count(*) from %s ',quote_ident(_tableName)); _sql_select_list:= format('select * from %s ',quote_ident(_tableName)); if char_length(_whereCondition) > 0 then -- 包含了 where condition if position('where ' in lower(_whereCondition)) < 1 then _whereCondition:='where '||whereCondition; end if; -- 将where 条件添加到后面. _sql_select_count:=_sql_select_count || _whereCondition; _sql_select_list:= _sql_select_list || _whereCondition; end if; if char_length(_order_by)>0 then -- 如果有order by if position('order by ' in lower(_order_by)) < 1 then -- 开头并无order by 关键字,添加上去. _order_by:='order by '||_order_by; end if; -- _sql_select_list:=format() _sql_select_list:=_sql_select_list||' '||_order_by; end if; -- 计算分页. if pageSize is null or pageSize < 1 then rv_limit := 20; else rv_limit := pageSize; end if; if rv_limit > 1000 then rv_limit:=1000; end if; if pageIndex is null or pageIndex < 1 then rv_offset := 0; else rv_offset := (pageIndex - 1) * rv_limit; end if; -- sql list === select array_to_json(array_agg(row_to_json(___tbl_middle))) into list_result from ( xxxx limit 20 offset 0 ) as ___tbl_middle ; _sql_select_list:=format('select array_to_json(array_agg(row_to_json(___tbl_middle))) from ( %s limit %s offset %s ) as ___tbl_middle ;' ,_sql_select_list ,rv_limit ,rv_offset ); raise notice '获取总数量的的sql:%',_sql_select_count; raise notice '获取列表的的sql:%',_sql_select_list; execute _sql_select_count into total_records; execute _sql_select_list into return_list_json; end; $$ language plpgsql;
测试
假设我们现在有一张表:
其中id是自增主键, word是需要禁用的关键词,createTime是bigint类型,时间戳.
好了,下面就是实际使用的效果----这里直接用sql工具,就不演示具体语言如何调用存储过程,如何获取 output的参数了.
添加记录---将会在添加完成时候,如果有指定自增主键,那么就会自动返回最近一次的添加记录.
在日志控制台上面可以看到日志记录,里面包含了动态构造的sql语句----注意,里面使用的是json参数, $1 就是 '{"word":"","createTIme":5455455}'这个参数.
查询刚才添加的记录,可以使用id=xxx,或者直接查询相等的条件的记录,如下:
可以看到返回的是数组形式的json字符串,里面的id是3,那么,根据这个id的条件来进行更新操作:
最后,获取所有记录:
一个很基本的增删改查通用存储过程就是这样,至于其他复杂的功能,譬如,postgresql里面如何添加数组之类的, 就要进一步优化了.