Evolving query from database to JAVA

Last time if I want to make a query through Session interface, I will invoke createQuery as shown below:

session = getSession();
Query query = session.createQuery(“from org.huahsin.Book”);
List<Book> booklist = query.list();

This way I only tackle for one single table. Somehow this need to work together with Hibernate mapping file. If the mapping file is missing, error would be seen like this

org.hibernate.hql.ast.QuerySyntaxException: Book is not mapped [from Book]

If I someone that could standalone without any dependency on Hibernate mapping, createSQLQuery would be a great help. This API is so friendly that it could allow me invoke a native SQL query and also able to tackle for multiple tables free of charge. So nice.

session = getSession();
Query query = session.createSQLQuery(“select ... from ...”);

But free thing doesn't mean good quality, some time this could be (very) error prone. Besides that, there is also another problem when retrieving the result set, it is so horrible. See code below.

for( Object row : query.list() ) {
 Object[] col = (Object[]) row;
    
 System.out.println((String)col[0]);
 System.out.println((String)col[1]);
 ...
}

Be cautious when working with the code snippet above because I have to ensure I'll never run out of array bounds and make sure I know which col index I'm working with. Since the retrieve object are Object type, I have no idea which type I should cast (String or BigDecimal), sometimes could easily lead to ClassCastException error.

Fortunately, software engineering is evolving, and it wouldn't just stop in that way. A better resolution has come into picture - Java Persistence Architecture. With this new technology, I'm not require to create XML mapping and retain my work in POJO, just that some new element has been introduce. For example, as the sample shown below, I need to declare which table does this POJO interact with? And how the column is being map on which field? In this case the User POJO class is map to users table, and the username field is the primary key corresponding to what have been declare in that table.

@Entity
@Table(name="users")
public class User {

 @Id
 private String username;

 ...
}

This is what have been declared in users table:

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO   | PRI |         |       |
...
...
+----------+-------------+------+-----+---------+-------+

When making a query, @NamedQuery come into play, and this is how the query being configure, just right at the top of the POJO class.

@NamedQuery(
 name="findByUser",
 query="select u from User u where u.username = :username ")
@Entity
@Table(name="users")
public class User {
 ...

To invoke this query, follow this way:

 List<users> userList;
 userList = emf.createEntityManager().createNamedQuery("findByUser", User.class).setParameter("username", username).getResultList();
 ...

Something to note when working with this method, I've gone through the pain due to my mistake when I first using it.

1. The query is almost similar to SQL syntax, just that there are base on object entity, not the one declare in database.
2. When something was not right in the query, error could be seen immediately after the web app is loaded. This is what they called fail-fast, never wait for error happen only when it is invoke.