Java8: solve the problem of Duplicate key when converting List to Map

Keywords: java8 Solutions

Get a List collection containing objects, and then select two properties. When performing List conversion to Map, the following error is reported:

java.lang.IllegalStateException: Duplicate key

Possible problems

Originally, I wanted to solve this problem by adding formal parameters (entity1, entity2) - > entity1), but I still want to solve this problem from the data source, because I'm worried that this processing method will lead to abnormal business data and Duplicate key duplicate business data is like a black box phenomenon. In serious cases, it may be a time bomb and will always break out in a test scenario.

IpMacQuery ipMacQuery = new IpMacQuery();
List<IpMacRelation> macList = IpMacRelationDao.queryGroupConcatMac(ipMacQuery);
Map<Integer, String> macList = macList.stream().collect(Collectors.toMap(IpMacRelation::getAssetId, IpMacRelation::getMac),(entity1, entity2) -> entity1));

The last discovery is because of MYSQL GROUP_CONCAT )The sorting problem is caused by the + SQL UNION operator. An example of the problem data is as follows

The UNION operator is used to combine the result sets of two or more SELECT statements.

Note that each SELECT statement inside a UNION must have the same number of columns. Columns must also have similar data types. At the same time, the order of columns in each SELECT statement must be the same

SQL UNION operator | rookie tutorial (runoob.com)

Three IP addresses and three MAC addresses. This time, MAC is used as a demonstration
```
100	10.255.174.152	B0:83:FE:88:46:3A
100	3.3.5.7			62:45:23:45:23:46
100	3.3.5.8			25:43:53:54:53:54
```
If the values in the results are not sorted, the sorting of multiple Macs is different, and the unordered query results will be different from the query results added to the sorting.
- Unsorted query results
```
SELECT
	test.id,
	group_concat( test.mac ) AS macs 
FROM
  ip_mac_test test
GROUP BY
	test.pc_id	
```
```
#1 
```

100 B0:83:FE:88:46:3A,62:45:23:45:23:46,25:43:53:54:53:54

Our expected result is that the content of the query is the same, even if MAC If the order is different, it should also be regarded as the same record, so we can implement it after sorting, as follows

- Add sorted query results

```sql
SELECT
	test.pc_id,
	group_concat( test.mac ORDER BY test.mac ) AS macs 
FROM
  ip_mac_test test 
GROUP BY
  test.pc_id

Query results

#2
100 25:43:53:54:53:54,62:45:23:45:23:46,B0:83:FE:88:46:3A

Comparison of two results

#1 
100 B0:83:FE:88:46:3A,62:45:23:45:23:46,25:43:53:54:53:54
#2
100 25:43:53:54:53:54,62:45:23:45:23:46,B0:83:FE:88:46:3A

Examples of abnormal scenarios

Using the UNION operator, if result set 1 is not sorted and result set 2 is sorted, the same PC will appear_ ID and macs have different result sets (but the actual content is the same), which also causes the Duplicate key exception

SELECT
	test.pc_id,
	group_concat( test.mac ) AS macs 
FROM
	ip_mac_test test 
GROUP BY
	test.pc_id UNION
SELECT
	test.pc_id,
	group_concat( test.mac ORDER BY test.mac ) AS macs 
FROM
	ip_mac_test test 
GROUP BY
	test.pc_id

Same pc_id and macs are different result sets (but in fact, the contents are the same, and the splicing string order of group_concat is different)

100	B0:83:FE:88:46:3A,62:45:23:45:23:46,25:43:53:54:53:54
100	25:43:53:54:53:54,62:45:23:45:23:46,B0:83:FE:88:46:3A

From the above operations, we can see that if two SQL queries use group_ When concat and union do not use the sort ORDER BY uniformly, the merged result set must cause an exception when converting List to Map.

Correct example

SELECT
	test.pc_id,
	group_concat( test.mac ORDER BY test.mac ) AS macs 
FROM
	ip_mac_test test 
GROUP BY
	test.pc_id UNION
SELECT
	test.pc_id,
	group_concat( test.mac ORDER BY test.mac ) AS macs 
FROM
	ip_mac_test test 
GROUP BY
	test.pc_id

# In a real project, the where conditions of the first and second SELECT may be different. Here is just a simple example

Sample data SQL

DDL

CREATE TABLE `ip_mac_test` (
  `id` int(11) NOT NULL,
  `pc_id` int(11) DEFAULT NULL,
  `ip` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mac` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DML

INSERT INTO `ip_mac_test`(`id`, `pc_id`, `ip`, `mac`) VALUES (1, 100, '10.255.174.152', 'B0:83:FE:88:46:3A');
INSERT INTO `ip_mac_test`(`id`, `pc_id`, `ip`, `mac`) VALUES (2, 100, '3.3.5.7', '62:45:23:45:23:46');
INSERT INTO `ip_mac_test`(`id`, `pc_id`, `ip`, `mac`) VALUES (3, 100, '3.3.5.8', '25:43:53:54:53:54');

Reference source

Posted by kkonline on Mon, 22 Nov 2021 15:45:50 -0800

Programmer Group