所有的sql,务必在数据库中执行测试通过后,再放到代码中去,特别注意查不出数据,查出数据为空、插入数据为null的这些情况的处理
1、like
if (StringUtils.isNotEmpty(lotId)) {
sql.append(” AND T.LOT_ID LIKE ? “);
args.add(“%” + lotId + “%”);
}
2、日期
if (StringUtils.isNotEmpty(startDay)) {
sql.append(” AND INFO.UPLOAD_DATE >= to_date(?,?) “);
args.add(startDay);
args.add(MiscUtils.DATE_FORMAT);
}
if (StringUtils.isNotEmpty(endDay)) {
sql.append(” AND INFO.UPLOAD_DATE < to_date(?,?) “);
args.add(endDay);
args.add(MiscUtils.DATE_FORMAT);
}
3、rs.getLong(“xxx”)默认返回0,如果需要返回null,需要判断:
Long orderRrn = rs.getLong(“ORDER_RRN”);
lot.setOrderRrn(rs.wasNull() ? null : orderRrn);
特别注意,必须先取出值rs.getLong(“ORDER_RRN”),然后立即判断rs.wasNull() 。
4、select count(*)
public Long queryLotCount(String lotId) {//即便lotId不存在,也不会抛异常,而是返回0
String sql = “SELECT COUNT(*) FROM LOT WHERE lot_id = ?”;
return jdbcTemplate.queryForLong(sql,new Object[] { lotId});
}
注意:select count(*) 始终会有结果,如果是select MAX(lot_rrn)等其它函数,则必须测试查询不到结果的情况
5、NOT EXISTS、UNION (除非有必要,不必写这么麻烦)
public Integer getNeedCheckProductCount() {
JdbcTemplate jdbcTemplate = new JdbcTemplate();
String sql_count = " SELECT COUNT(DISTINCT PRODUCT_RRN) FROM ( "
+ " SELECT DISTINCT T1.PRODUCT_RRN FROM PRODUCT_LAYER_MASK_TEMP T1 WHERE T1.STATUS = 'INACTIVE' OR NOT EXISTS "
+ " (SELECT T2.PRODUCT_RRN FROM PRODUCT_LAYER_MASK T2 WHERE T1.PRODUCT_RRN = T2.PRODUCT_RRN AND T1.PROCESS_RRN = T2.PROCESS_RRN AND T1.LAYER_ID = T2.LAYER_ID ) "
+ " UNION "
+ " SELECT DISTINCT T1.PRODUCT_RRN FROM PRODUCT_LAYER_MASK T1 WHERE NOT EXISTS "
+ " ( SELECT T2.PRODUCT_RRN FROM PRODUCT_LAYER_MASK_TEMP T2 WHERE T1.PRODUCT_RRN = T2.PRODUCT_RRN AND T1.PROCESS_RRN = T2.PROCESS_RRN AND T1.LAYER_ID = T2.LAYER_ID )"
+ " )";
Integer count = jdbcTemplate.queryForInt(sql_count);
return count;
}
6、查一行一列
try {
JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSource());
String sql = "SELECT T.ATTRIBUTE_VALUE FROM UNIT_ATTRIBUTE T WHERE T.UNIT_RRN = ? ;
return (String) jdbcTemplate.queryForObject(sql, new Object[] { unitRrn}, new RowMapper() {
@Override
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
return rs.getString("ATTRIBUTE_VALUE");
}
});
} catch (EmptyResultDataAccessException e) {
return null;//如果没有查到值会报异常,这里捕获异常后根据实际情况处理返回值
} catch (IncorrectResultSizeDataAccessException e) {
throw new ServiceException(e);//查出的结果数量大于1,会进入这里,因为无法处理该异常,所以一般不捕获而是直接抛出
}
7、Spring的JdbcTemplate语法详解
https://blog.csdn.net/alan_liuyue/article/details/72910365
8、批量执行sql
public void updateCarrierMapping(Long carrierRrn, Long carrierMappingRrn) throws CarrierDAOException {
String sql1 = "UPDATE " + DatabaseNames.CARRIER + " SET CARRIER_MAP_RRN=" + carrierMappingRrn
+ " WHERE CARRIER_RRN=" + carrierRrn;
String sql2 = "UPDATE " + DatabaseNames.CARRIER_MAPPING + " SET CARRIER_RRN =" + carrierRrn
+ " WHERE CARRIER_MAP_RRN=" + carrierMappingRrn;
String sql3 = "UPDATE " + DatabaseNames.CARRIER + " SET AVAILABLE_SLOT_COUNT=(SELECT SLOT_COUNT-count(*) FROM "
+ DatabaseNames.CARRIER_MAPPING + " WHERE carrier_map_rrn=" + carrierMappingRrn + " )"
+ " WHERE CARRIER_RRN=" + carrierRrn;
String[] sql = new String[] { sql1, sql2, sql3 };
jdbcTemplate.batchUpdate(sql);
}
9、批量update,方式二
public void batchUpdateLinkset(final List list) {
String sql = "update LINK_SET set N_CONFIRM=?,TIME_STAMP=?,DOMAIN_ID=?,SIGLINKSET_NAME=? where NE_ID=?";
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
public int getBatchSize() {
return list.size();
//这个方法设定更新记录数,通常List里面存放的都是我们要更新的,所以返回list.size();
}
public void setValues(PreparedStatement ps, int i)throws SQLException {
Linkset linkset = (Linkset) list.get(i);
ps.setString(1, linkset.getCHINA_NAME());
ps.setString(2, linkset.getENGLISH_NAME());
ps.setInt(3, linkset.getN_CONFIRM());
ps.setString(4, linkset.getTIME_STAMP());
ps.setInt(5, linkset.getDOMAIN_ID());
ps.setString(6, linkset.getSIGLINKSET_NAME());
ps.setString(7, linkset.getNE_ID());
}
});
}
10、时间加减
天数N可以用如下方法实现:
select sysdate+N from dual,
sysdate+1 加一天
sysdate+1/24 加1小时
sysdate+1/(24*60) 加1分钟
sysdate+1/(24*60*60) 加1秒钟
在当前时间加一月 或一年:
select sysdate,add_months(sysdate,12) from dual; –加1年
select sysdate,add_months(sysdate,1) from dual; –加1月
11、查询结果集中日期处理
rs.getString(“process_start_time”) 有默认格式,
也可以自定义格式:
m.put(“job_start_time”,MiscUtils.timestampToLongString(rs.getTimestamp(“process_start_time”)));
12、时间插入
jdbcTemplate插入时间只能用ps.setTimestamp(),如果用 ps.setDate();只能把年月日插入到数据库,时分秒会丢失
获取时间也应该用rs.getTimestamp();而不用rs.getDate()
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
int j = 1;
ps.setTimestamp(j++, new java.sql.Timestamp(System.currentTimeMillis()));
}
@Override
public int getBatchSize() {
return size;
}
});
13、注意:
1、多用query 自己处理查询结果
2、使用queryForXxx的时候注意没有查询结果的时候是否会抛异常
3、分页问题,同一个数据出现在相邻页面
4、注意查询出一个null和一条数据都未查到,这两个是有很大区别的,必须分清楚并加以测试