hikaricp連接sqlite hikaripool连接超时

转载

AI独步天下 2024-03-21 06:21:46

文章标签 hikaricp連接sqlite 数据库 mysql java bc 文章分类 计算机视觉人工智能

项目场景：

提示：这里简述项目相关背景：

项目场景：Springboot框架,hibernate框架,mysql数据库,hikari连接池

问题描述

项目部署到生产环境启动20分钟左右就会报错,报链接池链接数据库超时;
HikariPool-1 - Connection is not available, request timed out after 30001ms.

dao代码展示：

这是一种比较老并且恶心的sql写法,也是第一次接触这种框架

@Override
public List<BContractApplyListVO> selectContractList( String parentId) {
    StringBuffer sql = new StringBuffer();
    sql.append("SELECT * FROM ( select null AS id, a.contract_id as contractId\n" +
        "      ,a.contract_code as contractCode\n" +
        "      ,a.project_name as projectName\n" +
        "      ,a.parent_contract_id as parentContractId\n" +
        "      ,a.customer_name as customerName\n" +
        "      ,a.device_name as deviceName\n" +
        "      ,a.contract_type as contractType\n" +
        "      ,a.create_user as createUser\n" +
        "      ,a.create_user_name as createUserName\n" +
        "      ,a.flow_process_id as flowProcessId  \n" +
        "      ,a.contract_title as contractTitle\n" +
        "      ,a.verify_status as verifyStatus\n" +
        "      ,a.create_time as createTime\n" +
        "      ,a.execute_status as executeStatus  \n" +
        "      ,if(isnull(t.subNumber),'0','1') AS subExecuteStatus\n" +
        "     ,'0' AS appType,a.del_flag AS delFlag,a.tenant_id AS tenantId,a.business_type AS businessType,a.customer_credit_code AS customerCreditCode,a.project_id as projectId\n" +
        "  from b_contract_apply a\n" +
        "  left join (\n" +
        "select parent_contract_id,count(*) AS subNumber\n" +
        "from b_contract_apply bca \n" +
        "where bca.parent_contract_id IS NOT NULL \n" +
        "and bca.execute_status = 0 \n" +
        "GROUP BY bca.parent_contract_id HAVING COUNT(*) > 0 \n" +
        "  ) t on t.parent_contract_id = a.contract_id \n");
    sql.append(" UNION ALL " +
        "SELECT                                     \n" +
        "  bccf.id AS id,                          \n" +
        "  bccf.contract_id AS contractId,         \n" +
        "  bccf.contract_code AS contractCode,     \n" +
        "  bccf.project_name AS projectName,       \n" +
        "  '' AS parentContractId,                 \n" +
        "  bccf.customer_name AS customerName,     \n" +
        "  bccf.device_name AS deviceName,         \n" +
        "  bccf.contract_type AS contractType,     \n" +
        "  bccf.create_user AS createUser,         \n" +
        "  bccf.flow_process_id as flowProcessId,  \n" +
        "  bccf.create_user_name AS createUserName,\n" +
        "  bccf.contract_name AS contractTitle,    \n" +
        "  bccf.verify_status AS verifyStatus,     \n" +
        "  bccf.create_time AS createTime,         \n" +
        "  bccf.execute_status AS executeStatus,   \n" +
        "  '' AS subExecuteStatus,                 \n" +
        "  '1' AS appType,                         \n" +
        "  bccf.del_flag AS delFlag,               \n" +
        "  bccf.tenant_id AS tenantId,                         \n" +
        "  '' AS businessType,bccf.customer_file_name AS customerCreditCode,'' AS projectId          \n" +
        " FROM b_contract_change_flow bccf ) ai     \n" +
        " where ai.delFlag = 0 ");
    if (!StringUtils.isEmpty(parentId)){
        sql.append("and ai.parentContractId = '").append(parentId).append("'");
    }else {
        sql.append(" and ai.verifyStatus = 1 and ai.executeStatus = 0");
    }
    Query query = entityManager.createNativeQuery(sql.toString());
    List<BContractApplyListVO> list = query.unwrap(NativeQuery.class).setResultTransformer(new FluentHibernateResultTransformer(BContractApplyListVO.class)).list();
    EntityManagerFactoryUtils.closeEntityManager(this.entityManager);
    return list;
}