Create a Simple JPA Query

Create a simple PostgreSQL Database Table

psql mydb

mydb=# create user john with password ‘secret’;
CREATE ROLE

mydb=# create table person( personid serial, name varchar(30), age integer, height decimal (4,2) );
NOTICE: CREATE TABLE will create implicit sequence “person_personid_seq” for serial column “person.personid”
CREATE TABLE

mydb=# grant all on person to john;
GRANT

mydb=# GRANT USAGE, SELECT ON SEQUENCE person_personid_seq to john;
GRANT

mydb=# insert into person(name,age,height) values ( ‘John Dunning’, 21, 1.8 );
INSERT 0 1

mydb=# select * from person;
name | age | height
————–+—–+——–
John Dunning | 21 | 1.80
(1 row)

Create a JPA Maven Project

This was covered in a previous post: Create a JPA Maven Project

Create JPA Entities

• Project -> New -> Other -> JPA -> JPA Entities from Tables
• Next (Select Tables)
• Check Person table
• Next (Table Associations)
• Next (Customize Defaults)
• Key Generator -> none
• Change the Package – e.g. com.skills421.jpa.entities
• Next (Customize Individual Entities)
• Finish

This will create a Person.java entity and will add the class for the Person entity to the Persistence.xml file

Edit the Person Entity

Add the attributes @Id and @GeneratedValue just above the private Integer personid; declaration.

The details are as follows:

[sourcecode language=”java”]
@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE,generator=”customer_seq”)
@SequenceGenerator(name=”customer_seq”,sequenceName=”person_personid_seq”, allocationSize=1)
private Integer personid;
[/sourcecode]

Create a Main Class

create the main methods as follows

[sourcecode language=”java”]
public static void main(String[] args)
{
EntityManager em = Persistence.createEntityManagerFactory(“mavenjpa”).createEntityManager();

// Remove Jon Doe

Person jonDoe = em.createQuery(“select p from Person p where p.name = ?1”,Person.class)
.setParameter(1, “Jon Doe”).getSingleResult();

if(jonDoe!=null)
{
em.getTransaction().begin();
em.remove(jonDoe);
em.getTransaction().commit();
}

List<Person> people = em.createQuery(“select p from Person p”, Person.class).getResultList();

System.out.println(“\n\nBEFORE”);

for (Person p : people)
{
System.out.println(p.getName());
}

Person newPerson = new Person();
newPerson.setName(“Jon Doe”);
newPerson.setAge(23);

if(!people.contains(newPerson))
{
em.getTransaction().begin();
em.persist(newPerson);
em.getTransaction().commit();
}

people = em.createQuery(“select p from Person p”, Person.class).getResultList();

System.out.println(“\n\nAFTER”);

for (Person p : people)
{
System.out.println(p.getName());
}

}
[/sourcecode]