Lesson 3, queries. In this lesson, we'll use various options of the query interface to query a database using JPQL, statements based off entities and their properties rather than SQL. Consume ResultSets as collections of entities rather than manual consumption of ResultsSets like B, so with JDBC. Create tuple queries to restrict the amount of data returned in a result set. These are basically, if you like, subset of properties of an entity and they come back as a collection of objects arrays. Create projection queries, max, average, NativeQueries where we're actually using SQL instead of JPQL, and there's also something called TypedQueries where we can add a little bit a type safety. We'll also create NamedQueries that we can reference by a name and use throughout our code, and then we'll lastly talk about embeddable classes and composite keys. Basically, these are classes that are nested inside entities, and they do provide the basis for business keys to our entities. JPQL; a simple piece of JPQL, select empl from employee empl, that can be used by the EntityManager.createQuery method that takes the JPQL statement. The method returns an instance of the interface query. What's really happening here is that the employee is mapped to the schema table employee and it uses the JP annotations on the employee class to map the properties to columns of the table and generate SQL against the employee table courtesy of hibernate and then passes it through a JDBC connection. There we have it. Create the query, and this is the SQL that we will see in logs to show that we are indeed translating into SQL and going against the associated table of the entity. Now consuming the ResultSet, you've got a couple of options. It really just depends on whether you're expecting more than one row back in the ResultSet or not. getResultList will return a collection of entities where this gets single result, will return a single entity, and if you do get more than one entity back in that ResultSet, it will result in an error. Here we have a createQuery again, SELECT empl from employee empl. We can see.getResultList on the query interface. It returns a collection of employees, and we can iterate through the employees. There on the right-hand side, you can see the SQL that is being generated under the hood. Scalar queries. Create a query, SELECT MAX, hourlyRate from employee, we're using the alias again of empl, getSingleResult. Obviously, we're expecting a single result, the maximum hourly rate, so we can easily use getSingleResult, and you can see your SQL being generated under the hood. Sometimes you might see it listed twice. That's because you may have logging both in log for J and also it hibernate turned on courtesy of your persistence XML as one of the configuration properties. Now we want a WHERE clause. I've hardcoded a WHERE clause here. SELECT empl from employee empl where empl appearenceFee is greater than 50. I've also put in a second argument Employee.class. This enables me to create what we call a TypedQuery. What this will do is get rid of the morning that you might see in your Java code about type safety. Then you can see here the select statement that's executed. The WHERE clause is very clear when the SQL is created from the JPQL. We had a hardcode of WHERE clause, we need a more dynamic WHERE clause. We need to create something similar to a JDBC prepared statement where parameters can be injected into the WHERE clause. We could use positional parameters where you'll see placeholders identified in the JPQL as question marks. They will be populated by position by you setting parameters like I've done here, setParameter 1 Hola. It's one-based. It'll go into the first question mark placeholder. If I had two Hola, it will go into the second question mark or placeholder. These are now deprecated, we will not be using them. Instead, we will using NamedParameters where we set the parameter by a name, and we have in our JPQL a placeholder defined by a name. Here you can see the query being created in the JPQL where empl.appearanceFee is greater than and you see the colon fee, that is the NamedParameter. You can see the setParameter method being executed on the query instance, and we can see that the fee is aligning up by name and 50 will be positioned at the place where that NamedParameter is in the JPQL. We will execute the ResultList. Now we've got our first lab on queries. You've had some instructions for it. I'll walk you through the solution. Once you've done it, please follow the instructions and I will see you on the other side.
