14 Replies Latest reply on Sep 3, 2009 3:57 PM by rhauch

    I think I'm almost there - JPA and Oracle

    meetoblivion

      So, finally got the app built and am running some tests on the test server.

      I have a datasource configured like this:

      <?xml version="1.0" encoding="UTF-8"?>
      
      <!DOCTYPE datasources
       PUBLIC "-//JBoss//DTD JBOSS JCA Config 1.5//EN"
       "http://www.jboss.org/j2ee/dtd/jboss-ds_1_5.dtd">
      
      <datasources>
       <local-tx-datasource>
       <jndi-name>jdbc/JCRDS</jndi-name>
       <use-java-context>false</use-java-context>
       <connection-url>jdbc:oracle:thin:@connection descriptor...</connection-url>
       <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
       <user-name>myuser</user-name>
       <password>mypw</password>
       </local-tx-datasource>
      </datasources>


      In my code, I'm programmatically creating the repository:

      config = new JcrConfiguration();
       RepositorySourceDefinition<JcrConfiguration> configSource = config.repositorySource("MySource").usingClass(JpaSource.class);
       config.repository("MyRepo").setSource("MySource");
       Properties p = new Properties();
       p.setProperty("dialect", "org.hibernate.dialect.Oracle10gDialect");
       p.setProperty("dataSourceJndiName", "jdbc/JCRDS");
       p.setProperty("nameOfDefaultWorkspace", "MyRepo");
       Set<Object> keys = p.keySet();
       for(Object key : keys) {
       configSource.setProperty(key.toString(), p.getProperty(key.toString()));
       }


      i'm able to verify the connection using sqlplus. however, I am getting invalid username/password from dna. i have other apps pointing to the same db, but different users, so i know in general it is able to connect. any ideas?

        • 1. Re: I think I'm almost there - JPA and Oracle
          rhauch

          So you're configuring the JPA repository source (e.g., the JpaSource instance) to look up the DataSource object in JNDI. The JpaSource class will always perform this lookup if the "dataSourceJndiName" is provided, and if it cannot find it in JNDI it should log an error message of the form "Repository source {0} unable to find DataSource in JNDI at {1}". Right now, the JpaSource does not propagate this exception, but instead attempts to establish a connection using the other properties. Because you haven't set the other properties, it's unable to do this, and ultimately that exception is thrown.

          Can you look for this log message? If there is a message, the JNDI lookup isn't working, and the message should include the exception caught when trying to do the JNDI lookup.

          Also, simply logging the error may not be the best approach; perhaps a RepositorySourceException should be thrown. Thoughts?

          • 2. Re: I think I'm almost there - JPA and Oracle
            meetoblivion

            No, I'm not seeing that message when I deploy. Here are the few messages right before, mostly from hibernate. Currently, I'm set to INFO on org.jboss logger, do I need to make it DEBUG?

            2009-09-02 05:09:48,657 INFO [org.hibernate.cfg.AnnotationBinder] (ResourceContainer.invoker.nonDaemon-1) Binding entity from annotated class: org.jboss.dna.connector.store.jpa.util.StoreOptionEntity
            2009-09-02 05:09:48,657 INFO [org.hibernate.cfg.annotations.QueryBinder] (ResourceContainer.invoker.nonDaemon-1) Binding Named query: StoreOptionEntity.findAll => SELECT option FROM DNA_OPTIONS AS option
            2009-09-02 05:09:48,657 INFO [org.hibernate.cfg.annotations.EntityBinder] (ResourceContainer.invoker.nonDaemon-1) Bind entity org.jboss.dna.connector.store.jpa.util.StoreOptionEntity on table DNA_OPTIONS
            2009-09-02 05:09:48,673 INFO [org.hibernate.cfg.search.HibernateSearchEventListenerRegister] (ResourceContainer.invoker.nonDaemon-1) Unable to find org.hibernate.search.event.FullTextIndexEventListener on the classpath. Hibernate Search is not enabled.
            2009-09-02 05:09:48,674 INFO [org.hibernate.connection.ConnectionProviderFactory] (ResourceContainer.invoker.nonDaemon-1) Initializing connection provider: org.hibernate.ejb.connection.InjectedDataSourceConnectionProvider
            2009-09-02 05:09:48,675 INFO [org.hibernate.ejb.connection.InjectedDataSourceConnectionProvider] (ResourceContainer.invoker.nonDaemon-1) Using provided datasource
            2009-09-02 05:09:48,752 WARN [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] (ResourceContainer.invoker.nonDaemon-1) Throwable while attempting to get a new connection: null
            org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: ORA-01017: invalid username/password; logon denied
            


            • 3. Re: I think I'm almost there - JPA and Oracle
              meetoblivion

              and from the other side, i added properties necessary to have hibernate build the connection itself. i get java.lang.ClassNotFoundException: org.hibernate.connection.C3P0ConnectionProvider, which probably means I'm missing a jar somewhere. where does this class come from? mind you, i am using jboss 5.1.

              • 4. Re: I think I'm almost there - JPA and Oracle
                meetoblivion

                and just to ensure i'm not crazy, I just did this to ensure the DS was being bound correctly:

                sudo rm jcr-ds.xml
                05:28:58,112 INFO [ConnectionFactoryBindingService] Unbound ConnectionManager 'jboss.jca:service=DataSourceBinding,name=jdbc/JCRDS' from JNDI name 'jdbc/JCRDS'
                sudo view jcr-ds.xml
                05:29:24,385 INFO [WrapperDataSourceService] Bound ConnectionManager 'jboss.jca:service=DataSourceBinding,name=jdbc/JCRDS' to JNDI name 'jdbc/JCRDS'
                


                • 5. Re: I think I'm almost there - JPA and Oracle
                  meetoblivion

                  Ok ok figured out the issue. The connection identifier that looked valid wasn't. Sorry for the issues.

                  One last question, oracle doesn't like the generated hibernate tables. is there a default schema export file we could use?

                  • 6. Re: I think I'm almost there - JPA and Oracle
                    bcarothers

                    The JPA annotations themselves are used (by Hibernate) to generate the DDL. This assumes that the db user has appropriate permissions to execute DDL statements, which a bad practice for a production install anyway...

                    When you say that Oracle "doesn't like" the generated tables, what kind of exception are you getting? We haven't extracted the JPA annotations into a DDL file yet.

                    • 7. Re: I think I'm almost there - JPA and Oracle
                      meetoblivion

                      it's a common hibernate issue i've seen on a lot of things i've done w/ hibernate +oracle, and I can only assume most people just don't use hibernate w/ auto ddl. for example, if you annotate a class as @Id and @Column(unique=true) (for example, in StoreOptionEntity.name) the resulting table definition defines both a unique index and a PK, which oracle isn't happy about because you can't have both indexes on the same column.

                      i'll just manually go through and add each table as they come up.

                      • 8. Re: I think I'm almost there - JPA and Oracle
                        meetoblivion

                        Hmm, that looks like it's not going to work as nicely as Ihad hoped. It seems like you guys are doing a create-drop generation, which means that even in production it's going to drop the tables if the app server restarts :-(

                        I can't seem to find the code where you guys are doing that though...

                        • 9. Re: I think I'm almost there - JPA and Oracle
                          rhauch

                          All of the Hibernate configuration is done in the JpaSource.getConnection() method (roughly line 910). Hibernate uses the "hibernate.hbm2ddl.auto" method to set this, and we're not setting this so we're using the default. Unfortunately, I've seen several conflicting (albeit non-official) explanations of the default value, so ATM I'm not sure what the default value is.

                          You should be able to set this to "validate" and have it leave the database alone. I will log an issue so that this value is settable via a property, and I'll log another issue about generating the DDL (or making it available in the code).

                          • 10. Re: I think I'm almost there - JPA and Oracle
                            meetoblivion

                            I have a patch if you'd like. Seems like a pretty important feature. No unit tests though.

                            @@ -123,6 +123,7 @@
                             protected static final String DEFAULT_WORKSPACE = "defaultWorkspace";
                             protected static final String PREDEFINED_WORKSPACE_NAMES = "predefinedWorkspaceNames";
                             protected static final String ALLOW_CREATING_WORKSPACES = "allowCreatingWorkspaces";
                            + protected static final String DDL_GEN = "ddlAutoGeneration";
                            
                             /**
                             * This source supports events.
                            @@ -186,6 +187,7 @@
                             private volatile String url;
                             private volatile String driverClassName;
                             private volatile String driverClassloaderName;
                            + private volatile String ddlAutoGeneration;
                             private volatile String rootNodeUuid = DEFAULT_ROOT_NODE_UUID;
                             private volatile int maximumConnectionsInPool = DEFAULT_MAXIMUM_CONNECTIONS_IN_POOL;
                             private volatile int minimumConnectionsInPool = DEFAULT_MINIMUM_CONNECTIONS_IN_POOL;
                            @@ -215,6 +217,24 @@
                             private transient RepositoryContext repositoryContext;
                             private transient UUID rootUuid = UUID.fromString(rootNodeUuid);
                            
                            + /**
                            + * Gets the ddlAutoGeneration Value
                            + */
                            + public String getDdlAutoGeneration() {
                            + return ddlAutoGeneration;
                            + }
                            +
                            + /**
                            + * Set the ddlAutoGeneration
                            + */
                            + public synchronized void setDdlAutoGeneration(String ddlAutoGeneration) {
                            + if (ddlAutoGeneration != null) {
                            + ddlAutoGeneration = ddlAutoGeneration.trim();
                            + if (ddlAutoGeneration.length() == 0) ddlAutoGeneration = null;
                            + }
                            + this.ddlAutoGeneration = ddlAutoGeneration;
                            + }
                            +
                             /**
                             * {@inheritDoc}
                             *
                            @@ -741,6 +761,7 @@
                             ref.add(new StringRefAddr(USERNAME, getUsername()));
                             ref.add(new StringRefAddr(PASSWORD, getPassword()));
                             ref.add(new StringRefAddr(URL, getUrl()));
                            + ref.add(new StringRefAddr(DDL_GEN, getDdlAutoGeneration()));
                             ref.add(new StringRefAddr(DRIVER_CLASS_NAME, getDriverClassName()));
                             ref.add(new StringRefAddr(DRIVER_CLASSLOADER_NAME, getDriverClassloaderName()));
                             ref.add(new StringRefAddr(MAXIMUM_CONNECTIONS_IN_POOL, Integer.toString(getMaximumConnectionsInPool())));
                            @@ -796,6 +817,7 @@
                             String password = values.get(PASSWORD);
                             String url = values.get(URL);
                             String driverClassName = values.get(DRIVER_CLASS_NAME);
                            + String ddlAutoGeneration = values.get(DDL_GEN);
                             String driverClassloaderName = values.get(DRIVER_CLASSLOADER_NAME);
                             String maxConnectionsInPool = values.get(MAXIMUM_CONNECTIONS_IN_POOL);
                             String minConnectionsInPool = values.get(MINIMUM_CONNECTIONS_IN_POOL);
                            @@ -829,6 +851,7 @@
                             if (password != null) source.setPassword(password);
                             if (url != null) source.setUrl(url);
                             if (driverClassName != null) source.setDriverClassName(driverClassName);
                            + if (ddlAutoGeneration != null) source.setDdlAutoGeneration(ddlAutoGeneration);
                             if (driverClassloaderName != null) source.setDriverClassloaderName(driverClassloaderName);
                             if (maxConnectionsInPool != null) source.setMaximumConnectionsInPool(Integer.parseInt(maxConnectionsInPool));
                             if (minConnectionsInPool != null) source.setMinimumConnectionsInPool(Integer.parseInt(minConnectionsInPool));
                            @@ -902,6 +925,7 @@
                             }
                             }
                             // Set the connection properties ...
                            + setProperty(configurator, "hibernate.hbm2ddl.auto", this.ddlAutoGeneration);
                             setProperty(configurator, "hibernate.dialect", this.dialect);
                             setProperty(configurator, "hibernate.connection.driver_class", this.driverClassName);
                             setProperty(configurator, "hibernate.connection.username", this.username);
                            


                            • 11. Re: I think I'm almost there - JPA and Oracle
                              rhauch

                              Fantastic! I just logged https://jira.jboss.org/jira/browse/DNA-510 and referenced your patch. The issue is targeted to be fixed before 0.6 - I'll probably work on it today.

                              Thanks!

                              • 12. Re: I think I'm almost there - JPA and Oracle
                                meetoblivion

                                And if it helps, I've just deployed a few times w/ the setting my app to "validate" and it's working good.

                                • 13. Re: I think I'm almost there - JPA and Oracle

                                  Any word yet when version 0.6 will be released yet?

                                  • 14. Re: I think I'm almost there - JPA and Oracle
                                    rhauch

                                    There are just a few issues outstanding; see [1] for the complete report. I hope to have them all wrapped up (or pushed) today, with a release following in the next few days.

                                    [1] https://jira.jboss.org/jira/secure/IssueNavigator.jspa?reset=true&mode=hide&sorter/order=DESC&sorter/field=priority&resolution=-1&pid=12310520&fixfor=12313589