PostgreSQL及JDBC学习过程中的坑坑洼洼碎碎念

对Postgre每一种数据类型及数据库特征交互在JDBC中的细节进行全面的了解，在这个过程中，了解到很多坑，这里简单分享给大伙，希望可以避免或如何去修复。

 

JDBC maven引用路径为：

<dependency>

   <groupId>org.postgresql</groupId>

   <artifactId>postgresql</artifactId>

   <version>9.3-1102-jdbc4</version>

</dependency>

 

【money数据类型用getObject()会报错】

   org.postgresql.util.PSQLException: 不良的类型值 double : ￥123,456.00

at org.postgresql.jdbc2.AbstractJdbc2ResultSet.toDouble(AbstractJdbc2ResultSet.java:3059)

at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getDouble(AbstractJdbc2ResultSet.java:2383)

at org.postgresql.jdbc2.AbstractJdbc2ResultSet.internalGetObject(AbstractJdbc2ResultSet.java:152)

at org.postgresql.jdbc3.AbstractJdbc3ResultSet.internalGetObject(AbstractJdbc3ResultSet.java:36)

at org.postgresql.jdbc4.AbstractJdbc4ResultSet.internalGetObject(AbstractJdbc4ResultSet.java:300)

at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getObject(AbstractJdbc2ResultSet.java:2704)

    如何解：

    用getString()可以获取到正确的数据，要具体的值，自己处理。

    出现问题的原因：

    JDBC中获取到的columnType的值为8，等价于Types.DOUBLE，在getObject的时候按照这种方式去处理，就会发现字符串：“￥123,456.00”无法转换为double因此报错。

    可能的风险：

    开源框架下，例如ibatis之类的反射处理，可能会先做getObject来判定类型，那么这个时候如果直接就报错了，就有问题了。

 

 

【bit数据类型用getObject()如果有值则返回false】

    细节描述：

       当getObject()获取数据时，如果该值不为空，没有返回二进制的数字值或二进制字符串，而是始终返回false。当为空时依旧返回null。

    如何解：

       使用getString()可以获取到正确的二进制字符串。通过getInt获取到的并非二进制字符串对应的数字值，例如二进制011，对应数字值应该是3，但通过getInt返回的是11。

    出现问题的原因：

       搞不懂，可能写JDBC的人没有细节测试过，或许这个是开源界的问题。

    可能的风险：

       同上一个例子的风险一致。

 

 

【boolean数据类型获取的metadata类型是Types.BIT】

     问题描述：

      很多时候，我们会根据结果集的metadata的Type来封装Java的数据类型，这个地方是boolean值，不过获取到的类型是Types.BIT，而Types的描述中是有Types.BOOLEAN这种类型的。

      那么问题来了，如果判定逻辑中认为是BIT类型将会按照上面第2个坑的解法来处理：getString()来处理，但boolean类型时，如果用getString()获取到的将是"t"或"f"两个字符串。而并非"true"|"false"两个字符串，这样的字符串即使使用Boolean.valueOf(str)获取到的布尔值也是错误的值，得到的将全部是false，这样子为了解决上一个坑就会掉进另一个坑，因此为了解决这个坑还得想其它的办法。

     如何解：

       a)、该问题同上，只能在BIT判定基础上写死代码，再判定类型是不是bool，如果是则使用getObject()来处理，否则与bit相关的两个逻辑放在一起就会有逻辑冲突。

       b)、不要用数字来判定数据类型，而是用类型的字符串来判定。

       c)、将错就错，得到t或f字符串后，"t" 映射为 true、"f" 映射为 false。不过呢要考虑到万一那一天JDBC把这个BUG修复了，写代码的需要考虑兼容性问题。

     出现问题的原因：

       同上，估计也是一个遗留很久但很少有人提到的BUG，优先级就被无底线地降低了。

     可能的风险：

       同上一个例子的风险一致。

 

 

【int2vector类型数据比较神奇】

     为啥说神奇，因为它本身就是一个数组类型，但是不能直接用ARRAY写进去，以至于我当初搞不懂它和普通的数字类型到底有啥区别。

     如果要数组写进去，目前我知道的有两个办法：

        方法1：ARRAY[12,23]::int2vector           在后面带上类型描述，有点小蛋疼。

        方法2：'12  23 32'                        用空格分隔

     因此，如果要写入int2vector[]就需要在这个基础上再做ARRAY。在文章后面还会提到int2vector[]与JDBC的问题。

 

 

【几个日期类型的精确度】

     timestamp、time、interval精确到微妙（小数点后6位），而不是毫秒。在处理数据的时候大家要注意精度丢失问题，JDBC正常处理时时没问题的，我在测试过程中倒是发现了fastJSON在序列化的时候，对于Timestamp类型采用的是getTime来做的序列化，这个动作会丢失掉毫秒以后的值。

     原因：Java的Timestamp类型描述是由一个long和一个int数据两组来表达，long部分只表达毫秒以上的部分，int标示的秒以下的部分，因此精度会丢失。

     疑问：为什么Java的Timestamp中两个数字中，其中long值管理的是毫秒以上值，int是秒以下？

           对于java的Date类型只有一个long值来表达日期值，这个long值会包含到毫秒值，Timestamp类型是Date类型的子类，它用一个int来表达秒值以下的部分，精确到到纳秒，因此最多9位10进制，而其余的部分都属于父类Date的内容，因此毫秒值的管理就与父类有点冲突，Timestamp为了解决这个问题，在构造方法中传递给父类super日期值的时候，会将其除以1000再乘以1000，去掉毫秒值部分，这样父类就只会精确到秒，毫秒部分的3位都是0了，然后秒以下的部分全部由int部分来表达了。

 

     date精确到日、abstime 精确到秒，这两种数据类型在我看来还是有用途的，与常规大部分日期的格式应该算是一致的吧。

     

【bytea数据类型操作需要注意的细节】

     写入这种数据一般用16进制格式，不过一般有两种方式写入：

      insert into test(v_bytea) values(E'\\xDEADBEEF');

      insert into test(v_bytea) values('\xDEADBEEF');

     中文文档中是推荐第一种写法，不过大家注意的是，两个反斜杠前面要一个E开头，否则会将'\\xDEADBEEF'中的每一个字符当成一个字节来写入，而不是将DE AD BE EF这3个字节写入到数据库中。

     当然JDBC中可用setBytes(byte[])将字节数据直接写入到数据库中。

  

【JDBC在事务操作的细节处理上】

     1、开启BEGIN后，若执行一个报错的SQL语句，则必须在同一个会话中执行END语句才可以继续使用当前的connection会话，否则执行任何语句都会报错。当然在JDBC上的处理时隐式的，这一点请看第二点：

     2、JDBC执行setAutocommit(false）时，会先向数据库发送一个COMMIT提交掉上一个事务，JDBC客户端会设置一个事务待提交状态。并且如果处于该状态时，通过该连接第1个SQL语句时，会先执行BEGIN语句操作，如果要在执行某SQL报错时，要继续执行其他SQL必须执行以下几个动作之一：

        2.1.通过JDBC执行END。

        2.2.通过JDBC执行COMMIT或调用connection.commit()方法，在这个方法内部会自动做这样的动作，并且将JDBC客户端的状态设置为事务空闲状态;

        2.3.通过JDBC执行ROLLBACK或调用connection.rollback();方法。解释同上。

        2.4.connection.setAutocommit(false);再设置一次也会将事务状态处理，上文有解释，它会自动发起一次COMMIT操作。但一定是设置false才会生效发生这个动作。

 

 

 

【JDBC的metadata中的细节】

     getCatalogName(int)  不能获取库名，其他MySQL、OracleJDBC应该是OK的。

     getSchemaName(int)   不能获取schema名，MySQL、Oracle、SQL Server的JDBC应该支持。

     isReadOnly(int)、isWriteable(int) 返回的数据不会根据表是否有主键来控制，二进制类型、bit类型也是返回true，因此细节需要自己判定，不能根据该项信息来做控制代码逻辑。

 

 

【关于多维数组的碎碎念】

     1、PG当中99%的数据类型都可以定义数组类型，查询的时候可以用数组下标访问例如select user_desc[1] from user;也可以在update语句中使用同样的方式修改某一个数组下标的元素,注意，数组下标是从1开始，不是从0。真尼玛方便。

 

     2、为啥说方便呢，正常如果用其它如MySQL、Oracle一般会拿一个BLOB或CLOB来存放这样的数组数据或JSON、XML数据，但是如果你要修改某一个节点的元素，需要将整个大字段读取出来，然后在程序中修改后将整个大字段写回去，这中间对数据库、网络和程序的开销都是很巨大的。有这玩意确实方便，据可靠消息，JSON格式的数据可以在数据上建立索引，这是要兼容mongodb文档数据库的节奏，呵呵，所以我说真尼玛的方便，其余的还有空间、范围类型我这就不多说了。

 

     3、不过坑来了，第一个坑就是建表时可以指定字段的数组维度，例如create table a(col bigint[][]);代表是二维数组，但是从元数据中却找不到这个维数，我发现pgAdmin也没法加载这个维数。我尝试去做了一些数据写入和测试，发现一个很神奇的特征：即使定义为bitint[]一位数组，也可以将二维、三维数组的数据写进去，定义成三维、四维数组也可以将一位数组写进去。那么是不是这个维数是假的呢？我又测试了一点点东西发现不是：如果建立成多维数组，写进去是低维度的，查询时，用数组下标取访问是没有问题的，不过多维数组的访问下标一定要用这种方式user_desc[1:1][1:2]，你也可以想访问第几维就第几维，如果访问内层的数据，会返回外部所有层次的结构。问题来了，如果建立为一维数组，写入多维数据，要根据多维去检索就不行了，返回的内容有点看不懂，估计PG自己也没算明白吧，呵呵！

 

     4、如果数据库为数组类型，那么JDBC中通过getObject()默认获取到对象类型是org.postgresql.jdbc4.Jdbc4Array，在Type类型上获取的也是2003，也就是Types.ARRAY，正常情况下，可以通过Object []objects = (Object[])jdbc4Array.getArray();来获取到一个数组对象，并且与Java中的类型能够对应上，多维数组也是在Java中会生成对应的多维数组，我测试了数字和字符串基本是OK的。不过遇到一种特殊的int2vector[]类型时，这个地方会报错：

     这个 org.postgresql.jdbc4.Jdbc4Array.getArrayImpl(long,int,Map) 方法尚未被实作。

at org.postgresql.Driver.notImplemented(Driver.java:729)

at org.postgresql.jdbc2.AbstractJdbc2Array.buildArray(AbstractJdbc2Array.java:771)

at org.postgresql.jdbc2.AbstractJdbc2Array.getArrayImpl(AbstractJdbc2Array.java:171)

at org.postgresql.jdbc2.AbstractJdbc2Array.getArray(AbstractJdbc2Array.java:128)

 

     这个时候，尝试过通过getResult()来获取一个结果集来遍历，没有报错，但是遍历出来的结果十分奇葩，和实际的数据似乎一点关系都没有。

     因此对于这个类型，代码里头也只能写死逻辑处理用getString()来处理，然后自己根据业务需求来解析扩展。

 

 

【PostgreSQL的同一个会话Connection无法切换DB】

     在命令行下或许可以通过这种方式切换：\c db1 来切换到一个名为db1的数据库。

     不过这个不是SQL语句，如果单独发送给server端是不会认的。在iDB Cloud中有些地方基于UI的连接，这地方要做特殊处理，不过代码上的处理判定逻辑一定不是“isPostgreSQL()”做这样的处理，而是canChooseDB()的逻辑。

 

【默认结果集不能使用absolute(int)】

      基于ibatis分页的自动分页控件或某些客户端软件会定位行去修改数据可能会用到。

      默认的结果集方式是FORWARD_ONLY，这种方式的结果集是无法调用absolute(int)方法的，如果调用就会报错。

      如果设置了setFetchSize(int)，要让其生效必须开启事务（否则一次性获取所有数据，具体请参考脚仙的源码分析文章：http://askdba.alibaba-inc.com/libary/control/getArticle.do?articleId=17962），如果开启了事务，则结果集必须是FORWARD_ONLY状态，否则也会报错，因此在设置了fetchSize的时候，是肯定不能使用absolute(int)方法的。

 

【爆一些pgAdmin的小问题】

    1、pgAdmin执行多个SQL语句时，只会显示最后一个SQL执行结果，前面的执行结果和消息都会被覆盖掉，不过经过测试，前面的SQL肯定是执行了的。

    2、pgAdmin在windows上提供PSQL Console界面，在命令行上直接拷贝内容不太方便，需要在title上去点击右键完成。类似需求建议使用终端命令行，windows的DOS可直接在里面右键完成。psql -h xxx.xxx.xxx.xxx -U postgres

 

 

【PostgreSQL的DLL】

     1、PG的DLL是直接修改元数据库，因此速度非常快。和Oracle一样，不会导致锁表和IO压力，这一点我认为是做得比MySQL、SQL Server好的地方。

     2、PG默认情况下修改数据类型只能在一个范围内，例如数字类型修改的目标也一定是数字类型，这样就不用发生数据迁移动作。不过据老唐介绍，可以通过比较复杂的SQL表达式来完成转换动作，不过牺牲的肯定是性能。

     3、PG的DLL是可以回滚的，根据老唐的介绍有以下几个关键点：

          3.1.PG如果执行truncate语句，是在系统表中分配一个新的表给你，而老的表是改一个名字，在事务结束前如果出现问题可以换回来，因此truncate是可以回滚的，而且理论上代价不高。

          3.2.PG如果执行DROP语句与上面类似，也与oracle类型，只是将表改名字，其余的不会动，因此回滚就是回滚系统表的数据。

          3.3.PG如果执行加字段、改字段名、改表名、改备注等动作，不会迁移数据，只是改元数据。

          3.4.如果执行修改字段的类型，如果是同质化类型转换理论上也不需要数据转换，若不同类型可以转换，代价会稍微大一点。

          3.5.索引也是类似，加索引肯定会单独来处理，修改索引如果是名称也是无所谓的，如果是重建索引肯定这个开销是逃不掉的。

 

    总之，理论上讲，PG的DDL基本与DML区别不是很大，都是可以ROLLBACK的。

 

【其它小坑】

     1、中文文档中第8.5部分，描述数据类型部分，日期部分的timestamp、time、interval描述是识别到毫秒，实际正确的应该是微妙，这个部分已经做了测试。

 

 

转：