8 Replies - 9768 Views - Last Post: 15 September 2013 - 06:31 AM

#1 CY5   User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 413
  • Joined: 28-September 12

How to Retrieve a row from database using HQL

Posted 15 September 2013 - 05:07 AM

How to retrieve a row from database using HQL like in sql we write "Select * from database_name where id=?"
I have a Database name GuestBook and also the table name is same.
Columns in GuestBook are:

------------ ------------ ---------- -----------------
VisitorNo | VisitorName | Message | MessageDate |
------------ ------------ ---------- ----------------

where VisitorNo is Primary Key

A JavaBean Class is Created Name GuestBook and its property are :
visitorNo, visitorName, message, messageDate

There is JSP page which retrieve all data
 try{
        hibSession.beginTransaction();
        guestbook=hibSession.createQuery("from GuestBook").list();
    }
    catch(RuntimeException e){
        throw e;
    }


How can i put condition to retrieve a particular row from database like 'WHERE' clause used in SQL
What is the Syntax to retrieve a row from database in Hibernate Query Language(HQL) Using Named Paramters

This post has been edited by CY5: 15 September 2013 - 05:14 AM


Is This A Good Question/Topic? 0
  • +

Replies To: How to Retrieve a row from database using HQL

#2 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6831
  • View blog
  • Posts: 28,338
  • Joined: 12-December 12

Re: How to Retrieve a row from database using HQL

Posted 15 September 2013 - 05:22 AM

There is a simple example of the WHERE clause at tutorialspoint

String hql = "FROM Employee E WHERE E.id = 10";
Query query = session.createQuery(hql);
List results = query.list();


There is an example of using parameters at the docs

Query query = sess.createSQLQuery("SELECT * FROM CATS WHERE NAME like ?").addEntity(Cat.class);
List pusList = query.setString(0, "Pus%").list();
     
query = sess.createSQLQuery("SELECT * FROM CATS WHERE NAME like :name").addEntity(Cat.class);
List pusList = query.setString("name", "Pus%").list();  

I feel obliged to say, though, that I've never used Hibernate or any of the above code - I just spent two minutes Googling - so this (your post) is quite disappointing.

This post has been edited by andrewsw: 15 September 2013 - 05:29 AM

Was This Post Helpful? 1
  • +
  • -

#3 CY5   User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 413
  • Joined: 28-September 12

Re: How to Retrieve a row from database using HQL

Posted 15 September 2013 - 05:36 AM

Yeah i am referring to it,The Code for Using Named Paramters is
String hql = "FROM Employee E WHERE E.id = :employee_id";
Query query = session.createQuery(hql);
query.setParameter("employee_id",10);
List results = query.list();


and i am getting an error for the code below
  hibSession.beginTransaction();
        String hql="from GuestBook where visitorName=:vn";
        Query query = session.createQuery(hql);
        query.setParameter("vn","ABC");
        List results = query.list();



Error is:
Cannot Find symbol
symbol:class Query

This post has been edited by CY5: 15 September 2013 - 05:39 AM

Was This Post Helpful? 0
  • +
  • -

#4 CY5   User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 413
  • Joined: 28-September 12

Re: How to Retrieve a row from database using HQL

Posted 15 September 2013 - 05:53 AM

Below is the whole code

<%@page contentType="text/html" pageEncoding="UTF-8" import="org.hibernate.SessionFactory, org.hibernate.cfg.Configuration,org.hibernate.Session,org.hibernate.Transaction,java.util.List,java.util.Iterator,myApp.GuestBook"%>

<!DOCTYPE html>
<%!
    SessionFactory sessionFactory;
    org.hibernate.Session hibSession;
    List<GuestBook> guestbook;
%>
<%
    sessionFactory=new Configuration().configure().buildSessionFactory();
    hibSession=sessionFactory.openSession();
    Transaction tx=null;
    String submit=request.getParameter("btnSubmit");
    if(submit !=null && ("Submit").equals(submit)){
        GuestBook gb=new GuestBook();
        try{
            tx=hibSession.beginTransaction();
            String guest=request.getParameter("guest");
            String message=request.getParameter("message");
            String messageDate=new java.util.Date().toString();
            gb.setVisitorName(guest);
            gb.setMessage(message);
            gb.setMessageDate(messageDate);
            
            hibSession.save(gb);
            tx.commit();
        }
        catch(RuntimeException e){
            if(tx!=null)
                tx.rollback();
            throw e;
        }
        response.sendRedirect("GuestBookView.jsp");
    }
    try{
        hibSession.beginTransaction();
        String hql="from GuestBook where visitorName=:vn";
        Query query = hibSession.createQuery(hql);
        query.setParameter("vn","ABC");
        List results = query.list();
    }
    catch(RuntimeException e){
        throw e;
    }
    hibSession.close();
%>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Guest Book</title>
    </head>
    <body bgcolor="pink">
        <table border="0" cellpadding="0" cellspacing="0" align="center" width="760">
            <tr>
                <td>
                    <table border="0" cellpadding="0" cellspacing="0" width="100%">
                        <tr>
                            <td width="60%" valign="top" align="left" style="padding-right:0px; padding-left:0px; padding-bottom:0px; font:24px/30px Georgia; width:228px; color:#786e4e; padding-top:0px; height:37px;">
                                View The Guest Book
                            </td>
                            <td valign="bottom" align="right" style="font:12px/16px Georgia,serif; color:#786e4e;">
                                <b>Click <a href="index.jsp">here</a> to sign the guestbook.</b>
                            </td>
                        </tr>
                    </table>
                </td>
            </tr>
            <tr align="left" valign="top">
                <td height="20">
                    <hr/>
                </td>
            </tr>
            <tr>
                <td>
                    <table border="0" cellpadding="0" cellspacing="0" align="center" width="100%">
                        <%
                            Iterator iterator=guestbook.iterator();
                            while(iterator.hasNext()){
                                GuestBook objGb=(GuestBook)iterator.next();
                        %>
                        <tr>
                            <td style="font:12px/16px Georgia; color:#786e4e;">
                                On<%=objGb.getMessageDate()%>,<br/>
                                <b><%=objGb.getVisitorName()%>:</b>
                                <%=objGb.getMessage()%>
                                <br/><br/>
                            </td>
                        </tr>
                        <%
                          }
                        %>
                    </table>
                </td>
            </tr>
        </table>
    </body>
</html>



Was This Post Helpful? 0
  • +
  • -

#5 Michael26   User is offline

  • Futurama: Insert funny joke here
  • member icon

Reputation: 414
  • View blog
  • Posts: 1,664
  • Joined: 08-April 09

Re: How to Retrieve a row from database using HQL

Posted 15 September 2013 - 06:00 AM

Is Query class define in the external library?
Was This Post Helpful? 1
  • +
  • -

#6 CY5   User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 413
  • Joined: 28-September 12

Re: How to Retrieve a row from database using HQL

Posted 15 September 2013 - 06:03 AM

No Query class is not define in external library
As i found this in TUTORIAL POINT so i also added it in my code

This i my Java Bean Class
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package myApp;

public class GuestBook implements java.io.Serializable{
    private Integer visitorNo;
    private String visitorName;
    private String message;
    private String messageDate;
    
    public GuestBook(){
        
    }
    
    public GuestBook(String visitorName,String message,String messageDate){
        this.visitorName=visitorName;
        this.message=message;
        this.messageDate=messageDate;
    }
    
    public Integer getVisitorNo(){
        return this.visitorNo;
    }
    
    public void setVisitorNo(Integer visitorNo){
        this.visitorNo=visitorNo;
    }
    
    public String getVisitorName(){
        return this.visitorName;
    }
    
    public void setVisitorName(String visitorName){
        this.visitorName=visitorName;
    }
    
    public String getMessage(){
        return this.message;
    }
    
    public void setMessage(String message){
        this.message=message;
    }
    
    public String getMessageDate(){
        return this.messageDate;
    }
    
    public void setMessageDate(String messageDate){
        this.messageDate=messageDate;
    }
    
}



This post has been edited by CY5: 15 September 2013 - 06:05 AM

Was This Post Helpful? 0
  • +
  • -

#7 g00se   User is offline

  • D.I.C Lover
  • member icon

Reputation: 3652
  • View blog
  • Posts: 16,780
  • Joined: 20-September 08

Re: How to Retrieve a row from database using HQL

Posted 15 September 2013 - 06:07 AM

This is a J2EE question
Was This Post Helpful? 0
  • +
  • -

#8 CY5   User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 413
  • Joined: 28-September 12

Re: How to Retrieve a row from database using HQL

Posted 15 September 2013 - 06:26 AM

Thanks For Reply Friend
Error is Resovled
Here the hibsesion,guestbook are declared as:

 org.hibernate.Session hibSession;
    List<GuestBook> guestbook;

hibSession.beginTransaction();
       String hql="from GuestBook where visitorName=:vn";
       guestbook = hibSession.createQuery(hql).setParameter("vn","ABC").list();


This post has been edited by andrewsw: 15 September 2013 - 06:32 AM

Was This Post Helpful? 0
  • +
  • -

#9 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6831
  • View blog
  • Posts: 28,338
  • Joined: 12-December 12

Re: How to Retrieve a row from database using HQL

Posted 15 September 2013 - 06:31 AM

I guess that you could add org.hibernate.Query to your import/header statement (or potentially import org.hibernate, although this imports the whole library). Added: But I see that you have resolved this anyway.

This post has been edited by andrewsw: 15 September 2013 - 06:34 AM

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1