Results 1 to 1 of 1
  1. #1
    Java Exam is offline Member
    Join Date
    Dec 2011
    Posts
    64
    Rep Power
    0

    Default Tutorial: Review of Java Persistence Query Language for Component Developer Exam I

    In this series of articles we will look more in depth at querying focusing on JPA Query Language, native Structured Query Language (SQL) and the Criteria API in order to prepare you for the Component Developer Exam. In the first part we will look at the Query API and Java Persistence Query Language Statement Types.

    The Query API and Java Persistence Query Language (JPQL)

    One of the core features of relation databases is queries. It is a feature that allows a relational databases to generate complex reports, make calculations, as well as pull other information about related objects from persistence storage. In Java Persistence, queries are done using the following tools:
    • Java Persistence API Query Language (JPQL) - a declarative query language that is tailored to work with Java objects. It is similar to SQL in form.
    • Structure Query Language (SQL) - the programming language designed for managing relational databases
    • The Criteria API - used to define queries for entities and their persistent state by creating query-defining objects


    The principal tool for doing queries is JPA QL. To execute queries with JPA QL, it is necessary to reference the properties and relationships of the entity beans instead of the tables and columns that would be used with SQL. The EntityManager uses the information that is provided in the mapping metadata whenever a JPA QL query is executed to automatically translate it into native SQL. It is this generated native SQL that is executed via the JDBC driver on the database. As JPA QL is part and parcel of Java Persistence, it is portable across the numerous database vendorsĎ implementation. This is because the link between the Java objects (entities) and the database is provided by the EntityManager and it is this that handles the conversion to SQL. So when you want to retrieve entities and all related data, you can either use the EntityManagerís find method providing the entityís primary key for identifying the instance, or write a query in JPQL, or finally querying using native SQL on the underlying database.

    The Query API

    The Query API is a flexible query language that plays a key role in Object Relational Mapping. Is is comprised of the EntityManager interface methods which are used for creating query instances, the Query interface methods that are used for defining and executing queries and the Java Persistence Query Language (JPQL). In deference to the query capabilities in EJB 2 which were extremely limited. EJB 3 has significantly improved on the query capabilities of entities compared to itís ancestor, entity beans. For example, with simple queries, one can easily manage by using the EntityManager.find method to retrieve entities by their ID or primary key. Now when you need to make more powerful queries. The query API provides developers with all the tools necessary to write custom queries to retrieve either one or a collection of entities.

    The EntityManager interface provides a number of methods for creating queries to retrieve entities. You can use the Query interface methods along with the methods of the EntityManager to perform the query definition, parameter binding, execution as well as pagination. With the JPA Query API you can define queries using either JPQL or SQL. It is best to use in a majority of cases to use JPQL with the Query interface instead of SQL. This is primarily because a JPA query returns entities while SQL queries returns database records. A figure showing the various elements of JPA and the Query API are shown below:

    Tutorial: Review of Java Persistence Query Language for Component Developer Exam-a8-jpql-architecture.jpg
    Figure: JPA Architecture and Query API

    As we dive deeper into the query API, you will start to realize that building que- ries to retrieve entities is often the most challenging task of building any enter- prise applications, and almost every aspect of an application requires some data retrieval. The query API supports building and using queries in several ways, as youíll learn in the next few sections. Depending on what you are trying to accomplish, you may devise some interesting ways to utilize queries.
    • Below are the steps that you need to follow to create a JPA query. These are:
    • Obtain an instance of an EntityManager
    • Create a query instance
    • Execute the query
    • Retrieve the results (entities)


    There are very similar to the steps of a traditional JBDC query. The key differences being in with JPA you obtain an instance of a EntityManager while with JDBC, you obtain a connection to the database and with JPQL, you manipulate entities and their relationships while with JDBC you manipulate tables and their relationships.

    Query Types

    There are two types of queries that are supported by the query API:
    • Named queries - these are stored queries that can be reused when needed.
    • Dynamic queries - these are queries that are created on the fly.

    Letís review both types.

    Named queries are similar to predefined SQL statements. They are saved and reused. the benefit of this is that when are statement is needed to be used several times within an application, there is no need for it to be recreated. Instead, you just provide the name of the query, pass in any parameters and execute the query.

    Dynamic or ad hoc queries are queries that are defined on the moment. If you want for your users to build queries on demand based on user inputs or some conditions in your application logic, then you have the need for dynamic queries in your application.

    Queries are basically the same irrespective of the type used. Consider if you want to find all the todos that are high priority. The code for doing this will look like the following:

    Java Code: Dynamic Query Example
    @PersistenceContext em;
    ...
    public List findAllTodos() {
       Query query = em.createQuery("SELECT t FROM Todo t"); 
       ...
       return query.getResultList(); 
    }
    Here, we use dependency injection, to obtain an instance of an EntityManager and then we create an instance of a Query object. This is for querying entities using the EntityManager.createQuery method by passing it the relevant query string. Once the query instance is obtained, the last step is to return the results list using the getResultList method. Note that although here we have used JPQL to query the entities, we could have equally used native SQL for this query. If you wanted to create a named query, then you would have used the aptly named, createNamedQuery method. You would need to pass to it a query object that has already been created rather than a query string. Finally for your named or dynamic queries, you can use both JPQL or SQL.

    Benefits of Named Queries

    In order to use a named query it must be defined either using the @NamedQuery annotation or by defining it in the XML file used for object/relational mapping metadata. You use the name that you have given to the named query in order to access it. A named query is accessed by its name when the instance is created. There are a number of benefits that can be realized from using named queries. These are:
    • Named queries are reusable across multiple models.
    • Named queries improve the quality of your code since queries are encapsulated in clearly defined areas.
    • Named queries provide enhanced performance since they can be optimized during preparation and then reused.
    • Named queries facilitate enhance encapsulation of query language separate from java language.


    We will focus on using it with metadata annotations for brevity. If we wanted to create a named query on the TodoList entity to retrieve all TodoLists by passing a TodoList name. In order to do this, we will use the @NamedQuery annotation. The listing of this is shown below:

    Java Code: Named Query Example
    @Entity 
    @NamedQuery(
       name = "findAllTodos", 
       query = "SELECT l FROM TodoList l WHERE l.name
    		LIKE :name ")
    public class TodoList implements Serializable, 
    			Comparable<TodoList> { 
       ... 
    }
    If you want to create multiple queries in an application, you can use the @NamedQueries annotation to specify multiple named queries as shown below:

    Java Code: Example of Multiple Named Queries
    @Entity 
    @NamedQueries({
       @NamedQuery( 
          name = "findTodoByName", 
          query = "SELECT l FROM TodoList l WHERE l.name
                        LIKE :name order by l.listId"
    ), 
    @NamedQuery(
       name = "findTodoByRss", 
       query = "SELECT l FROM TodoList l
    	       WHERE l.rssAllowed = ?1" 
    )})
    @Table(name = "TODOLIST")
    
    public class TodoList implements Serializable, 
    			Comparable<TodoList> { 
       ...
    }
    Note how the @NamedQuery is nested within the @NamedQueries annotation. You can add as many queries as you need for the entity being declared. Letís move on to the execution of a query.

    Executing the queries

    There are the steps to running a query in EJB 3:
    1. Obtain an instance of an Entity Manager - The EntityManager instance can be injected in the case of a container-managed EntityManager or be created by a EntityManagerFactory for an application-managed EntityManager. We will step through creating a query instance and then we will look at the methods in the Query interface designed for executing queries.
    2. Creating a query instance - In order to execute a named or dynamic query with JPQL, after you have an instance of an EntityManager, you need to create a query instance to retrieve persistent data. You use the EntityManager interface to create a query instance. Below are the methods for creating native SQL queries. Some of the methods for creating query instances use JPQL and others use with native SQL queries. 

      Tutorial: Review of Java Persistence Query Language for Component Developer Exam-a8-entitymanagerquerymethods.jpg
      Table: Key EntityManager Create Query Methods

    3. Or Creating a named query instance - You create named query instances in any component that has access to the persistence unit in which the entity belongs. Remember that named queries are globally scoped. You need to open an EntityManager instance in order to create a named query instance. To use the named query, invoke the EntityManager.createNamedQuery method, passing in the name of the named query as the parameter. The instance of the EntityManager will handle of all the details of fetching the named query and returning a reference that we can assign to the query object. So in the previous listing, we created the stored query, we stored the named query, findAllTodos in the Todo entity. No to create a named query from that stored query we do the following:

Query query = em.createNamedQuery("findAllTodos");

    4. Or create a dynamic query instance - As long as there is an instance of the EntityManager, you are able to create dynamic queries. You can do this in a number of objects or modules including session beans, message driven beans or web applications. You donít even need to have an EJB Container. In our last article, Tutorial: Review of EntityManager Context for the Component Developer Exam, we showed how you can access an EntityManager from outside the container as well as provide a transaction context for your persistence operations. Please review the article if you want to know more on how to access an EntityManager from outside of the container. To create a dynamic query, we use the EntityManager.createQuery method to create a dynamic query. Remember to pass it a valid JPQL statement. As shown in our previous article, the EntityManager can be either is a container- or application-managed. An example of a dynamic query with a valid JPQL statement is shown below:

Query query = em.createQuery("SELECT i FROM Item i");



    Using the Query Interface

    Now letís dive into the execution of the query. The Query interface provides several methods for executing a query. You can also set parameters for a Query instance, set the pagination properties for the result set, control the flush mode, as well as other functions. It is important to remember that the Query interface makes no differentiation between JPQL and native SQL. You can use either JPQL or native SQL with the same interface. There methods that you need to use for setting parameters, executing a query or for taking the result set and iterating through it. In the table below, these methods are shown:
    Tutorial: Review of Java Persistence Query Language for Component Developer Exam-a8-querymethodinterface.jpg
    Table: Query Interface Methods

    Most of these methods are straightforward to use. Below we provide an example of how to apply a number of the methods in executing a query using the interface as well as preparing for handling the result set. We look to obtain a list of Todos that have a certain priority. We decide to limit the number of items to 10 and we position the first item to be returned on the first item. As the default for the first result is returned it is not necessary to set the first item to 1. This is done in order to demonstrate the method. You can set the first item at another number such as 5 or 2 if you choose.

    Java Code: Obtaining Results from Named Query
    query = em.createNamedQuery("findTodosByPriority"); 
    query.setParameter("priority", priority);
    query.setMaxResults(10); 
    query.setFirstResult(1); 
    List todos = query.getResultList();
    Setting Parameters for A Query

    There are two means of defining a parameter in a JPQL WHERE clause. In both cases you use the setParameter method to specify the value for the parameter but depending on how you specify the parameter, it will determine how you use the method. In the first case, if you specify the JPL in the following manner:

    Java Code: Example of Query with Positional Parameter
    SELECT t FROM Todo t WHERE t.priority = ?1
    In this statement we have defined the parameter by using a number, (?1). This is what is termed a positional or numbered parameter. In this case, we need to set the parameter in the following manner:

    Java Code: Setting a Positional Parameter
    query.setParameter(1, 100.00);
    You could have multiple parameters set this way. For example, if you wanted to retrieve all Todos items with one or another priority, you could do something like the following: In some cases youíll want to specify multiple parameters for a query. Say you want to retrieve all Todos with a priority that falls within a particular range:

    Java Code: Query with Multiple Positional Parameters
    SELECT t FROM Todo t WHERE i.priority = ?1 OR i.priority = ?2
    You would then set the two parameters in the following manner:

    Java Code: Setting Positional Parameters
    query.setParameter(1, 1); 
    query.setParameter(2, 2);
    Here we set the first parameter in position 1 to 1 as the first possible value and the second value is done by setting the parameter in position 2 to 2.
    The second means of defining parameters is to use names. This is termed named parameter. I would recommend using named parameters as the drastically improve the readability of code especially when there might be multiple parameters or if another developer is reading your code. An example of this using the previous query is shown below:

    Java Code: Query with a Named Parameter
    SELECT t FROM Todo t WHERE t.priority = :priority
    Here the named parameter, priority starts with : rather than the ? which is used for number parameters. You set a named parameter by passing the name of the parameter as the first argument when calling the setParameter method on the query in the following manner:

    Java Code:
    query.setParameter("priority", 1);
    Here the named parameter priority is set to a value of 1. For a developer who is looking at this code for the first time, itís alot easier to read.

    Polymorphic Queries

    As Java supports polymorphism so does JPQL queries. What this means is that a JPQL Query will not only retrieve a parent entity in an entity hierarchy, but all of its subclasses as well. So consider the following example. We have the following entity hierarchy:

    Tutorial: Review of Java Persistence Query Language for Component Developer Exam-a8-animalclasshierarchy.jpg
    Figure: Class Hierarchy for Animal

    In this example, any query to retrieve Animal entities will retrieve its subclasses, such as Cat and Dog. If we have a query like the following:

    Java Code: Query with a Named Parameter
    SELECT a FROM Animal a WHERE a.group LIKE :animalGroup
    The query will retrieve all instances of Cat and Dog and any other animal that matches the query condition. We manage a polymorphic query in client code in the following manner:

    Java Code: Example of a Polymorphic Query
    query = em.createNamedQuery("findAnimalByGroup"); 
    query.setParameter("animalGroup", animalGroup); 
    List<Animal> animals = query.getResultList();
    
    Iterator i = users.iterator(); 
    while (i.hasNext()) {
             Animal animal = (Animal) i.next(); 
             System.out.print("Animal:"+emp.getAnimalId()); 
             if (animal instanceof Dog) {
                 Dog dog = (Dog) animal; 
                 System.out.println("Dog:" +
                    dog.bark());
             }
             else if (animal instanceof Cat) {
                Cat cat = (Cat) animal; 
                System.out.println("Cat:" +
                    cat.meow());
             }
    }
    He we use the instanceof keyword to test animal. This could also be done in a generic manner by creating an operation called makeSound() in all entities. In our example, you can easily convert the operations to be polymorphic by adding a getRate method in all entities. In that case, the code would look like the following:

    Java Code: Iterating through Different Animal Types
    Iterator i = animals.iterator(); 
    while (i.hasNext()) {
       Animal animal = (Animal)i.next(); 
       System.out.print("Animal:" + emp.getAnimalId()); 
       System.out.println(animal.makeSound());
    }
    How to Retrieve an Entity

    To retrieve a single entity instance you use the Query.getSingleResult method. This must align with the result of the query. if the query returns multiple instances, your call to this method will throw a NonUniqueResultException. If there is no result, then the persistence provider will throw NoResultException. When the exceptions are thrown, it will not rollback the active transactions. You will need to handle the exceptions yourself. An example of how to manage all of this is shown below:

    Java Code: Example of Handling Query Exceptions
    try { 
              ...
              query.setParameter(1, 1458); 
              TodoList todoList = (TodoList)query.getSingleResult();
               ...
          }catch (NonUniqueResultException ex) { 
               handleException(ex);
          } catch (NoResultException ex) {
               handleException(ex);
          }
    }
    Finally, you donít need an active transaction in order to retrieve an entity, but if there is no active transaction, the entity that is retrieved will be detached after retrieval.

    How to Retrieve a Collection of Entities

    The majority of queries will retrieve more than one entity instance in a result set or result list. You use the Query interfaceís getResultList method to retrieve the results of a query. An example of retrieving a result list is shown below:

    Java Code: Example of Retrieving Query Results
    query.setParameter("priority", priority) 
    query.setParameter("user", user)
    List items = query.getResultList();
    In this example the first parameter is the priority of the Todo list and the second parameter is the user. The getResultsList method will return a list of Todos that meet the criteria specified. Similar to retrieving one entity, it is best to use an active transaction unless you want all the entities in the result set to be detached after retrieval.

    How to Page Through a Result List

    Once a query has retrieved hundreds of entities, the processing of the retrieved list of entities can be challenging. Fortunately the JPA provides the facilities for paginating through the result set. An example of how to specify the pagination property in a query:

    Java Code: Example of Setting Pagination for Results
    query.setMaxResults(30);
    query.setFirstResult(0); 
    List items = query.getResultList();
    We use the following two methods in order to specify the pagination. First the setMaxResults method is used to specify the maximum number of entities being retrieved in the result list, and second the setFirstResult method is used to set the position of the first result in the ResultList. In the previous listing, there are the first 30 entities retrieved in the query. To retrieve the next 50 entities, you use the code shown below:

    Java Code: Example of Setting Start Position for Pagination
    query.setMaxResults(30); 
    query.setFirstResult(30); 
    List items = query.getResultList();
    As you can see that the difference between the two listing is that the second method, setFirstResult has different values. This provides the starting offset for the result list.
    The simple way of setting this up in your program in a flexible way is to create a method that takes the two settings as parameters in the method. An example of what this could look like is shown below:

    Java Code: Example of Dynamic Pagination Offset
    public List getPagedItems(int pageIndex, int pageSize) { 
       ...
       query.setMaxResults(pageSize) ; 
       query.setFirstResult(pageIndex) ; 
       return query.getResultList();
    }
    Controlling the Query Flush Mode

    We use the flush mode determine when the EntityManager performs database writes. The setting of the flush mode will cause results of a query to vary. You can change the FlushMode for a specific query by using the setFlushMode method. The default for the flush mode is AUTO. In this mode, the persistence provider is responsible for ensuring that all entities are updated in the persistence context irrespective of whether the AUTO setting is on the persistence context or the Query object.

    Tutorial: Review of Java Persistence Query Language for Component Developer Exam-a8-flushmodes.jpg
    Table: Flush Modes for Persistence Providers

    When the Query is set to COMMIT, the actual behavior is dependent on the implementation by the persistence provider. I recommend that you only change the flush mode to COMMIT only if you need to for a specific reason. Otherwise you can use the default value of AUTO for the majority of situations.

    How to Specify Query Hints

    Query hints are tips that are used by persistence providers when queries are executed and entities are retrieved. These hints are linked with persistence provider as extensions that are used to provide performance optimizations for executing queries. These extensions are passed in as query hints. Hints come in different forms. They can be for a query or for using the cache while executing a query. Below is an example of a hint to set the timeout for a query to 15 seconds with Oracle TopLink:

    Java Code: Setting Hint for A Query with TopLink
    query.setHint("toplink.jdbc.timeout", new Integer(15000));
    To do the same using Hibernate would be the following:

    Java Code: Setting Hint for A Query with Hibernate
    query.setHint("org.hibernate.timeout", new Integer(15));
    As the format for each persistence provider is different, make sure to check the documentation in order to verify the format that you need to use. Below is a list of the more commonly used hints for two for Hibernate and TopLink.

    Tutorial: Review of Java Persistence Query Language for Component Developer Exam-a8-ormframeworkhints.jpg
    Table: ORM Framework Supported Hints for Query Interface setHint method

    The way to apply a hint is by using the hints element of @NamedQuery. An example of how it is used is shown below:

    Java Code: Applying a Hint to a Named Query
    @NamedQuery( 
    name = "findTodoListWithNoTodos", 
    query = "SELECT DISTINCT l FROM TodoList l WHERE l.todos is EMPTY", 
    hints = { @QueryHint(name = "org.hibernate.timeout", value = "15") } 
    )
    Details of Java Persistence Query Language (JPQL)

    The Java Persistence Query Language (JPQL) is an extension of EJB QL that was agreed upon by the EJB3 Expert Group as the standard query language for the Java Persistence API (JPA). The benefit of using JPQL is that it makes the migration of EJB 2 entity beans to EJB 3 persistence alot simpler.

    The key component for JPQL is the Processor Engine or the JPQL Query Processor. This is a persistence provider component that translates a JPQL query into a native SQL for the database that is being used by the persistence provider.

    JPQL looks very much like SQL. But you need to remember that there are significant differences between the two languages. We will go through the differences in the two languages in the sections below.
    Tutorial: Review of Java Persistence Query Language for Component Developer Exam-a8-jpqlqueryprocessor.jpg
    Figure: JPQL Query Processor

    JPQL Statement Types

    JPQL supports three types of statements. The types of statements that it supports is selects, updates, and deletes statements in queries.

    Tutorial: Review of Java Persistence Query Language for Component Developer Exam-a8-statementtypes.jpg
    Table: Statement Types Supported by Java Persistence Query Language

    Using a SELECT Statement

    SELECT statements are used for retrieving entities with JPQL. An example of a SELECT
    statement is shown below:

    Java Code: Example of a SELECT Statement
    SELECT t 
    FROM Todo t 
    WHERE t.priority = :priority 
    ORDER BY t.dueDate
    The general structure of a SELECT statement is the following:
    • A SELECT clause specifies the object type or entity or values being retrieved
    • A FROM clause specifies the entity declaration that is used by other clauses
    • A WHERE clause is an optional filter the results returned by the query
    • A ORDER BY clause is used optionally to order the results retrieved by the query
    • A GROUP BY clause is used optionally to perform aggregation
    • A HAVING clause is used optionally to perform filtering in conjunction with aggregation


    Using an UPDATE Statement

    UPDATE statement in JPQL is used to change the data of an entity. Often a WHERE clause is used to limit the number of entities affected by the statement. The syntax for an UPDATE statement is the following:

    Java Code: Syntax of an UPDATE Statement
    UPDATE entityName identifierVariable 
    SET single_value_path_expression1 = value1, ...
    single_value_path_expressionN = valueN 
    WHERE where_clause
    Using an DELETE statement

    A DELETE in JPQL is used to eliminate one or more entities of a particular entity type. You use a WHERE clause to limit the number of entities affected by the statement. The syntax for the DELETE statement is the following:

    Java Code: Syntax of DELETE Statement
    DELETE entityName indentifierVariable WHERE where_clause
    Using the FROM Clause

    The FROM clause of JPQL is used to define the domain for the query. You use the names for the entities that are to be used in the query. You use the FROM clause in the following manner:

    Java Code: Example of FROM clause
    FROM Todo t
    In this example, Todo is the domain that is being queried and we use t as the identifier of type Todo. The entity name is specified by using the @Entity annotation. The name must be unique within the persistence unit. The default is always the name of the entity class. If we give an class name that differs from the entity name than we need to use the entity name if the JPQL statement. So for example if we were to provide different names for the class Todo and entity in the following manner:

    Java Code: Example of Entity with Different Name than Class
    @Entity(name = "TodoEntity") 
    public class Todo
    This would require that the FROM clause for the query be constructed in the following manner to map the correct entity type:

    Java Code: Example of Query Based on Entity Name
    FROM TodoEntity t
    Using Identifier Variables

    In JPQL we define identifiers as a way of facilitating the creation of JPQL statements using SELECT and WHERE. They are optional but it is highly recommended that you use them in your JPQL statements. The syntax of an identifier definition is the following:

    Java Code: Syntax for Identifier Definition
    FROM entityName [AS] identificationVariable
    The square brackets ([]) indicates that the AS operator is optional. The identifier variable must be a valid Java identifier, cannot be the same as an entity name in the same persistence unit, and must avoid all of the JPQL reserved identifiers. The list of JPQL reserved words are given below:

    Tutorial: Review of Java Persistence Query Language for Component Developer Exam-a8-reservedkeywords.jpg
    Table: List of JPQL keywords by the specification

    The way in which the identifier is used is to provide a (.) identification to the fields of the particular entity. This is known as path expression. So for example if we wanted to point to the priority field of the Todo entity, it would be done in the following manner:

    Java Code: Example of . Identifier
    t.priority
    Using the WHERE clause

    The WHERE clause is used to filter the results of a query such that only the entities that match the query condition specified will be retrieved. An example of a SELECT statement using a where clause is shown below:

    Java Code: Example of WHERE Clause
    SELECT t FROM Todo t WHERE t.completed = TRUE
    You can use almost every type of Java literal (i.e. boolean, float, enum, String, int, etc) in a WHERE clause. The only types that are excluded are numeric types such as octal and hexadecimals or array types such as byte[] or char[].

    Using Conditional Expressions and Operators

    A condition in the WHERE clause that filters results from a query is known as a conditional expression. You build conditional expressions using path expressions and operators supported by JPQL. JPQL can evaluate a path expression with numeric, string, or boolean values using relational operators. An example of a conditional expression is shown below:

    Java Code: Example of Conditional Expression
    t.hasNotes = TRUE
    The table below shows the list of operator types supported by JPQL:

    Tutorial: Review of Java Persistence Query Language for Component Developer Exam-a8-jpql-operators.jpg
    Table: JPQL Supported Operators

    Using a Range with BETWEEN

    You can use the BETWEEN operator in an arithmetic expression to compare a range of values with a variable. You can also use it in arithmetic, string, or DATETIME expressions where you compare a path expression to a lower and upper limit. The syntax for the BETWEEN operator is the following:

    Java Code: Syntax of Between Expression
    path_expression [NOT] BETWEEN lowerRange and upperRange
    Using the IN Operator

    The IN operator is used to create a conditional expression based on whether a path expression exists in a list of values. The syntax for the IN operator is the folllowing:

    Java Code: Syntax for IN operator
    path_expression [NOT] IN (List_of_values)
    The list of values can be comprised of a static list of comma-separated values or a dynamic list retrieved by a subquery.

    Using the LIKE Operator

    The LIKE operator is used to determine whether a single-value path expression matches a string pattern. The syntax for the LIKE operator is:

    Java Code: Syntax of LIKE Operator
    string_value_path_expression [NOT] LIKE pattern_value_
    The pattern_value is a string literal or an input parameter. The pattern_value can contain an underscore (_) or a percent sign (%). The underscore represents a single character.

    Dealing with Null Values and Empty Collections

    One thing that is important to know is how to deal with null values and empty collections. JPQL treats null values differently from empty strings but some databases may treat them as though they are the same. The persistence provider translates the JPQL into SQL. So if one database returns true when comparing an empty string with a null, the results will vary across other databases. It is important to know what results you will get with your database. When you have a conditional expression with a null value, the expression evaluates to either null or unknown. So for a complex WHERE clause with multiple conditional expressions, the nulls and empty strings in the expressions may produce a result that is unknown. The table below provides a list of the results of a conditional expression when it is compared with a null value.

    Tutorial: Review of Java Persistence Query Language for Component Developer Exam-a8-nullbooleanoperations.jpg
    Table: Impact of Null on Boolean Operations

    Using the MEMBER OF Operator

    The MEMBER OF operator is used to test whether an identifier variable, a single- value path expression, or an input parameter exists in a collection-value path expression. You can optionally use the OF and NOT keywords. The syntax for the MEMBER OF operator is shown below:

    Java Code: Syntax of MEMBER OF
    entity_expression [NOT] MEMBER [OF] collection_value_path_expression
    OK. Thatís it for part 1. In part two we will cover using JPQL functions.
    Last edited by Java Exam; 01-03-2012 at 07:40 PM.

Similar Threads

  1. Replies: 0
    Last Post: 12-21-2011, 03:56 PM
  2. Replies: 0
    Last Post: 12-20-2011, 06:16 PM
  3. Replies: 0
    Last Post: 12-20-2011, 06:02 PM
  4. Replies: 0
    Last Post: 12-16-2011, 11:51 AM
  5. Replies: 0
    Last Post: 12-13-2011, 07:42 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •