spring boot Java在BeanPropertyRowMapper上对ArrayList的缓慢SQL检索<accountQueryResult>
我们在SpringBoot框架中有一个非常慢的Java API函数，下面是它的分析输出：
我们相信这是来自这一行代码：
该行之前的查询检索41573行（给定指定的accountId和一个以上参数）——这是在大约2秒的可接受时间内完成的
return (ArrayList) this.pbaJdbc.query(sql, new Object[] { accountId, taxRegId }, bpm);
我们尝试将FetchSize设置为1000（默认情况下设置为-1，我无法确定文档中的含义）-此更改并没有显著提高操作的性能
另一个建议是切换到HashMap结构而不是ArrayList，但我不知道如何做到这一点。。。尝试编写哈希映射，类似于：
HashMap<Integer, accountQueryResult> accmap = new HashMap(Integer, accountQueryResult);
但这与预期的表达错误，我不知道如何做它的权利
这是供您审阅的相关代码：
@Repository
public class UserRepository {
@Autowired
@Qualifier("pbaJdbc")
private JdbcTemplate pbaJdbc;
public ArrayList<accountQueryResult> getAccountsSubscriptionsResources(int accountId, String taxRegId) {
BeanPropertyRowMapper bpm = new BeanPropertyRowMapper(accountQueryResult.class);
bpm.setPrimitivesDefaultedForNullValue(true);
String sql =
"SELECT " +
"\"Account\".\"AccountID\",\n" +
"\"Account\".\"VendorAccountID\",\n" +
"\"Account\".\"AdminPhAreaCode\",\n" +
"\"Account\".\"AdminPhNumber\",\n" +
"\"Account\".\"AdminFaxAreaCode\",\n" +
"\"Account\".\"AdminFaxNumber\",\n" +
"\"Account\".\"AdminEmail\",\n" +
"\"Account\".\"PersPhAreaCode\",\n" +
"\"Account\".\"PersPhNumber\",\n" +
"\"Account\".\"PersFaxAreaCode\",\n" +
"\"Account\".\"PersFaxNumber\",\n" +
"\"Account\".\"PersEmail\",\n" +
"\"Account\".\"TaxStatus\",\n" +
"\"Account\".\"CompanyName\",\n" +
"\"Account\".\"Address1\",\n" +
"\"Account\".\"Address2\",\n" +
"\"Account\".\"City\",\n" +
"\"Account\".\"Zip\",\n" +
"\"ActivePaytool\".\"CutNumber\",\n" +
"\"ActivePaytool\".\"PaySystem\",\n" +
"\"Subscription\".\"subscriptionID\",\n" +
"\"Subscription\".\"SubscriptionName\",\n" +
"\"Subscription\".\"Status\",\n" +
"\"Subscription\".\"PlanID\",\n" +
"\"PlanPeriod\".\"Period\",\n" +
"\"PlanPeriod\".\"PlanPeriodID\",\n" +
"\"PlanPeriod\".\"PeriodType\",\n" +
"\"PlanPeriod\".\"RenewalFee\",\n" +
"\"PlanPeriod\".\"SetupFee\",\n" +
"\"SubscrParam\".\"resourceID\",\n" +
"\"BMResource\".\"name\" AS \"ResourceName\",\n" +
"\"SubscrParam\".\"IncludedValue\",\n" +
"\"SubscrParam\".\"Amount\",\n" +
"\"SubscrParamValue\".\"IdParameter\",\n" +
"\"SubscrParamValue\".\"Value\",\n" +
"\"IntUsers\".\"UsersID\",\n" +
"\"IntUsers\".\"Login\" AS \"LoginID\"\n" +
"FROM\n" +
"\"Account\"\n" +
"LEFT JOIN \"IntUsers\" ON \"IntUsers\".\"AccountID\" = \"Account\".\"AccountID\"\n" +
"LEFT JOIN \"Subscription\" ON \"Subscription\".\"AccountID\" = \"Account\".\"AccountID\"\n" +
"AND \"Subscription\".\"Status\" IN ('30', '40', '15')\n" +
"LEFT JOIN \"SubscrParam\" ON \"SubscrParam\".\"subscriptionID\" = \"Subscription\".\"subscriptionID\"\n" +
"LEFT JOIN \"BMResource\" ON \"BMResource\".\"resourceID\" = \"SubscrParam\".\"resourceID\"\n" +
"LEFT JOIN \"SubscrParamValue\" ON \"SubscrParamValue\".\"subscriptionID\" = \"Subscription\".\"subscriptionID\"\n" +
"LEFT JOIN \"PlanPeriod\" ON \"PlanPeriod\".\"PlanID\" = \"Subscription\".\"PlanID\"\n" +
"AND NOT (\n" +
"\"Subscription\".\"Period\" = \"PlanPeriod\".\"Period\"\n" +
"AND \"Subscription\".\"PeriodType\" = \"PlanPeriod\".\"PeriodType\"\n" +
")\n" +
"AND \"PlanPeriod\".\"Enabled\" = 1\n" +
"AND \"PlanPeriod\".\"Trial\" = 0\n" +
"LEFT JOIN (\n" +
"SELECT\n" +
" \"DefPayTool\".\"PayToolID\",\n" +
" \"PayTool\".\"CutNumber\",\n" +
" \"PayTool\".\"PaySystem\",\n" +
" \"PayTool\".\"OwnerAccountID\"\n" +
"FROM\n" +
" \"PayTool\"\n" +
"INNER JOIN \"DefPayTool\" ON \"DefPayTool\".\"AccountID\" = \"PayTool\".\"OwnerAccountID\"\n" +
"WHERE\n" +
" \"PayTool\".\"IsSuspended\" = 0\n" +
"GROUP BY\n" +
" \"PayTool\".\"PayToolID\",\n" +
" \"PayTool\".\"CutNumber\",\n" +
" \"PayTool\".\"PaySystem\",\n" +
" \"PayTool\".\"OwnerAccountID\",\n" +
" \"DefPayTool\".\"PayToolID\"\n" +
"HAVING\n" +
" COUNT (*) > 0\n" +
") AS \"ActivePaytool\" ON \"ActivePaytool\".\"OwnerAccountID\" = \"Account\".\"AccountID\"\n" +
"WHERE\n" +
"\"Account\".\"AccountID\" = ? " +
"AND \"Account\".\"TaxRegID\" = ? " +
"ORDER BY\n" +
"\"Account\".\"AccountID\",\n" +
"\"Subscription\".\"subscriptionID\";";
return (ArrayList) this.pbaJdbc.query(sql, new Object[] { accountId, taxRegId }, bpm);
}
下面是当前用作映射器的accountQueryResult类
package com.store.models.query;
public class accountQueryResult {
private int AccountID;
private int VendorAccountID;
private String CompanyName;
private String AdminPhAreaCode;
private String AdminPhNumber;
private String AdminFaxAreaCode;
private String AdminFaxNumber;
private String AdminEmail;
private String PersPhAreaCode;
private String PersPhNumber;
private String PersFaxAreaCode;
private String PersFaxNumber;
private String PersEmail;
private int TaxStatus;
private String CutNumber;
private String PaySystem;
private int subscriptionID;
private String SubscriptionName;
private int Status;
private int PlanID;
private int resourceID;
private String ResourceName;
private int IncludedValue;
private int Amount;
private String IdParameter;
private String Value;
private int UsersID;
private String LoginID;
private String Address1;
private String Address2;
private String City;
private String Zip;
private int period;
private int planPeriodID;
private int periodType;
private double renewalFee;
private double setupFee;
// Ommited getter and setters
}
请协助，如何更改此建议之一是使用JPA，最好是进行最小的更改，以避免以后必须更改处理结果阵列的所有业务逻辑
谢谢
# 1 楼答案
在处理这样的性能问题时，首先要弄清楚时间花在了哪里
考虑到你的探查器信息，我觉得奇怪的是，获取Int似乎比获取其他字段花费的时间要长得多。我认为可能的原因如下：
getInt执行一些昂贵的转换，例如，JDDBC驱动程序可能返回BigDecimal，然后必须将其转换为int它只是第一个被访问的列，实际上会触发抓取并花费大量时间等待
对于整体性能而言，另一个问题可能是
BeanPropertyRowMapper使用反射，但到目前为止，这甚至没有出现在分析器中我认为，语句可能在2秒后返回，但实际上要花费更长的时间才能获取所有的结果。
为了澄清这种情况，我建议测试以下各项：
检查JDBC驱动程序返回的
ResultSet中实际包含的数据类型，并检查是否可以通过更改列类型或在SQL语句中强制转换来获得更合适的数据类型从所有行中获取所有列，而无需对它们进行任何昂贵的操作。只需确保优化器不会删除访问权限。例如，您可能希望从每个值创建一个散列，然后添加所有散列，并在最后打印。检查一下它的性能