Connections could not be acquired from the underlying database

[Bruno Sant Ana]

Hey guys,

I bought a book about jsf + spring + hibernate which contains a e-commerce project as example. I'm trying to run this example project but it occurs an error. This is my persistence.xml file located at META-INF:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0"
xmlns="http://java.sun.com/xml/ns/persistence" xmlnssi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">

<persistence-unit name="ecommerce_teste">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<properties>

<property name="hibernate.archive.autodetection" value="class, hbm" />

<!-- Configuracoes de dialeto e conexao -->
<property name="hibernate.dialect" value="org.hibernate.dialect.MySQLInnoDBDialect" />
<property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver" />
<property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/ecommerce_teste" />
<property name="hibernate.connection.username" value="root" />
<property name="hibernate.connection.password" value="aquiMinhaSenha" />
<property name="connection.pool_size" value="1" />

<!-- Configuracoes de Debug -->
<property name="hibernate.show_sql" value="true" />
<property name="hibernate.format_sql" value="true" />
<property name="use_sql_comments" value="true" />

</properties>
</persistence-unit>
</persistence>

my database name is ecommerce_teste. I ensured that the username and password given in persistence.xml are right. The database is working fine because I'm able to access it from prompt command line(mysql -u root -psenha -D ecommerce_teste). Here is the printStackTrace:

INFO: Added Library from: jar:file:/C:/Users/bruno/workspace/.metadata/.plugins/org.eclipse.wst.server.core/tmp0/wtpwebapps/ProjEcommerce/WEB-INF/lib/trinidad-impl-1.2.7.jar!/META-INF/trh.taglib.xml
05/08/2013 06:57:12 com.sun.facelets.compiler.TagLibraryConfig loadImplicit
INFO: Added Library from: jar:file:/C:/Users/bruno/workspace/.metadata/.plugins/org.eclipse.wst.server.core/tmp0/wtpwebapps/ProjEcommerce/WEB-INF/lib/trinidad-impl-1.2.7.jar!/META-INF/tr.taglib.xml
06:57:15,808 ERROR JDBCExceptionReporter:78 - Connections could not be acquired from the underlying database!
05/08/2013 06:57:15 com.sun.facelets.FaceletViewHandler handleRenderException
GRAVE: Error Rendering View[/home.xhtml]
javax.faces.el.EvaluationException: javax.el.ELException: /template/template.xhtml @113,64 value="#{lojaController.todasCat}": Error reading 'todasCat' on type br.com.integrator.controller.LojaController
at javax.faces.component.ValueBindingValueExpressionAdapter.getValue(ValueBindingValueExpressionAdapter.java:106)
at com.sun.facelets.component.UIRepeat.getValue(UIRepeat.java:143)
at com.sun.facelets.component.UIRepeat.getDataModel(UIRepeat.java:121)
at com.sun.facelets.component.UIRepeat.setIndex(UIRepeat.java:305)
at com.sun.facelets.component.UIRepeat.process(UIRepeat.java:333)
at com.sun.facelets.component.UIRepeat.encodeChildren(UIRepeat.java:617)
at javax.faces.component.UIComponent.encodeAll(UIComponent.java:886)
at javax.faces.component.UIComponent.encodeAll(UIComponent.java:892)
at javax.faces.component.UIComponent.encodeAll(UIComponent.java:892)
at com.sun.facelets.FaceletViewHandler.renderView(FaceletViewHandler.java:57
at javax.faces.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:17
at org.apache.myfaces.trinidadinternal.application.ViewHandlerImpl.renderView(ViewHandlerImpl.java:176)
at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:106)
at com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:251)
at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:144)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:245)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:749)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:487)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:412)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:339)
at org.apache.jasper.runtime.PageContextImpl.doForward(PageContextImpl.java:746)
at org.apache.jasper.runtime.PageContextImpl.forward(PageContextImpl.java:716)
at org.apache.jsp.index_jsp._jspService(index_jsp.java:6
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.springframework.orm.jpa.support.OpenEntityManagerInViewFilter.doFilterInternal(OpenEntityManagerInViewFilter.java:112)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:16
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:929)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:11
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1002)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:585)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: javax.el.ELException: /template/template.xhtml @113,64 value="#{lojaController.todasCat}": Error reading 'todasCat' on type br.com.integrator.controller.LojaController
at com.sun.facelets.el.TagValueExpression.getValue(TagValueExpression.java:76)
at javax.faces.component.ValueBindingValueExpressionAdapter.getValue(ValueBindingValueExpressionAdapter.java:102)
... 50 more
Caused by: org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Cannot open connection
at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:375)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:377)
at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:263)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:101)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy51.todos(Unknown Source)
at br.com.integrator.controller.LojaController.getTodasCat(LojaController.java:102)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at javax.el.BeanELResolver.getValue(BeanELResolver.java:87)
at javax.el.CompositeELResolver.getValue(CompositeELResolver.java:67)
at com.sun.faces.el.FacesCompositeELResolver.getValue(FacesCompositeELResolver.java:64)
at org.apache.el.parser.AstValue.getValue(AstValue.java:183)
at org.apache.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:185)
at com.sun.facelets.el.TagValueExpression.getValue(TagValueExpression.java:71)
... 51 more
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Cannot open connection
at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:630)
at org.hibernate.ejb.TransactionImpl.begin(TransactionImpl.java:41)
at org.springframework.orm.jpa.DefaultJpaDialect.beginTransaction(DefaultJpaDialect.java:70)
at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:330)
... 68 more
Caused by: org.hibernate.exception.GenericJDBCException: Cannot open connection
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:426)
at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144)
at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:119)
at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:57)
at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1326)
at org.hibernate.ejb.TransactionImpl.begin(TransactionImpl.java:3
... 70 more
Caused by: java.sql.SQLException: Connections could not be acquired from the underlying database!
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:529)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:12
at org.hibernate.connection.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:56)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:423)
... 75 more
Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1319)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525)
... 78 more
06:57:15,908 ERROR JDBCExceptionReporter:78 - Connections could not be acquired from the underlying database!
06:57:45,100 ERROR JDBCExceptionReporter:78 - Connections could not be acquired from the underlying database!

Any suggestion what is the cause of this error? I'll appreciate any help.

Thanks

[Tim Holloway]

I cannot be certain, but I suspect that the MySQL tcp/ip port used by JDBC is being blocked by a firewall. I don't believe that the command-line MySQL client uses that port, so it could work when JDBC doesn't.

Incidentally, you don't write Controllers in JSF. The Controllers for JSF are in the FacesServlet and in the tag implementations. Backing beans are Models.

[Bruno Sant Ana]

Hi Tim,

Thank for your reply. I did another test: I create a new project with a simple connection using JDBC and the database is OK and there is no blocking firewall:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCStatementSelectExample {
 
	private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
	private static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/ecommerce_teste";
	private static final String DB_USER = "root";
	private static final String DB_PASSWORD = "password";
 
	public static void main(String[] argv) {
 
		try {
 
			selectRecordsFromDbUserTable();
 
		} catch (SQLException e) {
 
			System.out.println(e.getMessage());
 
		}
 
	}
 
	private static void selectRecordsFromDbUserTable() throws SQLException {
 
		Connection dbConnection = null;
		Statement statement = null;
 
		String selectTableSQL = "SELECT id, usuario, senha FROM admin";
 
		try {
			dbConnection = getDBConnection();
			statement = dbConnection.createStatement();
 
			System.out.println(selectTableSQL);
 
			// execute select SQL stetement
			ResultSet rs = statement.executeQuery(selectTableSQL);
 
			while (rs.next()) {
 
				String userid = rs.getString("id");
				String username = rs.getString("usuario");
 
				System.out.println("userid : " + userid);
				System.out.println("username : " + username);
 
			}
 
		} catch (SQLException e) {
 
			System.out.println(e.getMessage());
 
		} finally {
 
			if (statement != null) {
				statement.close();
			}
 
			if (dbConnection != null) {
				dbConnection.close();
			}
 
		}
 
	}
 
	private static Connection getDBConnection() {
 
		Connection dbConnection = null;
 
		try {
 
			Class.forName(DB_DRIVER);
 
		} catch (ClassNotFoundException e) {
 
			System.out.println(e.getMessage());
 
		}
 
		try {
 
			dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,
					DB_PASSWORD);
			return dbConnection;
 
		} catch (SQLException e) {
 
			System.out.println(e.getMessage());
 
		}
 
		return dbConnection;
 
	}
 
}

Output:

SELECT id, usuario, senha FROM admin
userid : 1
username : bruno


The controller is just a managed bean, but in the book's code the author called it LojaController.

Any other suggestion?

[Tim Holloway]

I would worry about learning JSF from a book that doesn't understand what a Controller does.

It's hard for me to tell, but I think you may need to define a database connection pool to your webapp server. I user Hibernate+Spring all the time, but I'm using pooled connections and my persistence.xml looks more like this:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0"
    xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
     http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"
>
    <persistence-unit name="myDataSource" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <jta-data-source>jdbc/localDB</jta-data-source>
        <non-jta-data-source>jdbc/localDB</non-jta-data-source>
        <exclude-unlisted-classes>false</exclude-unlisted-classes>
        <properties>
            <property name="hibernate.dialect"
                value="org.hibernate.dialect.DB2Dialect" />
            <property name="hibernate.show.sql" value="true"></property> 
        </properties>
    </persistence-unit>
</persistence>

This is DB2, not MySQL, but that's nothing major. Most of the database properties are in the spring beans. The EntityManagerFactory bean uses this bean as its DataSource:

	<bean id="myDataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
		<property name="jndiName" value="java:comp/env/jdbc/localDB" />
	</bean>

And the JNDI resource is defined in the application's deployment descriptor. Specifically, the Database Connection Pool definition for jdbc/localDB.