js.transaction.hibernate

Interface SqlQuery

All Known Implementing Classes:

SqlQueryImpl

public interface SqlQuery

SQL helper interface.

Author:

Iulian Rotaru

Method Summary

Modifier and Type	Method and Description
void	delete() Surrogate for update used for delete queries.
SqlQuery	limit(int rowsCount) Set the maximum number of rows this query may return.
SqlQuery	limit(int offset, int rowsCount) Set first record offset and maximum number of rows this query may return.
<T> List<T>	list(Class<T> entity) Execute database select and return list of entities.
<T> List<T>	list(String alias, Class<T> type) Execute database select and return list of scalar value.
<K,V> Map<K,V>	map() Execute database select on two columns and returns them as key/values map, first column in query being the map key.
<T> T	object(Class<T> entity) Execute database select and return single entity.
<T> T	object(String alias, Class<T> type) Execute scalar query.
<T> T	object(String alias, Class<T> type, Object defaultValue) Convenient alternative to object(String, Class) method returning given default value instead of null.
SqlQuery	param(String name, Object value) Bind a not null value to a named query parameter.
void	update() Execute update designated by this SQL query.

Method Detail

param

SqlQuery param(String name,
               Object value)

Bind a not null value to a named query parameter. Please remember that if mix positioned and named parameters former must appear first into query; more specifically, all positioned parameters MUST precede first named one.

Parameters:

name - parameter name,

value - parameter value.

Returns:

this pointer.

Throws:

IllegalArgumentException - if parameter value is null.

limit

SqlQuery limit(int rowsCount)

Set the maximum number of rows this query may return. Internally uses Query.setMaxResults(int).

Parameters:

rowsCount - maximum number of rows count.

Returns:

this pointer.

Throws:

IllegalArgumentException - if rows count argument is not strict positive.

limit

SqlQuery limit(int offset,
               int rowsCount)

Set first record offset and maximum number of rows this query may return. Internally uses Query.setFirstResult(int) and Query.setMaxResults(int).

Parameters:

offset - first record offset, starting with 0,

rowsCount - the number of maximum rows count.

Returns:

this pointer.

Throws:

IllegalArgumentException - if any given argument is negative or rows count is zero.

object

<T> T object(String alias,
             Class<T> type)

Execute scalar query. Execute this query select and convert result to given scalar value type. Results set must have at most one record with a single column; if more Hibernate runtime exception is thrown.

In this interface context scalar type is a simple Java class that wrap a single value, that is, is not an aggregate object or collection, e.g. File that wraps a file path. Internally uses a hash to bind Java type to StandardBasicTypes then delegate SQLQuery.addScalar(String, Type).

 Integer count = sm.SQL("SELECT COUNT(*) AS count FROM table").object("count", Integer.class);
 

This method returns scalar value or null. If result is to be assigned to a primitive type uses variant with default value.

Type Parameters:

T - instance type.

Parameters:

alias - result column name or alias,

type - scalar value type to bind.

Returns:

scalar value or null.

Throws:

org.hibernate.NonUniqueObjectException - if query returns more than one record.

org.hibernate.HibernateException - for Hibernate related fails like bad query syntax.

object

<T> T object(String alias,
             Class<T> type,
             Object defaultValue)

Convenient alternative to object(String, Class) method returning given default value instead of null. This is especially useful when assign scalar type to primitive Java types.

 int count = sm.SQL("SELECT COUNT(*) AS count FROM table").object("count", Integer.class, 0);
 

Type Parameters:

T - instance type.

Parameters:

alias - result column name or alias,

type - scalar value type to bind,

defaultValue - value to return if query returns null.

Returns:

scalar value or default.

Throws:

org.hibernate.NonUniqueObjectException - if query returns more than one record.

org.hibernate.HibernateException - for Hibernate related fails like bad query syntax.

object

<T> T object(Class<T> entity)

Execute database select and return single entity. In the scope of this interface an entity is a Java type with defined ORM mapping. Hibernate engine will rise exception if given entity argument is not defined.

 class Person {
    int id;
    String name;
 }
 . . .
 Person person = sm.SQL("SELECT id,name FROM person WHERE id=?", personId).object(Person.class);
 

In above, Person class has mapping defined; not all class fields must be present into mapping definition but all mapped fields, which actually defined the entity, must be present into query result set. Also query must return at most one record into results set; there is Hibernate runtime exception if more records. This method uses internally Query.uniqueResult().

Type Parameters:

T - instance type.

Parameters:

entity - entity to retrieve.

Returns:

instance of given entity.

Throws:

org.hibernate.NonUniqueObjectException - if query returns more than one record.

org.hibernate.HibernateException - for Hibernate related fails like bad query syntax or missing entity field from results set.

list

<T> List<T> list(String alias,
                 Class<T> type)

Execute database select and return list of scalar value. See object(String, Class) for scalar value definition. This method uses internally Query.list().

 List<Integer> prices = sm.SQL("SELECT price FROM stock").list("price", Integer.class);
 

Type Parameters:

T - list type.

Parameters:

alias - result column name or alias,

type - scalar value type to bind.

Returns:

list of scalar type.

Throws:

org.hibernate.HibernateException - for Hibernate related fails like bad query syntax.

list

<T> List<T> list(Class<T> entity)

Execute database select and return list of entities. See object(Class) for a discussion about entities. This method uses internally Query.list(). Usage pattern is like below:

 List<Person> persons = sm.SQL("SELECT id,name FROM person").list(Person.class);
 

Type Parameters:

T - list type.

Parameters:

entity - entity to retrieve.

Returns:

list of entities, possible empty.

Throws:

org.hibernate.HibernateException - for Hibernate related fails like bad query syntax.

map

<K,V> Map<K,V> map()

Execute database select on two columns and returns them as key/values map, first column in query being the map key. It is user code responsibility to ensure columns to map key/value type compatibility.

 Map<String, Integer> serialMap = sm.SQL("SELECT serial,id FROM ticket WHERE county=?", county).map();
 

Note that in above example database ID is map value, not necessarily map key. Key and value are determined by column position in query, first is key and the second is value.

Type Parameters:

K - map key type.

V - map value type.

Returns:

maps of key/values possible empty.

Throws:

org.hibernate.HibernateException - for Hibernate related fails like bad query syntax or entity not defined.

update

void update()

Execute update designated by this SQL query.

delete

void delete()

Surrogate for update used for delete queries. For now just delegate update().