X
  • Sunday, October 7, 2012

Test JPQL with NetBeans IDE 7.3 Tools

By: Geertjan Wielenga | Product Manager

Since I pretty much messed up this part of the "Unlocking Java EE 6 Platform" demo, which I did together with PrimeFaces lead Çağatay Çivici during JavaOne 2012, I feel obliged to blog about it to clarify what should have happened! In my own defense, I only learned about this feature 15 minutes before the session started. In 7.3 Beta, it works for Java SE projects, while for Maven-based web projects, you need a post 7.3 Beta build, which is what I set up for my demo right before it started. Then I saw that the feature was there, without actually trying it out, which resulted in that part of the demo being a bit messy. And thanks to whoever it was in the audience who shouted out how to use it correctly!

Screenshots below show everything related to this new feature, available from 7.3 onwards, which means you can try out your JPQL queries right within the IDE, without deploying the application (you only need to build it since the queries are run on the compiled classes):

SQL view:

Result view for the above:

Here, you see the result of a more specific query, i.e., check that a record with a specific name value is present in the database:

Also note that there is code completion within the editor part of the dialog above. I.e., as you press Ctrl-Space, you'll see context-sensitive suggestions for filling out the query. All this is pretty cool stuff! Saves time because now there's no need to deploy the app to check the database connection.

Join the discussion

Comments ( 25 )
  • guest Tuesday, October 9, 2012

    the option to start the JPQL editor remains grayed out when right-clicking on persistence.xml, despite building (or running) a maven project on GlassFish 3.1.2.2 Are there any special requirements to its structure? I set all additional eclipselink properties (see http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Configuration/JPA/persistence.xml) in persistence.xml (which I don't need because it references a jndi datasource)


  • Geertjan Tuesday, October 9, 2012

    7.3 Beta does not yet have support for this, you need a post 7.3 Beta build.


  • Jerry Wednesday, October 10, 2012

    trying to use Oracle INTERVAL with JPQL and cannot seem to get it right..Could you please post an example - both SQL and JPQL - using INTERVAL in the query.

    much appreciated


  • Richard Tuesday, January 8, 2013

    Is the Run JPQL only available for Java SE projects?

    I am unable to run a JPQL query. The editor finds my entities, because the code completion is working (entity names).

    When I attempt a query, I keep getting this error:

    "java.lang.Exception: You need to register database connection on Services tab"

    My database connection is setup under Services > Databases.

    Thanks


  • guest Wednesday, February 13, 2013

    i have the same error too

    "java.lang.Exception: You need to register database connection on Services tab"


  • guest Monday, February 18, 2013

    Me too, it would be nice if there is some explanation what netbeans needs if it gives the error "You need to register database connection on Services tab"


  • Geertjan Monday, February 18, 2013

    Go to the Services window and register a database connection there.


  • Karl Peterbauer Wednesday, February 27, 2013

    Hi Geertjan,

    same here: "You need to register database connection on Services tab", but I have an (open) JDBC connection registered in the services tab... Do I need to specify JDBC parameters in the persistence.xml?

    I think it would be great if the connection could be chosen as it can be done in the SQL query window.


  • guest Wednesday, February 27, 2013

    Same error:

    "You need to register database connection on Services tab".

    Connection registered correctly, an sql query within netbeans works fine.

    Autocomplete works correctly.

    ***Please help...!***


  • Geertjan Wednesday, February 27, 2013

    Have you started the database? Please send your questions on this point to the mailing list, nbusers@netbeans.org


  • serge Wednesday, February 27, 2013

    In general you may no need in start a connection for jpql features to work properly if it's registered properly on services tab. Connection should be started automatically in this case(at least derby and mysql if mysql server is registered and start path is specified), for other servers you may need to have it started first if nb can't start it from Services also.

    It's not so good to allow manual url selection as model should match tables and match specific persistence.xml, there will be more issues with selection of wrong connection in my opinion.

    Connection on Services tab should match one used in persistence.xml with all parameters match, please check if you have extra parameters (see for example if you have "zeroDateTimeBehavior=convertToNull" for mysql connections in one place only). If you are using datasources in jee environment, you may need to check connection url in your datasource configuration to see if it match with url for connection registered one on Services.

    Persistence do not try to look for "similar" urls even it's possible to implement. Some parameters may affect mappings and it may cause different hard to find errors by model-db mismatch. You need exact match for urls.


  • German Suárez Friday, March 1, 2013

    What is missing is the JDBC driver (ojdc6.jar, mysql.jar) in the project's libriries and clean and build the project. After that the JPQL Editor works fine!


  • guest Tuesday, March 5, 2013

    Doesn't work for postgresql !


  • Geertjan Tuesday, March 5, 2013

    Thanks. Let me try to reproduce your problem. Hmmm... ummm... no steps. Oh well.


  • guest Thursday, March 7, 2013

    java.lang.Exception: You need to register database connection on Services tab

    Drivers exist, native queries work fine.


  • Geertjan Thursday, March 7, 2013

    Please, instead of adding comments here, add your comments about the "You need to register database connection on Services tab" message here: http://netbeans.org/bugzilla/show_bug.cgi?id=227149


  • guest Tuesday, May 7, 2013

    Hello Geertjan Wielenga, I have one problem when I run the JPQL in NetBeans 7.3 it shows the following error.

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

    javax.persistence.PersistenceException: [PersistenceUnit: WebApplication1PU2] Unable to build EntityManagerFactory

    at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:915)

    at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:890)

    at org.hibernate.ejb.HibernatePersistence.createEntityManagerFactory(HibernatePersistence.java:57)

    at javax.persistence.Persistence.createEntityManagerFactory(Unknown Source)

    Caused by: org.hibernate.service.jndi.JndiException: Error parsing JNDI name [hi]

    at org.hibernate.service.jndi.internal.JndiServiceImpl.parseName(JndiServiceImpl.java:92)

    at org.hibernate.service.jndi.internal.JndiServiceImpl.locate(JndiServiceImpl.java:63)

    at org.hibernate.service.jdbc.connections.internal.DatasourceConnectionProviderImpl.configure(DatasourceConnectionProviderImpl.java:116)

    at org.hibernate.service.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:75)

    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:159)

    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:131)

    at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.buildJdbcConnectionAccess(JdbcServicesImpl.java:223)

    at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:89)

    at org.hibernate.service.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:75)

    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:159)

    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:131)

    at org.hibernate.cfg.SettingsFactory.buildSettings(SettingsFactory.java:73)

    at org.hibernate.cfg.Configuration.buildSettingsInternal(Configuration.java:2279)

    at org.hibernate.cfg.Configuration.buildSettings(Configuration.java:2275)

    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1744)

    at org.hibernate.ejb.EntityManagerFactoryImpl.<init>(EntityManagerFactoryImpl.java:94)

    at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:905)

    ... 5 more

    Caused by: javax.naming.NoInitialContextException: Need to specify class name in environment or system property, or as an applet parameter, or in an application resource file: java.naming.factory.initial

    at javax.naming.spi.NamingManager.getInitialContext(NamingManager.java:645)

    at javax.naming.InitialContext.getDefaultInitCtx(InitialContext.java:288)

    at javax.naming.InitialContext.getURLOrDefaultInitCtx(InitialContext.java:325)

    at javax.naming.InitialContext.getNameParser(InitialContext.java:480)

    at org.hibernate.service.jndi.internal.JndiServiceImpl.parseName(JndiServiceImpl.java:86)

    ... 21 more

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

    Any help would be greatly appreciated


  • Geertjan Tuesday, May 7, 2013

    Just go to Google and look for "Caused by: org.hibernate.service.jndi.JndiException: Error parsing JNDI name".

    And please don't put long stack traces in my blog again, just usse Google or go to the NetBeans mailing list.


  • Carlos Delgadillo Tuesday, May 7, 2013

    Hi Geertjan, I have a problem. When I try to run the query, I get the message "java.lang.Exception: You project either have no sources or you need to rebuild your project"

    I have rebuilt my project several times but still get this exception. What is the problem?. Thanks


  • guest Wednesday, May 8, 2013

    Regarding last one issue, it may be https://netbeans.org/bugzilla/show_bug.cgi?id=225911 resolved in next 7.3 patch/7.3.1, it should be released quite soon.

    Also 7.3.1 will contain more smart hint regarding connection misconfiguration.

    It's nice if everybody with any jpql related issue will file issues here https://netbeans.org/bugzilla/enter_bug.cgi?product=javaee also (Persistence component). it will help to track if issue is resolved, ask for more details etc.


  • guest Wednesday, May 29, 2013

    Hi,

    Spring 3.1 doesn't require the persistence.xml file anymore, and the persistence unit configuration can be done directly in the Java classes or the Spring context file. Is there a way to run the JPQL editor based on the Spring configuration/context file/whatever but without the dedicated persistence.xml? We have such a project and it would be nice to be able to use the JPQL editor features.


  • guest Friday, September 6, 2013

    Hay que hacer algo especial para que funcione JPQL Query en Netbeans 7.3. Utilizo MySql y no he podido hacer que funcione. Alexie


  • guest Wednesday, October 23, 2013

    Exception:

    javax.persistence.PersistenceException: No Persistence provider for EntityManager named test

    at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:85)

    with persistence:

    <?xml version="1.0" encoding="UTF-8"?>

    <persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">

    <persistence-unit name="test" transaction-type="RESOURCE_LOCAL">

    <provider>org.hibernate.ejb.HibernatePersistence</provider>

    <class>com...</class>

    <properties>

    <property name="hibernate.default_schema" value="something"/>

    <property name="hibernate.dialect" value="org.hibernate.dialect.OracleDialect"/>

    <property name="javax.persistence.jdbc.url" value="jdbc:oracle:thin:@DB:1521:XE"/>

    <property name="javax.persistence.jdbc.user" value="testuser"/>

    <property name="javax.persistence.jdbc.password" value="markant1"/>

    <property name="javax.persistence.jdbc.driver" value="oracle.jdbc.OracleDriver"/>

    </properties>

    </persistence-unit>

    </persistence>


  • diego Tuesday, September 8, 2015

    Hello, I get the error

    java.lang.Exception: You need to add your persistence provider library either to project classpath or registed as Ant Library within Netbeans

    work with NetBeans 8.0.2 , PostgreSQL , JBoss

    if you can guide me with the mistake I appreciate it

    Thank you


  • Onder Wednesday, November 4, 2015

    I am getting following error :

    java.lang.Exception: Persistence.xml can't be found at standard location


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha