{"id":1324,"date":"2016-06-27T09:40:45","date_gmt":"2016-06-27T09:40:45","guid":{"rendered":"http:\/\/codethataint.com\/blog\/?p=1324"},"modified":"2018-03-12T05:55:38","modified_gmt":"2018-03-12T05:55:38","slug":"hibernate-hql","status":"publish","type":"post","link":"https:\/\/codethataint.com\/blog\/hibernate-hql\/","title":{"rendered":"Hibernate HQL"},"content":{"rendered":"

Getting list of Users from users Table<\/strong><\/p>\n

\r\n     sessionFactory = createSessionFactory();\r\n     Session objSession = sessionFactory.openSession();\r\n     objSession.beginTransaction();\r\n\t\t\r\n     Query objQuery = objSession.createQuery("from Users");\r\n     List<Users> arrUsers = objQuery.list();\r\n\t\t\r\n     objSession.getTransaction().commit();\r\n     objSession.close();\r\n\t\t\r\n     System.out.println(arrUsers.size());\r\n\r\n     for (Users users : arrUsers) {\r\n\tSystem.out.println(users.getUserName());\r\n     }\r\n<\/pre>\n
Pagination  Using HQL<\/strong><\/p>\n
\r\n    Query objQuery = objSession.createQuery("from Users");\t\t\r\n    objQuery.setFirstResult(5);\r\n    objQuery.setMaxResults(2);\r\n    List<Users> arrUsers = objQuery.list();\r\n\t\t\t\t\r\n    objSession.getTransaction().commit();\r\n    objSession.close();\r\n\t\t\r\n    System.out.println(arrUsers.size());\r\n\t\t\r\n    for (Users users : arrUsers) {\r\n\tSystem.out.println(users.getUserName());\r\n    }\r\n<\/pre>\n
Note: <\/strong>In Pagination the Starting record is specified by setFirstResult<\/em> and ending record is specified by  setMaxResults<\/em>.<\/p>\n
Taking a Specific Column for Entity<\/strong><\/p>\n
\r\n\tQuery objQuery = objSession.createQuery("select UserName from Users");\t\t\r\n\tobjQuery.setFirstResult(5);\r\n\tobjQuery.setMaxResults(2);\r\n\tList<String> arrUsers = (List<String>)objQuery.list();\r\n\r\n\tobjSession.getTransaction().commit();\r\n\tobjSession.close();\r\n\r\n\tSystem.out.println(arrUsers.size());\r\n\r\n\tfor (String users : arrUsers) {\r\n\t\tSystem.out.println(users);\r\n\t}\r\n<\/pre>\n
Note :<\/strong>
\nThe Object Name in entity should be same as specified in class including Case. username will not work in select query but UserName does.<\/p>\n
Parameter Binding in Hibernate <\/strong>
\nMethod 1<\/strong><\/p>\n
\r\n  Query objQuery = objSession.createQuery("from Users where UserId >?");\r\n  objQuery.setParameter(0, 5);\t\t\r\n  List<Users> arrUsers = (List<Users>)objQuery.list();\r\n\r\n  for (Users users : arrUsers) {\r\n\tSystem.out.println(users.getUserName());\r\n  }\r\n<\/pre>\n
Method 2<\/strong><\/p>\n
\r\n     Query objQuery = objSession.createQuery("from Users where UserId > :limit");\r\n     objQuery.setInteger("limit", 5);\r\n\t\t\r\n     List<Users> arrUsers = (List<Users>)objQuery.list();\r\n\t\t\t\t\r\n     objSession.getTransaction().commit();\r\n     objSession.close();\r\n\t\r\n     for (Users users : arrUsers) {\r\n\tSystem.out.println(users.getUserName());\r\n     }\r\n<\/pre>\n
NamedQuery vs NamedNativeQuery<\/strong>
\nNamedQuery helps to consolidate all query at particular place.<\/p>\n
users.java<\/strong><\/p>\n
\r\n@Entity\r\n@NamedQuery(name="Users.byUserId", query="from Users where UserId=?")\r\npublic class Users {\r\n\t@Id @GeneratedValue(strategy=GenerationType.IDENTITY)\r\n\tprivate int UserId;\r\n\tprivate String UserName;\r\n\t\r\n\tpublic int getUserId() {\r\n\t\treturn UserId;\r\n\t}\r\n\tpublic void setUserId(int userId) {\r\n\t\tUserId = userId;\r\n\t}\r\n\tpublic String getUserName() {\r\n\t\treturn UserName;\r\n\t}\r\n\tpublic void setUserName(String userName) {\r\n\t\tUserName = userName;\r\n\t}\t\r\n}\r\n<\/pre>\n
CreationScript.java<\/strong><\/p>\n
\r\n\tsessionFactory = createSessionFactory();\r\n\tSession objSession = sessionFactory.openSession();\r\n\tobjSession.beginTransaction();\r\n\t\t\r\n\tQuery objQuery = objSession.getNamedQuery("Users.byUserId");\r\n        objQuery.setInteger(0, 5);\r\n\t\t\r\n\tList<Users> arrUsers = (List<Users>)objQuery.list();\r\n\t\r\n\tfor (Users users : arrUsers) {\r\n\t   System.out.println(users.getUserName());\r\n\t}\r\n<\/pre>\n
NativeQueries helps us to query the table directly by using table name instead of querying through Entity like one in NamedQuery.This is useful when we use stored procedure to take our resultSets.<\/p>\n
users.java<\/strong><\/p>\n
\r\n@Entity\r\n@NamedNativeQuery(name="Users.byUserId", query="SELECT * from Users where UserId=?", resultClass=Users.class)\r\npublic class Users {\r\n\t@Id @GeneratedValue(strategy=GenerationType.IDENTITY)\r\n\tprivate int UserId;\r\n\tprivate String UserName;\r\n\t\r\n\tpublic int getUserId() {\r\n\t\treturn UserId;\r\n\t}\r\n\tpublic void setUserId(int userId) {\r\n\t\tUserId = userId;\r\n\t}\r\n\tpublic String getUserName() {\r\n\t\treturn UserName;\r\n\t}\r\n\tpublic void setUserName(String userName) {\r\n\t\tUserName = userName;\r\n\t}\t\r\n} \r\n<\/pre>\n
Note:<\/strong>  resultClass=Users.class should be specified or else object class cast exception would be thrown.<\/p>\n","protected":false},"excerpt":{"rendered":"
Getting list of Users from users Table sessionFactory = createSessionFactory(); Session objSession = sessionFactory.openSession(); objSession.beginTransaction(); Query objQuery = objSession.createQuery("from Users"); List<Users> arrUsers = objQuery.list(); objSession.getTransaction().commit(); objSession.close(); System.out.println(arrUsers.size()); for (Users users : arrUsers) { System.out.println(users.getUserName()); } Pagination Using HQL Query objQuery = objSession.createQuery("from Users"); objQuery.setFirstResult(5); objQuery.setMaxResults(2); List<Users> arrUsers = objQuery.list(); objSession.getTransaction().commit(); objSession.close(); System.out.println(arrUsers.size()); for (Users users… Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[74],"tags":[],"class_list":["post-1324","post","type-post","status-publish","format-standard","hentry","category-hibernate"],"_links":{"self":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/1324","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/comments?post=1324"}],"version-history":[{"count":14,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/1324\/revisions"}],"predecessor-version":[{"id":2768,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/1324\/revisions\/2768"}],"wp:attachment":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/media?parent=1324"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/categories?post=1324"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/tags?post=1324"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}