How to fetch data from multiple table query using hibernate?

Question

I am developing a simple application using Spring & Hibernate. IDE :Eclipse

I want to show schedule of student.

Schedule uses Student,Subject,Exam, & Class tables.

Schedule contains: student name, roll no, subject, class, exam, from time, to time & date.

I have written a query (works with > 1 table) which will return me a record, & this record will be shown as schedule.

No problems with query (I have printed the object on console).

I am getting a NumberFormatException on my .jsp while retrieving records from list(list is sent from Controller )

1) Is it a right way to retrieve the multiple table values from list on jsp ?? Please suggest right way if this one is wrong.

2) Any other Changes in any part of following codes, which will able to fulfill my needs??

3) Any other ways for doing same thing..??

Exception:

    exception

org.apache.jasper.JasperException: An exception occurred processing JSP page /pages/studentschedule.jsp at line 301

298:                </tr>
299:                <c:forEach items="${scheduleList}" var="var">
300:                <tr>
301:                    <td><b>${var.firstname }</b></td>
302:                    <td><b>${var.roll }</b></td>
303:                    <td><b>${var.subjectname }</b></td>
304:                    <td><b>${var.classname }</b></td>


Stacktrace:
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:568)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:470)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
org.springframework.web.servlet.view.InternalResourceView.renderMergedOutputModel(InternalResourceView.java:238)
org.springframework.web.servlet.view.AbstractView.render(AbstractView.java:262)
org.springframework.web.servlet.DispatcherServlet.render(DispatcherServlet.java:1265)
org.springframework.web.servlet.DispatcherServlet.processDispatchResult(DispatcherServlet.java:1016)
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:965)
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:859)
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:883)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:781)
javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)

root cause

java.lang.NumberFormatException: For input string: "firstname"
java.lang.NumberFormatException.forInputString(Unknown Source)
java.lang.Integer.parseInt(Unknown Source)
java.lang.Integer.parseInt(Unknown Source)
javax.el.ArrayELResolver.coerce(ArrayELResolver.java:166)
javax.el.ArrayELResolver.getValue(ArrayELResolver.java:46)
javax.el.CompositeELResolver.getValue(CompositeELResolver.java:67)
org.apache.el.parser.AstValue.getValue(AstValue.java:169)
org.apache.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:189)
org.apache.jasper.runtime.PageContextImpl.proprietaryEvaluate(PageContextImpl.java:985)
org.apache.jsp.pages.studentschedule_jsp._jspx_meth_c_005fforEach_005f0(studentschedule_jsp.java:459)
org.apache.jsp.pages.studentschedule_jsp._jspService(studentschedule_jsp.java:364)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
org.springframework.web.servlet.view.InternalResourceView.renderMergedOutputModel(InternalResourceView.java:238)
org.springframework.web.servlet.view.AbstractView.render(AbstractView.java:262)
org.springframework.web.servlet.DispatcherServlet.render(DispatcherServlet.java:1265)
org.springframework.web.servlet.DispatcherServlet.processDispatchResult(DispatcherServlet.java:1016)
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:965)
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:859)
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:883)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:781)
javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
note The full stack trace of the root cause is available in the Apache Tomcat/7.0.25 logs.

Query+code is as follows.

AdminStudentDaoImpl.java

@Override
public List<Student> getStudentSchedule(int a) {
    // TODO Auto-generated method stub

    List<Student> u=hibernatetemplate.find("select stud.firstname,stud.roll,sub.subjectname,cls.classname,exam.exam_name,exam.from_time,exam.to_time,exam.exam_date from Student stud,Subject sub,Classs cls,Exam exam where exam.classs=cls.id and exam.subject=sub.id and sub.classs=cls.id and stud.classs=cls.id and stud.roll="+a);

    return u;   
}

Domain (Tables) are as follows:

student

@Entity
@Table(name="student")
public class Student {

@Id
@GeneratedValue
@Column(name="id")
private int id;
@Column(name="roll_no")
private int roll;
@Column(name="firstname")
private String firstname;
@Column(name="lastname")
private String lastname;
@Column(name="dob")
private String dob;
@Column(name="email")
private String email;
@Column(name="contactno")
private String contactno;
@ManyToOne
private Classs classs;

    Getters & Setters....... 
    }

class

@Entity
@Table(name="class")

public class Classs {

@Id
@GeneratedValue
@Column(name="id")
private int id;
    @Column(name="classname")
private String classname;
    Getters & Setters.....
}

Exam

@Entity
@Table(name="exam")
public class Exam {

@Id
@GeneratedValue
@Column(name="id")
private int id;
@Column(name="exam_name")
private String exam_name;

@Column(name="exam_date")
private String exam_date;

@Column(name="from_time")
private String from_time;

@Column(name="to_time")
private String to_time;

@ManyToOne
private Subject subject;

@ManyToOne
private Classs classs;

    Getters & Setters.....
}

Subject

@Entity
@Table(name="subject")
public class Subject {

    @Id
    @GeneratedValue

    @Column(name="id")
    private int id;

    @Column(name="subjectname")
    private String subjectname;

    @ManyToOne
    private Classs classs;

            Getters & Setters.....
}

In Controller i have written code like this :

@RequestMapping(value="schedule" ,method=RequestMethod.GET)
public ModelAndView schedule(@ModelAttribute("attribute") Student student,BindingResult result,HttpSession session)throws Exception
{
    int roll=Integer.parseInt((String) session.getAttribute("username"));
    System.out.println("roll no is========="+roll);
    List<Student> stud = adminstudentdao.getStudentSchedule(roll);
    System.out.println("list is========="+stud);
    ModelMap map=new ModelMap();
    map.addAttribute("scheduleList",stud);
    return new ModelAndView("studentschedule",map);
}

Jsp:

<table border="0" width="100%" cellpadding="0" cellspacing="0" id="schedule-table">
            <tr>

                <th class="table-header-repeat line-left minwidth-1"><a href="">Student name</th>
                <th class="table-header-repeat line-left minwidth-1"><a href="">Roll number</th>
                <th class="table-header-repeat line-left minwidth-1"><a href="">Subject</th>
                <th class="table-header-repeat line-left minwidth-1"><a href="">Class</th>
                <th class="table-header-repeat line-left minwidth-1"><a href="">exam</a></th>
                <th class="table-header-repeat line-left minwidth-1"><a href="">from time</th>
                <th class="table-header-repeat line-left minwidth-1"><a href="">to time</th>
                <th class="table-header-repeat line-left minwidth-1"><a href="">date</th>

            </tr>
             <c:if test="${empty scheduleList}">
                   <tr>
                         <td colspan="8"><b>No Results found</b></td>
                   </tr>
             </c:if>
            <c:forEach items="${scheduleList}" var="var">
            <tr>
                <td><b>${var.firstname }</b></td>
                <td><b>${var.roll }</b></td>
                <td><b>${var.subjectname }</b></td>
                <td><b>${var.classname }</b></td>
                <td><b>${var.exam_name }</b></td>
                <td><b>${var.from_time }</b></td>
                <td><b>${var.to_time }</b></td>
                <td><b>${var.exam_date }</b></td>


            </tr>
            </c:forEach>

            </table>

Nabil Salah · Accepted Answer · 2013-06-06 13:48:48Z

10

What I suggest to you is using Hibernate Native SQL. For this you can modify your AdminStudentDaoImpl.java to :

public List getStudentSchedule(int a) {
     Session sessionHb = session.openSession();
     String sql = "select stud.firstname,stud.roll,sub.subjectname,cls.classname,exam.exam_name,exam.from_time,exam.to_time,exam.exam_date from Student stud,Subject sub,Classs cls,Exam exam where exam.classs=cls.id and exam.subject=sub.id and sub.classs=cls.id and stud.classs=cls.id and stud.roll= :roll";
     SQLQuery query = sessionHb.createSQLQuery(sql);
     query.setParameter("roll", a);
     query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
     return query.list();
}

I hope it will help :)

answered Jun 6, 2013 at 13:48

Nabil Salah

3003 gold badges7 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

VJS Over a year ago

How to iterate this list return from this method ?? As in this list multiple entity's are present.

Collectives™ on Stack Overflow

How to fetch data from multiple table query using hibernate?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related