Spring jdbcTemplate的常规操作

对于jdbcTemplate的DI操作不做细说,主要记录jdbcTemplate的常规操作
*一.jdbcTemplate批量插入*

**updateCounts**记录的是每执行一条sql影响的行数,此数组的长度与需要插入的记录长度相等时说明都已经插入
public int insert(final List<goodsImages> imagesList) {
        String sql = " INSERT INTO goods_images (id, goods_image_id, goods_commonid, \n" +
                "      store_id, color_id, goods_image, \n" +
                "      goods_image_sort, is_default,recent_date,upload_status)\n" +
                "    VALUES (?,?,?, ?,?,?, ?,?,?,?)";
        try {
            **final List<goodsImages> temList = imagesList;**  
            int[] **updateCounts** = jdbcTemplate.batchUpdate(
                    sql,
                    new BatchPreparedStatementSetter() {
                        @Override
                        public void setValues(PreparedStatement ps, int i) throws SQLException {
                            ps.setString(1, UUIDutil.getUUID());
                            ps.setString(2, temList .get(i).getGoodsImageId());
                            ps.setString(3, temList .get(i).getGoodsCommonid());
                            ps.setString(4, temList .get(i).getStoreId());
                            ps.setString(5, temList .get(i).getColorId());
                            ps.setString(6, temList .get(i).getGoodsImage());
                            ps.setString(7, temList .get(i).getGoodsImageSort());
                            ps.setString(8, temList .get(i).getIsDefault());
                            ps.setTimestamp(9,temList .get(i).getRecentDate()==null?
                                    new Timestamp(System.currentTimeMillis()):temList .get(i).getRecentDate());
                            ps.setString(10,temList .get(i).getUploadStatus());
                        }

                        @Override
                        public int getBatchSize() {
                            return temList .size();
                        }
                    }
            );
            if (updateCounts.length == temList .size()) {
                return 1;
            }
        } catch (DataAccessException e) {
            log.error("保存触角商品将数据保存到okwuyou_goods_images表时出现异常={}",e.getMessage());
            log.error("失败的数据是={}", JSON.toJSONString(imagesList));
        }
        return 0;
    }

**二.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) {
            return null;
        }
    }

    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);