JdbcTemplate query

来源：互联网发布：服务器与域名的关系编辑：程序博客网时间：2024/06/09 03:08
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();        }    }}
0 0