Brian Storenson

I have three tables Lesson, Course, Program. Lesson has an attribute for Course's uuid, Course has an attribute for Program's uuid. How can I translate this to a Criteria API expression? Right now my statement returns a Path, and it's not returning any tuples.

I wrote out some sql to help me figure out what I needed to do. I think it should look something like this:

SELECT *
FROM Lesson AS l
INNER JOIN Course AS c
ON l.Course=c.uuid
INNER JOIN Program AS p
ON c.program=p.uuid
WHERE p.uuid = 'LessonSearchCriteria.PROGRAM_ID'


I am creating a dynamic query method that can search by Program_ID and by other characteristics if needed. My code looks like this as of right now:

if (searchCriteria != null && searchCriteria.containsKey(LessonSearchCriteria.PROGRAM_ID)){ predicates.add(criteriaBuilder.equal(root.join(Lesson_.course).join(Course_.program).get(Program_.uuid), searchCriteria.get(LessonSearchCriteria.PROGRAM_ID).toString())); }

I have also tried using the String versions of the join and get methods. Thanks!

Thanks Chris! I didn't think of looking for an open source ETL tool. These hints will be helpful

Oh yeah, I am now working on making a connection to an old version of the DB. I am going to use JDBC and sql statements. That is where I'm at right now. Thanks.

Hi, I need to write a DAO to extract data from an old sql server 2008 database. The data will eventually will be passed to other parts of the program that will then pass it to the new websites application layer. The new website will then take the data and use it to create new members for its database. We don't want to loose any data. The old DB has a lot of stored procedures. I've read a wiki on data migration, and looked at some postgres docs on data migration.

What do I need to research next to do this data migration? I have a big script that I created on sql server. I'm stuck right now. Thanks.

I'm trying to write an integration test that queries an hsql database. I'm using hibernate/spring. My program seems to throw up on line 68. When I highlight query.getResultList() while I am debugging the test, it says:

query.getResultList() = >Exception occurred in target VM: org.hibernate.SessionException: Session is closed!<

It seems like I am losing the context. How do I reopen the transaction, or keep the transaction open so I can query the database?

Here is my stack trace

------------------------------------------------------------------------------- Test set: com.sample.membership.data.EntityManagerPersonDaoIntegrationTest ------------------------------------------------------------------------------- Tests run: 2, Failures: 1, Errors: 0, Skipped: 1, Time elapsed: 140.402 sec <<< FAILURE! createOrUpdate(com.sample.membership.data.EntityManagerPersonDaoIntegrationTest) Time elapsed: 137.777 sec <<< FAILURE! javax.persistence.PersistenceException: org.hibernate.SessionException: Session is closed! at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1387) at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310) at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:277) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:311) at com.sun.proxy.$Proxy29.getResultList(Unknown Source) at com.sample.membership.data.EntityManagerPersonDaoIntegrationTest.createOrUpdate(EntityManagerPersonDaoIntegrationTest.java:68) Caused by: org.hibernate.SessionException: Session is closed! at org.hibernate.internal.AbstractSessionImpl.errorIfClosed(AbstractSessionImpl.java:129) at org.hibernate.internal.SessionImpl.getPersistenceContext(SessionImpl.java:1937) at org.hibernate.internal.AbstractQueryImpl.isReadOnly(AbstractQueryImpl.java:214) at org.hibernate.internal.AbstractQueryImpl.getQueryParameters(AbstractQueryImpl.java:933) at org.hibernate.internal.SQLQueryImpl.getQueryParameters(SQLQueryImpl.java:200) at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:157) at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:268) ... 47 more

Here is my integration test

package com.sample.membership.data; import com.sample.membership.EmergencyContact; import com.sample.membership.Member; import com.sample.membership.Person; import com.sample.membership.Relationship; import java.util.ArrayList; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import javax.persistence.Query; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.context.ApplicationContext; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.testng.AbstractTestNGSpringContextTests; import static org.testng.Assert.assertEquals; import static org.testng.Assert.assertFalse; import static org.testng.Assert.assertTrue; import org.testng.annotations.Test; /** * * * @author */ @ContextConfiguration(locations = {"classpath:com/sample/membership/data/applicationContext.xml"}) public class EntityManagerPersonDaoIntegrationTest extends AbstractTestNGSpringContextTests { @Autowired private ApplicationContext applicationContext; @Autowired private EntityManagerPersonDao entityManagerPersonDao; @PersistenceContext(unitName = "sample-membership") private EntityManager entityManager; private static String returnUuid; @Test // used to Create or update the specified Member // see MemberService for documentation // see DefaultMemberService for implementation public void createOrUpdate() { // create test data // create dummy member Member member = new Member(); List<Relationship> target = new ArrayList<Relationship>(); List<Relationship> source = new ArrayList<Relationship>(); List<EmergencyContact> emergencyContacts = new ArrayList<EmergencyContact>(); member.setvCard("TestVCard1234567890"); member.setTargets(target); member.setSources(source); member.setEmergencyContacts(emergencyContacts); // create hibernate transaction instance to persist data // method under test: saveOrUpdate entityManagerPersonDao.saveOrUpdate(member); // get uuid for for the saved member returnUuid = member.getUuid(); // write a sql statement to find all the tuples that match the returnUuid String sqlQuery = "select p from Member p where p.uuid = :uuid"; Query query = entityManager.createNativeQuery(sqlQuery, Member.class); query.setParameter("uuid", returnUuid); // assert that we query returned a result // program breaks on the next line. it never gets to assertions. List results = query.getResultList(); assertFalse(results.isEmpty()); // verify results were correct // String uuid // String vCard // List<Relationship> targets // List<Relationship> sources // List<EmergencyContact> emergencyContacts // verify using sql } @Test(dependsOnMethods = {"createOrUpdate"}) // create a test for getMember in MemberService class // used to Fetch a Member by its ID. // see MemberService for documentation // see DefaultMemberService for implementation public void getMember() { // create hibernate transaction instance to persist data // method under test: saveOrUpdate Person returnPerson = entityManagerPersonDao.findById(returnUuid); // verify results were correct // verify results were correct // String uuid // String vCard // List<Relationship> targets // List<Relationship> sources // List<EmergencyContact> emergencyContacts // verify using sql assertTrue((returnPerson instanceof Member)); } }

I am having problems building my EntityManagerFactory. I think it is related to my persistence.xml. I have been working at this problem for the last 5-6 hours I think I have run out of resources. I would appreciate any tips. Thanks.

Here is my error message

------------------------------------------------------------------------------- Test set: TestSuite ------------------------------------------------------------------------------- Tests run: 9, Failures: 1, Errors: 0, Skipped: 2, Time elapsed: 2.078 sec <<< FAILURE! init(com.sample.membership.data.EntityManagerPersonDaoIntegrationTest) Time elapsed: 0.321 sec <<< FAILURE! javax.persistence.PersistenceException: Invalid persistence.xml. Error parsing XML (line-1 : column -1): cvc-elt.1: Cannot find the declaration of element 'persistence'. at org.hibernate.ejb.packaging.PersistenceXmlLoader.loadURL(PersistenceXmlLoader.java:147) at org.hibernate.ejb.packaging.PersistenceXmlLoader.deploy(PersistenceXmlLoader.java:171) at org.hibernate.ejb.Ejb3Configuration.configure(Ejb3Configuration.java:326) at org.hibernate.ejb.HibernatePersistence.createEntityManagerFactory(HibernatePersistence.java:58) at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:63) at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:47) at com.sample.membership.data.EntityManagerPersonDaoIntegrationTest.init(EntityManagerPersonDaoIntegrationTest.java:58)

Here is my integration test

package com.sample.membership.data; import com.sample.membership.EmergencyContact; import com.sample.membership.Member; import com.sample.membership.Person; import com.sample.membership.Relationship; import java.util.ArrayList; import java.util.List; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.EntityTransaction; import javax.persistence.Persistence; import org.hsqldb.jdbc.JDBCDataSource; import org.testng.annotations.BeforeClass; import org.testng.annotations.Test; import static org.testng.Assert.*; import javax.naming.Context; import javax.transaction.Transaction; import org.eclipse.jetty.util.resource.Resource; /** * * * @author */ public class EntityManagerPersonDaoIntegrationTest { private static EntityManagerPersonDao entityManagerPersonDao; private static EntityManager entityManager; private static PersonDao personDao; @BeforeClass // setup db connection // setup entitymanager public static void init() throws NamingException { //Create an inital context before using entityManager System.setProperty("java.naming.factory.url.pkgs", "org.eclipse.jetty.jndi"); System.setProperty("java.naming.factory.initial", "org.eclipse.jetty.jndi.InitialContextFactory"); InitialContext initialContext = new InitialContext(); //Add the datasource to the context with the name specified in the persistence.xml JDBCDataSource dataSource = new JDBCDataSource(); dataSource.setUrl("jdbc:hsqldb:mem:testdb"); dataSource.setUser("sa"); dataSource.setPassword(""); initialContext.createSubcontext("jdbc"); // is the first parameter the correct directory? This is called the data source // i think we get this from the peristence.xml initialContext.bind("jdbc/TestDB", dataSource); //create the entity manager and set it into the dao after creating initial context // set the persistence unit name // i think we get this from the peristence.xml final EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("com.test-membership"); entityManager = entityManagerFactory.createEntityManager(); entityManagerPersonDao = new EntityManagerPersonDao(); entityManagerPersonDao.setEntityManager(entityManager); } @Test // used to Create or update the specified Member // see MemberService for documentation // see DefaultMemberService for implementation public void createOrUpdate() { // create test data // create dummy member Member member = new Member(); List<Relationship> target = new ArrayList<Relationship>(); List<Relationship> source = new ArrayList<Relationship>(); List<EmergencyContact> emergencyContacts = new ArrayList<EmergencyContact>(); member.setUuid("1234567890"); member.setvCard("TestVCard"); member.setTargets(target); member.setSources(source); member.setEmergencyContacts(emergencyContacts); // create hibernate transaction instance to persist data final EntityTransaction entityTransaction = entityManager.getTransaction(); entityTransaction.begin(); // method under test: saveOrUpdate entityManagerPersonDao.saveOrUpdate(member); entityTransaction.commit(); // verify results were correct // String uuid // String vCard // List<Relationship> targets // List<Relationship> sources // List<EmergencyContact> emergencyContacts // verify using sql } @Test // create a test for getMember in MemberService class // used to Fetch a Member by its ID. // see MemberService for documentation // see DefaultMemberService for implementation public void getMember() { // create test data String uuid = "1234567890"; // used for finding member // create dummy member Member member = new Member(); List<Relationship> target = new ArrayList<Relationship>(); List<Relationship> source = new ArrayList<Relationship>(); List<EmergencyContact> emergencyContacts = new ArrayList<EmergencyContact>(); member.setUuid("1234567890"); member.setvCard("TestVCard"); member.setTargets(target); member.setSources(source); member.setEmergencyContacts(emergencyContacts); // create hibernate transaction instance to persist data final EntityTransaction entityTransaction = entityManager.getTransaction(); entityTransaction.begin(); // method under test: saveOrUpdate Person person = personDao.findById(uuid); entityTransaction.commit(); // verify results were correct } }

Here is my persistence.xml

<?xml version="1.0" encoding="UTF-8"?> <!--<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd" version="2.1"> --> <persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd" version="2.0"> <persistence-unit name="com.Test-membership" transaction-type="JTA"> <provider>org.hibernate.ejb.HibernatePersistence</provider> <jta-data-source>jdbc/TestDB</jta-data-source> <class>com.sample.membership.EmergencyContact</class> <class>com.sample.membership.Gymnast</class> <class>com.sample.membership.Member</class> <class>com.sample.membership.Relationship</class> <properties> <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/> <property name="hibernate.hbm2ddl.auto" value="create-drop"/> <property name="hibernate.show_sql" value="true"/> </properties> </persistence-unit> </persistence>

I've looked through my pom.xml and made sure everything was there. I tried looking through my dependencies. I have my hibernate entity manager jar, and my hibernate jpa model gen jar. I've tried <provider>org.hibernate.ejb.HibernatePersistenceProvider</provider> and <provider>org.hibernate.ejb.PersistenceProvider</provider> I've also tried <jta-data-source>java:/jdbc/TestDB</jta-data-source> Lastly, I've tried different schema locations. One of them is commented out. Does anyone have any suggestions? Are there topics I can try googling?

I read 1.2.1. The persistence.xml file at http://docs.jboss.org/jbossas/docs/Server_Configuration_Guide/4/html/ch01s02s01.html

Btw, I'm using this hibernate version:
<groupId>org.hibernate</groupId> <artifactId>hibernate-jpamodelgen</artifactId> <version>1.3.0.Final</version>

I want to annotate my classes using Hibernate. Suppose you have a UML diagram were you have a Person as a superclass, and a Member who is a child class of Person. Then you have Member who can be composed of many EmergencyContacts:


EmergencyContact *----1<> Member ----> Person

The Member will have an attribute of type List<EmergencyContact>. The EmergencyContact will have a foreign key called Member_Id that points to an Id in Member. I'm going to annotate the inheritance relationship between Person with @MappedSuperclass and Person with @Entity. How would you annotate EmergencyContact? Is there a best way to design a database with a composite relationship like this, or is the choice debatable? Should I create another table by labeling EmergencyContact with @Entity?

James Boswell wrote:I agree with Campbell. Instead of calling:
person.getPersonAddress();
use:
person.getAddress().toString();
I would remove the getPersonAddress method altogether and implement similar logic in the toString method of Address.

Also, consider the following:
- Rename streetAddress to street within the Address class
- Addresses usually have a house number or name so perhaps houseNumber should be renamed. It should also be a String instead of an int in this case
- As you are using JPA annotations, you should probably consider adding @OneToOne to the address within the Person class

Thank you for the helpful comments!

Campbell Ritchie wrote:There is something wrong about having the address field as an Address object, and returning a String from the getAddress method. That breaches the principle that each class takes responsibility for itself. You should have a toString method overridden in the Address class, and the getAddress method should return an Address object.

Thank you! I was thinking that was poor OO design, but I didn't know the proper way to implement.

Thanks Junilu!

Bear Bibeault wrote:Where are you getting the NPE? Don't make people guess.

I think it is when I call the getPersonAddress(). I changed the method so that it would check to see if the Object was null. Now it looks like this, and it works.

public String getPersonAddress() { String actualAddress; // if address is null return no address if(address != null) { actualAddress = address.getHouseNumber() + address.getStreetAddress() + ", " + address.getCity() + ", " + address.getState() + " " + address.getZipCode(); }else{ actualAddress = "There is no address for this person"; } return actualAddress; }

On line 18 of the Person.class I tried to create an instance to deal with the null pointer exception. It was originally like this:

Address address;

I'm trying to create a demo website as an exercise. I want to be able to create a Person.class, and an Address.class. I want to associate an address to a person. I want to be able to create a Person without having to create an Address at the same time. I am getting a null pointer exception when I try to create a Person, because my Address is null. How should I design this?

package com.niyamit.services; import java.io.Serializable; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.SequenceGenerator; /** * Entity that represents a person * @author */ @Entity public class Person implements Serializable { // address associated with the person Address address = new Address(); @Id @GeneratedValue(strategy= GenerationType.AUTO, generator = "person_seq_gen") @SequenceGenerator(name="person_seq_gen", sequenceName="PERSON_SEQ") private Long id; @Column(name = "first_name", nullable = false) private String firstName; @Column(name = "last_name", nullable = false) private String lastName; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getFirstName() { return firstName; } public void setFirstName(String firstName) { this.firstName = firstName; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } public Address getAddress() { return address; } public void setAddress(Address address) { this.address = address; } public String getPersonAddress() { String actualAddress= address.getHouseNumber() + address.getStreetAddress() + ", " + address.getCity() + ", " + address.getState() + " " + address.getZipCode(); return actualAddress; } }

package com.niyamit.services; import java.io.Serializable; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.SequenceGenerator; /** * * @author */ @Entity public class Address implements Serializable { // person who is associated with the address Person person; @Id @GeneratedValue(strategy= GenerationType.AUTO, generator = "address_seq_gen") @SequenceGenerator(name="address_seq_gen", sequenceName="ADDRESS_SEQ") private Long id; @Column(name = "houseNumber", nullable = false) private int houseNumber; @Column(name = "streetAddress", nullable = false) private String streetAddress; @Column(name = "city", nullable = false) private String city; @Column(name = "state", nullable = false) private String state; @Column(name = "zipCode", nullable = false) private String zipCode; public Person getPerson() { return person; } public void setPerson(Person person) { this.person = person; } public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getCity() { return city; } public void setCity(String city) { this.city = city; } public String getState() { return state; } public void setState(String state) { this.state = state; } public String getZipCode() { return zipCode; } public void setZipCode(String zipCode) { this.zipCode = zipCode; } public int getHouseNumber() { return houseNumber; } public void setHouseNumber(int houseNumber) { this.houseNumber = houseNumber; } public String getStreetAddress() { return streetAddress; } public void setStreetAddress(String streetAddress) { this.streetAddress = streetAddress; } }

I am new to unit testing, and I am trying to test the handleCriteria(). I wanted to verify that root.get(Person_.firstName) on line 26 occurred. But, I am getting a null pointer exception. I am having a hard time figuring out how to mock things, and use when(). I have read some of the mockito docs, and other tutorials. According to stack,

"A unit test should test a single codepath through a single method. When the execution of a method passes outside of that method, into another object, and back again, you have a dependency.

When you test that code path with the actual dependency, you are not unit testing; you are integration testing. While that's good and necessary, it isn't unit testing."

How can I test handleCriteria, and get rid of this null pointer exception?


@Repository public class EntityManagerPersonDao extends EntityManagerDao<Person, Long> implements PersonDao { // sample sql query // SELECT p FROM Person p WHERE UPPER(p.FIRST_NAME) = UPPER('John') AND UPPER(P.LAST_NAME) = UPPER('Smtih'); @Override protected CriteriaQuery<Person> handleCriteria(final EnumMap<?, Object> searchCriteria, final Class<Person> type, final CriteriaBuilder criteriaBuilder) { // this object defines a particular query that will navigagte over one or more entities. final CriteriaQuery<Person> criteriaQuery = criteriaBuilder.createQuery(type); // type is a Person.class // create the query root from which all navigation originates. It's similar to the FROM clause final Root<Person> root = criteriaQuery.from(type); // type is a Person.class // Create ArrayList for storing Dynamic Query final List<Predicate> predicates = new ArrayList<Predicate>(); // Appends query if variable if not null if ( searchCriteria.get(Individual.IDNUM) != null ) { // find the tuples where Person_.id is == to the searchCriteria's IDNUM predicates.add(criteriaBuilder.equal(root.get(Person_.id), searchCriteria.get(Individual.IDNUM))); } if ( searchCriteria.get(Individual.FIRSTNAME) != null ) { // crit add search for fname predicates.add(criteriaBuilder.like(criteriaBuilder.upper(root.get(Person_.firstName)), searchCriteria.get(Individual.FIRSTNAME).toString().toUpperCase())); } if ( searchCriteria.get(Individual.LASTNAME) != null ) { // crit add search for lname predicates.add(criteriaBuilder.like(criteriaBuilder.upper(root.get(Person_.lastName)), searchCriteria.get(Individual.LASTNAME).toString().toUpperCase())); } criteriaQuery.select(root).where(criteriaBuilder.and(predicates.toArray(new Predicate[0]))); return criteriaQuery; } }

public class EntityManagerPersonDaoTest { @InjectMocks private EntityManagerPersonDao entityManagerPersonDao = new EntityManagerPersonDao(); @Mock private EntityManager entityManager; @Test public void testHandleCriteria_FirstNameAndLastName() { //////////////////////// // setup precondition // //////////////////////// //setup your test data for EnumMap argument final EnumMap searchCriteria = new EnumMap(Individual.class); //just use any enum so we don't have to define one //enter test data into EnumMap searchCriteria.put(Individual.FIRSTNAME, "John"); searchCriteria.put(Individual.LASTNAME, "Smith"); //setup type argument final Class<Person> type = Person.class; //setup criteriaBuilder argument final CriteriaBuilder criteriaBuilder = mock(CriteriaBuilder.class); //////////////////////// // setup query object // //////////////////////// final CriteriaQuery<Person> criteriaQuery = mock(CriteriaQuery.class); when(criteriaBuilder.createQuery(type)).thenReturn(criteriaQuery); final Root<Person> root = mock(Root.class); when(criteriaQuery.from(type)).thenReturn(root); final CriteriaQuery<Person> criteriaQueryResult = entityManagerPersonDao.handleCriteria(searchCriteria, type, criteriaBuilder); /////////////////////////////////////// // test that the methods were called // /////////////////////////////////////// // check to see if firstName predicates were added verify(root, atLeast(1)).get(Person_.firstName); }

This is supposed to be a good tutorial on JPA Criteria API:
http://www.ibm.com/developerworks/java/library/j-typesafejpa/

I will start reading that now.