本文共 8956 字，大约阅读时间需要 29 分钟。

PostgreSQL特点：

1. PostgreSQL 可以在所有主要操作系统中运行；

2. PostgreSQL支持文本、图像、声音和视频； 3. 支持SQL的基本功能，例如： 复杂SQL查询，子选择，外键，触发器，视图，事务，多进程并发控制（MVCC），流式复制（9.0），热备（9.0） 4. 在PostgreSQL中， 表可以设置为从 ‘父’表继承其特征； 5. 可以安装多个扩展以向PostgreSQL添加附加功能；

PostgreSQL常用工具：

1. psql: 命令行工具， 也是管理PostgreSQL的主要工具；

2. pgAdmin 是免费开源图形用户界面管理工具； 3. pgFouine: 日志分析器，可以从PostgreSQL日志文件创建报告。

1. 数据库切换中出现的差别记录

1. 不能建与表同名的索引。

   create index store on store; ×

2. 支持boolean类型， 可以存储T/F, TRUE/FALSE/NULL。
3. 不支持clob类型和blob类型。 在oralce中， clob字段的数据与记录不是存在一起，它会存储数据的指针（long类型），相当于是内存地址； 在PostgreSQL中，可以使用text类型存储。
4. 数据库字段类型为integer时，对应的jdbc查询类型也是Integer（oracle中查询类型是BigDecimal）; 使用聚合函数count(1)时的返回值类型为BigInteger(oracle中查询类型为BigDecimal)。
5. postgreSQL与oracle中函数的使用差异：

   oracle	postgreSQL	描述
   bitant(a, b)	a & b	按位与运算
   nvl(a, 0)	coalesce(a, 0)	判空
   to_number(int)	to_number(int, text) 例如: to_number(123, "999999") : text表示精度	转成数值类型
   to_char(int)	to_char(int, text) 例如 to_char(123, "999999") : text表示精度	转成字符类型
   to_date(text)	to_date(text, text)	格式化日期
   sysdate	current_date	当前时间
   instr('great', 'eat)	positon('eat' IN 'great')	字符串中包含字符
   序列名.nextval	nextval(序列名）	序列的下一个取值

6. 注意：要保留时分秒时用to_timestamp(text, text)
   例如：

   insert into monthsettle(no,startdate,finishdate,holder,settler,settletime)values(200301,to_timestamp('2003.1.1 0:00:01','yyyy.mm.dd hh24:mi:ss'),to_timestamp('2003.1.25 23:59:59','yyyy.mm.dd hh24:mi:ss'),'','',null);

7. 左连接和右连接 （使用标准写法）

   oracle: 左连接 : a.id = b.id(+) ;         右连接 : a.id(+) = b.id

   postgreSQL: 左连接：a left join b on a.id = b.id             右连接：a right join b on a.id = b.id

8. 系统隐藏字段ctid, 是每行数据在表中一个物理标识符， 和oracle的rowid类似； 有一点不同，当表被vacuumfull或该行值被update时该值会被改变。
9. oracle使用rownum分页, postgreSQL使用limit.
10. decode函数：
    oracle：

    decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)   该函数的含义如下：IF 条件=值1 THEN　　　　RETURN(翻译值1)ELSIF 条件=值2 THEN　　　　RETURN(翻译值2)　　　　......ELSIF 条件=值n THEN　　　　RETURN(翻译值n)ELSE　　　　RETURN(缺省值)END IFdecode(字段或字段的运算，值1，值2，值3）    这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3 当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多

    postgreSQL:

    Select CASE      WHEN foo = 'hi'   THEN 'there'      WHEN foo = 'good' THEN 'bye'      ELSE 'default'  END

11. 查询时别名使用关键字时需要用双引号（“”）
    例如： select findex index from dual;
    index为sql关键字， 所以需要改成： select findex \"index\"(将双引号转义）
12. 使用QueryDefition时字段类型需要严格对应，比如 ： integer类型就需要传integer; String类型传String （QueryDecoder里面的参数需要进行转换）
    例如 ：

    QueryDefinition def = new QueryDefinition();   def.addCondition("storeGid", Integer.valueOf(user.getUuid()));

    当storeGid字段数据类类型为integer时， 需要转成对应的数据类型传入，不然会报错(integer == varying)。

13. 自动生成uuid方法
    需要安装扩展程序： create extension "uuid-ossp";(只用安装一次）
    oracle： sys_uuid(); 
    postgreSQL： uuid_generate_v1()、 uuid_generate_v4()
14. 分页查询时使用NativeBigInQueryExecutor， 不使用NativeQueryExecutor。

    NativeBigInQueryExecutor queryExecutor = new NativeBigInQueryExecutor(getEm(), sq, fields);    QueryResult
         
           qr = queryExecutor.query(definition.getPage(),        definition.getPageSize(), WholeSaleBck.class);
         

15. 虚表dual问题 ： pgsql没有dual虚拟表， 为保证兼容性，可以创建伪视图代替，并赋予权限。

    create or replace view dual as     select NULL::"unknown" where 1=1;    alter table dual owner to postgres;    grant all on table dual to postgres;    grant select on table dual to public;

16. 子查询： pgsql必须有别名

    select count(1) from (select * from store) s;

17. Oracle中的"" 和NUll是相同的， 但是postgreSQL不同， 所以当插入空值时需要修改为NULL.

    insert into store (name) values ('');  insert into store (name) values (null);  第一种方式获取的值并不等于null.

18. 字符串连接符 || ， Oracle中 "a" || null 结果为 "a"; 而pgsql中结果为NULL。 所以使用concat（）函数代替。 但是pgsql没有concat方法， 所以可以创建函数concat代替；

    create or replace function concat(text, text)     returns text as   $body$select coalesce($1,'') || coalesce($2,'')$body$   language 'sql' volatile;    alter function concat(text, text) owner to postgres;

19. 用关键字作为字段名的字段需要区分大小写, 使用sql脚本执行的表名和字段名默认都为小写， 使用客户端生成的表如果有大写，将会带上双引号，例如： “ABc”

    create table abc (   username  varchar(30),   type  varchar(30),   id integer);select * from abc; select * from ABC; (查询的表都为abc表)select * from "ABc"; (查询的表为ABc表)关键字： type，使用双引号时INSERT INTO ABC ("TYPE") VALUES ("ab"）; ×INSERT INTO ABC ("type") VALUES ("ab");  √

20. pgsql不支持procedure和package，都需要改写成function。 当package有全局变量的情况修改起来比较麻烦，我们是用临时表传递的。
21. oracle没有继承和重载特性，pgsql支持继承和函数重载.
22. jdbc差异：
    Oracle的jdbc连接字符串：db.url=jdbc:oracle:thin:@192.168.1.1:1521:ORCL
    Postgresql的连接字符串：db.url=jdbc:postgresql:@192.168.1.1:5432/database
23. PostgreSQL不支持update的别名语法

    update fauser o set o.note='xxxx';提示报错：ERROR:  column "o" of relation "fauser" does not existLINE 1: update fauser o set o.note='xxxx'                             ^SQL state: 42703Character: 21

2. clob类型的处理

问题背景

• 当字段为clob/blob类型时，实体类中对应的属性增加注解@Lob，该属性用String/Clob/Character/Blob/Byte等类型作为返回类型。
• oracle数据库对该属性处理是在getClob方法调用了getLong，然后试图将这个CLob转换成Long。
• postgreSQL不支持clob类型，使用text存储大数据。

分析过程

1. hibernate根据查询的结果集转化成对应实体类时出现的类型错误。

AbstractEntityPersister.hydrate()加载数据结果集：

final ResultSet propertyResultSet = propertyIsDeferred ? sequentialResultSet : rs;   final String[] cols = propertyIsDeferred ? propertyColumnAliases[i] : suffixedPropertyColumns[i];   values[i] = types[i].hydrate( propertyResultSet, cols, session, object );

• 其中cols是实体类的每个属性值， types是每个属性对应的type；
• 再根据每个属性的type找到BasicType，去设置对应的值。
  例如：

  public class User {   private String username;   private Integer password; }

  User类定义的属性username为String, 对应hibernate在加载该实体为持久化对象时绑定的type就是默认的string, 对应的BasicType是StringType;

2. Hibernate解析type去赋值 (types.hydrate( propertyResultSet, cols, session, object )

protected final T nullSafeGet(ResultSet rs, String name, WrapperOptions options) throws SQLException {       return sqlTypeDescriptor.getExtractor( javaTypeDescriptor ).extract( rs, name, options ); }

public AbstractStandardBasicType(SqlTypeDescriptor sqlTypeDescriptor, JavaTypeDescriptor
   
     javaTypeDescriptor) {     this.sqlTypeDescriptor = sqlTypeDescriptor;     this.javaTypeDescriptor = javaTypeDescriptor;}
   

通过上面源码可以看到： 基本类型AbstractStandardBasicType有两个属性sqlTypeDescriptor 和 javaTypeDescriptor 。 每个继承AbstractStandardBasicType类的子类都会给这两个参数赋予初始值。

• 当Hibernate对实体类的field赋值时，会根据field的类型（继承于AbtractStandardBasicType）去执行相应的赋值操作。
  所以Hibernate在持久化实体类的时候就会对每个field确定一个基本类型，用来后续操作中的赋值。

3. Hibernate初始化实体类中标注有@Lob类型的字段处理

SimpleValueBinder类中绑定type

else if ( property.isAnnotationPresent( Lob.class ) ) {		if ( mappings.getReflectionManager().equals( returnedClassOrElement, java.sql.Clob.class ) ) {			type = "clob";		}		else if ( mappings.getReflectionManager().equals( returnedClassOrElement, java.sql.Blob.class ) ) {			type = "blob";		}		else if ( mappings.getReflectionManager().equals( returnedClassOrElement, String.class ) ) {			type = Hibernate.MATERIALIZED_CLOB.getName();		}		else if ( mappings.getReflectionManager().equals( returnedClassOrElement, Character.class ) && isArray ) {			type = Hibernate.WRAPPER_CHARACTERS_CLOB.getName();		}		else if ( mappings.getReflectionManager().equals( returnedClassOrElement, char.class ) && isArray ) {			type = Hibernate.CHARACTERS_CLOB.getName();		}		else if ( mappings.getReflectionManager().equals( returnedClassOrElement, Byte.class ) && isArray ) {			type = Hibernate.WRAPPER_MATERIALIZED_BLOB.getName();		}		else if ( mappings.getReflectionManager().equals( returnedClassOrElement, byte.class ) && isArray ) {			type = Hibernate.MATERIALIZED_BLOB.getName();		}

可以看到当实体类的java类型为String时，type = Hibernate.MATERIALIZED_CLOB.getName()， 对应的基本类型是MaterializedClobType。

查看MaterializedClobType源码：

public MaterializedClobType() {	this(		ClobTypeDescriptor.DEFAULT,		new AlternativeLobTypes.ClobTypes
   
    ( MaterializedClobType.class )	);	}	protected MaterializedClobType(SqlTypeDescriptor sqlTypeDescriptor,								   AlternativeLobTypes.ClobTypes
    
      clobTypes) {		super( sqlTypeDescriptor, StringTypeDescriptor.INSTANCE, clobTypes );	}	public String getName() {		return "materialized_clob";	}
    
   

由上述代码可以看到， MaterializedClobType中默认的sqlTypeDescriptor 是ClobTypeDescriptor.DEFAULT

从而让hibernate以ClobTypeDescriptor类型来处理clob类型的字段。

解决方案

• 定义EJB时 ，取消@Lob标注， 按String对待；
• 定义EJB时， 保留@Lob标注， 增加@Type((type = "org.hibernate.type.TextType")注解；
• 在hibernate 4时，数据库方言基础类中支持替换sqlTypeDescriptor； 可以重载PostgreSqlDialect类的remapSqlTypeDescriptor()方法， 将Clob当成longvarchar处理。

例如：

@Overridepublic SqlTypeDescriptor remapSqlTypeDescriptor(SqlTypeDescriptor sqlTypeDescriptor) {   switch (sqlTypeDescriptor.getSqlType()){   case Types.CLOB:   return LongVarcharTypeDescriptor.INSTANCE;   case Types.BLOB:   return LongVarbinaryTypeDescriptor.INSTANCE;}   return super.remapSqlTypeDescriptor(sqlTypeDescriptor);}

• 当前hibernate版本为3.6.8，且要同时支持oracle对clob的处理不冲突。
  Dialect类中提供了addTypeOverride（BasicType typeOverride）方法。
  这样就可以将MaterializedClobType类型进行重写， 让其当成longvarchar处理。

  public static final StringClobType INSTANCE = new StringClobType(      LongVarcharTypeDescriptor.INSTANCE, null);  protected StringClobType(SqlTypeDescriptor sqlTypeDescriptor,      AlternativeLobTypes.ClobTypes
       
         clobTypes) {    super(sqlTypeDescriptor, clobTypes);  }  @Override  public String getName() {    return "materialized_clob";  }
       

再重写数据库方言， 注册重写的basicType.

public PostgreSqlDialect() {    super();    addTypeOverride(StringClobType.INSTANCE);  }

转载地址：http://hqani.baihongyu.com/