4 Replies Latest reply: Apr 9, 2013 8:34 AM by 939520 RSS

    Database connectivity getting failed using JPA data source

    934794

      Using JDBC, when tried to connect to oracle database it works as expected.

      +<property name="hibernate.archive.autodetection" value="class, hbm"/>
      <property name="hibernate.connection.driver_class" value="oracle.jdbc.driver.OracleDriver"/>
      <property name="hibernate.connection.url" value="AppDataBaseConnectionValues"/>
      <property name="hibernate.connection.username" value="AppUserName"/>
      <property name="hibernate.connection.password" value="AppPassword"/>+

      Using datasource, instead of configuring the JDBC attributes (as above) in JPA persistence.xml_ --> initially it gets connect to the database but after executing around 15 queries connection gets lost. And throws the following highlighted in bold below

      +<jta-data-source>ReformDataSource</jta-data-source>+

      Error:
      - exception clearing maxRows/queryTimeout
      java.sql.SQLException: Statement has already been closed
      com.pg.giss.common.GissException: could not execute query
      Caused by: java.sql.SQLException: Result set already closed

      TIA.

        • 1. Re: Database connectivity getting failed using JPA data source
          gimbal2
          There is no such thing as a "JPA datasource" - what is configured is to use a datasource that is already defined in your server/container. So whatever issue you have, it is with the configuration of that datasource, not JPA.
          • 2. Re: Database connectivity getting failed using JPA data source
            939520
            I can't directly help you with your problem, but I found that Netbeans wizards help to initially get you set up quickly. I used them for setting up the initial entity beans (JPA) against a database (not a complete solution) and they generate a basic JSF front end to go with it. I see the wizards also supports hibernate creation although I haven't tried it. If you aren't set on using Hibernate, you may want to consider using JPA which is newer.

            Here's a quick rundown for hibernate:
            Install Netbeans 7.3
            file->new project->maven->web application
            Highlight your new project icon
            file->new file->hibernate->Hibernate Configuration Wizard (or one of the other wizards)

            If you want to see what JPA annotations/JSF has to offer here's a quick rundown:
            Install Netbeans 7.3
            file->new project->maven->web application
            Highlight your new project icon
            file->new file->persistence->Entity classes from database
            file->new file->persistence->JSF pages from Entity classes

            Note the above is just a suggestion. You can try it or reject it.
            Disclaimer: http://www.kith.org/jimmosk/disclaimer2.html
            • 3. Re: Database connectivity getting failed using JPA data source
              934794
              thanks for replying.

              I understand that Datasource is a seperate entity and not related to JPA.
              i mean to say is i am using JPA using datasource.
              So in weblogic i have created a Generic datasource without global XA support (since i am interacting with single DB.)

              This is my complete persistence.xml_

              <?xml version="1.0" encoding="UTF-8"?>
              <persistence 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"
              version="1.0">

              <persistence-unit name="GissPersistenceBrokerFactory" transaction-type="RESOURCE_LOCAL">
              <provider>org.hibernate.ejb.HibernatePersistence</provider>

              +*<non-jta-data-source>jdbc/ReformDataSource</non-jta-data-source>*+
              <properties>
              <property name="hibernate.show_sql" value="true"/> <!-- ${hibernate.show_sql} -->
              <property name="hibernate.use_sql_comments" value="true"/> <!-- ${hibernate.use_sql_comments} -->
              <property name="hibernate.cache.use_second_level_cache" value="true"/>
              <property name="hibernate.cache.use_query_cache" value="true"/>
              <property name="hibernate.cache.use_minimal_puts" value="true"/>
              <property name="hibernate.cache.provider_class" value="org.hibernate.cache.EhCacheProvider"/>
              <property name="hibernate.cache.provider_configuration_file_resource_path" value="META-INF/ehcache.xml"/>
              <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect"/>
              <property name="hibernate.max_fetch_depth" value="3"/>
              </properties>
              </persistence-unit>
              </persistence>

              and this is the code to create EntityManager_

              public class GissPersistenceBrokerFactory implements GissPersistenceBroker {
                   private static final HibernateEntityManagerFactory entityManagerFactory;
                   private static final ThreadLocal<EntityManager> threadLocal;
                   private static final GissLogger LOGGER = GissLogger.getLogger(GissPersistenceBrokerFactory.class);

                   static {
                        entityManagerFactory = (HibernateEntityManagerFactory) Persistence
                                  .createEntityManagerFactory("GissPersistenceBrokerFactory");
                        threadLocal = new ThreadLocal<EntityManager>();
                        threadLocal.set(entityManagerFactory.createEntityManager());
                   }

                   public static GissPersistenceBrokerFactory getPersistenceBroker() {
                        return new GissPersistenceBrokerFactory();
                   }

                   public EntityManager getDelegate() {
                        return getEntityManager();
                   }

                   public static EntityManager getEntityManager() {
                        EntityManager manager = threadLocal.get();
                        if (manager == null || !manager.isOpen()) {
                             manager = entityManagerFactory.createEntityManager();
                             threadLocal.set(manager);
                        }
                        return manager;
                   }

                   public static void closeEntityManager() {
                        EntityManager em = threadLocal.get();
                        threadLocal.set(null);
                        if (em != null)
                             em.close();
                   }

                   
                   public Session getSession() {
                        System.out.println("2");
                        return ((EntityManagerImpl) getDelegate()).getSession();
                   }

                   /**
                   * Releases Resources held by EntityManager.
                   */
                   public void close() {
                        release();
                   }

                   /**
                   * Releases Resources held by EntityManager.
                   */
                   public static void release() {
                        EntityManager result = threadLocal.get();
                        if (result != null) {
                             result.close();
                             threadLocal.set(null);
                        }
                   }
                   
              }

              and this is how i am accessign it in the code_
                   
              public static Long getByPGID(String ThePGID) throws GissException {
                   GissPersistenceBroker broker = GissPersistenceBrokerFactory.getPersistenceBroker();
                   try {
                        Criteria criteria = broker.getSession().createCriteria(UserPOJO.class);
                        criteria.add(Restrictions.eq("pgId", ThePGID));
                        List list1 = criteria.list();
                        List keys = new ArrayList();
                        for (Object obj : list1) {
                             UserPOJO userpojo = (UserPOJO) obj;
                             keys.add(new Long(userpojo.getSqloid()));
                        }     
                        
                        return (Long) keys.get(0);
                        } catch (Exception pe) {
                             // pe.printStackTrace();
                             throw new GissException(pe);
                        } finally {
                             GissPersistenceBrokerFactory.release();
                        }
              }
              • 4. Re: Database connectivity getting failed using JPA data source
                939520
                Sorry, offhand I don't see what's causing the problem. I see you are using threading. Perhaps it might help to get it to work without threading first (if possible). If it works, add threading. If it fails then, it may indicate it has something to do with threading.

                I suggest asking if you really need threading (I don't know since I'm not familar with your requirements). If so, perhaps you can find a different example on-line that you can try out. Perhaps you can get that one to work.