json pgsql 函数 操作_科学网—使用PostgreSQL中的row_to_json()直接获得JSON（PG JSON系列1） - 孙鹏的博文...

本系列博文讲述使用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)