JdbcTemplate query

import com.netposa.entities.Camera;
import com.netposa.pvm.sync.framework.OperateType;
import jodd.bean.BeanUtil;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowCallbackHandler;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * Created by Administrator on 2016/12/7.
 */
public class JdbCTemp {
    @Autowired
    JdbcTemplate jdbcTemplate;
    /*
   不带参数每行处理，在processRow中一行行处理
    */
    public void query1(){

        final Map<UUID,Camera> map=new HashMap<>() ;
        jdbcTemplate.query("select id,name,orgId from camera ", new RowCallbackHandler() {
            Camera camera=new Camera();
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                UUID id = UUID.fromString(rs.getString("id"));
                camera.setId(id);
                camera.setName(rs.getString("name"));
                camera.setOrgId(UUID.fromString(rs.getString("orgId")));
                map.put(id,camera);
            }
        });
    }
    /*
    带参数每行处理，在processRow中一行行处理
     */
    public void query2(String id){
        final Camera camera=new Camera();
        jdbcTemplate.query("select id,name,orgId from camera where id=?", new RowCallbackHandler() {

            @Override
            public void processRow(ResultSet rs) throws SQLException {
                UUID id = UUID.fromString(rs.getString("id"));
                camera.setId(id);
                camera.setName(rs.getString("name"));
                camera.setOrgId(UUID.fromString(rs.getString("orgId")));

            }
        },id);
    }
    

    /**
     * 不带参数，在extractData中处理整个ResultSet
     * @return
     */
    public List<Camera> query3(){
        return jdbcTemplate.query("select id,name,orgId from camera", new ResultSetExtractor<List<Camera>>() {
            List<Camera> lists=new ArrayList<Camera>();
            @Override
            public List<Camera> extractData(ResultSet rs) throws SQLException, DataAccessException {
                while(rs.next()){
                    Camera camera=new Camera();
                    UUID id = UUID.fromString(rs.getString("id"));
                    camera.setId(id);
                    camera.setName(rs.getString("name"));
                    camera.setOrgId(UUID.fromString(rs.getString("orgId")));
                    lists.add(camera);
                }
                return lists;
            }
        });
    }

    

    /**
     * 带参数，在extractData中处理整个ResultSet
     * @param serviceType
     * @return
     */
    public List<Camera> query3(String serviceType){
        return jdbcTemplate.query("select id,name,orgId from camera where service_type=?", new ResultSetExtractor<List<Camera>>() {
            List<Camera> lists=new ArrayList<Camera>();
            @Override
            public List<Camera> extractData(ResultSet rs) throws SQLException, DataAccessException {
                while(rs.next()){
                    Camera camera=new Camera();
                    UUID id = UUID.fromString(rs.getString("id"));
                    camera.setId(id);
                    camera.setName(rs.getString("name"));
                    camera.setOrgId(UUID.fromString(rs.getString("orgId")));
                    lists.add(camera);
                }
                return lists;
            }
        },serviceType);
    }

    
 

    /**
     * 批量插入
     * @param cameras
     * @param insertKeys  (id,name,....)
     * @param preparedKeys (?,?,?...)
     * @param keys
     */
    public void batch(List<Camera> cameras,String insertKeys,String preparedKeys,Set<String> keys){
        jdbcTemplate.batchUpdate("insert into camera "+insertKeys+" VALUE " +preparedKeys,new MyBatchPreparedStatementSetter1(cameras,keys));
    }
    public static class MyBatchPreparedStatementSetter1 implements  BatchPreparedStatementSetter {
        private final List<Camera> cameras;
        private final Set<String> keys;

        public MyBatchPreparedStatementSetter1(List<Camera> cameras, Set<String> keys) {
            this.cameras = cameras;
            this.keys = keys;
        }

        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            int idx=1;
            Camera camera=cameras.get(i);

            for(String key:keys){
                ps.setString(idx,  BeanUtil.declared.getProperty(camera,key).toString());
                idx++;
            }
        }

        @Override
        public int getBatchSize() {
            return cameras.size();
        }
    }
    
    public static class MyBatchPreparedStatementSetter implements BatchPreparedStatementSetter {
        private final List<Map<String, String>> cache;
        private final Set<String> allKeys;
        private final Map<String, String> defaultValueMap;
        private final int op;

        public MyBatchPreparedStatementSetter(List<Map<String, String>> cache, Set<String> allKeys, Map<String, String> defaultValueMap,int op) {
            this.cache = cache;
            this.allKeys = allKeys;
            this.defaultValueMap = defaultValueMap;
            this.op=op;
        }

        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            Map<String, String> map = cache.get(i);
            int idx = 1;
            for (String key : allKeys) {
                if (map.containsKey(key)) {
                    ps.setString(idx, map.get(key));
                } else if (defaultValueMap.containsKey(key)) {
                    ps.setString(idx, defaultValueMap.get(key));
                } else {
                    ps.setString(idx, null);
                }
                ++idx;
            }
            if(op== OperateType.UPDATE.value){
                ps.setString(idx, map.get("id"));
            }
        }

        @Override
        public int getBatchSize() {
            return cache.size();
        }
    }
}