jdbcTemplate个人小结

//取得jdbcTemplate对象
JdbcTemplate jdbcTemplate=(JdbcTemplate) applicationContext.getBean("jdbcTemplate");
//或者注入
@Resource
JdbcTemplate jdbcTemplate;
//本地获取jdbcTemplate对象
//JdbcTemplate jdbcTemplate=(JdbcTemplate) AppUtil.getBean("jdbcTemplate") 

//输入参数通过BeanPropertyRowMapper查询并转换成实体类
String sql = "SELECT * FROM CircuitSection where connectionkind=? ";
BeanPropertyRowMapper<TCimCircuitSection> rowMapper = new BeanPropertyRowMapper<TCimCircuitSection>(TCimCircuitSection.class);
TCimCircuitSection mainCs = jdbcTemplate.queryForObject(sql, rowMapper, "主干线");

//批量插入
public void insertData(List<GzrepDwjzmx> list) {
        String sql = "INSERT INTO W_DWJZMX (MY_ID_,XH,SDSD,SF,DQ,XQ,DW,DG,DGDW,DC,JZBH,RL,LX,DYDJ,CQDW,TCRQ,ZT,TYSJ,BZ,SWDJ,TLDJ,TXDJ,CCDJ,ZDJ,SBZT)" + " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)";

        List<Object[]> lists = new ArrayList<Object[]>();
        for (GzrepDwjzmx gd : list) {
            Object[] os = new Object[] { gd.getMyId(), gd.getXh(), gd.getSdsd(), gd.getSf(), gd.getDq(), gd.getXq(), gd.getDw(), gd.getDg(), gd.getDgdw(), gd.getDc(), gd.getJzbh(), gd.getRl(), gd.getLx(), gd.getDydj(), gd.getCqdw(), gd.getTcrq(), gd.getZt(), gd.getTysj(), gd.getBz(), gd.getSwdj(), gd.getTldj(), gd.getTxdj(), gd.getCcdj(), gd.getZdj() ,gd.getSbzt()};
            lists.add(os);
        }

        jdbcTemplate.batchUpdate(sql, lists);
    }

//    批量删除
    public void deleteBetchData(List<QwfgdlfxbAll> list) {
        
//        String sql = "DELETE FROM W_QWFGDLFXBALL WHERE RQ=#{?,jdbcType=DATE}";
        String sql = "DELETE FROM W_QWFGDLFXBALL WHERE RQ=?";

        List<Object[]> lists = new ArrayList<Object[]>();
        for (QwfgdlfxbAll e : list) {
            Object[] os = new Object[]{e.getRq()};
            lists.add(os);
        }

//        int[] batchUpdate = jdbcTemplate.batchUpdate(sql, lists);
//        System.out.println("成功删除的条数==="+batchUpdate.length);
    }

      //queryForList

        String userId = curUser.getUserId();
            
        String sqlRole = 
        "select name_ from ebos_org_group  where group_id_ in"+
        "("+
        "select group_id_ from EBOS_ORG_USER_GROUP t where user_id_ ='"+userId+"'"+
        ")";
               
        List<Map<String, Object>> queryForList = jdbcTemplate.queryForList(sqlRole);
        boolean flag = false;
        for(Map<String, Object> map : queryForList){
            //System.out.println(map.get("name_"));
            if("审批人".equals(map.get("name_"))){
                flag = true;
            }
        }

旧版jdbc即使用BatchPreparedStatementSetter对象操作

 // 批量插入 旧版jdbc批量插入（使用BatchPreparedStatementSetter对象），这里似乎有个bug，插入的时间没有时分秒的，而且只能用setObject不然值为空的话就报错
    public void insertData(List<YjDlfhBaseData> list) {
        String sql = "INSERT INTO W_YJDLFHBASEDATA (ID,F_RQ,F_ZDM,F_DRZ,F_DYZ," + "F_DNZ,F_DRZD,F_DYZD,F_DNZD,F_DRZDSK," + "F_DYZDSK,F_DNZDSK,F_DRZX,F_DYZX,F_DNZX,"
                + "F_DRZXSK,F_DYZXSK,F_DNZXSK,F_QNJRZ,F_QNJYZ," + "F_QNJNZ,F_ZT,F_DRPJZ,F_DYPJZ,F_DNPJZ)" + " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        final List<YjDlfhBaseData> list2 = list;

        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return list2.size();
            }

            public void setValues(PreparedStatement ps, int index) throws SQLException {
                int j = 1;
                YjDlfhBaseData e = list2.get(index);

                ps.setObject(j++, e.getId());
                ps.setObject(j++, new java.sql.Date(e.getRq().getTime()));
                ps.setObject(j++, e.getZdm());
                ps.setObject(j++, e.getDrz());
                ps.setObject(j++, e.getDyz());
                ps.setObject(j++, e.getDnz());
                ps.setObject(j++, e.getDrzd());
                ps.setObject(j++, e.getDyzd());
                ps.setObject(j++, e.getDnzd());
                ps.setObject(j++, e.getDrzdsk() == null ? null : new java.sql.Date(e.getDrzdsk().getTime()));
                ps.setObject(j++, e.getDyzdsk() == null ? null : new java.sql.Date(e.getDyzdsk().getTime()));
                ps.setObject(j++, e.getDnzdsk() == null ? null : new java.sql.Date(e.getDnzdsk().getTime()));
                ps.setObject(j++, e.getDrzx());
                ps.setObject(j++, e.getDyzx());
                ps.setObject(j++, e.getDnzx());
                ps.setObject(j++, e.getDrzxsk() == null ? null : new java.sql.Date(e.getDrzxsk().getTime()));
                ps.setObject(j++, e.getDyzxsk() == null ? null : new java.sql.Date(e.getDyzxsk().getTime()));
                ps.setObject(j++, e.getDnzxsk() == null ? null : new java.sql.Date(e.getDnzxsk().getTime()));
                ps.setObject(j++, e.getQnjrz());
                ps.setObject(j++, e.getQnjyz());
                ps.setObject(j++, e.getQnjnz());
                ps.setObject(j++, e.getZt());
                ps.setObject(j++, e.getDrpjz());
                ps.setObject(j++, e.getDypjz());
                ps.setObject(j++, e.getDnpjz());
            }
        });
    }

    // 批量插入
    public void insertData(List<YjDlfhBaseData> list) {
        String sql = "INSERT INTO W_YJDLFHBASEDATA (ID,F_RQ,F_ZDM,F_DRZ,F_DYZ,F_DNZ,F_DRZD,F_DYZD,F_DNZD,F_DRZX,F_DYZX,F_DNZX,"
                + "F_QNJRZ,F_QNJYZ,F_QNJNZ,F_ZT,F_DRPJZ,F_DYPJZ,F_DNPJZ,F_DRZDSK,F_DYZDSK,F_DNZDSK,F_DRZXSK,F_DYZXSK,F_DNZXSK)" 
                + " values (?,to_date(?,'yyyy-MM-dd'),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,to_date(?,'yyyy-MM-dd HH24:mi:ss'),to_date(?,'yyyy-MM-dd HH24:mi:ss'),to_date(?,'yyyy-MM-dd HH24:mi:ss'),to_date(?,'yyyy-MM-dd HH24:mi:ss'),to_date(?,'yyyy-MM-dd HH24:mi:ss'),to_date(?,'yyyy-MM-dd HH24:mi:ss'))";

        final List<YjDlfhBaseData> list2 = list;

        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return list2.size();
            }

            public void setValues(PreparedStatement ps, int index) throws SQLException {
                int j = 1;
                YjDlfhBaseData e = list2.get(index);
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                SimpleDateFormat sdfTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

                ps.setObject(j++, e.getId());
                ps.setObject(j++, e.getRq()==null?null:sdf.format(e.getRq()));
                ps.setObject(j++, e.getZdm());
                ps.setObject(j++, e.getDrz());
                ps.setObject(j++, e.getDyz());
                ps.setObject(j++, e.getDnz());
                ps.setObject(j++, e.getDrzd());
                ps.setObject(j++, e.getDyzd());
                ps.setObject(j++, e.getDnzd());
                ps.setObject(j++, e.getDrzx());
                ps.setObject(j++, e.getDyzx());
                ps.setObject(j++, e.getDnzx());
                ps.setObject(j++, e.getQnjrz());
                ps.setObject(j++, e.getQnjyz());
                ps.setObject(j++, e.getQnjnz());
                ps.setObject(j++, e.getZt());
                ps.setObject(j++, e.getDrpjz());
                ps.setObject(j++, e.getDypjz());
                ps.setObject(j++, e.getDnpjz());
                ps.setObject(j++, e.getDrzdsk() == null ? null : sdfTime.format(e.getDrzdsk()));
                ps.setObject(j++, e.getDyzdsk() == null ? null : sdfTime.format(e.getDyzdsk()));
                ps.setObject(j++, e.getDnzdsk() == null ? null : sdfTime.format(e.getDnzdsk()));
                ps.setObject(j++, e.getDrzxsk() == null ? null : sdfTime.format(e.getDrzxsk()));
                ps.setObject(j++, e.getDyzxsk() == null ? null : sdfTime.format(e.getDyzxsk()));
                ps.setObject(j++, e.getDnzxsk() == null ? null : sdfTime.format(e.getDnzxsk()));
            }
        });
    }

    // 批量删除
        public void deleteData(List<YjDlfhBaseData> list) {
            String sql = "delete from W_YJDLFHBASEDATA where F_RQ=to_date(?,'yyyy-MM-dd')  and F_ZDM=?";

            final List<YjDlfhBaseData> list2 = list;

            jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
                public int getBatchSize() {
                    return list2.size();
                }

                public void setValues(PreparedStatement ps, int index) throws SQLException {
                    int j = 1;
                    YjDlfhBaseData e = list2.get(index);
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    ps.setObject(j++, e.getRq()==null?null:sdf.format(e.getRq()));
                    ps.setObject(j++, e.getZdm());
                }
            });
        }