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]
Tag:JPA, Postgres, PostgreSQL