本系列博文讲述使用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 0;
-- 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对象
/*
* 根据用户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();
/*
* WITH myInfo AS (select a.id,a.realName from TUsers a where a.id = 1 )
SELECT row_to_json(b.*) from myInfo b
*/
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 ：
@WebServlet(asyncSupported = true, urlPatterns = { "/user" })
public class UserWS extends HttpServlet {
private static final long serialVersionUID = 1L;
public UserWS() {
super();
}
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"));
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request,response);
}
}测试一下，当我们发送：http://localhost:8080/jsonTest/user?action=getuserinfo&userid=1
返回：
{
"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
测试一下：http://localhost:8080/jsonTest/project?action=getmyprojects&userid=1
返回：
{
"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库再做一次封装，这是架构师要注意的一个重要趋势。