PostgreSQL 递归查询

WITH提供了一种在更大的SELECT查询中编写子查询的方式。这个通常称为公共表表达式或CTEs的子查询可以认为是定义存在于查询中的临时表。这个特性的一个应用是用于分解复杂查询为简单的部分。

递归查询评估

评估无递归术语。使用UNION(并不是UNION ALL)，去除重复的行。包括在递归查询结果中所有剩余的行，并将它们放入临时的工作表。 只要工作表不为空，那么将重复这些步骤： 评价递归术语，为递归自我参照替换当前工作表内容。 用UNION(并不是UNION ALL)，去除重复的行和与以前 结果行重复的行。 包括所有在递归查询结果中剩余的行，并将它们放入一个临时的中间表。 以中间表的内容替换工作表的内容，然后清空中间表。

Note: 严格的说，该过程是迭代而不是递归，但是RECURSIVE是通过SQL标准委员会选择的术语

可选的RECURSIVE修饰符从仅有的语法便利性到一个完成事情的特性的改变。 使用RECURSIVE，一个WITH查询可以引用它自己的输出。

一个简单的例子就是查询从1加到100的和：

WITH RECURSIVE t(n) AS (
        VALUES (1)
    UNION ALL
        SELECT n+1 FROM t ...
more ...

PostgreSQL 创建template_postgis模板

Create PostGIS template

su - postgres
psql postgres
postgres=# \c template1
postgres=# CREATE EXTENSION postgis;
createdb geodata -T template1

or

psql -d template_postgis -c "CREATE EXTENSION postgis;"
more ...

hibernate 执行sql语句总结

1.使用SQLQuery

hibernate对原生SQL查询执行的控制是通过SQLQuery接口进行的.

Session.createSQLQuery();

1.1标量查询

最基本的SQL查询就是获得一个标量（数值）的列表。

sess.createSQLQuery("SELECT * FROM CATS").list();
sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").list();

将返回一个Object数组(Object[])组成的List，数组每个元素都是CATS表的一个字段值。Hibernate会使用ResultSetMetadata来判定返回的标量值的实际顺序和类型。 如果要避免过多的使用ResultSetMetadata,或者只是为了更加明确的指名返回值，可以使用addScalar()。

sess.createSQLQuery("SELECT * FROM CATS")
    .addScalar("ID", Hibernate.LONG)
    .addScalar("NAME", Hibernate.STRING)
    .addScalar("BIRTHDATE", Hibernate ...
more ...

JSONObject 过滤字段

使用net.sf.json.JSONObject包

在使用JSONObject.fromObject把对象转换成json时，经常会有一些不需要的字段，可以通过配置文件设置忽略来精简json长度，并可忽略部分无法转换成json的字段，如空间变量。

建立配置文件

JsonConfig jsonConfig = new JsonConfig();

设置默认忽略

jsonConfig.setIgnoreDefaultExcludes(false);

添加忽略字段

jsonConfig.setExcludes(new String[]{"positions"});


JSONObject json = JSONObject.fromObject(map, jsonConfig);
more ...

SQL小计合计汇总

CREATE TABLE #t(province nvarchar(10),city nvarchar(10),score int) 
insert into #t values('陕西','西安',3 )
insert into #t values('陕西','安康',4 )
insert into #t values('陕西','汉中',2 )
insert into #t values('广东','广州',5 )
insert into #t values('广东','珠海',2 )
insert into #t values ...
more ...

PostgreSQL 添加 UUID

[felix@centos ~]$ sudo yum install postgresql93-contrib.x86_64
[felix@centos ~]$ sudo su postgres

bash-4.1$ psql

postgres=# CREATE EXTENSION "uuid-ossp";
CREATE EXTENSION
postgres=# select uuid_generate_v4() ;
uuid_generate_v4
--------------------------------------
e34f4b0b-aeae-4999-bda8-08eb9705dcc2
(1 row)
more ...

PostgreSQL 配置

修改PostgreSQL数据库的默认用户postgres的密码(注意不是linux系统帐号)

  1. PostgreSQL登录(使用psql客户端登录)

    sudo -u postgres psql

    psql (9.3.5)

    Type "help" for help.

    postgres=#

  2. 修改PostgreSQL登录密码：

     postgres=# ALTER USER postgres WITH PASSWORD 'postgres';
 postgres=# select * from pg_shadow ;

  3. 退出PostgreSQL psql客户端

    postgres=# \q

修改linux系统的postgres用户的密码（密码与数据库用户postgres的密码相同）

sudo passwd postgres

修改PostgresSQL数据库配置实现远程访问

vim /var/lib/pgsql/9.3/data/postgresql.conf

  1. 监听任何地址访问，修改连接权限 ...

more ...