Inconsistent execution order of INSERT statements using cascading persist

[Timothy Fanelli <ti...@timfanelli.com>]

Hello -

I'm hoping someone can help me out here... I have two related entities
using a bi-direction one-to-many relationship, as follows:


@Entity
@Table(name="E_ORDER")
@NamedQueries({...})
public class NewOrder implements Serializable {
	public NewOrder() {
		this.date = new Timestamp(System.currentTimeMillis());
	}
	
	private final static Logger LOG = Logger.getLogger( NewOrder.class.getName() );

	@Id
	@GeneratedValue(strategy=GenerationType.SEQUENCE,generator="ORDERID_SEQ")
	@SequenceGenerator(name="ORDERID_SEQ",sequenceName="ORDER_ID",allocationSize=1)
	@Column(name = "NEWORDER_ORDER_ID",updatable=false,nullable=false)
	private Integer orderIdentification;

	@Basic
	@Temporal(TemporalType.DATE)
	@Column(name = "NEWORDER_DATE")
	private Timestamp date;

	@ManyToOne(fetch = FetchType.EAGER,cascade=ALL)
	@JoinColumn(name = "NEWORDER_CUSTOMER_ID", referencedColumnName = "CUSTOMER_IDENTIFICATION")
	private Customer customer;

	@OneToMany(fetch = FetchType.EAGER,cascade={ALL},mappedBy="newOrder")
	private Set<NewOrderLineItem> items = new HashSet<NewOrderLineItem>();
 
	// Set and get methods here...
}


and


@Entity
@Table(name = "E_NEWORDER_ORDERLINE")
public class NewOrderLineItem implements Serializable {
	@Id
	@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="LINEITEMID_SEQ_GEN")
	@SequenceGenerator(name="LINEITEMID_SEQ_GEN", allocationSize=1, sequenceName="NEWORDERLINE_ID_SEQ")
	@Column(name="NEWORDER_LINEITEM_ID",nullable=false,updatable=false)
	private Integer id;

	@ManyToOne(cascade={CascadeType.ALL})
	@JoinColumn(name="NEWORDER_ORDER_ID", nullable=false)
	private NewOrder newOrder;

	@Column(name="NEWORDER_ITEM_ID",nullable=false)
	private String itemId;
	
	@Column(name = "NEWORDER_QUANTITY")
	private Integer quantity;

	// Set and get methods here...
}


So the NewOrder entity has a one-to-many relationship with the
NewOrderLineItem entity, which is the owning side. My application
creates a new NewOrder instance and add's several NewOrderLineItem
instances to it, via an add method which maintains both sides of the
relationship (by setting "this" as the neworder on the neworderlineitem,
and then adding that line item to the order's hashset). I then persist
my new order, and expect that cascading persistence will take care of
the rest.

What I expect to see in my openjpa.jdbc.SQL trace output is something
like:

SELECT NEXTVAL FOR ORDERID_SEQ
SELECT NEXTVAL FOR NEWORDERLINE_ID_SEQ
SELECT NEXTVAL FOR NEWORDERLINE_ID_SEQ
SELECT NEXTVAL FOR NEWORDERLINE_ID_SEQ
INSERT INTO E_NEWORDER ( NEWORDER_ORDER_ID, NEWORDER_DATE,
NEWORDER_CUSTOMER_ID ) VALUES ( ?, ?, ? )
INSERT INTO E_NEWORDER_LINEITEM ( NEWORDER_LINEITEM_ID,
NEWORDER_ORDER_ID, NEWORDER_ITEM_ID, NEWORDER_QUANTITY ) VALUES
( ?, ?, ?, ? )
INSERT INTO E_NEWORDER_LINEITEM ( NEWORDER_LINEITEM_ID,
NEWORDER_ORDER_ID, NEWORDER_ITEM_ID, NEWORDER_QUANTITY ) VALUES
( ?, ?, ?, ? )
INSERT INTO E_NEWORDER_LINEITEM ( NEWORDER_LINEITEM_ID,
NEWORDER_ORDER_ID, NEWORDER_ITEM_ID, NEWORDER_QUANTITY ) VALUES
( ?, ?, ?, ? )

However, it appears as though MOST of the time, the INSERT into the
E_NEWORDER (the parent table) does not occur before the attempted
inserts into the E_NEWORDER_ORDERLINE table; thus violating my
referential integrity and throwing a foreign key violation SQL
Exception. Sometimes though, everything works as it should and nothing
goes wrong.

Any idea why OpenJPA (1.1.0) would not properly insert the parent entity
first?

Also - I had attempted to have OpenJPA using <property
name="openjpa.jdbc.SchemaFactory" value="native(ForeignKeys=true)"/> but
we use custom data types in our schema that weren't being properly
validated... so that wasn't really an option for me.

Any help would be greatly appreciated!
-Tim Fanelli

Re: Inconsistent execution order of INSERT statements using cascading persist

[sudhi <su...@gmail.com>]

Hello Pinaki,

i am facing same kind of issue, 
I had raised a question here
http://openjpa.208410.n2.nabble.com/OpenJPA-2-2-0-Oracle-Issue-td7582658.html 

After seeing this post i tried with @ForeignKey annotation and it works now.
So my question is why we have to use OpenJPA specific annotations??? I
believe Cascade definitions should be sufficient to identify parent child
insertion order??





--
View this message in context: http://openjpa.208410.n2.nabble.com/Inconsistent-execution-order-of-INSERT-statements-using-cascading-persist-tp678745p7582683.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Inconsistent execution order of INSERT statements using cascading persist

[Sudhir K Sethi <ks...@gmail.com>]

Thanks a lot Pinaki. I will try this see the difference in behavior and let
know the result. But if I use List, I lost the constraint of duplicity. In
my design I should have unique workflows for a case.

I am not quite clear on this "JPA 2.0 also allows a column to designate as
an ordering element."
How does this is supported (through annotations, etc)?
-- 
View this message in context: http://n2.nabble.com/Inconsistent-execution-order-of-INSERT-statements-using-cascading-persist-tp678745p4889718.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Inconsistent execution order of INSERT statements using cascading persist

[Pinaki Poddar <pp...@apache.org>]

Set<T> does not impose ordering of its element. List<T> does.

To impose ordering the field type could be declared as List<>.

JPA 2.0 also allows a column to designate as an ordering element.

-----
Pinaki 
-- 
View this message in context: http://n2.nabble.com/Inconsistent-execution-order-of-INSERT-statements-using-cascading-persist-tp678745p4889645.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Inconsistent execution order of INSERT statements using cascading persist

[Sudhir K Sethi <ks...@gmail.com>]

Hi,
I am using openJPA since couple of months. What keep me troubling is use of
custom properties those needs to be defining in persistence.xml

<properties>
	<property name="openjpa.jdbc.UpdateManager" value="operation-order" />
	<property name="openjpa.jdbc.SchemaFactory"
value="native(ForeignKeys=true)" />			
</properties>



I am still confused with the use of these two properties above and what
should be the behavior incase I define both of them? Which one has
precedence on other? Proper documents with use cases?

My problem is that: I am not able to maintain order of insertion for
collection of objects. I have used set of WorkFlowStateBean in CaseBean as
below (CaseBean is my container object and WorkFlowStateBean is my contained
object)
private Set<WorkFlowStateBean> workflows =
			new LinkedHashSet<WorkFlowStateBean>();


What I want is that my operation order of container and all the contained
objects to be consistence the way I created the objects:

CaseBean case1 = new CaseBean();
WorkFlowStateBean w1 = new WorkFlowStateBean();
WorkFlowStateBean w2 = new WorkFlowStateBean();
WorkFlowStateBean w3 = new WorkFlowStateBean();
WorkFlowStateBean w4 = new WorkFlowStateBean();

case1. addWorkflow(w1);
case1. addWorkflow(w2);
case1. addWorkflow(w3);
case1. addWorkflow(w4);

The output should be: case1[w1,w2,w3,w4]

But the behavior for contained objects addition is very random. Sometime I
get results as: case1[w4,w2,w3,w1] or case1[w2,w1,w3,w4], etc…

I want to control the order of insertion for contained objects as well. How
can I do this? Is there any additional custom properties provided by OpenJPA
(I tried both in OpenJPA 1.1.x and 1.2.x behavior is same).

I have also added @ForeignKey attribute to the column in my persistence
classes and nothing seems to be working.

I have also tried removing the property "openjpa.jdbc.SchemaFactory" defined
in persistence.xml defined above and it gave different problem. It is
throwing NullPointerException for a column which is marked as NOT NULL in my
table definition. The field which is marked as NOT NULL and when application
is not providing the values, database should take care to fill this value
(as my column definition is as below).

CREATED_DATE                  DATE              DEFAULT SYSDATE NOT NULL,


I am using oracle db and weblogic server for my application. Any help on
this should be greatly appreciative.

Thanks a lot.
Regards,
Sudhir

-- 
View this message in context: http://n2.nabble.com/Inconsistent-execution-order-of-INSERT-statements-using-cascading-persist-tp678745p4889398.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Inconsistent execution order of INSERT statements using cascading persist

[timfanelli <ti...@timfanelli.com>]

Sorry it took a while to get this confirmed. I wound up having to do some
work to my application server environment and got hung up for a bit.

Adding the @ForeignKey attribute to the column worked perfectly. For now,
I'm also sticking with the operation-order update manager... performance is
not a big concern of mine at the moment, I'm driving for stability under
load and feel better knowing that the database operations will execute in an
expected order.

Thanks again for the help!
-Tim


Pinaki Poddar wrote:
> 
> Hi,
>> Would using the OpenJPA "ForeignKey" annotation be a sufficient work
>> around for not being able to use the native schema factory?
> 
> Good idea. Should work as well as this annotation tells OpenJPA that a
> 'logical' foreign key exists.
> Have not tried/tested though. Let us know if this solves your problem.
> 
>> Also, if I use the operation-order update manager, would I need to
>> explicitly insert the parent entity and child entities separately, or
>> would the cascading persist work in that manner?
> 
> The cascade will insert parent first and child later if Parent.children is
> annotated with CascadeType.PERSIST/ALL. The cascading scheme and sql
> ordering strategy are not aware of each other but in this case they will
> be consistent.
> 
> 
> 

-- 
View this message in context: http://n2.nabble.com/Inconsistent-execution-order-of-INSERT-statements-using-cascading-persist-tp678745p722018.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Inconsistent execution order of INSERT statements using cascading persist

[Pinaki Poddar <pp...@apache.org>]

Hi,
> Would using the OpenJPA "ForeignKey" annotation be a sufficient work
> around for not being able to use the native schema factory?

Good idea. Should work as well as this annotation tells OpenJPA that a
'logical' foreign key exists.
Have not tried/tested though. Let us know if this solves your problem.

> Also, if I use the operation-order update manager, would I need to
> explicitly insert the parent entity and child entities separately, or
> would the cascading persist work in that manner?

The cascade will insert parent first and child later if Parent.children is
annotated with CascadeType.PERSIST/ALL. The cascading scheme and sql
ordering strategy are not aware of each other but in this case they will be
consistent.


-- 
View this message in context: http://n2.nabble.com/Inconsistent-execution-order-of-INSERT-statements-using-cascading-persist-tp678745p679216.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Inconsistent execution order of INSERT statements using cascading persist

[Timothy Fanelli <ti...@timfanelli.com>]

Thanks -

Would using the OpenJPA "ForeignKey" annotation be a sufficient work
around for not being able to use the native schema factory?

Also, if I use the operation-order update manager, would I need to
explicitly insert the parent entity and child entities separately, or
would the cascading persist work in that manner?

-Tim

On Thu, 2008-08-07 at 07:59 -0700, Pinaki Poddar wrote:
> Hi,
> > using <property name="openjpa.jdbc.SchemaFactory"
> > value="native(ForeignKeys=true)"/> but
> > we use custom data types in our schema that weren't being properly
> > validated... so that wasn't really > an option for me.
> 
> > Sometimes though, everything works as it should and nothing goes wrong.
> 
> Without reading the existing foreign key information, OpenJPA will not be
> able to reorder the SQL it issues to satisfy the database constraints. The
> suggestion choice is 
> 1. to instruct OpenJPA *not* to reorder the SQL or rather follow the order
> in which object operations were performed
>     <property name="openjpa.jdbc.UpdateManager" value="operation-order">
> 2. perform the object operations in a order that satisfy the foreign key
> constraints (such as inserting a parent before a child)
> 

Re: Inconsistent execution order of INSERT statements using cascading persist

[Pinaki Poddar <pp...@apache.org>]

Hi,
> using <property name="openjpa.jdbc.SchemaFactory"
> value="native(ForeignKeys=true)"/> but
> we use custom data types in our schema that weren't being properly
> validated... so that wasn't really > an option for me.

> Sometimes though, everything works as it should and nothing goes wrong.

Without reading the existing foreign key information, OpenJPA will not be
able to reorder the SQL it issues to satisfy the database constraints. The
suggestion choice is 
1. to instruct OpenJPA *not* to reorder the SQL or rather follow the order
in which object operations were performed
    <property name="openjpa.jdbc.UpdateManager" value="operation-order">
2. perform the object operations in a order that satisfy the foreign key
constraints (such as inserting a parent before a child)

-- 
View this message in context: http://n2.nabble.com/Inconsistent-execution-order-of-INSERT-statements-using-cascading-persist-tp678745p678793.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.