Hibernate helps you to transform SQL results into entity classes.

Sample code in mapping-examples-annotation package de.laliluna.other.namedquery.

Assuming that SQL column names and class properties are the same, you can write the following code to get a list of computer books.

List<ComputerBook> reports = session.createSQLQuery
   ("select * from computerbook")
    .addEntity(ComputerBook.class)
   .list();

The column names of the SQL result must match the expected columns. We will slightly modify the example. The book has two attributes. The name of the book is mapped to book_name

@Entity
public class ComputerBook {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @Column(name = "book_name")
    private String name;
// ...

Therefor the SQL must return at least two columns named id and book_name. The following SQL query will fail with a column book_name not found exception.

List<ComputerBook> reports = session.createSQLQuery
   ("select id, name from computerbook")
   .addEntity(ComputerBook.class)
   .list();

To fix the query you can make use of alias in your SQL query or use SQL result set mapping. The latter is explained in the next chapter.

Using an alias. 

List<ComputerBook> reports = session.createSQLQuery
   ("select id, name as book_name from computerbook")
   .addEntity(ComputerBook.class)
   .list();

Modifying entities

Your entities are real entities. Their state is persistent after the query, you can modify the entities and they will be saved in the database with the next transaction commit.

Session session = InitSessionFactory.getInstance().getCurrentSession();
session.beginTransaction();
Lab aLab = (Lab) session.createSQLQuery("select l.* from lab l limit 1")
    .addEntity(Lab.class)
    .uniqueResult();
aLab.setTitle("My first Hibernate Lab");
session.getTransaction().commit();

SQL debug output. 

DEBUG SQL:111 - /* update de.laliluna.other.namedquery.Lab */
   update Lab set title=? where id=?

Multiple entities

If you want to select multiple entities or join a collection, there is a good chance to have naming conflicts of the result set columns (multiple columns with the same name). Hibernate cannot find out which column to use to create an entity.

There for there is a special syntax {alias.*} or {alias.propertyName}. If this syntax is used, Hibernate will modify the SQL to create distinct alias names.

The next query will load an instance of lab and the related book.

List<Object[]> result = session.createSQLQuery(
    "select {c.*}, {l.*} from computerbook c join lab l on l.computerbook_fk=c.id")
    .addEntity("c", ComputerBook.class)
    .addEntity("l", Lab.class)
    .list();

for (Object report[] : result) {
    ComputerBook book = (ComputerBook) report[0];
    Lab lab = (Lab) report[1];
    System.out.println(book+ "-"+lab);
}

The "c" in addEntity(..) corresponds to the {c.*} alias. It tells Hibernate to fill the entity Computerbook with the values of {c.*}

Below you can see that the SQL query has been modified by Hibernate before being executed.

select c.id as id73_0_, c.book_name as book2_73_0_, l.id as id74_1_,
   l.title as title74_1_
   from computerbook c join lab l on l.computerbook_fk=c.id

Instead of using wildcards, you can be explicit as well with the alias names.

List<Object[]> result = session.createSQLQuery(
    "select c.id as {c.id}, c.book_name as {c.name}, " +
        "l.id as {l.id}, l.title as {l.title} from computerbook c " +
        "join lab l on l.computerbook_fk=c.id")
    .addEntity("c", ComputerBook.class)
    .addEntity("l", Lab.class)
    .list();

for (Object report[] : result) {
    ComputerBook book = (ComputerBook) report[0];
    Lab lab = (Lab) report[1];
    System.out.println(book+ "-"+lab);
}

Initializing collections

The class Computerbook has a property Set<Lab> labs. Using the {..} syntax we can eager load the collection in the same query. The next query will load a computerbook and will initialize the collection labs of the book in one query.

List<Object[]> result = session.createSQLQuery(
    "select {c.*}, {l.*} from computerbook c join lab l on l.computerbook_fk=c.id")
    .addEntity("c", ComputerBook.class)
   .addJoin("l", "c.labs")
   .list();

for (Object report[] : result) {
    ComputerBook book = (ComputerBook) report[0];
    Lab lab = (Lab) report[1];
    System.out.println(book+ "-"+lab);
}

The method addJoin defines that the collection labs of the computer book is initialized and filled with the values of {l.*} .

SQL result set mapping is an alternative to map JDBC columns and class properties. As JPA does not support the {alias.*} notation, result set mapping is at the same time a more portable way.

Sample code in mapping-examples-annotation package de.laliluna.other.namedquery.

The following annotation defines a resultset mapping with the name bookReport2.

SQL result set mapping. 

@SqlResultSetMapping(name = "bookReport",  entities = {
   @EntityResult(entityClass = ComputerBook.class,
    fields = {
      @FieldResult(name = "id", column = "id"),
        @FieldResult(name = "name", column = "book_name")
   })
})

@Entity
public class ComputerBook { ...

Now, we can use the result set mapping in a SQL query.

Using an SQL result set mapping. 

List<ComputerBook> books = session.createSQLQuery
    ("select id, book_name from computerbook")
    .setResultSetMapping("bookReport")
   .list();
for (ComputerBook report : books) {
    System.out.println(report);
}

A result set mapping may contain as well scalar values (ie. single columns).

The next example returns a list of object arrays. The first element of the array is an ComputerBook and the second the number of books with the same name.

@SqlResultSetMappings({
    @SqlResultSetMapping(name = "bookReport2", entities = {@EntityResult
      (entityClass = ComputerBook.class,
      fields = {
         @FieldResult(name = "id", column = "id"),
         @FieldResult(name = "name", column = "name")})},
      columns = {@ColumnResult(name = "count_group")})
})

Using the result set mapping. 

List<Object[]> list = session.createSQLQuery(
    "select b.id, b.book_name, (select count(1) as count_group " +
    "from computerbook where book_name = b.book_name) as count_group " +
    "from computerbook b")
    .setResultSetMapping("bookReport2")
    .list();
for (Object o[] : list) {
    ComputerBook computerBook = (ComputerBook) o[0];
    long total = ((BigInteger) o[1]).longValue();
    log.debug(computerBook + "-- Books with same name: " + total);
}

A named native query is defined once and reused multiple times. It has a name to be identified. Named queries are useful, if you need to reuse a complex query at various places. In addition they provide a slight performance advantage.

Sample code in mapping-examples-annotation package de.laliluna.other.namedquery.

The example below shows a named SQL query using a result set mapping.

@NamedNativeQueries({
    @NamedNativeQuery(name = "reportSql",
        query = "select b.id, b.book_name, (select count(1) as count_group " +
            "from " +
            "computerbook where book_name = b.book_name) as count_group " +
            "from computerbook b", resultSetMapping = "bookReport2")})
@Entity
public class ComputerBook {...

Using a named query is pretty simple.

List<Object[]> list = session.getNamedQuery("reportSql").list();
for (Object o[] : list) {
    ComputerBook computerBook = (ComputerBook) o[0];
    long total = ((BigInteger) o[1]).longValue();
    log.debug(computerBook + "-- Books with same name: " + total);
}

You might have noticed that @NamedNativeQuery and @NamedQuery can both be executed by calling the getNamedQuery method. This can help if you need to migrate an existing application with a lot of SQL queries. First, you transform the SQL in named queries and later on you change the code to HQL.

Sometimes you need plain access to the JDBC connection. There are two approaches.

The first provides access to the currently used connection and in addition provides exception handling. It will close the connection if an exception happens. I would consider it the preferred approach. The session.doWork(..) method will inject the current session.

session.doWork. 

session.doWork(new Work(){
    @Override
    public void execute(Connection connection) throws SQLException {
        ResultSet resultSet = connection.prepareCall(
         "select * from hello_world").getResultSet();
    }
});

Alternatively, you can get a new connection from the connection provider. This connection will not have any relation to Hibernate.

ConnectionProvider. 

ConnectionProvider connectionProvider = ((SessionFactoryImplementor) sf)
   .getConnectionProvider();

Connection connection = null;
try {
    connectionProvider.getConnection();
} catch (SQLException e) {
    e.printStackTrace();

}
finally {
    if(connection!=null)
        try {
            connection.close();
        } catch (SQLException e) {
            // ignore or warn (as you like)
        }
}