Querying Entities using JPQL and the Criteria API

When i started using EJB2.0 the only way to query the database is to use EJB QL(EJB Query Language) later on with the introduction of JavaEE 5.0 the EJB QL is Replaced by JPQL (Java Persistence Query Language). Now in today’s world, There are two primary Java EE technologies for querying a database: Java Persistence Query Language (JPQL) and the Criteria API. JPQL is similar in appearance and usage to SQL while the Criteria API provides a more type-safe and object-oriented entity querying capability.JPQL is based on the Hibernate Query Language (HQL) and is a subset of HQL. It is portable and allows queries to be executed against different data stores. This is in contrast to the use of a native query where the query is expressed in the native SQL of the data store.Another older, yet still viable technique is the Java database classes and interfaces found in the java.sql package.

Now i want to share some comparison of JPQL and CriteriaAPI:

JPQL:
String param = "Pav%"; Query query = em.createQuery( "select e from Employee e where e.firstName like :param"); query.setParameter("param", param); List<Employee> employees = query.getResultList(); for (Employee emp : employees){ System.out.println(emp.getFirstName()); }

Equivalent with Criteria API:
String param = "Pav%";

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Employee> query = cb.createQuery(Employee.class);

        Root<Employee> emp = query.from(Employee.class);
        query.where(cb.like(emp.<String>get("firstName"),
                            cb.parameter(String.class, "param")));

        TypedQuery<Employee> tq = em.createQuery(query);
        tq.setParameter("param", param);
        List<Employee> employees = tq.getResultList();

        for (Employee employee : employees){
            System.out.println(employee.getFirstName());
        }

Example of criteria API using count:
In JPQL

String param = "2000";

        Query query = em.createQuery(
             "select count(e) from Employee e where e.sal = :empSal");
        query.setParameter("empSal", param);
        Long count1 = (Long)query.getSingleResult();

        System.out.println("count : "+count1);

Equivalent with Criteria API

String param = "2000";

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Long> query = cb.createQuery(Long.class);

        Root<Employee> emp = query.from(Employee.class);
        query.select(cb.count(emp));
        query.where(cb.equal(emp.<String>get("sal"),
                             cb.parameter(String.class,"empSal")));

        TypedQuery<Long> tq = em.createQuery(query);
        tq.setParameter("empSal", param);

        Long count = tq.getSingleResult();

        System.out.println("count : "+count);

Example of criteria using distinct
In JPQL

String param = "dev%";

        Query query = em.createQuery(
             "select distinct e.disignation from Employee e where e.designation like :param");
        query.setParameter("param", param);
        List<String> desg= query.getResultList();

        for (String desgs : desg){
            System.out.println(desgs);
        }

Equivalent with Criteria API

String param = "dev%";

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<String> query = cb.createQuery(String.class);

        Root<Employee> emp = query.from(Employee.class);
        query.select(emp.<String>get("designation"));
        query.distinct(true);

        query.where(cb.like(music.<String>get("artisteName"),
                            cb.parameter(String.class, "param")));

        TypedQuery<String> tq = em.createQuery(query);
        tq.setParameter("param", param);

        List<String> designations = tq.getResultList();


        for (String desgs : designations){
            System.out.println(desgs);
        }

Happy Coding :)
Advertisements

Java Code to display all tables and their Relations in Database

From the past few months out of my interest i was working on designing a Database object migration tool, ie migrate DB objects from one Database to other eg:From Oracle to Mysql. in this i had a requirement when i specify a schema my tool should display all tables and their relation among themselves, so i had tough time writing code to get the meta data and and see their relations, after some time of research i wrote a generic code which can display all tables with their relationships. so that i can export specific set of tables to Databases.

Please find the below Code to the Objects and their Relations:

/**
*
*/
package com.spark.dbmigrationtool.utili;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
* @author Pavan Mantha
* @version 1.0
*/
public class DBObjectsMappingUtil {

/**
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
Connection connection = DBUtils.getConnection(“mysql”);
DatabaseMetaData metaData = connection.getMetaData();

/**    if using Oracle pass the schema name also in order
*  to reduce the iteration time if not all the DBobjects
*  will be compared with each other redusing system performance.
*
* metaData.getTables(catalog, schemaPattern, tableNamePattern, types)
*/
ResultSet rs = metaData.getTables(null, null, “%”, null);
List tableNamesList = new ArrayList();
Map tableMap = new HashMap();

/**
* get all tables and add them to list
*/
while (rs.next()) {
String tableName = rs.getString(3);
tableNamesList.add(tableName);
}
if (rs != null) {
rs.close();
}
// The Oracle database stores its table names as Upper-Case,
// if you pass a table name in lowercase characters, it will not work.
// MySQL database does not care if table name is uppercase/lowercase.

/**
* iterate through the list and compare the column types for constraints.
*/
for(int i=0;i<tableNamesList.size();i++){
for(int j=0;j<tableNamesList.size();j++){

/**
* since i and j at first time refer to same Table so leave it.
*/
if(i == j){
continue;
}

String primaryTable = (String) tableNamesList.get(i);
String foreignTable = (String) tableNamesList.get(j);

ResultSet rs1 = metaData.getCrossReference(null, null, primaryTable.toUpperCase(), null, null, foreignTable.toUpperCase());

/*Itterate through the Objects and their relations*/
while (rs1.next()) {

DBObject primaryDBTable = null;
DBObject foreignDBTable = null;

String primaryTableName = rs1.getString(“PKTABLE_NAME”);
String foreignTableName = rs1.getString(“FKTABLE_NAME”);

if (tableMap.get(primaryTableName) != null) {
primaryDBTable = (DBObject) tableMap
.get(primaryTableName);
} else {
primaryDBTable = new DBObject();
primaryDBTable.setTableName(primaryTableName);
}

if (tableMap.get(foreignTableName) != null) {
foreignDBTable = (DBObject) tableMap
.get(foreignTableName);
} else {
foreignDBTable = new DBObject();
foreignDBTable.setTableName(foreignTableName);
}

System.out.println(“Primary Key Table Name :”
+ rs1.getString(“PKTABLE_NAME”)
+ ” Primary Key Column :”
+ rs1.getString(“PKCOLUMN_NAME”));
System.out.println(“Foreign Key Table Name :”
+ rs1.getString(“FKTABLE_NAME”)
+ ” Foreign Key Column :”
+ rs1.getString(“FKCOLUMN_NAME”));
System.out
.println(“——————————————————————–“);
}
if (rs1 != null) {
rs1.close();
}
}
}

connection.close();
}

}
Bellow two classes are like helper classes for the main class.

Note:need to change the parameters of ur db here
Please find the DBUtils Class here.

/**
*
*/
package com.spark.dbmigrationtool.utili;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
* @author Pavan Mantha
* @version 1.0
*/
public class DBUtils {

private static Connection connection = null;

public static Connection getConnection(String DBname) throws SQLException, ClassNotFoundException{

if(connection == null){
if(DBname.equalsIgnoreCase(“Mysql”)){

Class.forName(“com.mysql.jdbc.Driver”);
connection = DriverManager.getConnection(“jdbc:mysql://localhost:3306/test”, “username”, “password”);
}else if(DBname.equalsIgnoreCase(“oracle”)){

Class.forName(“oracle.jdbc.driver.OracleDriver”);
connection = DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”, “username”, “password”);
}

}else{
return connection;
}

return connection;
}

}
Please find the DBObject class here.

/**
*
*/
package com.spark.dbmigrationtool.utili;

import java.util.Map;

/**
* @author Pavan Mantha
* @version 1.0
*/
public class DBObject {

private String tableName;

/**
* @return
*/
public String getTableName() {
return tableName;
}

/**
* @param tableName
*/
public void setTableName(String tableName) {
this.tableName = tableName;
}

public void addRelations(DBObject primaryDBTable,DBObject foreignDBTable,Map<Object, Object> tableMap,String primaryTableName,String foreignTableName) {
// TODO Auto-generated method stub
tableMap.put(foreignTableName, foreignDBTable);
tableMap.put(primaryTableName, primaryDBTable);

}
}
Note:for mysql the code works fine, but for Oracle if DB is too large there is some performance issue,looking into it. will post once resolved.

please follow the comments for more readability.

Follow by blog to know for more innovative Java tweets. 🙂

Happy Coding !