本文详细介绍了一种基于PostgreSQL的数据库设计方法，包括表结构、索引创建、存储过程编写等，旨在提高数据处理效率和并发性能。通过具体实例展示了如何使用存储过程实现CRUD操作、分页查询、业务逻辑处理等功能。
数据库设计
那么，转换为sql语句就是：
注意，都用的是pg数据库
create or replace function global_temp_update_database()
-- returns integer as
returns text as
$body$
declare
DECLARE sql_select_max varchar(400);
starts_with_val int:=1;
sql_create_sequence varchar(600);
debug_str varchar(800):='';
sequence_name varchar(60):='';
sql_bind_sequence varchar(800):='';
sp_sequence_name varchar(80):='';
BEGIN
-- 山寨 ins 模块。
CREATE TABLE if not EXISTS "ins_post" (
"id" serial primary key,
"publish_time" timestamp (0) without time zone,
"uid" integer null,
"title" varchar(200) not null ,
"view_num" integer null default 0 ,
"like_num" integer null default 0 ,
"images" varchar(250)[] null,
"description" varchar(600),
CONSTRAINT "FKInstPost2Member" FOREIGN KEY (uid) REFERENCES member(id)
MATCH SIMPLE ON UPDATE CASCADE ON DELETE SET NULL
);
BEGIN
create index "InsPostPublishIndex" on ins_post(publish_time);
EXCEPTION
WHEN others THEN
RAISE EXCEPTION '(%)', SQLERRM;
END
COMMENT ON TABLE "ins_post" IS '【ins模块】帖子列表';
COMMENT ON COLUMN "ins_post"."publish_time" IS '帖子发布时间，注意，一旦发布通常发布时间是不会变更的。';
COMMENT ON COLUMN "ins_post"."view_num" IS '浏览人数';
COMMENT ON COLUMN "ins_post"."like_num" IS '点赞人数';
CREATE TABLE if not EXISTS "ins_like" (
"uid" integer not null,
"post_id" integer not null,
"status" boolean not null default true,
"create_time" timestamp (0) without time zone not null,
CONSTRAINT "FKInsLike2Member" FOREIGN KEY ("uid") REFERENCES member(id)
MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ,
CONSTRAINT "FKInsLike2InsPost" FOREIGN KEY ("post_id") REFERENCES ins_post(id)
MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ,
);
BEGIN
create unique index "InsLikeUniqueCondition" on ins_like(post_id,uid);
EXCEPTION
WHEN others THEN
RAISE EXCEPTION '(%)', SQLERRM;
END
BEGIN
create index "InsLikeIndex4PostIdAndStatus" on ins_like(post_id,status);
EXCEPTION
WHEN others THEN
RAISE EXCEPTION '(%)', SQLERRM;
END
COMMENT ON TABLE "ins_like" IS '【ins模块】点赞表';
COMMENT ON COLUMN "ins_like"."status" IS '点赞状态，true表示点赞，false表示取消点赞';
return 'Done';
end
$body$
language plpgsql;
在数据库里面执行成功
骨架代码
好了，为了加快进度，特意生成了两张表的crud----大家都这样的，对吧，
生成的代码有控制器，模型，view object，存储过程，service代码以及条件查询condition当然还有后台的view界面。
为了简单说明，将模型存储过程及service的代码作一番说明。
sql存储过程：
ins post表
-- 当前数据库脚本、存储过程、视图均为自动工具生成，替换之前请确认是否已经做出改动了。
/***分页存储过程，高性能版本***/
CREATE OR REPLACE FUNCTION public."sp_ins_ins_post_pager"(
in pageindex integer,
in pagesize integer,
in cnd_json varchar,--条件复合对象，必须为json格式。
out outline_result varchar,
out list_result varchar
)
RETURNS record
AS $BODY$
declare pagerOutLine sys_type_pager_outline;
declare totalSize integer;
declare tmpStr varchar(400);
declare json_condition json;
declare cnd_id_eq integer;
begin
list_result:='[]';
-- 组装条件语句
json_condition:='{}'::json;
if cnd_json is not null and char_length(cnd_json)>0 then
json_condition:=cnd_json::json;
end if;
--
-- cnd_id_eq:=json_extract_path(json_condition,'cnd_id_eq'); json_extract_path返回的是json对象，不能这样用的
cnd_id_eq:=json_condition->>'cnd_id_eq';
if cnd_id_eq=0 then
cnd_id_eq:=null;
end if;
-- 首先获取记录总数
select count(*) into totalSize from
/**中间表，begin**/
(
select * from "ins_post"
where 1=1
and
case when cnd_id_eq is not null then "id"=cnd_id_eq else 1=1 end
-- 请自行条件条件语句。
-- and
-- case when cnd_name_like is not null and char_length(cnd_name_like) > 0
-- then "name" LIKE CONCAT('%',cnd_name_like,'%') else 1=1 end
order by "id" desc
) tbl_middle
/**中间表，end**/
;
-- 然后计算beginIndex，totalPages
pagerOutLine:=sys_func_cal_pager(pageIndex,pageSize,totalSize);
-- raise notice '好了，看看pagerOutline的数据：';
-- raise notice '看看pagerOutLine里面的数据：%，真奇怪，那么，totalSize 是：%',pagerOutLine,totalSize;
-- 定义返回的状态。
pagerOutLine."state":=true;
pagerOutLine."stateCode":=0;
pagerOutLine."message":='';
-- 返回相关数据。
select row_to_json(t) into outline_result from
(select pagerOutLine."state",
pagerOutLine."stateCode",
pagerOutLine."pageIndex",
pagerOutLine."pageSize",
pagerOutLine.total,
pagerOutLine."totalPages",
pagerOutLine."message") t;
select array_to_json(array_agg(row_to_json(tbl_middle))) into list_result from
/**中间表，begin**/
(
/**中间表定义，begin**/
select * from "ins_post"
where 1=1
and
case when cnd_id_eq is not null then "id"=cnd_id_eq else 1=1 end
-- 请自行条件条件语句。
-- and
-- case when cnd_name_like is not null and char_length(cnd_name_like) > 0
-- then "name" LIKE CONCAT('%',cnd_name_like,'%') else 1=1 end
order by "id" desc
/**中间表定义，end**/
limit pagerOutLine."pageSize" offset pagerOutLine."beginIndex"
) tbl_middle
/**中间表，end**/
;
-- 有可能找不到记录，这时候就返回空数组。
if list_result is null or char_length (list_result) < 1 then
list_result:='[]';
end if;
end;
$BODY$ LANGUAGE plpgsql volatile;
-- 存储过程 单独一条数据
CREATE OR REPLACE FUNCTION "sp_ins_ins_post_fetch"(
in id integer,
out model_json varchar
)
RETURNS varchar
AS $BODY$
declare tmpId integer;
declare json_result varchar;
begin
tmpId=id;
json_result:='{}'::json;
select ((row_to_json(t))) into model_json from (select * from "ins_post" where "ins_post"."id"=tmpId) t;
end;
$BODY$ LANGUAGE plpgsql volatile;
/***添加记录***/
-- 存储过程 添加记录
CREATE OR REPLACE FUNCTION "sp_ins_ins_post_insert"(
para_publish_time timestamp(0) without time zone,
para_uid integer,
para_title character varying(200),
para_view_num integer,
para_like_num integer,
-- (-_-) 注意，这个字段是数组来的，然而jdbc没有数组支持，这里先用varchar代替，在调用时候将原本的数组转换一下即可。
para_images varchar,
para_description character varying(600),
out op_result varchar
)
RETURNS varchar
AS $BODY$
declare tmpId integer;
declare tmpId_str varchar;
declare opResult sys_type_operation_outline;
declare logic_allow boolean;
declare c_v_publish_time timestamp(0) without time zone;
declare c_v_uid integer;
declare c_v_title character varying(200);
declare c_v_view_num integer;
declare c_v_like_num integer;
-- (-_-) 注意，这个字段是数组来的，然而jdbc没有数组支持，这里先用varchar代替，在调用时候将原本的数组转换一下即可。
declare c_v_images character varying(250)[];
declare c_v_description character varying(600);
begin
logic_allow:=false;
-- 请先打开相关操作。
if logic_allow = false then
opResult.message:=config('errors','logic_not_allow','message');
opResult."stateCode":=config('errors','logic_not_allow','code');
opResult."state":=false;
select row_to_json(t) into op_result from ( select opResult.state,opResult."stateCode",opResult.message ) t;
return;
end if;
opResult.state:=false;
opResult."stateCode":=0;
opResult.message:='';
-- 正式的参数赋值
c_v_publish_time:=para_publish_time;
c_v_uid:=para_uid;
c_v_title:=para_title;
c_v_view_num:=para_view_num;
c_v_like_num:=para_like_num;
c_v_images := string_to_array(para_images, ',')::character varying(250)[];
c_v_description:=para_description;
INSERT INTO "ins_post"(
-- 主键不插入记录。
"publish_time"
, "uid"
, "title"
, "view_num"
, "like_num"
, "images"
, "description"
)
VALUES (
c_v_publish_time
, c_v_uid
, c_v_title
, c_v_view_num
, c_v_like_num
, c_v_images
, c_v_description
);
if FOUND then
opResult.message:='保存成功';
opResult."stateCode":=0;
opResult."state":=true;
else
opResult.message:='保存失败';
opResult."stateCode":=0;
opResult."state":=false;
end if;
if opResult.state=true then
select row_to_json(t) into op_result from ( select opResult.state,opResult."stateCode",opResult.message,currval(pg_get_serial_sequence('ins_post', 'id')) as data ) t;
else
select row_to_json(t) into op_result from ( select opResult.state,opResult."stateCode",opResult.message ) t;
end if;
end;
$BODY$ LANGUAGE plpgsql volatile;
/***更新记录***/
-- 存储过程 更新记录
CREATE OR REPLACE FUNCTION "sp_ins_ins_post_update"(
para_publish_time timestamp(0) without time zone,
para_uid integer,
para_title character varying(200),
para_view_num integer,
para_like_num integer,
-- (-_-) 注意，这个字段是数组来的，然而jdbc没有数组支持，这里先用varchar代替，在调用时候将原本的数组转换一下即可。
para_images varchar,
para_description character varying(600),
para_id integer,
out op_result varchar
)
RETURNS varchar
AS $BODY$
declare tmpId integer;
declare tmpId_str varchar;
declare opResult sys_type_operation_outline;
declare logic_allow boolean;
declare record_primary_key integer;
declare c_v_publish_time timestamp(0) without time zone;
declare c_v_uid integer;
declare c_v_title character varying(200);
declare c_v_view_num integer;
declare c_v_like_num integer;
-- (-_-) 注意，这个字段是数组来的，然而jdbc没有数组支持，这里先用varchar代替，在调用时候将原本的数组转换一下即可。
declare c_v_images character varying(250)[];
declare c_v_description character varying(600);
begin
logic_allow:=false;
-- 请先打开相关操作。
if logic_allow = false then
opResult.message:=config('errors','logic_not_allow','message');
opResult."stateCode":=config('errors','logic_not_allow','code');
opResult."state":=false;
select row_to_json(t) into op_result from ( select opResult.state,opResult."stateCode",opResult.message ) t;
return;
end if;
opResult.state:=false;
opResult."stateCode":=0;
opResult.message:='';
-- 正式的参数赋值
record_primary_key:=para_id;
c_v_publish_time:=para_publish_time;
c_v_uid:=para_uid;
c_v_title:=para_title;
c_v_view_num:=para_view_num;
c_v_like_num:=para_like_num;
c_v_images := string_to_array(para_images, ',')::character varying(250)[];
c_v_description:=para_description;
update "ins_post" set
-- 主键不插入记录。
"publish_time"=c_v_publish_time
, "uid"=c_v_uid
, "title"=c_v_title
, "view_num"=c_v_view_num
, "like_num"=c_v_like_num
, "images"=c_v_images
, "description"=c_v_description
where "id"=record_primary_key
;
if FOUND then
opResult.message:='保存成功';
opResult."stateCode":=0;
opResult."state":=true;
else
opResult.message:='保存失败';
opResult."stateCode":=0;
opResult."state":=false;
end if;
if opResult.state=true then
select row_to_json(t) into op_result from ( select opResult.state,opResult."stateCode",opResult.message,record_primary_key as data ) t;
else
select row_to_json(t) into op_result from ( select opResult.state,opResult."stateCode",opResult.message ) t;
end if;
end;
$BODY$ LANGUAGE plpgsql volatile;
/***删除记录***/
-- 存储过程 删除记录
CREATE OR REPLACE FUNCTION "sp_ins_ins_post_delete"(
para_id integer,
out op_result varchar
)
RETURNS varchar
AS $BODY$
declare tmpId integer;
declare tmpId_str varchar;
declare opResult sys_type_operation_outline;
declare logic_allow boolean;
declare record_primary_key integer;
begin
logic_allow:=false;
-- 请先打开相关操作。
if logic_allow = false then
opResult.message:=config('errors','logic_not_allow','message');
opResult."stateCode":=config('errors','logic_not_allow','code');
opResult."state":=false;
select row_to_json(t) into op_result from ( select opResult.state,opResult."stateCode",opResult.message ) t;
return;
end if;
opResult.state:=false;
opResult."stateCode":=0;
opResult.message:='';
-- 正式的参数赋值
record_primary_key:=para_id;
delete from "ins_post"
where "ins_post"."id"=record_primary_key
;
if FOUND then
opResult.message:='删除成功';
opResult."stateCode":=0;
opResult."state":=true;
else
opResult.message:='删除失败';
opResult."stateCode":=0;
opResult."state":=false;
end if;
if opResult.state=true then
select row_to_json(t) into op_result from ( select opResult.state,opResult."stateCode",opResult.message,record_primary_key as data ) t;
else
select row_to_json(t) into op_result from ( select opResult.state,opResult."stateCode",opResult.message ) t;
end if;
end;
$BODY$ LANGUAGE plpgsql volatile;
ins like 表
-- 当前数据库脚本、存储过程、视图均为自动工具生成，替换之前请确认是否已经做出改动了。
/***分页存储过程，高性能版本***/
CREATE OR REPLACE FUNCTION public."sp_ins_ins_like_pager"(
in pageindex integer,
in pagesize integer,
in cnd_json varchar,--条件复合对象，必须为json格式。
out outline_result varchar,
out list_result varchar
)
RETURNS record
AS $BODY$
declare pagerOutLine sys_type_pager_outline;
declare totalSize integer;
declare tmpStr varchar(400);
declare json_condition json;
begin
list_result:='[]';
-- 组装条件语句
json_condition:='{}'::json;
if cnd_json is not null and char_length(cnd_json)>0 then
json_condition:=cnd_json::json;
end if;
--
-- 首先获取记录总数
select count(*) into totalSize from
/**中间表，begin**/
(
select * from "ins_like"
where 1=1
-- 请自行条件条件语句。
-- and
-- case when cnd_name_like is not null and char_length(cnd_name_like) > 0
-- then "name" LIKE CONCAT('%',cnd_name_like,'%') else 1=1 end
) tbl_middle
/**中间表，end**/
;
-- 然后计算beginIndex，totalPages
pagerOutLine:=sys_func_cal_pager(pageIndex,pageSize,totalSize);
-- raise notice '好了，看看pagerOutline的数据：';
-- raise notice '看看pagerOutLine里面的数据：%，真奇怪，那么，totalSize 是：%',pagerOutLine,totalSize;
-- 定义返回的状态。
pagerOutLine."state":=true;
pagerOutLine."stateCode":=0;
pagerOutLine."message":='';
-- 返回相关数据。
select row_to_json(t) into outline_result from
(select pagerOutLine."state",
pagerOutLine."stateCode",
pagerOutLine."pageIndex",
pagerOutLine."pageSize",
pagerOutLine.total,
pagerOutLine."totalPages",
pagerOutLine."message") t;
select array_to_json(array_agg(row_to_json(tbl_middle))) into list_result from
/**中间表，begin**/
(
/**中间表定义，begin**/
select * from "ins_like"
where 1=1
-- 请自行条件条件语句。
-- and
-- case when cnd_name_like is not null and char_length(cnd_name_like) > 0
-- then "name" LIKE CONCAT('%',cnd_name_like,'%') else 1=1 end
/**中间表定义，end**/
limit pagerOutLine."pageSize" offset pagerOutLine."beginIndex"
) tbl_middle
/**中间表，end**/
;
-- 有可能找不到记录，这时候就返回空数组。
if list_result is null or char_length (list_result) < 1 then
list_result:='[]';
end if;
end;
$BODY$ LANGUAGE plpgsql volatile;
/***添加记录***/
-- 存储过程 添加记录
CREATE OR REPLACE FUNCTION "sp_ins_ins_like_insert"(
para_uid integer,
para_post_id integer,
para_status boolean,
para_create_time timestamp(0) without time zone,
out op_result varchar
)
RETURNS varchar
AS $BODY$
declare tmpId integer;
declare tmpId_str varchar;
declare opResult sys_type_operation_outline;
declare logic_allow boolean;
declare c_v_uid integer;
declare c_v_post_id integer;
declare c_v_status boolean;
declare c_v_create_time timestamp(0) without time zone;
begin
logic_allow:=false;
-- 请先打开相关操作。
if logic_allow = false then
opResult.message:=config('errors','logic_not_allow','message');
opResult."stateCode":=config('errors','logic_not_allow','code');
opResult."state":=false;
select row_to_json(t) into op_result from ( select opResult.state,opResult."stateCode",opResult.message ) t;
return;
end if;
opResult.state:=false;
opResult."stateCode":=0;
opResult.message:='';
-- 正式的参数赋值
c_v_uid:=para_uid;
c_v_post_id:=para_post_id;
c_v_status:=para_status;
c_v_create_time:=para_create_time;
INSERT INTO "ins_like"(
"uid"
, "post_id"
, "status"
, "create_time"
)
VALUES (
c_v_uid
, c_v_post_id
, c_v_status
, c_v_create_time
);
if FOUND then
opResult.message:='保存成功';
opResult."stateCode":=0;
opResult."state":=true;
else
opResult.message:='保存失败';
opResult."stateCode":=0;
opResult."state":=false;
end if;
if opResult.state=true then
select row_to_json(t) into op_result from ( select opResult.state,opResult."stateCode",opResult.message,'' as data ) t;
else
select row_to_json(t) into op_result from ( select opResult.state,opResult."stateCode",opResult.message ) t;
end if;
end;
$BODY$ LANGUAGE plpgsql volatile;
模型：
package net.w2p.DevBase.model.ins;
import java.util.Date;
import java.sql.*;
import java.util.List;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import java.sql.Timestamp;
/**
* @author MR white
* @version 2.00
* id
*/
public class PostModel {
public PostModel(){
this.setNULL();
}
public Integer id=0;
public void setId(Integer id){
this.id=id;
}
public Integer getId(){
return this.id;
}
public Timestamp publish_time=new Timestamp(new Date().getTime());
public void setPublish_time(Timestamp publish_time){
this.publish_time=publish_time;
}
public Timestamp getPublish_time(){
return this.publish_time;
}
public Integer uid=0;
public void setUid(Integer uid){
this.uid=uid;
}
public Integer getUid(){
return this.uid;
}
public String title="";
public void setTitle(String title){
this.title=title;
}
public String getTitle(){
return this.title;
}
public Integer view_num=0;
public void setView_num(Integer view_num){
this.view_num=view_num;
}
public Integer getView_num(){
return this.view_num;
}
public Integer like_num=0;
public void setLike_num(Integer like_num){
this.like_num=like_num;
}
public Integer getLike_num(){
return this.like_num;
}
public String[] images=new String[]{};
public void setImages(String[] images){
this.images=images;
}
public String[] getImages(){
return this.images;
}
public String description="";
public void setDescription(String description){
this.description=description;
}
public String getDescription(){
return this.description;
}
public void setNULL(){
this.id=null;
this.publish_time=null;
this.uid=null;
this.title=null;
this.view_num=null;
this.like_num=null;
this.images=null;
this.description=null;
}
public void resetDefaultVal(){
this.id=0;
this.publish_time=new Timestamp(new Date().getTime());
this.uid=0;
this.title="";
this.view_num=0;
this.like_num=0;
this.images=new String[]{};
this.description="";
}
}
package net.w2p.DevBase.model.ins;
import java.util.Date;
import java.sql.*;
import java.util.List;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import java.sql.Timestamp;
/**
* @author MR white
* @version 2.00
*/
public class LikePostModel {
public LikePostModel(){
this.setNULL();
}
public Integer uid=0;
public void setUid(Integer uid){
this.uid=uid;
}
public Integer getUid(){
return this.uid;
}
public Integer post_id=0;
public void setPost_id(Integer post_id){
this.post_id=post_id;
}
public Integer getPost_id(){
return this.post_id;
}
public Boolean status=false;
public void setStatus(Boolean status){
this.status=status;
}
public Boolean getStatus(){
return this.status;
}
public Timestamp create_time=new Timestamp(new Date().getTime());
public void setCreate_time(Timestamp create_time){
this.create_time=create_time;
}
public Timestamp getCreate_time(){
return this.create_time;
}
public void setNULL(){
this.uid=null;
this.post_id=null;
this.status=null;
this.create_time=null;
}
public void resetDefaultVal(){
this.uid=0;
this.post_id=0;
this.status=false;
this.create_time=new Timestamp(new Date().getTime());
}
}
service服务层
服务层用于调用存储过程—额，提一句，这个网站分层中，数据库的业务只会调用存储过程，并不会将sql分散到java代码中去的：
package net.w2p.DevBase.service.ins;
import net.w2p.DevBase.model.ins.PostModel;
import net.w2p.DevBase.vo.ins.Post;
import net.w2p.DevBase.searcher.ins.PostCondition;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.session.SqlSession;
import org.apache.commons.lang.StringUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import org.apache.log4j.Logger;
import net.w2p.Shared.common.*;
import net.w2p.Shared.queries.*;
import java.util.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Service;
import org.springframework.jdbc.core.*;
import net.w2p.Shared.mybatis.TypeHandlers.*;
import org.apache.ibatis.type.JdbcType;
/******************************************/
/**
* <p>Title: Postmodel</p>
*
* <p>Description: Post MODEL 处理类</p>
*
* <p>Copyright: Copyright (c) 2017</p>
*
* <p>Company: BLD</p>
*
* @author Mr White 电话：
* @vers
**/
@Service
public class PostService {
private static final String serial_name="ins_post";
private Logger logger=Logger.getLogger(this.getClass());
@Autowired
JdbcTemplate jdbcTemplate;
@Autowired
DoubleArrayTypeHandler doubleArrayTypeHandler;
@Autowired
IntegerArrayTypeHandler integerArrayTypeHandler;
@Autowired
ShortArrayTypeHandler shortArrayTypeHandler;
@Autowired
StringArrayTypeHandler stringArrayTypeHandler;
@Autowired
LongArrayTypeHandler longArrayTypeHandler;
@Autowired
BooleanArrayTypeHandler booleanArrayTypeHandler;
@Autowired
JsonArrayTypeHandler jsonArrayTypeHandler;
@Autowired
JsonTypeHandler jsonTypeHandler;
/**
* 获取搜索分页列表
* @param condition 条件。
* ***/
public PagerResult<Post> pager(
int pageindex,
int pagesize,
PostCondition condition,
Boolean needCompleteInfo
){
final HashMap<String,Integer> map_out_paras=new HashMap<>();
PagerResult<Post> pagerResult=new PagerResult<>();
final String sql="{ call \"sp_ins_ins_post_pager\"(?,?,?,?,?)}";
CallableStatementCreator stCreator=new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement st=con.prepareCall(sql);
int paraIndex=0;
paraIndex++;st.setInt(paraIndex,pageindex);
paraIndex++;st.setInt(paraIndex,pagesize);
paraIndex++;st.setString(paraIndex,JSONObject.toJSONString(condition));
paraIndex++;st.registerOutParameter(paraIndex,Types.VARCHAR);map_out_paras.put("outline_result",paraIndex);
paraIndex++;st.registerOutParameter(paraIndex,Types.VARCHAR);map_out_paras.put("list_result",paraIndex);
return st;
}
};
CallableStatementCallback<PagerResult<Post>> stCallback=new CallableStatementCallback<PagerResult<Post>>(){
@Override
public PagerResult<Post> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
PagerResult<Post> pager=new PagerResult<>();
List<Post> list=new ArrayList<>();
cs.execute();
ResultSet rs=(ResultSet)cs.getResultSet();
String outline_result=cs.getString(map_out_paras.get("outline_result"));
String list_result=cs.getString(map_out_paras.get("list_result"));
System.out.println(outline_result);
pager=JSONObject.parseObject(outline_result,PagerResult.class);
list=JSONObject.parseArray(list_result,Post.class);
pager.setData(new ArrayList<>());
for(Post item:list){
pager.getData().add(item);
}
// if (rs.next()){
// String json_str=rs.getString(1);
// if(ValidateUtils.isEmpty(json_str)){
//return pager;
// }
// pager=JSONObject.parseObject(json_str,PagerResult.class);
// }
if(rs!=null){
rs.close();
}
cs.getConnection().setAutoCommit(true);
return pager;
}
};
pagerResult=jdbcTemplate.execute(stCreator,stCallback);
if(needCompleteInfo){
completeInfo(pagerResult.getData());
}
return pagerResult;
}
/**
* 获取搜索分页列表
* @param condition 条件。
* ***/
public PagerResult<Post> pager(
int pageindex,
int pagesize,
PostCondition condition
){
PagerResult<Post> pagerResult=new PagerResult<>();
pagerResult=pager(pageindex,pagesize,condition,true);
return pagerResult;
}
/***crud模式生成 begin***/
/**
* 获取记录详情
* @param id 主键id
* ***/
public Post getDetail(Integer id){
return getDetail(id,true);
}
public Post getDetail(
Integer id
,Boolean needCompleteInfo
){
final HashMap<String,Integer> map_out_paras=new HashMap<>();
Post detailVo=null;
final String sql="{ call \"sp_ins_ins_post_fetch\"(?,?)}";
CallableStatementCreator stCreator=new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement st=con.prepareCall(sql);
int paraIndex=0;
paraIndex++; st.setObject(paraIndex,id);
paraIndex++;st.registerOutParameter(paraIndex,Types.VARCHAR);map_out_paras.put("model_json",paraIndex);
return st;
}
};
CallableStatementCallback<Post> stCallback=new CallableStatementCallback<Post>(){
@Override
public Post doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
Post res=new Post();
cs.execute();
ResultSet rs=(ResultSet)cs.getResultSet();
String model_json=cs.getString(map_out_paras.get("model_json"));
res=JSONObject.parseObject(model_json,Post.class);
if(rs!=null){
rs.close();
}
cs.getConnection().setAutoCommit(true);
return res;
}
};
detailVo=jdbcTemplate.execute(stCreator,stCallback);
ArrayList<Post> list=new ArrayList<Post>();
list.add(detailVo);
if(needCompleteInfo){
completeInfo(list);
}
return detailVo;
}
/***crud模式生成 end***/
/*********crud begin***********/
/***
* 添加记录
***/
public OpResult insert(PostModel model)
{
int totalParaCount=7 + 1;//--存储过程参数总个数。,其中1个out 参数
String procName="sp_ins_ins_post_insert";
final HashMap<String,Integer> map_out_paras=new HashMap<>();
int theParamIndex=0;
OpResult opResult=new OpResult();
StringBuilder paraStr=new StringBuilder();
String callable_para_str="";
for(int i=0;i<totalParaCount;i++){
paraStr.append('?');
paraStr.append(',');
}
if(totalParaCount>0){
paraStr.append(',');
callable_para_str=(paraStr.toString().replaceFirst(",,",""));
}
final String sql="{ call \""+procName+"\"("+callable_para_str+")}";
CallableStatementCreator stCreator=new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement st=con.prepareCall(sql);
int paraIndex=0;
paraIndex++; st.setTimestamp(paraIndex,model.publish_time);
paraIndex++; st.setObject(paraIndex,model.uid);
paraIndex++; st.setString(paraIndex,model.title);
paraIndex++; st.setObject(paraIndex,model.view_num);
paraIndex++; st.setObject(paraIndex,model.like_num);
paraIndex++; stringArrayTypeHandler.setParameter(st,paraIndex,model.images, JdbcType.VARCHAR);
paraIndex++; st.setString(paraIndex,model.description);
paraIndex++;st.registerOutParameter(paraIndex,Types.VARCHAR);map_out_paras.put("op_result",paraIndex);
return st;
}
};
CallableStatementCallback<OpResult> stCallback=new CallableStatementCallback<OpResult>(){
@Override
public OpResult doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
OpResult op_result=new OpResult();
cs.execute();
ResultSet rs=(ResultSet)cs.getResultSet();
String json_op_result=cs.getString(map_out_paras.get("op_result"));
op_result=JSONObject.parseObject(json_op_result,OpResult.class);
if(rs!=null){
rs.close();
}
cs.getConnection().setAutoCommit(true);
return op_result;
}
};
opResult=jdbcTemplate.execute(stCreator,stCallback);
return opResult;
}
/***
* 更新记录
***/
public OpResult update(PostModel model)
{
int totalParaCount=7 + 1 + 1;//--存储过程参数总个数。其中一个out参数
String procName="sp_ins_ins_post_update";
final HashMap<String,Integer> map_out_paras=new HashMap<>();
int theParamIndex=0;
OpResult opResult=new OpResult();
StringBuilder paraStr=new StringBuilder();
String callable_para_str="";
for(int i=0;i<totalParaCount;i++){
paraStr.append('?');
paraStr.append(',');
}
if(totalParaCount>0){
paraStr.append(',');
callable_para_str=(paraStr.toString().replaceFirst(",,",""));
}
final String sql="{ call \""+procName+"\"("+callable_para_str+")}";
CallableStatementCreator stCreator=new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement st=con.prepareCall(sql);
int paraIndex=0;
paraIndex++; st.setTimestamp(paraIndex,model.publish_time);
paraIndex++; st.setObject(paraIndex,model.uid);
paraIndex++; st.setString(paraIndex,model.title);
paraIndex++; st.setObject(paraIndex,model.view_num);
paraIndex++; st.setObject(paraIndex,model.like_num);
paraIndex++; stringArrayTypeHandler.setParameter(st,paraIndex,model.images, JdbcType.VARCHAR);
paraIndex++; st.setString(paraIndex,model.description);
paraIndex++; st.setObject(paraIndex,model.id);
paraIndex++;st.registerOutParameter(paraIndex,Types.VARCHAR);map_out_paras.put("op_result",paraIndex);
return st;
}
};
CallableStatementCallback<OpResult> stCallback=new CallableStatementCallback<OpResult>(){
@Override
public OpResult doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
OpResult op_result=new OpResult();
cs.execute();
ResultSet rs=(ResultSet)cs.getResultSet();
String json_op_result=cs.getString(map_out_paras.get("op_result"));
op_result=JSONObject.parseObject(json_op_result,OpResult.class);
if(rs!=null){
rs.close();
}
cs.getConnection().setAutoCommit(true);
return op_result;
}
};
opResult=jdbcTemplate.execute(stCreator,stCallback);
return opResult;
}
/***
* 删除记录
***/
public OpResult delete(
Integer para_id
){
int totalParaCount=1 + 1;//--存储过程参数总个数。其中一个out参数
String procName="sp_ins_ins_post_delete";
final HashMap<String,Integer> map_out_paras=new HashMap<>();
int theParamIndex=0;
OpResult opResult=new OpResult();
StringBuilder paraStr=new StringBuilder();
String callable_para_str="";
for(int i=0;i<totalParaCount;i++){
paraStr.append('?');
paraStr.append(',');
}
if(totalParaCount>0){
paraStr.append(',');
callable_para_str=(paraStr.toString().replaceFirst(",,",""));
}
final String sql="{ call \""+procName+"\"("+callable_para_str+")}";
CallableStatementCreator stCreator=new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement st=con.prepareCall(sql);
int paraIndex=0;
paraIndex++; st.setObject(paraIndex,para_id);
paraIndex++;st.registerOutParameter(paraIndex,Types.VARCHAR);map_out_paras.put("op_result",paraIndex);
return st;
}
};
CallableStatementCallback<OpResult> stCallback=new CallableStatementCallback<OpResult>(){
@Override
public OpResult doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
OpResult op_result=new OpResult();
cs.execute();
ResultSet rs=(ResultSet)cs.getResultSet();
String json_op_result=cs.getString(map_out_paras.get("op_result"));
op_result=JSONObject.parseObject(json_op_result,OpResult.class);
if(rs!=null){
rs.close();
}
cs.getConnection().setAutoCommit(true);
return op_result;
}
};
opResult=jdbcTemplate.execute(stCreator,stCallback);
return opResult;
}
/**********crud end**********/
private void completeInfo(ArrayList<Post> list){
if(list==null||list.size()<1){
return;
}
}
}
package net.w2p.DevBase.service.ins;
import net.w2p.DevBase.model.ins.LikePostModel;
import net.w2p.DevBase.vo.ins.LikePost;
import net.w2p.DevBase.searcher.ins.LikePostCondition;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.session.SqlSession;
import org.apache.commons.lang.StringUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import org.apache.log4j.Logger;
import net.w2p.Shared.common.*;
import net.w2p.Shared.queries.*;
import java.util.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Service;
import org.springframework.jdbc.core.*;
import net.w2p.Shared.mybatis.TypeHandlers.*;
import org.apache.ibatis.type.JdbcType;
/******************************************/
/**
* <p>Title: LikePostmodel</p>
*
* <p>Description: LikePost MODEL 处理类</p>
*
* <p>Copyright: Copyright (c) 2017</p>
*
* <p>Company: BLD</p>
*
* @author Mr White 电话：
* @vers
**/
@Service
public class LikePostService {
private static final String serial_name="ins_like";
private Logger logger=Logger.getLogger(this.getClass());
@Autowired
JdbcTemplate jdbcTemplate;
@Autowired
DoubleArrayTypeHandler doubleArrayTypeHandler;
@Autowired
IntegerArrayTypeHandler integerArrayTypeHandler;
@Autowired
ShortArrayTypeHandler shortArrayTypeHandler;
@Autowired
StringArrayTypeHandler stringArrayTypeHandler;
@Autowired
LongArrayTypeHandler longArrayTypeHandler;
@Autowired
BooleanArrayTypeHandler booleanArrayTypeHandler;
@Autowired
JsonArrayTypeHandler jsonArrayTypeHandler;
@Autowired
JsonTypeHandler jsonTypeHandler;
/**
* 获取搜索分页列表
* @param condition 条件。
* ***/
public PagerResult<LikePost> pager(
int pageindex,
int pagesize,
LikePostCondition condition,
Boolean needCompleteInfo
){
final HashMap<String,Integer> map_out_paras=new HashMap<>();
PagerResult<LikePost> pagerResult=new PagerResult<>();
final String sql="{ call \"sp_ins_ins_like_pager\"(?,?,?,?,?)}";
CallableStatementCreator stCreator=new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement st=con.prepareCall(sql);
int paraIndex=0;
paraIndex++;st.setInt(paraIndex,pageindex);
paraIndex++;st.setInt(paraIndex,pagesize);
paraIndex++;st.setString(paraIndex,JSONObject.toJSONString(condition));
paraIndex++;st.registerOutParameter(paraIndex,Types.VARCHAR);map_out_paras.put("outline_result",paraIndex);
paraIndex++;st.registerOutParameter(paraIndex,Types.VARCHAR);map_out_paras.put("list_result",paraIndex);
return st;
}
};
CallableStatementCallback<PagerResult<LikePost>> stCallback=new CallableStatementCallback<PagerResult<LikePost>>(){
@Override
public PagerResult<LikePost> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
PagerResult<LikePost> pager=new PagerResult<>();
List<LikePost> list=new ArrayList<>();
cs.execute();
ResultSet rs=(ResultSet)cs.getResultSet();
String outline_result=cs.getString(map_out_paras.get("outline_result"));
String list_result=cs.getString(map_out_paras.get("list_result"));
System.out.println(outline_result);
pager=JSONObject.parseObject(outline_result,PagerResult.class);
list=JSONObject.parseArray(list_result,LikePost.class);
pager.setData(new ArrayList<>());
for(LikePost item:list){
pager.getData().add(item);
}
// if (rs.next()){
// String json_str=rs.getString(1);
// if(ValidateUtils.isEmpty(json_str)){
//return pager;
// }
// pager=JSONObject.parseObject(json_str,PagerResult.class);
// }
if(rs!=null){
rs.close();
}
cs.getConnection().setAutoCommit(true);
return pager;
}
};
pagerResult=jdbcTemplate.execute(stCreator,stCallback);
if(needCompleteInfo){
completeInfo(pagerResult.getData());
}
return pagerResult;
}
/**
* 获取搜索分页列表
* @param condition 条件。
* ***/
public PagerResult<LikePost> pager(
int pageindex,
int pagesize,
LikePostCondition condition
){
PagerResult<LikePost> pagerResult=new PagerResult<>();
pagerResult=pager(pageindex,pagesize,condition,true);
return pagerResult;
}
/**非crud，无须生成**/
private void completeInfo(ArrayList<LikePost> list){
if(list==null||list.size()<1){
return;
}
}
}
初步运行逻辑：
好了，增删改查定义好了，不过crud没什么用，最多是节省后台操作而已。下面继续开发相关接口及功能。
功能简述
假设我们的用例图如下—嗯，一切从简，不是正式项目，实现为主。
那么功能点就可以确定了，所以根据这几个功能开发如下：
存储过程：
/*业务逻辑，发布帖子*/
CREATE OR REPLACE FUNCTION "sp_ins_ins_post_publish"(
para_uid integer,
para_title character varying,
-- (-_-) 注意，这个字段是数组来的，然而jdbc没有数组支持，这里先用varchar代替，在调用时候将原本的数组转换一下即可。
para_images varchar,
para_description character varying,
out op_result varchar
)
RETURNS varchar
AS $BODY$
declare tmpId integer;
declare tmpId_str varchar;
declare opResult sys_type_operation_outline;
declare logic_allow boolean;
declare c_v_publish_time timestamp(0) without time zone;
declare c_v_uid integer;
declare c_v_title character varying;
declare c_v_view_num integer;
declare c_v_like_num integer;
-- (-_-) 注意，这个字段是数组来的，然而jdbc没有数组支持，这里先用varchar代替，在调用时候将原本的数组转换一下即可。
declare c_v_images character varying[];
declare c_v_description character varying;
begin
logic_allow:=true;
-- 请先打开相关操作。
if logic_allow = false then
opResult.message:=config('errors','logic_not_allow','message');
opResult."stateCode":=config('errors','logic_not_allow','code');
opResult."state":=false;
select row_to_json(t) into op_result from ( select opResult.state,opResult."stateCode",opResult.message ) t;
return;
end if;
opResult.state:=false;
opResult."stateCode":=0;
opResult.message:='';
-- 正式的参数赋值
c_v_publish_time:=current_timestamp (0);
c_v_uid:=para_uid;
c_v_title:=para_title;
c_v_view_num:=0;
c_v_like_num:=0;
c_v_images := string_to_array(para_images, ',')::character varying(250)[];
c_v_description:=para_description;
INSERT INTO "ins_post"(
-- 主键不插入记录。
"publish_time"
, "uid"
, "title"
, "view_num"
, "like_num"
, "images"
, "description"
)
VALUES (
c_v_publish_time
, c_v_uid
, c_v_title
, c_v_view_num
, c_v_like_num
, c_v_images
, c_v_description
);
if FOUND then
opResult.message:='保存成功';
opResult."stateCode":=0;
opResult."state":=true;
else
opResult.message:='保存失败';
opResult."stateCode":=0;
opResult."state":=false;
end if;
if opResult.state=true then
select row_to_json(t) into op_result from ( select opResult.state,opResult."stateCode",opResult.message,currval(pg_get_serial_sequence('ins_post', 'id')) as data ) t;
else
select row_to_json(t) into op_result from ( select opResult.state,opResult."stateCode",opResult.message ) t;
end if;
end;
$BODY$ LANGUAGE plpgsql volatile;
-- 点赞或者取消点赞
CREATE OR REPLACE FUNCTION "sp_ins_ins_post_like_or_not_like"(
para_uid integer,
para_post_id integer,
para_status boolean,
out op_result varchar
)
RETURNS varchar
AS $BODY$
declare tmpId integer;
declare tmpId_str varchar;
declare opResult sys_type_operation_outline;
declare logic_allow boolean;
declare c_v_uid integer;
declare c_v_post_id integer;
declare c_v_status boolean;
declare c_v_create_time timestamp(0) without time zone;
begin
logic_allow:=true;
-- 请先打开相关操作。
if logic_allow = false then
opResult.message:=config('errors','logic_not_allow','message');
opResult."stateCode":=config('errors','logic_not_allow','code');
opResult."state":=false;
select row_to_json(t) into op_result from ( select opResult.state,opResult."stateCode",opResult.message ) t;
return;
end if;
opResult.state:=false;
opResult."stateCode":=0;
opResult.message:='';
-- 正式的参数赋值
c_v_uid:=para_uid;
c_v_post_id:=para_post_id;
c_v_status:=para_status;
c_v_create_time:=current_timestamp(0);
INSERT INTO "ins_like"(
"uid"
, "post_id"
, "status"
, "create_time"
)
VALUES (
c_v_uid
, c_v_post_id
, c_v_status
, c_v_create_time
)
on conflict (uid,post_id) DO
update set "status"=EXCLUDED.status;
if FOUND then
opResult.message:='保存成功';
opResult."stateCode":=0;
opResult."state":=true;
else
opResult.message:='保存失败';
opResult."stateCode":=0;
opResult."state":=false;
end if;
if opResult.state=true then
select row_to_json(t) into op_result from ( select opResult.state,opResult."stateCode",opResult.message,'' as data ) t;
else
select row_to_json(t) into op_result from ( select opResult.state,opResult."stateCode",opResult.message ) t;
end if;
end;
$BODY$ LANGUAGE plpgsql volatile;
/***最新帖子***/
CREATE OR REPLACE FUNCTION public."sp_ins_ins_post_newest_list"(
in pageindex integer,
in pagesize integer,
out outline_result varchar,
out list_result varchar
)
RETURNS record
AS $BODY$
declare pagerOutLine sys_type_pager_outline;
declare totalSize integer;
declare tmpStr varchar(400);
declare json_condition json;
declare cnd_id_eq integer;
begin
list_result:='[]';
-- 组装条件语句
json_condition:='{}'::json;
-- 首先获取记录总数
select count(*) into totalSize from
/**中间表，begin**/
(
select * from "ins_post"
where 1=1
-- 请自行条件条件语句。
-- and
-- case when cnd_name_like is not null and char_length(cnd_name_like) > 0
-- then "name" LIKE CONCAT('%',cnd_name_like,'%') else 1=1 end
order by "publish_time" desc
) tbl_middle
/**中间表，end**/
;
-- 然后计算beginIndex，totalPages
pagerOutLine:=sys_func_cal_pager(pageIndex,pageSize,totalSize);
-- raise notice '好了，看看pagerOutline的数据：';
-- raise notice '看看pagerOutLine里面的数据：%，真奇怪，那么，totalSize 是：%',pagerOutLine,totalSize;
-- 定义返回的状态。
pagerOutLine."state":=true;
pagerOutLine."stateCode":=0;
pagerOutLine."message":='';
-- 返回相关数据。
select row_to_json(t) into outline_result from
(select pagerOutLine."state",
pagerOutLine."stateCode",
pagerOutLine."pageIndex",
pagerOutLine."pageSize",
pagerOutLine.total,
pagerOutLine."totalPages",
pagerOutLine."message") t;
select array_to_json(array_agg(row_to_json(tbl_middle))) into list_result from
/**中间表，begin**/
(
/**中间表定义，begin**/
select * from "ins_post"
where 1=1
order by "publish_time" desc
/**中间表定义，end**/
limit pagerOutLine."pageSize" offset pagerOutLine."beginIndex"
) tbl_middle
/**中间表，end**/
;
-- 有可能找不到记录，这时候就返回空数组。
if list_result is null or char_length (list_result) < 1 then
list_result:='[]';
end if;
end;
$BODY$ LANGUAGE plpgsql volatile;
/***查看人数最多的帖子***/
CREATE OR REPLACE FUNCTION public."sp_ins_ins_post_hottest_list"(
in pageindex integer,
in pagesize integer,
out outline_result varchar,
out list_result varchar
)
RETURNS record
AS $BODY$
declare pagerOutLine sys_type_pager_outline;
declare totalSize integer;
declare tmpStr varchar(400);
declare json_condition json;
declare cnd_id_eq integer;
begin
list_result:='[]';
-- 组装条件语句
json_condition:='{}'::json;
-- 首先获取记录总数
select count(*) into totalSize from
/**中间表，begin**/
(
select * from "ins_post"
where 1=1
-- 请自行条件条件语句。
-- and
-- case when cnd_name_like is not null and char_length(cnd_name_like) > 0
-- then "name" LIKE CONCAT('%',cnd_name_like,'%') else 1=1 end
order by "view_num" desc
) tbl_middle
/**中间表，end**/
;
-- 然后计算beginIndex，totalPages
pagerOutLine:=sys_func_cal_pager(pageIndex,pageSize,totalSize);
-- raise notice '好了，看看pagerOutline的数据：';
-- raise notice '看看pagerOutLine里面的数据：%，真奇怪，那么，totalSize 是：%',pagerOutLine,totalSize;
-- 定义返回的状态。
pagerOutLine."state":=true;
pagerOutLine."stateCode":=0;
pagerOutLine."message":='';
-- 返回相关数据。
select row_to_json(t) into outline_result from
(select pagerOutLine."state",
pagerOutLine."stateCode",
pagerOutLine."pageIndex",
pagerOutLine."pageSize",
pagerOutLine.total,
pagerOutLine."totalPages",
pagerOutLine."message") t;
select array_to_json(array_agg(row_to_json(tbl_middle))) into list_result from
/**中间表，begin**/
(
/**中间表定义，begin**/
select * from "ins_post"
where 1=1
order by "view_num" desc
/**中间表定义，end**/
limit pagerOutLine."pageSize" offset pagerOutLine."beginIndex"
) tbl_middle
/**中间表，end**/
;
-- 有可能找不到记录，这时候就返回空数组。
if list_result is null or char_length (list_result) < 1 then
list_result:='[]';
end if;
end;
$BODY$ LANGUAGE plpgsql volatile;
/***最受欢迎的，最多人喜欢的***/
CREATE OR REPLACE FUNCTION public."sp_ins_ins_post_popular_list"(
in pageindex integer,
in pagesize integer,
out outline_result varchar,
out list_result varchar
)
RETURNS record
AS $BODY$
declare pagerOutLine sys_type_pager_outline;
declare totalSize integer;
declare tmpStr varchar(400);
declare json_condition json;
declare cnd_id_eq integer;
begin
list_result:='[]';
-- 组装条件语句
json_condition:='{}'::json;
-- 首先获取记录总数
select count(*) into totalSize from
/**中间表，begin**/
(
select * from "ins_post"
where 1=1
order by "like_num" desc,"publish_time" asc
) tbl_middle
/**中间表，end**/
;
-- 然后计算beginIndex，totalPages
pagerOutLine:=sys_func_cal_pager(pageIndex,pageSize,totalSize);
-- raise notice '好了，看看pagerOutline的数据：';
-- raise notice '看看pagerOutLine里面的数据：%，真奇怪，那么，totalSize 是：%',pagerOutLine,totalSize;
-- 定义返回的状态。
pagerOutLine."state":=true;
pagerOutLine."stateCode":=0;
pagerOutLine."message":='';
-- 返回相关数据。
select row_to_json(t) into outline_result from
(select pagerOutLine."state",
pagerOutLine."stateCode",
pagerOutLine."pageIndex",
pagerOutLine."pageSize",
pagerOutLine.total,
pagerOutLine."totalPages",
pagerOutLine."message") t;
select array_to_json(array_agg(row_to_json(tbl_middle))) into list_result from
/**中间表，begin**/
(
/**中间表定义，begin**/
select * from "ins_post"
where 1=1
order by "like_num" desc,"publish_time" asc
/**中间表定义，end**/
limit pagerOutLine."pageSize" offset pagerOutLine."beginIndex"
) tbl_middle
/**中间表，end**/
;
-- 有可能找不到记录，这时候就返回空数组。
if list_result is null or char_length (list_result) < 1 then
list_result:='[]';
end if;
end;
$BODY$ LANGUAGE plpgsql volatile;
java调用逻辑：
package net.w2p.DevBase.biz.ins;
import com.alibaba.fastjson.JSONObject;
import net.w2p.DevBase.model.account.MemberModel;
import net.w2p.DevBase.model.account.MemberRegByAdmin;
import net.w2p.DevBase.model.account.MemberUpdateByAdmin;
import net.w2p.DevBase.model.ins.PostModel;
import net.w2p.DevBase.searcher.ins.PostCondition;
import net.w2p.DevBase.service.account.MemberService;
import net.w2p.DevBase.service.common.RegionService;
import net.w2p.DevBase.vo.account.Member;
import net.w2p.DevBase.vo.common.Region;
import net.w2p.DevBase.vo.common.RegionSimpleVO;
import net.w2p.DevBase.vo.ins.Post;
import net.w2p.Shared.common.DB.DataTableHelper;
import net.w2p.Shared.common.OpResult;
import net.w2p.Shared.common.PagerResult;
import net.w2p.Shared.common.ValidateUtils;
import net.w2p.Shared.mybatis.TypeHandlers.*;
import org.apache.commons.lang.StringUtils;
import org.apache.ibatis.type.JdbcType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.DependsOn;
import org.springframework.context.annotation.Primary;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
@Service
public class PostBiz {
@Autowired
JdbcTemplate jdbcTemplate;
@Autowired
NamedParameterJdbcTemplate namedParameterJdbcTemplate;
@Autowired
DoubleArrayTypeHandler doubleArrayTypeHandler;
@Autowired
IntegerArrayTypeHandler integerArrayTypeHandler;
@Autowired
ShortArrayTypeHandler shortArrayTypeHandler;
@Autowired
StringArrayTypeHandler stringArrayTypeHandler;
@Autowired
LongArrayTypeHandler longArrayTypeHandler;
@Autowired
BooleanArrayTypeHandler booleanArrayTypeHandler;
@Autowired
JsonArrayTypeHandler jsonArrayTypeHandler;
@Autowired
JsonTypeHandler jsonTypeHandler;
/***
* 发布帖子
***/
public OpResult publish(
Integer uid,
String title,
String[] images,
String description
)
{
int totalParaCount=4 + 1;//--存储过程参数总个数。,其中1个out 参数
String procName="sp_ins_ins_post_publish";
final HashMap<String,Integer> map_out_paras=new HashMap<>();
int theParamIndex=0;
OpResult opResult=new OpResult();
StringBuilder paraStr=new StringBuilder();
String callable_para_str="";
for(int i=0;i<totalParaCount;i++){
paraStr.append('?');
paraStr.append(',');
}
if(totalParaCount>0){
paraStr.append(',');
callable_para_str=(paraStr.toString().replaceFirst(",,",""));
}
final String sql="{ call \""+procName+"\"("+callable_para_str+")}";
CallableStatementCreator stCreator=new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement st=con.prepareCall(sql);
int paraIndex=0;
paraIndex++; st.setObject(paraIndex,uid);
paraIndex++; st.setString(paraIndex,title);
paraIndex++; stringArrayTypeHandler.setParameter(st,paraIndex,images, JdbcType.VARCHAR);
paraIndex++; st.setString(paraIndex,description);
paraIndex++;st.registerOutParameter(paraIndex,Types.VARCHAR);map_out_paras.put("op_result",paraIndex);
return st;
}
};
CallableStatementCallback<OpResult> stCallback=new CallableStatementCallback<OpResult>(){
@Override
public OpResult doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
OpResult op_result=new OpResult();
cs.execute();
ResultSet rs=(ResultSet)cs.getResultSet();
String json_op_result=cs.getString(map_out_paras.get("op_result"));
op_result=JSONObject.parseObject(json_op_result,OpResult.class);
if(rs!=null){
rs.close();
}
cs.getConnection().setAutoCommit(true);
return op_result;
}
};
opResult=jdbcTemplate.execute(stCreator,stCallback);
return opResult;
}
/***
* 点赞或者取消点赞
***/
public OpResult likeOrNot(
Integer uid,
Integer post_id,
Boolean status
)
{
int totalParaCount=3 + 1;//--存储过程参数总个数。,其中1个out 参数
String procName="sp_ins_ins_post_like_or_not_like";
final HashMap<String,Integer> map_out_paras=new HashMap<>();
int theParamIndex=0;
OpResult opResult=new OpResult();
StringBuilder paraStr=new StringBuilder();
String callable_para_str="";
for(int i=0;i<totalParaCount;i++){
paraStr.append('?');
paraStr.append(',');
}
if(totalParaCount>0){
paraStr.append(',');
callable_para_str=(paraStr.toString().replaceFirst(",,",""));
}
final String sql="{ call \""+procName+"\"("+callable_para_str+")}";
CallableStatementCreator stCreator=new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement st=con.prepareCall(sql);
int paraIndex=0;
paraIndex++; st.setObject(paraIndex,uid);
paraIndex++; st.setObject(paraIndex,post_id);
paraIndex++; st.setBoolean(paraIndex,status);
paraIndex++;st.registerOutParameter(paraIndex,Types.VARCHAR);map_out_paras.put("op_result",paraIndex);
return st;
}
};
CallableStatementCallback<OpResult> stCallback=new CallableStatementCallback<OpResult>(){
@Override
public OpResult doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
OpResult op_result=new OpResult();
cs.execute();
ResultSet rs=(ResultSet)cs.getResultSet();
String json_op_result=cs.getString(map_out_paras.get("op_result"));
op_result=JSONObject.parseObject(json_op_result,OpResult.class);
if(rs!=null){
rs.close();
}
cs.getConnection().setAutoCommit(true);
return op_result;
}
};
opResult=jdbcTemplate.execute(stCreator,stCallback);
return opResult;
}
/**
* 最新帖子
*
* ***/
public PagerResult<Post> newestList(
int pageindex,
int pagesize
){
final HashMap<String,Integer> map_out_paras=new HashMap<>();
PagerResult<Post> pagerResult=new PagerResult<>();
final String sql="{ call \"sp_ins_ins_post_newest_list\"(?,?,?,?)}";
CallableStatementCreator stCreator=new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement st=con.prepareCall(sql);
int paraIndex=0;
paraIndex++;st.setInt(paraIndex,pageindex);
paraIndex++;st.setInt(paraIndex,pagesize);
paraIndex++;st.registerOutParameter(paraIndex,Types.VARCHAR);map_out_paras.put("outline_result",paraIndex);
paraIndex++;st.registerOutParameter(paraIndex,Types.VARCHAR);map_out_paras.put("list_result",paraIndex);
return st;
}
};
CallableStatementCallback<PagerResult<Post>> stCallback=new CallableStatementCallback<PagerResult<Post>>(){
@Override
public PagerResult<Post> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
PagerResult<Post> pager=new PagerResult<>();
List<Post> list=new ArrayList<>();
cs.execute();
ResultSet rs=(ResultSet)cs.getResultSet();
String outline_result=cs.getString(map_out_paras.get("outline_result"));
String list_result=cs.getString(map_out_paras.get("list_result"));
System.out.println(outline_result);
pager=JSONObject.parseObject(outline_result,PagerResult.class);
list=JSONObject.parseArray(list_result,Post.class);
pager.setData(new ArrayList<>());
for(Post item:list){
pager.getData().add(item);
}
// if (rs.next()){
// String json_str=rs.getString(1);
// if(ValidateUtils.isEmpty(json_str)){
//return pager;
// }
// pager=JSONObject.parseObject(json_str,PagerResult.class);
// }
if(rs!=null){
rs.close();
}
cs.getConnection().setAutoCommit(true);
return pager;
}
};
pagerResult=jdbcTemplate.execute(stCreator,stCallback);
completeInfo(pagerResult.getData());
return pagerResult;
}
/**
* 查看人数最多的帖子列表
*
* ***/
public PagerResult<Post> hottestList(
int pageindex,
int pagesize
){
final HashMap<String,Integer> map_out_paras=new HashMap<>();
PagerResult<Post> pagerResult=new PagerResult<>();
final String sql="{ call \"sp_ins_ins_post_hottest_list\"(?,?,?,?)}";
CallableStatementCreator stCreator=new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement st=con.prepareCall(sql);
int paraIndex=0;
paraIndex++;st.setInt(paraIndex,pageindex);
paraIndex++;st.setInt(paraIndex,pagesize);
paraIndex++;st.registerOutParameter(paraIndex,Types.VARCHAR);map_out_paras.put("outline_result",paraIndex);
paraIndex++;st.registerOutParameter(paraIndex,Types.VARCHAR);map_out_paras.put("list_result",paraIndex);
return st;
}
};
CallableStatementCallback<PagerResult<Post>> stCallback=new CallableStatementCallback<PagerResult<Post>>(){
@Override
public PagerResult<Post> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
PagerResult<Post> pager=new PagerResult<>();
List<Post> list=new ArrayList<>();
cs.execute();
ResultSet rs=(ResultSet)cs.getResultSet();
String outline_result=cs.getString(map_out_paras.get("outline_result"));
String list_result=cs.getString(map_out_paras.get("list_result"));
System.out.println(outline_result);
pager=JSONObject.parseObject(outline_result,PagerResult.class);
list=JSONObject.parseArray(list_result,Post.class);
pager.setData(new ArrayList<>());
for(Post item:list){
pager.getData().add(item);
}
// if (rs.next()){
// String json_str=rs.getString(1);
// if(ValidateUtils.isEmpty(json_str)){
//return pager;
// }
// pager=JSONObject.parseObject(json_str,PagerResult.class);
// }
if(rs!=null){
rs.close();
}
cs.getConnection().setAutoCommit(true);
return pager;
}
};
pagerResult=jdbcTemplate.execute(stCreator,stCallback);
completeInfo(pagerResult.getData());
return pagerResult;
}
/**
* 最受欢迎，最多人喜欢的帖子
*
* ***/
public PagerResult<Post> popularList(
int pageindex,
int pagesize
){
final HashMap<String,Integer> map_out_paras=new HashMap<>();
PagerResult<Post> pagerResult=new PagerResult<>();
final String sql="{ call \"sp_ins_ins_post_popular_list\"(?,?,?,?)}";
CallableStatementCreator stCreator=new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement st=con.prepareCall(sql);
int paraIndex=0;
paraIndex++;st.setInt(paraIndex,pageindex);
paraIndex++;st.setInt(paraIndex,pagesize);
paraIndex++;st.registerOutParameter(paraIndex,Types.VARCHAR);map_out_paras.put("outline_result",paraIndex);
paraIndex++;st.registerOutParameter(paraIndex,Types.VARCHAR);map_out_paras.put("list_result",paraIndex);
return st;
}
};
CallableStatementCallback<PagerResult<Post>> stCallback=new CallableStatementCallback<PagerResult<Post>>(){
@Override
public PagerResult<Post> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
PagerResult<Post> pager=new PagerResult<>();
List<Post> list=new ArrayList<>();
cs.execute();
ResultSet rs=(ResultSet)cs.getResultSet();
String outline_result=cs.getString(map_out_paras.get("outline_result"));
String list_result=cs.getString(map_out_paras.get("list_result"));
System.out.println(outline_result);
pager=JSONObject.parseObject(outline_result,PagerResult.class);
list=JSONObject.parseArray(list_result,Post.class);
pager.setData(new ArrayList<>());
for(Post item:list){
pager.getData().add(item);
}
// if (rs.next()){
// String json_str=rs.getString(1);
// if(ValidateUtils.isEmpty(json_str)){
//return pager;
// }
// pager=JSONObject.parseObject(json_str,PagerResult.class);
// }
if(rs!=null){
rs.close();
}
cs.getConnection().setAutoCommit(true);
return pager;
}
};
pagerResult=jdbcTemplate.execute(stCreator,stCallback);
completeInfo(pagerResult.getData());
return pagerResult;
}
private void completeInfo(ArrayList<Post> list){
if(list==null||list.size()<1){
return;
}
}
}
顺便写测试代码：
package ins;
import com.alibaba.fastjson.JSONObject;
import main.BaseTest;
import net.w2p.DevBase.biz.ConfigBiz;
import net.w2p.DevBase.biz.ins.PostBiz;
import net.w2p.Shared.common.OpResult;
import net.w2p.Shared.common.PagerResult;
import org.junit.Test;
import org.omg.PortableInterceptor.SYSTEM_EXCEPTION;
import org.springframework.beans.factory.annotation.Autowired;
import java.util.ArrayList;
public class PostTester extends BaseTest {
@Autowired
PostBiz postBiz;
@Test
public void publish(){
OpResult opResult=postBiz.publish(1,"你好",new String[]{"/res/images/gallery-sample/img1.jpg"},"测试用帖子");
System.out.println(JSONObject.toJSONString(opResult));
}
@Test
public void like(){
OpResult opResult=postBiz.likeOrNot(2,1,true);
System.out.println(JSONObject.toJSONString(opResult));
}
@Test
public void notLike(){
OpResult opResult=postBiz.likeOrNot(2,1,false);
System.out.println(JSONObject.toJSONString(opResult));
}
@Test
public void newestList(){
PagerResult opResult=postBiz.newestList(1,20);
System.out.println(JSONObject.toJSONString(opResult));
}
@Test
public void hottestList(){
PagerResult opResult=postBiz.hottestList(1,20);
System.out.println(JSONObject.toJSONString(opResult));
}
@Test
public void popularList(){
PagerResult opResult=postBiz.popularList(1,20);
System.out.println(JSONObject.toJSONString(opResult));
}
}
好了，测试通过的。。
所以，网页api接口以及网页端展示就不做了—因为本来就不是要做一个成品，做的是一个高并发的实验。
本节结语
可以看到，按照传统方式完成功能是非常容易的。不考虑任何性能问题的话。
那么好了，下一小节我们将编写测试用的机器人兵团来压垮我们自己的业务。缓存是很有必要的。
扫一扫
625
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
