Java Persistence Query Language-JPQL

JPQL :- Java Persistence Query Language is a query language in JPA which is similar to SQL

• JPQL queries on Entities and their properties not on tables and queries
• Syntax is similar to SQL
• JPQL queries are converted to SQL using JPQL query processor
• As the queries are based on Objects and attributes,they are independent of database
• JPQL provides SELECT,UPDATE and DELETE operations,but doesn't support INSERT as insert operations are taken care by em.persist(Entity) method
• EntityManager.createQuery() and EntityManager.createnamedQuery() are used to query JPQL

Entity class for database table Company

@Entity

public class Company implements Serializable {

private static final long serialVersionUID = 1L;

@Id

private BigDecimal sno;

private String mail;

private String name;

public Company() {

}

public String getMail() {

return this.mail;

}

public void setMail(String mail) {

this.mail = mail;

}

public String getName() {

return this.name;

}

public void setName(String name) {

this.name = name;

}

public BigDecimal getSno() {

return this.sno;

}

public void setSno(BigDecimal sno) {

this.sno = sno;

}

}

Using createQuery :-

Example :-In the below example we are fetching all the records from Company to List

EntityManagerFactory emf = Persistence

.createEntityManagerFactory("JPAExample_Toplink");

EntityManager em = emf.createEntityManager();

EntityTransaction et = em.getTransaction();

et.begin();

String selectQuery = "SELECT c FROM Company c";

Query rs = em.createQuery(selectQuery);

List<Company> list = rs.getResultList();

System.out.println("Printing Company Details");

for (Company company : list) {System.out.println("Name" + company.getName() + " mail id "+ company.getMail());

}

em.close();

If we want to pass values to query then we need to pass parameters.Below techniques are used for setting parameters

Named and Positional parameters :-

Named parameters :-

• Query parameters are prefixed with colon :
• query.setParameter() is used to set the values

Query query = em.createQuery("SELECT c FROM Company c WHERE c.name=:companyName");

query.setParameter("companyName", name);

Positional parameters :-

• Query parameters are prefixed with '?' and followed by numeric position
• Its similar to PreparedStatement in JDBC

Query query = em.createQuery("SELECT c FROM Company c WHERE c.name=?1");

query.setParameter(1, name);

Here 1 represent the position ? represent value to be set

Similarly we can use query.update() and query.delete() methods also