Oracle使用经验点滴

1、由setLong()产生的空指针异常：

ref = jdbcTemplate.update(sql, new PreparedStatementSetter() {

	@Override
	public void setValues(PreparedStatement ps)
			throws SQLException {
       ....
       ps.setLong(18, crefFlag);

 如果这里的crefFlag为null，数据库定义的类型为INTEGER或NUMBER，则会抛出空指针异常，解决方法，先进行非空判断，如果为空则置0：

if (crefFlag == null) {
	ps.setLong(18, 0);
} else {
	ps.setLong(18, crefFlag);
}

 

2、在进行多表联查（包括自联接）时使用特殊处理方式

修改前：

SELECT T0.CORG_ID       CENT_ID,
 T0.CORG_NAME     ENT_NAME,
 T1.CORG_ID       CORG_ID,
 T1.CORG_NAME     ORG_NAME,
 T2.CUSER_ID      CUSER_ID,
 T2.CNAME         CNAME,
 T2.CTELEPHONE_NO CTELEPHONE_NO,
 T2.CFAX          CFAX,
 T2.CCREATOR_ID   CCREATOR_ID,
 T2.CEMAIL        CEMAIL,
 T2.CUSER_STATUS  CUSER_STATUS
  FROM T_ENTERPRISE T0, T_ENTERPRISE T1, T_USER T2, T_ENT_ORG_USER T3
 WHERE T2.CENT_ID != 'XL_01'
   AND T0.CORG_ID IN
       (SELECT CENT_ID FROM T_USER WHERE UPPER(CNAME) LIKE UPPER('%co%'))
   AND T2.CUSER_ID IN
       (SELECT CUSER_ID FROM T_USER WHERE UPPER(CNAME) LIKE UPPER('%co%'))
   AND T0.CORG_ID = T1.CENTERPRISE_ID
   AND T1.CORG_ID = T3.CORG_ID
   AND T2.CUSER_ID = T3.CUSER_ID
 ORDER BY T2.CNAME, T1.CORG_ID;

修改后：

SELECT /*+FIRST_ROWS*/
 T0.CORG_ID       CENT_ID,
 T0.CORG_NAME     ENT_NAME,
 T1.CORG_ID       CORG_ID,
 T1.CORG_NAME     ORG_NAME,
 T2.CUSER_ID      CUSER_ID,
 T2.CNAME         CNAME,
 T2.CTELEPHONE_NO CTELEPHONE_NO,
 T2.CFAX          CFAX,
 T2.CCREATOR_ID   CCREATOR_ID,
 T2.CEMAIL        CEMAIL,
 T2.CUSER_STATUS  CUSER_STATUS
  FROM T_ENTERPRISE T0, T_ENTERPRISE T1, T_USER T2, T_ENT_ORG_USER T3
 WHERE T2.CENT_ID != 'XL_01'
   AND T0.CORG_ID IN
       (SELECT CENT_ID FROM T_USER WHERE UPPER(CNAME) LIKE UPPER('%co%'))
   AND T2.CUSER_ID IN
       (SELECT CUSER_ID FROM T_USER WHERE UPPER(CNAME) LIKE UPPER('%co%'))
   AND T0.CORG_ID = T1.CENTERPRISE_ID
   AND T1.CORG_ID = T3.CORG_ID
   AND T2.CUSER_ID = T3.CUSER_ID
 ORDER BY T2.CNAME, T1.CORG_ID;

   

修改后，Oracle会调整执行SQL的路径，会提高执行效率！