本系列博文讲述使用PG JSON/JSONB类型考虑的问题,这是第一篇(我尽量写的简单,让初学者跟着做很容易也能重复实验),看看不使用JSON/JSONB,仅仅使用row_to_json()函数能帮我们做什么。
JSON数据类型在当前互联网浪潮中得到了大量的采用,见下图GOOGLE指数对比JSON和XML,从全球范围来看:
蓝色表示JSON的趋势,成明显的上升趋势。
从中国范围来看,JSON搜索热度明显高于XML:
PG也从9.2开始正式引入JSON类型,那自然就问:既然DB支持了JSON和JSON相关函数,是不是中间服务层(如PHP,JAVA)通过JDBC可以直接获取到JSON数据,而无需再用org.json和json-lib库把以前的行数据进行转换?
我们来做个实验:
运行环境:PG 9.6,WIN8.1 64,Tomcat 8.0,JDK 8
-- 创建数据库
--postgres登陆pg,使用如下sql创建数据库和用户名密码:
CREATE ROLE jsontest PASSWORD 'jsontest.dba' CREATEDB NOSUPERUSER CREATEROLE LOGIN;
CREATE DATABASE jsontestdb OWNER jsontest ENCODING 'UTF8';
-- 用jsontest登录,然后是DDL:
-- 创建一个全局的序列
DROP SEQUENCE IF EXISTS jsontest_uuid_seq CASCADE;
CREATE SEQUENCE jsontest_uuid_seq START 1;
-- Table: TUsers, 用户表
DROP TABLE IF EXISTS TUsers CASCADE;
CREATE TABLE TUsers (
id bigint DEFAULT nextval('jsontest_uuid_seq') PRIMARY KEY,-- 用户id
realName character varying(64) -- 真实姓名
)WITH ( OIDS=FALSE );
CREATE INDEX TUsers_cellphone_idx ON TUsers (realName);
-- Table: TProject,用户创建的项目表
DROP TABLE IF EXISTS TProject CASCADE;
CREATE TABLE TProject (
id bigint DEFAULT nextval('jsontest_uuid_seq') PRIMARY KEY,-- 活动id
title character varying(256) NOT NULL UNIQUE, -- 项目名称,设置为UNIQUE,避免混淆
creatorId integer DEFAULT NULL REFERENCES TUsers (id) match simple on delete SET NULL -- 活动创建的用户id
)WITH ( OIDS=FALSE );
CREATE INDEX TProject_creator_idx ON TProject( creatorId );
-- 测试数据:
insert into TUsers values(1,'test1');
insert into TUsers values(2,'test2');
insert into TUsers values(3,'test3');
insert into TProject values(1,'测试项目1',1);
insert into TProject values(2,'测试项目2',1);
insert into TProject values(3,'测试项目3',1);
DB设计图(在Eclipse用ERMaster反向生成)如下:
好,JAVA这一块,我们分多种情况来分析:
1. 数据库最多只会返回一行数据,然后转换成JSON对象:如通过用户id获得用户JSON对象public class User {
public User() {
super();
}
/*
* 根据用户id获取用户信息
*/
public String getUserInfoFromId(String userId){
String res = null ;
if(null == userId || userId.isEmpty() ) return res;
Connection conn;
Statement stm;
ResultSet rs;
DBPool dbp = new DBPool();
conn = dbp.getConnection();
try {
stm = conn.createStatement();
String sql = "SELECT row_to_json(a.*) from TUsers a where a.id = "+userId;
rs = stm.executeQuery(sql);
if(rs != null && rs.next()){
res = rs.getString(1);
}
} catch (SQLException e) {
e.printStackTrace();
} finally{
dbp.closeConnection();
}
return res;
}}
上面的核心是SQL:SELECT row_to_json(a.*) from TUsers a where a.id = 1
那如果我获得的不是a.*,即不需要一整行数据(如密码不想返回)怎么办?如下会报错:
SELECT row_to_json(a.id,a.realName) from TUsers a where a.id = 1
解决办法有:
SELECT row_to_json(a.*) from (select b.id,b.realName from TUsers b where b.id = 1 ) a
或者使用WITH:
WITH myInfo AS (select a.id,a.realName from TUsers a where a.id = 1 )
SELECT row_to_json(b.*) from myInfo b
或者:
SELECT
(SELECT row_to_json(_) from (select a.id, a.realName) as _)
from
TUsers a where a.id = 1
注意的是,不能用row()函数,因为无法产生别名,即正如https://www.postgresql.org/docs/9.6/static/functions-json.html里所演示的那样:row_to_json(row(1,'foo')){"f1":1,"f2":"foo"}
所以综合以上,建议用WITH方法,简洁明了。
public class UserWS extends HttpServlet :
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String rtn = "";
request.setCharacterEncoding("UTF-8");
response.setContentType("text/json; charset=UTF-8");
response.setCharacterEncoding("UTF-8");
String action = request.getParameter("action");
if(action.equalsIgnoreCase("getuserinfo")) {//其他人通过用户ID获取用户信息
String userid = (String) request.getParameter("userid");
User u = new User();
String userJson = u.getUserInfoFromId(userid);
if(null != userJson){//存在该用户信息
rtn = new StringBuilder()
.append( "{"issuccessful":"true","data":")
.append((null==userJson)?"":userJson)
.append("}")
.toString();
}else{
rtn = "{"issuccessful":"false","errorcode":"NOEXIST_USER"}";
}
}
String callbackFunName = request.getParameter("callbackparam");
if(null != callbackFunName ){
response.getOutputStream().write(StringUtil.wrapCrossDomain(rtn,callbackFunName).getBytes("UTF-8"));
}else{
response.getOutputStream().write(rtn.getBytes("UTF-8"));
}
}
返回:
{"issuccessful":"true","data":{"id":1,"realname":"test1"}}
2. 数据库返回多行数据:如获取用户参与的项目
WITH myProjects AS (select a.id,a.title from TProject a where a.creatorId = 1)
SELECT row_to_json(b.*) from
(SELECT array_to_json(array(select row_to_json(myProjects.*) from myProjects),false) as myProjects) b
返回:
{
"issuccessful":"true",
"data":{
"myprojects":[
{
"id":3,
"title":"测试项目3"
},
{
"id":2,
"title":"测试项目2"
},
{
"id":1,
"title":"测试项目1"
}
]
}
}
3. 返回某些表的一行数据,加上某些表的多行数据:如获取用户基本信息以及用户参与的项目
WITH myInfo AS (select id,realName from TUsers where id = 1 ), -- 一行数据
myProjects AS (select a.id,a.title from TProject a,myInfo b where a.creatorId = b.id) -- 多行数据
SELECT row_to_json(x.*) from
(
select c.*,d.* from
myInfo c,
(SELECT array_to_json(array(select row_to_json(myProjects.*) from myProjects),false) as myProjects) d -- 把多行数据生成一行一列json数组
) x
该SQL返回:
{"myprojects":[{"id":3,"title":"测试项目3"},{"id":2,"title":"测试项目2"},{"id":1,"title":"测试项目1"}],"id":1,"realname":"test1"}
总结一下:
仅仅使用PG的row_to_json(),即可轻松返回需要的各种SQL数据。这样子使得中间层(JAVA/PHP)的代码变得简化,无需org.json和json-lib库再做一次封装,这是架构师要注意的一个重要趋势。
码字码了半天,手指头都麻了,如果觉得对您还有用的话,赞助一下辛苦费吧:
转载本文请联系原作者获取授权,同时请注明本文来自孙鹏科学网博客。
链接地址:http://blog.sciencenet.cn/blog-419883-1007839.html
上一篇:OSG+QT开发环境搭建
下一篇:如何使用PostgreSQL中的JSONB数据类型(PG JSON系列2)