I'm creating a web application with Netbeans 7.3, JSF 2.1 with Primefaces 3.5 and Hibernate 3.2.5. I'm running it on a glassfish server 3.1.2 My project has some upkeeps ( I'm not sure about how to call it in english, I'm talking about a screen where you can create, update and delete data ) as this one:
(I changed some original names and texts from Spanish to English)
MANOPINIONES VIEW:
<f:view locale="#{sesion.idiomaActual}">
<h:head>
<link href="CSS/estilos.css" rel="stylesheet" type="text/css"/>
</h:head>
<h:body rendered="#{sesion.isLogadoAdm()}">
<h:form>
<div class="area_mantenimiento">
<br/>
<p:panel id="panelopi" header="Mantenimiento de opiniones" styleClass="panel_mantenimiento_opi">
<p:messages globalOnly="true"/>
<br/>
<p:toolbar>
<p:toolbarGroup align="left">
<p:commandButton title="Ok" icon="ui-icon-check" disabled="#{Manopiniones_BackingBean.disabled_ok}" actionListener="#{Manopiniones_BackingBean.saveChangesListener(event)}" update="panelopi"/>
<p:commandButton title="Cancel" icon="ui-icon-closethick" disabled="#{Manopiniones_BackingBean.disabled_cancel}" actionListener="#{Manopiniones_BackingBean.cancelChangesListener(event)}" update="panelopi"/>
<p:commandButton title="Delete" icon="ui-icon-circle-minus" type="button" disabled="#{Manopiniones_BackingBean.disabled_del}" onclick="vardialogoopi.show();"/>
</p:toolbarGroup>
</p:toolbar>
<br/><br/>
<p:panel>
<p:panelGrid columns="3" styleClass="rejilla_panel_login" style="margin:0 0 0 0;font: 10px Verdana;">
<h:outputText value="Codigo de opinion:" style="font-weight:bold;"/>
<p:inputText value="#{Manopiniones_BackingBean.codigo}" disabled="#{Manopiniones_BackingBean.disabled_cod}" />
<p:commandButton title="Buscar" value=" " icon="ui-icon-search" disabled="#{Manopiniones_BackingBean.disabled_bot}" actionListener="#{Manopiniones_BackingBean.getOpinionListener(event)}" update="panelopi"/>
</p:panelGrid>
</p:panel>
<p:separator style="width:580px;height:10px; margin:10px auto auto 0;" />
<br/>
<p:panel>
<p:panelGrid columns="2" styleClass="rejilla_panel_login" style="margin:0 0 0 0;font: 10px Verdana;">
<h:outputText value="Texto: " style="font-weight:bold;"/>
<p:editor value="#{Manopiniones_BackingBean.opinion_actual.opiDes}" maxlength="5000" height="150" width="450" disabled="#{Manopiniones_BackingBean.disabled_opi}"/>
<h:outputText value="" />
<h:outputText value="" />
</p:panelGrid>
</p:panel>
<p:confirmDialog message="¿Are you sure to delete this?" header="Confirmacion" severity="alert" widgetVar="vardialogoopi" >
<p:commandButton value="OK" actionListener="#{Manopiniones_BackingBean.deleteOpinionListener(event)}" update="panelopi" oncomplete="vardialogoopi.hide();" />
<p:commandButton value="Cancel" type="button" onclick="vardialogoopi.hide();" />
</p:confirmDialog>
</p:panel>
</div>
</h:form>
</h:body>
</f:view>
MANOPINIONES BACKING BEAN:
@ManagedBean
@ViewScoped
public class Manopiniones_BackingBean {
private Session sesion=null;
private Opiniones opinion_actual;
private Boolean disabled_ok=true;
private Boolean disabled_cancel=true;
private Boolean disabled_del=true;
private Boolean disabled_cod=false;
private Boolean disabled_bot=false;
private Boolean disabled_opi=true;
private String codigo="";
public Manopiniones_BackingBean() {
}
@PreDestroy
public void parar() {
if(sesion!=null && sesion.isOpen()){
sesion.getTransaction().rollback();
sesion.close();
opinion_actual=new Opiniones();
disabled_ok=true;
disabled_cancel=true;
disabled_del=true;
disabled_cod=false;
disabled_bot=false;
disabled_opi=true;
}
}
public Session getSesion() {
return sesion;
}
//GETTER & SETTERS OMITTED
public void saveChangesListener(ActionEvent event) {
try {
if(opinion_actual!=null && !codigo.isEmpty()){
opinion_actual.setOpiCod(codigo);
sesion.saveOrUpdate(opinion_actual);
sesion.getTransaction().commit();
sesion.close();
opinion_actual=new Opiniones();
disabled_ok=true;
disabled_cancel=true;
disabled_del=true;
disabled_cod=false;
disabled_bot=false;
disabled_opi=true;
};
}
catch (HibernateException he) {
FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error saving opinion.", null));
sesion.getTransaction().rollback();
sesion.close();
};
}
public void cancelChangesListener(ActionEvent event) {
sesion.getTransaction().rollback();
sesion.close();
opinion_actual=new Opiniones();
disabled_ok=true;
disabled_cancel=true;
disabled_del=true;
disabled_cod=false;
disabled_bot=false;
disabled_opi=true;
}
public void deleteOpinionListener(ActionEvent event) {
try {
sesion.delete(opinion_actual);
sesion.getTransaction().commit();
sesion.close();
opinion_actual=new Opiniones();
codigo="";
disabled_ok=true;
disabled_cancel=true;
disabled_del=true;
disabled_cod=false;
disabled_bot=false;
disabled_opi=true;
}
catch (HibernateException he) {
FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error deleting opinion.", null));
sesion.getTransaction().rollback();
sesion.close();
};
}
public void getOpinionListener(ActionEvent event) {
try {
if(!codigo.isEmpty()){
sesion=HibernateUtil.getSessionFactory().openSession();
sesion.beginTransaction();
opinion_actual=(Opiniones)sesion.get(Opiniones.class, codigo);
disabled_ok=false;
disabled_cancel=false;
disabled_cod=true;
disabled_bot=true;
disabled_opi=false;
disabled_del=false;
if(opinion_actual==null){
opinion_actual=new Opiniones();
disabled_del=true;
};
};
}
catch (HibernateException he) {
FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error getting opinion.", null));
sesion.getTransaction().rollback();
sesion.close();
};
}
}
As you can see its a very simple example of "upkeep" and with the original configuration it worked perfectly.
I wanted to implement a connection pool using the c3p0 library to improve the speed so I added this lines on the hibernate.cfg.xml
HIBERNATE.CFG.XML:
<hibernate-configuration>
<session-factory>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/agencia</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password">THE_PASSWORD</property>
<property name="hibernate.show_sql">true</property>
<property name="hibernate.query.factory_class">org.hibernate.hql.classic.ClassicQueryTranslatorFactory</property>
<property name="hibernate.current_session_context_class">thread</property>
<property name="hibernate.jdbc.batch_size">50</property>
**<!-- ADDED LINES TO IMPLEMENT CONNECTION POOL -->**
<property name="hibernate.c3p0.min_size">8</property>
<property name="hibernate.c3p0.max_size">32</property>
<property name="hibernate.c3p0.timeout">300</property>
<property name="hibernate.c3p0.max_statements">50</property>
<property name="hibernate.c3p0.idle_test_period">300</property>
<property name="hibernate.c3p0.acquire_increment">1</property>
<property name="hibernate.connection.datasource">jdbc/PoolAgencia</property>
**<!-- ADDED LINES TO IMPLEMENT CONNECTION POOL -->**
**<!-- HIBERNATE MAPPING OMITTED -->**
</session-factory>
</hibernate-configuration>
I also created a connection pool on the glassfish server.
I downloaded and added the mysql-connector-java-5.0.8-bin to my server. To configure the pool I just changed these optional properties:
Databasename = the_database_name
Password = the_password
Portnumber = 3306
Servername = localhost
User = root
And finally I created a JDBC Resource called jdbc/PoolAgencia.
It seemed to work well, data was retrieved very fast at other points of the web. Then I used the upkeep this way:
I went to the "upkeep" page.
I introduced a code
I pressed the "search" button.
Then I press the "cancel" button and I get this error:
SEVERE: JDBC rollback failed
com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: Can't call rollback when autocommit=true
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:888)
at com.mysql.jdbc.Connection.rollback(Connection.java:5257)
at com.sun.gjc.spi.base.ConnectionHolder.rollback(ConnectionHolder.java:630)
at org.hibernate.transaction.JDBCTransaction.rollbackAndResetAutoCommit(JDBCTransaction.java:183)
at org.hibernate.transaction.JDBCTransaction.rollback(JDBCTransaction.java:162)
If I use the "upkeep" that way:
SEVERE: JDBC commit failed
java.sql.SQLException: Can't call commit when autocommit=true
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:914)
at com.mysql.jdbc.Connection.commit(Connection.java:2275)
at com.sun.gjc.spi.base.ConnectionHolder.commit(ConnectionHolder.java:244)
at org.hibernate.transaction.JDBCTransaction.commitAndResetAutoCommit(JDBCTransaction.java:139)
As I read both errors I understand that transactions aren't working properly with the pool. I think it's a problem of the connection pool or a hibernate configuration issue. I have made a search over the net and I have tried to change some configuration options of the connection pool without luck.
Why is this happening?
Try to set autocommit mode to false in hibernate configuration
<property name="connection.autocommit">false</property>