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的路径,会提高执行效率!