GeeCON Prague 2014*
The moose likes EJB Certification (SCBCD/OCPJBCD) and the fly likes Apache Derby Sql Statement's Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Certification » EJB Certification (SCBCD/OCPJBCD)
Bookmark "Apache Derby Sql Statement Watch "Apache Derby Sql Statement New topic
Author

Apache Derby Sql Statement's

Amandeep Singh
Ranch Hand

Joined: Jul 17, 2008
Posts: 844
inside CarCreatorBean.java

---------------
public List sqlQuery(){
// System.out.println("Inside the sqlQuery method");
// Query query = entityManager.createQuery("select c from Car c");

Query query = entityManager.createNamedQuery("findCar");
query.setParameter("steering", "steering");
query.getResultList();
return query.getResultList();
}
-------------


inside Car.java

-------------------------
@Entity
@Table(name="Car")
@EntityListeners(Pack.CarMonitor.class)
@NamedQuery(name="findCar", query = "select c from Car c where c.steering like :steering")
public class Car implements Serializable{

-------------------------

i have this query instance defined. This query is only understood by EJB3 JPA but not by Apache Derby, if i directly ran over there.

So i want to debug, to show in the log's what query is ran against the Apahce Derby.

Actually i want to see the translation of EJB3 JPA query to Apache Derby sql query.



Happy ThanksGiving Day.,


SCJP 1.4, SCWCD 5, SCBCD 5, OCPJWSD 5,SCEA-1, Started Assignment Part 2
My blog- http://rkydesigns.blogspot.com
Ralph Jaus
Ranch Hand

Joined: Apr 27, 2008
Posts: 342
If your persistence provider is toplink you may include one of the following properties in persistence.xml:

<property name="toplink.logging.level" value="FINE"/>
<property name="toplink.logging.level.sql" value="FINE"/>
[ November 27, 2008: Message edited by: Ralph Jaus ]

SCJP 5 (98%) - SCBCD 5 (98%)
Amandeep Singh
Ranch Hand

Joined: Jul 17, 2008
Posts: 844
thanks Ralph, it worked
------------------------
[#|2008-11-27T16:36:07.312-0500|INFO|sun-appserver-pe9.0|javax.enterprise.system.stream.out|_ThreadID=18;_ThreadName=p: thread-pool-1; w: 13;|
Montior Car Done for @PostPersist operations.|#]

[#|2008-11-27T16:36:07.312-0500|FINE|sun-appserver-pe9.0|oracle.toplink.essentials.file:/C:/Sun/SDK/domains/domain1/applications/j2ee-modules/JarFile/-actionBazaar.sql|_ThreadID=17;_ThreadName=p: thread-pool-1; w: 12;ClassName=null;MethodName=null;_RequestID=96e7feef-6357-4a6f-851d-1557ad257840;|SELECT id, steering, ENTITYMANAGER, wheel, horn, ZIPCODE, STREET, CARBILLINGINFO_car_billing_id FROM Car WHERE (horn LIKE CAST (? AS VARCHAR(32672) ))
bind => [horn]|#]

[#|2008-11-27T16:36:07.390-0500|FINE|sun-appserver-pe9.0|oracle.toplink.essentials.file:/C:/Sun/SDK/domains/domain1/applications/j2ee-modules/JarFile/-actionBazaar.sql|_ThreadID=17;_ThreadName=p: thread-pool-1; w: 12;ClassName=null;MethodName=null;_RequestID=96e7feef-6357-4a6f-851d-1557ad257840;|SELECT id, steering, ENTITYMANAGER, wheel, horn, ZIPCODE, STREET, CARBILLINGINFO_car_billing_id FROM Car WHERE (steering LIKE CAST (? AS VARCHAR(32672) ))
bind => [steering]|#]

[#|2008-11-27T16:36:07.437-0500|FINE|sun-appserver-pe9.0|oracle.toplink.essentials.file:/C:/Sun/SDK/domains/domain1/applications/j2ee-modules/JarFile/-actionBazaar.sql|_ThreadID=18;_ThreadName=p: thread-pool-1; w: 13;ClassName=null;MethodName=null;_RequestID=ced599c0-72e0-49ef-befc-7a067218ee14;|SELECT id, steering, ENTITYMANAGER, wheel, horn, ZIPCODE, STREET, CARBILLINGINFO_car_billing_id FROM Car WHERE (horn LIKE CAST (? AS VARCHAR(32672) ))
bind => [horn]|#]
-----------------------------------------------

In the log's, the sql query generated has selected field also entitymanager, and it is not any existing column present in the database, so it is always returned as null. So why it is included in the sql query.
Ralph Jaus
Ranch Hand

Joined: Apr 27, 2008
Posts: 342
Hmm ?? No idea. My sql log entries usually look like

SELECT CUSTOMER_ID, CUSTOMER_TYPE, NAME, FIRST_NAME, FROM CUSTOMER WHERE (CUSTOMER_ID = ?) bind => [61]
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14688
    
  16

the sql query generated has selected field also entitymanager, and it is not any existing column present in the database

In your Car entity, didn't you leave an field called "entityManager" ? Or in an embedded class, or in a super class... Show us your Car entity


[My Blog]
All roads lead to JavaRanch
Amandeep Singh
Ranch Hand

Joined: Jul 17, 2008
Posts: 844
Car.java

-----------------------
package Pack;

import java.io.Serializable;
import java.util.Set;

import javax.persistence.Basic;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.DiscriminatorColumn;
import javax.persistence.DiscriminatorType;
import javax.persistence.Embedded;
import javax.persistence.Entity;
import javax.persistence.EntityListeners;
import javax.persistence.EntityManager;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Inheritance;
import javax.persistence.InheritanceType;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.OneToOne;
import javax.persistence.PersistenceContext;
import javax.persistence.PrimaryKeyJoinColumn;
import javax.persistence.Table;

import oracle.toplink.essentials.config.HintValues;
import oracle.toplink.essentials.config.TopLinkQueryHints;


@Entity
@Table(name="Car")
@EntityListeners(Pack.CarMonitor.class)
//@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
//@Inheritance(strategy = InheritanceType.JOINED)
//@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
//@DiscriminatorColumn(name="car_type", discriminatorType=DiscriminatorType.STRING, length=1)
@NamedQuery(name="findCar",
query = "select c from Car c where c.steering like :steering"
)

@NamedQueries({@NamedQuery(name="findCar", query = "select c from Car c where c.steering like :steering"),
@NamedQuery(name="findCar1", query = "select c from Car c where c.horn like ?1")
})
public class Car implements Serializable{


@PersistenceContext(unitName ="actionBazaar")
private EntityManager entityManager;

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
@Column(name="id")
protected Long id;
public Long getId() {
return id;
}

public void setId(Long id) {
this.id = id;
}

public String getHorn() {
return horn;
}

public void setHorn(String horn) {
this.horn = horn;
}

public String getSteering() {
return steering;
}

public void setSteering(String steering) {
this.steering = steering;
}

public String getWheel() {
return wheel;
}

public void setWheel(String wheel) {
this.wheel = wheel;
}

@Column(name="horn")
@Basic(fetch = FetchType.EAGER)
protected String horn;

@Column(name="steering")
@Basic(fetch = FetchType.LAZY)
protected String steering;

@Column(name="wheel")
@Basic(fetch = FetchType.LAZY)
protected String wheel;


public Car(){

}

public Car(String horn, String steering, String wheel){
this.horn = horn;
this.steering = steering;
this.wheel = wheel;
}

@Embedded
protected Address address;

//@OneToOne
//@JoinColumn(name="CarBillingId", referencedColumnName="car_billing_id", updatable=false)
//protected CarBillingInfo carBillingInfo;

//@OneToOne
//@PrimaryKeyJoinColumn(name="id", referencedColumnName="car_billing_id")
//protected CarBillingInfo carBillingInfo;

//@ManyToOne
//@JoinColumn(name="car_billing_id", referencedColumnName="car_billing_id")
//protected CarBillingInfo carBillingInfo;

//@ManyToMany
//@JoinTable(name="join_car_carbillinginfo", joinColumns = @JoinColumn(name="id", referencedColumnName = "id")
//, inverseJoinColumns = @JoinColumn(name ="car_billing_id", referencedColumnName="car_billing_id"))
//protected Set<CarBillingInfo> carBillingInfo1;

@OneToOne(cascade=CascadeType.PERSIST)
protected CarBillingInfo carBillingInfo;



}
--------------------------------

there is a field EntityManager, but is not a Column.
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14688
    
  16

there is a field EntityManager, but is not a Column.

Think about it. What is the persistence container going to do to transform your JPQL into native SQL ? He's not going to look at how your database table. He is going to look into your entity, and put each field in the SELECT. He sees a field called "entityManager", then he's going to populate that field from the database.
Amandeep Singh
Ranch Hand

Joined: Jul 17, 2008
Posts: 844
So i need to use-

@PersistenceContext(unitName ="actionBazaar")
private EntityManager entityManager;


at the class level.

Does the persistence provider by looking at get method's(which have no @Column) will also treat them as property accessor field's for sql queries?
Amandeep Singh
Ranch Hand

Joined: Jul 17, 2008
Posts: 844
but why when i deploy my jar, the persistence provider doesn't make's

as
-------------------------------
@PersistenceContext(unitName ="actionBazaar")
private EntityManager entityManager;
--------------------------------

column in Car table.
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14688
    
  16

Because you didn't tell him to. You need to set a property in your persistence.xml to tell the persistence provider to make tables from entities at deploy time if you want to.
Amandeep Singh
Ranch Hand

Joined: Jul 17, 2008
Posts: 844
yup, that is fine.

when i deploy my jar, the persistence provider make's

as
-------------------------------
@PersistenceContext(unitName ="actionBazaar")
private EntityManager entityManager;
--------------------------------

EntityManager column in Car table. When i remove this private field from Car.java, it is fine doesn't make's the EntityManager column.

But i am unable to make out, how it make's column in Apache Derby, even though i haven't mentioned the @Column annotation.

If the persistence provider just look's at the field's defined and put it into the insert or select queries.

Then what is the use of @Column annotation.
[ November 27, 2008: Message edited by: Amandeep Singh ]
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14688
    
  16

Then what is the use of @Column annotation.

It's optional. The container defaults the name of the table column to the name of the field. You use the @Column annotation if you want to give it a different name, or if you want to map the field to a different table... Try to remove all your @Column annotation to see how it works. Also try to set different names in the @Column annotation.
Ralph Jaus
Ranch Hand

Joined: Apr 27, 2008
Posts: 342
If there are instance variables in your entity class, that shouldn't be persisted, annotate them with @Transient.

Anyway, I would say it is bad design to declare operational objects like entity managers in an entity class. It's better to decouple data (entities) and data processing.
Amandeep Singh
Ranch Hand

Joined: Jul 17, 2008
Posts: 844
thanks very much...
 
GeeCON Prague 2014
 
subject: Apache Derby Sql Statement's