1 Reply Latest reply on Nov 28, 2005 2:10 PM by Emmanuel Bernard

    Type Mappings for EJB3 Entity Beans in Postgre

    Shiung Yong Newbie

      How do we specify the type-mappings to use for an EJB3 entity bean, and where are the defaults located?

      The problem is, the default install of JBoss has the java.sql.Timestamp java class mapped to Timestamp in my Postgre installation, when I want it mapped to Timestamptz so it holds the timezone info as well.

      Here is my -ds.xml file in my deploy directory which sets up the database and the type mappings:

      <datasources>
       <local-tx-datasource>
       <jndi-name>MyDB</jndi-name>
       <connection-url>jdbc:postgresql://localhost:5432/mydb</connection-url>
       <driver-class>org.postgresql.Driver</driver-class>
       <user-name>user</user-name>
       <password>12345</password>
       <!-- sql to call when connection is created
       <new-connection-sql>some arbitrary sql</new-connection-sql>
       -->
      
       <!-- sql to call on an existing pooled connection when it is obtained from pool
       <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
       -->
      
       <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
       <metadata>
       <type-mapping>PostgreSQL 7.2</type-mapping>
       </metadata>
       </local-tx-datasource>
      
      </datasources>


      So far, I've tried modifying the two files standardjaws.xml and standardjbosscmp-jdbc.xml. However, changes to these files don't make any difference when I redeploy my entity bean. I tried copying the contents of both these files into jaws.xml and jbosscmp-jdbc.xml in the META-INF directory of the ejb3 archive containing the beans, but that doesn't work either.

      Here's the change I made to standardjbosscmp-jdbc.xml, which according to all the documentation I could find, should have worked.

      <type-mapping>
       <name>PostgreSQL 7.2</name>
       <row-locking-template>SELECT ?1 FROM ?2 WHERE ?3 ORDER BY ?4 FOR UPDATE</row-locking-template>
       <pk-constraint-template>CONSTRAINT ?1 PRIMARY KEY (?2)</pk-constraint-template>
       <fk-constraint-template>ALTER TABLE ?1 ADD CONSTRAINT ?2 FOREIGN KEY (?3) REFERENCES ?4 (?5)</fk-constraint-template>
       <auto-increment-template>?1</auto-increment-template>
       <alias-header-prefix>t</alias-header-prefix>
       <alias-header-suffix>_</alias-header-suffix>
       <alias-max-length>32</alias-max-length>
       <subquery-supported>true</subquery-supported>
       <true-mapping>TRUE</true-mapping>
       <false-mapping>FALSE</false-mapping>
      
       <function-mapping>
       <function-name>concat</function-name>
       <function-sql>(?1 || ?2)</function-sql>
       </function-mapping>
       <function-mapping>
       <function-name>substring</function-name>
       <function-sql>substring(?1 FROM ?2 FOR ?3)</function-sql>
       </function-mapping>
       <function-mapping>
       <function-name>lcase</function-name>
       <function-sql>lower(?1)</function-sql>
       </function-mapping>
       <function-mapping>
       <function-name>length</function-name>
       <function-sql>length(?1)</function-sql>
       </function-mapping>
       <function-mapping>
       <function-name>locate</function-name>
       <function-sql>(CASE position(?1 in substring(?2 from ?3)) WHEN 0 THEN 0 ELSE position(?1 in substring(?2 from ?3)) + ?3 - 1 END)</function-sql>
       </function-mapping>
       <function-mapping>
       <function-name>abs</function-name>
       <function-sql>abs(?1)</function-sql>
       </function-mapping>
       <function-mapping>
       <function-name>sqrt</function-name>
       <function-sql>sqrt(CAST(?1 AS double precision))</function-sql>
       </function-mapping>
       <function-mapping>
       <function-name>ucase</function-name>
       <function-sql>upper(?1)</function-sql>
       </function-mapping>
       <function-mapping>
       <function-name>count</function-name>
       <function-sql>count(?1)</function-sql>
       </function-mapping>
      
       <mapping>
       <java-type>java.lang.Boolean</java-type>
       <jdbc-type>CHAR</jdbc-type>
       <sql-type>BOOLEAN</sql-type>
       </mapping>
       <mapping>
       <java-type>java.lang.Byte</java-type>
       <jdbc-type>TINYINT</jdbc-type>
       <sql-type>INT2</sql-type>
       </mapping>
       <mapping>
       <java-type>java.lang.Short</java-type>
       <jdbc-type>SMALLINT</jdbc-type>
       <sql-type>INT2</sql-type>
       </mapping>
       <mapping>
       <java-type>java.lang.Integer</java-type>
       <jdbc-type>INTEGER</jdbc-type>
       <sql-type>INT4</sql-type>
       </mapping>
       <mapping>
       <java-type>java.lang.Long</java-type>
       <jdbc-type>BIGINT</jdbc-type>
       <sql-type>INT8</sql-type>
       </mapping>
       <mapping>
       <java-type>java.lang.Float</java-type>
       <jdbc-type>FLOAT</jdbc-type>
       <sql-type>FLOAT(7)</sql-type>
       </mapping>
       <mapping>
       <java-type>java.lang.Double</java-type>
       <jdbc-type>DOUBLE</jdbc-type>
       <sql-type>FLOAT8</sql-type>
       </mapping>
       <mapping>
       <java-type>java.lang.Character</java-type>
       <jdbc-type>CHAR</jdbc-type>
       <sql-type>CHAR(1)</sql-type>
       </mapping>
       <mapping>
       <java-type>java.lang.String</java-type>
       <jdbc-type>VARCHAR</jdbc-type>
       <sql-type>TEXT</sql-type>
       </mapping>
       <mapping>
       <java-type>java.util.Date</java-type>
       <jdbc-type>TIMESTAMP</jdbc-type>
       <sql-type>TIMESTAMP with Time Zone</sql-type>
       </mapping>
       <mapping>
       <java-type>java.sql.Date</java-type>
       <jdbc-type>DATE</jdbc-type>
       <sql-type>DATE</sql-type>
       </mapping>
       <mapping>
       <java-type>java.sql.Time</java-type>
       <jdbc-type>TIME</jdbc-type>
       <sql-type>TIME</sql-type>
       </mapping>
       <mapping>
       <java-type>java.sql.Timestamp</java-type>
       <jdbc-type>TIMESTAMP</jdbc-type>
       <sql-type>TIMESTAMP with Time Zone</sql-type>
       </mapping>
       <mapping>
       <java-type>java.math.BigDecimal</java-type>
       <jdbc-type>NUMERIC</jdbc-type>
       <sql-type>NUMERIC</sql-type>
       </mapping>
       <mapping>
       <java-type>java.lang.Object</java-type>
       <jdbc-type>VARBINARY</jdbc-type>
       <sql-type>BYTEA</sql-type>
       </mapping>
       </type-mapping>


      Changes are in bold, to the Timestamp and Date entries. What am I doing wrong?

      Thank you in advance for any feedback.