NamedParameterJdbcTemplate —— SqlParameterSource实现具名参数查询

小咪咪 2024-03-31 18:04 51阅读 0赞

#### 文章目录 ####

*  NamedParameterJdbcTemplate —— SqlParameterSource实现具名参数查询
 *   *  一、数据库准备
     *  二、java Bean实体类准备
     *  三、要执行的SQL语句
     *  四、定义业务接口service
     *  五、定义一个SqlParameterSource实现具名参数的实体类
     *  六、定义controller接口
     *  七、业务实现
     *   *  （1）使用MapSqlParameterSource
         *  （2）使用BeanPropertySqlParameterSource

## NamedParameterJdbcTemplate —— SqlParameterSource实现具名参数查询 ##

如果对使用不了解可以参考如下链接：

[NamedParameterJdbcTemplate使用详解][NamedParameterJdbcTemplate]

注意：使用`queryForList()与queryForObject()`这两个方法一般情况下只返回`单一列`的数据，不能返回复杂的数据对象。本文只介绍通过`SqlParameterSource`具名参数返回复杂数据对象。

### 一、数据库准备 ###

/*
     Navicat Premium Data Transfer
    
     Source Server         : my_project
     Source Server Type    : MySQL
     Source Server Version : 80027
     Source Host           : localhost:3306
     Source Schema         : order
    
     Target Server Type    : MySQL
     Target Server Version : 80027
     File Encoding         : 65001
    
     Date: 12/12/2022 23:00:57
    */
    
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for ordertb
    -- ----------------------------
    DROP TABLE IF EXISTS `ordertb`;
    CREATE TABLE `ordertb`  (
      `orderID` int NOT NULL AUTO_INCREMENT COMMENT '订单号',
      `webName` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '订购商平台',
      `productName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '商品名称',
      `productType` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '商品类别',
      `orderCount` int NULL DEFAULT NULL COMMENT '订单数量',
      `orderDate` datetime NULL DEFAULT NULL COMMENT '订单日期',
      `agent` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '代理商',
      PRIMARY KEY (`orderID`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of ordertb
    -- ----------------------------
    INSERT INTO `ordertb` VALUES (1, '淘宝', '高露洁', '牙膏', 2000, '2015-03-14 09:02:28', '王丽');
    INSERT INTO `ordertb` VALUES (2, '淘宝', '佳洁士', '牙膏', 700, '2015-03-14 09:02:28', '张涛');
    INSERT INTO `ordertb` VALUES (3, '淘宝', '佳洁士', '牙膏', 700, '2015-03-14 09:02:28', '张涛');
    INSERT INTO `ordertb` VALUES (4, '京东', 'IPAD Mini', '电子产品', 670, '2015-03-14 09:02:28', '赵晓菲');
    INSERT INTO `ordertb` VALUES (5, '淘宝', '高露洁', '牙膏', 532, '2015-03-16 12:32:18', '张涛');
    INSERT INTO `ordertb` VALUES (6, '一号店', '佳洁士', '牙膏', 988, '2015-03-16 12:32:18', '张丽');
    INSERT INTO `ordertb` VALUES (7, '京东', 'iphone 6s', '电子产品', 2180, '2015-03-16 12:32:18', '张涛');
    INSERT INTO `ordertb` VALUES (8, '京东', '佳洁士', '牙膏', 700, '2015-03-16 12:32:18', '朱晓宇');
    INSERT INTO `ordertb` VALUES (9, '淘宝', '黑妹', '牙膏', 855, '2015-03-16 12:32:18', '张涛');
    INSERT INTO `ordertb` VALUES (10, '淘宝', '佳洁士', '牙膏', 745, '2015-03-16 12:32:18', '赵晓菲');
    INSERT INTO `ordertb` VALUES (11, '京东', '力士', '牙膏', 923, '2015-03-17 12:32:18', '赵晓菲');
    INSERT INTO `ordertb` VALUES (12, '淘宝', '舒肤佳', '香皂', 866, '2015-03-17 12:32:18', '朱晓宇');
    INSERT INTO `ordertb` VALUES (13, '淘宝', 'iphone 6s', '电子产品', 998, '2015-03-21 12:32:18', '赵晓菲');
    INSERT INTO `ordertb` VALUES (14, '京东', '佳洁士', '牙膏', 2110, '2015-03-21 12:32:18', '张涛');
    INSERT INTO `ordertb` VALUES (15, '一号店', '佳洁士', '牙膏', 1000, '2015-03-21 12:32:18', '张涛');
    INSERT INTO `ordertb` VALUES (16, '京东', '佳洁士', '牙膏', 809, '2015-03-21 12:32:18', '朱晓宇');
    INSERT INTO `ordertb` VALUES (17, '淘宝', '舒肤佳', '香皂', 319, '2015-03-24 15:31:48', '朱晓宇');
    INSERT INTO `ordertb` VALUES (18, '淘宝', '佳洁士', '牙膏', 3290, '2015-03-24 15:31:48', '张涛');
    INSERT INTO `ordertb` VALUES (19, '京东', '华为888手机', '电子产品', 500, '2015-03-24 15:31:48', '赵晓菲');
    INSERT INTO `ordertb` VALUES (20, '京东', '力士', '香皂', 2188, '2015-03-24 15:31:48', '张涛');
    INSERT INTO `ordertb` VALUES (21, '一号店', '佳洁士', '牙膏', 2000, '2015-03-24 15:31:48', '张丽');
    INSERT INTO `ordertb` VALUES (22, '淘宝', '佳洁士', '牙膏', 1000, '2015-03-24 15:31:48', '张涛');
    INSERT INTO `ordertb` VALUES (23, '京东', 'IPAD Mini', '电子产品', 1000, '2015-03-24 15:31:48', '赵晓菲');
    INSERT INTO `ordertb` VALUES (24, '淘宝', '高露洁', '牙膏', 1000, '2015-03-24 15:31:48', '张涛');
    INSERT INTO `ordertb` VALUES (25, '一号店', '佳洁士', '牙膏', 1000, '2015-03-24 15:31:48', '张丽');
    INSERT INTO `ordertb` VALUES (26, '京东', 'iphone 6s', '电子产品', 1000, '2015-03-24 15:31:48', '张涛');
    INSERT INTO `ordertb` VALUES (27, '京东', '佳洁士', '牙膏', 1000, '2015-03-24 15:31:48', '朱晓宇');
    INSERT INTO `ordertb` VALUES (28, '淘宝', '黑妹', '牙膏', 1000, '2015-03-24 15:31:48', '张涛');
    INSERT INTO `ordertb` VALUES (29, '淘宝', '佳洁士', '牙膏', 1000, '2015-04-02 15:31:48', '赵晓菲');
    INSERT INTO `ordertb` VALUES (30, '京东', '力士', '牙膏', 1000, '2015-04-02 15:31:48', '赵晓菲');
    INSERT INTO `ordertb` VALUES (31, '淘宝', '舒肤佳', '香皂', 1000, '2015-04-02 15:31:48', '朱晓宇');
    INSERT INTO `ordertb` VALUES (32, '淘宝', 'iphone 6s', '电子产品', 1000, '2015-04-02 15:31:48', '赵晓菲');
    INSERT INTO `ordertb` VALUES (33, '京东', '佳洁士', '牙膏', 1000, '2015-04-02 15:31:48', '张涛');
    INSERT INTO `ordertb` VALUES (34, '一号店', '佳洁士', '牙膏', 1000, '2015-04-02 15:31:48', '张涛');
    INSERT INTO `ordertb` VALUES (35, '京东', '佳洁士', '牙膏', 1000, '2015-04-02 15:31:48', '朱晓宇');
    INSERT INTO `ordertb` VALUES (36, '淘宝', '舒肤佳', '香皂', 1000, '2015-04-02 15:31:48', '朱晓宇');
    INSERT INTO `ordertb` VALUES (37, '淘宝', '佳洁士', '牙膏', 1000, '2015-04-02 15:31:48', '张涛');
    INSERT INTO `ordertb` VALUES (38, '京东', '华为888手机', '电子产品', 1000, '2015-04-02 15:31:48', '赵晓菲');
    INSERT INTO `ordertb` VALUES (39, '京东', '力士', '香皂', 1000, '2015-04-02 15:31:48', '张涛');
    INSERT INTO `ordertb` VALUES (40, '一号店', '佳洁士', '牙膏', 1000, '2015-04-02 15:31:48', '张丽');
    INSERT INTO `ordertb` VALUES (41, '淘宝', '佳洁士', '牙膏', 1000, '2015-04-06 15:31:48', '张涛');
    INSERT INTO `ordertb` VALUES (42, '京东', 'IPAD Mini', '电子产品', 1000, '2015-04-06 15:31:48', '赵晓菲');
    INSERT INTO `ordertb` VALUES (43, '淘宝', '高露洁', '牙膏', 1000, '2015-04-06 15:31:48', '张涛');
    INSERT INTO `ordertb` VALUES (44, '一号店', '佳洁士', '牙膏', 1000, '2015-04-06 15:31:48', '张丽');
    INSERT INTO `ordertb` VALUES (45, '京东', 'iphone 6s', '电子产品', 1000, '2015-04-06 15:31:48', '张涛');
    INSERT INTO `ordertb` VALUES (46, '京东', '佳洁士', '牙膏', 1000, '2015-04-06 15:31:48', '朱晓宇');
    INSERT INTO `ordertb` VALUES (47, '淘宝', '黑妹', '牙膏', 1000, '2015-04-06 15:31:48', '张涛');
    INSERT INTO `ordertb` VALUES (48, '淘宝', '佳洁士', '牙膏', 1000, '2015-04-10 15:31:48', '赵晓菲');
    INSERT INTO `ordertb` VALUES (49, '京东', '力士', '牙膏', 1000, '2015-04-10 15:31:48', '赵晓菲');
    INSERT INTO `ordertb` VALUES (50, '淘宝', '舒肤佳', '香皂', 1000, '2015-04-10 15:31:48', '朱晓宇');
    INSERT INTO `ordertb` VALUES (51, '淘宝', 'iphone 6s', '电子产品', 1000, '2015-04-10 15:31:48', '赵晓菲');
    INSERT INTO `ordertb` VALUES (52, '京东', '佳洁士', '牙膏', 1000, '2015-04-10 15:31:48', '张涛');
    INSERT INTO `ordertb` VALUES (53, '一号店', '佳洁士', '牙膏', 1000, '2015-04-10 15:31:48', '张涛');
    INSERT INTO `ordertb` VALUES (54, '京东', '佳洁士', '牙膏', 1000, '2015-04-10 15:31:48', '朱晓宇');
    INSERT INTO `ordertb` VALUES (55, '淘宝', '舒肤佳', '香皂', 1000, '2015-04-10 15:31:48', '朱晓宇');
    INSERT INTO `ordertb` VALUES (56, '淘宝', '佳洁士', '牙膏', 1000, '2016-05-03 14:39:24', '张涛');
    INSERT INTO `ordertb` VALUES (57, '京东', '华为888手机', '电子产品', 1000, '2016-05-03 14:39:24', '赵晓菲');
    INSERT INTO `ordertb` VALUES (58, '京东', '力士', '香皂', 1000, '2016-05-03 14:39:24', '张涛');
    INSERT INTO `ordertb` VALUES (59, '一号店', '佳洁士', '牙膏', 1000, '2016-05-03 14:39:24', '张丽');
    INSERT INTO `ordertb` VALUES (60, '淘宝', '佳洁士', '牙膏', 1000, '2016-05-03 14:39:25', '张涛');
    INSERT INTO `ordertb` VALUES (61, '淘宝', '佳洁士', '牙膏', 1000, '2016-04-05 14:40:49', '张涛');
    INSERT INTO `ordertb` VALUES (62, '京东', 'IPAD Mini', '电子产品', 1000, '2016-04-05 14:40:49', '赵晓菲');
    INSERT INTO `ordertb` VALUES (63, '淘宝', '高露洁', '牙膏', 1000, '2016-04-05 14:40:49', '张涛');
    INSERT INTO `ordertb` VALUES (64, '一号店', '佳洁士', '牙膏', 1000, '2016-04-05 14:40:49', '张丽');
    INSERT INTO `ordertb` VALUES (65, '京东', 'iphone 6s', '电子产品', 1000, '2016-04-05 14:40:49', '张涛');
    INSERT INTO `ordertb` VALUES (66, '京东', '佳洁士', '牙膏', 1000, '2016-04-05 14:40:49', '朱晓宇');
    INSERT INTO `ordertb` VALUES (67, '淘宝', '黑妹', '牙膏', 1000, '2016-04-05 14:40:49', '张涛');
    INSERT INTO `ordertb` VALUES (68, '淘宝', '佳洁士', '牙膏', 1000, '2016-04-05 14:40:49', '赵晓菲');
    INSERT INTO `ordertb` VALUES (69, '京东', '力士', '牙膏', 1000, '2016-04-05 14:40:49', '赵晓菲');
    INSERT INTO `ordertb` VALUES (70, '淘宝', '舒肤佳', '香皂', 1000, '2016-04-05 14:40:49', '朱晓宇');
    INSERT INTO `ordertb` VALUES (71, '淘宝', 'iphone 6s', '电子产品', 1000, '2016-04-05 14:40:49', '赵晓菲');
    INSERT INTO `ordertb` VALUES (72, '京东', '佳洁士', '牙膏', 1000, '2016-04-05 14:40:49', '张涛');
    INSERT INTO `ordertb` VALUES (73, '一号店', '佳洁士', '牙膏', 1000, '2016-04-05 14:40:49', '张涛');
    INSERT INTO `ordertb` VALUES (74, '京东', '佳洁士', '牙膏', 1000, '2016-04-05 14:40:49', '朱晓宇');
    INSERT INTO `ordertb` VALUES (75, '淘宝', '舒肤佳', '香皂', 1000, '2016-04-05 14:40:49', '朱晓宇');
    INSERT INTO `ordertb` VALUES (76, '淘宝', '佳洁士', '牙膏', 1000, '2016-04-05 14:40:50', '张涛');
    INSERT INTO `ordertb` VALUES (77, '京东', '华为888手机', '电子产品', 1000, '2016-04-05 14:40:50', '赵晓菲');
    INSERT INTO `ordertb` VALUES (78, '京东', '力士', '香皂', 1000, '2016-04-05 14:40:50', '张涛');
    INSERT INTO `ordertb` VALUES (79, '一号店', '佳洁士', '牙膏', 1000, '2016-04-05 14:40:50', '张丽');
    
    SET FOREIGN_KEY_CHECKS = 1;

### 二、java Bean实体类准备 ###

import lombok.AllArgsConstructor;
    import lombok.Builder;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    import java.util.Date;
    
    
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    @Builder
    public class OrderTB {
        
    
        private Integer orderID;
    
        private String webName;
    
        private String productName;
    
        private String productType;
    
        private Integer orderCount;
    
        private Date orderDate;
    
        private String agent;
    
    }

### 三、要执行的SQL语句 ###

public interface Constants {
        
    
        String QUERY_ORDER_TB_LIST = "SELECT ORDERID,WEBNAME,PRODUCTNAME,PRODUCTTYPE,ORDERCOUNT,ORDERDATE,AGENT FROM ORDERTB " +
                "WHERE WEBNAME = :webName AND PRODUCTNAME = :productName AND PRODUCTTYPE = :productType AND ORDERCOUNT = :orderCount AND " +
                "AGENT = :agent" ;
    }

*注意：可以看到，SQL语句放在interface修饰的接口内，至于为何放在接口内，可以参考下面文章：*

[java的接口][java]

### 四、定义业务接口service ###

public interface OrderService {
        
    
        List<OrderTB> queryOrderTB(QueryCondition condition);
    
    }

### 五、定义一个SqlParameterSource实现具名参数的实体类 ###

import lombok.AllArgsConstructor;
    import lombok.Builder;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    @Builder
    public class QueryCondition {
        
    
        private String webName;
    
        private String productName;
    
        private String productType;
    
        private Integer orderCount;
    
        private String agent;
    
    }

### 六、定义controller接口 ###

import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestBody;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.util.List;
    
    @RestController
    public class OrderController {
        
    
        @Autowired
        OrderService service;
    
        @GetMapping("/query/order/by/bean")
        public List<OrderTB> queryOrderList(@RequestBody QueryCondition condition){
        
            return service.queryOrderTB(condition);
        }
    }

### 七、业务实现 ###

使用SqlParameterSource实现具名参数，默认实现有 ：

1.  MapSqlParameterSource，只是封装了java.util.Map
2.  BeanPropertySqlParameterSource封装了一个JavaBean对象，通过JavaBean对象属性设置具名参数值
3.  EmptySqlParameterSource 一个空的SqlParameterSource ，占位使用

这里只对前两个的使用进行介绍。

#### （1）使用MapSqlParameterSource ####

**业务层实现：**

import lombok.extern.slf4j.Slf4j;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.RowMapper;
    import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
    import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
    import org.springframework.stereotype.Service;
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.List;
    
    @Service
    @Slf4j
    public class OrderServiceImpl implements OrderService {
        
    
        @Autowired(required = false)
        NamedParameterJdbcTemplate template;
    
    
        @Override
        public List<OrderTB> queryOrderTB(QueryCondition condition) {
        
    
            MapSqlParameterSource mapCondition = this.setCondition(condition);
    
            List<OrderTB> list = template.query(Constants.QUERY_ORDER_TB_LIST, mapCondition, new RowMapper<OrderTB>() {
        
                @Override
                public OrderTB mapRow(ResultSet rs, int rowNum) throws SQLException {
        
                    OrderTB orderTB = new OrderTB();
                    orderTB.setOrderID(rs.getInt("orderID"));
                    orderTB.setWebName(rs.getString("webName"));
                    orderTB.setProductName(rs.getString("productName"));
                    orderTB.setProductType(rs.getString("productType"));
                    orderTB.setOrderCount(rs.getInt("orderCount"));
                    orderTB.setOrderDate(rs.getDate("orderDate"));
                    orderTB.setAgent(rs.getString("agent"));
                    return orderTB;
                }
            });
            return list;
        }
    
        private MapSqlParameterSource setCondition(QueryCondition condition) {
        
            MapSqlParameterSource mapCondition = new MapSqlParameterSource();
            mapCondition.addValue("webName", condition.getWebName())
                    .addValue("productName", condition.getProductName())
                    .addValue("productType", condition.getProductType())
                    .addValue("orderCount", condition.getOrderCount())
                    .addValue("agent", condition.getAgent());
            return mapCondition;
        }
    }

**postman测试：**

![在这里插入图片描述][ec3ab2f6ae564792b553f7ef8d892904.png]

#### （2）使用BeanPropertySqlParameterSource ####

import lombok.extern.slf4j.Slf4j;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.RowMapper;
    import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
    import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
    import org.springframework.stereotype.Service;
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.List;
    
    @Service
    @Slf4j
    public class OrderServiceImpl implements OrderService {
        
    
        @Autowired(required = false)
        NamedParameterJdbcTemplate template;
    
    
        @Override
        public List<OrderTB> queryOrderTB(QueryCondition condition) {
        
    
            BeanPropertySqlParameterSource beanParam = this.setCondition(condition);
    
            List<OrderTB> list = template.query(Constants.QUERY_ORDER_TB_LIST, beanParam, new RowMapper<OrderTB>() {
        
                @Override
                public OrderTB mapRow(ResultSet rs, int rowNum) throws SQLException {
        
                    OrderTB orderTB = new OrderTB();
                    orderTB.setOrderID(rs.getInt("orderID"));
                    orderTB.setWebName(rs.getString("webName"));
                    orderTB.setProductName(rs.getString("productName"));
                    orderTB.setProductType(rs.getString("productType"));
                    orderTB.setOrderCount(rs.getInt("orderCount"));
                    orderTB.setOrderDate(rs.getDate("orderDate"));
                    orderTB.setAgent(rs.getString("agent"));
                    return orderTB;
                }
            });
            return list;
        }
    
        private BeanPropertySqlParameterSource setCondition(QueryCondition condition) {
        
            BeanPropertySqlParameterSource beanParam = new BeanPropertySqlParameterSource(condition);
            return beanParam;
        }
    }

**postman测试：**  
![在这里插入图片描述][fd20ef77590646248bc14db0fc9b315a.png]

[NamedParameterJdbcTemplate]: https://blog.csdn.net/m0_58680865/article/details/126901687?spm=1001.2014.3001.5501
[java]: https://blog.csdn.net/m0_58680865/article/details/122178092
[ec3ab2f6ae564792b553f7ef8d892904.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/0cb004822c814928af0bcf29df51a910.png
[fd20ef77590646248bc14db0fc9b315a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/592538398ff744d08d3eb71fc764636c.png