**二.jdbcTemplate的查询**
BeanPropertyRowMapperquery过程都可以进行数据类型自动转换,而且不仅仅按标准命名,还可以支持下划线分隔后拼接成驼峰式字符
例如:属性名称(vehicleNo)匹配到同名列或带下划线的同名列(VEHICLE_NO),如果某个属性不匹配则返回属性值为Null;需要Javabean命名规范
public class BeanPropertyRowMapper<T> implements RowMapper<T> ..注:BeanPropertyRowMapper 实现了 RowMapper 接口
1.单查询
**注意:**如果查询得到多个值或者查不到结果会报错Incorrect result size: expected 1, actual 0或者Incorrect result size: expected 1, actual 3(N)
public ShopMerchant getMerchantById(String id) {
try {
return jdbcTemplate.queryForObject("SELECT * FROM shop_merchant WHERE id= ?",new Object[]{id},new BeanPropertyRowMapper<ShopMerchant>(ShopMerchant.class));
} catch (DataAccessException e) {
returnnull;
}
}
2.多查询返回值为集合形式
public List<ShopMerchantAccount> showMerchant(Integer current, int i, ShopMerchantAccount shopMerchantAccount,
String selectedCompanyid) {
StringBuilder sql=new StringBuilder();
sql.append(" SELECT a.id,a.merchant_id,a.`name`,a.account,a.`password` ,a.`status` FROM shop_merchant_account a,shop_merchant m WHERE m.id=a.merchant_id ");
commonsql(sql,selectedCompanyid,shopMerchantAccount);
sql.append(" ORDER BY a.`status` DESC, a.update_time DESC LIMIT "+current+","+i+" ");
return jdbcTemplate.query(sql.toString(),new BeanPropertyRowMapper<ShopMerchantAccount>(ShopMerchantAccount.class));
}
private void commonsql(StringBuilder sql, String selectedCompanyid, ShopMerchantAccount shopMerchantAccount) {
if (StringUtils.isNotBlank(selectedCompanyid)) {
sql.append(" AND m.company_id='"+selectedCompanyid+"' ");
}
if (StringUtils.isNotBlank(shopMerchantAccount.getName())) {
sql.append(" AND a.`name` LIKE '%"+shopMerchantAccount.getName()+"%' ");
}
if (StringUtils.isNotBlank(shopMerchantAccount.getAccount())) {
sql.append(" AND a.account LIKE '%"+shopMerchantAccount.getAccount()+"%' ");
}
if (StringUtils.isNotBlank(shopMerchantAccount.getStatus())) {
sql.append(" AND a.`status`= '"+shopMerchantAccount.getStatus()+"' ");
}
}
三.其他非自定义对象查询
sql.append(" SELECT count(1) FROM shop_merchant_account a,shop_merchant m WHERE m.id=a.merchant_id ");
return jdbcTemplate.queryForObject(sql.toString(), Integer.class);