Using Hibernate

in a Java Swing Application 6

 

 

  1. Creating the Query in the HQL Query Editor

 

Creating the Query in the HQL Query Editor

 

In the IDE you can construct and test queries based on the Hibernate Query Language (HQL) using the HQL Query Editor. As you type the query the editor shows the equivalent (translated) SQL query. When you click the 'Run HQL Query' button in the toolbar, the IDE executes the query and shows the results at the bottom of editor.

In this exercise you use the HQL Editor to construct simple HQL queries that retrieve a list of actors' details based on matching the first name or last name (not both). Before you add the query to the class you will use the HQL Query Editor to test that the connection is working correctly and that the query produces the desired results.

 

Expand the <default package> source package node in the Projects window.

Select and right-click hibernate.cfg.xml file and choose Run HQL Query to open the HQL Query Editor.

 

Invoking the HQL Query Editor in NetBeans 6.9.1

 

Test the connection by typing 'from Actor' (make sure the 'A' in 'Actor' is a capital letter) in the HQL Query Editor. Click the Run HQL Query button ( Run HQL Query button ) in the toolbar.

When you click Run HQL Query you should see the query results in the bottom pane of the HQL Query Editor.

 

The HQL Query Editor with sample query and its output

 

If you click the SQL button above the results you should see the following equivalent SQL query.

 

select actor0_.actor_id as col_0_0_ from sakila.actor actor0_

 

The equivalent SQL language of the HQL query

 

If you run the HQL query before building the project, the following error should be expected.

 

org.hibernate.exception.SQLGrammarException: could not execute query

     at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)

     at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)

     at org.hibernate.loader.Loader.doList(Loader.java:2223)

     at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)

     at org.hibernate.loader.Loader.list(Loader.java:2099)

     at org.hibernate.hql.classic.QueryTranslatorImpl.list(QueryTranslatorImpl.java:912)

     at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)

     at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)

     at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from limit 100' at line 1

     at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

     at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)

     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

     at java.lang.reflect.Constructor.newInstance(Constructor.java:513)

     at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)

     at com.mysql.jdbc.Util.getInstance(Util.java:381)

     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)

     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)

     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)

     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)

     at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)

     at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)

     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)

     at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)

     at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885)

     at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)

     at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)

     at org.hibernate.loader.Loader.doQuery(Loader.java:674)

     at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)

     at org.hibernate.loader.Loader.doList(Loader.java:2220)

     ... 8 more

Try another query by opening another HQL query editor.

 

 

 

 

 

 

 

 

 

 

 

 

-------------------------------------------------------------------

Opening another HQL query editor

 

Type the following query in the HQL Query Editor and click Run HQL Query to check the query results when the search string is 'PE'.

 

from Actor a where a.firstName like 'PE%'

 

The query returns a list of actors' details for those actors whose first names begin with 'PE'.

 

An HQL query and the returns result of a list of actors' details for those actors whose first names begin with 'PE'

 

The SQL version is shown below when you click the SQL button.

 

The equivalent of the SQL statement for the HQL query

 

Open a new HQL Query Editor and type the following query in the editor pane. Click Run HQL Query.

 

from Actor a where a.lastName like 'MO%'

 

Running the from Actor a where a.lastName like 'MO%' HQL query, showing its result

 

The query returns a list of actors' details for those actors whose last names begin with 'MO'. The SQL version is shown below when you clicking the SQL button.

 

The SQL equivalent of the HQL from Actor a where a.lastName like 'MO%' query

 

Testing the queries shows that the queries return the desired results. The next step is to implement the queries in the application so that the appropriate query is invoked by clicking the Query button in the form.

 

 

 

 

 

 


< Java Swing and Hibernate 5 | Java and NetBeans Tutorial | Java Swing and Hibernate 7 >