从2019年开始，就有一个很火热的话题：“去O化”。O就是oracle，也就是将oracle替换成别的数据库。为什么要去O？大致有以下原因：
- oracle是收费的，为了进一步降低成本；
- 以美国为首的西方国家对华科技种种遏制行为，最近越闹越厉害，最近docker的付费服务就禁止中国企业使用；
- 甲骨文公司中国区大幅度裁员，或将放弃中国市场也说不定；
- 2020年12月31起，甲骨文公司将不再对oracle11.2版本提供技术支持，即出现bug也不会维护了。
鉴于以上种种原因，很多企业都在更换数据库，但是如果新的数据库语法和oracle差别很大，那工作量会特别大，权衡之下，postgreSQL是个不错的选择。
本人前两周就在做这个“去O化”，将遇到的改造点记录下来，供大家参考。
1、jar包/maven依赖的更换：
驱动包要换成postgresql-xxx.jrex.jar，x表示版本。如果是maven项目，则添加如下依赖：
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>版本</version>
</dependency>
欢迎大家关注我的公众号 javawebkf，目前正在慢慢地将简书文章搬到公众号，以后简书和公众号文章将同步更新，且简书上的付费文章在公众号上将免费。
2、driverClassName等信息的更换：
datasource.driverClassName=org.postgresql.Driver
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
3、字段类型问题：
- 主外键字段类型问题：A表的主键在B表做外键时，这个字段在两张表的类型一定要一致，否则连接查询会报错。
- 实体类与数据表字段类型问题：实体类字段类型一定要与数据表字段类型对应，否则映射的时候就报错了。
- 查询条件的字段类型问题：假如数据表中id字段类型是int4，查询条件传String类型的1，是会报错的，要用Integer类型，而oracle不会报错，会自动进行类型转换。
4、postgre与java字段类型对照表：
以下是常用字段类型对照：
| postgre | java |
|---|---|
| varchar | String |
| char | String |
| text | String |
| int2/int4 | Integer |
| int8 | Long |
| float4 | Float |
| float8/money | Double |
| numeric | Bigdecimal |
| bool | Boolean |
| bytea | byte[] |
5、序列问题：
- oracle获取序列：
my_sequece.nextval - postgre获取序列：
nextval('my_sequece')
6、sysdate替换方案：
| oracle | postgre | |
|---|---|---|
| 当前日期 时间 | sysdate | now() |
| 当前日期 | trunc(sysdate) | now() :: date |
| ?分钟前 | sysdate - ?/24/60 | now() - (? || 'min')::interval |
| ?天前 | sysdate - ? | now() - (? || 'day')::interval |
7、NVL函数替换方案：
oracle中的NVL(arg1, arg2)用来设置默认值，arg1为空就设置为arg2。postgre中可以用coalesce(arg1, arg2)实现相同效果。
8、分页问题：
比如要查询user表第一页，每页显示10条数据：
- oracle中用rownum进行分页：
select * from (select aa.*, rownum rn from (select * from user) aa where rownum <= 11 ) where rn > 1;
- postgre中用limit进行分页：
select * from user limit 10 offset 1
查询user表5条数据：
- oracle写法：
select * from user where rownum <= 5
- postgre写法：
select * from user limit 5
9、不等于的问题：
postgre中column !=-?会报错，负号和数字应用括号括起来，或者!=统一改成<>。
10、数字类型字段模糊查询问题：
非String类型的字段要进行模糊查询，需要先将数据库字段类型转成varchar，如下：
and cast(user_phone as varchar) LIKE ?
11、update语句相关问题：
postgre中，update语句不能设置表别名，否则会报错。
12、count和order by的问题：
select count(*)的语句不能加order by，否则会报错。
13、order by的问题：
group by的字段一定要在select中查出来，并且如果group by的字段有使用函数，select的时候也要使用相同的函数，例如：
select upper(user_id) from user order by upper(user_id)
14、blob类型的问题：
oracle中用blob类型可以存储文件，在java中也用blob类型对应。postgre与之对应的是bytea，在java中用byte[]数组对应就可以了。
15、clob类型的问题：
oracle中用clob存储大文本，在java中也用clob类型对应。postgre与之对应的是text，在java中直接用String对应就可以了。
16、decode函数问题：
oracle中的decode(arg1, arg2, arg3, arg4)函数，表示：当 arg1 等于 arg2 时，取 arg3 ，否则取 arg4。postgre中没有类似的函数，可以用如下方式实现：
case when arg1 = arg2 then arg3 else arg4 end
17、to_date函数的问题：
特别注意，postgre中to_date函数转出来的是不带时分秒的时间，如果想要带时分秒的，需要用to_timestamp。
18、substr函数的问题：
substr(arg, begin, num)函数，表示对arg进行截取，从第begin位开始，截取num个。oracle中，arg可以是字符串也可以数字类型，但是postgre中只支持对字符串的截取，如果要对数字进行截取，得写成：SUBSTR(1.23 :: TEXT,1,3)。还有一点，oracle中substr(1.23, -2)表示截取最后两位，结果就是23，postgre中不支持这种用法，要实现相同功能，可以用right函数：right(1.23::text, 2)。
19、 listagg(column1, ',') within group(column2)函数的问题：
这个函数的意思是将column2相同的多行记录的column1的值合并成一行，例如：
SELECT tr.tr_gw_no,
listagg(tr.tr_status, ',') WITHIN GROUP(ORDER BY tr.tr_status) status
FROM trade tr
where tr_gw_no = 12198006
or tr_gw_no = 12167001
GROUP BY tr.tr_gw_no;
查询结果就是：
查询结果
postgre中可以用string_agg函数实现相同的功能，具体用法如下：
SELECT
tr_gw_no,
string_agg ( tr_status :: TEXT, ',' ) status
FROM
trade
WHERE
tr_gw_no = 12198006
OR tr_gw_no = 12167001
GROUP BY
tr_gw_no;
20、start with connect by函数的问题：
oracle的这个函数是用来查树形结构的，即同一张的表的记录有父子级关系的那种。oracle中用法如下：
select module_id
from sys_modules
start with module_id = ?
connect by prior module_id = module_fid;
这就表示查询module_id为?的所有子module，即父模块idmodule_fid为?的所有的记录。postgre可以用WITH RECURSIVE实现相同的效果，如下：
WITH RECURSIVE subtabela AS (
SELECT
module_id
FROM
sys_modules
WHERE
module_id = ? UNION ALL
SELECT
tt.module_id
FROM
sys_modules tt
INNER JOIN subtabela st ON tt.module_fid = st.module_id
) SELECT
*
FROM
subtabela;
21、存储过程调用的问题：
postgre11开始，支持存储过程procedure，之前的版本只支持function。通过代码去调用存储过程时，要注意以下三点：
- 调用存储过程的sql语句不需要加大括号，加了大括号的调用的是function而不是procedure；
- 如果存储过程中用了事务，那个在代码中调用时就不要使用spring的事务了，否则会冲突；
- 如果存储过程有
inout参数，表示这个即是输入又是输出参数，都要进行设置，oracle可以只当作输入或者输出来用。
比如现在有一个名为test_procedure(IN "id" int4, INOUT "result" int4)的存储过程，java中调用方式如下：
Session session = sessionFactory.getCurrentSession();
Connection conn = session.connection();
CallableStatement cs = null;
Integer returnInfo = -1;
cs = conn.prepareCall("call p_paynum_processing_create(?,?)");
// oracle中调用要加大括号
// cs = conn.prepareCall("{call p_paynum_processing_create(?,?)}");
// 输入的参数
cs.setInt(1, id.intValue());
// 输出的参数
cs.setInt(2, -1); // oracle中这一行可以不用
cs.registerOutParameter(2, Types.INTEGER);
// 执行存储过程
cs.execute();
// 获取返回值，-1为操作异常
returnInfo = cs.getInt(2);
22、连接查询的问题：
oracle中外连接可以这样写：
select * from tableA a, tableB b where a.id = b.id(+);
有(+)的一方是副表，另一方是主表，即上面那种写法表示左外连接。postgre不支持这种写法，可以用left join代替。
23、instr函数的问题：
instr函数表示包含，postgre中可以用strpos函数替代。
网友评论