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

Composite primary key -JPA

Composite Primary Key in JPA:-

Composite Primary Key :- Having more than one column as primary key in a data base table is called composite primary key

If a database table has composite primary key then how to represent it in JPA Entity bean?

@Id represents a primary key field

There are two ways of declaring composite primary key

1. @IdClass
2. @EmbeddedId with @Embeddable

Suppose we have a table where we depart_name and coll_name are as composite primary key

@IdClass :- using this approach we need to create a separate class,which holds the primary key variables.This class will be referred by entity using @IdClass(CollegeDeptPK.class)

public class CollegeDeptPK implements Serializable{

/**

*

*/

private static final long serialVersionUID = 1L;

private String collname;

private String deptname;

// getter and setters are generated here

}

@Entity

@IdClass(CollegeDeptPK.class)

public class Collegedept implements Serializable {

private static final long serialVersionUID = 1L;

@Id

private String collname;

@Id

private String deptname;

public Collegedept() {

}

public String getCollname() {

return this.collname;

}

public void setCollname(String collname) {

this.collname = collname;

}

public String getDeptname() {

return this.deptname;

}

public void setDeptname(String deptname) {

this.deptname = deptname;

}

}

In the above example Collegedept refers CollegeDeptPK .

CollegeDeptPk has two variables collname and deptname which are composite priamry key

the same variables are declared in our entity class using @Id annotation

The primary key class must contain the fields that match the priamry key attributes in the entity in both name and type

Collegedept collegedept=em.find(Collegedept.class,"Primary Key");

So we need to give a primary key here ,so then how can we proceed in this scenario?

Create an object of CollegeDeptPK ,set the variable values and pass this object reference to find method.

CollegeDeptPK collegedeptpk=new CollegeDeptPK();

collegedeptpk.setCollname("GPREC");

collegedeptpk.setDeptname("EEE");

Collegedept collegedept=em.find(Collegedept.class,collegedeptpk);

Drawbacks :- If composite primary key is implemented using @IdClass then there is duplication of declaration of primary keys in both the cases.

Using @EmbeddedId will solve the above problem

@EmbeddedId :-

• The priamry key class declaration is annotated @Embeddable and it has all the primary key declarations
• In entity class instead of using @Id,@EmbeddedId is used.It will be annotated on the top of reference variable
  @EmbeddedId
  priavte CollegeDeptPK collegeDeptPk;
• The Embeddable priamry key class must be configured in persistence.xml file similar to Entity class
  <class>com.CollegeDeptPK </class>

Primary Key class :-

@Embeddable

public class CollegeDeptPK implements Serializable{

/**

*

*/

private static final long serialVersionUID = 1L;

private String collname;

private String deptname;

public String getDeptname() {

return deptname;

}

//setter and getters

}

Entity class :-

@Entity

public class Collegedept implements Serializable {

private static final long serialVersionUID = 1L;

@EmbeddedId

private CollegeDeptPK collegedeptpk;

public Collegedept() {

}

//getter and setters

}

Take away points :-

• There are two ways of declaring composite primary keys

@IdClass
@EmbeddedId with @Embeddable

• @EmbeddedId is preferred over @IdClass
• Using @IdClass will have duplication of primary declarations

Persistence.xml file in JPA

Persistence.xml 

How will JPA API (EntityManager)  come to know which database to connect, connection parameters,transaction types,logging level etc.? Persistence.xml is a standard configuration file which gives complete flexibility to configure EntityManager

Persistence.xml :-

1. is to be created under META-INF/persistence.xml
2. A persistence.xml can contain one or more unique persistence unit names
3. Persistence units are unique values used by EntityManagerFactory/Entitymanager
4. Entities ,Connection parameters,logging level ,transaction types etc are declared in persistence.xml

EntityManagerFactory emf =Persistence.createEntityManagerFactory("JPAExample_Toplink");

The following persistence.xml defines one persistence unit with name JPAExample_Toplink

<persistence version="2.0"

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">

<persistence-unit name="JPAExample_Toplink"

transaction-type="RESOURCE_LOCAL">

<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>

<class>model.Company</class>

<class>model.Department</class>

<class>model.DeptEmpl</class>

<properties>

<property name="javax.persistence.jdbc.password" value="admin" />

<property name="javax.persistence.jdbc.user" value="system" />

<property name="javax.persistence.jdbc.driver" value="oracle.jdbc.OracleDriver" />

<property name="javax.persistence.jdbc.url" value="jdbc:oracle:thin:@localhost:1521:XE" />

<property name="eclipselink.logging.level" value="INFO" />

<property name="eclipselink.ddl-generation" value="create-tables" />

</properties>

</persistence-unit>

</persistence>

Eclipse automatically creates persistence.xml file when we create a JPA project.It also provides an persistence xml file editor which reduces the manual effort of writing the file.

We can edit :-

Connection

Managed classes

mapping files

Query timeouts

                         Happy Learning

Please provide your valuable comments on this article and share it across your network.

Contact me @ sudheer@javarecent.com orjavarecent@gmail.com

JDBC statements-Prepared,Callable

Hi All,

In this post we will discuss about different connection statements used in JDBC.

There are primarily three types of connection statements:-

1. Statement
2. PreparedStatement
3. CallableStatement

Lets discuss each one of them in detail

Statement interface :- is used to query database from Java,it provides different methods like executeQuery(), executeUpdate() etc.

• Resultset executeQuery(String) :- is used to execute SQL SELECT queries,returns ResultSet.
•  Int executeUpdate(String) :- is used to execute SQL(INSERT,UPDATE,DELETE) queries,returns int value indicating number of rows affected
• boolean execute(String) :- returns true if a resultSet is retrieved successfully, generally used to execute DML statements
• Statement query gets compiled and executed every time when it is called
• It is used in case where there is no repetition of a query

Syntax :- statement.executeQuery("select mail from COMPANY where name="+"'"+uName+"'");

PreparedStatement interface :-

• Prepared statement queries are precompiled and the fetch plan will be stored in cache,so for subsequent requests only execution will happen
• They are faster than Statement queries because Statement queries will get compiled every time
• Used in case there is repetition of a query
• Prepared statements can be parameterized,parameterization of query values is done by using '?' - place holder in setXXX() method
• Prepared Statement prevents SQL injection
  For detailed explanation on SQL injection with example refer SQL injection

Syntax :-There are two ways of using prepared statement

1.PreparedStatement preparedStatement = connection.prepareStatement("select * from COMPANY where sid="+"'"+emailId+"'");

2. PreparedStatement preparedStatement = connection.prepareStatement("select * from COMPANY where sid=? AND name=?");

preparedStatement.setInt(1, 101);

preparedStatement.setString(2, "Google");

setXXX() -- takes two parameters first one tells position of the value to be placed,

this starts from 1 .

Second parameter is the respective value to be passed for ?-place holder

The first type of declaration will not prevent SQL injections because we are hard coding the where clause with a variable.

Second type of declaration will prevent SQL injection because all the parameters passed will be escaped by JDBC

CallableStatement :-

• is an interface used to execute stored procedures
• There are three types of parameters for a stored procedure
  • IN :- used to provide input to stored procedure,this is set by using setXXX()
  • OUT :- is used to hold values from a procedure,this is retrieved using getXXX()
  • INOUT :- acts as both input and output parameters

Code snippets :-

Procedure

create or replace procedure "P_COMPANY" (pname IN VARCHAR2, pemailid OUT VARCHAR2)

is begin

select mail into pemailid from COMPANY where name=pname;

end;

Procedure P_COMPANY takes two parameters and select query put  the  mail Id into pemailid(OUT parameter)

//CallableStatement usage

1 CallableStatement cs = connection.prepareCall("{call P_COMPANY(?,?)}");

2 cs.setString(1, name);

3 cs.registerOutParameter(2, java.sql.Types.VARCHAR);

4 cs.execute();

5 String mailId = cs.getString(2);

• Line 1 we are calling the the procedure P_COMPANY
• setting the IN parameter using setString(1, name);
• registering OUT parameter ,we have to mention sql data type also
• execute() or executeQuery() or executeUpdate() can be used to execute the procedure
• Line 5 getting the output from pemailid OUT variable

Complete code

JDBCConnection.java

public class JDBCConnection {

public void connectDB(String name) throws ClassNotFoundException,

SQLException {

Connection connection = null;

;

CallableStatement cs = null;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

// Creating connection object

connection = DriverManager.getConnection(

"jdbc:oracle:thin:@localhost:1521:xe", "system", "admin");

cs = connection.prepareCall("{call P_COMPANY(?,?)}");

cs.setString(1, name);

cs.registerOutParameter(2, java.sql.Types.VARCHAR);

cs.execute();

String mailId = cs.getString(2);

System.out.println("Required Mail id" + mailId);

} finally {

try {

connection.close();

cs.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

public static void main(String[] args) {

JDBCConnection jdbc = new JDBCConnection();

try {

jdbc.connectDB("Yahoo");

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

Output :- Required Mail id yahoo@yahoo.com

Key Points :-

• Statement is used for non-repeating queries
• Prepared Statement will be pre-compiled,on subsequent requests the query will get executes
• Action/Execution plan is stored in cache which helps in faster execution
• Prepared Statements will prevent SQL injections
• Prepared Statements allows parameterization of values by using place holder [?]
• Procedure can be executed using callable interface
• IN parameter is set using setXXX()
• OUT parameter is retrived by first registering the parameter and using getXXX()

                          Happy Learning

Please provide your valuable comments on this article and share it across your network.

Contact me @ sudheer@javarecent.com or admin@java-recent.com

Primary key generation strategies in JPA(Java Persistence API)

Hi,

In this post we will discuss about generating primary key values in JPA(Java Persistence API).In general there are two strategies.A sequence number in JPA is a sequential id generated by the JPA implementation and automatically assigned to new objects

Natural id :- is like using telephone number,SSN number etc as primary keys in tables

Generated id :- where primary key value is generated by application/framework

Generated id is preferably used because data of Natural Id may change with time

There are different ways of generating primary key id's in JPA

• Identity
• Table
• Sequence
• Auto

Each one of it is described below with examples

• IDENTITY :-Specifies the use of database identity column.@GeneratedValue annotation indicates that identifier value should be automatically created, and the specified strategy of IDENTITY indicates that an identity column should be used to generate the identifier
  
  
      @Entity
      public class Company implements Serializable {
      private static final long serialVersionUID = 1L;
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      private BigDecimal sno;
  
  

• TABLE :- There are two ways of generating sequence using TABLE
  
  
  • Using default strategy:-JPA will create a default table for identifier generation,Specify the strategy of TABLE in the @GeneratedValue. JPA will create default table during schema generation at run time
    @Entity
       public class Company implements Serializable {
       private static final long serialVersionUID = 1L;
       @Id
       @GeneratedValue(strategy = GenerationType.TABLE)
       private BigDecimal sno;
  
  
  • User defined table :- We can map an existing table or tell JPA to create a table with user defined name columns etc. Generating or specifying a table will be done by using @TableGenerator annotation,after declaring the required details then we can use @GeneratedValue
    
    
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @TableGenerator(name = "javarecent_seq", table = "GEN_ID", pkColumnName = "NAME_ID", valueColumnName = "VAL_ID", pkColumnValue = "GEN_INV")
    private BigDecimal sno;

 

NAME_ID	VAL_ID
GEN_INV	<Recent_Generated_Value>

When ever a new value is required,value from VAL_ID will be picked and incremented and send that value to JPA to use.we can specify the allocationSize = "value to be incremented"


• Sequence generator :- will create a sequence for generating unique values.Like TABLE generator JPA provides two ways
  • Default Sequence :- JPA can generate unique values for a persistence object.JPA will generate/create a default sequence object during run time and will be used for genearting unique values
    @Entity
    public class Company implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private BigDecimal sno;

• • User defined sequence:- If we want to use specific sequence already created or to create and use a new one can be possible by using @SequenceGenerator annotation
    @Entity
    public class Company implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE,generator="my_seq")
    @SequenceGenerator(name = "my_seq" , sequenceName="pk_generator", allocationSize=3)
    private BigDecimal sno;

In above example a example a sequence is generated if not present with name pk_generator and each time a new value will be created with an increment of 3 as specified in allocationSize ,this sequence is named as my_seq and used in @GeneratedValue

• AUTO :- Indicates that JPA will pick the appropriate generation strategy for a particular database,there is no gaurentee that generated values will be in sequence,by default TABLE strategy is picked as this is the mosdt portable approach which is supported by all databases
      @Entity
      public class Company implements Serializable {
      private static final long serialVersionUID = 1L;
      @Id
      @GeneratedValue(strategy = GenerationType.AUTO)
      private BigDecimal sno;

Common Problems
Error when allocating a sequence number.

1. Errors such as "table not found","invalid column" can occur if you do not have a SEQUENCE table defined in your database, or its schema does not match what you have configured, or what your JPA provider is expecting by default. Ensure you create the sequence table correctly, or configure your @TableGenerator to match the table that you created, or let your JPA provider create you tables for you (most JPA provider support schema creation). You may also get an error such as "sequence not found", this means you did not create a row in the table for your sequence. You must insert an initial row in the sequence table for your sequence with the initial id (i.e. INSERT INTO SEQUENCE_TABLE (SEQ_NAME, SEQ_COUNT) VALUES ("EMP_SEQ", 0)), or let your JPA provider create your schema for you.
2. If there is any issue using TABLE sequence creation in JPA.We need to first change/check the persistence.xml file
   Specify <property name="eclipselink.ddl-generation" value="create-tables"/>

References :- http://en.wikibooks.org/wiki/Java_Persistence/Identity_and_Sequencing

                         Happy Learning

Please provide your valuable comments on this article and share it across your network.

Contact me @ sudheer.reddy@live.com or admin@java-recent.com