hibernate Query

hibernate Query

HQL

https://www.tutorialspoint.com/hibernate/hibernate_query_language.htm

Hibernate Query Language (HQL) is an object-oriented query language, similar to SQL, but instead of operating on tables and columns, HQL works with persistent objects and their properties. HQL queries are translated by Hibernate into conventional SQL queries, which in turns perform action on database.

query

// from
String hql = "FROM Employee";
Query query = session.createQuery(hql);
List results = query.list();

// as 
String hql = "FROM Employee AS E";

// select 
String hql = "SELECT E.firstName FROM Employee E";

// where 
String hql = "FROM Employee WHERE E.id = 10";

// orderby
String hql = "FROM Employee E WHERE E.id > 10 ORDER BY E.salary DESC";

// named parameter
String hql = "FROM Employee E WHERE E.id = :employee_id";
Query query = session.createQuery(hql);
query.setParameter("employee_id",10);
List results = query.list();

Update

String hql = "UPDATE Employee set salary = :salary "  + 
             "WHERE id = :employee_id";
Query query = session.createQuery(hql);
query.setParameter("salary", 1000);
query.setParameter("employee_id", 10);
int result = query.executeUpdate();
System.out.println("Rows affected: " + result);

Insert

​ 通常使用save or persist

INSERT INTO EntityName *`properties_list`* *`select_statement`*

Query query = session.createQuery("insert into Stock(stock_code, stock_name)" +
    			"select stock_code, stock_name from backup_stock");
int result = query.executeUpdate();

Delete

String hql = "DELETE FROM Employee "  + 
             "WHERE id = :employee_id";
Query query = session.createQuery(hql);
query.setParameter("employee_id", 10);
int result = query.executeUpdate();
System.out.println("Rows affected: " + result);

Criteria

https://howtodoinjava.com/hibernate/hibernate-criteria-queries-tutorial/#unique_result

Criteria API, which allows you to build up a criteria query object programmatically where you can apply filtration rules and logical conditions.

The Hibernate Session interface provides createCriteria() method, which can be used to create a Criteria object that returns instances of the persistence object's class when your application executes a criteria query.

// restriction
Criteria cr = session.createCriteria(Employee.class);
cr.add(Restrictions.gt("salary", 2000));
cr.add(Restrictions.like("firstName", "zara%"));

// restriction and/or logicalExpression
Criterion salary = Restrictions.gt("salary", 2000);
Criterion name = Restrictions.ilike("firstNname","zara%");
LogicalExpression orExp = Restrictions.or(salary, name);
cr.add( orExp );

// sorting
cr.addOrder(Order.desc("salary"));

// projections & aggregation
cr.setProjection(Projections.avg("salary"));
cr.setProjection(Projections.rowCount());

// pagination
public Criteria setFirstResult(int firstResult)
public Criteria setMaxResults(int maxResults)
  
  

Projections

// single field
Criteria criteria = session.createCriteria(Product.class);
criteria = criteria.setProjection(Projections.property("name"));

// multiple fields
Criteria criteria = session.createCriteria(Product.class);
criteria = criteria.setProjection(
  Projections.projectionList()
  .add(Projections.id())
  .add(Projections.property("name")));

// aggregation
	Criteria criteria = session.createCriteria(Product.class);
	criteria = criteria.setProjection(
	  Projections.projectionList()
	    .add(Projections.groupProperty("category"))
	    .add(Projections.rowCount()));

Result Transformer

AliasToBeanResultTransformer

uses the default constructor of the DTO class to instantiate a new object. In the next step, Hibernate uses reflection to call a setter method for each aliased value in the query.

ToListResultTransformer and AliasToEntityMapResultTransformer

The ToListResultTransformer maps the Object[] returned by your query with all its elements to a java.util.List

The AliasToEntityMapResultTransformer transforms the query result to a java.util.Map that contains all aliased values of the result set. The alias of each value is used as the key of the Map.

Query selectPerson = session.createQuery(
    "Select p.id as id, " +
    "p.firstName as firstName, " +
    "p.lastName as lastName " +
    "from Person p")
    .setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
List<Map> list = selectPerson.list();

Implementing your own ResultTransformer

Query query = session.createNativeQuery("select id as personId, first_name as firstName, last_name as lastName, city from Person p")
    .setResultTransformer(new ResultTransformer(){
            @Override
            public Object transformTuple(Object[] tuples, String[] aliases) {
                PersonDTO personDTO = new PersonDTO();
                personDTO.setPersonId((int)tuples[0]);
                personDTO.setFirstName((String)tuples[1]);
                personDTO.setLastName((String)tuples[2]);
                return personDTO;
            }
 
            @Override
            public List transformList(List list) {
                return list;
            }
        });
List<PersonDTO> list = query.list();

---

使用自定义SQL（非hibernate generated）， 可以使用

• named sql
• createSQL

Create SQL

// 指定返回类型
Query query = session.createSQLQuery(
"select * from stock s where s.stock_code = :stockCode")
.addEntity(Stock.class)
.setParameter("stockCode", "7277");
List result = query.list();

// 返回Object array
Query query = session.createSQLQuery(
"select s.stock_code from stock s where s.stock_code = :stockCode")
.setParameter("stockCode", "7277");
List result = query.list();

NamedQuery

providing a centralized, quick and easy way to read and find *Entity*‘s related queries.

NamedQuery

org.hibernate.annotations.NamedQuery annotation

every *@NamedQuery* annotation is attached to exactly one entity class or mapped superclass. But, since the scope of named queries is the entire persistence unit, we should select the query name carefully to avoid a collision. And we have achieved this by using the entity name as a prefix.

@org.hibernate.annotations.NamedQueries({
	    @org.hibernate.annotations.NamedQuery(name = "DeptEmployee_FindByEmployeeNumber", 
	      query = "from DeptEmployee where employeeNumber = :employeeNo"),
	    @org.hibernate.annotations.NamedQuery(name = "DeptEmployee_FindAllByDesgination", 
	      query = "from DeptEmployee where designation = :designation"),
	    @org.hibernate.annotations.NamedQuery(name = "DeptEmployee_UpdateEmployeeDepartment", 
	      query = "Update DeptEmployee set department = :newDepartment where employeeNumber = :employeeNo"),
	...
	})
  
  // use named query
  Query<DeptEmployee> query = session.createNamedQuery("DeptEmployee_FindByEmployeeNumber", 
	  DeptEmployee.class);
	query.setParameter("employeeNo", "001");
	DeptEmployee result = query.getSingleResult();

Configs

cacheable – whether the query (results) is cacheable or not
cacheMode – the cache mode used for this query; this can be one of GET, IGNORE, NORMAL, PUT, or REFRESH
cacheRegion – if the query results are cacheable, name the query cache region to use
comment – a comment added to the generated SQL query; targetted for DBAs
flushMode – the flush mode for this query, one of ALWAYS, AUTO, COMMIT, MANUAL, or PERSISTENCE_CONTEXT

NamedNativeQuery

Since this is a native query, we'll have to tell Hibernate what entity class to map the results to. Consequently, we've used the *resultClass* property for doing this.

Another way to map the results is to use the *resultSetMapping* p

@org.hibernate.annotations.NamedNativeQueries(
	    @org.hibernate.annotations.NamedNativeQuery(name = "DeptEmployee_GetEmployeeByName", 
	      query = "select * from deptemployee emp where name=:name",
	      resultClass = DeptEmployee.class)
	)
	
		Query<DeptEmployee> query = session.createNamedQuery("DeptEmployee_FindByEmployeeName", DeptEmployee.class);
	query.setParameter("name", "John Wayne");
	DeptEmployee result = query.getSingleResult();

Notes; We can use the @NamedNativeQuery annotation to define calls to stored procedures and functions as well: