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
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 220.127.116.11 62:45:23:45:23:46 100 18.104.22.168 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
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
#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.
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
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;
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, '22.214.171.124', '62:45:23:45:23:46'); INSERT INTO `ip_mac_test`(`id`, `pc_id`, `ip`, `mac`) VALUES (3, 100, '126.96.36.199', '25:43:53:54:53:54');
- A problem with Collectors.toMap - Jianshu (jianshu.com)
- (70 messages) mysql uses group_ Irregular data sequence during concat splicing query_ Wang Mengjiao's blog - CSDN blog
- Function CONCAT and group in MySQL_ CONCAT - Wang buhun - blog Park (cnblogs.com)
- (69 messages) Java 8 – Convert List to Map_ wangmm0218 blog - CSDN blog
- java8 two List sets take intersection, union, difference, de duplication Union - Nuggets (juejin.cn)
- List set conventional de duplication and new feature de duplication methods of java8 - Yifeng blog - blog Garden (cnblogs.com)