You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by baboune <se...@yahoo.com> on 2011/02/23 10:00:52 UTC
Generating SQL including Foreign Key constraints (OpenJpa 1.2.2)
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.
RE: Generating SQL including Foreign Key constraints (OpenJpa1.2.2)
Posted by baboune <se...@yahoo.com>.
OK. Good thanks for the help. I will take a look in the source.
--
View this message in context: http://openjpa.208410.n2.nabble.com/Generating-SQL-including-Foreign-Key-constraints-OpenJpa-1-2-2-tp6055639p6061712.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
RE: Generating SQL including Foreign Key constraints (OpenJpa1.2.2)
Posted by Miłosz Tylenda <mt...@o2.pl>.
Hi,
I don't think you will get foreign key constraints expressed in CREATE TABLE statement out of the box. However, if you have the time and are not afraid of digging into the source, you can try extending the DBDictionary for your database and override a few methods. See getCreateTableSQL(Table table) and getForeignKeyConstraintSQL(ForeignKey fk) methods. Since Table has the getForeignKeys method, that way seems promising. I am referring here to OpenJPA 2.1 source code but 1.2.2 should be similar in this area.
Hope this helps,
Milosz
>
> Hi,
>
> Adding:
>
> <property name="openjpa.jdbc.MappingDefaults"
> value="ForeignKeyDeleteAction=restrict,
> JoinForeignKeyDeleteAction=restrict" />
>
> Does indeed solve problem 1.
>
> Thank you.
>
> Any ideas/suggestions on how to get the generated SQL with the constraint
> expressed in the
> CREATE table statement and not as a separate ALTER statement?
>
RE: Generating SQL including Foreign Key constraints (OpenJpa
1.2.2)
Posted by baboune <se...@yahoo.com>.
Hi,
Adding:
<property name="openjpa.jdbc.MappingDefaults"
value="ForeignKeyDeleteAction=restrict,
JoinForeignKeyDeleteAction=restrict" />
Does indeed solve problem 1.
Thank you.
Any ideas/suggestions on how to get the generated SQL with the constraint
expressed in the
CREATE table statement and not as a separate ALTER statement?
--
View this message in context: http://openjpa.208410.n2.nabble.com/Generating-SQL-including-Foreign-Key-constraints-OpenJpa-1-2-2-tp6055639p6057861.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
RE: Generating SQL including Foreign Key constraints (OpenJpa 1.2.2)
Posted by Henno Vermeulen <he...@huizemolenaar.nl>.
We also had the problem that no foreign keys were generated. We solved this by adding:
<property name="openjpa.jdbc.MappingDefaults"
value="ForeignKeyDeleteAction=restrict, JoinForeignKeyDeleteAction=restrict" />
to out persistence.xml. If I understand it well (the OpenJPA manual, is quite concise about this, see also http://en.wikipedia.org/wiki/Foreign_key#RESTRICT), this simply means that you should not be able to delete an entity when there is still a foreign key reference to it. The way to enforce this is by using foreign key constraints, so when you use this option, OpenJPA will generate them.
HTH,
Henno
-----Oorspronkelijk bericht-----
Van: baboune [mailto:seyvet@yahoo.com]
Verzonden: woensdag 23 februari 2011 10:01
Aan: users@openjpa.apache.org
Onderwerp: Generating SQL including Foreign Key constraints (OpenJpa 1.2.2)
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.