Announcement Announcement Module
Collapse
No announcement yet.
Database pooling - Spring, Hibernate Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Database pooling - Spring, Hibernate

    Hi.

    I'm new using hibernate, spring and struts.
    I have a small aplication that use all this 3 frameworks but know i need to implement this using database pooling.

    To do so i change my aplicationContext.xml like this:
    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
    "http://www.springframework.org/dtd/spring-beans.dtd">
    <beans>

    <!-- datasource (database connection) -->
    <!-- poolable connection -->
    <bean id="poolableConnectionFactory" class="org.apache.commons.dbcp.PoolableConnectionF actory">
    <constructor-arg index="0"><ref bean="dsConnectionFactory"/></constructor-arg>
    <constructor-arg index="1"><ref bean="pool"/></constructor-arg>
    <constructor-arg index="2"><null/></constructor-arg>
    <constructor-arg index="3"><null/></constructor-arg>
    <constructor-arg index="4"><value>false</value></constructor-arg>
    <constructor-arg index="5"><value>true</value></constructor-arg>
    </bean>


    <bean id="dsConnectionFactory" class="org.apache.commons.dbcp.DataSourceConnectio nFactory">
    <constructor-arg><ref bean="cpmsDS"/></constructor-arg>
    </bean>

    <bean id="pool" class="org.apache.commons.pool.impl.GenericObjectP ool">
    <property name="minEvictableIdleTimeMillis"><value>300000</value></property>
    <property name="timeBetweenEvictionRunsMillis"><value>60000</value></property>
    </bean>


    <bean id="cpmsDS" class="org.springframework.jdbc.datasource.DriverM anagerDataSource">
    <property name="driverClassName"><value>com.mysql.jdbc.Drive r</value></property>
    <property name="url"><value>jdbc:mysql://localhost:3306/bi</value></property>
    <property name="username"><value>root</value></property>
    <property name="password"><value>1234</value></property>
    </bean>

    <!-- Hibernate SessionFactory -->
    <bean id="pooledDS" class="org.apache.commons.dbcp.PoolingDataSource" depends-on="poolableConnectionFactory">
    <constructor-arg><ref bean="pool"/></constructor-arg>
    </bean>

    <!-- References all OR mapping files. -->
    <bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSes sionFactoryBean">
    <property name="dataSource"><ref local="pooledDS"/></property>
    <property name="mappingResources">
    <list>
    <value>form/action/Person.hbm.xml</value>
    </list>
    </property>

    <!-- Set the type of database; changing this one property will port this to Oracle, MS SQL etc. -->
    <property name="hibernateProperties">
    <props>
    <prop key="hibernate.dialect">org.hibernate.dialect.MySQ LDialect</prop>
    </props>
    </property>
    </bean>

    <!-- Transaction manager for a single Hibernate SessionFactory -->
    <bean id="transactionManager" class="org.springframework.orm.hibernate3.Hibernat eTransactionManager">
    <property name="sessionFactory"><ref local="sessionFactory"/></property>
    </bean>

    <!-- parent template for the transaction proxy beans -->
    <bean id="txProxyTemplate" abstract="true" class="org.springframework.transaction.interceptor .TransactionProxyFactoryBean">
    <property name="transactionManager"><ref local="transactionManager"/></property>
    <property name="transactionAttributes">
    <props>
    <prop key="get*">PROPAGATION_REQUIRED,readOnly</prop>
    <prop key="add*">PROPAGATION_REQUIRED</prop>
    <prop key="delete*">PROPAGATION_REQUIRED</prop>
    <prop key="update*">PROPAGATION_REQUIRED</prop>
    </props>
    </property>
    </bean>


    <!-- personMangerDao data access object: Hibernate implementation -->
    <bean id="personDAO" class="form.dao.hibernate.PersonDAOImpl">
    <property name="sessionFactory"><ref local="sessionFactory"/></property>
    </bean>

    <!-- Transactional proxy for jobManagerDao data access object. -->
    <bean id="personManagerDAO" parent="txProxyTemplate">
    <property name="target"><ref local="personDAO"/></property>
    </bean>

    <bean id="personAction" class="form.action.PersonAction">
    <constructor-arg ref="personDAO"/>
    </bean>
    </beans>


    The personAction.java it's here:

    package form.action;

    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.List;

    import javax.naming.InitialContext;
    import javax.naming.NamingException;
    import javax.sql.DataSource;

    import form.action.Person;
    import form.dao.hibernate.PersonDAO;

    import com.opensymphony.xwork2.ActionSupport;



    @SuppressWarnings("serial")
    public class PersonAction extends ActionSupport {

    List persons;
    PersonDAO service;
    Person person;
    private int id;


    String message;


    public String execute(){
    this.persons = service.getPersons();
    return SUCCESS;
    }


    public String save() {

    if (person.getAge() != null && person.getFirstName() != null
    && person.getLastName() != null) {

    person.setAgeDays(Integer.parseInt(person.getAge() ) * 365);
    System.out.println("SAVE PERSON");
    this.service.savePerson(person);

    //this.setMessage("Sucesso! Esta mensagem foi gerada na Action");
    return execute() ;
    } else {
    this.setMessage("Erro! Esta mensagem foi gerada na Action");
    return ERROR;
    }


    }



    public String remove(){
    this.service.removePerson(id);
    return execute();
    }


    public String edit(){
    setPerson(service.getPerson(id));
    return SUCCESS;
    }

    public String update(){

    if (person.getAge() != null && person.getFirstName() != null && person.getLastName() != null)
    person.setAgeDays(Integer.parseInt(person.getAge() ) * 365);

    person.setId(person.getId());

    System.out.println(person.id);
    System.out.println(person.firstName);
    System.out.println(person.lastName);
    System.out.println(person.age);
    System.out.println(person.ageDays);

    service.updatePerson(person);
    return execute();
    }

    public PersonAction(PersonDAO service){
    this.service = service;
    }


    /************************************************** *************************/


    public String getMessage() {
    return message;
    }

    public void setMessage(String message) {
    this.message = message;
    }


    public Person getPerson() {
    return person;
    }


    public void setPerson(Person person) {
    this.person = person;
    }


    public List<Person> getPersons() {
    return persons;
    }


    public void setPersons(List persons) {
    this.persons = persons;
    }


    public int getId() {
    return id;
    }


    public void setId(int id) {
    this.id = id;
    }

    }



    I had to the web.xml this lines, because i create a MySQL pooling in the Websphere Comunity Edition:

    <resource-ref>
    <res-ref-name>jdbc/MyDataSource</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
    <res-sharing-scope>Shareable</res-sharing-scope>
    </resource-ref>



    If you need more information please let me know.
    I need help fast as possible....
    Thx.

  • #2
    When posting code please use [ code][ /code] tags.

    Also you have a lot of stuff in there for your pooling. When you get the feeling that something is to complex it most of the time is. Also you configure a DataSource in WebSphere but implement pooling and a connection yourself?! Why???

    Code:
    <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
      <property name="jndiName" value="jdbc/MyDataSource"/>
      <property name="proxyInterface" value="javax.sql.DataSource"/>
    </bean>
    In your hibernate session factory use that datasource

    Code:
    <bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSes sionFactoryBean">
     <property name="dataSource" ref="dataSource"/>
    Furthermore remove all the obsolete stuff you have for connection pooling, you made it way to complex.

    Code:
    <bean id="poolableConnectionFactory" class="org.apache.commons.dbcp.PoolableConnectionF actory">
    <constructor-arg index="0"><ref bean="dsConnectionFactory"/></constructor-arg>
    <constructor-arg index="1"><ref bean="pool"/></constructor-arg>
    <constructor-arg index="2"><null/></constructor-arg>
    <constructor-arg index="3"><null/></constructor-arg>
    <constructor-arg index="4"><value>false</value></constructor-arg>
    <constructor-arg index="5"><value>true</value></constructor-arg>
    </bean>
    
    
    <bean id="dsConnectionFactory" class="org.apache.commons.dbcp.DataSourceConnectio nFactory">
    <constructor-arg><ref bean="cpmsDS"/></constructor-arg>
    </bean>
    
    <bean id="pool" class="org.apache.commons.pool.impl.GenericObjectP ool">
    <property name="minEvictableIdleTimeMillis"><value>300000</value></property>
    <property name="timeBetweenEvictionRunsMillis"><value>60000</value></property>
    </bean>
    
    
    <bean id="cpmsDS" class="org.springframework.jdbc.datasource.DriverM anagerDataSource">
    <property name="driverClassName"><value>com.mysql.jdbc.Drive r</value></property>
    <property name="url"><value>jdbc:mysql://localhost:3306/bi</value></property>
    <property name="username"><value>root</value></property>
    <property name="password"><value>1234</value></property>
    </bean>
    
    <!-- Hibernate SessionFactory -->
    <bean id="pooledDS" class="org.apache.commons.dbcp.PoolingDataSource" depends-on="poolableConnectionFactory">
    <constructor-arg><ref bean="pool"/></constructor-arg>
    </bean>
    The above can all be removed.

    Next time if you want to configure a Connection Pool you might want to check-out this.

    Next to that I suggest that you take a look at the samples and the reference guide.

    Comment


    • #3
      Thx for help.

      Soo my applicationContext.xml stays like this?

      Code:
      <?xml version="1.0" encoding="UTF-8"?>
      <!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
      "http://www.springframework.org/dtd/spring-beans.dtd">
      
      <beans>
       <bean id="dataSource"
              class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
              <property name="driverClassName" value="com.mysql.jdbc.Driver" />
              <property name="url" value="jdbc:mysql://localhost:3306/bi" />
              <property name="username" value="root" />
              <property name="password" value="1234" />   
              <property name="initialSize" value="1"/>
      		<property name="maxActive" value="1"/>
      		<property name="maxIdle" value="1"/>  
          </bean>
      
       <bean id="sessionFactory" 
       	class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
       	<property name="dataSource">
         		<ref bean="dataSource"/>
        	</property>
        	<property name="mappingResources">
         		<list>
          		<value>form/action/Person.hbm.xml</value>
         		</list>
        	</property>
        	<property name="hibernateProperties">
         		<props>
          	<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
          	<prop key="hibernate.show_sql">true</prop>
         		</props>
         		
        	</property>
       </bean>
       
       <bean id="hibernateTemplate" 
       	class="org.springframework.orm.hibernate3.HibernateTemplate" >
        	<property name="sessionFactory">
         		<ref local="sessionFactory"/>
        	</property>
       </bean>
      
      <bean id="personDAO" 
       	class="form.dao.hibernate.PersonDAOImpl">
      	<property name="hibernateTemplate">
      		<ref local="hibernateTemplate"/>
      	</property>
      </bean>
      
      <bean id="personAction" class="form.action.PersonAction">
       	<constructor-arg ref="personDAO" />
      </bean>
       
      </beans>
      This code works but i dont know if im really using a pooling....
      Should i change my PersonAction.java to get a connection from the pool, or this is enought?

      I'm using hibernate to persiste data. I need to change something?

      I add this to my web.xml (like websphere said to do):
      Code:
      resource-ref>
          <res-ref-name>jdbc/MyDataSource</res-ref-name>
          <res-type>javax.sql.DataSource</res-type>
          <res-auth>Container</res-auth>
          <res-sharing-scope>Shareable</res-sharing-scope>
      </resource-ref>

      Thx.

      Comment


      • #4
        It depends...

        If you want to use the datasource configured in websphere you need to change your datasource to the JndiObjectFactoryBean (check my first post). If you want Spring (and not your application server) to manage the pool for you you can leave it this way.

        With your current configuration you don't do anything with the datasource configured in websphere.

        Comment


        • #5
          Once again thx for your reply.

          My last applicationContext was working.
          Now i try like you said, use the JndiObjectFactoryBean.

          I think i understand what you said and here's my new bean "dataSource" file:

          Code:
          <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
          <property name="jndiName" value="java:comp/env/jdbc/MyDataSource"/>
          <property name="lookupOnStartup" value="false"/>
          <property name="cache" value="true"/>
          <property name="proxyInterface" value="javax.sql.DataSource"/>
          </bean>
          This works.

          Now what's better? Use the datasource from application server or Spring?


          I have another question related with this. I want to use log4j but i wanna write all logs to a database.
          I have the log4j.xml like this:

          Code:
          <?xml version="1.0" encoding="UTF-8" ?>
          <!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
          
          <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
          
              <appender name="jdbcAppender" class="org.apache.log4j.jdbc.JDBCAppender"> 
                  <param name="URL" value="jdbc:mysql://localhost:3306/bi" /> 
                  <param name="Driver" value="com.mysql.jdbc.Driver" /> 
                  <param name="User" value="root" /> 
                  <param name="Password" value="1234" /> 
                  <layout class="org.apache.log4j.PatternLayout"> 
                      <param name="ConversionPattern" 
                        value="INSERT INTO BlauLOG (log_date, log_level, location, message) VALUES ( '%d{ISO8601}','%p', '%C;%L', '%m' )" 
                      /> 
                  </layout> 
              </appender> 
          
          <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender"> 
                  <layout class="org.apache.log4j.PatternLayout"> 
                      <param name="ConversionPattern" 
                        value="%d{ISO8601} %p (%C;%L) %m%n" 
                      /> 
                  </layout> 
              </appender> 
          
              <logger name="logging.simple.jdbcLogger"> 
                  <level value="debug"/> 
                  <appender-ref ref="jdbcAppender"/> 
              </logger> 
          
              <root> 
                  <level value="error"/> 
                  <appender-ref ref="jdbcAppender"/> 
              </root> 
          
          </log4j:configuration>
          This is not has it should be right? My <appender name="jdbcAppender> configures the connection... It should use the applicationContext.xml definitions right?
          I need a bean to the log4j?
          Any idea about this?
          Thx for help.
          Last edited by l1nk; Jul 10th, 2007, 04:54 PM.

          Comment


          • #6
            Integrating log4j to use the datasource configured in spring isn't going to work I'm afraid. It might be possible to re-use the same datasource configured in your application server.

            Which is the best, well both work. If you need to share your datasource or want to decouple the datasource configuration from your spring configuration (maybe due to some security issues?!) then use the jndi datasource.

            Comment


            • #7
              Thx a lot for your help.

              Comment

              Working...
              X