Hi. I’m new to OpenJPA (and JPA in general) so please forgive me if I’m asking stupid questions.
I have a legacy database that I cannot change that has some strange mapping for logical relationships and I’m struggling to get the custom mapping done correctly. I have a table with description text that I need to reference using a value for ‘code’. The code value alone isn’t enough to uniquely identify the record, so I need to add a description type and language. So basically this table contains descriptions for many types (like Title, Province, etc.) for many languages. I’ve gotten OpenJPA’s Constant Joins to work using the following: (I checked out version 2.2.0 and applied a patch that I found on the forum [https://issues.apache.org/jira/browse/OPENJPA-1979?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel] to fix the Constant Joins issue [the patch was for an earlier version, but still worked for me]) @Entity @Table(name = "test") public class Test implements Serializable { @ManyToOne @JoinColumns({ @JoinColumn(name = "CODE", referencedColumnName = "TABLE_CODE"), @JoinColumn(name = "table_descriptions.TABLE_NO", referencedColumnName = "2"), @JoinColumn(name = "table_descriptions.LANGUAGE", referencedColumnName = "1") }) private TableDescription code; ... } This works for uni-directional and bidirectional relationships. The problem I’m having is that some of the tables that look up to this description table use Integer values for the value of ‘code’, and the ‘code’ value in the description table is a 2 char string (01, 02 etc.) So, I can get entity to find and store the correct value using: @ManyToOne //@Column(name="CODE") @JoinColumn(name="CODE", referencedColumnName="TABLE_CODE") @Externalizer(value = "Test.codeExternalizer") @Factory(value = "Test.codeFactory") private TableDescription code; @Transient private static EntityManagerFactory emf; With the Externalizer (as an inner class) looking like this: public static String codeExternalizer(TableDescription val) { if (val == null) { return null; } return val.getTableCode().toString(); } And the Factory looking like this: public static TableDescription codeFactory(String val) { if (val == null) { return null; } while (val.length() < 2) { val = "0" + val; } if (emf == null) { emf = Persistence.createEntityManagerFactory("EjbH2TestWebPU"); } EntityManager em = emf.createEntityManager(); TypedQuery<TableDescription> q = em.createQuery( "SELECT o FROM TableDescription as o WHERE o.tableCode = ?1 AND o.tableNo = ?2 AND o.language = ?3", TableDescription.class); q.setParameter(1, val); q.setParameter(2, 1); //f this is specific to each field that does a lookup, indicating what type of description it’s looking for. q.setParameter(3, 1); //f this is only 1 (English), but could be the currently logged in user's language. TableDescription result = null; try { result = q.getSingleResult(); } catch (javax.persistence.NoResultException nre) { System.err.println("no value for: " + val); } catch (javax.persistence.NonUniqueResultException notUnique) { System.err.println("more than one value for: " + val); } catch (Exception e) { System.err.println("An error occured getting value for: " + val + ": " + e.getMessage()); } em.close(); em = null; return result; } Firstly, is this the right way to use Externalizer and Factory? I can’t find one example of a working app that uses these annotations. The above code works when I get an instance of my entity and call getCode() and setCode(), but when doing a JPQL query it still returns the underlying String/Integer value instead of the related entity. I’m also getting an error when I need a bidirectional relationship and use the mappedBy value for the OneToMany annotation for the collection side of the relationship like this: @Entity @Table(name = "table_descriptions") public class TableDescription implements Serializable { @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, mappedBy = "code") private Collection<Test> tests; ... } When I try to do this, I get the following Exception when using @Column (commented out in the code above): Collection field "entity.TableDescription.tests" declares that it is mapped by "entity.Test.code", but this is not a valid inverse relation. And the following Exception when using @JoinColumn: "entity.Test.code" has columns with targets, but OpenJPA does not support any joins on this mapping in this context I have another scenario that doesn’t use Constant Joins, but the foreign key also needs to be prepended with zeros before the value is compared to the related table’s column. I also use the Externalizer/Factory setup and get the same problem with bidirectional relationships. So, what is the correct way of using Externalizer/Factory so that bidirectional relationships can work? The manual for OpenJPA is very sparse on these topics, listing only the annotations and the method signatures. I can’t find any sample code that shows the body of these methods. I’ve seen the forums refer to @Strategy as a possible sollution for mapping. I’ve tried the following but now I get an exception that it can’t load the class (related to an old post I found: http://openjpa.208410.n2.nabble.com/Using-custom-ValueHandler-td210193.html): @Strategy("entity.CodeStrategy") private TableDescription code; and the CodeStrategy class looks like this: public class CodeStrategy2 extends AbstractValueHandler { @Transient private static EntityManagerFactory emf; @Override public org.apache.openjpa.jdbc.schema.Column[] map(ValueMapping vm, String name, ColumnIO io, boolean adapt) { org.apache.openjpa.jdbc.schema.Table table = new org.apache.openjpa.jdbc.schema.Table(); table.setIdentifier(DBIdentifier.newTable("test")); DBIdentifier codeDbId = DBIdentifier.newColumn("code"); org.apache.openjpa.jdbc.schema.Column codeCol = new org.apache.openjpa.jdbc.schema.Column(); codeCol.setJavaType(JavaTypes.STRING); return new org.apache.openjpa.jdbc.schema.Column[]{codeCol}; } @Override public Object toDataStoreValue(ValueMapping vm, Object val, JDBCStore store) { if (val == null) { return null; } if (val instanceof TableDescription) { return ((TableDescription) val).getTableCode(); } else { throw new IllegalArgumentException("Code is not an instance of TableDescription: " + val); } } @Override public boolean objectValueRequiresLoad(ValueMapping vm) { return true; } @Override public Object toObjectValue(ValueMapping vm, Object value, OpenJPAStateManager sm, JDBCStore store, JDBCFetchConfiguration fetch) throws SQLException { if (value == null) { return null; } //f do a select statement to get it from the db. String val = value.toString(); while (val.length() < 2) { val = "0" + val; } //f can I use StoreContext to do this instead? What is the best way? if (emf == null) { emf = Persistence.createEntityManagerFactory("EjbH2TestWebPU"); } EntityManager em = emf.createEntityManager(); TypedQuery<TableDescription> q = em.createQuery( "SELECT o FROM TableDescription as o WHERE o.tableCode = ?1 AND o.tableNo = ?2 AND o.language = ?3", TableDescription.class); q.setParameter(1, val); q.setParameter(2, 1); //f this is specific to each field that does a lookup q.setParameter(3, 1); //f this is only 1 (English), but could be the currently logged in user's language. TableDescription result = null; try { result = q.getSingleResult(); } catch (javax.persistence.NoResultException nre) { System.err.println("no value for: " + val); } catch (javax.persistence.NonUniqueResultException notUnique) { System.err.println("more than one value for: " + val); } catch (Exception e) { System.err.println("An error occured getting value for: " + val + ": " + e.getMessage()); } em.close(); em = null; return result; } } In the post mentioned above they suggest a patch for the classpath issue (which didn’t work for me), or adding your ValueHandler code in the OpenJpa jar file. Since it references another entity of mine, I would have to add my whole app in there, which won’t work. Ideally I want all my Strategy value handlers as inner classes of the entity like I did for my Externalizers. Are there any tutorials/walkthroughs/sample projects that I can use to learn how this works? Thanks in advance for any help on this -- View this message in context: http://openjpa.208410.n2.nabble.com/Custom-Mapping-with-Externalizer-Factory-Strategy-tp7563345.html Sent from the OpenJPA Users mailing list archive at Nabble.com.
