Solving Hibernate SyntaxException: token '*', no viable alternative at input

Table of Contents#

Understanding the Error
Root Cause: HQL vs SQL Syntax Differences
Common Scenarios and Solutions
Best Practices for HQL Query Writing
Alternative Approaches
Conclusion
References

Understanding the Error#

The SyntaxException: token '*', no viable alternative at input occurs when Hibernate's query parser encounters an asterisk (*) in a position where it doesn't expect it. The error message indicates that the parser reached a point in your query where it found a * token but couldn't determine what to do with it based on the grammar rules of HQL.

Example Error Stack Trace:

org.hibernate.hql.internal.ast.QuerySyntaxException: 
token '*' requires a SELECT clause to define the query result type 
[ SELECT * FROM com.example.User u WHERE u.active = true ]
    
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: 
token '*', no viable alternative at input '*'

Root Cause: HQL vs SQL Syntax Differences#

The fundamental reason for this error lies in the philosophical difference between HQL and SQL:

SQL works with tables and columns
HQL works with objects and their properties

In SQL, SELECT * means "return all columns from the specified table." However, in HQL, you're not selecting columns—you're selecting objects or properties of objects. HQL is designed to return managed entities or specific data projections, not raw column results.

Key Differences:

Aspect	SQL	HQL
Target	Database tables and columns	Persistent objects and their properties
SELECT *	Returns all columns	Not supported (except in count())
Result Type	ResultSet with columns	List of objects or object arrays

Common Scenarios and Solutions#

Scenario 1: Using * in SELECT Clause#

Problematic Code:

// This will throw SyntaxException
String hql = "SELECT * FROM User u WHERE u.active = true";
Query<User> query = session.createQuery(hql, User.class);
List<User> users = query.getResultList();

Solution: Instead of using *, explicitly list the entity alias or omit the SELECT clause entirely when you want to return the complete entity.

Option A: Use entity alias

String hql = "SELECT u FROM User u WHERE u.active = true";
Query<User> query = session.createQuery(hql, User.class);
List<User> users = query.getResultList();

Option B: Omit SELECT clause (when returning entire entity)

String hql = "FROM User u WHERE u.active = true";
Query<User> query = session.createQuery(hql, User.class);
List<User> users = query.getResultList();

Option C: Select specific properties (for projections)

String hql = "SELECT u.id, u.username, u.email FROM User u WHERE u.active = true";
Query<Object[]> query = session.createQuery(hql, Object[].class);
List<Object[]> results = query.getResultList();
 
// Process results
for (Object[] result : results) {
    Long id = (Long) result[0];
    String username = (String) result[1];
    String email = (String) result[2];
    // ... use the data
}

Scenario 2: Using * in COUNT() Function#

Problematic Code:

// This may work but is not recommended
String hql = "SELECT COUNT(*) FROM User u WHERE u.active = true";

Solution: HQL does support COUNT(*) but with a specific syntax. However, it's often better to use alternatives:

Option A: Use COUNT(*) with proper syntax

String hql = "SELECT COUNT(*) FROM User u WHERE u.active = true";
Query<Long> query = session.createQuery(hql, Long.class);
Long count = query.getSingleResult();

Option B: Use COUNT with specific property (recommended)

String hql = "SELECT COUNT(u.id) FROM User u WHERE u.active = true";
Query<Long> query = session.createQuery(hql, Long.class);
Long count = query.getSingleResult();

Option C: Use COUNT with entity alias

String hql = "SELECT COUNT(u) FROM User u WHERE u.active = true";
Query<Long> query = session.createQuery(hql, Long.class);
Long count = query.getSingleResult();

Scenario 3: Complex Queries with Multiple Entities#

Problematic Code:

// This will throw SyntaxException
String hql = "SELECT * FROM User u JOIN u.department d WHERE d.name = 'Engineering'";

Solution: For joins and complex queries, you need to be explicit about what you want to select.

Option A: Select both entities

String hql = "SELECT u, d FROM User u JOIN u.department d WHERE d.name = 'Engineering'";
Query<Object[]> query = session.createQuery(hql, Object[].class);
List<Object[]> results = query.getResultList();
 
for (Object[] result : results) {
    User user = (User) result[0];
    Department department = (Department) result[1];
    // Process both entities
}

Option B: Use JOIN FETCH for eager loading

String hql = "SELECT u FROM User u JOIN FETCH u.department d WHERE d.name = 'Engineering'";
Query<User> query = session.createQuery(hql, User.class);
List<User> users = query.getResultList();
 
// Department is eagerly loaded and available without lazy loading exception

Best Practices for HQL Query Writing#

1. Always Use Explicit SELECT Clauses#

// ✅ Good
String hql = "SELECT u FROM User u WHERE u.active = true";
 
// ❌ Avoid
String hql = "FROM User WHERE active = true"; // Less explicit

2. Use Aliases Consistently#

// ✅ Good
String hql = "SELECT u.username, u.email FROM User u WHERE u.active = true";
 
// ❌ Avoid
String hql = "SELECT username, email FROM User WHERE active = true"; // Inconsistent

3. Prefer Typed Queries#

// ✅ Good - Type-safe
Query<User> query = session.createQuery("SELECT u FROM User u", User.class);
 
// ❌ Avoid - Not type-safe
Query query = session.createQuery("SELECT u FROM User u");

4. Use Constructor Expressions for Complex Projections#

// Define a constructor in your DTO
public class UserDTO {
    public UserDTO(Long id, String username, String email) {
        // constructor implementation
    }
}
 
// Use in HQL
String hql = "SELECT NEW com.example.UserDTO(u.id, u.username, u.email) " +
             "FROM User u WHERE u.active = true";
Query<UserDTO> query = session.createQuery(hql, UserDTO.class);
List<UserDTO> users = query.getResultList();

5. Leverage JPA Criteria API for Dynamic Queries#

// For complex, dynamic queries, use Criteria API
CriteriaBuilder cb = session.getCriteriaBuilder();
CriteriaQuery<User> query = cb.createQuery(User.class);
Root<User> root = query.from(User.class);
query.select(root).where(cb.equal(root.get("active"), true));
 
List<User> users = session.createQuery(query).getResultList();

Alternative Approaches#

1. Native SQL Queries#

When you genuinely need SELECT * functionality or complex SQL-specific features:

String sql = "SELECT * FROM users u WHERE u.active = :active";
Query query = session.createNativeQuery(sql, User.class);
query.setParameter("active", true);
List<User> users = query.getResultList();

⚠️ Warning: Use native queries sparingly as they bypass Hibernate's caching, object mapping, and database abstraction features.

2. Spring Data JPA Projections#

// Interface-based projection
public interface UserProjection {
    Long getId();
    String getUsername();
    String getEmail();
}
 
// Repository method
public interface UserRepository extends JpaRepository<User, Long> {
    List<UserProjection> findByActiveTrue();
}

3. Entity Graphs for Controlling Fetch Behavior#

EntityGraph<User> graph = session.createEntityGraph(User.class);
graph.addAttributeNodes("department");
 
String hql = "SELECT u FROM User u WHERE u.active = true";
Query<User> query = session.createQuery(hql, User.class);
query.setHint("javax.persistence.fetchgraph", graph);
 
List<User> users = query.getResultList(); // Department eagerly loaded

Conclusion#

The SyntaxException: token '*', no viable alternative at input error is a common stumbling block for developers transitioning from SQL to HQL. The key takeaway is that HQL operates at the object level rather than the column level, which requires a different mindset when writing queries.

Remember these main points:

HQL doesn't support SELECT * for entity queries—use entity aliases instead
Be explicit about what you want to select, whether it's entire entities or specific properties
Leverage HQL's object-oriented features like constructor expressions and fetch joins
Consider using JPA Criteria API or Spring Data JPA for more complex or dynamic queries

By understanding the philosophy behind HQL and following the best practices outlined in this post, you'll write more efficient, maintainable queries and avoid common pitfalls like the * token error.

References#

Note: The examples in this post use Hibernate 5.x/6.x and JPA 2.x syntax. Always check your specific version's documentation for any syntax variations.