14 Replies Latest reply on Mar 15, 2010 3:58 AM by michael.gloeckner

    howto setup jpa using oracle 10g unicode nvarchar2

    michael.gloeckner

      Hello folks,

       

      i like to setup jpa to use oracles nvarchar2 datatyp for string columns. Im using ejb3.0 and oracle 10.2.0.4.0

       

       

      I already tried the suggestions made in http://community.jboss.org/message/489679#489679, but the only think which worked out was the following annotation added to the specific String variables.

       

      @Column(columnDefinition="nvarchar2")
      
      

       

      But this would force me to implement the source code in a oracle database depended way.

      Is there any standard way to customize the datasource or the persistence.xml so the jpa framework is switching automatically to nvarchar2.

       

      my persistence.xml looks like:

       

         <persistence-unit name="MyPersistentUnit" transaction-type="JTA">
             <jta-data-source>java:/MyDS</jta-data-source>
             <properties>
                  <property name="hibernate.hbm2ddl.auto" value="create"/> 
                   <property name="hibernate.show_sql" value="true"/>
                   <property name="hibernate.dialect" value="org.hibernate.dialect.OracleDialect"/> 
                  <property name="jboss.entity.manager.factory.jndi.name" value="java:/MyEjbTestEntityManagerFactory"/>
             </properties>
         </persistence-unit>
      

      My ds.xml file looks like:

       <xa-datasource>
          <jndi-name>MyDS</jndi-name>
          <track-connection-by-tx/>
          <isSameRM-override-value>false</isSameRM-override-value>
          <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
          <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
          <xa-datasource-property name="URL">jdbc:oracle:thin:@localhost:1521:XE</xa-datasource-property>
          <xa-datasource-property name="User">user</xa-datasource-property>
          <xa-datasource-property name="Password">password</xa-datasource-property>
          <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
             <no-tx-separate-pools/>
        </xa-datasource>
      

       

      Thanks in advanced,

      Micha

        • 1. Re: howto setup jpa using oracle 10g unicode nvarchar2
          wolfgangknauf

          Hi,

           

          there is a file "JBOSS_HOME\server\default\standardjbosscmp-jdbc.xml", which contains column type mappings.

           

          If you change one of the "Oracle" type mappings to "nvarchar2" and activate the new type mapping in your "..-ds.xml" file, this should work.

           

          Snippet from "hsqldb-ds.xml":

          {code:xml}<!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->

          <metadata>

            <type-mapping>Hypersonic SQL</type-mapping>

          </metadata>

          {code}

           

          Hope this works (I never did something like this) ;-)

           

          Wolfgang

          • 2. Re: howto setup jpa using oracle 10g unicode nvarchar2
            michael.gloeckner

            Hi Wolfgang,

             

            thanks for the quick answer.

             

            I tried your suggestions and modify the standardjbosscmp-jdbc.xml. Unfortunatily these changes have been ignored.

             

            I also updated my ojdbc14.jar to version 10.2.0.4.0 and changed my datasource from an  <xa-datasource>  to a   <local-tx-datasource>.

             

            Additional i created an additional  type-mapping Oracle9iUNICODE and it looks like it got loaded.


            2010-02-25 11:47:31,972 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.metadata.MetaDataLibrary] Starting jboss.jdbc:service=metadata
            2010-02-25 11:47:32,050 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.metadata.MetaDataLibrary] Loading standardjbosscmp-jdbc.xml : file:/D:/jboss-portal-2.7.2/server/profile2/conf/standardjbosscmp-jdbc.xml
            ..
            2010-02-25 11:47:32,378 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.metadata.MetaDataLibrary] added type-mapping: Oracle9i
            2010-02-25 11:47:32,378 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.metadata.MetaDataLibrary] added type-mapping: Oracle9iUNICODE
            2010-02-25 11:47:32,409 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.metadata.MetaDataLibrary] added type-mapping: Oracle8
            2010-02-25 11:47:32,425 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.metadata.MetaDataLibrary] added type-mapping: Oracle7
            ..
            2010-02-25 11:47:32,503 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.metadata.MetaDataLibrary] Started jboss.jdbc:service=metadata
            

             

            However, if my application got deployed jpa still creates varchar2(255) for all string fields.

             

            Do you know if there are any issues with jboss 4.2.3GA?

             

            Any help would be appreciated!

             

            Cheers,

            Micha

            • 3. Re: howto setup jpa using oracle 10g unicode nvarchar2
              wolfgangknauf

              Hi,

               

              did you check the log output? Which "create table" statements are called?

               

              See here:

              {url}http://docs.jboss.org/hibernate/core/3.3/reference/en/html/session-configuration.html#configuration-logging{url}

               

              You probably have to switch the category "org.hibernate.tool.hbm2ddl" to "DEBUG" (which means that you have to switch the default appender from INFO to DEBUG).

               

              Hope this helps

               

              Wolfgang

              • 4. Re: howto setup jpa using oracle 10g unicode nvarchar2
                michael.gloeckner

                Hi Wolfgang,

                 

                i checked the log and haven´t found any errors:The create table log entries look like the following one.

                 

                DEBUG [org.hibernate.tool.hbm2ddl.SchemaExport] create table person (GUID varchar2(255) not null, name varchar2(255), primary key (GUID))

                 

                It just looks like that the standardjbosscmp-jdbc.xml got ignored.

                 

                Any further help would be appreciated!

                 

                Ciao,

                Micha

                • 5. Re: howto setup jpa using oracle 10g unicode nvarchar2
                  wolfgangknauf

                  Hi,

                   

                  just to check: did you also modify your "ds.xml" and point it to your new type mapping (see my first reply):

                   

                  {code:xml}<xa-datasource>

                    ...
                    <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional)-->
                    <metadata>       

                      <type-mapping>Oracle9iUNICODE</type-mapping>
                    </metadata>
                  </xa-datasource>{code}

                   

                  Best regards

                   

                  Wolfgang

                  • 6. Re: howto setup jpa using oracle 10g unicode nvarchar2
                    michael.gloeckner

                    Hi Wolfgang,

                     

                    yes I adapted the ds file to my custom type-mapping. But it has been ignored. I also changed it to some wrong type-mapping "Oracle9ixyz" which also has been ignored.

                     

                    Cheers,

                    Micha

                    • 7. Re: howto setup jpa using oracle 10g unicode nvarchar2
                      jaikiran

                      Sorry to jump in late, on this one. The type-mapping is used for the outdated EJB2.x Container Managed Persistence (CMP). It is not used JPA entities.

                      • 8. Re: howto setup jpa using oracle 10g unicode nvarchar2
                        michael.gloeckner

                        Hi Jaikiran,


                        thanks for this information. Does it mean that there is no configuration way to setup the jpa datatypes? Do i have to annotate each String attribute with the following annotation?

                         

                        @Column(columnDefinition="nvarchar2")

                         

                        Cheers,

                        Micha

                        • 9. Re: howto setup jpa using oracle 10g unicode nvarchar2
                          jaikiran

                          Does setting this property in -ds.xml help:

                           

                          <xa-datasource-property name="oracle.jdbc.defaultNChar">true</xa-datasource-property>
                          
                          • 10. Re: howto setup jpa using oracle 10g unicode nvarchar2
                            michael.gloeckner

                            Hi,

                             

                            if i put

                            <xa-datasource-property name="oracle.jdbc.defaultNChar">true</xa-datasource-property>

                             

                            to my ds file I get the following exception.

                             

                            Caused by: java.lang.NoSuchMethodException: oracle.jdbc.xa.client.OracleXADataSource.setOracle.jdbc.defaultNChar(java.lang.String)

                            • 11. Re: howto setup jpa using oracle 10g unicode nvarchar2
                              jaikiran

                              A couple of other options you could try:

                               

                              1) Try setting this in the -ds.xml:

                               

                              <connection-property name = "oracle.jdbc.defaultNChar">true</connection-property>

                               

                              I am not sure whether that will work (I have seen some other users complain that it doesn't work)

                               

                              2) Pass the oracle.jdbc.defaultNChar as a Java System property through your run.conf (or run.bat for Windows)

                               

                              -Doracle.jdbc.defaultNChar=true
                              
                              

                               

                              Add that to the JAVA_OPTS section

                               

                              P.S: Make sure that you are using the correct Oracle JDBC driver version. From what found in a google search, Oracle 10 driver is required for this property to be recognized.

                              • 12. Re: howto setup jpa using oracle 10g unicode nvarchar2
                                michael.gloeckner

                                The first option has no effect at all. There are no errors and the database shows me String mapped to a varchar2 instead of nvarchar2.

                                 

                                I tried the second suggestion, which changed nothing to my new schema. But for an already existing schema, which included some data,

                                it shows up the following exception:

                                Caused by: java.sql.SQLException: ORA-12704: character set mismatch

                                 

                                So it looks like it has some influence on the database, but not on my new schema.

                                 

                                Cheers,

                                Micha

                                • 13. Re: howto setup jpa using oracle 10g unicode nvarchar2
                                  jaikiran
                                  I am out of ideas Hibernate forums https://forum.hibernate.org/viewforum.php?f=1 would be a better place to ask this
                                  • 14. Re: howto setup jpa using oracle 10g unicode nvarchar2
                                    michael.gloeckner

                                    Hi Jaikiran,

                                     

                                    thanks anyway. I found a first solution for my purposes. Im using the orm.xml file to make an attribute overwriting. This allows me to set the database dependend informations into the orm.xml file instead of make annotations directly to the string attributes in java code. The handicap of this solution is, that you have to do the overwriting for every single string attribute where you want to use nvarchar2.


                                    <?xml version="1.0" encoding="UTF-8"?>
                                    
                                    <entity-mappings
                                      xmlns="http://java.sun.com/xml/ns/persistence/orm"
                                      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                                      xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm_1_0.xsd 1_0.xsd"
                                      version="1.0">
                                    
                                        <package>com.factory.model</package>
                                        <entity class="Customer" access="FIELD" metadata-complete="false">
                                             <attribute-override name="description">
                                                  <column column-definition="nvarchar2(255)"/>
                                             </attribute-override>
                                        </entity>
                                    </entity-mappings>
                                    

                                    Cheers,

                                    Micha