JdbcTemplate和NamedParameterJdbcTemplate的使用

1. Spring NamedParameterJdbcTemplate 详解 解决jdbcTemplate中 in 参数的问题

1.1 封装一个分页的工具类JdbcTemplatePage

PageVo

 1 package utils.sql;
 2 
 3 import java.io.Serializable;
 4 import java.util.List;
 5 
 6 /**
 7  * Created by oracle on 2016/10/26.
 8  */
 9 public class PageVo<T> implements Serializable {
10     //分页查询结果
11     List<T> Result;
12     //页总数
13     Integer pageNum;
14     //一页记录数
15     Integer pageSize;
16     //总记录数
17     Integer totalCount;
18 
19 
20     public PageVo() {
21 
22     }
23 
24     public PageVo(Integer pageNum, Integer pageSize) {
25         this.pageNum = pageNum;
26         this.pageSize = pageSize;
27     }
28 
29     public Integer getTotalCount() {
30         return totalCount;
31     }
32 
33     public void setTotalCount(Integer totalCount) {
34         this.totalCount = totalCount;
35     }
36 
37     public List<T> getResult() {
38         return Result;
39     }
40 
41     public void setResult(List<T> result) {
42         Result = result;
43     }
44 
45     public Integer getPageNum() {
46         return pageNum;
47     }
48 
49     public void setPageNum(Integer pageNum) {
50         this.pageNum = pageNum;
51     }
52 
53     public Integer getPageSize() {
54         return pageSize;
55     }
56 
57     public void setPageSize(Integer pageSize) {
58         this.pageSize = pageSize;
59     }
60 }
View Code

JdbcTemplatePage

 1 package utils.sql;
 2 
 3 import org.springframework.beans.factory.annotation.Autowired;
 4 import org.springframework.jdbc.core.BeanPropertyRowMapper;
 5 import org.springframework.jdbc.core.JdbcTemplate;
 6 import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
 7 import org.springframework.stereotype.Component;
 8 import utils.log.Log;
 9 import utils.log.Logs;
10 import java.util.List;
11 import java.util.Map;
12 
13 /**
14  * parent
15  *
16  * @date 2018/7/25
17  * 分页查询
18  */
19 @Component
20 public class JdbcTemplatePage {
21     private static final Log log = Logs.getLog(JdbcTemplatePage.class.getName());
22 
23     @Autowired
24     JdbcTemplate jdbcTemplate;
25 
26     /**
27      * @param sql
28      * @param elementType 返回需要封装的pageVo
29      * @param page
30      * @param pageSize
31      * @param args
32      * @return PageVo
33      */
34     public <T> PageVo<T> query(String sql, Class<T> elementType, Integer page, Integer pageSize, Map<String,Object> args) throws Exception {
35         NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate.getDataSource());
36         sql = sql.toLowerCase();
37         log.info("query sql:"+sql);
38         SqlReCreateFactory sqlReCreateFactory = new SqlReCreateFactory<Object>(sql, page, pageSize, args).invoke();
39         sql = sqlReCreateFactory.getSql();
40         PageVo<T> pageVo = sqlReCreateFactory.getPageVo();
41         List<T> result = namedParameterJdbcTemplate.query(sql, args, new BeanPropertyRowMapper<>(elementType));
42         pageVo.setResult(result);
43         return pageVo;
44     }
45 
46     private class SqlReCreateFactory<T> {   //
47         private String sql;
48         private Integer page;
49         private Integer pageSize;
50         private Map<String,Object> args;
51         private PageVo<T> pageVo;
52 
53         public SqlReCreateFactory(String sql, Integer page, Integer pageSize, Map<String, Object> args) {
54             this.sql = sql;
55             this.page = page;
56             this.pageSize = pageSize;
57             this.args = args;
58         }
59 
60         public String getSql() {
61             return sql;
62         }
63 
64         public PageVo<T> getPageVo() {
65             return pageVo;
66         }
67 
68         public SqlReCreateFactory invoke() {
69             NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate.getDataSource());
70             pageVo = new PageVo<>(0, pageSize);
71             String sqlCount = "select count(*) from (" + sql + ")  ";
72             Integer count = namedParameterJdbcTemplate.queryForObject(sqlCount, args, Integer.class); //查询页数
73             pageVo.setTotalCount(count);
74             int pageNum = 0;
75             if (count != null && count > 0) {
76                 if (count % pageSize == 0) {
77                     pageNum = count / pageSize;
78                 } else {
79                     pageNum = count / pageSize + 1;
80                 }
81                 pageVo.setPageNum(pageNum);
82             }
83             int pageMin = (page - 1) * pageSize;
84             int pageMax = pageMin + pageSize;
85             args.put("pagemin",pageMin);
86             args.put("pagemax",pageMax);
87             sql = "select p.* from (select t.* ,rownum rownumber from  (" +sql+ ") t) p where p.rownumber>:pagemin and p.rownumber<=:pagemax ";
88             return this;
89         }
90     }
91 }
View Code

1.2 使用JdbcTemplatePage进行分页

 1 package finance.service.service;
 2 
 3 import finance.api.dto.FinanceToAccountDto;
 4 import finance.api.vo.FundReconciliationQueryVo;
 5 import lombok.extern.slf4j.Slf4j;
 6 import org.apache.commons.collections.map.HashedMap;
 7 import org.springframework.beans.factory.annotation.Autowired;
 8 import org.springframework.stereotype.Component;
 9 import utils.Lang;
10 import utils.sql.JdbcTemplatePage;
11 import utils.sql.PageVo;
12 
13 import java.util.ArrayList;
14 import java.util.HashMap;
15 import java.util.List;
16 import java.util.Map;
17 
18 /**
19  * @date 2018/5/7
20  */
21 @Component
22 @Slf4j
23 public class FundReconciliationService {
24 
25     @Autowired
26     private JdbcTemplatePage jdbcTemplatePage;
27 
28     /**
29      * 查询
30      *
31      * @param fundReconciliationQueryVo
32      * @param size
33      * @param number
34      * @return
35      */
36     public PageVo<FinanceToAccountDto> findfinancialInfo(FundReconciliationQueryVo fundReconciliationQueryVo, int size, int number) {
37         PageVo<FinanceToAccountDto> pageVo = null;
38         Map<String, Object> sqlListMap = financialInfoSql(fundReconciliationQueryVo);
39         try {
40             pageVo = jdbcTemplatePage.query(String.valueOf(sqlListMap.get("sql")), FinanceToAccountDto.class, number, size, (Map<String, Object>) sqlListMap.get("params"));
41         } catch (Exception e) {
42             e.printStackTrace();
43         }
44         return pageVo;
45     }
46 
47 
48     /**
49      * sql
50      *
51      * @param fundReconciliationQueryVo
52      * @return
53      */
54     public Map<String, Object> financialInfoSql(FundReconciliationQueryVo fundReconciliationQueryVo) {
55         StringBuffer sql = new StringBuffer();
56         sql.append(" select rownum as indexNum,c.capital_number as capitalNumber, ");
57         sql.append(" c.customer_reference_name as customerReferenceName, ");
58         sql.append(" c.source_note_code as sourceNoteCode, ");
59         sql.append(" c.amount as amount, ");
60         sql.append(" c.post_date_time as postDateTime, ");
61         sql.append(" c.direction as direction, ");
62         sql.append(" fba.account_status as accountStatus ");
63         sql.append(" from CAPITAL c, fn_bills_account fba ");
64         sql.append(" where c.capital_number = fba.capital_number(+) ");
65 
66         Map<String, Object> params = new HashMap<String, Object>();
67         List<Object> list = new ArrayList<Object>();
68         String[] accountStatusArry = fundReconciliationQueryVo.getAccountStatus().split(",");
69         for (String accountStatus : accountStatusArry) {
70             list.add(accountStatus);
71         }
72         if (!Lang.isEmpty(fundReconciliationQueryVo.getAccountStatus())) {
73             if (fundReconciliationQueryVo.getAccountStatus().indexOf("0") >= 0) {
74                 sql.append(" and (fba.account_status is null or fba.account_status in(:accountstatus)) ");
75             } else {
76                 sql.append(" and fba.account_status in(:accountstatus) ");
77             }
78             params.put("accountstatus", list);
79         }
80         if (!Lang.isEmpty(fundReconciliationQueryVo.getPostDateStartTime())) {
81             sql.append(" and c.post_date_time >= to_date(:postdatestarttime, 'yyyy-mm-dd hh24:mi:ss') ");
82             params.put("postdatestarttime", fundReconciliationQueryVo.getPostDateStartTime());
83         }
84         if (!Lang.isEmpty(fundReconciliationQueryVo.getPostDateEndTime())) {
85             sql.append(" and c.post_date_time <= to_date(:postdateendtime, 'yyyy-mm-dd hh24:mi:ss') ");
86             params.put("postdateendtime", fundReconciliationQueryVo.getPostDateEndTime());
87         }
88         if (!Lang.isEmpty(fundReconciliationQueryVo.getSourceNoteCode())) {
89             sql.append(" and c.source_note_code = :sourcenotecode ");
90             params.put("sourcenotecode", fundReconciliationQueryVo.getSourceNoteCode());
91         }
92         log.info("财务到账信息列表查询sql===》：{}", sql.toString());
93         Map<String, Object> map = new HashedMap();
94         map.put("sql", sql.toString());
95         map.put("params", params);
96         return map;
97     }
98 }
View Code

 2. jdbcTemplate没有使用in 参数分页查询

2.1 封装一个分页的工具类QueryByPage

QueryByPage

  1 package utils.sql;
  2 
  3 
  4 import org.springframework.beans.factory.annotation.Autowired;
  5 import org.springframework.jdbc.core.BeanPropertyRowMapper;
  6 import org.springframework.jdbc.core.JdbcTemplate;
  7 import org.springframework.stereotype.Component;
  8 import utils.log.Log;
  9 import utils.log.Logs;
 10 
 11 import java.util.ArrayList;
 12 import java.util.List;
 13 import java.util.Map;
 14 
 15 /**
 16  * Created by oracle on 2016/10/26.
 17  */
 18 @Component
 19 public class QueryByPage {  //分页查询
 20     private static final Log log = Logs.getLog(QueryByPage.class.getName());
 21     @Autowired
 22     JdbcTemplate jdbcTemplate;
 23 
 24     /**
 25      * @param sql
 26      * @param elementType 返回需要封装的javaVo
 27      * @param page
 28      * @param pageSize
 29      * @param args        sql参数
 30      * @return PageVo
 31      */
 32     public <T> PageVo<T> query(String sql, Class<T> elementType, Integer page, Integer pageSize, Object... args) throws Exception {
 33         sql = sql.toLowerCase();
 34         log.info("query sql:"+sql);
 35         SqlReCreateFactory sqlReCreateFactory = new SqlReCreateFactory<Object>(sql, page, pageSize, args).invoke();
 36         sql = sqlReCreateFactory.getSql();
 37         List<Object> argsList = sqlReCreateFactory.getArgsList();
 38         PageVo<T> pageVo = sqlReCreateFactory.getPageVo();
 39         List<T> result = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(elementType), argsList.toArray());
 40         pageVo.setResult(result);
 41         return pageVo;
 42     }
 43 
 44     /**
 45      * @param sql      默认封装成List<Map<String,Object>>
 46      * @param page
 47      * @param pageSize
 48      * @param args     sql参数
 49      * @return PageVo
 50      */
 51     public PageVo query(String sql, Integer page, Integer pageSize, Object... args) throws Exception {
 52         sql = sql.toLowerCase();
 53         log.info("query sql:"+sql);
 54         SqlReCreateFactory sqlReCreateFactory = new SqlReCreateFactory<Object>(sql, page, pageSize, args).invoke();
 55         sql = sqlReCreateFactory.getSql();
 56         List<Object> argsList = sqlReCreateFactory.getArgsList();
 57         PageVo<Map<String, Object>> pageVo = sqlReCreateFactory.getPageVo();
 58         List<Map<String, Object>> result = jdbcTemplate.queryForList(sql, argsList.toArray());
 59         pageVo.setResult(result);
 60         return pageVo;
 61     }
 62 
 63     public PageVo querySqlCaseNoConvert(String sql, Integer page, Integer pageSize, Object... args) throws Exception {
 64         log.info("SQL IS {}", sql);
 65         SqlReCreateFactory sqlReCreateFactory = new SqlReCreateFactory<>(sql, page, pageSize, args).invoke();
 66         sql = sqlReCreateFactory.getSql();
 67         List<Object> argsList = sqlReCreateFactory.getArgsList();
 68         PageVo<Map<String, Object>> pageVo = sqlReCreateFactory.getPageVo();
 69         List<Map<String, Object>> result = jdbcTemplate.queryForList(sql, argsList.toArray());
 70         pageVo.setResult(result);
 71         return pageVo;
 72     }
 73 
 74     private class SqlReCreateFactory<T> {   //
 75         private String sql;
 76         private Integer page;
 77         private Integer pageSize;
 78         private Object[] args;
 79         private PageVo<T> pageVo;
 80         private List<Object> argsList;
 81 
 82         public SqlReCreateFactory(String sql, Integer page, Integer pageSize, Object... args) {
 83             this.sql = sql;
 84             this.page = page;
 85             this.pageSize = pageSize;
 86             this.args = args;
 87         }
 88 
 89         public String getSql() {
 90             return sql;
 91         }
 92 
 93         public PageVo<T> getPageVo() {
 94             return pageVo;
 95         }
 96 
 97         public List<Object> getArgsList() {
 98             return argsList;
 99         }
100 
101         public SqlReCreateFactory invoke() {
102             pageVo = new PageVo<>(0, pageSize);
103             String sqlCount = "select count(*) from (" + sql + ")  ";
104             Integer count = jdbcTemplate.queryForObject(sqlCount, args, Integer.class);   //查询页数
105             pageVo.setTotalCount(count);
106             int pageNum = 0;
107             if (count != null && count > 0) {
108                 if (count % pageSize == 0) {
109                     pageNum = count / pageSize;
110                 } else {
111                     pageNum = count / pageSize + 1;
112                 }
113                 pageVo.setPageNum(pageNum);
114             }
115     /*        String sqlPrefix = sql.substring(0, sql.indexOf("from"));
116             String sqlSuffix = sql.substring(sql.indexOf("from"));*/
117             int pageMin = (page - 1) * pageSize;
118             int pageMax = pageMin + pageSize;
119             argsList = new ArrayList<>();
120             for (Object arg : args) {
121                 argsList.add(arg);
122             }
123             argsList.add(pageMin);
124             argsList.add(pageMax);
125             sql = "select p.* from (select t.* ,rownum rownumber from  (" +sql+ ") t) p where p.rownumber>? and p.rownumber<=? ";
126             return this;
127         }
128     }
129 }
View Code

2.1 使用QueryByPage进行分页

 1 package finance.service.service;
 2 
 3 import finance.api.dto.DetailListDto;
 4 import lombok.extern.slf4j.Slf4j;
 5 import org.apache.commons.collections.map.HashedMap;
 6 import org.springframework.beans.factory.annotation.Autowired;
 7 import org.springframework.stereotype.Component;
 8 import utils.Lang;
 9 import utils.sql.PageVo;
10 import utils.sql.QueryByPage;
11 
12 import java.util.ArrayList;
13 import java.util.List;
14 import java.util.Map;
15 
16 /**
17  * @date 2018/5/7
18  */
19 @Component
20 @Slf4j
21 public class FundReconciliationService {
22 
23     @Autowired
24     private QueryByPage queryByPage;
25 
26     /**
27      * 列表查询
28      *
29      * @param pageSize
30      * @param pageNumber
31      * @param billsNo
32      * @return
33      */
34     public PageVo<DetailListDto> detailList(int pageSize, int pageNumber, String billsNo) {
35         PageVo<DetailListDto> detailListDtoPageVo = null;
36         Map<String, Object> sqlList = detailListSql(billsNo);
37         try {
38             detailListDtoPageVo = queryByPage.query(String.valueOf(sqlList.get("sql")), DetailListDto.class, pageNumber, pageSize, ((List) sqlList.get("list")).toArray());
39         } catch (Exception e) {
40             e.printStackTrace();
41         }
42         return detailListDtoPageVo;
43     }
44 
45     /**
46      * 列表查询Sql
47      *
48      * @param billsNo
49      * @return
50      */
51     public Map<String, Object> detailListSql(String billsNo) {
52         StringBuffer sql = new StringBuffer();
53         sql.append(" select rownum as indexNum,detail.* from  (\n");
54         sql.append(" select ro.order_no as orderNo,\n");
55         sql.append(" ru.user_name as userName,\n");
56         sql.append(" to_char(l.receive_time, 'yyyy-mm-dd HH24:mi:ss') as receiveTime,\n");
57         sql.append(" ro.sum_price as sumPrice,\n");
58         sql.append(" pack.name as name\n");
59         sql.append(" from UG_GIFT_PACK_RECEIVE_LOG l,\n");
60         sql.append(" UG_GIFT_PACK_RECEIVE_ORDER ro,\n");
61         sql.append(" ug_gift_pack pack,\n");
62         sql.append(" ug_gift_receive_user ru,\n");
63         sql.append(" UG_GIFT_ACTIVITY uga,\n");
64         sql.append(" order_main om,\n");
65         sql.append(" fn_bills fb,\n");
66         sql.append(" fn_bills_order fbo\n");
67         sql.append(" where ro.gift_pack_receive_log_id = l.id\n");
68         sql.append(" and pack.id = l.gift_pack_id\n");
69         sql.append(" and uga.id = pack.activity_id\n");
70         sql.append(" and ru.id = l.gift_receive_user_id\n");
71         sql.append(" and om.id = ro.order_id\n");
72         sql.append(" and fb.id = fbo.bills_id\n");
73         sql.append(" and fbo.order_no = om.order_no\n");
74 
75         List<Object> list = new ArrayList<Object>();
76         if (!Lang.isEmpty(billsNo)) {
77             sql.append(" and fb.bills_no = ? ");
78             list.add(billsNo);
79         }
80 
81         sql.append(" order by l.receive_time desc) detail ");
82         log.info("明细清单列表查询Sql===》：{}", sql.toString());
83         Map<String, Object> map = new HashedMap();
84         map.put("sql", sql.toString());
85         map.put("list", list);
86         return map;
87     }
88 }
View Code

3 . jdbcTemplate没有使用in 参数不分页查询

 1 package finance.service.service;
 2 
 3 import finance.api.dto.FundReconciliationDto;
 4 import finance.api.vo.FundReconciliationQueryVo;
 5 import lombok.extern.slf4j.Slf4j;
 6 import org.apache.commons.collections.map.HashedMap;
 7 import org.springframework.beans.factory.annotation.Autowired;
 8 import org.springframework.jdbc.core.BeanPropertyRowMapper;
 9 import org.springframework.jdbc.core.JdbcTemplate;
10 import org.springframework.stereotype.Component;
11 import utils.Lang;
12 
13 import java.util.ArrayList;
14 import java.util.List;
15 import java.util.Map;
16 
17 /**
18  * @date 2018/5/7
19  */
20 @Component
21 @Slf4j
22 public class FundReconciliationService {
23 
24     @Autowired
25     private JdbcTemplate jdbcTemplate;
26 
27 
28     /**
29      * @param fundReconciliationQueryVo
30      * @return
31      */
32     public List<FundReconciliationDto> downloadFundReconciliation(FundReconciliationQueryVo fundReconciliationQueryVo) {
33         Map<String, Object> sqlListMap = fundReconciliationSql(fundReconciliationQueryVo);
34         List<FundReconciliationDto> fundReconciliationDtos = null;
35         try {
36             fundReconciliationDtos = jdbcTemplate.query(String.valueOf(sqlListMap.get("sql")), new BeanPropertyRowMapper<FundReconciliationDto>(FundReconciliationDto.class), ((List) sqlListMap.get("list")).toArray());
37         } catch (Exception e) {
38             e.printStackTrace();
39         }
40         return fundReconciliationDtos;
41     }
42 
43     /**
44      * Sql
45      *
46      * @param fundReconciliationQueryVo
47      * @return
48      */
49     public Map<String, Object> fundReconciliationSql(FundReconciliationQueryVo fundReconciliationQueryVo) {
50         StringBuffer sql = new StringBuffer();
51         sql.append(" select rownum  as indexNum,\n");
52         sql.append(" fba.bills_no  as billsNo,\n");
53         sql.append(" fba.bills_price as billsPrice ,\n");
54         sql.append(" fba.capital_number as capitalNumber,\n");
55         sql.append(" c.post_date_time as postDateTime,\n");
56         sql.append(" c.customer_reference as customerReference,\n");
57         sql.append(" c.post_date as postDate\n");
58         sql.append(" from CAPITAL c, fn_bills_account fba\n");
59         sql.append(" where c.capital_number = fba.capital_number(+) ");
60         sql.append(" and fba.account_status ='1'\n");
61 
62         List<Object> list = new ArrayList<Object>();
63         if (!Lang.isEmpty(fundReconciliationQueryVo.getBillsNo())) {
64             sql.append(" and fba.bills_no = ? ");
65             list.add(fundReconciliationQueryVo.getBillsNo());
66         }
67         if (!Lang.isEmpty(fundReconciliationQueryVo.getPostDateStartTime())) {
68             sql.append(" and TO_CHAR(c.post_date_time,'YYYY-MM-DD HH24:mi:ss')>=? ");
69             list.add(fundReconciliationQueryVo.getPostDateStartTime());
70         }
71         if (!Lang.isEmpty(fundReconciliationQueryVo.getPostDateEndTime())) {
72             sql.append(" and TO_CHAR(c.post_date_time,'YYYY-MM-DD HH24:mi:ss') <=? ");
73             list.add(fundReconciliationQueryVo.getPostDateEndTime());
74         }
75 
76         log.info("Sql===》：{}", sql.toString());
77         Map<String, Object> map = new HashedMap();
78         map.put("sql", sql.toString());
79         map.put("list", list);
80         return map;
81     }
82 }
View Code

 

posted @ 2018-07-26 12:48  坚持就是胜利66  阅读(2282)  评论(0)  编辑  收藏  举报