Oracle迁移PostgreSQL系列文章之三:java中注意点

Posted on 2015-03-06 17:03:47 by osdba

1. fetch size Oracle与PG的区别

当把程序迁移过来时,当存在有返回很多行的SQL时,常常发生占用内存过多,甚至发生OOM(out of memory)的问题。当把heap dump出来以后,发现一些ResultSet占用了大概600M,而原先在oracle上面就没有问题。这个原因是PG的JDBC驱动默认会一次把query的结果都取完存在客户端, 导致占用了大量的内存的原因,这个问题可以见PostgreSQL官网:http://www.postgresql.org/message-id/3DA5AD73.7090502@xythos.com 中的内容:

This has been discussed before on this list many times. But the short answer is that that is how the postgres server handles queries. If you issue a query the server will return the entire result. (try the same query in psql and you will have the same problem). To work around this you can use explicit cursors (see the DECLARE CURSOR, FETCH, and MOVE sql commands for postgres).

此问题可以通过设置只取一定的行数来解决,方法在PostgreSQL的JDBC的官网也给出了例子 http://jdbc.postgresql.org/documentation/head/query.html#fetchsize-example :

Changing code to cursor mode is as simple as setting the fetch size of the Statement to the appropriate size. Setting the fetch size back to 0 will cause all rows to be cached (the default behaviour).

代码摘录如下:

// make sure autocommit is off
conn.setAutoCommit(false);
Statement st = conn.createStatement();

// Turn use of the cursor on.
st.setFetchSize(50);
ResultSet rs = st.executeQuery("SELECT * FROM mytable");
while (rs.next())
{
   System.out.print("a row was returned.");
}
rs.close();

// Turn the cursor off.
st.setFetchSize(0);
rs = st.executeQuery("SELECT * FROM mytable");
while (rs.next())
{
   System.out.print("many rows were returned.");
}
rs.close();

// Close the statement.
st.close();

Oracle的JDBC驱动每次返回10条记录,可以在Oracle的官方文档上看到:https://docs.oracle.com/cd/E11882_01/java.112/e16548/resltset.htm#JJDBC28621

Fetch Size

By default, when Oracle JDBC runs a query, it retrieves a result set of 10 rows at a time from the database cursor. This is the default Oracle row fetch size value. You can change the number of rows retrieved with each trip to the database cursor by changing the row fetch size value.

Standard JDBC also enables you to specify the number of rows fetched with each database round-trip for a query, and this number is referred to as the fetch size. In Oracle JDBC, the row-prefetch value is used as the default fetch size in a statement object. Setting the fetch size overrides the row-prefetch setting and affects subsequent queries run through that statement object.

Fetch size is also used in a result set. When the statement object run a query, the fetch size of the statement object is passed to the result set object produced by the query. However, you can also set the fetch size in the result set object to override the statement fetch size that was passed to it.

另顺便说一下,MySQL也是有此问题,解决此问题还需要设置MIN_VALUE才能起效,具体见: http://stackoverflow.com/questions/20496616/fetchsize-in-resultset-set-to-0-by-default

By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate, and due to the design of the MySQL network protocol is easier to implement. If you are working with ResultSets that have a large number of rows or large values, and cannot allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.

To enable this functionality, create a Statement instance in the following manner:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
              java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

The combination of a forward-only, read-only result set, with a fetch size of Integer.MIN_VALUE serves as a signal to the driver to stream result sets row-by-row. After this, any result sets created with the statement will be retrieved row-by-row.

2. 一些数据类型的问题

PostgreSQL中存在丰富的数据类型,使用JDBC操作这些数据类型,需要注意 。

2.1 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)

出现问题的原因:

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

解决办法:

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

可能的风险:

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

2.2 bit数据类型用getObject()如果不为空总是返回false

现象描述:

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

如何解:

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

可能的风险: 同上一个例子的风险一样。

2.3 boolean 数据类型问题

问题描述:

boolean数据类型获取的metadata类型是Types.BIT,很多时候,我们会根据结果集的metadata的Type来封装Java的数据类型,这个地方是boolean值,不过获取到的类型是Types.BIT,而Types的描述中是有Types.BOOLEAN这种类型的。通常在java中如果判定逻辑中认为是BIT类型将会按getString()来处理,然后再通过把Boolean.valueOf(str)转换成boolean类型,但因为用getString()获取到的将是"t"或"f"两个字符串,而并非"true"或"false"两个字符串,这样的字符串使用Boolean.valueOf(str)获取到的布尔值也是错误的值,得到的将全部是false,如果不注意会导致一个严重的问题。

如何解:

得到"t"或"f"字符串后,"t" 映射为 true、"f" 映射为 false。

2.4 几个日期类型的精度问题

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部分来表达了。

2.5 int2vector类型的注意

虽然它内部是用一个数组实现的,但在JDBC中不能当成数组处理,而要做为一个字符串来处理。不能直接用ARRAY写进去,如果要输入有两个办法:

方法1:类型转换

ARRAY[12,23]::int2vector;

方法二:用字符串表示:

'12 23 32';

注意分隔符不是逗号,而是空格。

3. 元数据的注意事项

  • PostgreSQL中获得的列名都是小写的,而Oracle都是大写的,需要注意。
  • getCatalogName(int) 不能获取库名,其他MySQL、OracleJDBC应该是OK的。
  • getSchemaName(int) 不能获取schema名,MySQL、Oracle、SQL Server的JDBC应该支持。
  • isReadOnly(int)、isWriteable(int) 返回的数据不会根据表是否有主键来控制,二进制类型、bit类型也是返回true,因此细节需要自己判定,不能根据该项信息来做控制代码逻辑。