how to generate barcode in vb.net 2008 Using the query API and JPQL to retrieve entities in Java

Encode Data Matrix ECC200 in Java Using the query API and JPQL to retrieve entities

Just what is native SQL It s the SQL understood by the specific database server Oracle, MySQL, Derby, etc. that you are using. This section provides what you need to start using native SQL with EJB 3 right now.

In EJB 2 CMP entity beans, almost every vendor supported their own way of using SQL to perform queries. The many limitations in EJBQL were the primary driver for the vendor-specific extension for native SQL in EJB 2. Although JPA standardizes use of native SQL queries you should think twice about using native SQL in your applications, unless you are very proficient in SQL and you are trying to take advantage of the proprietary features of your database. Also keep in mind that the use of native SQL will make your applications less portable, if you decide to change your underlying database.

Suppose you want to generate a hierarchical list of categories, each showing its subcategories; it s impossible to do that in JPQL because JPQL does not support recursive joins, similar to databases like Oracle. This means you have to take advantage of native SQL. Let s assume you re using an Oracle database and you want to retrieve all subcategories of a particular Category by using recursive joins in the form of a START WITH CONNECT BY clause as follows:

SELECT CATEGORY_ID, CATEGORY_NAME FROM CATEGORY START WITH parent_id = CONNECT BY PRIOR category_id = category_id

Ideally, you should limit your use of native SQL to queries that you cannot express using JPQL (as in our Oracle database specific SQL query). However, for demonstration purposes, in our example in the next section, we ve used a simple SQL statement that can be used with most relational databases.

A JPA provider just executes SQL statements as JDBC statements and does not track whether the SQL statement updated data related to any entities. You should avoid using SQL INSERT, UPDATE, and DELETE statements in a native query because your persistence provider will have no knowledge of such changes in the database and it may lead to inconsistent/stale data if your JPA provider uses caching.

As in JPQL, you can use both dynamic queries and named queries with SQL. You have to remember the subtle differences between JPQL and SQL. JPQL returns an entity, or set, of scalar values, but a SQL query returns database records. Therefore, a SQL query may return more than entities, because you may join multiple tables in your SQL. Let s see how to use native SQL with both dynamic and native queries.

You can use the createNativeQuery method of the EntityManager interface to create a dynamic query using SQL as follows:

Query q = em.createNativeQuery("SELECT user_id, first_name, last_name " + " FROM users WHERE user_id IN (SELECT seller_id FROM " + "items GROUP BY seller_id HAVING COUNT(*) > 1)", actionbazaar.persistence.User.class); return q.getResultList();

In this statement, the createNativeQuery method takes two parameters: the SQL query and the entity class being returned. This will become an issue if the query returns more than one entity class which is why JPA allows a @SqlResultSetMapping to be used with the createNativeQuery method instead of passing an entity class. A @SqlResultSetMapping may be mapped to one or more entities. For example, if we want to create a SqlResultSetMapping for the User entity and use in our native query, then we can use the @SqlResultSetMapping annotation as follows:

@SqlResultSetMapping(name = "UserResults", entities = @EntityResult( entityClass = actionbazaar.persistence.User.class))

Then we can specify the mapping in the Query as follows:

Query q = em.createNativeQuery("SELECT user_id, first_name, last_name " + " FROM users WHERE user_id IN (SELECT seller_id FROM " + "items GROUP BY seller_id HAVING COUNT(*) > 1)", "UserResults"); return q.getResultList();

This is useful when the SQL query returns more than one entity. The persistence provider will automatically determine the entities being returned based on the SqlResultSetMapping, instantiate the appropriate entities, and initialize those entities with values based on the O/R mapping metadata. Once you create a query, it makes no difference whether you retrieve the results from a native SQL or a JPQL query.