您的位置: 首页  >  技术问答  >  Hibernate - Oracle - 一次取很多数据-Cannot open connection

Hibernate - Oracle - 一次取很多数据-Cannot open connection

分类: 技术问答 • 2022-03-13 12:28:57

Hibernate - Oracle - 一次取很多数据-Cannot open connection

问题描述：

[quote]2010-10-21 10:29:51,140 [http-8888-2] ERROR in [org.hibernate.util.JDBCException
Reporter.logExceptions(JDBCExceptionReporter.java:101)] - Listener refused the c
onnection with the following error:
ORA-12519, TNS:no appropriate service handler found
The Connection descriptor used by the client was:
127.0.0.1:1521:ORBITDB

org.hibernate.exception.GenericJDBCException: Cannot open connection
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException
(SQLStateConverter.java:126)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.j
ava:114)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelp
er.java:66)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelp
er.java:52)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager
.java:449)
at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.
java:167)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatc
her.java:161)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1573)
at org.hibernate.loader.Loader.doQuery(Loader.java:696)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Lo
ader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2228)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
at org.hibernate.loader.Loader.list(Loader.java:2120)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1722)

    at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:

165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.j
ava:835)
at cn.comdev.dao.AssetPropertyDAL.GetAssetPropertyValue(AssetPropertyDAL
.java:83)
at cn.comdev.enterprise.AssetAssocBLL.BuildNextLevelFacilityHierarchy(As
setAssocBLL.java:418)
at cn.comdev.enterprise.AssetAssocBLL.BuildNextLevelFacilityHierarchyRec
ursively(AssetAssocBLL.java:335)
at cn.comdev.enterprise.AssetAssocBLL.BuildNextLevelFacilityHierarchyRec
ursively(AssetAssocBLL.java:340)
at cn.comdev.enterprise.AssetAssocBLL.GetFacilityHierarchy(AssetAssocBLL
.java:258)
at cn.comdev.service.EquipmentExportService.WS_GetOrganizationHierarchy(
EquipmentExportService.java:174)
at test.WS_GetOrganizationHierarchy.doGet(WS_GetOrganizationHierarchy.ja
va:69)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:206)
at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.do
FilterInternal(OpenSessionInViewFilter.java:198)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerR
equestFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperV
alve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextV
alve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.j
ava:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.j
ava:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineVal
ve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.jav
a:298)
at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcesso
r.java:861)
at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.pr
ocess(Http11AprProtocol.java:579)
at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:15
84)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.SQLException: Listener refused the connection with the follo
wing error:
ORA-12519, TNS:no appropriate service handler found
The Connection descriptor used by the client was:
127.0.0.1:1521:ORBITDB

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java

:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:261)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387)
at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:
441)
at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:165)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtensio
n.java:35)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
at java.sql.DriverManager.getConnection(DriverManager.java:525)
at java.sql.DriverManager.getConnection(DriverManager.java:140)
at org.hibernate.connection.DriverManagerConnectionProvider.getConnectio
n(DriverManagerConnectionProvider.java:133)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager
.java:446)
... 38 more
java.lang.RuntimeException: Cannot open connection
[/quote]

答

我给你写一个connection连接；
[code="java"]
public class DB
{
private static Connection con = null;
private static ResultSet rs = null;
private static PreparedStatement pstm = null;

public static Connection getCon()
{
    String strDriver = "oracle.jdbc.driver.OracleDriver";
    String strUrl = "jdbc:oracle:thin:@192.168.100.70:1521:orcl";
    String strUid = "fms";
    String strPid = "fms!123";

    try
    {
        Class.forName(strDriver);
        con = DriverManager.getConnection(strUrl, strUid, strPid);
    }
    catch (Exception e)
    {
        e.printStackTrace();
        con = null;
    }
    return con;
}

public static void closeCon(Connection con, Statement stmt, ResultSet rs)
{
    try
    {
        if (rs != null)
        {
            try
            {
                rs.close();
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
        }
        if (stmt != null)
        {
            try
            {
                stmt.close();
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
        }
        if (con != null)
        {
            try
            {
                con.close();
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
        }
    }
    catch (Exception e)
    {
        e.printStackTrace();
    }
}

}
[/code]
用法
[code="java"]
public class FmBld0101Dao
{
private Connection con = null;
ResultSet rs = null;
CallableStatement cstm = null;

@SuppressWarnings("static-access")
public String GetMaxBldcode() 
{
    try 
    {      //我这里用的是存储过程你可以换别的。jdbc也行。你自己写吧
        String sql = "call SP_FMS_BLD_GetMaxBuildingCode(?)";

        con = DB.getCon();
        cstm = con.prepareCall(sql);
        cstm.registerOutParameter(1, Types.VARCHAR);
        cstm.execute();

    } 
    catch (Exception e)
    {
        e.printStackTrace();
    } 
    finally 
    {
        DB.closeCon(con, cstm, null);
    }
    return "";
}

[/code]

答

一个是Oracle初始化参数的设置问题导致了bug。
在一个就是你的数据库现有的进程数，已经达到参数processes的大小，
查看一下
1.select count(*) from v$process; 取得数据库目前的进程数。
2.select value from v$parameter where name = 'processes'; 取得进程数的上限。
3.如已达到上限，修改initSID.ora中的processes的大小。
4.重新启动数据库到nomount状态下，执行create spfile from pfile; 并startup open
但是这种方式不提倡去更改进程上线。可能你在做项目的时候connection没有得到很好的处理，导致的connection没有关闭，一直在创建connection。我建议重新处理一下connection的连接。

答

你最好还是仔细的测试一下，我想只有这么一个问题导致的。
因为你有一次出现连接线程超出的时候线程就会减少，还有一定时间内也会减少。
用多个机器去访问，一个人去不断的查询对数据库的连接进程数。看看是否在不断的增加，最后可能超出最大的线程数。
不管怎么说，出现了这种问题就是你的项目在处理connection的时候没有处理好。实在你找不出问题建议用连接池。

相关推荐