Hi,
We recently did a switch from toplink to OpenJPA. I am trying to generate
the sql files attached to the entities we have. For context, we use
Glassfish, EJB3, JPA, and MySQL.
So, I have two entities:
@Entity
@Table(name="SMALLEMPLOYEE")
public class SmallEmployee implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
//@GeneratedValue(strategy = GenerationType.SEQUENCE)
private Long id = null;
private String name = null;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "COMP_ID", nullable = false)
//@ForeignKey
private BigCompany company = null;
@Version
private int version;
...
}
And:
@Entity
@Table(name = "BIGCOMPANY")
public class BigCompany implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id = null;
private String name = null;
@OneToMany(cascade = CascadeType.ALL, mappedBy = "company", fetch =
FetchType.EAGER)
private Set<SmallEmployee> employees = new HashSet<SmallEmployee>();
@Version
private int version;
...
}
Which is quite simple.
We use maven for building:
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>openjpa-maven-plugin</artifactId>
<version>1.1</version>
<configuration>
<includes>**/persist/*.class</includes>
<addDefaultConstructor>true</addDefaultConstructor>
<enforcePropertyRestrictions>true</enforcePropertyRestrictions>
<sqlFile>./mytables.sql</sqlFile>
</configuration>
<executions>
<execution>
<id>enhancer</id>
<phase>process-classes</phase>
<goals>
<goal>enhance</goal>
<goal>sql</goal>
</goals>
</execution>
</executions>
</plugin>
And the persistence.xml:
<!-- OpenJPA -->
<persistence-unit name="BM" transaction-type="JTA">
<provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
<jta-data-source>jdbc/bm</jta-data-source>
<class>persist.BigCompany</class>
<class>persist.SmallEmployee</class>
<properties>
<property name="openjpa.jdbc.DBDictionary"
value="mysql(DriverVendor=mysql)"/>
</properties>
</persistence-unit>
Ok, now my question, how can I generate the SQL so that the foreign keys are
part of the generated sql?
If I run maven, what I get is this:
CREATE TABLE BIGCOMPANY (id BIGINT NOT NULL AUTO_INCREMENT, name
VARCHAR(255), version INTEGER, PRIMARY KEY (id)) TYPE = innodb;
CREATE TABLE SMALLEMPLOYEE (id BIGINT NOT NULL AUTO_INCREMENT, name
VARCHAR(255), version INTEGER, COMP_ID BIGINT NOT NULL, PRIMARY KEY (id))
TYPE = innodb;
CREATE INDEX I_SMLLPLY_COMPANY ON SMALLEMPLOYEE (COMP_ID);
By default, I would expect that "CREATE INDEX I_SMLLPLY_COMPANY ON
SMALLEMPLOYEE (COMP_ID);" would be expressed as a foreign key constraint.
The only way I manage to generate that is by modifying the MsallEMployee
class and add the @ForeignKey annotation on the BigCompany company field.
So if I do:
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "COMP_ID", nullable = false)
//@ForeignKey
private BigCompany company = null;
Then I get:
CREATE TABLE BIGCOMPANY (id BIGINT NOT NULL AUTO_INCREMENT, name
VARCHAR(255), version INTEGER, PRIMARY KEY (id)) TYPE = innodb;
CREATE TABLE SMALLEMPLOYEE (id BIGINT NOT NULL AUTO_INCREMENT, name
VARCHAR(255), version INTEGER, COMP_ID BIGINT NOT NULL, PRIMARY KEY (id))
TYPE = innodb;
ALTER TABLE SMALLEMPLOYEE ADD FOREIGN KEY (COMP_ID) REFERENCES BIGCOMPANY
(id);
That would be problem 1. How to generate the foreign key constraint without
the openjpa annotation (which toplink did automatically)?
Problem 2, can I have my generated SQL with the constraint expressed in the
CREATE table statement and not as a separate ALTER statement?
CREATE TABLE SMALLEMPLOYEE (id BIGINT NOT NULL AUTO_INCREMENT, name
VARCHAR(255), version INTEGER, COMP_ID BIGINT NOT NULL, PRIMARY KEY (id),
CONSTRAINT smallemployee_ibfk_1 FOREIGN KEY (COMP_ID) REFERENCES
BIGCOMPANY(id)) ENGINE=InnoDB;
Instead of the extra line with an ALTER statement...
?
Thanks
--
View this message in context:
http://openjpa.208410.n2.nabble.com/Generating-SQL-including-Foreign-Key-constraints-OpenJpa-1-2-2-tp6055639p6055639.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
