CODEWITHSUNDEEP
jsfprimefaces
user3743956
user3743956

Reputation: 3

create native query JSF

i got error in my codes like this :

javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered ":" at line 1, column 42. Error Code: -1 Call: select userLevelId from USERS where id = :id Query: DataReadQuery(sql="select userLevelId from USERS where id = :id ")

and my code :

        public String cek() { if (tmp.equals(tmp2)) {
          y =1;
          level = getItems().toString(); } 
          .....
          .....
        }


       public List<Users> getItems() {
         if (y.equals(1)) {
         y=0;
         tmp = tmp.trim();
         return em.createNativeQuery("select userLevelId from USERS where id = :id")
         .setParameter("id", tmp).getResultList(); }
    }

so where's my fault ? can anyone help me ? thx b4

Upvotes: 0

Views: 1024

Answers (1)

Paul Samsotha
Paul Samsotha

Reputation: 209092

Main Problem

  • Native queries don't use named parameter syntax (:id). Use prepared statement syntax with the ?. Then use. setParameter( indexOf?, value ) . For example

    ...("select userLevelId from USERS where id = ?")
...setParameter(1, tmp)

Other Notes

  • Don't do your querying in the getter if the getter is part of the property binding. For instance if you have List<User> items as a bean property, don't do any business logic or in your case querying in the getter. The getter may be called for a few number of reason, apart from what you expect. Instead you can initialize it in a @PostConstruct method or in your constructor. If you need to update the list, have a listener method that will update it, and directly call that method

  • A common practice is to separate the business layer and web layer (the managed bean). The business layer can be an EJB in which you inject into the managed bean

Upvotes: 1

Related Questions