JdbcTemplate批量插入和有则更新无则新增等sql样例

为什么80%的码农都做不了架构师？>>>   

/**
 * 报表dao
 *
 * @author zhaojp-a@2018-6-22 15:38:18
 */
@Component
public class DashboardReportManager {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 获取所有报告列表
     *
     * 删除的和终止状态的不会获取
     * @param project
     * @return
     */
    public List<Report> list(Project project) {
        StringBuffer sb = new StringBuffer(
                " SELECT * FROM " + project.getDbName() + ".bim5d_zx_report " +
                        " WHERE is_deleted <> 1 AND report_status <> 3 ORDER BY order_num DESC");
        List<Report> list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowsMapper<>(Report.class));
        return list;
    }

    /**
     * 获取前台报告列表
     *
     * 只返回已发布的报告
     * @param project
     * @return
     */
    public List<Report> boardList(Project project) {
        StringBuffer sb = new StringBuffer(
                " SELECT * FROM " + project.getDbName() + ".bim5d_zx_report " +
                        " WHERE is_deleted <> 1 AND report_status = 2  ORDER BY order_num DESC");
        List<Report> list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowsMapper<>(Report.class));
        return list;
    }

    /**
     * 获取单条数据
     * @param project
     * @param reportId
     * @return
     */
    public Report get(Project project,Long reportId) {
        StringBuffer sb = new StringBuffer(
                " SELECT * FROM " + project.getDbName() + ".bim5d_zx_report " +
                        " WHERE id = ?");
        return jdbcTemplate.queryForObject(sb.toString(),new Object[]{reportId},new BeanPropertyRowMapper<>(Report.class));
    }

    /**
     * 新增
     *
     * @param project
     * @param report
     * @return
     */
    public int add(Project project, Report report) {
        StringBuffer sb = new StringBuffer(
                " INSERT INTO " + project.getDbName() + ".bim5d_zx_report " +
                        " (id,report_name,report_unit,remarks,sys_revision,sys_creator_id,report_status,sys_modified_time) VALUES (?,?,?,?,1,?,1,?)");
        int rs = jdbcTemplate.update(sb.toString(),report.getId(), report.getReportName(), report.getReportUnit(), report.getRemarks(),report.getSysCreatorId(),report.getSysModifiedTime());
        return rs;
    }

    /**
     * 更新
     *
     * @param project
     * @param report
     * @return
     */
    public int update(Project project, Report report) {
        StringBuffer sb = new StringBuffer(
                " UPDATE " + project.getDbName() + ".bim5d_zx_report " +
                        " SET ");
        List<Object> param = new ArrayList<>();
        List<String> sqlParts = new ArrayList<>();
        if (report.getReportUnit() != null) {
            sqlParts.add("report_unit=?");
            param.add(report.getReportUnit());
        }
        if (report.getReportName() != null) {
            sqlParts.add("report_name=?");
            param.add(report.getReportName());
        }
        if (report.getRemarks() != null) {
            sqlParts.add("remarks=?");
            param.add(report.getRemarks());
        }
        if (report.getReportStatus() != null && report.getReportStatus() >= 0) {
            sqlParts.add("report_status=?");
            param.add(report.getReportStatus());
        }
        if (report.getIsDeleted() != null) {
            sqlParts.add("is_deleted=?");
            param.add(report.getIsDeleted());
        }
        if (report.getSysModifierId() != null) {
            sqlParts.add("sys_modifier_id=?");
            param.add(report.getSysModifierId());
        }
        if (report.getSysModifiedTime() != null) {
            sqlParts.add("sys_modified_time=?");
            param.add(report.getSysModifiedTime());
        }
        if (report.getCostBeginTime() != null) {
            sqlParts.add("cost_begin_time=?");
            param.add(report.getCostBeginTime());
        }
        if (report.getCostEndTime() != null) {
            sqlParts.add("cost_end_time=?");
            param.add(report.getCostEndTime());
        }
        sb.append(StringUtils.join(sqlParts,","));
        sb.append(" WHERE id = ? ");
        param.add(report.getId());
        int rs = jdbcTemplate.update(sb.toString(), param.toArray());
        return rs;
    }

    /**
     * 新增
     *
     * @param project
     * @param report
     * @return
     */
    public int addProgress(Project project, Report report) {
        StringBuffer sb = new StringBuffer(
                " INSERT INTO " + project.getDbName() + ".bim5d_zx_report " +
                        " (report_name,report_unit,remarks) VALUES (?,?,?)");
        int rs = jdbcTemplate.update(sb.toString(), report.getReportName(), report.getReportUnit(), report.getRemarks());
        return rs;
    }
    /**
     * 批量insert
     */
    public int saveData(String dbName, String tableName, List<String> fieldList, List<String> positionCharList, List<Object[]> paramList) {
        StringBuffer createSql = new StringBuffer(" insert ignore into `" + dbName + "`." + tableName + " (" + StringUtils.join(fieldList, ",") + ")  values (" + StringUtils.join(positionCharList, ",") + ")");
        int[] rs = jdbcTemplate.batchUpdate(createSql.toString(), paramList);
        return sum(rs, rs.length);
    }
    
    /**
     * 批量执行sql
     */
    public int batchUpdate(Project project, List<CostCommitDetail> costCommitDetails) {
        if (costCommitDetails.size() == 0) {
            return 0;
        }
        String sql[] = new String[costCommitDetails.size() + 1];
        int index = 0;
        for (CostCommitDetail costCommitDetail : costCommitDetails) {
            StringBuffer sb = new StringBuffer("UPDATE " + project.getDbName() + ".bim5d_cost_commit_detail " +
                    " SET  table_reversion = " + costCommitDetail.getTableReversion() +
                    " WHERE commit_id = " + costCommitDetail.getCommitId() + "  AND table_name = '" + costCommitDetail.getTableName() + "'");
            sql[index] = sb.toString();
            index++;
        }
        int[] rs = jdbcTemplate.batchUpdate(sql);
        return CostCommonManager.sum(rs, rs.length);
    }

    /**
     * 有则更新，无则新增
     */
    public int insertOrUpdate(Project project, String tableName) {
        StringBuffer sb = new StringBuffer(" insert into "+project.getDbName()+".bim5d_cost_table_reversion (table_name,max_reversion,update_time)  values(?,1,NOW()) on  DUPLICATE key update max_reversion=max_reversion+1;");
        return jdbcTemplate.update(sb.toString(), tableName);
    }

}

 

转载于:https://my.oschina.net/luanwu/blog/1924012