很奇怪的错误

Question

预设条件为：spDetailForm.setIs_arbitrage("0");
当我增加COUNTY_ID可以查询，不加的话就报列名错误。拿到数据库运行没有问题。中间我测试的list可以正确查询！
public java.util.List<ChannelSpDetail> getSpDetailList(
ChannelSpDetailForm spDetailForm, int ipageIndex, int ipageSize)
throws AibiabrException {
// TODO Auto-generated method stub
String sSQL = "SELECT * FROM BIA_BR_CHANNEL_SP"
+(spDetailForm.getCity_id()!=null && spDetailForm.getCity_id().trim().length()>0 && spDetailForm.getData_month()!=null && spDetailForm.getData_month().trim().length()>0?"_"+spDetailForm.getCity_id()+"_"+spDetailForm.getData_month().replaceAll("-", ""):"")+" T WHERE 1=1";
java.util.List _tmp_list = new java.util.ArrayList();
System.out.println(spDetailForm.getIs_arbitrage());
if(spDetailForm.getIs_arbitrage()!=null && spDetailForm.getIs_arbitrage().trim().length() > 0)
{
sSQL += " AND IS_ARBITRAGE=?";
_tmp_list.add(spDetailForm.getIs_arbitrage());
}
if(spDetailForm.getCounty_id()!=null && spDetailForm.getCounty_id().trim().length() > 0)
{
sSQL += " AND COUNTY_ID=?";
_tmp_list.add(spDetailForm.getCounty_id());
}
if(spDetailForm.getChannel_type()!=null && spDetailForm.getChannel_type().trim().length() > 0)
{
sSQL += " AND CHANNEL_TYPE LIKE ?";
_tmp_list.add("%"+spDetailForm.getChannel_type()+"%");
}
if(spDetailForm.getChannel_level()!=null && spDetailForm.getChannel_level().trim().length() > 0)
{
sSQL += " AND CHANNEL_LEVEL=?";
_tmp_list.add(spDetailForm.getChannel_level());
}
if(spDetailForm.getChannel_name()!=null && spDetailForm.getChannel_name().trim().length() > 0)
{
sSQL += " AND CHANNEL_NAME=?";
_tmp_list.add(spDetailForm.getChannel_name());
}
Object[] objs = new Object[_tmp_list.size()];
System.out.println(_tmp_list);
for(int i=0;i<_tmp_list.size();i++)
{
objs[i] = _tmp_list.get(i);
}
try {
int iCount_Total = 0;
if(ipageSize>=0 && ipageIndex>=0)
{
String sSQL_Total = "SELECT COUNT(*) FROM("+sSQL+")";
iCount_Total = jdbctemplate.queryForInt(sSQL_Total,objs);
}
//System.out.println("iCount_Total:"+iCount_Total);
java.util.List<ChannelSpDetail> lBack = null;
if(ipageSize>=0 && ipageIndex>=0 && iCount_Total >=0 )
{
sSQL = "SELECT * FROM (SELECT T_1.*,ROWNUM AS ROW_NUM FROM ("
+ sSQL
+ " ) T_1 WHERE ROWNUM<="+ (ipageIndex*ipageSize)+")"
+ " T_2 WHERE T_2.ROW_NUM>"
+ ((ipageIndex-1)*ipageSize<0?0:(ipageIndex-1)*ipageSize);

// List list = jdbctemplate.queryForList(sSQL,objs);
// System.out.println("list:"+list);
// System.out.println(sSQL);
//下边这行会报列名无效。
lBack = (List<ChannelSpDetail>)jdbctemplate.query(sSQL,objs,new ResultSetExtractor(){
public List<ChannelSpDetail> extractData(ResultSet rs) throws SQLException{

 java.util.List<ChannelSpDetail> _obj_list = new java.util.ArrayList<ChannelSpDetail>();
 while(rs.next())
          {
 ChannelSpDetail _obj = new ChannelSpDetail();
  _obj.setCounty_id(rs.getString("county_id"));
  _obj.setChannel_type(rs.getString("channel_type"));
  _obj.setChannel_level(rs.getString("channel_level"));
  _obj.setChannel_id(rs.getString("channel_id"));
  _obj.setChannel_name(rs.getString("channel_name"));
  _obj.setPrincipal_id(rs.getString("principal_id"));
  _obj.setPrincipal_name(rs.getString("principal_name"));
  _obj.setBill_id(rs.getString("bill_id"));
  _obj.setSp_code(rs.getString("sp_code"));
  _obj.setSp_name(rs.getString("sp_name"));
  _obj.setSp_fee(rs.getString("sp_fee"));
  _obj.setIncome(rs.getString("income"));
  _obj.setIs_arbitrage(rs.getString("is_arbitrage"));
 
  _obj_list.add(_obj);
          }
return _obj_list;
         }});
} if(lBack !=null && lBack.size()>0)
{
lBack.get(0).setTotal_size(iCount_Total);
}

return lBack;
} catch (Exception e) {
log.error(e.getMessage());
throw new AibiabrException("SP套利稽核详单查询失败,数据库错误",e.getMessage());
}
}

Answer 1

 +(spDetailForm.getCity_id()!=null && spDetailForm.getCity_id().trim().length()>0 && spDetailForm.getData_month()!=null && spDetailForm.getData_month().trim().length()>0?"_"+spDetailForm.getCity_id()+"_"+spDetailForm.getData_month().replaceAll("-", ""):"")+" T WHERE 1=1";你这个后面有一个 where 
如果你不加这个语句，那你的查询条件中就不存在where 这个关键字了，肯定会报错
 

Answer 2

如果真的报列名无效的话！那么： _obj.setCounty_id(rs.getString("county_id"));
                             _obj.setChannel_type(rs.getString("channel_type"));
                             _obj.setChannel_level(rs.getString("channel_level"));
                             _obj.setChannel_id(rs.getString("channel_id"));
                             _obj.setChannel_name(rs.getString("channel_name"));
                             _obj.setPrincipal_id(rs.getString("principal_id"));
                             _obj.setPrincipal_name(rs.getString("principal_name"));
                             _obj.setBill_id(rs.getString("bill_id"));
                             _obj.setSp_code(rs.getString("sp_code"));
                             _obj.setSp_name(rs.getString("sp_name"));
                             _obj.setSp_fee(rs.getString("sp_fee"));
                             _obj.setIncome(rs.getString("income"));
                             _obj.setIs_arbitrage(rs.getString("is_arbitrage"));
的某一个列名肯定有错，你得仔细找找